1. 表的加法 (union)
快速建立相同结构表(以course为例):
course ---》右键单击---》复制表---》结构和数据---》对新表重命名---》改数据
1)union不保留重复行
2)union all 保留重复行
2. 表的联接
1) 交叉联接(cross join):实际业务中应用比较少,但是其他联接的基础
2) 内联接(inner join):交集
取数图例:
SQL语句:
select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号 = b.学号;
3) 左联接(left join)
取数图例:
SQL语句:
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号;
SQL语句:去掉交集部分
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is Null;
4) 右联接(right join)
取数图例:
SQL语句:
select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号 = b.学号;
SQL语句:去掉交集部分
select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号 = b.学号
where a.学号 is Null;
5) 全联接(full join):MySQL不支持全连接
取数图例:
联接总结图
3. 联接应用案例
问题1:查询所有学生的学号、姓名、选课数、总成绩
分析:
1)学号、姓名(student表)
2)选课数(每个学生的选课数、score表)
3)总成绩(每个学生的总成绩,score表,按学号分组,对成绩求和)
见图:
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.学号;
问题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;
问题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.课程号;
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;
问题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 ()
步骤见图:
注意事项:
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.课程名称; (这里加上课程名称对结果无影响)
SQLZOO练习
1.列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員,要檢查: teamid = 'GER'
select matchid, player
from goal
where teamid = 'GER';
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';
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';
以上结果与答案不符,可能数据有改变。
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%';
名字的模糊查询。
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的联接条件不能忘写,答案显示为错误,但是其给出的答案只是三次行改变了相对位置。
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';
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';
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
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;
如果不用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;
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;
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;
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;
此处自己问题在于,group by和order by 后之前没有添加g1.id,g1.team1,g1.team2;添加多个分组可避免同一天有多个比赛时,不知会只有一场比赛。