查询某列为空的数据_多表查询

多表查询

2020年7月31日

16:36

知识点：

• 表的加法
• 表的联结
• 联结应用案例
• case表达式

一、表的加法

Union保留两个表中不重复的行

Union all保留两个表中所有行

复制表方法：右键表名——复制表——结构和数据——表重命名

二、表的联结：

交叉联结（cross join）、内联结（inner join）、左联结（left join）、右联结（right join)、全联结（full join)

1. 交叉联结（cross join）:表1A行，表2B行，合并后的表行数=A*B

1. 内联结（inner join）：先取出符合条件的行，然后进行交叉联结

3． 左联结（Left join）：先取出符合条件的行然后进行交叉联结，当左边表格在右表找不到对应值时，右表显示空值。左边是主表，所以左边表不会有空值，左边表的列都会展示

取出如下图所示部分

思路：先取出左联结部分，然后加where子句筛选右表联结列为空部分

4． 右联结（Right join）（同理left join）：先取出符合条件的行然后进行交叉联结，当右边表格在左表找不到对应值时，左表显示空值。右边是主表。

如何取出图中所示右联结但不包含交叉联结部分

同理左联结，用where子句取出左表为空的内容/先取出右联结部分，然后加where子句筛选左表联结列为空部分

（因为下图表格中a表中包含b表所有学号字段，所以不存在b有a没有的情况，即返回空值）

5． Full jion全联结：左表和右表的所有行

取出两个表中所有值，当两个表中有对应的联结字段时，同一字段做笛卡尔乘积，两张表中没有对应的联结字段时，对方表字段显示空值

My sql不支持全联结，如下图所示，不能识别full join，只需要理解概念即可

所有联结类型如下图所示：如果问题特殊说明需要某张表的全部数据可以选择使用左联结或是右联结，否则使用内联结

三、 联结应用案例

翻译成大白话→写出分析思路→写出对应的sql子句

发生错误时，用排除法逐一查找，单独运行子查询

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

分析思路：

• 学号、姓名（学生表student）;

选课数：每个学生的选课数目，成绩表score，按学号分组，对课程号技计数

总成绩：每个学生的总成绩，成绩表score，按照学号分组，对成绩求和sum

• 字段存在于两张表且需查询所有学生所以需进行左联结
• 所有学生的选课数需要按学号进行分组

上面的分组group by 为什么是a.学号，而不是b.学号？

因为左联结是保留左边表的所有数据（全部学生），题目要求也是“全部学生”，所以分组是以左表学号分组。

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

分析思路：

• 查询出所有学生的学号，姓名（学号姓名在student表中），平均成绩（每个学生的平均成绩，在成绩表score中，按照学号分组，平均成绩avg（成绩））

平均成绩>85

• 学号、姓名和平均成绩存在于两张表中，查询平均成绩大于85分的内容，使用右联结
• 平均成绩大于85分的学生，需要按学号分组求平均成绩

3． 查询学生的选课情况，查询结果显示：学号，姓名，课程号，课程名称

分析思路：

• 学号、姓名、课程号，课程名称分别在三个表中（学生表与课程表没有直接联系，所以需要三张表之间查询）
• 要做两次联结内联结即可

四、case when表达式：作用等同于条件判断函数，可以解决复杂查询；

判断某一行是否符合某个条件，如果符合就运行then，如果不符合执行下一条when语句……负责执行else语句；

注意事项：书写习惯要号，else写全；end不能省略；case表达式可以使用在sql语句的任何字句中；

使用case语句的条件：当有多种情况需要条件判断的时候。

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

分析思路：使用case when写出及格和不及格字段并统计人数，按每门课程分组

第一步：查询出每门课程的的人数；

第二步：查询出每门课程的及格人数和不及格人数

及格人设置1，不及格人设置0，可以用求和及格总人数

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

分析思路：自定义分组使用case表达式；涉及到两个表的查询使用联结；

【使用case when得出成绩分段字段，需要成绩表和课程表左联结，然后使用课程ID，课程名称分组】

