表的加法表的联结联结应用案例case表达式sqlzoo
一、表的加法
使用场景：列数一致、列名一致的多张表格，合并在一张表上。使用union合并。
union：重复数据会删除
union all：保留所有行
二、表的联结
交叉联结（cross join）、内联结（inner join）、左联结（left join）、右联结（right join）、全联结（full join）
（1）交叉联结：笛卡尔积
（2）内联结：查找出两张表同时出现的数据
SELECT a.学号,a.姓名,b.课程号from student a INNER JOIN score bon a.学号=b.学号
（3）左联结：查找出左侧表的数据
SELECT a.学号,a.姓名,b.课程号from student a LEFT JOIN score bon a.学号=b.学号
-- 去除两张表的共同部分SELECT a.学号,a.姓名,b.课程号from student a LEFT JOIN score bon a.学号=b.学号where b.学号 is null
（4）右联结：查找出右侧表的数据
SELECT a.学号,a.姓名,b.课程号FROM student a RIGHT JOIN score bon a.学号=b.学号
-- 去除两张表共同部分SELECT a.学号,a.姓名,b.课程号FROM student a RIGHT JOIN score bon a.学号=b.学号where a.学号 is null
（5）全联结：返回左右两张表所有行（mysql不支持全联结）
三、联结应用案例
例1：查询所有学生的学号、姓名、选课数、总成绩
SELECT a.学号,a.姓名,count(b.课程号) 选课数,sum(b.成绩) 总成绩FROM student a left join score b on a.学号=b.学号group by a.学号
例2：查询平均大于85的所有学生的学号，姓名和平均成绩
SELECT a.学号,a.姓名,avg(b.成绩) 平均成绩FROM student a left join score b on a.学号=b.学号group by a.学号having avg(b.成绩)>85
例3：查询学生的选课情况，学号、姓名、课程号以及课程名称
select a.学号,a.姓名,c.课程号,c.课程名称from student a inner join score b on a.学号=b.学号inner join course c om b.课程号=c.课程号
四、case表达式
例1：查询学生成绩是否及格
SELECT 学号,课程号,成绩,(case when 成绩>=60 then '及格'when 成绩<60 then '不及格'else nullend) 是否及格from score
例2：查询每门课程及格与不及格的人数
SELECT 课程号,sum(case when 成绩>=60 then 1 else 0 end) 及格人数,sum(case when 成绩<60 then 1 else 0 end) 不及格人数from scoregroup by 课程号
例3：使用分段100-85、85-70、70-60、<60来统计各科成绩，分别统计：各分数段人数，课程和课程名称。
SELECT a.课程号,b.课程名称,sum(case when 成绩<60 then 1 else 0 end) '<60',sum(case when 成绩>=60 then 1 else 0 end) '60-70',sum(case when 成绩>=70 then 1 else 0 end) '70-85',sum(case when 成绩>=85 then 1 else 0 end) '85-100'from score a RIGHT JOIN course b on a.课程号=b.课程号group by a.课程号,b.课程名称
注意事项：
else子句可省略不写，省略则为空值，建议不省略end不能省略不写case表达式可以写到sql语句的任意子句中
五、sqlzoo
1.賽事編號matchid和球員名player,該球員代表德國隊Germany入球的。要找出德國隊球員
select matchid,playerfrom goalwhere teamid='ger';
2.由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。
留意在goal表格中的欄位matchid，是對應表格game的欄位id。我們可以在表格game中找出賽事1012的資料。
只顯示賽事1012的 id, stadium, team1, team2
SELECT id,stadium,team1,team2FROM game where id=1012;
3.以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player,teamid,stadium,mdateFROM game JOIN goal ON (id=matchid)where teamid='ger';
4.列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select team1,team2,playerfrom game join goalon id=matchidwhere player like'mario%';
5.列出每場球賽中首10分鐘gtime<=10有入球的球員player, 隊伍teamid, 教練coach, 入球時間gtime
SELECT player, teamid, coach,gtimeFROM goal join eteam on teamid=idWHERE gtime<=10;
6.列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
select mdate,teamnamefrom game join eteamon team1=eteam.idwhere coach='Fernando Santos';
7.列出場館 'National Stadium, Warsaw'的入球球員。
select playerfrom goal join gameon id=matchidwhere stadium= 'National Stadium, Warsaw';
8.以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
修改它，只列出全部賽事，射入德國龍門的球員名字。
SELECT distinct playerFROM game JOIN goal ON matchid = id WHERE (team1='GER' and teamid=team2) or (team2='GER' and teamid=team1);
9.列出隊伍名稱teamname和該隊入球總數
SELECT teamname, count(*)FROM eteam JOIN goal ON id=teamidgroup by teamname;
10.列出場館名和在該場館的入球數字。
select stadium,count(*)from goal join gameon matchid=idgroup by stadium;
11.每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
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'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
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 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 score2FROM game as a left JOIN goal as b ON a.id=b.matchid group by a.id,a.mdate,a.team1,a.team2order by a.mdate,a.id,a.team1,a.team2;
