sql查询4分钟_SQL练习4-多表查询

1 表的加法

1）union 自动去重

2）union all 保留重复

2 表的联结

1）内联结 inner join

根据关系（on）内容取交集：同时存在于两个表中的数据。

2）左联结 left join

取左侧数据，右侧选择与左侧一样的数据，通过where语句选择是否包含交集部分。

3）右联结 right join

取右侧数据，左侧选择与右侧一样的数据，通过where语句选择是否包含交集部分。

4）全联结 full join（mySQL不支持）

3 联结应用案例

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

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

3）查询学生的选课情况：学号、姓名、课程号、课程名称（三表联结）

4 case表达式

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

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

5 sqlzoo 练习

1) 列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的

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) 顯示每一個德國入球的球員名，隊伍名，場館和日期

select a.player, a.teamid,b.stadium,b.mdate
from goal as a left join game as b 
on a.matchid = b.id
where a.teamid = 'GER';

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

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分鐘gtime<=10有入球的球員player, 隊伍teamid, 教練coach, 入球時間gtime

分析思路：

1. 每场球赛，首十分钟入球：goal表，条件：gtime<10 ;
2. 入球的球员：goal表，player
3. 入球队伍ID：goal表，teamid
4. 教练：eteam表，coach
5. 表联结：goal表、eteam表，通过teamID和ID，内联结

select b.player,b.teamid,c.coach,b.gtime
from goal as b inner join eteam as c
on b.teamid = c.id
where b.gtime <= 10;

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

分析思路：

1. 'Fernando Santos'教练：eteam表，条件：coach='Fernando Santos'；
2. 参赛日期：game表，mdate；
3. 队伍名：eteam表，teamname；
4. 表联结：game表、eteam表，通过team1与ID，实现内联结

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表，条件：stadium='National Stadium, Warsaw'；
2. 入球球员：goal表，player；
3. 表联结：game、goal，通过id、matchid，实现内联结

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. 球员名字：goal表，player；
2. 表联结：game、goal，通过id、matchid，实现内联结；
3. 射入德国龙门：条件：1）goal中teamid不等于'GER'；2）对应game中 team1 or team2 有'GER'；
4. distinct去掉重复球员

select distinct b.player
from game as a inner join goal as b
on a.id = b.matchid
where b.teamid <> 'GER'
           and (a.team1='GER' or a.team2='GER');

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

1. 队伍名称：eteam表，teamname
2. 入球总数：goal表，count(gtime)
3. 每个队伍：分组，goal表的teamid
4. 表联结：goal、eteam，通过teamid、id，实现内联结

select c.teamname,count(b.gtime)
from goal as b inner join eteam as c
on b.teamid = c.id
group by c.teamname;

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

1. 场馆名：game表，stadium
2. 入球数字：goal表，count（入球时间）
3. 每个场馆入球数字：分组：game表stadium
4. 表联结：game、goal表，通过id、matchid，实现左联结（所有场馆）

select a.stadium,count(gtime)
from game as a left join goal as b
on a.id = b.matchid
group by a.stadium;

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

1. 每一场'POL'参与的赛事：game表，team1='POL' 或 team2='POL'
2. 赛事编号：goal表，matchid
3. 日期：game表，mdate
4. 每场赛事入球数字：根据matchid、mdate分组，计算入球总数count(gtime)
5. 表联结：game、goal，通过id、matchid，实现内联结

select a.id,a.mdate,count(b.gtime)
from game as a inner join goal as b
on a.id = b.matchid
where a.team1='POL' or a.team2='POL'
group  by a.id, a.mdate;

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

1. 赛事编号：goal表，matchid
2. 日期：game表，mdate
3. 德国入球数字：条件筛选德国队伍，teamid = 'GER'；根据matchid分组，计算入球总数count(gtime)
4. 表联结：game、goal，通过id、matchid，实现内联结

select a.id, a.mdate, count(gtime)
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) 列出每一场比赛以及每支球队的进球数，结果按照mdate, matchid, team1 and team2排序

1. 每一场比赛每支球队的进球数：每场比赛有两支球队，team1、team2，分别汇总team1、team2 进球数；
   - 构想联结后的表格，根据赛事分组后，当team1=teamid时，表示team1在该场赛事进球1次，使用case语句计数求和，得到该场比赛team1的进球总和：
   sum(case when team1=goal.matchid then 1 else 0 end) as 'score1'
   sum(case when team2=goal.matchid then 1 else 0 end) as 'score2'
   - 每一场比赛：按赛事id、mdate、team1、team2分组
2. 表联结：game、goal，通过id、matchid，实现左联结（所有赛事）
3. 排序：order by mdate, matchid, team1 and team2

select a.mdate,a.team1,
sum(case when a.team1=b.teamid then 1 else 0 end) as 'score1',
team2,
sum(case when a.team2=b.teamid then 1 else 0 end) as '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;