一、表的加法不同连接的应用场景举例
在SQL中,通过union连接词实现多表相加
练习:如何合并两个表
union对于重复数据只保留其中一个:
union all会将重复数据都进行保留:
二、表的联结
当进行多个表查询的时候,需要通过联结进行。
在联结中,表和表之前是通过列产生对应关系的,联结是通过表和表之间的关系将两个表合并在一起的操作。
常用的联结包括:
- 交叉联结(笛卡尔积):将一张表中的每一行都与另一张表中的每一行合并在一起。交叉联结结果的行数是两张表中行数的乘积
2. 内联结:选取出同时存在于两张表中的数据,关键词on后面的表示两张表通过哪个列匹配产生关系从而联结起来的
3. 左联结:将左侧表中的数据全部取出来。两个表进行左联结的时候,会将左侧的表作为主表,主表中的数据全部读取出来。
如果想要如下图所示的效果该怎么办呢?
这是在左联结的基础上去掉了两张表中共同的地方,也就是去掉了两个圆圈的公共部分。在刚才左联结的sql语句中加入一个where子句,实现效果:
4. 右联结:将右侧表中的数据全部取出来。两个表进行右联结的时候,会将右侧的表作为主表,主表中的数据全部读取出来。
如果想要如下图所示的效果该怎么办呢?
这是在右联结的基础上去掉了两张表中共同的地方,也就是去掉了两个圆圈的公共部分。在刚才右联结的sql语句中加入一个where子句,实现效果:
5. 全联结
全联结的查询结果会返回左表和右表中的所有行,当某行和另一张表中的某行有匹配的时候,两个行进行合并;如果某一行和另一张表中没有匹配的行时,另一张表中的值对应的地方用空值来填充。
值得注意的是:MySQL不支持全连接
- 当实际工作业务中想要生成固定行数的表单,或者特别说明了要哪一张表里的全部数据的 时候,会使用左联结或右联结
- 其他情况用内连接来获取两张表的公共部分
各联结文氏图表达:
(字丑,见谅)
三、联结应用案例
利用联结解决实际业务中的问题
问题1:查询所有学生的学号、姓名、选课数目和总成绩
因为问题中需要查询的四个字段涉及到多个表(两张表),因此本题需要通过联结学生表和成绩表进行查询。
通过写分析思路(在各子句中写)来看如何把问题的每个部分对应到每个子句后面
注:用联结的时候要考虑到两个问题
a. 两个表如何联结?此题中两张表是通过学号来产生匹配关系的
b. 用哪种联结方式?因为要查到每个学生的信息,所以要保留左边学生表里的所有学号,因此用左联结。
把分析思路里的各个子句写成对应的sql,如下图:
问题2:查询平均成绩大于85的所有学生的学号、姓名和平均成绩
问题可拆解为下图中两部分
接下来把分析思路里的各个子句写成对应的sql,首先写出第一部分对应的sql,如下图:
在上面提到的分析思路中,第一步是查询出所有学生的学号、姓名和平均成绩。第二步是要平均成绩大于85分,所以现在应该把第二部分的条件加到上面的sql语句中去。而平均成绩是分组以后得到的,所以需要给分组结果加上条件,平均成绩大于85分用having子句。如下图:
问题3:查询学生的选课情况:学号,姓名,课程号,课程名称
三张表联结的sql语句,其实就是在from子句中加入了多个join两两相连
四、case表达式
使用case表达式可以帮助解决复杂的查询问题,当有多种情况需要条件判断的时候可以使用case表达式。case表达式的作用相当于一个进行条件判断的函数,用来判断某一行是否满足某一个条件。
case表达式的sql语句:
其中when子句的判断表达式用来判断某行数据是否满足某个条件,
- 如果符合条件,就运行后面的then子句,则case表达式运行到此终止,不会运行后面的when子句了。
- 如果不符合某个条件,则进入下一个when子句。
- 如果直到最后的when子句也没有找到符合条件的数据,则会运行else中的表达式。
问题1:查询出每门课程的及格人数和不及格人数
问题2:使用分段【100-85】,【85-70】,【70-60】,【<60】来统计各科成绩,分别统计各分数段人数,课程号和课程名称
五、sql join练习题
以上就是第五节课程《多表查询》的内容。