6-SQL联结查询（子查询、组合查询、联结查询）

说明

▍ 子查询：嵌套在其他查询中的查询；

▍ 组合查询（复合查询 / 并操作）：执行多次查询，多条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;