SQL statement syntax in DB2

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.

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 of DDL datatypes

smallint a 16-bit integer.

Interger a 32-bit integer.

Decimal (P,S) p exact value and a decimal integer of s size, the exact value p means that all have several number (digits) size values, S is the number of digits after the decimal point. If not specifically specified, the system is set to p=5; S=0.

A float 32-bit real number.

A double 64-bit real number.

char (n) n length of string, n cannot exceed 254.

varchar (n) length is not fixed and its maximum length is n of the string, n can not exceed 4000.

Graphic (n) is the 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 that cannot exceed 2000.

Date contains the year, month, and date.

Time contains hours, minutes, and seconds.

Timestamp contains years, months, days, hours, minutes, seconds, and 1 per thousand seconds.

Iii. 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:

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. ' * ' means that all of the fields are listed.

The 2.where is followed by a conditional type, which lists the eligible data.