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.)
LTER TABLE TABLE_NAME Add Primault 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 and the size of the size of the S size, the precise value P refers to all of the number (DIGITS) size, and S is a few digits after the decimal. 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 a font that supports two character lengths, 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 color2> yyy] [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 in a certain field to be sorted, [DESC] means from big to small arrangement, if there is no indication, it is from small to large.
Combination query
Combined query refers to the source of 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, ... SABLE2 WHERE TABLE1.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_name Group by Column1 Having Avg (Column2)> XXX
Description:
1.Group BY: Use column1 to calculate the average of Column2 for a set of column2, must be used with keywords of AVG, SUM and other integrated queries.
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 column1 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:
Where mydatetime> to_date ('2000-01-01 10:00:01', 'YYYY-MM-DD HH24: MI: SS')
