本文介绍了SQLite3中如何通过UNION进行表的加法操作，详细阐述了表的联结及其应用案例。此外，讲解了CASE表达式的使用，并提供了Sqlzoo练习题，帮助读者深入理解GROUP BY与HAVING子句，以及聚合函数如COUNT, AVG, MIN, MAX和SUM的运用。还涉及了JOIN操作，特别是LEFT JOIN在处理缺失数据时的作用。"
113223227,10293805,MySQL中的数据库链接FEDERATED引擎,"['MySQL', '数据库链接']
1.表的加法
表的加法是指按行，将表合并在一起，通过关键字union实现。现在，这里有两张格式一样的表，想要把两张表的数据合并起来，该如何操作？
select 课程号,课程名称
from course
union -- 会删除重复的数据
select 课程号,课程名称
from course1;
select 课程号,课程名称
from course
union all -- 保留重复值
select 课程号,课程名称
from course1;
2.表的联结
/*
交叉联结，也叫笛卡尔积cross join
将表中的每一行与另一张表中的每一行合并在一起
如扑克牌：13张牌表示A,2,3...Q,K以及4种花色♠️,♥️,♣️,♦️,共4*13=52种花色
交叉联结是一切联结的基础，其他联结都是加了其他的筛选条件
*/
-- 内联结，查询出同时存在于两张表的数据，见结果1
select a.学号,a.姓名,b.课程号
from student as a inner join score as b
on a.学号 = b.学号; -- 通过学号这一列将两张表联结起来
-- 左联结，取出左侧表中全部的数据，右边的表中只选出与左边表相同的学号的行，left join，见结果2
select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号;
-- 返回只在a表中存在学号，在b表中不存在学号的行，见结果3
select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is null; -- null只能用用is/is not来判断，不能用=
-- 右联结，right join，见结果4
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号 = b.学号;
-- 返回只在b表中存在学号，在a表中不存在学号的行，见结果5
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号 = b.学号
where a.学号 is null;
-- 全联结，返回左，右表中所有行，若母行和另一表中有匹配时，该行合并，若无匹配的行，则用null填充
-- 但是mysql不支持全联结
3.联结应用案例
-- 查询所有学生的学号，姓名，选课数，总成绩
/*
1.学号，姓名➡️student表
2.选课数(每个学生的选课数目：score表，按学号分组，对课程号计数count)
3.总成绩(每个学生的总成绩：score表，按学号分组，对成绩求和sum)
*/
-- 见结果1
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;
-- 查询平均成绩大于85的所有学生的学号，姓名和平均成绩
/*
1.查询出所有学生的学号，姓名，平均成绩：学号，姓名在student表；平均成绩(每个学生的平均成绩：在score表，按学号分组，平均成绩：avg(成绩))
2.平均成绩大于85
*/
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
where avg(b.成绩)>85 -- 此处错误，where子句后面不能使用汇总函数
group by a.学号
-- 见结果2
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(成绩)>85;
-- 查询学生的选课情况：学号，姓名，课程号，课程名称
-- 1.学号，姓名 from student
-- 2.课程号，课程名称 from course
-- 见结果3
select a.学号,a.姓名,c.课程号,c.课程名称
from student a inner join score b on a.学号 = b.学号 -- student a就是student as a
inner join course c on b.课程号 = c.课程号;
4.case表达式
/* 就是if...else...的逻辑判断
case表达式
case when <判断表达式> then <表达式>
case when <判断表达式> then <表达式>
case when <判断表达式> then <表达式>
...
else <表达式>
end
*/
select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
when 成绩 < 60 then '不及格'
else null -- 可以不写，默认为null
end) as 是否及格 -- end不能省略
from score;
-- 查询出每门课程的及格人数和不及格人数
/*
1.查询出每门课程的人数
select 课程号,count(学号) as 人数
from score
group by 课程号;
2.上面代码每门课的人数的代码改为case表达式
*/
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 课程号;
/*
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：
各分数段人数，课程号和课程名称
*/
select a.课程号,b.课程名称,
sum(case when 成绩 between 100 and 85 then 1
else 0
end) as '[85-100]',
sum(case when 成绩<85 and 成绩>70 then 1
else 0
end) as '[70-85]',
sum(case when 成绩<70 and 成绩>60 then 1
else 0
end) as '[60-70]',
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.课程名称; -- select里的列名只能是group by里的列名，当用多个列来分组时，这几个列的值全部相同时才算一组
Sqlzoo练习题
Group by&having部分
当在select子句中同时出现列名&汇总函数，之后必须使用<group by 列名>的进行分组，否则会报错；若select子句之后只有汇总函数，则不需要group by 分组。
Aggregates function部分
MySQL中的聚合函数有：
1.count() 所有记录数
2.count(*)所有非null记录数
3.avg() 某一列平均值
4.min() 某一列最小值
5.max() 某一列最大值
6.sum() 某一列总和
Join部分
前面的题都是使用的inner join内联结(简写为join)。
第13题使用的是左连接(left join)，由于需要查询所有比赛的比分，所以需要连接表game与表goal(两张表均可见此张截图最上方)，而goal表相当于只记录了有比分的比赛，对于0:0的比赛无法反映在goal这张表上；因此，需要使用左连接(left join)即取game的整张表，来与goal表连接，当整场比赛均无进球时，通过sum(case when(...) ) score1&sum(case when(...) ) score2可得比分为0:0并记录在查询所得的新表中。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
