一、表的加法/组合查询
1.union
union将两个表的数据按行合并在一起,两个表重复的数据只保留一个。
- course
- course1
合并:
2.union all
union all将两个表的数据按行合并在一起并保留重复行。
注意:作为集合运算对象的表的前提是列数要一致。运算结果会导致行的增减,但是不会导致列的增减。
二、表的联结
联结的分类:
1.交叉联结cross join
交叉联结又称笛卡尔积,交叉联结是对两张表中的全部记录进行交叉组合,结果中的记录数通常是两张表中行数的乘积。例如,表1有n条记录,表2有m条记录,如果将两张表交叉联结,就是将表1中的每一行和表2中的每一行两两合并在一起,结果就会有n*m条记录。
交叉联结是所有联结运算的基础,但是在实际业务中很少使用,一是因为其结果行数太多,需要花费大量的运算时间和高性能设备的支持;二是因为其结果实用价值不高。
2.内联结 inner join
内联结是查找出同时存在于两张表的数据。
实例:student和score这两张表相同的字段是“学号”,通过学号可以将两个表进行内联结。
3.左联结 left join
左联结可将from子句左侧的表中的数据全部取出来,与右边进行匹配,若右表无匹配项,则其值为空值Null。
如果两个表的数据有部分重叠,但是想要只取出from子句左侧的表独有的部分(如下图),则需要再加一个where子句进行限定。
比如对于下面student表和score表,想要只取出student表中独有的部分,也就是只取出学号为0002、0003、0004的信息。
4.右联结 right join
右联结可将from子句右侧的表中的数据全部取出来,与左边进行匹配,若左表无匹配项,则其值为空值Null。
如果两个表的数据有部分重叠,但是想要只取出from子句右表独有的部分(如下图),可以再加一个where子句进行限定。
select a.学号,a.姓名,b.课程号,b.成绩
from student as a right join score as b
on a.学号=b.学号
WHERE a.学号 is null
5.全联结 full join
全联结会返回左表和右表中的所有行,如果两个表之间有匹配,则进行合并;当没有匹配行时,另一个表中对应的值用空值Null填充。
需要注意的是,mysql是不支持全联结的,这里了解这个概念即可。
SQL运行顺序
1)先运行子查询
2)在每个查询语句里的运行顺序:先运行from,where,group by,having这些子句,然后运行select子句,最后运行order by,limit子句。
三、联结应用案例
案例1:查询所有学生的学号、姓名、选课数、总成绩
需要用到学生表与成绩表,这两张表通过学号联结。涉及到联结就要考虑用哪种联结呢?因为要查到每个学生的信息,也就是要保留学生表里的所有学号,所以要用左联结。
select
案例2:查询平均成绩大于60的所有学生的学号,姓名和平均成绩
select
案例3:查询学生的选课情况,信息包括:学号,姓名,课程号,课程名称
此题涉及三个表的联结,用到两个inner join
select
四、case表达式
CASE
case表达式的作用相当于一个条件判断的函数,用于判断每一行是否满足某一条件;如果满足when的判断表达式则继续运行then子句,然后case表达式结束,不会再运行后面的when子句了。如果未满足条件,则进行下一个when子句,如果直到最后的when子句也没有找到符合条件的数据,那么就会进行else后面的<表达式>。
注意事项:
(1)else 子句可以省略不写, 这时会默认else是空值,但是为了书写规范,尽量要写。
(2)最后的end不能省略。
(3)case表达式除了可以放在select子句中,还可以放在SQL的任意子句里面
实例1:对成绩表中的学生成绩进行及格或不及格的判断
-- 注意:case子句是select子句的一部分,因此不能忽略其前面的逗号
实例2:查询出每门课程的及格人数和不及格人数
-- 注意下面语句的运行顺序:先按照课程号分组,然后运行case表达式,再运行求和函数sum
实例3:使用分段[100-85],[85-70],[70-60],[<60] 来统计各科成绩,分别统计:各分段人数,课程号和课程名称
- 思路:
各分数段人数: case when 分情况讨论筛选出各分数段的学生,再用sum汇总及格人数
各分数段人数:用到score表 课程号和课程名称:用到course表 涉及到两个表查询所以要用联结。
分别统计课程号和课程名称的分数段情况 :按course表的课程号和课程名称分组
为什么要用两个列来分组:查询结果要求显示出课程号和课程名称,select里的列名只能是group by子句里的列名,为了让查询结果显示出课程号和课程名称,所以要在group by子句里加入课程名称。这里加入课程名称的前提是课程号与课程名称是一对一的关系,所以去掉课程名称与加上课程名称对分组结果都是没有影响的;若不是一对一关系,加入后会改变分组结果,这时就不能随便加了。
SELECT
三、SQLZOO平台The JOIN operation练习题
再补充一些比较好的联结的习题:SQLZOO平台More JOIN operations练习题
解法二:
SELECT
解法二:同上,用三表联结