一、大纲
二、实战
1.表的加法-union
提前准备好结构相同的course表和course1表
course表
course1
(1)将两个course和course1表的内容合并，不保留重复值
select
运行结果1-去重union
(2)将两个course和course1表的内容合并，保留重复值
select *
from course
union all
select *
from course1;
运行结果2-union all不去重
2.表的联结-交叉联结cross join+内联结inner join+左连接left join+右联结right join+全联结full join
student表
score表
（1）内联结
#内联结inner join
#查询学生的学号，姓名和课程号
select a.学号,a.姓名,b.课程号
from student as a
inner join
score as b
on a.学号=b.学号
inner join... on ...
(2)左联结
select a.学号,a.姓名,b.课程号
from student as a
left join
score as b
on a.学号=b.学号
（3）左联结,保留a表中的特有行
select a.学号,a.姓名,b.课程号
from student as a
left join
score as b
on a.学号=b.学号
where b.学号 is null;
（4）右联结
select a.学号,a.姓名,b.课程号
from student as a
right join
score as b
on a.学号=b.学号
（5）右联结-删除左表中的空值
select a.学号,a.姓名,b.课程号
from student as a
right join
score as b
on a.学号=b.学号
where a.学号 is null;
(6)全联结full join
mysql不支持全联结。
3.联结案例：
（1）#查询所有学生的学号,姓名,学科数目,总成绩
step1:学号，姓名来自student表，直接查询
学科数目和总成绩来自score表，需要左联结
需要对学生进行分组，
之后count（课程号）得到学科数目，sum(成绩)得到总成绩
step2:
select 学号,姓名,选课数,总成绩
from （student表和score表，通过学号左联结，获得课程号计数和成绩求和
）
where 条件【没有】
group by 学号
having 分组条件【没有】
order by 排序条件【没有】
limit 限制显示条数【没有】
step3:
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分的所有学生的学号、姓名和平均成绩
step1:学号+姓名【student】
平均成绩【score表】：需要左联结，按照学号分组，avg(成绩)
然后where子句选出平均成绩大于85的学生。
step2:
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from (表student，score通过学号左联结)
where 条件【没有】
group by 学号
having avg(b.成绩)>85;
order by 排序【没有】
limit 限制显示行数【没有】
step3:
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)查询学生的选课情况：显示学号，姓名，课程号，课程名称
step1:学号，姓名【student】
课程号，课程名称【course】
step2：select 学号，姓名，课程号，课程名称
from 表【student和score通过学号左联结，score和course表通过课程号内联结】
where 条件【没有】
group by 分组条件【没有】
having 分组条件【没有】
order by 排序条件【没有】
limit 限制显示条数【没有】
step3:
select a.学号,a.姓名,c.课程号,c.课程名称
from student as a left join score as b on a.学号=b.学号
inner join course as c on b.课程号=c.课程号;
4.case表达式
(1)查询学生的成绩是及格还是不及格
(2)查询出每门课程的及格人数和不及格人数
step2:在score表利用课程号对课程进行分组，对成绩进行评定，及格（>=60）,不及格(<60),然后统计人数。
step3:
select 课程号,及格人数，不及格人数
from score
group by 课程号
及格人数：
sum(case when 成绩>=60 then 1
else 0
end) as 及格人数
不及格人数：
sum(case when 成绩<60 then 1
else 0
end) as 不及格人数
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）使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计
各分数段人数、课程号和课程名称
step1：课程号，课程名称【course】
分数段【score表和course表右联结，使用sum统计人数，case来分段】
step3:
select a.课程号,b.课程名称,
sum( case when 成绩 between 100 and 85 then 1 else 0
end) as '[100-85]',
sum( case when 成绩 between 70 and 85 then 1 else 0
end) as '[85-70]',
sum( case when 成绩 between 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.课程名称;
拓展练习：
https://sqlzoo.net/wiki/The_JOIN_operation/zhsqlzoo.net
练习结果如下：
SELECT matchid,player FROM goal
WHERE teamid = 'GER'
SELECT id,stadium,team1,team2
FROM game
where id=1012
SELECT goal.player,goal.teamid,game.stadium,game.mdate
FROM game JOIN goal ON (game.id=goal.matchid)
WHERE teamid = 'GER'
SELECT a.team1,a.team2,b.player
FROM game as a JOIN goal as b ON (a.id=b.matchid)
WHERE b.player LIKE 'Mario%'
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;
select a.mdate,c.teamname
from game as a JOIN eteam as c ON (a.team1=c.id)
where c.coach='Fernando Santos' ;
select b.player
from game as a inner JOIN goal as b on a.id=b.matchid
where a.stadium='National Stadium, Warsaw'
SELECT distinct b.player
FROM game as a JOIN goal as b ON b.matchid = a.id
WHERE (b.teamid=a.team1 and a.team2='GER'
or b.teamid=a.team2 and a.team1='GER')
SELECT c.teamname, count(b.teamid)
FROM eteam as c right JOIN goal as b ON c.id=b.teamid
group by b.teamid
ORDER BY teamname
select a.stadium,count(a.id)
from game as a JOIN goal as b ON b.matchid = a.id
group by a.stadium
SELECT b.matchid,a.mdate,count(a.id)
from game as a inner JOIN goal as b ON b.matchid = a.id
WHERE (a.team1 = 'POL' OR a.team2 = 'POL')
group by a.id
SELECT b.matchid,a.mdate,count(a.id)
from game as a inner JOIN goal as b ON b.matchid = a.id
WHERE b.teamid='GER'
group by a.id
SELECT a.mdate,
a.team1,sum( CASE WHEN b.teamid=a.team1 THEN 1 ELSE 0 END)as score1,
a.team2,sum( CASE WHEN b.teamid=a.team2 THEN 1 ELSE 0 END)as score2
FROM game as a left JOIN goal as b ON b.matchid = a.id
group by a.mdate, b.matchid, a.team1,a.team2;
