MySQL SQL syntax reference
- 2020-05-06 11:44:21
- OfStack
I. data definition DDL (data definition language)
Data defined language refers to the format and form of the data under the definition of the language, it is every database to establish when the first to face, all the data is divided into what table relations, what column key in the table, table and table mutual reference relations, etc., are in the beginning must be planned.
1. Form:
create table table_name(
column1 datatype [not null] [not null primary key],
column2 datatype [not null],
...);
Explanation:
datatype -- is the format of the data, see table.
nut null -- would you please allow the data to be available (no data has been filled in)?
primary key -- is the primary key of this table.
2. Change form
alter table table_name
add column column_name datatype
Add a field (there is no syntax to remove a field.
alter table table_name
add primary key (column_name)
Note: change the definition of the table to set a field as the primary key.
alter table table_name
drop primary key (column_name)
Delete the definition of the primary key.
3. Index
create index index_name on table_name (column_name)
Description: index the fields of a table to increase query speed.
4. Delete
drop table_name
drop index_name
Ii. Data form datatypes
smallint
Integer of 16 bits.
interger
Integer of 32 bits.
decimal(p,s)
p exact value and s decimal integer of size, p exact value means all Numbers (digits) size value, s is the decimal
A few digits after the dot. If not specified, p=5; s = 0.
float
32 bits of real Numbers.
double
64-bit real Numbers.
char(n)
For strings of n length, n cannot exceed 254.
varchar(n)
A string whose length is not fixed and whose maximum length is n , n cannot exceed 4000.
graphic(n)
It is the same as char(n) , except that it is in two characters double-bytes, n cannot exceed 127. This form is
Supports two character length fonts, such as Chinese text.
vargraphic(n)
A double-character string with a variable length and a maximum length of n , n cannot exceed 2000.
date
Contains years, months, and dates.
time
Contains hours, minutes, and seconds.
timestamp
Contains years, months, days, hours, minutes, seconds, thousandths of a second.
3. Data operation DML (data manipulation language)
After the data is defined, the next step is the operation of the data. The operation of data consists of four modes: add data (insert), query data (query), change data (update), , and delete data (delete)
1. Additional information:
insert into table_name (column1,column2,...)
values ( value1,value2, ...)
Description:
1. If the column system is not specified, the data will be entered in the order of columns in the form.
2. The data form of the field must match the information filled in.
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 fill in other forms through a subquery (subquery).
2. For information:
Basic query
select column1,columns2,...
from table_name
Note: list all the specific fields of table_name select *
from table_name
where column1 = xxx
[and column2 > yyy] [or column3 < > zzz]
Description:
1.'*' indicates that all fields are listed.
2. After where is the conditional form, listing the information that meets the conditions.
select column1,column2
from table_name
order by column2 [desc]
Instruction: order by is specified to sort by a column, [desc] is specified to rank from large to small, or, if not specified, from small to large
Arrange
Composite query
A composite query is a query that does not contain a single table, but combines more than one
Tables are the only way to get results.
select *
from table1,table2
where table1.colum1=table2.column1
Description:
1. Query the data in the two tables where column1 values are the same.
2. Of course, the fields in which the two tables are compared must have the same data form.
3. A complex query may involve many tables.
Integrated queries:
select count (*)
from table_name
where column_name = xxx
Description:
There are several requests for qualified information.
select sum(column1)
from table_name
Description:
1. Calculate the sum. The selected field must be a countable number.
2. In addition, avg() is an integrated query to calculate the average, max(), min(), and min().
select column1,avg(column2)
from table_name
group by column1
having avg(column2) > xxx
Description:
1.group by: takes column1 as a group to calculate the average of column2 and avg, sum,
Use together.
2.having : must be used in conjunction with group by as an integral limitation.
Compound query
select *
from table_name1
where exists (
select *
from table_name2
where conditions )
Description:
1.where conditions can be another query.
2.exists here means existence or absence.
select *
from table_name1
where column1 in (
select column1
from table_name2
where conditions )
Description:
1. in is followed by a set, which means column1 is in the set.
2. The data from select must conform to column1.
Other queries
select *
from table_name1
where column1 like 'x%'
Note: like must correspond to 'x%' to indicate a string beginning with x.
select *
from table_name1
where column1 in ('xxx','yyy',..)
Note: in is followed by a set, indicating that column1 is in the set.
select *
from table_name1
where column1 between xx and yy
Note: between indicates that column1 has a value between xx and yy .
3. Change of information:
update table_name
set column1='xxx'
where conditoins
Description:
1. Change the value of a field to 'xxx'.
2.conditions is the condition to be met. Without where , the entire table field will be changed.
4. Delete data:
delete from table_name
where conditions
Note: delete qualified data.
Note: different databases have different expressions for the where condition if it contains a comparison of dates. The details are as follows:
(1) if it is access database, it is where mydate> #2000-01-01#
(2) if it is oracle database, it is where mydate> cast('2000-01-01' as date)
Or: where mydate> to_date('2000-01-01','yyyy-mm-dd')
In delphi it is written:
thedate='2000-01-01';
query1.sql.add('select * from abc where mydate > cast('+''''+thedate+''''+' as date)');
If you compare date and time,
where mydatetime > to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss');
Data defined language refers to the format and form of the data under the definition of the language, it is every database to establish when the first to face, all the data is divided into what table relations, what column key in the table, table and table mutual reference relations, etc., are in the beginning must be planned.
1. Form:
create table table_name(
column1 datatype [not null] [not null primary key],
column2 datatype [not null],
...);
Explanation:
datatype -- is the format of the data, see table.
nut null -- would you please allow the data to be available (no data has been filled in)?
primary key -- is the primary key of this table.
2. Change form
alter table table_name
add column column_name datatype
Add a field (there is no syntax to remove a field.
alter table table_name
add primary key (column_name)
Note: change the definition of the table to set a field as the primary key.
alter table table_name
drop primary key (column_name)
Delete the definition of the primary key.
3. Index
create index index_name on table_name (column_name)
Description: index the fields of a table to increase query speed.
4. Delete
drop table_name
drop index_name
Ii. Data form datatypes
smallint
Integer of 16 bits.
interger
Integer of 32 bits.
decimal(p,s)
p exact value and s decimal integer of size, p exact value means all Numbers (digits) size value, s is the decimal
A few digits after the dot. If not specified, p=5; s = 0.
float
32 bits of real Numbers.
double
64-bit real Numbers.
char(n)
For strings of n length, n cannot exceed 254.
varchar(n)
A string whose length is not fixed and whose maximum length is n , n cannot exceed 4000.
graphic(n)
It is the same as char(n) , except that it is in two characters double-bytes, n cannot exceed 127. This form is
Supports two character length fonts, such as Chinese text.
vargraphic(n)
A double-character string with a variable length and a maximum length of n , n cannot exceed 2000.
date
Contains years, months, and dates.
time
Contains hours, minutes, and seconds.
timestamp
Contains years, months, days, hours, minutes, seconds, thousandths of a second.
3. Data operation DML (data manipulation language)
After the data is defined, the next step is the operation of the data. The operation of data consists of four modes: add data (insert), query data (query), change data (update), , and delete data (delete)
1. Additional information:
insert into table_name (column1,column2,...)
values ( value1,value2, ...)
Description:
1. If the column system is not specified, the data will be entered in the order of columns in the form.
2. The data form of the field must match the information filled in.
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 fill in other forms through a subquery (subquery).
2. For information:
Basic query
select column1,columns2,...
from table_name
Note: list all the specific fields of table_name select *
from table_name
where column1 = xxx
[and column2 > yyy] [or column3 < > zzz]
Description:
1.'*' indicates that all fields are listed.
2. After where is the conditional form, listing the information that meets the conditions.
select column1,column2
from table_name
order by column2 [desc]
Instruction: order by is specified to sort by a column, [desc] is specified to rank from large to small, or, if not specified, from small to large
Arrange
Composite query
A composite query is a query that does not contain a single table, but combines more than one
Tables are the only way to get results.
select *
from table1,table2
where table1.colum1=table2.column1
Description:
1. Query the data in the two tables where column1 values are the same.
2. Of course, the fields in which the two tables are compared must have the same data form.
3. A complex query may involve many tables.
Integrated queries:
select count (*)
from table_name
where column_name = xxx
Description:
There are several requests for qualified information.
select sum(column1)
from table_name
Description:
1. Calculate the sum. The selected field must be a countable number.
2. In addition, avg() is an integrated query to calculate the average, max(), min(), and min().
select column1,avg(column2)
from table_name
group by column1
having avg(column2) > xxx
Description:
1.group by: takes column1 as a group to calculate the average of column2 and avg, sum,
Use together.
2.having : must be used in conjunction with group by as an integral limitation.
Compound query
select *
from table_name1
where exists (
select *
from table_name2
where conditions )
Description:
1.where conditions can be another query.
2.exists here means existence or absence.
select *
from table_name1
where column1 in (
select column1
from table_name2
where conditions )
Description:
1. in is followed by a set, which means column1 is in the set.
2. The data from select must conform to column1.
Other queries
select *
from table_name1
where column1 like 'x%'
Note: like must correspond to 'x%' to indicate a string beginning with x.
select *
from table_name1
where column1 in ('xxx','yyy',..)
Note: in is followed by a set, indicating that column1 is in the set.
select *
from table_name1
where column1 between xx and yy
Note: between indicates that column1 has a value between xx and yy .
3. Change of information:
update table_name
set column1='xxx'
where conditoins
Description:
1. Change the value of a field to 'xxx'.
2.conditions is the condition to be met. Without where , the entire table field will be changed.
4. Delete data:
delete from table_name
where conditions
Note: delete qualified data.
Note: different databases have different expressions for the where condition if it contains a comparison of dates. The details are as follows:
(1) if it is access database, it is where mydate> #2000-01-01#
(2) if it is oracle database, it is where mydate> cast('2000-01-01' as date)
Or: where mydate> to_date('2000-01-01','yyyy-mm-dd')
In delphi it is written:
thedate='2000-01-01';
query1.sql.add('select * from abc where mydate > cast('+''''+thedate+''''+' as date)');
If you compare date and time,
where mydatetime > to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss');