sql classification
DDL (data definition language) database definition language
Some sql used when creating table views, such as CREATE, ALTER, DROP, etc. DDL is mainly used to define or modify database, table and view structures
DML (data manipulation language):
These are the SELECT, UPDATE, INSERT and DELETE that we most often use. It is mainly used to perform some operations on the data of the database.
DCL (Data Control Language):
It is a statement used to set or change database user or role permissions, including (grant,deny,revoke, etc.) statements. This is rarely used.

Create a sample table
DDL statement:

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'full name',
  `sex` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'Gender',
  `nickname` varchar(200) DEFAULT NULL COMMENT 'nickname',
  `stars` varchar(100) DEFAULT NULL COMMENT 'Stars',
  `position` varchar(100) DEFAULT NULL COMMENT 'position',
  `ranking` int DEFAULT NULL COMMENT 'ranking',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Import test data

INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(1, 'Li gung', 'male', 'Flying saint', 'Earth walking star', 'The fourth of the seventeen general schools of the infantry army and the commander of Javelin battalion, and the Assistant General of six passes', 65);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(2, 'Xiang Chong', 'male', 'Eight armed Nezha', 'Earth flying star', 'The third of the seventeen general schools of the infantry army and the commander of the throwing dagger battalion, and the Assistant General of six passes', 64);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(3, 'Fan Rui', 'male', 'a fiend in human shape', 'Earth natural star', 'The head of the seventeen general schools of the infantry army and the commander of the charm camp, and the Assistant General of the six passes', 61);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(4, 'Gongsun Sheng', 'male', 'Ru Yunlong', 'Sky idle star', 'Deputy military division in charge of Secrets', 4);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(5, 'Ruan Xiaoer', 'male', 'Site Taisui', 'Sky sword star', 'The fourth of the eight sea Jiaos of the Navy and the commander of the ship camp, the Deputy General of the southeast water stronghold', 27);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(6, 'Ruan Xiaowu', 'male', 'Short-lived Erlang', 'Sin star', 'The fifth of the eight sea Jiaos of the Navy and the commander of the crossbow camp is the captain of the northeast water stronghold', 29);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(7, 'Ruan Xiaoqi', 'male', 'Living hell', 'Tianbaixing', 'The sixth of the eight sea Jiaos of the Navy and the commander of the clipper battalion, the chief General of the northwest water stronghold', 31);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(8, 'Hu Sanniang', 'female', 'Yizhangqing', 'Earth comet', 'Two of the four schools of military law execution are under the control of both military teams', 59);
INSERT INTO test.`user`
(id, name, sex, nickname, stars, `position`, ranking)
VALUES(9, 'Female night fork', 'female', 'Sun erniang', 'Dizhuang star', 'The seventh of the eight ambassadors of the interior department and the deputy head of Xishan Hotel', 103);

View table data

id	name	sex	nickname	stars	position	ranking
1	Li gung	male	Flying saint	Earth walking star	The fourth of the seventeen general schools of the infantry army and the commander of Javelin battalion, and the Assistant General of six passes	65
2	Xiang Chong	male	Eight armed Nezha	Earth flying star	The third of the seventeen general schools of the infantry army and the commander of the throwing dagger battalion, and the Assistant General of six passes	64
3	Fan Rui	male	a fiend in human shape	Earth natural star	The head of the seventeen general schools of the infantry army and the commander of the charm camp, and the Assistant General of the six passes	61
4	Gongsun Sheng	male	Ru Yunlong	Sky idle star	Deputy military division in charge of Secrets	4
5	Ruan Xiaoer	male	Site Taisui	Sky sword star	The fourth of the eight sea Jiaos of the Navy and the commander of the ship camp, the Deputy General of the southeast water stronghold	27
6	Ruan Xiaowu	male	Short-lived Erlang	Sin star	The fifth of the eight sea Jiaos of the Navy and the commander of the crossbow camp is the captain of the northeast water stronghold	29
7	Ruan Xiaoqi	male	Living hell	Tianbaixing	The sixth of the eight sea Jiaos of the Navy and the commander of the clipper battalion, the chief General of the northwest water stronghold	31
8	Hu Sanniang	female	Yizhangqing	Earth comet	Two of the four schools of military law execution are under the control of both military teams	59
9	Female night fork	female	Sun erniang	Dizhuang star	The seventh of the eight ambassadors of the interior department and the deputy head of Xishan Hotel	103

