SQL Syntax Reference Manual (SQL) DB2 provides a query language SQL (Structure Query Language) of the Off-Database, which is a very spoken, easy-to-understand syntax. This language is almost almost every library system, which is used to indicate a connection-like *, including the definition (DDL) of the data (DDL), and 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 Standard Society (ANSI) and the International Standardization Organization (ISO) follows an almost IBM SQL-based standard-based standard-off language definition in 1987. I. Data definition DDL (Data Definition language) DDL is a language defined in the format and morphology of the information. He is the first to face when each database is to be established, and the data is divided into which form relationships, forms The relationship between the primary key, the table and the table, etc., all must be planned at the beginning. 1. Built: Create Table Table_Name (Column1 Datatype [NOT NULL] [Not Null Primary Key], Column2 DataType [Not Null], ...) Description: DataType - is the format of the data, 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. 2, change the form ALTER TABLE_NAME Add Column Column_name DataType Description: Add a field (no grammar to delete a certain field. Alter Table Table_Name Add Primary Key Description: Change the definition of the episode sets a certain field set Primary key. ALTER TABLE TABLE_NAME DROP Primary Key Description: Delete the primary key. 3, establish an index create index index_name on table_name (column_name) Description: Establish an index for a table to increase the speed of the query. 4 Delete DROP TABLE_NAME DROP INDEX_NAME II, the data form DataTypes Smallint 16-bit integers. Interger 32-bit elements of the integer. Decimal (p, s) p exact value and S size ten-in-one integer, accurate value P is all Several number (DIGITS) large value, S is a few digits after the decimal point. If there is no special designation, the system is set to P = 5; s = 0. The real number of Float 32-bit yuan. Double 64-bit real number CHAR (N) N length string, N cannot exceed 254. VARCHAR (N) is not fixed and its maximum length is a string of N, N cannot exceed 4000. Graphic (n) and char (n), but Its unit is two characters Double-Bytes, N cannot exceed 127.
This form is a font that supports two character lengths, such as Chinese characters. Vargraphic (n) variable length and its maximum length is a double-character string of N, N cannot exceed 2000. Date contains years, month, date. Time contains hours, minutes, seconds. Timestamp contains years, months, days, time, minute, seconds, thousands of seconds. Third, the information * Data manipulation language is defined after the definition is the information of the information. Information is not more in addition to add data, query information (query), change data (UPDATE), deleting data (delete), the following introduces their grammar: 1, add information: Insert Into Table_name Column1, Column2, ...) Values (Value1, Value2, ...) Description: 1. If you do not specify a column system, it will be filled in the order in the field within the table. 2. The data form of the field and the information filled in the field must be consistent. 3.Table_name can also be a landscape view_name. INSERT INTO TABLE_NAME (Column1, Column2, ...) SELECT Columnx, Columnx, Column, ... from another_table description: You can also fill in other forms of tables after a subquery (SubQuery). 2, inquiry information: Basic query Select Column1, Column2, ... from Table_Name Description: Column of Table_name's specific field information SELECT * home_name where column1 = xxx [and color2> yyy] [or column3 <> zzz] Description: 1. '*' is listed in all fields. 2.Where is the conditional pattern, and the eligible information is listed. Select Column1, Column2 from Table_Name ORDER BY Column2 [Desc] Description: ORDER BY is specified to be sorted in a certain field, and [dec] means from big to small arrangement, if there is no indication, it is from a small to large array inquiry combination The query refers to the source of the data is not only a single table, but a combination of more than one table can be obtained. Select * from table1, table2 where table1.colum1 = table2.column1 Description: 1. Query data in both tables 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 that it can be used to use may be many. Integrated query: select count (*) from table_name where colorn_name = xxx Description: Querying the qualified information has a total of several. SELECT SUM (Column1) from table_name Description: 1. Calculate the sum, the selected field must be a number of digital forms. 2. Avg () is the integrated query for calculating average, max () and min () calculate the maximum minimum.
