从零学会SQL：多表查询

1.表的加法

表的加法：

• union，删除重复行
• union all，保留重复行

select 课程号,课程名称
from course
union
select 课程号,课程名称
from course1

select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1

2.交叉联结

2.1 4张表的联结关系图

2.2 联结

联结：表和表之间通过列产生关系，联结就是根据表和表之间的关系将两个表合并在一起的操作

常用的联结：交叉联结、内联结、左联结、右联结、全联结

2.3 交叉联结 cross join

交叉联结也称为笛卡尔积，即将表1中的每一行与表2中的每一行合并在一起

交叉联结的行数是表1的行数*表2的行数

3.内联结 inner join

3.1 什么是内联结

内联结（inner join）：查找同时存在于两张表的数据

3.2 内联结的步骤

内联结的步骤：

1. 分别从两张表中取出符合条件的行。即同时存在于两张表中的数据
2. 将两张表中取出的数据进行合并，即交叉联结

3.3 内联结SQL语句

select a.学号,a.姓名,b.课程号
from student as a inner join course as b
on a.学号=b.学号;

4.左联结

4.1 什么是左联结

4.2 左联结的步骤

   左联结的步骤：

1. 左边表的数据全部取出，右边的表选择同时存在于两张表中的数据
2. 将两张表中取出的数据进行合并，即交叉联结

4.3 左联结SQL语句

select a.学号,a.姓名,b.课程号
from student as a left join course as b
on a.学号=b.学号;

4.4 不包含公共部分的左联结

select a.学号,a.姓名,b.课程号
from student as a left join course as b
on a.学号=b.学号
where b.学号 is null;

5.右联结 right join

5.1 什么是右联结

5.2 右联结步骤

1. 右边表的数据全部取出，左边的表选择同时存在于两张表中的数据
2. 将两张表中取出的数据进行合并，即交叉联结

5.3 右联结SQL语句

select a.学号,a.姓名,b.课程号
from student as a right join course as b
on a.学号=b.学号;

5.4 不包含公共部分的右联结

select a.学号,a.姓名,b.课程号
from student as a right join course as b
on a.学号=b.学号
where a.学号 is null;

6.全联结

7.一张图记住所有联结

7.1 联结图

7.2 sql运行顺序

1. 先运行子查询
2. 每个查询语句里运行顺序：先运行蓝框里的子句；然后运行select子句；最后运行红框里的子句

8.联结应用案例

8.1 如何用sql解决业务问题

1. 翻译成大白话
2. 写出分析思路
3. 写出对应的sql子句

8.2 查询所有学生的学号、姓名、选课数、总成绩

• 翻译成大白话
  • 学号、姓名（学生表student）
  • 选课数（每个学生的选课数目：成绩表score，按学号分组，对课程号计数count）
  • 总成绩（每个学生的总成绩：成绩表score，按学号分组，对成绩求和sum）
• 写出分析思路
  • select 查询结果：[学号，姓名，选课数，总成绩]
  • from 从哪张表查找数据：[学生表student，成绩表score；两个表如何联结？通过学号；用哪种联结？左联结）
  • where 查询条件：[没有]
  • group by 分组：[每个学生的选课数目：按学号分组，对课程号计数count；每个学生的总成绩：按学号分组，对成绩求和sum]
  • having 对分组结果指定条件：[没有]
  • order by 对查询结果排序：[没有]
  • limit 从查询结果中取出指定行：[没有];
• 写出对应的sql子句

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号;

8.3 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

• 翻译成大白话
  • 查询出所有学生的学号，姓名，平均成绩
    • 学号，姓名（在学生表student）
    • 平均成绩（每个学生的平均成绩：在成绩表score，按学号分组，平均成绩：avg（成绩））
  • 平均成绩>85
• 写出分析思路
  • limit 从查询结果中取出指定行：[没有];
  • order by 对查询结果排序：[没有]
  • having 对分组结果指定条件：[平均成绩大于85]
  • group by 分组：[每个学生的总成绩：按学号分组，对成绩求和sum]
  • where 查询条件：[没有]
  • from 从哪张表中查找数据：[学生表student，成绩表score；两个表如何联结？通过学号；用哪种联结？左联结）
  • select 查询结果：[学号，姓名，平均成绩]
  • 写出对应的sql子句

select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号
having avg(b.成绩)>85;

8.4 查询学生的选课情况：学号，姓名，课程号，课程名称

select a.学号,a.姓名,c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;

9.case表达式

9.1 case表达式

case when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     ...
     else <表达式>
end

9.2 如何使用case表达式

当有多种条件进行判断时，使用case表达式

-- 在查询结果中显示成绩是否及格
select 学号,课程号,成绩,
(case when 成绩>=60 then '及格'
when 成绩<60 then '不及格'
else null
end) as 是否及格
from score;

9.3 查询出每门课程的及格人数和不及格人数

-- 查询出每门课程的及格人数和不及格人数
select 课程号,
sum(case when 成绩>=60 then 1
else 0
end) as 及格人数,
sum(case when 成绩<60 then 1
else 0
end) as 不及格人数
from score
group by 课程号;

9.4 注意事项

• else语句可以省略不写，默认为空值，建议不要省略
• end不能省略
• case表达式可以写在sql的任意子句中

9.5 例子

使用分段[100-85] [85-70] [70-60] [<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100
then 1 else 0)as '[100-85]',
sum(case when 成绩>=70 and 成绩<85
then 1 else 0)as '[85-70]',
sum(case when 成绩>=60 and 成绩<70
then 1 else 0)as '[70-60]',
sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b
on a.课程号=b.课程号
group by a.课程号,b.课程名称;

10.总结