Records in MySQL's DQL query table

1. table creation

CREATE TABLE student (
 id int, -- number
 name varchar(20), -- full name
 age int, -- Age
 sex varchar(5), -- Gender
 address varchar(100), -- address
 math int, -- mathematics
 english int -- English
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'Jack Ma',55,'male','State',66,78),(2,'pony ',45,'female','Shenzhen',98,87),(3,'Majingtao',55,'male','Hong Kong',56,77),(4,'Liuyan',20,
,20,'female','Hunan',76,65),(5,'Liuqing',20,'male','Hunan',86,NULL),(6,'Lau Andy',57,'male','Hong Kong',99,99),(7,'Mader',22,'female','Hong Kong',99,99),(8,'Demacia',18,'male','Nanjing',56,65);

2. grammar

Syntax:
select
Field list
from
Table name list
where
Condition list
group by
Group fields
having
Conditions after grouping
order by
sort
limit
Paging qualification

3.select basic query

Basic query

1. Query of multiple fields
   select field name 1, field name 2... from table name;

• be careful:
• If you query all fields, you can use * instead of the field list.

1. Remove duplicates:

• distinct

1. Calculation column

• Generally, four operations can be used to calculate the values of some columns. (generally, only numerical calculation will be performed)
• Ifnull (expression 1, expression 2): the calculation results of operations involving null are null
• Expression 1: which field needs to be judged to be null
• The replacement value if the field is null.

1. Alias:

• as:

select * from Table name;
select name,age from student;
select address from student;
select distinct address from student;    #Remove duplicate
select distinct address ,name from student; #You must have duplicate address and name to perform de duplication
select name,math,english,math+english  from student;#Calculate the sum of mathematics and English, but the original value will not be changed. If NULL is involved, the operation result will be null
select name,math,english,math+ifnull(english,0)  from student; #Treating null as 0 does not change the original value, but the column name is ugly
select name,math,english,math+ifnull(english,0) as Total score  from student; #Aliasing does not change the original value,
select name ,math as mathematics,english as English ,math+ifnull(english,0) as Total score  from student; #Alias

4.where condition query

Condition query

1. where clause followed by condition
2. operator

• ,< ,<= ,>= ,= ,<>,!=

• and or&&

• BETWEEN...AND

• Or or||

• In (set)

• IS NULL , IS NOT NULL

• LIKE: fuzzy query

• Placeholder:

• _: Single arbitrary character

• %: multiple arbitrary characters

SELECT * FROM student WHERE age > 20;-- Query age > 20
SELECT * FROM student WHERE age >= 20;-- Query age ≥ 20
SELECT * FROM student WHERE age = 20;-- Query age equals 20 years old #Note not = =, yes=
SELECT * FROM student WHERE age != 20;-- Query age is not equal to 20 years old
SELECT * FROM student WHERE age <> 20;-- Like the above sentence functions, it is also an unequal sign; Query age is not equal to 20 years old
SELECT * FROM student WHERE age >= 20 &&  age <=30;-- Query age ≥ 20 ≤ 30
SELECT * FROM student WHERE age >= 20 AND  age <=30;-- The same function as the above statement
SELECT * FROM student WHERE age BETWEEN 20 AND 30-- The same function as the above statement
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25-- Query the information of 22, 18 and 25 years old; Output if any one of them exists
 SELECT * FROM student WHERE age = 22 || age = 18 || age = 25;-- Query the information of 22, 18 and 25 years old; Output if any one of them exists
SELECT * FROM student WHERE age IN (22,18,25);-- Query the information of 22, 18 and 25 years old; Output if any one of them exists
SELECT * FROM student WHERE english = NULL; -- null Value cannot be used = (!=) judge
SELECT * FROM student WHERE english IS NULL;
** SELECT * FROM student WHERE english = NULL; --  This statement is incorrect;  null Value cannot be used = (!=) judge;The following statement is required
SELECT * FROM student WHERE english  IS NOT NULL;	-- Query English score is not null	
SELECT * FROM student WHERE NAME LIKE 'horse%';-- What are the surnames of Ma? 
SELECT * FROM student WHERE NAME LIKE "_turn%";-- The second word of the query name is the person
SELECT * FROM student WHERE NAME LIKE '___';-- Query the person whose name is 3 characters; These are three underscores
SELECT * FROM student WHERE NAME LIKE '%virtue%';-- Query the person whose name contains de

5.order sort query

Sort query

• Syntax: order by clause
• order by sort field 1 sort by 1, sort field 2 sort by 2
• Sort by:
• ASC: ascending, default.
• DESC: descending.
• be careful:
• If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same.

select * from student order by math ;#In ascending order by default; Note that English is in descending order when math scores are the same
select * from student order by math desc; #In descending order; Note that English is in descending order when math scores are the same
select * from student order by math asc; #In ascending order; Note that English is in descending order when math scores are the same
select * from student order by math asc,english asc ;#In ascending order; Note that English is in ascending order when math scores are the same
select * from student order by math asc,english desc;#In ascending order; Note that English is in descending order when math scores are the same
select * from student order by math asc,english; #In ascending order; Note that English is in ascending order when math scores are the same

6. aggregate query

Aggregate function: performs vertical calculation on a column of data as a whole.

1. Count: count
2. Generally, non empty columns are selected: primary key
3. count(*)
4. max: calculate the maximum value
5. min: calculate the minimum value
6. sum: Calculation and
7. avg: calculate the average value
   *Note: the calculation of aggregate function excludes null values.
   Solution:
8. Select non empty columns for calculation
9. IFNULL function

select count(name) from student;
select count(english) from student;
select count(ifnull(english,0)) from student;
select count(*) from student;  #As long as one is not null, it can be counted
select max(math)  from student;
select min(math)  from student;
select sum(math)  from student;
select avg(math)  from student;

7.group and having group query

Group query:

1. Syntax: group by group field;
2. be careful:
3. Fields queried after grouping: grouping fields and aggregate functions (Note: only these two types can be used, and no others can be written)
4. What is the difference between where and having?
5. where is qualified before grouping. If the conditions are not met, it will not participate in grouping. having is qualified after grouping. If the result is not satisfied, it will not be queried
6. where cannot be followed by an aggregate function. having can judge the aggregate function.

SELECT sex , AVG(math) FROM student GROUP BY sex;-- Grouped by sex. Query the average scores of male and female students respectively
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;-- Grouped by sex. Query the average scores of male and female students respectively,Number of persons
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;--  Grouped by sex. Query the average scores of male and female students respectively,Number of people required: those whose scores are lower than 70 will not participate in the grouping
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;--  Grouped by sex. Query the average scores of male and female students respectively,Number of people required: those whose scores are lower than 70 will not participate in the grouping,After grouping. The number of people should be more than 2
SELECT sex , AVG(math),COUNT(id) Number of persons FROM student WHERE math > 70 GROUP BY sex HAVING Number of persons > 2;

8.limit paging query

Paging query
1. syntax: the index starting from limit, and the number of queries per page;
2. formula: starting index = (current page number - 1) * number of entries displayed per page
– 3 records per page
3.limit is a MySQL "dialect"
4.LIMIT accepts one or two digital parameters;
For example: SELECT * FROM table LIMIT 5// Retrieve the first 5 record lines. In other words, LIMIT n is equivalent to LIMIT 0,n,

SELECT * FROM student LIMIT 0,3; -- Page 1	;Check from 1 to 3
SELECT * FROM student LIMIT 3,3; -- Page 2 ;From 3 to 6
 select *from student limit 2,5; --       From 3 to 7
SELECT * FROM student LIMIT 6,3; -- Page 3 ;Check from 7 to 9