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 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 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 an item sets a certain field as the 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 the DROP TABLE_NAME DROP INDEX_NAME II, the data form DataTypes Smallint 16-bit elements. 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. 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 column
1 in
(Select Column1 From Table_name2 WHERE CONDitions) Description: 1. In the back is 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 back 'X%' Echo indicates the string starting with X. Select * from table_name1 where column
1 in
('Xxx', 'yyy', ..) Description: The later is 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 conditionoins 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_name where conditions Description: Delete eligible information.
Note: About WHERE conditions If there is a date, different databases have different expressions. The specific is as follows: (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 ') Writing in Delphi: thisdate ='
2000-01-01
'; Query1.sql.add (' Select * from abc where mydate> Cast (' ' '' ' thedate ' '' 'as date)');
If the date time is: where mydatetime> to_date ('2000-01-01 10:00:
01 '
, 'YYYY-MM-DD HH24: MI: SS')
