1. The statements of the database can be divided into the following four categories:

(1) DDL: Data Define Language, defines data

(2) DML: Data Manipulate Language, to manipulate data

(3) DQL: Data Query Language ,to query data

(4) DCL: Data Control Language ,controls user permissions

2. Query the database list

show databases;

3. Create the database

create database database name;

4. Specify the database (all subsequent operations are performed under this library)

use database name;

5. Create table statement

create table table name (

Field 1 Name Data Type,

Field 2 Name Data Type

);

6. Query all tables under the library

(1) show tables; (the premise of execution is that there is a specified database)

(2) show tables from database name

7. Query all field properties of the table

(1) show columns from table name;

(2) desc table name;

(3) SELECT

column_name AS ‘column name’,

data_type AS ‘data type’,

column_default AS ‘default value’,

column_comment AS ‘comment’

FROM

information_schema.columns

WHERE

table_name=’table name’ and table_schema = ‘library name’;

8. Modify the table structure and add fields

alter table table name add field name data type;

9. Modify the table structure and add field B after field A

alter table table name add field B name data type after field A name;

10. Modify the table structure, only modify the field name or only modify the field attributes or both

alter table table name change field name to be modified modified field name modified field attribute;

11. Create a table to set a non-null constraint statement (if you do not insert a non-null field, an error will be prompted)

create table table name (

field 1 name data type not null,

Field 2 Name Data Type

);

12. Create a table to set the default value (if the default value is not set, all fields are empty (null) by default)

create table table name (

Field 1 name Data type default Default value,

Field 2 Name Data Type

);

13. Modify the default value of the table field

ALTER TABLE table name ALTER field name SET DEFAULT default value

14. Delete Field Defaults

(1) ALTER TABLE table name ALTER field name SET DEFAULT null; (note that when the field is not empty, the default value of null cannot be set)

(2) ALTER TABLE table name ALTER field name DROP DEFAULT;

15. Modify table field constraints (can be multiple)

alter table table name modify original field name original field attribute constraint 1 constraint 2 …;

16. Non-null fields are set to allow nulls

alter table table name modify original field name original field attribute null;

17. Add the unique attribute (when setting the unique value constraint of multiple fields, the uniqueness is verified by these fields as a whole)

CREATE TABLE tablename(

field name 1 data type 1,

field name 2 data type 2,

CONSTRAINT constraint name UNIQUE (field1, field2, …)

);

ALTER TABLE table name

ADD CONSTRAINT constraint name UNIQUE (field1,field2…)

18. Delete the unique attribute (if there is no constraint set when creating UNIQUE, you need to query the default constraint name of the system through the script to operate)

ALTER TABLE table name DROP INDEX constraint name

19. Set the primary key (it can be one field as the primary key, or multiple fields as the joint primary key)

create table table name (

field 1 name data type not null,

Field 2 Name Data Type

primary key (primary key field 1 name, primary key field 2 name…)

);

ALTER TABLE table name ADD PRIMARY KEY (primary key field 1 name, primary key field 2 name…);

20. Set Notes

(1) Create a note

create table table name (

Field 1 name Data type comment Remarks,

Field 2 Name Data Type

);

(2) Modify the remarks

alter table table name modify original field name original field attribute comment remark information;

21. Add a foreign key constraint (note that the foreign key dependent field must be the primary key of the table, the types of the two fields of the foreign key must be exactly the same, and the coded character set must be the same)

CREATE TABLE tablename(

field one field type,

field two field type,

CONSTRAINT foreign key constraint name FOREIGN KEY (foreign key field) REFERENCES foreign key dependent table (foreign key dependent field)

)

ALTER TABLE table name

ADD CONSTRAINT foreign key constraint name FOREIGN KEY (foreign key field) REFERENCES foreign key dependent table (foreign key dependent field);

ALTER TABLE table name DROP foreign key foreign key constraint name;

22. Add Index

CREATE INDEX index name ON table name (index field 1, index field 2…)

1. Add data

