SQL syntax reference Manual (SQL)

Reference | Reference Manual | grammar | Grammar reference
SQL syntax reference Manual (SQL)

DB2 provides query language SQL (Structured query Language) of the related database, which is a very colloquial, easy to learn and understandable syntax. This language is almost always required by every database system to represent a connected type of * doing, including the definition of data (DDL) and the processing of data (DML). SQL originally spelled sequel, the language of the prototype of "System R" in the name of the IBM San Jose Laboratory completed, through the internal IBM and many other usability and efficiency testing, the results are quite satisfactory, and decided to develop the system R technology based on IBM's products. And the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) in 1987 follow an almost IBM-SQL based standard related data language definition.

I. Data definition DDL (data definition Language)
Data-setting language refers to the language defined in the format and form of data, he is the first to be set up when each database is to be established, what is the form of data, the form of a field key, tables and tables of the relationship between the reference and so on, are in the beginning must be planned.

1, build the form:
CREATE TABLE table_name (
Column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY],
Column2 DATATYPE [Not NULL],
... ）
Description
DATATYPE--is the format of the data, as detailed in the table.
NUT null--Can you allow the data to be empty (no data has been filled in yet).
PRIMARY Key--is the primary key for this table.

2. Change the form
ALTER TABLE table_name
ADD COLUMN column_name DATATYPE
Description: Adds a field (without deleting a field's syntax.)
ALTER TABLE table_name
ADD PRIMARY KEY (column_name)
Description: Change the definition of a table to set a field as the primary key.
ALTER TABLE table_name
DROP PRIMARY KEY (column_name)
Description: Delete the definition of the primary key.

3, the establishment of the index
CREATE INDEX index_name on table_name (column_name)
Description: Index The field of a table to increase the speed of the query.

4, delete
DROP table_name
DROP index_name

Second, the data form datatypes
smallint
A 16-bit integer.
Interger
A 32-bit integer.
Decimal (P,s)
P exact value and s size decimal integer, the exact value P refers to the total number of values (digits) size value, S is the decimal
There are several numbers after the point. If not specifically specified, the system is set to p=5; S=0.
Float
A 32-bit real number.
Double
A 64-bit real number.
CHAR (n)
N-length string, n cannot exceed 254.
VARCHAR (n)
A string whose length is not fixed and its maximum length is n, which cannot exceed 4000.
Graphic (n)
Same as char (n), but its unit is two characters Double-bytes and N cannot exceed 127. This form is for
A font that supports two character lengths, such as Chinese text.
Vargraphic (N)
A two-character string with a variable length and a maximum length of n, and N cannot exceed 2000.
Date
Contains the year, month, and date.
Time
Contains the hours, minutes, and seconds.
Timestamp
Includes year, month, day, time, minutes, seconds, 1 per thousand seconds.

III. data * For DML (data manipulation Language)
After the data is defined, the next thing is the data. The information is made up of four modes of additional data (insert), query data (queries), change data (update), delete data (delete), and the following are their syntax:

1. Additional Information:
INSERT into table_name (COLUMN1,COLUMN2,...)
ValueS (value1,value2, ...)
Description
1. If no column system is specified, the information will be filled in the order of the fields in the table.
2. The data form of the field and the information to be filled must match.
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 subquery (subquery) to fill in the other forms of information.

2, Inquiry information:
Basic Query
SELECT Column1,columns2,...
From table_name
Description: List all the specific field data of the TABLE_NAME
SELECT *
From table_name
WHERE column1 = xxx
[and Column2 > YYY] [OR column3 <> zzz]
Description
1. ' * ' indicates that all of the fields are listed.
The 2.WHERE is followed by a conditional type, which lists the eligible data.

SELECT Column1,column2
From table_name
Order by Column2 [DESC]
Description: Order BY IS to specify a column to do the sort, [DESC] refers to from large to small, if not specified, is small to large
Arranged

Group Query
A combination query means that the source of the query is not only a single form, but a joint of more than one
The form will be able to get the result.
SELECT *
From Table1,table2
WHERE Table1.colum1=table2.column1
Description
1. Query the information in the two tables with the same column1 value.
2. Of course, two tables are compared to each other in a field with the same data form.
3. A complex query may be used in a number of forms.

Consolidated queries:
SELECT COUNT (*)
From table_name
WHERE column_name = xxx
Description
There are a few strokes of the information that meets the criteria.
SELECT SUM (Column1)
From table_name
Description
1. Calculates the sum, the selected field must be a number shape that can be counted.
2. In addition to AVG () is the calculation of average, Max (), Min () to calculate the maximum minimum value of the integration of the query.
SELECT Column1,avg (Column2)
From table_name
GROUP by Column1
Having AVG (column2) > XXX
Description
1.GROUP by: Column1 for a set of column2 average must be the keyword of the consolidated query such as AVG, SUM, etc.
Used together.
2.HAVING: Must be used with GROUP by as a consolidation restriction.

Complex query
SELECT *
From table_name1
WHERE EXISTS (
SELECT *
From Table_name2
WHERE conditions)
Description
The 1.WHERE conditions can be another query.
2.EXISTS here refers to the existence or not.
SELECT *
From table_name1
WHERE Column1 in (
SELECT Column1
From Table_name2
WHERE conditions)
Description
1. In the back face is a collection that indicates that the COLUMN1 exists within the set.
2. The data form of SELECT must conform to column1.

Other queries
SELECT *
From table_name1
WHERE column1 like ' x percent '
Description: Like must correspond to the ' x ' of the back side to indicate a string with the beginning of X.
SELECT *
From table_name1
WHERE column1 in (' xxx ', ' yyy ',..)
Description: In the back face is a collection, which means that the column1 exists within the set.
SELECT *
From table_name1
WHERE column1 BETWEEN xx and yy
Description: BETWEEN indicates that the value of Column1 is between XX and yy.

3. Change information:
UPDATE table_name
SET column1= ' xxx '
WHERE Conditoins
Description
1. Change a field to set its value to ' xxx '.
2.conditions is the condition you want to meet, and if there is no WHERE, the entire table's field will all be changed.

4, delete the information:
DELETE from table_name
WHERE conditions
Note: Delete the eligible information.

Note: There are different expressions for different databases if there is a comparison of dates after the Where condition. Specifically as follows:
(1) In the case of an Access database: WHERE mydate> #2000 -01-01#
(2) If it is an Oracle database: WHERE mydate>cast (' 2000-01-01 ' as date)
Or: WHERE mydate>to_date (' 2000-01-01 ', ' yyyy-mm-dd ')
Written in Delphi:
Thedate= ' 2000-01-01 ';
Query1. Sql.add (' SELECT * FROM ABC where mydate>cast (' + ' ' ' +thedate+ ' ' + ' as Date ');

If the date-time type is compared:
WHERE mydatetime>to_date (' 2000-01-01 10:00:01 ', ' yyyy-mm-dd hh24:mi:ss ')