说明
▍ 子查询：嵌套在其他查询中的查询；
▍ 组合查询（复合查询 / 并操作）：执行多次查询，多条select语句，并将结果作为单个查询结果返回；
- union all：包含重复行;
- union：取消重复行;
▍ 联结查询
image.png
- inner join 内联结
保留两边表都有的记录； - left join 左联结
保留左边表的信息，右边表没有匹配上的字段显示为null； - right join 右联结
保留右边表的信息，左边表没有匹配上的字段显示为null； - full join 全联结：
左右两边表的信息都保留，没有匹配上的字段显示为null；
练习
表例
--1、查询来自安徽省的学生都在哪些班级（用子查询做）；字段：学号，姓名，省份，班级；
select
distinct
stu_id as 学号
,name as 姓名
,from_where as 省份
,(select class from class_info where student_info.stu_id=class_info.stu_id ) as 班级
from student_info
where from_where='安徽省';
--方法二
select
distinct
stu_id as 学号
,name as 姓名
,'安徽省'
,class
from class_info
where stu_id in
(
--查询来自安徽省的学生ID
select
distinct stu_id
from student_info
where from_where='安徽省'
);
--2、查询每个班级考试不及格的学生信息（用子查询做）；字段：班级，学号，姓名，学科，不及格分数；
select
(select class from class_info where score_info.stu_id=class_info.stu_id ) as 班级
,stu_id as 学号
,name as 姓名
,subject as 学科
,score as 不及格分数
from score_info
where score<60
order by 班级;
--3、查询每个班级考试不及格的学生人数（子查询+聚合函数）；字段：专业，班级，不及格人数；
select
distinct
major
,class
,(select count(if(score<60,stu_id,NULL)) from score_info where score_info.stu_id=class_info.stu_id) as 不及格人数
from class_info
group by major,class
order by major,class;
--方法2
major
,class
,count(distinct stu_id)
from class_info
where stu_id in
(
select
distinct stu_id
from score_info
where score<60
)
group by 1,2;
--4、查询 电气工程专业 和 粮食专业 的学生信息：姓名；
--简单查询的方式;
select name
from student_info
where major in('电气工程专业','粮食专业');
--组合查询的方式：去重、不去重的区别;
--union all：包含重复行;
--union：取消重复行;
f1.name
from
(
select
name
from student_info
where major='粮食专业'
union all
select
name
from student_info
where major='电气工程专业'
)f1;
--5、查询物理和政治考试的学生信息（用组合查询做）；字段：科目，学号，姓名，分数；
select
f1.subject
,f1.stu_id
,f1.name
,f1.score
from
(
select
distinct
subject
,stu_id
,name
,score
from score_info
where subject='物理'
union all
select
subject
,stu_id
,name
,score
from score_info
where subject='政治'
)f1;
--6、查询物理和政治考试的学生人数（组合查询+聚合函数）；字段：科目，考试人数；
select
f1.*
from
(
select
subject
,count(distinct stu_id)
from score_info
where subject='政治'
group by 1
union all
select
subject
,count(distinct stu_id)
from score_info
where subject='物理'
group by 1
)f1;
--第二部分：联结查询
--1、查询来自安徽省的学生都在哪些班级；字段：学号，姓名，省份，班级；
select
distinct
f1.stu_id
,f1.name
,f1.from_where
,f2.class
from
(
select
distinct
stu_id
,name
,from_where
from student_info
where from_where='安徽省'
)f1
inner join
(
select
distinct
stu_id
,class
,name
from class_info
)f2 on f1.stu_id=f2.stu_id;
--2、查询每个班级没有参加考试的学生信息；字段：班级，学号，姓名；
select
distinct
f1.class
,f1.stu_id
,f1.name
from
(
--全部学生信息
select
distinct
stu_id
,name
,class
from class_info
)f1
left join
(
--参加了考试的学生id
select
distinct
stu_id
from student_info
)f2 on f1.stu_id=f2.stu_id
--筛选null
where f2.stu_id is null;
--3、查询每个班级考试的平均分（联结+聚合函数）；字段：学院，专业，班级，平均分；
select
f1.college
,f1.major
,f1.class
,avg(f2.score)
from
(
select
distinct
college
,major
,class
,stu_id
from class_info
)f1
inner join
(
select
distinct
stu_id
,score
from score_info
)f2 on f1.stu_id=f2.stu_id
group by f1.college,f1.major,f1.class
order by f1.college,f1.major,f1.class;
--4、查询每个班级考试不及格的人数（联结+聚合函数）；字段：班级，不及格人数；
select
f1.class as 班级
,count(f2.stu_id) as 不及格人数
from
(
select
distinct
stu_id
,class
from class_info
)f1
inner join
(
select
distinct
stu_id
from score_info
where score<60
)f2 on f1.stu_id=f2.stu_id
group by f1.class
order by f1.class;
--5、查询每个班级，不同省份的人数（联结+聚合函数）；字段：班级，省份，人数；
select
f1.class as 班级
,f2.from_where as 省份
,count(f1.stu_id) as 人数
from
(
select
distinct
class
,stu_id
from class_info
)f1
inner join
(
select
distinct
from_where
,stu_id
from student_info
)f2 on f1.stu_id=f2.stu_id
group by f1.class,f2.from_where
order by f1.class,f2.from_where;
