The second day
Preparing data
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', 'Hangzhou',66,78),(2,'pony',45,'female','Shenzhen',98,87),(3,'Ma Jingtao',55,'male','Hong Kong',56,77),(4,'Liulite',20,'female','Hunan',76,65),(5,'Liuqing',20,'male','Hunan',86,NULL),(6,'Lau Andy',57,'male','Hong Kong',99, 99),(7,'Made',22,'female','Hong Kong',99,99),(8,'Demasia',18,'male','Nanjing',56,65),(9,'k_ing',25,'male','Suzhou',100,100);
1. Basic Query: Understanding the select Field section
 field
 constant value
 Expression
 function
1.1 Field
 Single Field select name from student;  Multifield select name,address from student;  All Fields select * from student;
The as keyword can be aliased
select name as Full name from student;
distinct keyword deduplication
select count(distinct name) from student
1.2 Constant Value
select 100; select 'abcd';

+has two meanings in java
 Stitching whenever one side is a string type
 operator

In mysql + has only one meaning: calculation
select 1+1; 2
select '1'+1; 2
select 'a'+1; 1
select 'a'+'a'; 0
Mysql +compulsory behavior in calculation
Converting data types to numerical types
If the conversion is successful
So the results after conversion add up
Set to 0 if conversion is unsuccessful
Add Again
As long as one value is null, the result must be null.
select 1+null; null
1.3 Expression
select 100%98;
1.4 Common Functions
1.4.1 Single Line Function

Character function
 length select length('abcd'); select length(name) from student;  Stitching select concat('String 1','String 2'); Uppercase, lowercase select upper('abc'); select lower('ABC');  Sort by name length,Student Information select *,length(name) from student order by length(name) desc  The first occurrence of the index position returns 0 if it does not exist; select instr('Zhang Wuji Zhao Min fell in love with Zhao Min','Zhao Min');  Remove Side Spaces select trim(' a sd ad s a ');  Left Completion, Right Completion select lpad('abc',10,'*'); select rpad('abc',10,'*');  replace select replace('abcde','de','ooo');

Mathematical Functions
 Rounding select round(1.5);  Rounding up and rounding down select ceil(1.1); select floor(1.9);  truncation select truncate('1.56999',3);  Modeling aa/b*b:Symbols depend on a select mod(10,3);

Date function
select now(); current date+time select curdate(); current date select month(now()) select monthname(now()) str_to_date:Converts a character in date format to a date in a specified format select str_to_date('9/13/2004','%m/%d/%Y') date_format : Standard date format converted to string select date_format('20200101','%Y/%m/%d');

Other Functions
select database(); select version(); select user();
1.4.2 Process Control Functions
select if(10<5,'greater than','less than'); Ternary operator
case switch(variable){ case Constant 1: Execute 1 ; break; case Constant 2: Execute 2 ; break; .... dafult:implement n; } case column when Constant 1 then Result 1 when Constant 2 then Result 2 ... else Result n end Cases multiply math scores by different coefficients, depending on the region(Hangzhou 1.1 Shenzhen 1.2 Hong Kong 1.3 Other unchanged) SELECT NAME, math, address, CASE address WHEN 'Hangzhou' THEN math * 1.1 WHEN 'Shenzhen' THEN math * 1.2 WHEN 'Hong Kong' THEN math * 1.3 ELSE math END FROM student;
1.4.3 Aggregation Function
1.count Count how many rows this table has select count(*) from student; 2.max select max(math) from student; 3.min select min(math) from student 4.sum select sum(math) from student; 5.avg select avg(math) from student;
Matters needing attention:

Can aggregate functions be used together
select count(*),max(math),min(math),sum(math),avg(math) from student;

Are aggregate functions ignoring all null values All aggregate functions ignoring null values
select min(ifnull(english,0)) from student;
select sum(english) from student;
select avg(ifnull(english,0)) from student; 
What kind of parameters can you put in an aggregate function
count(*) All types have
max min numeric and character
select max(name) from student;
select min(name) from student;
sum avg numerical model
select sum(name) from student; //0
select avg(name) from student; //0 
Aggregate functions can be queried with ordinary columns Aggregate functions cannot be queried with ordinary columns ambiguous than error
select name,max(math) from student; 
Aggregate functions can be used with distinct
select count(distinct address),count(address) from student;
2. Conditional Query: where Partial Filter Conditions
select Query List from Table Name where Filter Criteria;
2.1 Conditional Expression
> < = != <>(wrong) <=>(All equals)etc.
Case:
 Case 1: Query Age>30 Student Information Aged select * from student where age>30;  Case 2: Can't find the name and address of students in Hangzhou select name,address from student where address!='Hangzhou';
