DB2 provides a relational database query language sql (structured query language), is a very colloquial, not only to learn it to understand syntax. This language is almost every database system must be provided to indicate the connection type, and contains a data definition (ddl), and data processing (dml). sql original spell sequel, the language of the prototype "system r" in the name of San Jose Laboratory ibm, ibm internal and the other through the use and efficiency of many tests, the results were satisfactory, and decided to Systems developed the technical basis of r ibm products. And the American National Standards Institute (ansi) and the International Organization for Standardization (iso) in 1987, followed by an almost standard ibm sql-based language of the definition of relational data.
First, data to define DDL (data definition language)
Data set language is that the format and form of information to define the language, he is every time the database should be established when the first had to confront that whenever the form data be divided relationship, what form of a bar-bit host key, forms and mutual relationship between the reference form, etc., all in the beginning of the planning that must be good.
1, built form:
create table table_name (column1 datatype [not null] [not null primary key], column2 datatype [not null], ...)
Description:
datatype - is the data format, see Table.
nut null - Can allow the information free of the (not yet have information to fill in).
primary key - primary key of this table.
2, change the form
alter table table_name add column column_name datatype
Description: Add a field (not delete a field syntax.
alter table table_name add primary key (column_name)
Note: change the definition of the table have a field to set the primary key.
alter table table_name drop primary key (column_name)
Note: the definition of the primary key to delete.
3, indexing
create index index_name on table_name (column_name)
Note: The fields of a table index to increase query speed.
4, delete
drop table_name drop index_name
Second, DDL Information form datatypes
smallint 16-bit integer.
interger 32-bit integer.
decimal (p, s) p s exact value and the size of a decimal integer, the exact value of p is the number of all there are several (digits) size value, s is some number after the decimal point. If not specified, the system will be set to p = 5; s = 0.
float 32-bit real number.
double 64-bit real number.
char (n) n-length string, n can not be more than 254.
varchar (n) length is not fixed and the maximum string length n, n can not be more than 4000.
graphic (n) and char (n) the same, but the unit is two words per double-bytes, n can not be more than 127. This form is to support the length of words per font, such as Chinese characters.
vargraphic (n) variable length and its maximum length n of the two-character string, n can not be more than 2000.
date contains the year, month, date.
time includes the hours, minutes, seconds.
timestamp contains the year, month, day, hour, minute, second, millisecond.
Third, data manipulation DML (data manipulation language)
Information after the definition of good is the information the next operation. Information on the operation of no more than increase the data (insert), query data (query), change the data (update), delete (delete) the four models, the following were to introduce their syntax:
1, additional information:
insert into table_name (column1, column2 ,...) values (value1, value2, ...)
Description:
1. If the column is not specified, the system will be in accordance with the order form fill in the information field.
2. Field data form and fill in the information which must be consistent.
3.table_name can also be a landscape view_name.
insert into table_name (column1, column2 ,...) select columnx, columny, ... from another_table
Note: You can also go through a sub-query (subquery) to fill in other forms of information.
2, the query information:
Basic Query
select column1, columns2, ... from table_name
Note: the table_name field-specific information to list out
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table_name where column1 = xxx [and column2> yyy] [or column3 <> zzz]
Description:
1 .''''*'''' that all fields are listed.
2.where followed by conditional access, to meet the requirements of the information listed.
select column1, column2 from table_name order by column2 [desc]
Description: order by is specified to do a column sort, [desc] is the big to small order, if not specified, it is ordered from small to large
Combined query
Combination query is obtained by the query source does not have a single form, but more than one form joint can get results.
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table1, table2 where table1.colum1 = table2.column1
Description:
1. Query two tables in one of the column1 value of the same information.
2. Of course, comparing the two form fields, the information must be the same shape.
3. A complex query into the form of its use may be many months.
Integrated query:
select count (*) from table_name where column_name = xxx
Note: Query data meet the requirements of a few there.
select sum (column1) from table_name
Note: 1. Calculate the sum of the selected field must be countable number of forms.
2. In addition there are avg () is the calculation of the average, max (), min () minimum value of integration for calculating the maximum query.
select column1, avg (column2) from table_name group by column1 having avg (column2)> xxx
Description: 1.group by: to column1 column2 calculated as the average of a group must avg, sum and so integrated with the use of the keyword query.
2.having: must be used together as an integrated group by the restrictions.
Compound query
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table_name1 where exists (select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table_name2 where conditions)
Description: 1.where the conditions can be another's query.
2.exists refers to the existence of.
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table_name1 where column1 in (select column1 from table_name2 where conditions)
Note: 1. In the back then was a collection that exists column1 set inside.
2. Select out the information form must be consistent with column1.
Other inquiries
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table_name1 where column1 like''''x %''''
Description: like to be and behind''''x %'''' echoes that start with x as string.
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table_name1 where column1 in ('''' xxx'''','''' yyy'''',..)
Note: in the back then is a collection of collections that exist inside column1.
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table_name1 where column1 between xx and yy
Note: between the values that column1 between between xx and yy.
3, change the data:
update table_name set column1 ='''' xxx''''where conditoins
Description:
1. To change a field to set the value of''''xxx''''銆?br />
2.conditions is to be consistent with the conditions, if there is no where for the entire table of the field will all be changed.
4, delete:
delete from table_name where conditions
Description: Remove matching information.
Note: where the conditions on the back if the comparison includes the date, different databases have different expressions. As follows:
(1) If access the database, was: where mydate> # 2000-01-01 #
(2) If the oracle database, was: where mydate> cast ('''' 2000-01-01''''as date)
Or: where mydate> to_date ('''' 2000-01-01'''','''' yyyy-mm-dd'''')
Is written in delphi: thedate ='''' 2000-01-01'''';
query1.sql.add ('''' select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from abc where mydate> cast (''''+''''''''' '''''''+ thedate +''''''''''''''''+'''' as date )'''');
If you compare date and time types, was: where mydatetime> to_date ('''' 2000-01-01 10:00:01'''','''' yyyy-mm-dd hh24: mi: ss''' ')
相关链接:
M4a to m4r
.ape Files
.ts file Extension
Project Management introduction
Easy Gallery And Cataloging Tools
Senior Advanced: 12 strokes overall speed of your Firefox 3.5
He Jie won the Xuhui District, President of ice, "KPS Cup top ten youth"
Understanding of EIP
Deletion of posts by the company, "3.15" price hike: prices starting at 20,000 yuan
Mathematics Education Expert
Video formats
The brilliant idea to computer speed Do not waste your big memory
Simple Network And Internet
Media Relations Public Relations Manager, How To Protect Journalists
BRIGHT out of 3G
Assembly INSTRUCTIONS and the machine code of each conversion
No comments:
Post a Comment