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