![fa03419d9b4fc36a424e0f8841f36b32.png](https://img-blog.csdnimg.cn/img_convert/fa03419d9b4fc36a424e0f8841f36b32.png)
1. 表的加法 (union)
快速建立相同结构表(以course为例):
course ---》右键单击---》复制表---》结构和数据---》对新表重命名---》改数据
![048e6dcbaf2a8e093ed489845957ba0c.png](https://img-blog.csdnimg.cn/img_convert/048e6dcbaf2a8e093ed489845957ba0c.png)
1)union不保留重复行
![845a6f47654ee65fc09e594dd139b6bc.png](https://img-blog.csdnimg.cn/img_convert/845a6f47654ee65fc09e594dd139b6bc.png)
2)union all 保留重复行
![e4530b89bd7136ecda26e568020503ca.png](https://img-blog.csdnimg.cn/img_convert/e4530b89bd7136ecda26e568020503ca.png)
2. 表的联接
![816ba01c1ca44085b27cb2760fffd694.png](https://img-blog.csdnimg.cn/img_convert/816ba01c1ca44085b27cb2760fffd694.png)
1) 交叉联接(cross join):实际业务中应用比较少,但是其他联接的基础
![0d42a3ac7ee7bb9ccd56f874ddb3193b.png](https://img-blog.csdnimg.cn/img_convert/0d42a3ac7ee7bb9ccd56f874ddb3193b.png)
2) 内联接(inner join):交集
取数图例:
![942a2cc05c3bc793c97c62f114fada38.png](https://img-blog.csdnimg.cn/img_convert/942a2cc05c3bc793c97c62f114fada38.png)
![c500e144de1f116d7ce66148c82ad6c5.png](https://img-blog.csdnimg.cn/img_convert/c500e144de1f116d7ce66148c82ad6c5.png)
SQL语句:
select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号 = b.学号;
![f2000539459f65b4fa83be9586b62111.png](https://img-blog.csdnimg.cn/img_convert/f2000539459f65b4fa83be9586b62111.png)
3) 左联接(left join)
取数图例:
![2b46072e50a58a234fa1e7fe8f24e459.png](https://img-blog.csdnimg.cn/img_convert/2b46072e50a58a234fa1e7fe8f24e459.png)
![4b095944d2477195f9411d64cef6132c.png](https://img-blog.csdnimg.cn/img_convert/4b095944d2477195f9411d64cef6132c.png)
SQL语句:
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号;
![df1dfd50308487ffec8535b93c563a9b.png](https://img-blog.csdnimg.cn/img_convert/df1dfd50308487ffec8535b93c563a9b.png)
SQL语句:去掉交集部分
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is Null;
![82bc8a586532b5c27c31d0693061f296.png](https://img-blog.csdnimg.cn/img_convert/82bc8a586532b5c27c31d0693061f296.png)
4) 右联接(right join)
取数图例:
![304ceb3eb9422589797b2a8f1c8bc0f8.png](https://img-blog.csdnimg.cn/img_convert/304ceb3eb9422589797b2a8f1c8bc0f8.png)
![44dd7d30348c23ecbe076c69125cb4e0.png](https://img-blog.csdnimg.cn/img_convert/44dd7d30348c23ecbe076c69125cb4e0.png)
SQL语句:
select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号 = b.学号;
![f668262fdef350dbe69ee73683f11bac.png](https://img-blog.csdnimg.cn/img_convert/f668262fdef350dbe69ee73683f11bac.png)
SQL语句:去掉交集部分
select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号 = b.学号
where a.学号 is Null;
![9386912520f7fe9b097980f36a2b8942.png](https://img-blog.csdnimg.cn/img_convert/9386912520f7fe9b097980f36a2b8942.png)
5) 全联接(full join):MySQL不支持全连接
取数图例:
![8d184e35d74e9c5abfe53500287885e6.png](https://img-blog.csdnimg.cn/img_convert/8d184e35d74e9c5abfe53500287885e6.png)
![ed8d77d36ae8d6b2dafe916201c816f2.png](https://img-blog.csdnimg.cn/img_convert/ed8d77d36ae8d6b2dafe916201c816f2.png)
联接总结图
![54cb288a7cc7f2fea99e5b5aa3ce5728.png](https://img-blog.csdnimg.cn/img_convert/54cb288a7cc7f2fea99e5b5aa3ce5728.png)
3. 联接应用案例
问题1:查询所有学生的学号、姓名、选课数、总成绩
分析:
1)学号、姓名(student表)
2)选课数(每个学生的选课数、score表)
3)总成绩(每个学生的总成绩,score表,按学号分组,对成绩求和)
见图:
![ec07ef663da266f08cc8ca92ef06f5ca.png](https://img-blog.csdnimg.cn/img_convert/ec07ef663da266f08cc8ca92ef06f5ca.png)
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.学号;
![e443363284c5b9b6e159de98720e93bd.png](https://img-blog.csdnimg.cn/img_convert/e443363284c5b9b6e159de98720e93bd.png)
问题2:查询平均成绩大于85的所有学生的学号、姓名、平均成绩
分析:
1) 查询出所有学生的学号、姓名、平均成绩、学号、姓名(student表)
平均成绩(score表),按学号分组求平均成绩
2) 平均成绩 > 85
SQL语句
select a.学号, a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
where avg(b.成绩) > 85
group by a.学号
having avg(b.成绩) > 85;
![b98074f93066ceb905efbb4e72b77683.png](https://img-blog.csdnimg.cn/img_convert/b98074f93066ceb905efbb4e72b77683.png)
问题3:查询学生的选课情况:学号、姓名、课程号、课程名称
分析:
1) 学号、姓名(student表)
课程号(score表)
2) 课程名称(course表)
SQL语句
select a.学号, a.姓名, b.课程号, c.课程名称
from student as a left inner join score as b
on a.学号 = b.学号
left inner join course as c
on b.课程号 = c.课程号;
![7cfe716ed5ceebea84a00a59265b527f.png](https://img-blog.csdnimg.cn/img_convert/7cfe716ed5ceebea84a00a59265b527f.png)
4. case 表达式
case when (判断表达式) then (表达式)
when (判断表达式) then (表达式)
when (判断表达式) then (表达式)
…
else(表达式)
end
例1:
select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
when 成绩 < 60 then '不及格'
else
end) as 是否及格
from score;
![43d48d7907681f783325b76ccb5a07d0.png](https://img-blog.csdnimg.cn/img_convert/43d48d7907681f783325b76ccb5a07d0.png)
问题1:查询每门课程的及格人数和不及格人数
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 课程号;
注:sum()不应写成sum ()
步骤见图:
![d3fe2051aae1046cf48f496672eef203.png](https://img-blog.csdnimg.cn/img_convert/d3fe2051aae1046cf48f496672eef203.png)
![b44aba8cb55454baae372d8858319f7d.png](https://img-blog.csdnimg.cn/img_convert/b44aba8cb55454baae372d8858319f7d.png)
注意事项:
1. else 子句可以默认不写,但是为了完整性,保留
2. end 不可省略不写
3. 该表达式可写在任何子句中
问题2:使用分段[100-85], [85-70], [70-60], [<60]来统计各科成绩,分别统计:各各分段人数、课程号和课程名称
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.课程名称; (这里加上课程名称对结果无影响)
![6e1073aab2bbc7b9603b613166443cd6.png](https://img-blog.csdnimg.cn/img_convert/6e1073aab2bbc7b9603b613166443cd6.png)
SQLZOO练习
1.列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員,要檢查: teamid = 'GER'
select matchid, player
from goal
where teamid = 'GER';
![8ebf779a490272aafa276d2c51ef6d69.png](https://img-blog.csdnimg.cn/img_convert/8ebf779a490272aafa276d2c51ef6d69.png)
2. 在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2
select game.id, game.stadium, game.team1, game.team2
from game left join goal
on game.id = goal.matchid
where game.id = '1012' and goal.matchid = '1012'
and goal.player = 'Lars Bender';
![6c898cbd4db735568ee398a2585b1edb.png](https://img-blog.csdnimg.cn/img_convert/6c898cbd4db735568ee398a2585b1edb.png)
3.顯示每一個德國入球的球員名,隊伍名,場館和日期。
select goal.player, goal.teamid, game.stadium, game.mdate
from game inner join goal (之前用left join,但此处为两表共有的部分)
on game.id = goal.matchid
where goal.teamid = 'Gre';
![7e6dc621c6ff77ded4546e198b36149b.png](https://img-blog.csdnimg.cn/img_convert/7e6dc621c6ff77ded4546e198b36149b.png)
以上结果与答案不符,可能数据有改变。
4. 列出球員名字叫Mario (player LIKE 'Mario%'
)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select g1.team1, g1.team2, g2.player
from game as g1 right join goal as g2
on g1.id = g2.matchid
where g2.player like 'Mario%';
名字的模糊查询。
![03383da627cc33e3689f51400950cdee.png](https://img-blog.csdnimg.cn/img_convert/03383da627cc33e3689f51400950cdee.png)
5. 列出每場球賽中首10分鐘gtime<=10
有入球的球員 player
, 隊伍teamid
, 教練coach
, 入球時間gtime
select g.player,g.teamid,e.coach,g.gtime
from goal as g left join eteam as e
on g.teamid = e.id
where g.gtime <= 10;
指定on的联接条件不能忘写,答案显示为错误,但是其给出的答案只是三次行改变了相对位置。
![e85a8b3c58064099f8ff1d26faa60bad.png](https://img-blog.csdnimg.cn/img_convert/e85a8b3c58064099f8ff1d26faa60bad.png)
6. 列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期,和隊伍名。
select g.mdate, e.teamname
from game as g left join eteam as e
on e.id = g.team1
where e.coach = 'Fernando Santos';
![2d005f90f4184626b312094da1a675ff.png](https://img-blog.csdnimg.cn/img_convert/2d005f90f4184626b312094da1a675ff.png)
7. 列出場館 'National Stadium, Warsaw'的入球球員。
select g2.player
from game as g1 left join goal as g2
on g1.id = g2.matchid
where g1.stadium = 'National Stadium, Warsaw';
![affbf0dd0d856b85cabb1ade1e8d79ee.png](https://img-blog.csdnimg.cn/img_convert/affbf0dd0d856b85cabb1ade1e8d79ee.png)
8. 以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
修改它,只列出全部賽事,射入德國龍門的球員名字。
select player
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'Ger' or g1.team2 = 'Ger')
and g2.player not in (select player
from goal
where teamid = 'Ger');
和给出的答案有出入,原因在于选取的名字应该用distinct
![3998a794a908c7c2d62ea53a80f657d0.png](https://img-blog.csdnimg.cn/img_convert/3998a794a908c7c2d62ea53a80f657d0.png)
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.列出隊伍名稱teamname和該隊入球總數
select e.teamname, count(g.teamid)
from goal as g inner join eteam as e
on g.teamid = e.id
group by e.teamname;
![0c376724513adf08bbe21ec58189d2a1.png](https://img-blog.csdnimg.cn/img_convert/0c376724513adf08bbe21ec58189d2a1.png)
如果不用group by 分组,那么球队和进球数均为总数。
10. 列出場館名和在該場館的入球數字。
select g1.stadium, count(g2.matchid)
from game as g1 inner join goal as g2
on g1.id = g2.matchid
group by g1.stadium;
![19b0b7e62108ac55d18f88698faa702e.png](https://img-blog.csdnimg.cn/img_convert/19b0b7e62108ac55d18f88698faa702e.png)
11. 每一場波蘭'POL'有參與的賽事中,列出賽事編號 matchid, 日期date 和入球數字。
select g2.matchid, g1.mdate, count(g2.matchid)
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where g1.team1 = 'POL' or g1.team2 = 'POL'
group by g2.matchid;
![1c8a95477c0748a742fb7334eb82b359.png](https://img-blog.csdnimg.cn/img_convert/1c8a95477c0748a742fb7334eb82b359.png)
12. 每一場德國'GER'有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。
select g2.matchid, g1.mdate, count(g2.teamid = 'GER')
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'GER' or g1.team2 = 'GER')
and g2.teamid = 'GER'
group by g2.matchid;
![c968249d5d9b359cc1bcb5452158a222.png](https://img-blog.csdnimg.cn/img_convert/c968249d5d9b359cc1bcb5452158a222.png)
13. 每场比赛主客队进球数。
select g1.mdate, g1.team1,
sum(case when g1.team1 = g2.teamid then 1
else 0
end ) as score1,
g1.team2,
sum(case when g1.team2 = g2.teamid then 1
else 0
end ) as score2
from game as g1 left join goal as g2
on g1.id = g2.matchid
group by g1.mdate,g1.id,g1.team1,g1.team2
order by g1.mdate,g1.id,g1.team1,g1.team2;
![486b29dc6231cb0a31632b8dc9294a60.png](https://img-blog.csdnimg.cn/img_convert/486b29dc6231cb0a31632b8dc9294a60.png)
此处自己问题在于,group by和order by 后之前没有添加g1.id,g1.team1,g1.team2;添加多个分组可避免同一天有多个比赛时,不知会只有一场比赛。