MySQL Ⅳ query advanced

MySQL

Aggregate function

Take the exam_resulttable as an example

drop table if exists exam_result;
create table exam_result ( 
	id INT, 
	name VARCHAR(20), 
	chinese DECIMAL(3,1), 
	math DECIMAL(3,1), 
	english DECIMAL(3,1) 
);
insert into exam_result (id,name, chinese, math, english)
values
(1,'A', 67, 98, 56), 
(2,'B', 87.5, 78, 77),
(3,'C', 88, 98, 90), 
(4,'D', 82, 84, 67), 
(5,'E', 55.5, 85, 45), 
(6,'F', 70, 73, 78.5), 
(7,'G', 75, 65, 30),
(8,'H', 78, 32, 98);
Insert picture description here

count

Quantity

select count(*) from exam_result;
Insert picture description here

sum

Sum (not a number, meaningless)

select sum(math) from exam_result;
Insert picture description here

avg

average value

select avg(math) from exam_result;
Insert picture description here

max

Max

select max(math) from exam_result;
Insert picture description here

min

Minimum

select min(math) from exam_result;
Insert picture description here

group by

Grouping

Take the emptable as an example

drop table if exists emp; 
create table emp( 
	id int primary key auto_increment, 
	name varchar(20) not null, 
	role varchar(20) not null, 
	salary numeric(11,2) 
);
insert into emp(name, role, salary) values 
('老师A','讲师', 2000.20),
('老师B','讲师', 2000.99), 
('老师C','讲师', 2000.11), 
('老师D','教授', 3000.5), 
('老师E','辅导员', 1000.33), 
('老师F','教授', 3000.66),
('老师E','辅导员', 1000.33);

Query the maximum wage, minimum wage and average wage of each role

Divide the same roles into a group and find the maximum, minimum, and average value

select role,max(salary),min(salary),avg(salary) from emp group by role;
Insert picture description here

having

Filter condition

Aggregate functions cannot be added after where, you need to use having

Query the role whose average salary is lower than 1500 and its average salary

select role,avg(salary) from emp group by role having avg(salary)<1500;
Insert picture description here

Joint query

The joint query is two tables or more than two tables, and join the query

Because the data we need is not just from one table, but from multiple tables

Cartesian Product

All joint queries are to fetch data from the Cartesian product. When fetching data, certain rules must be met. Next, let's learn about various joint query methods~

Take the following list as an example

drop table if exists classes; 
create table classes( 
	id int primary key auto_increment,
	name varchar(50), 
	`desc` varchar(50)
);
insert into classes(name, `desc`) values 
('计算机系2018级1班', '学习了计算机原理'),
('中文系2018级3班','学习了中国传统文学'), 
('自动化2018级5班','学习了机械自动化');
Insert picture description here
drop table if exists student; 
create table student( 
	id int primary key auto_increment, 
	sn int,
	name varchar(30), 
	qq_mail varchar(30), 
	classes_id int 
);
insert into student(sn, name, qq_mail, classes_id) values 
('10010','张三','[email protected]',1), 
('10012','李四',null,1), 
('10034','王五',null,1), 
('10023','赵六','[email protected]',1), 
('20019','郑一',null,1),
('20021','刘二','[email protected]',2), 
('30098','lily',null,2), 
('30045','joey','[email protected]',2);
Insert picture description here
drop table if exists course; 
create table course( 
	id int primary key auto_increment, 
	name varchar(20) 
);
insert into course(name) values 
('Java'),('中国传统文化'),('计算机原理'),('语文'),('数学'),('英语');
Insert picture description here
drop table if exists score; 
create table score( 
	id int primary key auto_increment, 
	score DECIMAL, 
	student_id int, 
	course_id int 
);
insert into score(score, student_id, course_id) values 
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5), 
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), 
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);
Insert picture description here


There are 8 students,
20 scores, a
total of 8*20=160

Internal connection

Insert picture description here
select 字段 from 表1 别名1 inner join 表2 别名2 on 连接条件 and 其他条件
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件
  1. Check Zhao Liu's grades
select stu.sn,stu.name,sco.score,cou.name as 课程名 
from student stu 
inner join score sco 
on stu.id = sco.student_id 
	inner join course cou 
	on sco.course_id = cou.id 
and stu.name='赵六';
Insert picture description here


Note: inner can be omitted and
can be used directly, written in +where

select stu.sn,stu.name,sco.score,cou.name as 课程名
from student stu ,score sco,course cou
where stu.id = sco.student_id
and sco.course_id = cou.id
and stu.name='赵六';
  1. Query the total score of all students
select stu.sn,stu.name,sum(score)
from student stu,score sco
where stu.id = sco.student_id
group by stu.id;
Insert picture description here

Outer join

Insert picture description here


Query each classmate’s grades, personal information, and display if there is no grade

select stu.id,stu.name,sco.score 
from student stu,score sco 
where stu.id=sco.student_id 
group by stu.id;
select stu.id,stu.name,sco.score 
from student stu left join score sco 
on stu.id=sco.student_id 
group by stu.id;
select stu.id,stu.name,sco.score 
from score sco right join student stu 
on stu.id=sco.student_id 
group by stu.id;
Insert picture description here

Self-connection

Use one table as two tables

Query all information with higher scores in "Computer Principles" than in "Java"

select s2.* from score s1,score s2 
where s1.student_id = s2.student_id
and s1.score<s2.score
and s1.course_id = 1
and s2.course_id = 3;
Insert picture description here

Subquery

Single row subquery

Subquery that returns a row of records

Query classmates of "Zheng Yi"

①Check which class he is in first

select classes_id from student stu where name = '郑一';
Insert picture description here


②Check who is in this class

select * from student where classes_id = 1;
Insert picture description here


③Merge

select * from student 
where classes_id = (
	select classes_id 
	from student stu 
	where stu.name = '郑一'
);

Multi-row subquery

Subquery that returns multiple rows

(not) in

Inquire about the performance information
of Chinese or English ①Check the id of Chinese or English first

select id from course 
where name = '语文' or name = '英语';
Insert picture description here

②Check the results corresponding to these two ids

select * from score 
where course_id in (
	select id from course 
	where name = '语文' or name = '英语'
);
Insert picture description here

(not) exists

As long as this expression is true, return true

select * from A where exists (
	select 1 from B 
	where B.id=A.id
);

①First execute an external query and cache the result set. For example, select * from A
②Traverse each row of the result set of the external query as R, and substitute it into the subquery as a condition for the query. For example select 1 from B where B.id=A.id
, ③If the subquery returns a result, the exists clause returns true. A row of R can be used as an external query result row, otherwise it cannot

Combined query (union)

union: unite and remove duplication

select * from student
where id <= 3
union  
	select * from student
	where name = '张三';
Insert picture description here

union all: merge (do not de-duplicate)

select * from student
where id <= 3
union all 
	select * from student
	where name = '张三';
Insert picture description here

note

It is forbidden to query with more than three tables. If three tables are needed, solve it in the back-end code, not in MySQL