DML statements are mainly used in the work

1.INSERT INTO statement

The INSERT INTO statement is used to insert a new record into a table

1.1 syntax 1

There is no need to specify the column name to insert data, just provide the inserted value:
as

INSERT INTO test.`user`
VALUES(10, 'Zhu Wu', 'male', 'Shenji military division', 'Dikuixing', 'Counselor of military division in charge of secrets and commander of military law execution camp', 37);

The data after execution is

Inserting data without specifying columns requires that the data of each column be listed. That is, if the table has seven columns, the inserted data should list all the values of the seven columns and correspond one by one in the order of the columns

1.2 syntax 2

Specify the column name and the inserted value:
as

INSERT INTO test.`user` ( name, sex, nickname, ranking, stars)
VALUES('Liu Tang', 'male', 'Red haired ghost', 21, 'Celestial alien');

The data after execution is

The data insertion method of the specified column requires that each column value of the inserted data corresponds to the specified column order quantity one by one. If there is no specified column, the corresponding column of the inserted data is the default value, and if there is no default value, it is NULL

2.SELECT statement:

The SELECT statement is used to SELECT data from a database. The results are stored in a result table called a result set.
grammar

2.1 query specified column

SELECT column_name,column_name
FROM table_name;
as

select  name,nickname from user;

The result is

2.2 query all columns

SELECT * FROM table_name;
as

select  * from user;

The result is

3.SELECT DISTINCT syntax

In a table, a column may contain multiple duplicate values, and sometimes you may want to list only different values.
The DISTINCT keyword is used to return a unique different value.
SELECT DISTINCT column_name,column_name
FROM table_name;
as

SELECT DISTINCT sex from user;

The result is

4.WHERE clause

The WHERE clause is used to extract records that meet the specified conditions.
The WHERE clause can be used in insert, delete, select and update statements to filter out data that meets the conditions
SELECT column_name,column_name
FROM table_name
WHERE conditional expression;
as

--Gender in query table is'female'Everyone
select * from user where sex='female';

Output results

4.1 common conditional operators in where clause

=Equals
< > or= Not equal to
is null or empty
is not null is not empty
>Greater than
< less than
>=Greater than or equal to
< = less than or equal to
between interval range filtering
in multiple value filtering
like fuzzy query

5. Relational operators

Relational operators are used to describe the relationship between multiple conditions

5.1 AND operator

If both the left AND right conditional operations of AND are true, the condition is true. If one of the left AND right conditions is not true, the whole is not true
as

-- Data with female gender and ranking less than 100 in the query table
select * from user where sex='female' and ranking<100;

The output result is

5.1 OR operator

If either of the left and right OR conditions is true, the overall condition is true
as

-- Data with male gender or ranking greater than 100 in the query table
select * from user where sex='male'  or ranking>100;

The output result is

6.ORDER BY syntax

order by can sort the query result set
order by syntax
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
Order by can be followed by one or more columns, sorted from left to right according to the field order, followed by ASC or DESC. It is sorted in ascending order by default, that is, ASC by default; DESC indicates reverse order
as

-- Sort by ranking from small to large
select * from user order by ranking;
select * from user order by ranking asc;

Both sql results are the same
The output result is

-- Sort by rank from large to small
select * from user order by ranking desc;

Output results

order by multiple columns
as

-- Sort by sex in ascending order and then in reverse order
select * from user order by sex asc,ranking desc;

Output results

7.UPDATE statement (! Pay attention to where condition filtering)

The UPDATE statement is used to UPDATE records in a table.
UPDATE syntax
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
The UPDATE statement will filter out all qualified data according to the conditions of the where clause, and then UPDATE the corresponding fields of the filtered data
as

-- Find the person whose gender is female and ranking is 103. Update the name to sun erniang and nickname to mother Yasha
update  user  set  name='Sun erniang',nickname='Female night fork' where sex='female' and  ranking=103;

The updated content is

7.DELETE statement (! Pay attention to where condition filtering)

The DELETE statement is used to DELETE rows in a table.
DELETE syntax
DELETE FROM table_name
WHERE some_column=some_value;
The DELETE statement will filter out all qualified data according to the conditions of the where clause, and then DELETE the filtered data; Therefore, unless you want to DELETE the data of the whole table, you must remember to add a where clause for data filtering
as

-- Find the data named Liu Tang and delete it
delete from user where name='Liu Tang';

The deleted content is