DB2 provides a query language SQL (Structure Query Language) of the off-site database, which is a very spoken, easy-to-understand syntax. This language is almost per database system must be provided to indicate a connection operation, including data definition (DDL), and processing processing (DML). SQL originally metrics, the prototype of this language is completed in the IBM San Jose laboratory with the name "System R". It is quite satisfactory, and the result is quite satisfactory and determines in the system. R The technical foundation of R is developed from IBM products. Moreover, the National Standards Association (ANSI) and the International Standardization Organization (ISO follows an almost IBM SQL-based standard connection information language definition. Data definition DDL (Data Definition Language) Data-specific language is the format of the information And the language defined in the shape, he is the first to face each database, and what is the formal relationship of the information, what is the main key, the table, and the table, the relationship between the table, and the table It is all necessary to plan at the beginning.
Build a form: Create Table Table_name (Column1 Datatype [NOT NULL] [NOT NULL Primary Key], Column2 DataType [not null], ...) Description DataType - is the format of the information, see the table. NUT NULL - You can not allow information to be available (not yet filled in). Primary Key - is the primary key of this table.
Change table ALTER TABLE table_name ADD COLUMN column_name DATATYPE instructions to add a field (not delete a field of grammar) ALTER TABLE table_nameADD PRIMARY KEY (column_name) instructions to change the definition of the table have a primary key field located ALTER TABLE table_nameDROP PRIMARY Key (column_name) Description Remove the definition of the primary key.
Establish index create index index_name on table_name (column_name) Description Establish an index for a table to increase the speed of the query
Delete DROP TABLE_NAMEDROP INDEX_NAME
Data form DataTypeSQL's data form DATATYPESSMALLINT16-bit integer interger32-bit integer decimal (p, s) p accuracy value and S size ten-in-one integers, accurate value P refers to all several (DIGITS) large value S is There are several digits after the decimal point. If there is no special designation, the system will be set to P = 5; s = 0. FLOAT32-bit real number Double64-bit real number CHAR (N) N length string, N cannot exceed 254VARCHAR (N) length is not fixed and its maximum length is a string of N, N cannot exceed 4000 graphic (n) and char (N) and CHAR N), but its unit is two characters Double-Bytes, n cannot exceed 127. This form is to support the font of the length of the two characters, such as the Chinese word vargraphic (n) variable length and its maximum length is a double-character string, and N cannot exceed 2000date contains years, the month, date .time contains Hours, minutes, second .timestamp included the year, month, day, hour, minute, second, one thousand second. After the data operation DML (Data manipulation language) is defined, the next is the operation of the data. The operation is not more in addition to adding information (query), query, changing the information (UPDATE), deleting four modes, the following introduces their syntax:
Add data: INSERT INTO TABLE_NAME (Column1, Column2, ...) VALUES (Value1, Value2, ...) Description: 1. If you do not specify a column system, you will fill in the information in the order in the form of the table 2. The field The data form and the filled information must be consistent with the 3.Table_name or the landscape view_name
INSERT INTO TABLE_NAME (Column1, Column2, ...) Select Columnx, Columnx, Columnx, ... from another_table description: You can also fill in other forms of forms after a subquery (Subquery)
Query information: Basic query Select Column1, Column2, ... from table_name Description: Column of Table_name's specific field information is listed in SELECT * from table_namewhere column1 = xxx [and color [and color "]] [or column3 <> zzz] Description: 1 . '*' Indicates that all fields are listed in 2.where is the conditional style, list the eligible information to select Column1, column2From table_nameorder by column2 [dec] Description: Order by is specified to do with a certain field Sort, [DESC] refers to the ranking from the big to small, and if there is no indication, it is from small to large.
Combined query combined query refers to the source of the data that is not only a single table, but a combination of more than one table can be obtained. Select * from table1.colum1 = table2.column1 Description: 1. Query the same information in the two tables 2. Of course, the two forms are compared to each other, and their data must be the same 3. a complex Query the table that can be used may be many
Integration query: select count (*) from table = xxx Description: Query the qualified information A total of several Select Sum (Column1) from table_name Description: 1. Calculate the sum, the selected field must be possible Digital morphology 2. In addition to this, AVG () is calculating average, max (), min () integrated integrace SELECT Column1, AVG (Column2) from table_namegroup by Column1haVing AVG (Column2)> XXX Description: 1.Group BY: Use column1 to calculate the average of COLUMN2. Use 2.Having: You must use the Group By to use the restriction complexity Query SELECT * FROM table_name1WHERE eXISTS (SELECT * FROM table_name2WHERE conditions) Description: 1.WHERE the Conditions may be another one query2.EXISTS herein refers to the presence or absence SELECT * FROM table_name1WHERE column1 iN (SELECT column1 FROM table_name2WHERE conditions) described later 1. iN The connection is a collection, indicating that the color of Column1 exists 2. SELECT's data form must comply with column1
Other Query Select * from table_name1where column1 limited 'x%' Description: 1.Like must and the back 'X%' Elementaries indicate the string Slect * from Table_name1where column1 in ('xxx', 'yyy "starting with x ..) Note 1. In the back, it is a collection, indicating that column1 exists in the collection Select * from table_name1where column1 betWeen XX and yy Description 1. Between indicates that the value of Column1 is between XX and YY
Change information: update table_nameset column1 = 'xxx'where conditoins Description: 1. Changing a field setting It is' xxx'2.conditions is the condition you want, if there is no WHER, the entire table will be all Change
Delete information: delete from table_namewhere conditions Description: Delete eligible information
