SQL syntax Reference Manual

I. Data Definition Language)

A document language is a language defined for the format and form of a document. It is the first thing that every database needs to create, the table relationships, column primary keys in the table, and the reference relationships between tables are all planned at the beginning.

1. Create a table:

Create Table table_name (column1 datatype [not null] [not null primary key], column2 datatype [not null],...)

Note:

Datatype -- is the data format. For details, see the table.
Nut null-do not allow empty data (no data is provided ).
Primary Key -- is the primary key of the table.

2. Change the table

Alter table table_name add column column_name datatype

Note: Add a column (the syntax of a column is not deleted .)

LTER table table_name add primary key (column_name)

Note: You can change the table definition to set a column as a primary key.

Alter table table_name drop primary key (column_name)

Delete the definition of the primary key.

3. Create an index

Create index index_name on table_name (column_name)

Note: index the column of a table to increase the query speed.

4. Delete

Drop table_name

Drop index_name

Ii. Data Format datatypes

Smallint
An integer of 16 bits.

Interger
An integer of 32 bits.

Decimal (P, S)
The exact value of P and the decimal integer of S. The exact value of P refers to the number of digits after a decimal number (digits. If not specified, the system is set to P = 5; S = 0.

Float
32-bit real number.

Double
The real number of 64-bit RMB.

Char (N)
A string of N length. N cannot exceed 254.

Varchar (N)
A string with an unfixed length and a maximum length of N. N cannot exceed 4000.

Graphic (N)
The Unit is the same as char (N), but it is a double-bytes character. N cannot exceed 127. This form is a font supporting two character lengths, such as Chinese characters.

Vargraphic (N)
A two-character string with a variable length and a maximum length of N. N cannot exceed 2000.

Date
Contains the year, month, and date.

Time
Contains hours, minutes, and seconds.

Timestamp
Including year, month, day, hour, minute, second, 1‰ seconds.
3. data manipulation language)

After the data is defined, the next step is the data operation. Data operations are similar to four modes: insert, query, update, and delete:

1. Add information:

Insert into table_name (column1, column2,...) values (value1, value2 ,...)

Note:

1. If column is not specified, the system will fill in the data in the column order in the table.

2. The information format of the column must be consistent with the information entered.

3. table_name can also be landscape view_name.

Insert into table_name (column1, column2,...) Select columnx, columny,... from another_table

Note: You can also enter the data in other tables through a subquery.

2. Query Information:

Basic Query

Select column1, columns2. .. From table_name

Description: lists all specific column information of table_name.

Select * From table_name where column1 = xxx [and column2> YYY] [or column3 <> ZZZ]

Note:

1. '*' indicates that all columns are listed.

2. The WHERE clause is followed by a conditional statement, listing the qualified materials.

Select column1, column2 from table_name order by column2 [DESC]

Note: order by is used to sort by a column. [DESC] refers to the arrangement from large to small. If not specified, it is arranged from small to large.

Combined Query

Combined Query means that the data source queried is not only a single table, but more than one table can be combined to obtain results.

Select * From Table1, Table2 where table1.colum1 = table2.column1

Note:

1. query the data in the two tables with the same column1 value.

2. Of course, the columns of the two tables must be in the same format.

3. A complex query may use many tables.

Integrated Query:

Select count (*) from table_name where column_name = xxx

Note:

There are several pieces of qualified materials to query.

Select sum (column1) from table_name

Note:

1. Calculate the sum. The selected column must be a handful of numbers.

2. In addition, AVG () is an integrated query that calculates the mean, Max (), and min () for the maximum and minimum values.

Select column1, AVG (column2) from table_name group by column1 having AVG (column2)> xxx

Note:

1. Group by: to use column1 as a group, the average value of column2 must be used together with the keywords of AVG, sum, and other integrated queries.

2. Having: it must be used with group by as an integral constraint.

Compound Query

Select * From table_name1 where exists (select * From table_name2 where conditions)

Note:

1. The condition of Where can be another query.

2. exists indicates whether or not exists.

Select * From table_name1 where column1 in (select column1 from table_name2 where conditions)

Note:

1. In is followed by a set, indicating that column1 exists in the set.

2. Select data format must comply with column1.

Other queries

Select * From table_name1 where column1 like 'x %'

Note: Like must echo the following 'x % 'to indicate a string starting with X.

Select * From table_name1 where column1 in ('xxx', 'yyy ',..)

Note: In is followed by a set, indicating that column1 exists in the set.

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

Note:

1. Change a field to 'xxx '.

2. conditions is the condition to be met. If there is no where, all the columns of the entire table will be changed.

4. Delete materials:

Delete from table_name where conditions

Note: delete qualified materials.

Note: If the where condition is followed by a date comparison, different databases have different expressions. The details are 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 ('2017-01-01 'as date) or: Where mydate> to_date ('2017-01-01 ', 'yyyy-mm-dd ')

Write in Delphi:

Thedate = '2017-01-01 ';

Query1. SQL. Add ('select * from ABC where mydate> cast ('+ ''' + thedate + ''' + 'as date )');

If the datetime type is compared:

Where mydatetime> to_date ('2017-01-01 10:00:01 ', 'yyyy-mm-dd hh24: MI: ss ')