Next day (DQL Data Query Language)

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;
-- Multi-field
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 de-duplication

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 a-a/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('2020-01-01','%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 20-30 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 [asc|desc] 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 (page-1)*size,size;

Posted by ahsanmani on Sun, 28 Nov 2021 13:21:54 -0800

Programmer Group

Next day (DQL Data Query Language)

The second day

1. Basic Query: Understanding the select Field section

1.1 Field

1.2 Constant Value

1.3 Expression

1.4 Common Functions

1.4.1 Single Line Function

1.4.2 Process Control Functions

1.4.3 Aggregation Function

2. Conditional Query: where Partial Filter Conditions

2.1 Conditional Expression

2.2 Logical Expression

2.3 Fuzzy Query

3. Sort query order by

4. Grouping query group by

5. Paging query limit

Hot Keywords