mysql多表联合查询_MYSQL多表查询

一. 课堂练习以及知识点

表的加法

含义：将两个表按行合并在一起，用union将两个表中的结果合并在一起

案例

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

用union all可以保留重复行

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

表的联结

联结（join）-表与表之间通过列产生对应关系，联结是将表与表之间关系合并在一起的操作

交叉联结

1. 表1的第一行和表2的两行合并在一起，形成两行数据，交叉结果是两个表中行数的乘积；
2. 交叉连接在实际业务中用的比较少，因为结果太多，花费大量的成本运算；

内联结

1. 含义：同时存在两张表的数据，即交集
2. 用inner join联结
3. 关键点

• from 子句中，同时使用两张表，需要给表取别名
• inner join 表示内连接
• on 表示哪个表中的哪个列匹配，表示匹配关系

案例

#查询学生的姓名，学号和课程号

SELECT s.姓名,s.学号,sc.课程号 
FROM student AS s 
INNER JOIN score AS sc 
ON s.`学号`=sc.`学号`;

左联结/右联结（以下内容以左联结为例）

用left join联结，left左边的表为主表，右边的为从表（right join右边的是主表，左边的是从表）

案例

SELECT s.姓名,s.学号,sc.`课程号` 
FROM student AS s 
LEFT  JOIN score AS sc 
ON s.`学号`=sc.`学号`;

SELECT s.姓名,s.学号,sc.`课程号` 
FROM student AS s 
LEFT  JOIN score AS sc 
ON s.`学号`=sc.`学号` 
WHERE sc.`课程号` IS NULL;

SELECT s.姓名,s.学号,sc.`课程号` 
FROM student AS s 
RIGHT JOIN score AS sc 
ON s.`学号`=sc.`学号`;

全联结

1. 全联结用full join 关键词联结；
2. 得到两个表中的所有行，若匹配不到则返回空值，合并在一起；

（mysql中不支持该联结，暂不详细说明）

总结

当实际业务中需要生成固定数据的表单或者需要指定数据，可以用左/右联结，其他情况用内联结，取两个表的公共部分。

应用案例

用sql解决问题的步骤：翻译题目，分析思路，sql语句

案例1：查询所有学生的学号，姓名，选课数，总成绩

翻译题目：

• 学号，姓名在student表
• 选课数（在score表中，计算count（课程号)，对每个学号分组）
• 总成绩（在score表中，计算sum(成绩),对每个学号分组）

分析思路

• FROM学生表，成绩表，因为要查到所有学生的信息，因此应该让student做主表，通过学号用左连接
• 没有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.`学号`;

因为要查询所有学生信息，因此应该按主表即a表的学号分组

案例2 查询平均成绩大于85的所有学生的学号，姓名，平均成绩

翻译题目以及思路

• FROM学生表，成绩表，因为查询所有学生的信息，则学生表为主表，通过学号左连接
• 没有where条件
• group by:在成绩表中对学号分组，计算每个学生的平均成绩
• having: 对平均成绩比较，筛选出大于85分的
• order by:没有排序
• limit:没有指定行

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;

案例3 查询所有学生的选课情况：学号，姓名，课程号，课程名称

翻译题目

• 在学生表中找学号，姓名
• 在课表中找课程号，课程名称

思路

from 学生表和课程表，但是两个表没有关系条件联结，所以需要通过成绩表进行联结，学生表和成绩表通过学号联结，课程表和成绩表通过课程号联结

sql语句

SELECT s.学号,s.姓名,c.课程号,c.课程名称 
FROM student AS s 
LEFT JOIN score AS sc 
ON s.`学号`=sc.`学号` 
LEFT JOIN course AS c 
ON c.`课程号`=sc.`课程号`;

case表达式

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

案例1： 在查询结果中显示成绩是否及格

SELECT 学号,课程号,成绩,( 
CASE WHEN 成绩<=60 THEN '不及格' 
WHEN 成绩>60 AND 成绩<=80 THEN '中等' 
WHEN 成绩>80 THEN '优秀' ELSE '未知' END) AS 是否及格
FROM score; 

案例2：查找出每门课程的及格人数和不及格人数

思路：先判断出每门课程及格或者不及格，及格为1，不及格为0，然后对课程号分组统计sum

sql语句：

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 课程号; 

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

翻译题目

1. 根据分段，对成绩进行判断
2. 需要对不同分数段分组，计算每个分数段人数
3. 用课程表查出课程名称，课程号

思路

• FROM 因为需要成绩则用成绩表，需要课程名称则需要课程表，因为统计的是各个科目，则课程表做主表，通过课程号左连接，课程表放在左边，如果用右联结，course放在右边
• 没有where条件
• group by:对每个分数段进行分组，统计总人数
• having：没有
• order by:没有排序
• limit:没有指定行

sql语句

