三张表关联查询统计_SQL多表查询

真实的数据操作中，通常要获取的信息横跨多个表格，这个时候就要用到SQL多表查询，建立表格和表格的关联关系。

一、表的加法

即两张表加在一起。使用union 关键字，将两个表格查询的结果相加，在相加时会将重复的数据删除。如果想保留重复行，使用union all关键字，会将所有记录相加。如下图，清晰的显示两张课程表相加之后的结果。

练习：合并两个表course和course1:

SQL语句：

运行结果：

二、表的联结

通过各个表中对应列的数据之间的关系，建立表和表的关系联结。比如student、course、teacher、score四张表联结关系如下所示：

根据联结关系的不同，表联结分为：

1. 交叉联结（cross join）：表一的每一行与表二的每一行合并在一起产生数据，交叉联结结果的行数是两张表行数的乘积。交叉联结在业务中用的比较少，常见是扑克牌的13张数字牌与花色牌结合，总共52张牌，加上大小王，总共54张牌。交叉联结是所有其它联结的基础。
2. 内联结（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语句的复杂度提高，解决问题的维度增加，同时也方便为查询结果设置种种条件。这一章重点是要掌握分析思路并多加练习。