一、集合运算
集合运算：对满足同一规则的记录进行的加减等四则运算。
UNION以行
（纵向）为单位进行操作，而
JOIN以列
（横向）为单位进行操作。
1.表的加法（UNION）
两个表的结构相同，存放的数据不一样，可以使用表的加法将两个表的数据按行加在一起。
如果要在结果中保留重复行，只需要在UNION后面添加ALL关键字。
注意事项：
- 表的列数必须相同；
- 列的类型必须一致；
- 可以使用任何SELECT语句，但是ORDER BY子句只能在最后使用一次。
其他：
- 表的减法：EXCEPT/MINUS（差集）；
- 选取表的公共部分：INTERSCECT（交集）；
-
上面两个语法与UNION相同，只是MYSQL暂不支持。这些运算的特征是
以行方向
为单位进行操作，不会导致列数的增减。
2.表的联结（JOIN）
表的联结就是通过表和表之间的关系，将表合并到一起。也就是将其他表中的列添加过来，进行
添加列
的运算。
常用联结：
1）内联结（INNER JOIN）
查找出同时存在于两张表中的数据，是应用最广泛的联结运算。
要点：
- 对原表命名别名不是必须的，但是表名太长可能会影响SQL语句的可读性，因此建议使用别名；
- ON是必不可少的，起到和WHERE相同的作用，书写时须在FROM和WHERE之间；需要指定多个键时，也可以使用AND和OR。
2）左联结（LEFT JOIN）和右联结（RIGHT JOIN）
左联结
：以左表为主表，将左侧表中的数据全部取出来。
右联结
：将右侧表中的数据全部取出来
3）交叉联结（CROSS JOIN）-笛卡尔积
将表中的每一行都与另一张表的每一行合并在一起。在实际业务中几乎不会使用。
4）全联结（FULL JOIN）
返回两张表的所有行，如某一行有匹配就数据合并，没有就用空值填充。MYSQL不支持全联结。
5）多表联结
可以使用INNER JOIN进行添加，原则上联结表的数量是没有限制的。
总结
二、联结应用案例
1.查询所有学生的学号、姓名、选课数和总成绩
2.查询平均成绩大于85分的所有学生的学号、姓名和平均成绩
3.查询学生的选课情况：学号，姓名，课程号，课程名称
三、CASE表达式
CASE表达式用来解决复杂的查询问题，是一种条件判断的函数。CASE表达式会从最初的WHEN子句中的<判断表达式>求值开始执行。如果符合某个条件，就运行后面的THEN子句，若不符合条件，继续运行下一条WHEN子句，直到返回ELSE中的表达式，执行终止。CASE表达式是
SQL中极其重要
的功能。
CASE WHEN <判断表达式>THEN<表达式>
WHEN <判断表达式>THEN<表达式>
WHEN <判断表达式>THEN<表达式>…
ELSE<表达式>
END
举例：
1.在查询结果中显示成绩是否及格
2.查询出每门课程的及格人数和不及格人数
注意事项：
- CASE表达式中的ELSE子句可以省略，但是防止漏读，还是建议写出来；
- END不能省略；
- CASE表达式可以写在SQL中任意地方。
3.使用分段[100-85]、[85-70]、[70-60]、[<60]来统计各科成绩。分别统计：课程号、课程名称和各分数段人数
四、练习：
SQLZOO
The JOIN operation
1.show thematchidandplayername for all goals scored by Germany. To identify German players, check for:teamid = 'GER'.
SELECT matchid,player FROM goal
WHERE teamid = 'GER';
2.Show id, stadium, team1, team2 for just game 1012.
SELECT id,stadium,team1,team2
FROM game
WHERE id = '1012';
3.show the player, teamid, stadium and mdate for every German goal.
SELECT player,teamid,stadium,mdate
FROM game INNER JOIN goal ON id=matchid
WHERE teamid = 'GER';
或 SELECT b.player,b.teamid,a.stadium,a.mdate
FROM game AS a INNER JOIN goal AS b
ON a.id=b.matchid
WHERE teamid = 'GER';
4.Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'.
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.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10.
SELECT a.player, a.teamid, b.coach, a.gtime
FROM goal AS a INNER JOIN eteam AS b
ON a.teamid=b.id
WHERE a.gtime<=10;
6.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
SELECT a.mdate, b.teamname
FROM game AS a INNER JOIN eteam AS b
ON a.team1 = b.id
WHERE coach = 'Fernando Santos';
7.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'.
SELECT a.player
FROM goal AS a INNER JOIN game AS b
ON a.matchid = b.id
WHERE stadium = 'National Stadium, Warsaw';
8.show the name of all players who scored a goal against Germany.
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.Show teamname and the total number of goals scored.
SELECT c.teamname, COUNT(b.player)
FROM goal AS b INNER JOIN eteam AS c ON b.teamid = c.id
GROUP BY c.teamname;
10.Show the stadium and the number of goals scored in each stadium.
SELECT a.stadium, COUNT(b.player)
FROM game AS a INNER JOIN goal AS b ON a.id = b.matchid
GROUP BY a.stadium;
11.For every match involving 'POL', show the matchid, date and the number of goals scored.
SELECT b.matchid,a.mdate,COUNT(b.player)
FROM game AS a INNER JOIN goal AS b ON b.matchid = a.id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY b.matchid,a.mdate;
12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'.
SELECT b.matchid,a.mdate,COUNT(b.player)
FROM game AS a INNER JOIN goal AS b ON b.matchid = a.id
WHERE b.teamid = 'GER'
GROUP BY b.matchid,a.mdate;
13.List every match with the goals scored by each team as shown. Sort your result 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,
a.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 b.matchid = a.id
GROUP BY a.id,a.mdate,a.team1,a.team2
ORDER BY a.mdate,a.id,a.team1,a.team2;