SQL Syntax Reference Manual
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 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 definition of the primary key.
3. 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.
4, delete DROP TABLE_NAME DROP INDEX_NAME
Second, the data form is an integer of DataTypes Smallint 16-bit yuan. Integer 32-bit integers. Decimal (p, s) p accuracy value and the size of the size of the s size, the precise value P refers to all of the number (DIGITS) large values, S is a few digits after the decimal point. If there is no special designation, the system is set to P = 5; s = 0. Float 32-bit real number. Double 64-bit real. CHAR (N) N length string, n cannot exceed 254. VARCHAR (N) length is not fixed and its maximum length is a string of N, N cannot exceed 4000. Grawhic (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 data operation DML (Data Manipulation Language) is defined after the data is the operation of the information. The operation is not more than an Insert, query information (Query), change the information (UPDATE), deleting four modes, the following points introduce their syntax:
1. Add information: INSERT INTO TABLE_NAME (Column1, Column2, ...) VALUES (Value1, Value2, ...) Description: 1. If you do not specify a column system, you will be filled in the order in the field in the form. 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. "*" means that all fields are listed. 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, [dec] means from the big to small, 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, 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. Select Column1, AVG (Column2) from Table_name Group By Column1 Having AVG (Column2)> xxx Description: 1.Group By: Use Column1 to calculate the average of the COLUMN2 must be used with the keywords of AVG, SUM and other integrated queries . 2.Having: It must be used as a constraint for integration with Group By. Complency Query Select * from Table_name1 WHERE EXISTS (Select * from table_name2 where conditions) Description: 1.where's conditions can be another Query. 2.Exists herein refers to whether there is. Select * from table_name1 WHERE Column1 in (Select Column1 from Table_Name2 WHERE CONDitions) Description: 1. The later is connected to a collection, indicating that the column1 exists in a collection. 2. SELECT's data must meet Column1.
Other Query Select * from table_name1 WHERE Column1 Like "X%" Description: LIKE must be and the "x%" adhered to the "x%" indicates that the string starting with X. Select * from table_name1 where column1 in ("xxx", "yyy", ..) Description: The later is connected to a collection, indicating that the column1 exists in a collection. 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. Changing a field setting It is "xxx". 2. Conditions is the condition you have to match. If there is no WHERE, all TABLE will be all changed.
4, delete information: delete from table_name where conditions Description: Delete eligible information.
Note: About WHERE conditions If there is a date, different databases have different expressions. The specific: (1) If it is an Access database, it is: where mydate> # 2000-01-01 # (2) If it is an Oracle database, it is: where mydate> cast ("2000-01-01" AS Date) Or: where mydate> to_date ("2000-01-01", "YYYY-MM-DD") writes in Delphi: these = "2000-01-01"; query1.sql.add ('Select * from abc Where MyDATE> CAST (' ' '') '); if the comparative date is: where mydatetime> to_date ("2000-01-01 10:00:01" "YYYY-MM-DD HH24: MI: SS")
Author: unknown Source: www.programer.com