SELECT s.`课程号`,c.`课程名称`, SUM(
CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS '[<60]', 
SUM(CASE WHEN 成绩>=60 AND 成绩<70 THEN 1 ELSE 0 END) AS '[60-70]', 
SUM(CASE WHEN 成绩>=70 AND 成绩<85 THEN 1 ELSE 0 END) AS '[70-85]', 
SUM(CASE WHEN 成绩>=85 AND 成绩<=100 THEN 1 ELSE 0 END) AS '[85-100]' 
FROM course AS c 
LEFT JOIN score AS s ON s.`课程号`=c.`课程号` 
GROUP BY s.`课程号`,c.`课程名称`; 

注意：select子句中的被分组的列应该是group by子句中的，因此都要放在group by后边；group by后边用多个列分组时，这几个列的值全部相同才算一组，比如‘01’-语文 是一组，‘01’-数学 是另一组，该案例中课程号和课程名称是一对一的关系，多列分组不影响结果。

注意事项

• else可以不写，默认为空值，但是为了方便理解，建议书写养成良好习惯
• 最后的end不能省略
• case表达式可以写在任何子句中

什么情况下使用case表达式：当有多种情况需要判断，则需要CASE表达式

二. SQLzoo练习

数据来源

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

The JOIN operation/zh

The JOIN operation/zh​sqlzoo.net

#1列出 賽事編號matchid和球員名player,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查:teamid = 'GER'

SELECT matchid,player
FROM goal
WHERE teamid='GER';

#2由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。留意在 goal 表格中的欄位 matchid ，是對應表格game的欄位id。我們可以在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2
FROM game
WHERE id='1012';

#3顯示每一個德國入球的球員名，隊伍名，場館和日期。

SELECT gl.player,gl.teamid,gm.stadium,gm.mdate
FROM game as gm
INNER JOIN goal  as gl
ON gm.id=gl.matchid
where gl.teamid='GER';

#4列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

select gm.team1,gm.team2,gl.player
from game as gm
inner join goal as gl
on gm.id=gl.matchid
where gl.player like 'Mario%';

#5列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT gl.player, gl.teamid, et.coach,gl.gtime
FROM goal as gl
inner join eteam as et
on gl.teamid=et.id
WHERE gtime<=10;

#6列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。

思路：赛事日期和team1在在game表中，队伍名和教练在eteam中,game表中的team1 和eteam表中的id可以关联，用内连接。

select gm.mdate,et.teamname
from game as gm
inner join  eteam as et 
on gm.team1=et.id
where et.coach='Fernando Santos';

#7列出場館 'National Stadium, Warsaw'的入球球員。

思路：player在goal表中，场馆信息在game中，game表中的id和goal表的matchid可以关联，用内联结

select player
from game as gm
inner join goal as gl
on gm.id=gl.matchid
where gm.stadium= 'National Stadium, Warsaw';

#8找出德國-希臘Germany-Greece 的八強賽事的入球,只列出全部賽事，射入德國龍門的球員名字。

提醒：一个球员可能多次将球射入德国的球门，因此需要用select distinct 去除重复值；其他球队射入德国的门，首先德国需要参与比赛，所以要么team1='GER’要么team2='GER', 根据比赛规则，德国不可能将球射入自己的门中，则入球球队不能是德国，即goal.teamid<>'GER' （可能不知道足球规则做错）;题目中要求全部赛事，用left join

SELECT distinct gl.player
FROM game as gm
left join goal as gl
ON gl.matchid =gm.id 
WHERE gl.teamid<>'GER'
and
(team1='GER' or team2='GER');

#9列出隊伍名稱teamname和該隊入球總數

思路：球队名称在eteam表中，入球信息在goal表中，goal.teamid=eteam.id；入球总数可以将eteam.teamname分组统计各个teamname出现次数作为入球数量.

SELECT et.teamname,count(et.teamname)
FROM eteam as et
inner JOIN goal as gl
ON et.id=gl.teamid
group by et.teamname;

#10列出場館名和在該場館的入球數字。

select gm.stadium,count(gl.player)
from game as gm
inner join goal as gl
on gm.id=gl.matchid
group by gm.stadium;

#11每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

思路：FROM---赛事信息和入球信息分别在game 和goal 表中，两个表可以通过goal.matchid=game.id内连接关联；

where---条件是由pol参与，则team1为pol或者team2为pol;

group---入球信息通过对每个赛事分组统计count（player），因此需要用到group by子句；

SELECT gl.matchid,gm.mdate,count(gl.player)
FROM game as gm
inner JOIN goal as gl
ON gm.id = gl.matchid 
WHERE gm.team1 = 'POL' OR gm.team2 = 'POL'
group by gm.id;

#12每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

思路：上一题类似，但是条件多了一个德国入球数字，即德国参赛且入球的数字，那么加上goal.teamid为GER这条件即可.每场比赛可能是一场赛事进行多天，则分组时应该是group by gm.id,gm.mdate

select gl.matchid,gm.mdate,count(gl.player)
from game as gm
inner join goal as gl
on gm.id=gl.matchid
where (gm.team1 = 'GER' OR gm.team2 = 'GER') and gl.teamid='GER'
group by gm.id,gm.mdate;

#13查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）,并且按照mdate, matchid, team1 and team2排序。

翻译题目

• 查找game中的mdate,team1,team2
• 统计team1和team2的进球数字 作为score1和score2显示，按比赛分组

分析思路

• from---game表和goal表，通过id 和matchi联结，因为是所有比赛，所以用left join
• where---没有
• group by---需要将比赛分组，统计进球数，用多条件判断sum(case when...)
• order by---按题意要求排序

select gm.mdate,gm.team1,
sum(case when gl.teamid=gm.team1 then 1 else 0 end) as score1,
gm.team2,sum(case when gl.teamid=gm.team2 then 1 else 0 end) as score2
from game as gm
left join goal as gl
on gm.id=gl.matchid
group by gm.id,gm.mdate,gm.team1,gm.team2
order by gm.mdate,gm.id,gm.team1,gm.team2;