真实的数据操作中,通常要获取的信息横跨多个表格,这个时候就要用到SQL多表查询,建立表格和表格的关联关系。
一、表的加法
即两张表加在一起。使用union 关键字,将两个表格查询的结果相加,在相加时会将重复的数据删除。如果想保留重复行,使用union all关键字,会将所有记录相加。如下图,清晰的显示两张课程表相加之后的结果。
练习:合并两个表course和course1:
SQL语句:
运行结果:
二、表的联结
通过各个表中对应列的数据之间的关系,建立表和表的关系联结。比如student、course、teacher、score四张表联结关系如下所示:
根据联结关系的不同,表联结分为:
- 交叉联结(cross join):表一的每一行与表二的每一行合并在一起产生数据,交叉联结结果的行数是两张表行数的乘积。交叉联结在业务中用的比较少,常见是扑克牌的13张数字牌与花色牌结合,总共52张牌,加上大小王,总共54张牌。交叉联结是所有其它联结的基础。
- 内联结(inner join):查找出同时存在于两张表中的数据。如下文氏图表示内联结取出中间相同部分的红色数据。
举例子:下图中student表和score表通过学号这一个相同列进行联结。通过SQL语句,以同时存在于两张表中的相同数据为桥梁进行内联结,查找出具有学号的数据行。
student表:
score表:
SQL语句如下:
运行结果如下:
通过上述运行结果,取出同时存在两张表中学号为0001/0002/0003三个学号的数据行。
3、左联结 (left join):将左边表中数据全部取出来,右边表中只选出和左边表相同数据的数据行。用文氏图表示如下:
还以上面的student表和score表格为例,运行两个表格左联结的SQL语句如下:
运行结果如下:
相比内联结,左联结将左边表格中的数据全部取出来,右边表中只取出了与左边表中共同的学号0001/0002/0003三个数据行,由于0004号学号在右边表中不存在,所以对应的课程号栏目的值为空值。
同样,将左联结的问题扩展,去掉两个表中属于公共部分的数据,如下文氏图所示:
对应的SQL语句中联结数据行中的数据应取空值Null,以学生表和成绩表为例,SQL语句为:
注意这里b.学号取空值一定要用is null,不能用=null。
运行结果为:
4、右联结(right join):将右边表格中的数据全部取出来,左边表中只选出和右边表相同数据的数据行。用文氏图表示如下:
还以student表和score表格为例,运行两个表格右联结的SQL语句如下:
结果如下:
同样,将右联结的问题扩展,去掉两个表中属于公共部分的数据,如下文氏图所示:
SQL语句如下:
运行结果:
5、全联结(full join):取出两个表中所有数据,用文氏图表示如下:
mysql不支持全联结,这里理解概念即可。
6、SQL运行顺序
1、先运行子查询
2、再运行select查询结果
3、最后对结果进行排序
三、联结应用案例
1、查询所有学生的学号、姓名、选课数、总成绩
分析思路:
1、从学生表student里查询学号、姓名。
2、选课数从score表里查询,计算每个学生的选课数,需要按学号分组,并用count 函数对课程号计数。
3、总成绩从score表里查询,计算每个学生的总成绩,需要按学号分组,并用sum 函数对成绩求和。
4、student表格和sore表格用左联结,原因是要查询所有学生的记录,需要将student表中全部学号数据行取出。
SQL语句:
运行结果:
2、查询平均成绩大于85分的所有学生的学号、姓名和平均成绩
分析思路:
1、从学生表student里查询学号、姓名。
2、从成绩表score里查询成绩,计算每个学生的平均成绩,需要按学号分组,并用avg函数计算平均成绩。并使用having 子句对分组结果设置>85分的条件。
3、student表格和sore表格用左联结,原因是要查询所有学生的记录,需要将student表中全部学号数据行取出。
SQL语句:
运行结果:
3、查询学生的选课情况,列出 学号、姓名、课程号以及课程名称
分析思路:
1、从学生表student里查询学号、姓名。
2、从成绩表score里查询课程号。
3、从课程表course里查询课程名称。
4、三张表联结,如果要取出所有学生的选课情况,student表与score表通过学号左联结,联结结果再与course表格通过课程号左联结成为一张表。如果仅仅想显示有选课的学生,则使用内联结。这里我们使用左联结显示全部学生的选课情况。
SQL语句:
运行结果:
四、case表达式
case表达式是一个条件判断函数,用来判断某行数据是否符合条件,如果符合条件就进入后面的运行条件,不符合就进入下一步。常用语法如下:
1、查询出每门课程的及格人数和不及格人数
分析思路:
1、查询结果显示每门课程号,以及对应的及格人数和不及格人数。
2、在score表里对课程号进行分组,同时以课程成绩>=60作为条件进行判断,对判断结果进行计数。
SQL语句:
运行结果:
2、使用分段【100-85】,【85-70】,【70-60】,【<60】来统计各科成绩,分别统计:各分段人数,课程号和课程名称。
分析思路:
1、查询结果要显示分段成绩统计人数、课程号和课程名称,涉及到course表和score表。
2、对score表中按课程号进行分类,对每一类课程,采用条件判断,分别统计各分段人数。
3、上述查询结果与course表进行左联结,取出对应课程号的课程名称。
SQL语句:
运行结果:
五、SQLZOO练习题
数据库有三个表格
movie电影(id编号, title电影名称, yr首影年份, director导演, budget制作费, gross票房收入)
actor演员(id编号, name姓名)
casting角色(movieid电影编号, actorid演员编号, ord角色次序)
角色次序代表第1主角是1, 第2主角是2...如此类推.
1、SQL语句
结果:
2、SQL语句
运行结果
3、SQL 语句
总结:本章重点学习了表的联结,走到这里才真正迈入了SQL查询高阶阶段,随着SQL语句的复杂度提高,解决问题的维度增加,同时也方便为查询结果设置种种条件。这一章重点是要掌握分析思路并多加练习。