本次内容包括:
- 表的加法
- 表的联结
- 联结应用案例
- case表达式
一、表的加法 union
表的加法是指将两个表(相同列数、相同数据类型)中的数据汇总在一起。
举例:现在有两张表:course、course1
SELECT 课程号,课程名称
from course
union
SELECT 课程号,课程名称
from course1
使用union时,两张表中重复的行会只记为一行;如果需要保留重复行,可以使用union all
SELECT 课程号,课程名称
from course
union all
SELECT 课程号,课程名称
from course1
二、表的联结 join
(1)交叉联结(笛卡尔积)
指将表1中的每一行与表2中的每行进行合并,因此合并后得到的行数是两张表行数的乘积。
例子:扑克牌13个牌号和4种花色的交叉联结
交叉联结在实际应用中比较少,因为结果行数较多,运算量比较大,实际使用价值也不大
(2)内联结 inner join
内联结用于查找出同时存在于两张表中的数据。
例子:已有表格student1和score1
两张表相同的字段是“学号”,通过这个可以将两个表进行内联结。
select a.学号,a.姓名,b.课程号,b.成绩
from student1 as a inner join score1 as b
on a.学号=b.学号 -- 标示出两个表示通过“学号”这个字段连接起来的
(3)左联结 left join
左联结可将from子句左侧的表中的数据全部取出来,与右边进行匹配,若右表无匹配项,则其值为空值Null。
select a.学号,a.姓名,b.课程号,b.成绩
from student1 as a inner join score1 as b
on a.学号=b.学号
如果两个表的数据有部分重叠,但是只取出左表独有的部分,可以再加一个子句对SQL进行限定。
select a.学号,a.姓名,b.课程号
from student1 as a left join score1 as b
on a.学号=b.学号
where b.学号 is Null
(4)右联结 right join
右联结可将from子句右侧的表中的数据全部取出来,与左边进行匹配,若左表无匹配项,则其值为空值Null。
select a.学号,a.姓名,b.课程号,b.成绩
from student1 as a right join score1 as b
on a.学号=b.学号
如果两个表的数据有部分重叠,但是只取出右表独有的部分,可以再加一个子句对SQL进行限定。
select a.学号,a.姓名,b.课程号,b.成绩
from student1 as a right join score1 as b
on a.学号=b.学号
WHERE a.学号 is null
(5)全联结 full join
全联结会返回左表和右表中的所有行,如果两个表之间有匹配,则进行合并;否则无匹配部分用空值表示。
需要注意的是,mysql是不支持全联结的,了解这个概念即可。
联结的所有情况用下图进行总结:
三、联结应用案例
问题1:查询所有学生的学号、姓名、选课数、总成绩
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student1 as a left join score1 as b
on a.学号=b.学号
group by 学号
问题2:查询平均成绩大于85的所有学生的学号,姓名和平均成绩
SELECT a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student1 as a left join score1 as b
on a.学号=b.学号
GROUP BY 学号
having avg(b.成绩)>85
问题3:查询学生的选课情况,信息包括:学号,姓名,课程号,课程名称
此题涉及三个表的联结,用到两个inner join,注意句子的书写格式
select a.学号,a.姓名,b.课程号,c.课程名称
from student as a inner join score as b on a.学号=b.学号
inner join course as c on b.课程号=c.课程号
四、case表达式
作用相当于一个条件判断的函数,用于判断每一行是否满足某一条件;如何满足则继续运行then子句,运行完后结束。如果未满足条件,则继续运行,直至找到满足条件的语句或者最终结束。
当要用到条件判断时,需要用到case表达式。
注:
(1)else 子句可以省略不写, 但为了书写规范,尽量要写。
(2)最后的end不能省略。
(3)case表达式除了可以放在select子句中,还可以放在SQL的任意子句里面
实例1:对成绩表中的学生成绩进行及格或不及格的判断
-- 规范写法:case子句其实是select子句的一部分,因此不能忽略其前面的逗号
SELECT 学号,课程号,成绩,
(case
when 成绩>=60 then '及格'
when 成绩<60 then '不及格'
else null
end) as 是否及格
from score
实例2:查询出每门课程的及格人数和不及格人数
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:使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:各分段人数,课程号和课程名称
select a.课程号,b.课程名称,
sum(case when a.成绩 BETWEEN 85 and 100 then 1 else 0
end) as '[100-85]',
sum(case when a.成绩<85 and a.成绩>=70 then 1 else 0
end) as '[85-70]',
sum(case when a.成绩<70 and a.成绩>=60 then 1 else 0
end) as '[70-60]',
sum(case when a.成绩<60 then 1 else 0
end) as '[<60]'
from score as a RIGHT JOIN course as b
on a.课程号=b.课程号
GROUP BY a.课程号,b.课程名称
-- 当使用多个列来分组时,这几个列的值完全相同时才算作一组
-- 此题需注意在[100-85]这样的字段两端加上单引号,使之成为字符串,否则运行错误
课后练习:
来自SQLZOO网站的JOIN部分。
注:本题只需查出球员名称,因此需要用distinct对重复进球的球员去重