Summarized from database principle and application (MySQL Edition) Tsinghua University Press

#In syntax, [] indicates that this part is optional, < > indicates that this part is required, and is not written when writing specific commands, # as a single line comment, / * * / multiple lines

1, Data definition

1. Create database

CREATE DATABASE Database name;

2. Select database

USE Database name;

3. Delete database

DROP DATABASE Database name;

4. Create table

CREATE TABLE Table name

(<Listing> <data type> [DEFAULT<Default value>]  /*DEFAULT Set the default value for the specified column, that is, the user does not set the default value for the column, and the system 
                                      The default value set for the value of this column is automatically*/
  [,...]
);

5. Create tables using subqueries

CREATE TABLE <Table name>
SELECT sentence;

6. Modify table structure

#The following statements can only modify one column. If you want to modify multiple columns, you should reuse the statements
#Add new column 
ALTER TABLE <Table name>
ADD [COLUMN] <Listing> <data type> [DEFAULT <Default value>];
#Modify existing columns
ALTER TABLE <Table name>
MODIFY [COLUMN] <Listing> <data type> [DEFAULT <Default value>];
#Delete column
ALTER TABLE <Table name>
DROP [COLUMN] <Listing> 

7. Truncate and delete tables

#Truncate table (delete data only)
TRUNCATE TABLE <Table name>;
#Delete table
DROP TABLE <Table name>;

2, Data query

1. Basic query

SELECT [ALL|DISTINCT] * | <Column name or column name expression sequence>   #ALL indicates that ALL records are output, and DISTINCT outputs no duplicate records
FROM <Table name or view name>                               # *Select all fields in the table
[WHERE <Conditional expression>]
[GROPE BY <Group column name 1 ][,<Group column name 2 >]...
   [HAVING<Group conditional expression>]
[ORDER BY <Sort column name 1 [ASC | DESC]> [,<Sort column name 2 [ASC | DESC]>]...];/*ASC Ascending (default ascending) DESC 
                                                                      Descending order*/

2. Connection query

(1) The tables to be joined should be placed in the FROM clause, and the table names should be separated by commas

(2) For convenience, alias the table in the FROM clause and separate it directly with spaces. Only the table alias can be used below

(3) The join condition is placed in the WHERE clause

(4) If there are the same column names in multiple tables, these columns can be distinguished by their names     Alias. Column name

Left | right external connection

FROM Table 1 LEFT OUTER JOIN Table 2 ON Table 1.column = Table 2.column
             #The results show all records in Table 1 and the same records in Table 2 as column 1
FROM Table 1 RIGHT OUTER JOIN Table 2 ON Table 1.column = Table 2.column
             #The results show all records in Table 2 and the same records in Table 1 as column 2

3. Merge query results

When the results of two SELECT queries are completely consistent, you can merge the two queries with the operator UNION

SELECT Statement 1
UNION [ALL]
SELECT Statement 2;

3, Data maintenance

1. Insert data

INSERT INTO [(Column name 1[,Column name 2...])]
   VALUES (Value 1[,Value 2...])
[,(Value 1[,Value 2...]),...,(Value 1[,Value 2...])];

2. Update data

UPDATE Table name
SET Listing = value[,Listing = value,...]
[WHERE <condition>];

3. Delete data

DELETE FROM Table name
[WHERE<condition>];

4, Indexes and views

1. Create index

When a user establishes a primary key or unique constraint on a table, the system will automatically create a unique index

Manual creation:

CREATE [UNIQUE] INDEX Index name ON Table name(Listing[,Listing]...)
  [ON Table name(Listing[,Listing]...)];   #Index a table by a column

2. View index

SHOW INDEX FROM <Table name>;

3. Delete index

DROP INDEX Index name ON Table name;

4. Create a view

CREATE VIEW View name [(alias[,alias]...)]
AS                         #Alias: the column name defined for the column generated by the view
SELECT sentence                
[WITH CHECK OPTION];       #The data inserted or modified by WITH CHECK OPTION must meet the constraints defined by the view

5. Modify view

CREATE OR REPLACE VIEW View name
AS                         
SELECT sentence                
[WITH CHECK OPTION];           #OR REPLACE if the created view already exists, the system will rebuild the view

#Method 2

ALTER VIEW View name[(alias[,alias]...)]
AS                         
SELECT sentence                
[WITH CHECK OPTION]; 

6. Delete view

DROP VIEW View name [,View name,...];

appendix

1. Data type

CHAR (L): fixed length string. If it is less than l, the space will be filled automatically

VACHAR (L): variable length string. If it is less than l, it will be stored according to the actual length

INT (four bytes by default): integer

DECIMAL(p,s): the maximum length of all numerical data, p, s, the number of digits after the decimal point

DATE: DATE   YYY-MM-DD

TIME: TIME     HH:MM:SS

DATETIME: date time    YYY-MM-DD   HH:MM:SS

2.WHERE   Comparison operator

<,<=,>,>=,<>|!=,=

NOT,AND,OR   # Not, and, or

[NOT]BETWEEN...AND...

[NOT]LIKE    # String fuzzy judgment,% represents zero or any number of characters_ Represents any character

IS [NOT] NULL

[not] in (< set >)

3. Column constraints

PRIMARY KEY: PRIMARY KEY

UNIQUE: UNIQUE constraint

NOT NULL: NOT NULL

DEFAULT: DEFAULT constraint

CHECK: CHECK constraints

FOREIGN: FOREIGN key constraint