需要查询课程表里的所有内容所以使用的右联结；

select的列名只能是Group by 子句里的列名。

当前表课程号与课程名称是一一对应的，所以对查询结果没有影响。

但是当用多个列来分组的时候，这几个列的值全部相同才算一组。

【老师答案：zoo的联系已经查看，稍后会在电脑端再次练习】

一、练习题

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

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

【问】上面分组（group by）为什么是a.学号，而不是b.学号？

【答】左连接是保留左边表的所有数据（全部学生），你再看题目要求也是“全部学生”，所以你分组是以左表学号分组。

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

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

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

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

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

-- 考察case表达式select 课程号,sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,sum(case when 成绩 < 60 then 1 else 0 end) as 不及格人数from scoregroup by 课程号;

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

-- 考察case表达式select a.课程号,b.课程名称,sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',sum(case when 成绩 >=70 and 成绩<85 then 1 else 0 end) as '[85-70]',sum(case when 成绩>=60 and 成绩<70 then 1 else 0 end) 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.课程名称;

二、题目来自sqlzoo的多表查询题目

网址：

https://sqlzoo.net/wiki/The_JOIN_operation/zh

这部分题目使用的是‘2012年欧洲足球锦标赛’数据库里的表。

1.比赛信息表game

id比赛编号，mdate比赛日期， stadium比赛地点， team1对战双方（球队编号1）， team2对战双方（球队编号2）

2.进球信息表goal

matchid比赛编号，teamid球队编号， player进球球员的姓名，gtime多长时间进的球（从开始比赛到进球多长时间，单位：分钟）

比赛信息表game（id比赛编号）和进球信息表goal（matchid比赛编号）通过‘比赛编号’联结

3.球队信息表eteam

id球队编号（联结2） teamname 球队名称 coach教练

进球信息表goal（teamid球队编号）球队信息表eteam（球队编号id）通过‘球队编号’联结

如何做题？

练习题答案

先自己在sqlzoo平台里（网址：

https://sqlzoo.net/wiki/The_JOIN_operation/zh）

输入sql，查看运行结果，然后再和下面的答案对比哪里写的不一样

1. 在进球表（goal）中查找德国球队（teamid = 'GER'）进球的比赛编号（matchid），进球球员姓名（player）
 
select matchid, player from goal where teamid = 'GER';
 
2. 在比赛信息表（game）查找比赛编号1012的信息
 
select id,stadium,team1,team2from game where id = 1012;
 
3. 查找德国队进球球员姓名，球队编号（在进球信息表goal）, 比赛地点,比赛日期（在比赛信息表game）
 
问题分析：
进球球员姓名，球队编号（在进球信息表goal）
比赛地点,比赛日期（在比赛信息表game）
两个表如何联结？通过比赛编号
哪一种联结？需要两个表中共同部分，内联结
 
select b.player, b.teamid, a.stadium, a.mdatefrom game as a inner join goal as b on a.id=b.matchidwhere b.teamid = 'GER';
 
4. 查找姓名中以Mario开头的进球球员，符合条件球员参加比赛的对战双方
 
问题分析：
1）查找出进球球员姓名（进球信息表goal：进球球员姓名player），
比赛的对战双方（比赛信息表game：对战双方team1,team2）
两个表如何联结？通过比赛编号
哪一种联结？需要两个表中共同部分，内联结
2）加入条件：进球球员姓名中以Mario开头，模糊查询：like 'Mario%'
 
select a.team1, a.team2, b.playerfrom game as a inner join goal as b on a.id=b.matchidwhere b.player like 'Mario%';
 
5. 查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟
 
问题分析：
1）查找出进球球员的姓名、球队编号、多长时间进球（在进球信息表goal）
教练（在球队信息表eteam）
 
两个表如何联结？通过球队编号（进球信息表goal：teamid球队编码 = 球队信息表eteam：id球队编码）
哪一种联结？需要两个表中共同部分，内联结
 
2）加入条件：多长时间进球<=10分钟
 
