I. SQL Query

The execution order of the SQL query is to filter the existing fields in the table (1, where), then aggregate grouping (2, group), then search according to the query conditions (3, select), then filter the results (4, have), then sort according to the conditions (5, order), and finally restrict the display of records (6, lim). It).
3. select... aggregate function from table name
            1,where ...
            2,group by ...
            4,having ...
            5,order by ...
            6,limit ...;

    1,order by

Sort the results of the query
Syntax: order by field name sort
Sorting
Ascending order: ASC (def a ult)
b, descending order: DESC

1,Sort the heroes of Shu Kingdom according to attack value from high to low
select * from sanguo 
where country="Shu Kingdom" 
order by gongji DESC;
2,The heroes whose names are three characters in the Wei and Shu heroes are arranged in ascending order of defense value.
select * from sanguo 
where country in("Shu Kingdom","Wei") and sex="male" and name like "___" 
order by fangyu ASC;

2. limit (always at the end of the SQL statement)

Limit the number of query records displayed

(a) Usage

1) limit n - > displays n records
                2)limit m,n  
m: Indicates that the display begins with m+1 record
N: Indicates that n bars are displayed.
limit 2,4: Displays four records, 3, 4, 5, and 6
limit 0,2: Display records 1 and 2

b. Example

1,Search for the top three attack values among Shu heroes with no name NULL Hero Name, Attack Value and Country
select name,gongji,country from sanguo
where country="Shu Kingdom" and name is not NULL
order by gongji DESC
limit 3;
2,Among the heroes of Shu,Search for hero information from penultimate to penultimate defense value
select * from sanguo
where country="Shu Kingdom" order by fangyu limit 1,3;

c. Paging queries

Display 5(n) records per page and page 4(m)
Page 1: limit 0,5 \ 1 234 5
Page 2: limit 5,5 \ 678 9 10 2-1 *5
Page 3: limit 10, 5 \ 11, 12, 13, 14, 15 3-1 *5
Page 4: limit 15, 5 \ 16 17 18 19 20 4-1 *5
            
Paging formula: limit (m-1)*n,n: Which page n: Number of records per page

3. Aggregation function

AVG (field name): average
Max (field name): maximum
Min (field name): minimum
Summation
Count: counting the number of records in that field

4. Group by (grouping-aggregation-de-duplication)

Grouping the results of queries

(a) Examples

Calculate the average attack power of all countries, show the name of the country and the average attack power.

Shu State
Shu State 400 Shu State
Shu State
Wei Guo 300 Wei Guo
Wei Guo
Wu Guo 200

Find out the top two heroes in all countries, showing the name of the country and the number of heroes.
select country,count(*) as number from sanguo
group by country
order by number DESC limit 2;

b. Attention

1) The field name after group by must be the field name after select
2) If the field name after select is inconsistent with that after group by, the field must be aggregated (aggregation function)

    5,having

Further screening of query results

Find out the top two countries with an average attack power greater than 105, showing the name of the country and the average attack power.
select country,avg(gongji) from sanguo
group by country
having avg(gongji)>105
order by avg(gongji) DESC limit 2;

Attention
1) having statements are usually used in conjunction with group by statements to filter recordsets returned by group by statements
2) The existence of having statement compensates for the inadequacy of where conditional clause that cannot be used in conjunction with aggregation function. Where operates on the actual fields in the table and has operates on the display columns generated by aggregation function.

    6,distinct

Duplicate values of fields are not displayed

1,sanguo Which countries are listed in the table?
select distinct country from sanguo;
2,Calculate how many heroes there are in the State of Wei
select count(distinct name) from sanguo
where country="Wei";

Attention
1) distinct handles all fields between distinct and from, and the values of all fields must be exactly the same before they can be de-duplicated.
2) distinct cannot aggregate any fields

7. Mathematical Operations in Query Table Recording

Oper a tors

                +   -   *    /    %

b. Example

Query shows all Heroes'attack power*10
select name,gongji*10,country from sanguo;

Two, constraint

Role

2. Classification of constraints

Default constraints

create  table  tt(sex  enum("male","female","secrecy") default  "secrecy");
desc   tt ;

(b) Non-empty constraints

create  table xx(id  int  not  null,name  varchar(20)  not  null);
desc  xx;

Nested Query (Sub-Query)

Make the inner query result the outer query condition

Format: select... from table name where field name operator (select....);

1,Show the name and attack value of the attack value less than the average attack value (in two steps)
select name,gongji from MOSHOU.sanguo
where gongji < (select avg(gongji) from MOSHOU.sanguo);
2,Find out the hero's name and attack value with the highest attack power in each country
select name,gongji from sanguo
where (country,gongji) in (select country,max(gongji) from sanguo group by country);

Appendix 1: Database Data Creation

In order to facilitate the establishment of direct copy, paste the following code, quickly create databases and tables.

create database MOSHOU;
use MOSHOU;
create table hero(
id int,
name char(15),
sex enum("male","female"),
country char(10)
)default charset=utf8;
insert into hero values
(1,"Cao Cao","male","Wei"),
(2,"Little Joe","female","Wu kingdom"),
(3,"Zhu Geliang","male","Shu Kingdom"),
(4,"army officer's hat ornaments","female","Eastern Han Dynasty"),
(5,"Zhao Zi Long","male","Shu Kingdom"),
(6,"Wei Yan","male","Shu Kingdom");
 
use MOSHOU;
create table sanguo(
id int,
name char(20),
gongji int,
fangyu tinyint unsigned,
sex enum("male","female"),
country varchar(20)
)default charset=utf8;
insert into sanguo values
(1,'Zhu Geliang',120,20,'male','Shu Kingdom'),
(2,'Sima Yi',119,25,'male','Wei'),
(3,'Guan 6 feather',188,60,'male','Shu Kingdom'),
(4,'Zhao Yun 666',200,66,'male','Wei'),
(5,'8 king of Wu in the Three Kingdoms Era',110,20,'male','Wu kingdom'),
(6,'army officer's hat ornaments',666,10,'female','Wei'),
(7,null,1000,99,'male','Shu Kingdom'),
(8,'',1005,88,'female','Shu Kingdom');

Appendix 2

        Artificial Intelligence (mysql) - Directory Summary