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

97ad77bc6aed346bfc01d19f1c634bf6.png

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

一、表的加法

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

30f8f823d0de82596f5d8c735e8c1ac1.png

aa573c8ce9be9d7327d6cd4ad3ee54d4.png

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

f1f089ea479e69aa6396abe33a0d06bc.png

e4a7c85cd608c0385c4318c8d1f2b49a.png

SQL语句:

fc290cc8ca2db4f3a0a5164656592021.png

运行结果:

5a2588626d8a313b56bc49c8abc17a7e.png

二、表的联结

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

d9f8355054eefb4932515146ff5f8894.png

根据联结关系的不同,表联结分为:

  1. 交叉联结(cross join):表一的每一行与表二的每一行合并在一起产生数据,交叉联结结果的行数是两张表行数的乘积。交叉联结在业务中用的比较少,常见是扑克牌的13张数字牌与花色牌结合,总共52张牌,加上大小王,总共54张牌。交叉联结是所有其它联结的基础。
  2. 内联结(inner join):查找出同时存在于两张表中的数据。如下文氏图表示内联结取出中间相同部分的红色数据。

2cc02940984560a7847927aae9c50eff.png

举例子:下图中student表和score表通过学号这一个相同列进行联结。通过SQL语句,以同时存在于两张表中的相同数据为桥梁进行内联结,查找出具有学号的数据行。

student表:

d8d29fae6e60ff7466c4661d626eb125.png

score表:

e265423e1e42091b927fcc58e4ee1784.png

SQL语句如下:

1cb9719c29332e040704366f9f1ed9bc.png

运行结果如下:

a6977e2b4a77191d802e03456761fa72.png

通过上述运行结果,取出同时存在两张表中学号为0001/0002/0003三个学号的数据行。

3、左联结 (left join):将左边表中数据全部取出来,右边表中只选出和左边表相同数据的数据行。用文氏图表示如下:

a140dd279cc3ec2516ff8a5be4cfd62f.png

还以上面的student表和score表格为例,运行两个表格左联结的SQL语句如下:

7a8897e0b47f0f51259c6cfb88907fdb.png

运行结果如下:

11b602b8748e0c38f273c44f90fdd30a.png

相比内联结,左联结将左边表格中的数据全部取出来,右边表中只取出了与左边表中共同的学号0001/0002/0003三个数据行,由于0004号学号在右边表中不存在,所以对应的课程号栏目的值为空值。

同样,将左联结的问题扩展,去掉两个表中属于公共部分的数据,如下文氏图所示:

189859d75ef3355fee4f6e0fcae90455.png

对应的SQL语句中联结数据行中的数据应取空值Null,以学生表和成绩表为例,SQL语句为:

37d0522b99b5425b251fa685d10ca014.png

注意这里b.学号取空值一定要用is null,不能用=null。

运行结果为:

190a57f671ac6d1ed0a68ca0218e27b5.png

4、右联结(right join):将右边表格中的数据全部取出来,左边表中只选出和右边表相同数据的数据行。用文氏图表示如下:

56c891c77d97326c2053de2f5e12c072.png

还以student表和score表格为例,运行两个表格右联结的SQL语句如下:

a8b762c51aafc7373cd5185fc69bf7f7.png

结果如下:

09e966612a1ef3f872489ec6b9b4c5e4.png

同样,将右联结的问题扩展,去掉两个表中属于公共部分的数据,如下文氏图所示:

8143c27d19fef909c6bcd3fc440f2af0.png

SQL语句如下:

c6b726985d23f79568faf81d529621eb.png

运行结果:

eb781ac985d202b088f1b408dbcda14a.png

5、全联结(full join):取出两个表中所有数据,用文氏图表示如下:

bf067effb2d2ebff47985d3e2d930b39.png

mysql不支持全联结,这里理解概念即可。

6、SQL运行顺序

1、先运行子查询
2、再运行select查询结果
3、最后对结果进行排序

fb3eaad0579ed684ab56ae994eb77803.png

三、联结应用案例

1、查询所有学生的学号、姓名、选课数、总成绩