select b.player, b.teamid, c.coach, b.gtimefrom goal as b inner join eteam as c on b.teamid=c.idwhere b.gtime<=10;
 
6. 'Fernando Santos'作为教练的比赛日期，球队编号有哪些？
 
问题分析：
1）查找出
教练（在球队信息表eteam）
比赛日期，球队编码（在比赛信息表game）
 
两个表如何联结？通过球队编号（比赛信息表game：team1对战双方的球队编码（team1或者team2的球队编码） = 球队信息表eteam：id球队编码）
哪一种联结？需要两个表中共同部分，内联结
 
2）加入条件：教练是'Fernando Santos'
 
select a.mdate, c.teamnamefrom  game as a inner join eteam as c on a.team1=c.idwhere c.coach = 'Fernando Santos';
 
7. 在比赛地点'National Stadium, Warsaw'有哪些进球球员？
 
问题分析：
1）查找
比赛地点（在比赛信息表game），进球球员（在进球信息表goal）
2）加入条件：比赛地点是'National Stadium, Warsaw'
 
select b.playerfrom game as a inner join goal as b on a.id=b.matchidwhere a.stadium = 'National Stadium, Warsaw';
 
8.射入德国球门的球员姓名
 
问题分析：
1）查找进球球员姓名，对战双方
2）条件：有一个球队是德国（球队编号'GER'），被射入了球
 
select distinct b.playerfrom game as a inner join goal as b on a.id = b.matchidwhere (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER');
 
1）解释下where子句中的条件：
主队是德国或者客队是德国，比如
德国 和 A对比赛（德国是主队），进球的是A队
A队和德国比赛（德国是客队），进球的是A队
所以条件是(b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER')
2）内联结的结果中入门球员有重复值，用distinct去掉重复值
 
9. 查找出球队名称，和每个球队进球人数
 
问题分析：
球队名称(球队信息表eteam)，和每个球队进球人数(进球信息表goal：按球队名称分组统计count(teamname)
 
select c.teamname, count(c.teamname)from eteam as c inner join goal as b on c.id=b.teamidgroup by c.teamname;
 
10. 查找出所有比赛地点，每个比赛地点的进球数
 
问题分析：
比赛地点(比赛信息表game)，和每个比赛地点的进球数(进球信息表goal：按比赛地点分组，对进球人员进行汇总count(player))
联结方式是什么？要查出比赛信息表game中全部比赛地点，所以是左联结
 
select a.stadium, count(b.player)from game as a left join goal as b on a.id=b.matchidgroup by a.stadium;
 
11. 查找出有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数
 
select a.id,a.mdate, count(b.player)from game as a inner join goal as b on a.id = b.matchid where (team1 = 'POL' OR team2 = 'POL')group by a.id;
 
或者
 
select a.id,a.mdate, count(b.player)from game as a inner join goal as b on a.id = b.matchid where (team1 = 'POL' or team2 = 'POL')group by a.id,a.mdate;
 
12. 对于德国队'GER'得分的每场比赛，显示比赛编号，比赛日期和'GER'得分的进球数
 
问题分析：
比赛编号，比赛日期（在比赛信息表game）和'GER'得分的进球数(进球信息表goal：按比赛地点分组，对进球人员进行汇总count(player))
 
select a.id,a.mdate,count(player) from game as a inner join goal as b on a.id=b.matchid where b.teamid='GER' group by a.id,a.mdate;
 
13. 查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）
 
问题分析：
当比赛信息表game中的球队编号(team1)也出现在进球信息表(goal)中时，表示这个球队进球了，使用case语句来统计
 
select a.mdate,a.team1,sum(case when a.team1=b.teamid then 1 else 0 end) as score1,a.team2,sum(case when a.team2=b.teamid then 1 else 0 end) score2 from game as a left join goal as b on a.id = b.matchidgroup by a.id,a.mdate,a.team1,a.team2order by a.mdate,a.id,a.team1,a.team2;

-- 插入数据
insert into course1
						VALUES('0003','英语','0003');
insert into course1
						VALUES('0004','计算机','0004');
insert into course1
						VALUES('0005','数据库','0005');						


-- 表的加法（UNION）:保留两个表中不重复的行
select 课程号,课程名称
from course
union
select 课程号,课程名称
from course1


-- 表的加法（UNION all）:保留两个表中所有行
select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1


-- 内联结（cross join）
select a.学号,a.姓名,b.课程号
from student as a inner join score as b
on a.学号=b.学号;


-- 左联结（Left join）：先取出符合条件的行然后进行交叉联结，当左边表格在右表找不到对应值时，右表显示空值
select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号=b.学号

-- 左联结（Left join）但不包括内联结：先取出符合条件的行然后进行交叉联结，当左边表格在右表找不到对应值时，右表显示空值
select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号=b.学号
where b.学号 is NULL;
-- where b.课程号 is NULL;



-- -- 右联结（right join）
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号;
-- -- 右联结（right join）但不包括内联结
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号;
where a.学号 is NULL;



-- 全联结（full join）
-- 错误内容：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full join score as bon a.学号=b.学号' at line 3
select a.学号,a.姓名,b.课程号
from student as a full join score as b
on a.学号=b.学号;


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

分析思路：
	• 学号、姓名（学生表student）;
	选课数：每个学生的选课数目，成绩表score，按学号分组，对课程号技计数
	总成绩：每个学生的总成绩，成绩表score，按照学号分组，对成绩求和sum
	• 字段存在于两张表且需查询所有学生所以需进行左联结
所有学生的选课数需要按学号进行分组
*/
Select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
From student as a left join score as b 
on a.学号=b.学号
group by 学号;



/*
2．查询平均成绩大于85的所有学生的学号、姓名和平均成绩
分析思路：
	• 查询出所有学生的学号，姓名（学号姓名在student表中），平均成绩（每个学生的平均成绩，在成绩表score中，按照学号分组，平均成绩avg（成绩））
	平均成绩>85
	• 学号、姓名和平均成绩存在于两张表中，查询平均成绩大于85分的内容，使用右联结
平均成绩大于85分的学生，需要按学号分组求平均成绩
*/
Select a.学号,a.姓名,avg(b.成绩) as 平均成绩
From student as a right join score as b on a.学号=b.学号
group by a.学号
having avg(b.成绩)>85;


/*
3．查询学生的选课情况，查询结果显示：学号，姓名，课程号，课程名称
分析思路：
	• 学号、姓名、课程号，课程名称分别在三个表中（学生表与课程表没有直接联系，所以需要三张表之间查询）
	• 要做两次联结内联结即可
	*/
Select a.学号,a.姓名,b.课程号,c.课程名称
From student as a inner join score as b on a.学号=b.学号 inner join course as c on
b.课程号=c.课程号




/*
1．查询出每门课程的及格人数和不及格人数
分析思路：使用case when写出及格和不及格字段并统计人数，按每门课程分组
第一步：查询出每门课程的的人数；
第二步：查询出每门课程的及格人数和不及格人数
*/
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 课程号;




/*
2. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程号和课程名称
分析思路：自定义分组使用case表达式；涉及到两个表的查询使用联结；
【使用case when得出成绩分段字段，需要成绩表和课程表左联结，然后使用课程ID，课程名称分组】
需要查询课程表里的所有内容所以使用的右联结；
select的列名只能是Group by 子句里的列名。
当前表课程号与课程名称是一一对应的，所以对查询结果没有影响。
但是当用多个列来分组的时候，这几个列的值全部相同才算一组。
*/
-- sum语句后面需要增加逗号。
select a.课程号,b.课程名称,
sum(case when 成绩 BETWEEN 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成绩<=85 and 成绩>70 then 1 else 0 end) as '[85-70]',
sum(case when 成绩<=70 and 成绩>60 then 1 else 0 end) as '[70-60]',
sum(case when 成绩<=60 then 1 else 0 end) as '[<60]'
from score as a left join course as b on a.课程号=b.课程号
group by a.课程号,b.课程名称;