MySQL database SQL syntax reference _ MySQL

MySQL database SQL syntax reference bitsCN.com I. data definition language (ddl)
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.
Alter 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
2. 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 from small to large.
Arrange
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'); bitsCN.com