2.2 Logical Expression
Logical operators: &, ,!, and, or, not
Case:
 Case 1: Query names of students with math scores greater than 80 and less than 90,Age, and math scores select name,age,math from student where math>80 && math<90;  Case 2: Query age is not 2030 Information about a student between ages or with a math score greater than 90 select * from student where age<20  age>30  math>90;
2.3 Fuzzy Query
like,between and,in,is null
Placeholder:
%: for any character
_: Represents an arbitrary character
Case:
 Case 1: Query students whose employee names contain character virtues select * from student where name like '%Virtue%';  Case 2: Query employee student information for a person whose second word is virtue select * from student where name like '_Virtue%';  Case 3: Query the employee for information about a student whose name is 3 select * from student where name like '___';  Case 4: Include in query name_Students select * from student where name like '%\_%';  Case 5: Query student information between 18 and 30 years of age select * from student where age between 18 and 30;  Case 6: Query Living in Hangzhou, Student Information in Hong Kong, Shenzhen select * from student where address in ('Hangzhou','Hong Kong','Shenzhen');  Case 7: Query English for null Name and address of the student of select * from student where english is null; select * from student where english <=> null;  Case 8: Query English for failures null Name and address of the student of select name,address from student where english is not null;
3. Sort query order by
Format:
select Query List from surface [where Filter Criteria] order by Sort List [ascdesc] asc positive sequence desc Reverse order defaults to positive order
Case:
 Case 1:Query Student Information,Require high to low math scores select * from student order by math desc  Case 2:Query Student Information,Require high to low math scores,Ordered by English when math is the same select * from student order by math desc,english asc  Case 3:Query male students for information and rank them from high to low in math select * from student where sex='male' order by math desc  Case 4:Query students'information and ask them to sort by total score from high to low select * ,math+english as Total score from student order by Total score desc  null What to do with values select ifnull(english,0) from student; select * ,math+ ifnull(english,0) as Total score from student order by Total score desc
Matters needing attention:
order by sorted items must be queried in select
order by sorts the selected results globally
4. Grouping query group by
Format:
select Grouping function,column(Request appears group by behind) from surface [where Filter Criteria] group by Group List [having Filter Criteria] [order by Sort List]
Case:
 Case 1:Query the highest math and English scores for different genders select sex,max(math),max(english) from student group by sex;  Case 2: Counting the number of students in different regions select address, count(*) from student group by address;  Case 3:Statistics of students from Hong Kong and average math scores by gender select sex,avg(math) from student where address='Hong Kong' group by sex;  Case 4:Statistics of Mathematical Average Scores of Students of Different Genders in Different Areas select address,sex,avg(math) from student group by address,sex;  Case 5:Statistical average math score for students from different regions and require average score greater than 80 select address ,avg(math) as avg_math from student group by address having avg_math >80;  Case 6: Statistics show that students from Hong Kong get average math scores by gender and require average scores greater than 80  Writing 1 select sex, avg(math) avg_math from student where address='Hong Kong' group by sex having avg_math>80  Writing 2 select address, sex, avg(math) avg_math from student group by address,sex having address='Hong Kong' and avg_math>80  Case 7: Count the number of students in each group according to the length of their names ( group by Functions are available later) select length(name), count(*) from student group by length(name)  Case 8:Statistics show average math scores for students of different genders in different regions, and require that the average math scores be sorted from large to small SELECT address, sex, avg(math) avg_math from student group by address,sex order by avg_math desc  Can grouped queries be checked group by Field Ambiguity Outside select name, sex, max(math) from student group by sex
Matters needing attention:
 Fields that appear in group by need to appear after select;
 Have filtering after grouping
where to filter before grouping  Execution Order
From>where>group by>aggregate function>having>select>order by
Writing Order
From>where>group by>select>aggregate function>having>order by  Grouping queries cannot look up fields other than group by, which can cause ambiguity
5. Paging query limit
select Field List from Table Name List [where List of conditions] [group by Grouping Fields] [having Conditions after grouping] [order by sort] limit Paging restriction
Case:
 Case 1:Show top 3 student information select * from student limit 3; select * from student limit 3,4;  Case 2:Show information about the top 3 students in math select * from student order by math desc limit 3;
Matters needing attention:
 Execution Order
From>where>group by>aggregate function>having>select>order by>limit
Writing Order
From>where>group by>select>aggregate function>having>order by>limit  page Number page Line size
select * from student limit (page1)*size,size;