一、知识点
表的加法
联结
什么是联结
联结(JOIN)就是将其他表中的列添加进来，进行’添加列‘的集合运算
UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的
CASE表达式
CASE表达式
CASE WHEN <判断表达式> THEN <表达式>
WHEN <判断表达式> THEN <表达式>
WHEN <判断表达式> THEN <表达式>
...
ELSE <表达式>
END
二、练习：
1、查询所有学生的学号、姓名、选课数、总成绩
分析思路
select 查询结果 [学号，姓名，选课数：count，总成绩:sum]
from 从哪张表中查找数据[学生表student，成绩表score，通过学号联结，左联结]
group by 分组[学号]
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的所有学生学号、姓名和平均成绩
select 查询结果 [学号，姓名，平均成绩:avg]
from 从哪张表中查找数据[学生表student，成绩表score，通过学号联结，左联结]
group by 分组[学号]
having 平均成绩 > 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、查询学生的选课情况：学号、姓名、课程号、课程名称
select 查询结果 [学号，姓名, 课程号, 课程名称]
from 从哪张表中查找数据[学生表student，成绩表score，课程表course 通过学号、课程号联结，左联结]；
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.课程号;
4、查询出每门课程的及格人数和不及格人数
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 课程号;
5、使用分段[100-85]，[85-70]，[70-60]，[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
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练习
练习地址
1. 在进球表（goal）中查找德国球队（teamid = 'GER'）进球的比赛编号（matchid），进球球员姓名（player）
select matchid, player
from goal
where teamid = 'GER';
2. 在比赛信息表（game）查找比赛编号1012的信息id, stadium, team1, team2
select id,stadium,team1,team2
from game
where id = 1012;
3. 查找德国队进球球员姓名，球队编号（在进球信息表goal）, 比赛地点,比赛日期（在比赛信息表game）
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 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分钟
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'作为教练的比赛日期，球队编号有哪些？
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'有哪些进球球员？
select b.player
from game as a inner join goal as b on a.id=b.matchid
where a.stadium = 'National Stadium, Warsaw';
8.射入德国球门的球员姓名
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. 查找出球队名称，和每个球队进球人数
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. 查找出所有比赛地点，每个比赛地点的进球数
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,a.mdate;
12. 对于德国队'GER'得分的每场比赛，显示比赛编号，比赛日期和'GER'得分的进球数
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进球数）
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;
网友评论