Reference | Reference Manual | grammar | Grammar reference 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 connected operations, including the definition of data (DDL) and Data Processing (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.
Data definition DDL (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.
Build the form:
Grammar:
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.
Change table
ALTER TABLE table_name
ADD COLUMN column_name DATATYPE
Note Add a field (not remove the syntax of a field)
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 deletes the definition of the primary key.
Establish an index
CREATE INDEX index_name on table_name (column_name)
Explains how to index a table's fields to increase the speed of a query
Delete
DROP table_name
DROP index_name
Data Form DATATYPE
SQL data-form datatypes
smallint
16-bit integer
Interger
32-bit integer
Decimal (P,s)
P exact value and s size of the decimal integer, the exact value P refers to all have several numbers (digits)
The size value s refers to several digits after the decimal point.
If not specifically specified, the system is set to p=5; S=0.
Float
32-bit real numbers
Double
64-bit real numbers
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 designed to support two-character-length fonts, such as Chinese characters
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.
Data manipulation DML (data manipulation Language)
After the data is defined, the next thing is the operation of the data. The operation of the data is nothing more than the addition of data (insert), query data (query), change data (update), delete data (delete) four modes, the following describes their syntax:
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 pass a subquery (subquery) to fill in the other forms of information
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, listing the eligible data
SELECT Column1,column2
From table_name
Order by Column2 [DESC]
Description
Order BY is to specify a column to be sorted, [DESC] refers to from large to small, if
Not specified, is from small to large arrangement
Group Query
Combined query means that the source of the query is not only a single form, but a combination of more than one table can get results.
SELECT *
From Table1,table2
WHERE Table1.colum1=table2.column1
Description
1. Information on the same column1 values in two tables
2. Of course, two tables are compared to each other, and their data form must be the same
3. A complex query may be used in a number of forms
Consolidated queries:
SELECT COUNT (*)
From table_name
WHERE column_name = xxx
Description
Query for eligible information A total of several pens
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 calculated average, MAX (), MIN ()
An integrated query that calculates the maximum minimum value
SELECT Column1,avg (Column2)
From table_name
GROUP by Column1
Having AVG (column2) > XXX
Description
1.GROUP by: Calculates the average of Column2 in a group of Column1
Must be used with keywords for consolidated queries such as AVG, SUM, and so on
2.HAVING: Must be used with GROUP by as a consolidation constraint
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 rear is a collection that indicates that the COLUMN1 exists in the collection.
2. The data form of SELECT must conform to Column1
Other queries
SELECT *
From table_name1
WHERE column1 like ' x percent '
Description
1.LIKE must correspond to the ' percent ' of the back to indicate a string beginning with X
SELECT *
From table_name1
WHERE column1 in (' xxx ', ' yyy ',..)
Description
1. In the rear is a collection that indicates that the COLUMN1 exists in the collection.
SELECT *
From table_name1
WHERE column1 BETWEEN xx and yy
Description
1. BETWEEN indicates that the value of Column1 is between XX and yy
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 to be met, if there is no WHERE
That field in the entire table will be changed.
Delete data:
DELETE from table_name
WHERE conditions
Note: Delete eligible information
