SQL Syntax Reference Manual (SQL)
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.
First, the data definition DDL (Data Definition Language)
Data-oriented language refers to the language defined in the format and morphology of the information. He is the first to face when each database is to be established. Which of the formats are all in the form, what kind of key key, form, and The relationship between the tables, etc., all must plan at the beginning.
1, 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 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 (Column_Name)
Description: Change the definition of an analog to set a certain field as a primary key.
Alter Table Table_name
Drop Primary Key (Column_Name)
Description: Delete the definition of the primary key.
3, establish an index
Create index index_name on table_name (Column_name)
Description: Establish an index for a table's field to increase the speed of the query.
4, delete
Drop Table_name
Drop Index_name
Second, the data form DATATYPES
Smallint
The integer of 16-bit yuan.
Interger
32-bit integers.
Decimal (p, s)
p Precision 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 decimal
There are several digits after the point. If there is no special designation, the system is set to P = 5; s = 0.
Float
32-bit elements.
Double
The real number of 64-bit yuan.
char (n)
N the string of the length, n cannot exceed 254.
VARCHAR (N)
The length of the length is not fixed and its maximum length is N, N cannot exceed 4000.
Graphic (n)
Like 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)
The double-character string of variable length and its maximum length is N, N cannot exceed 2000.
date
Contains year, month, date.
Time
It contains hours, minutes, and seconds.
Timestamp
Includes year, month, day, hour, minute, second, one thousandth.
Third, data operation DML (Data Manipulation Language)
After the data is defined, the next thing 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, increase 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 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, Columny, ... from another_table
Explanation: You can also fill in other forms of tables through a subquery.
2, inquiry information:
Basic query
Select Column1, Columns2, ...
From table_name
Explanation: All of the specific fields of Table_name is listed all.
SELECT *
From table_name
Where column1 = xxx
[And color] [or column3 <> zzz]
Description:
1. '*' Indicates 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 big to small, if there is no indication, it is from small to large
arrangement
Combination query
Combined query refers to the source of information that is queried does not only have a single form, but a combination of more than one.
Table can be obtained.
SELECT *
From Table1, Table2
Where table1.colum1 = Table2.column1
Description:
1. Query the same information on the same value in both 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.
Integration query:
SELECT Count (*)
From table_name
Where colorn_name = xxx
Description:
There are a few of the qualified information.
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 column1
Having Avg (Column2)> XXX
Description:
1.Group by: Keywords that calculate the average of COLUMN2 with column1 for a set of column2
use together.
2.Having: It must be used as a constraint for integration with Group By.
Complex 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 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 colorn1 like 'x%'
Note: The elements of LIKE must be in the back 'X%' indicating the string starting with X.
SELECT *
From table_name1
WHERE column1 in ('xxx', 'yyy', ..)
Note: The later is connected is a collection, indicating that the column1 exists in a collection.
SELECT *
From table_name1
Where column1 betWeen XX and YY
Note: 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. 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. details 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')
Write in Delphi:
TheDate = '2000-01-01';
Query1.sql.add ('Select * from abc where mydate> Cast (' '' '' theDate '' ' ' as date) ');
If the comparative date is:
