十二生肖配对表查询_SQL多表查询

多表查询：从多张表中获取数据；

1、表的加法（union）

两个表的结构一样，但是红框的数据不一样，利用表的加法，把两张表合并成一张表；

表的加法（union）是将两个表的数据按照行合并到一起，删除重复数据；

要想保留重复数据，用union all

2、表的联结

1）什么是联结

关系就是数据库能对应的匹配，在关系数据库中，叫做联结（join）；

联结是通过表和表之间的关系，把表合并到一起的操作；

学生表-成绩表 通过学号联结；成绩表-课程表 通过课程号联结；课程表-教师表 通过教师号联结；

2）常见的联结方式

①交叉联结（笛卡尔积）

是将表中的每一行，都与另一个表的每一行合并在一起；

如图：表1、表2、表3分别和A、B合并在一起；

新表的行数=表1行数*表2行数。

交叉联结在实际工作中用的比较少，结果行数太多，需要花费大量的运算成本和设备的支持，

而且行数太多，实际价值有限；

但交叉联结是后边所有联结的基础，其他的联结都是在交叉连接的基础上+过滤条件。

②内联结（inner join）

查找同时存在在两张表中的数据

内联结的运行：分别从学生表、成绩表中取出符合条件的行 → 交叉联结；

内联结SQL语句：

/*
Select 表示查找出哪些数据
From 便于使用，我们把列名简化成别名
On后边的语句，表示两个表通过学号联结
*/

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

③左联结（left join）

将表中左侧的数据全部查找出来，左联结是下图中红色的部分；

左联结的运行：通过学号产生匹配关系，左联结会把左边的表作为主表，全部数据读取出来（学生表的数据全部取出来），右边的表只取学号相同的数据，然后交叉联结合并；

左联结SQL语句：

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

-- 左侧的数据，如果在右表中查询不到对应的行，那么在右边的表格中会显示null；

如何联结下图的语句，只保留红色区域

左联结+where子句

④右联结（right join）

右侧表中的数据全部取出来，右边0005在左边的表中没有对应的数据，显示null

右联结的运行：通过学号产生匹配关系，把右表的全部数据读取出来（成绩表的数据全部取出来），左边的表只取学号相同的数据，然后交叉联结合并；

右联结SQL语句：

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

-- 右侧的数据，如果在左表中查询不到对应的行，那么在左边的表格中会显示null；

右联结基础上，去掉重合的部分

⑤全联结（full join）

返回左表和右表的所有行，当某行和另一个行中数据匹配的时候，两个行进行合并，如果没有匹配的行，对应的地方用空值填充，my sql不支持全联结；

SQL 联结方式总结

3、联结应用案例

翻译成大白话，写出分析思路，写出对应的sql语句；

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

1）学号、姓名（学生表 student）

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

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

/*
select 查询结果[学号，姓名，选课数，总成绩]
from 从哪张表中查找数据[学生表student,成绩表score两个表如何联结？通过学号；用哪种联结？左联结】
where查询条件[没有]
group by分组[
每个学生的选课数目：按学号分组，对课程号计数count；
每个学生的总成绩：按学号分组，对成绩求和sum；]
having对分组结果指定条件[没有]
order by 对查询结果排序[没有]
limit 从查询结果中取出指定行（没有];
*/

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

-- 因为是联结多个表查询，我们要在select语句中的列名前加上表的别名；
select a.学号,a.姓名,count(b.课程号）as选课数,sum(b.成绩）as总成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号;

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

1)查询出所有学生的学号，姓名，平均成绩；学号，姓名（在学生表student)；

平均成绩（每个学生的平均成绩：在成绩表score，按学号分组，平均成绩：avg(成绩）)；

2)平均成绩>85

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;

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

1）学号，姓名在学生表（student）

2）课程号，课程名称在课程表（course）

学生表和课程表发生联系，需要通过成绩表建立关系

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

4、case 表达式

case when <判断表达式> then <表达式>

when <判断表达式> then <表达式>

when <判断表达式> then <表达式>

......

else <表达式>

end

1）case表达式的作用

当有多种情况需要判断的时候，就需要使用case表达式了；

可以帮助我们解决复杂的查询问题，case表达的作用相当于进行一个条件判断的函数，用来判断每一行，是不是满足某个条件；

如何符合某个条件，就运行后边的then子句，不符合条件继续进行运行when子句，如果还没有查找到合适的数据，就会到else子句。

2）问题1：

select 学号,课程号,成绩,
(case when 成绩>=60 then '及格'
      when 成绩 <60  then '不及格'
else null
end)as 是否及格
from score;

运行顺序：

第一步：先运行>=60，满足后，显示及格，end；

第二步：在运行<60，满足后，不及格，结束end。

问题2：查询出 每门课程 的 及格人数 和 不及格人数

查询出 每门课程 的人数

select 课程号
count(学号) as 人数
from score
group by 课程号;

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

select 课程号,
sum(case when 成绩 >=60 then 1 else 0
end) as 及格人数,
sum(case when 成绩<60 then 1else 0
end) as 不及格人数
group by 课程号;

3）case表达式 注意事项

①else可以省略不写，这时候默认else是空值，为了养成更好的书写习惯，不建议省略；

②最后的end不能省略；

③把case表达式放在了select子句中，对查询结果进行了条件判断，其实case表达式可以写字SQL语句的任意子句中。

练习：

使用分段[100-85]，[85-70]，[70-60]，[<60]来统计各科成绩，分别统计：

各分数段人数，课程号和课程名称

各分数段人数（成绩表 score），课程号和课程名称（课程表 course）

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.课程名称;

-- 当用多个列来分组时，这几个列的值全部相同才算一组
-- GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。

5、SQL Zoo练习

/*
【问题1】列出赛事编号matchid 和球员名 player ,该球员代表德国队Germany入球的。
第一个例子列出球员姓氏为'Bender'的入球数据。 * 表示列出表格的全部字段，
简化了写matchid, teamid, player, gtime语句。
要找出德国队球员，要检查: teamid = 'GER'
*/
SELECT matchid,player
FROM goal 
WHERE teamid = 'GER';


-- 【问题2】查找显示赛事1012的 id, stadium, team1, team2 
SELECT id,stadium,team1,team2
FROM game
Where id=1012；

/*
【问题3】查找德国队进球球员姓名，球队编号（在进球信息表goal）, 比赛地点,比赛日期（在比赛信息表game）
【问题分析】
进球球员姓名，球队编号（在进球信息表goal）
比赛地点,比赛日期（在比赛信息表game）
两个表如何联结？通过比赛编号
哪一种联结？需要两个表中共同部分，内联结
语句FROM 表示合拼两个表格game 和 goal的数据。
语句ON 表示如何找出 game中每一列应该配对goal中的哪一列 
goal的 id 必须配对game的 matchid 。 简单来说，就是 ON (game.id=goal.matchid)
*/
select b.player, b.teamid, a.stadium, a.mdate
from game as a inner join goal as b 
on a.id=b.matchid
where b.teamid = 'GER';

/*
【问题4】查找姓名中以Mario开头的进球球员，符合条件球员参加比赛的对战双方
【问题分析】
1）查找出进球球员姓名（进球信息表goal：进球球员姓名player），
比赛的对战双方（比赛信息表game：对战双方team1,team2）
两个表如何联结？通过比赛编号
哪一种联结？需要两个表中共同部分，内联结
2）加入条件：进球球员姓名中以Mario开头，模糊查询：like 'Mario%'
*/
select a.team1, a.team2, b.player
from game as a inner join goal as b 
on a.id=b.matchid
where b.player like = 'Mario%';

/*
【问题5】查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟
【问题分析】
1）查找出进球球员的姓名、球队编号、多长时间进球（在进球信息表goal）
教练（在球队信息表eteam）
两个表如何联结？通过球队编号（进球信息表goal：teamid球队编码 = 球队信息表eteam：id球队编码）
哪一种联结？需要两个表中共同部分，内联结
2）加入条件：多长时间进球<=10分钟
*/
select b.player,b.teamid,c.coach,b.gtime
from goal as a inner join eteam as c 
on b.teamid=c.id
where b.gtime<=10;

/*
【问题6】'Fernando Santos'作为team1 的教练的赛事日期，和队伍名有哪些
【问题分析】
1）查找出
教练（在球队信息表eteam）
比赛日期，球队编码（在比赛信息表game）
两个表如何联结？通过球队编号（比赛信息表game：team1对战双方的球队编码（team1或者team2的球队编码） = 球队信息表eteam：id球队编码）
哪一种联结？需要两个表中共同部分，内联结
2）加入条件：教练是'Fernando Santos'
*/
select a.mdate, c.teamname
from  game as a inner join eteam as c 
on a.team1=c.id
where c.coach = 'Fernando Santos';

/*
【问题7】列出场馆 'National Stadium, Warsaw'的入球球员。
【问题分析】
1）查找：比赛地点（在比赛信息表game），进球球员（在进球信息表goal）
2）加入条件：比赛地点是'National Stadium, Warsaw' 
*/
Select b.player
From game as a inner join goal as b
on a.id=b.matchid
where a.stadium= 'National Stadium, Warsaw'


/*
【问题8】射入德国球门的球员姓名
【问题分析】
1）查找进球球员姓名，对战双方
2）条件：有一个球队是德国（球队编号'GER'），被射入了球
找非德国球员的入球，德国可以在赛事中作team1 队伍１（主）或team2队伍２（客）
你可以用teamid!='GER' 来防止列出德国球员。 你可以用DISTINCT来防止球员出现两次以上。
3）解释下where子句中的条件：
主队是德国或者客队是德国，比如
德国 和 A对比赛（德国是主队），进球的是A队
A队和德国比赛（德国是客队），进球的是A队
所以条件是(b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER')
4）内联结的结果中入门球员有重复值，用distinct去掉重复值
*/
select distinct b.player
from game as a inner join goal as b 
on a.id = b.matchid
where (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER');

/*
【问题9】查找出球队名称，和每个球队进球人数
【问题分析】球队名称(球队信息表eteam)，和每个球队进球人数(进球信息表goal：
按球队名称分组统计count(teamname)
*/
select c.teamname, count(c.teamname)
from eteam as c inner join goal as b 
on c.id=b.teamid
group 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.matchid
group 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;

/*
【问题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.matchid
group by a.id,a.mdate,a.team1,a.team2
order by a.mdate,a.id,a.team1,a.team2;