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 (not delete the syntax of a certain field. RALTER TABLE TABLE_NAMEADD PRIMARY Key "Description: Change the definition of the episode to set a certain field as the primary key .Alter Table Table_namedrop 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_NAMEDROP INDEX_NAME
Second, the data form is the integer of the DattyPESSMALLINT16. Integer32-bit integer. Decimal (p, s) p Precise value and the size of the size of the S size, the precise value P refers to all of the 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. Float32-bit real number. Double64 bit of 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 to support two fonts of the length of the two characters, 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 fill in the information in the order 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, columns2, ... from table_name Description: Column of Table_Name is listed in SELECT * home_namewhere column1 = xxx [and color [and color "]] [or column3 <> zzz] : 1. '' * '' means all the fields are listed. 2.Where is the conditional pattern, and the eligible information is listed.
Select Column1, Column2From Table_NameOrder by Column2 [Desc] Description: Order by is specified to be sorted in a certain field, [DESC] refers to the rank from the big to small, and is not specified, 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, table2where table1.colum1 = Table2.column1 Description: 1. Query the same information on 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_namewhere 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_nameGroup By Column1Having Avg (Column2)> xxx Description: 1.Group By: Use Column1 to calculate the average of the COLUMN2 and the keywords of the AVG, SUM and other integrated queries. 2.Having: It must be used as a constraint for integration with Group By. Complex Select * from Table_name1where EXISTS (Select * from table_name2where conditions) Description: 1.where's consivations can be another Query. 2.Exists herein refers to whether there is. Select * from table_name1where column1 in (Select Column1 from Table_Name2where Conditions) Description: 1. In the back is connected, it is a collection, indicating that the column1 exists in a collection. 2. SELECT's data must meet Column1.
Other Query Select * from table_name1where column1 like '' 'X%' 'Description: LIKE must and the back' 'X%' 'Element indicates the string starting with X. Select * from table_name1where column1 in ('' XXX '', 'YYY', ..) Description: The later is connected to a collection, indicating that the Column1 exists in a collection. Select * from table_name1where column1 betWeen XX and yy Description: BetWeen indicates that the value of Column1 is between XX and YY.
3, change information: update table_nameset column1 = '' xxx''where conditoins Description: 1. Change a certain 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_namewhere 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,: where mydate> cast ('' 2000-01-01 'AS Date) or: where mydate> to_date ('' 2000-01-01 '', '' YYYY-mm-DD '') is written in Delphi: theDate = '' 2000-01-01 '; query1.sql. Add ('SELECT * from ABC WHERE MyDate> Cast (' ' ' '' '' '' '' '' 'AS Date)'); if the date and time type, Then: where mydatetime> to_date ('' 2000-01-01 10:00:01 ',' 'YYYY-MM-DD HH24: MI: SS' ')
