MySQL Ⅳ 查询进阶

聚合函数

以exam_result表为例

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);

count

数量

select count(*) from exam_result;

sum

总和（不是数字，无意义）

select sum(math) from exam_result;

avg

平均值

select avg(math) from exam_result;

max

最大值

select max(math) from exam_result;

min

最小值

select min(math) from exam_result;

group by

分组

以emp表为例

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);

查询每个角色的最高工资、最低工资和平均工资

将role 相同的分为一组，求其最大，最小，平均值

select role,max(salary),min(salary),avg(salary) from emp group by role;

having

过滤条件

where后不能加聚合函数，需要使用having

查询平均工资低于1500的角色和它的平均工资

select role,avg(salary) from emp group by role having avg(salary)<1500;

联合查询

联合查询就是两张表或者两张以上的表，进行连接查询

因为我们所需要的数据，不仅仅是来自于一张表的，而是来自于多张表的

笛卡尔积

所有的联合查询都是从笛卡尔积当中去取出数据，在取数据的时候，一定是要满足某种规则，接下来，我们来学习一下各种联合查询的方式把~

以下列表为例

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班','学习了机械自动化');

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','张三','123@qq.com',1), 
('10012','李四',null,1), 
('10034','王五',null,1), 
('10023','赵六','456@qq.com',1), 
('20019','郑一',null,1),
('20021','刘二','789@qq.com',2), 
('30098','lily',null,2), 
('30045','joey','happy@qq.com',2);

drop table if exists course; 
create table course( 
	id int primary key auto_increment, 
	name varchar(20) 
);

insert into course(name) values 
('Java'),('中国传统文化'),('计算机原理'),('语文'),('数学'),('英语');

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);

学生有8个
分数有20个
一共8*20=160个

内连接

select 字段 from 表1 别名1 inner join 表2 别名2 on 连接条件 and 其他条件

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件

1. 查询赵六同学的成绩

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='赵六';

注：inner可以省略
可以直接用，+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='赵六';

2. 查询所有同学的总成绩

select stu.sn,stu.name,sum(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,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;

自连接

把一张表看做两张表来使用

查询所有“计算机原理”成绩比“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;

子查询

单行子查询

返回一行记录的子查询

查询“郑一”同学的同班同学

①先查询他是哪个班的

select classes_id from student stu where name = '郑一';

②再查这个班有谁

select * from student where classes_id = 1;

③合并

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

多行子查询

返回多行记录的子查询

(not) in

查询语文或英语的成绩信息
①先查语文或英语的id

select id from course 
where name = '语文' or name = '英语';

②再查这两个id对应的成绩

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

(not) exists

只要这个表达式为真，返回true

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

①首先执行一次外部查询，并缓存结果集，如 select * from A
②遍历外部查询结果集的每一行记录为R，代入子查询中作为条件进行查询，如 select 1 from B where B.id=A.id
③若子查询有返回结果，exists子句返回true，这一行R可以作为外部查询结果行，否则不可

合并查询（union）

union ：合并且去重

select * from student
where id <= 3
union  
	select * from student
	where name = '张三';

union all ：合并（不去重）

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

注意

禁止三张表以上的连表查询，若需要三张表，在后端代码中解决，不要在MySQL中解决