分析思路:

1、从学生表student里查询学号、姓名。
2、选课数从score表里查询,计算每个学生的选课数,需要按学号分组,并用count 函数对课程号计数。
3、总成绩从score表里查询,计算每个学生的总成绩,需要按学号分组,并用sum 函数对成绩求和。
4、student表格和sore表格用左联结,原因是要查询所有学生的记录,需要将student表中全部学号数据行取出。

SQL语句:

bcc1e40889cfab5eb50e74f9162da2da.png

运行结果:

50a23b7482063870c3d190d2ebc0ef4a.png

2、查询平均成绩大于85分的所有学生的学号、姓名和平均成绩

分析思路:

1、从学生表student里查询学号、姓名。
2、从成绩表score里查询成绩,计算每个学生的平均成绩,需要按学号分组,并用avg函数计算平均成绩。并使用having 子句对分组结果设置>85分的条件。
3、student表格和sore表格用左联结,原因是要查询所有学生的记录,需要将student表中全部学号数据行取出。

SQL语句:

91edbfea3afbb465b3b4a7430c881a80.png

运行结果:

a1e3532c85bf818b4431d980f7a6a188.png

3、查询学生的选课情况,列出 学号、姓名、课程号以及课程名称

分析思路:

1、从学生表student里查询学号、姓名。
2、从成绩表score里查询课程号。
3、从课程表course里查询课程名称。
4、三张表联结,如果要取出所有学生的选课情况,student表与score表通过学号左联结,联结结果再与course表格通过课程号左联结成为一张表。如果仅仅想显示有选课的学生,则使用内联结。这里我们使用左联结显示全部学生的选课情况。

SQL语句:

934a10487c845c16a9b355dc96282e96.png

运行结果:

cc967a01a1d266de494998d537723909.png

四、case表达式

case表达式是一个条件判断函数,用来判断某行数据是否符合条件,如果符合条件就进入后面的运行条件,不符合就进入下一步。常用语法如下:

d458925645a74e0559f2b8fbf15852b6.png

1、查询出每门课程的及格人数和不及格人数

分析思路:

1、查询结果显示每门课程号,以及对应的及格人数和不及格人数。
2、在score表里对课程号进行分组,同时以课程成绩>=60作为条件进行判断,对判断结果进行计数。

SQL语句:

7f96bf238ec0e3163b3994a9fa6d5578.png

运行结果:

a542eb273d6b8ad4b34bcff1724c3192.png

2、使用分段【100-85】,【85-70】,【70-60】,【<60】来统计各科成绩,分别统计:各分段人数,课程号和课程名称。

分析思路:

1、查询结果要显示分段成绩统计人数、课程号和课程名称,涉及到course表和score表。
2、对score表中按课程号进行分类,对每一类课程,采用条件判断,分别统计各分段人数。
3、上述查询结果与course表进行左联结,取出对应课程号的课程名称。

SQL语句:

647df811e240cfd418a60d017ca41cd0.png

运行结果:

5b881ee1f5ddae324e1866c251c4f8ee.png

五、SQLZOO练习题

数据库有三个表格

movie电影(id编号, title电影名称, yr首影年份, director导演, budget制作费, gross票房收入)

actor演员(id编号, name姓名)

casting角色(movieid电影编号, actorid演员编号, ord角色次序)

角色次序代表第1主角是1, 第2主角是2...如此类推.

1、SQL语句

0b839c77cf49b56dcc1c1a78c2d8deeb.png

结果:

8f322de0feb84fcda3836dd06cc25fc5.png

2、SQL语句

494469901d4102da134730987c7c3b61.png

运行结果

eb233f3ecd73bed041d516093c8a31af.png

3、SQL 语句

12c62b138b7186bc4b1327795dd81237.png

885b225e6e5368dd00c9ae04b99ebb76.png

总结:本章重点学习了表的联结,走到这里才真正迈入了SQL查询高阶阶段,随着SQL语句的复杂度提高,解决问题的维度增加,同时也方便为查询结果设置种种条件。这一章重点是要掌握分析思路并多加练习。

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页