INSERT INTO tablename(field1,field2,field3,…) VALUES(value1,value2,value3,…);

Add multiple sets of data

INSERT INTO tablename(field1, field2, field3,…) VALUES (value1, value2, value3,…), (value1, value2, value3,…),( value1, value2, value3,…);

Insert query result set

INSERT INTO table1 (field1, field2,…) SELECT (field1, field2,…) FROM table2;

2. Update data

UPDATE table name SET field1=value1, field2=value2,… WHERE condition;

3. Delete data

delete from table name where condition;

1. Simple single table query

1.where condition is actually a conditional expression

2. Common operators for expressions = , <> (not equal, !=), >, <, >=, <=

3.in specifies multiple values, followed by an array (arrays are in parentheses, where multiple sets of values ​​are separated by commas)

where field in (value1, value2)

Example: select * from student where id in (1,2);

in corresponds to an array, you can nest query sql subsets

Example: select * from student where id in (select id from test);

4.between

The BETWEEN operator selects a value in the data range between two values. Can represent numbers and times, including boundary values

select * from student where id between 5 and 7;

select * from schedule where date between ‘2020=09-21 23:00:00’ and ‘2020-09-22 18:00:00’;

5.and operator (and the logical relationship, multiple simultaneous conditions are connected with and)

Example: select * from student where name = ‘lifang’ or sex = ‘M’;

6.or operator (or logical relationship, connected with or)

Example: select * from student where id =1 or id =2;

7. not (opposite)

Example: select * from student where id not in (1,2);

select * from schedule where date not between ‘2020=09-21 23:00:00’ and ‘2020-09-22 18:00:00’;

8.like fuzzy query

(1) % replaces 0 or more characters

like %keyword% all result sets that contain the keyword

like %keyword The keyword is at the end

like keyword % keyword first

(2) _ replaces one character (Chinese takes up two bytes)

_keyword means that the keyword is in the second position

9.null as a condition

is null

is not null

10. Aliases

Specify column header information by as

11.order by

Sort key: asc ascending, desc descending

If order by field 1 sort key 1, field 2 sort key 2

It means field 1 is the primary sort field

12. limit

limit a,b a represents the serial number of the starting data, b represents how many pieces of data are displayed from the starting data

For example: limit 0,4 display the first four data limit 2,3 display the third to fifth data

limit b is shorthand for limit 0,b

13. distinct

The DISTINCT keyword is used to return uniquely distinct values

2. Joint query

Left join The left table of left join is used as the main table (the fields of the main table are fully displayed, and the auxiliary table is supplemented as extended information (may be null))

right join takes the table on the right side of the right join as the main table (the fields of the main table are fully displayed, and the auxiliary table is supplemented as extended information (may be null))

inner join inline two tables to take the intersection

Joint query of two tables

select A.field,B.field from A left join B on A.Associated field=B.Associated field where condition;

select A.field,B.field from A inner join B on A.related field=B.related field where condition;

select A.field,B.field from A right join B on A.Associated field=B.Associated field where condition;

Multi-table joint query

left join b on b. Associated field = a. Associated field You can judge who the corresponding main table is through the two tables in on, you don’t need to write them together

Example:

select

route.RouteId ,

a1.airportName as DepartureAirportName,

c1.CityName as DepartureCityName,

country1.CountryName as DepartureCountryName,

a2.AirportName as ArrivalAirportName,

c2.CityName as ArrivalCityName,

country2.CountryName as ArrivalCountryName

from

route

left join airport as a1 on a1.IATACode=route.DepartureAirportIATA

left join airport as a2 on a2.IATACode = route.ArrivalAirportIATA

left join city as c1 on a1.CityCode = c1.CityCode

left join city as c2 on c2.cityCode = a2.CityCode

left join country as country1 on country1.CountryCode = c1.CountryCode

left join country as country2 on country2.CountryCode = c2.CountryCode

left join city as c2 on c2.cityCode = a2.CityCode its main table is a2, but they are not written together