一. 课堂练习以及知识点
表的加法
含义：将两个表按行合并在一起，用union将两个表中的结果合并在一起
案例
select 课程号,课程名称
from course
union
select 课程号,课程名称
from course1
用union all可以保留重复行
select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1
表的联结
联结(join)-表与表之间通过列产生对应关系，联结是将表与表之间关系合并在一起的操作
交叉联结表1的第一行和表2的两行合并在一起，形成两行数据，交叉结果是两个表中行数的乘积；
交叉连接在实际业务中用的比较少，因为结果太多，花费大量的成本运算；
内联结含义：同时存在两张表的数据，即交集
用inner join联结
关键点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.`学号`;
全联结全联结用full join 关键词联结；
得到两个表中的所有行，若匹配不到则返回空值，合并在一起；
(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,来统计各科成绩，分别统计：各个分数段人数，课程号，课程名称
翻译题目根据分段，对成绩进行判断
需要对不同分数段分组，计算每个分数段人数
用课程表查出课程名称，课程号
思路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练习
数据来源/wiki/The_JOIN_operation/zhThe JOIN operation/zh
#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;
