introduction

All presentations are based on the table student. The student creation statement is as follows:

	create table if not exists Student(
		id int, 
		name varchar(20),
		math float,
		english float,
		chinese float
	);

I newly added

1. Single row data + full column insertion

insert into Table name values(Corresponding value of field 1, corresponding value of field 2,...);

Example:

insert into Student values(9, "root", 99, 99, 99);

After insertion, the contents of the Student table are as follows:

2. Multi row data + full column insertion

insert into Table name values
(Corresponding value of field 1, corresponding value of field 2,...),
(Corresponding value of field 1, corresponding value of field 2,...);

Data between rows needs to be separated by commas (,)

Example:

insert into Student values
(1, "Zhang San", 66, 59, 78),
(2, "Li Si", 88, 91, 57);

Contents of Student table after insertion:

3. Single row data + specified column insertion

insert into Table name(Field 1,Field 2) values(Corresponding value of field 1, corresponding value of field 2);

Example:

insert into Student(id, name, math, english) values(3, "Nicholas Zhao Si", 66, 66);

Contents of Student table after insertion:

4. Multi row data + specified column insertion

insert into Table name(Field 1,Field 2) values
(Corresponding value of field 1, corresponding value of field 2),
(Corresponding value of field 1, corresponding value of field 2);

Data between rows needs to be separated by commas (,)
Example:

insert into Student(id, name, math, english) values
(4, "Leonardo Aotian", 99, 67),
(5, "Schwarzenegger Zhang Dasan", 88, 88); 

Contents of Student table after insertion:

II query

1. Full column query

select * from Student;

Query results:

2. Specify column query

select Field 1, Field 2 from Table name;

Example:

select id, name, math, english from Student;

Query results:

3. The query field is an expression

select Field 1, Field 2, expression from Table name;

1. The expression does not contain fields
   Example:

select id, name, 1024 from Student;

Query results:

1. The expression contains a field
   Example:

select id, name, math + 10 from Student;

Query results:

3. The expression contains multiple fields
Example:

select id, name, math + english + chinese from Student;

Query results:

4. Specify alias

The alias specified for the query results is displayed as a separate column

select expression [as] alias from Table name;

as can be omitted
Example:

select id, name, math + english + chinese as total from Student;

Query results:

5. Weight removal

Use the distinct keyword to de duplicate a column of data

select distinct Listing from Table name;

Example:

select distinct math from Student;

Query result comparison:

6. Sorting

asc: in ascending order (from small to large)
desc: in descending order (from large to small)

select Field 1, Field 2, ... from indicate where condition order by Field name asc/desc;

Collation:

1. If the sorting method is not specified, it is sorted in ascending order by default;
2. The value of NULL data is smaller than any data. It is arranged at the top in ascending order and at the bottom in descending order;
3. You can sort by using the alias of the expression;
4. The priority of sorting multiple fields varies with the writing order;

Example 1:
Sort in ascending order without specifying the sort method

select math from Student order by math;

Query results:

Example 2:
Sorting of NULL values

select id, name, chinese from Student order by chinese;

Query results:

Example 3:
Sort using the alias of the expression

select *, math + chinese + english as total from Student order by total;

Query results:

Example 4:
The priority of sorting multiple fields varies with the writing order

select * from Student order by math desc, english, chinese;

First, sort in descending order according to math. If math values are the same, sort in ascending order according to english. If english values are the same, sort in ascending order according to chinese
Query results:

7. Condition query

Connect query criteria through the where keyword

(1) Comparison operator

operator	explain
>,>=,<,<=	Greater than, greater than or equal to, less than or equal to
=	Equal to, the comparison of NULL values is not safe
<=>	Equal to, the comparison of NULL values is safe
!=,<>	Not equal to
between x and y	The range matches, and the field value is greater than or equal to x and less than or equal to y
In (field value 1, field value 2,...)	Returns true if in options
is null	Judge whether it is a NULL value, and return true if it is
is not null	Judge whether it is a non NULL value. If yes, return true
like	Fuzzy matching% represents any number of characters (including 0 characters)_ Represents any character

Example 1:

select * from Student where math between 80 and 100;

Query results:

Example 2:

select * from Student 
where name in("Zhang San", "Li Si", "Wang Mazi");

Query results:

Example 3:

select * from Student where chinese is null;

Query results:

Example 4:

select * from Student where chinese is not null;

Query results:

Example 5:

select * from Student where name like "%four";

Query results:

Example 6:

select * from Student where name like "Zhang_";

Query results:

(2) Logical operator

operator	explain
and	All conditions are true and the result is true
or	All conditions are false and the result is false
not	The condition is true and the result is false; The condition is false and the result is true

Priority: not > and > or

Example 1:

select * from Student where math > 80 and english > 80;

Query results:

Example 2:

select * from Student 
where not math > 90 and english > 80;

The priority of not is higher than and, and the not operation is performed first, so the final condition is math < = 90 and English > 80;
Query results:

III modify

update indicate set Field 1 = Field 1 new value, Field 2 = Field 2 new value
where condition;

Example 1:

update Student set chinese = 44 
where name = "Nicholas Zhao Si";

Execution process:


Example 2:

update Student set math = 100, english = english + 1, 
chinese = 100 where name = "root";

Execution process:

IV delete

1. Delete specific rows of data

delete from Table name where condition;

Example:

delete from Student where math > 70;

Execution process:

2. Delete the whole table data

delete from Table name;

Example:

delete from Student;

Execution process: