【数据库和SQL学习笔记】5.SELECT查询3：多表查询、连接查询

数据库系统软件：SQL Server 2019 Express
操作系统：Windows 10

本节接着学select查询。
用到数据库备份： teaching.bak

多表查询

我们在现实中通常需要联合多张表才能得到所需要的查询结果。

如：查询教师授课信息，查询结果展示教师工号、姓名、课程名。
我们回顾一下表结构：

t_student (S#, Sname, Sex, Age, Major)
t_teacher (T#, Tname, Age, Title)
t_course (C#, Cname, T#)
t_student_course (S#, C#, Score)

可以看到需要 t_teacher、t_course 两张表才能完全展示教师授课信息。所以需要多表查询。

多表查询理论基础：笛卡尔积

在select语法中，各子句对应的行为：
SELECT A1,…,An 【列投影】
FROM R1,…Rn 【笛卡尔积】
WHERE F 【行选择】
我们做多表查询，是基于from子句中的笛卡尔积，将多张表拼接起来进行查询的。

我们来看看两张表的笛卡尔积长什么样：

可以看到笛卡尔积将 t_teacher中的三列 和 t_course中的三列 进行了全组合性的连接。
t_teacher共有10条记录，t_course共有10条记录，全组合就是共100条记录。
但是可以看到有些记录是没有意义的（比如图中的第2条记录）。

如何让记录有意义呢？
我们在笛卡尔积的基础上，做等值连接筛选，并去除重复的公共字段：

select t_teacher.*, C#, Cname
from t_teacher, t_course
where t_teacher.T# = t_course.T#

上面我们让 t_teacher和t_course 的T#列做等值连接（使用where子句），并取 t_teacher的所有字段（用t_teacher.＊表示），以及 t_course的C#, Cname字段。
这样，我们从笛卡尔积中筛选出了有效的拼接结果。

我们回到一开始的例子：

例：查询教师授课信息，查询结果展示教师工号、姓名、课程名。
我们就可以用笛卡尔积+等值连接，并取需要的列，就可以得到结果了。

select t_teacher.T#, Tname, Cname
from t_teacher, t_course
where t_teacher.T# = t_course.T#

多表查询特点总结

• 使用 FROM子句 罗列多张表
• 在 WHERE子句 中利用等值连接的方式进行筛选操作
• 表与表之间的公共字段，在引用时需要采用 表名.字段名 的方式
• 多表查询有时也会遇到需要非等值连接的情况，但是大部分应用场景采用的是等值连接的方式。

实例

表结构：
（我们在多表查询时一定要参照表结构进行查询）

t_student (S#, Sname, Sex, Age, Major)
t_teacher (T#, Tname, Age, Title)
t_course (C#, Cname, T#)
t_student_course (S#, C#, Score)

例1：查询学生选课信息，查询结果展示学号、姓名、课程号、成绩。

select t_student.S#, Sname, C#, Score
from t_student, t_student_course
where t_student.S# = t_student_course.S#

例2：查询学生选课信息，查询结果展示学号、姓名、课程号、课程名、成绩。
我们需要连接3张表，等值连接条件用AND连接。

select t_student.S#, Sname, t_student_course.C#, Cname, Score
from t_student, t_student_course, t_course
where t_student.S# = t_student_course.S# and t_student_course.C# = t_course.C#

例3：查询姓名为“李华”的学生选修的所有课程的课程名以及成绩。

select Cname, Score
from t_student, t_student_course, t_course
where t_student.S# = t_student_course.S# and 
	t_student_course.C# = t_course.C# and Sname='李华'

例4：查询所有姓李的学生的姓名、选修的所有课程的课程名以及成绩。

select Sname, Cname, Score
from t_student, t_student_course, t_course
where t_student.S# = t_student_course.S# and 
	t_student_course.C# = t_course.C# and Sname like '李%'

例5：查询选修了数据可课程的所有学生的学号和姓名。

sselect t_student.S#, Sname
from t_student, t_student_course, t_course
where t_student.S# = t_student_course.S# and
	t_student_course.C# = t_course.C# and Cname='数据库'

例6：查询同时选修了c001和c002课程的学生的学号。
要查同时存在有c001和c002两门课，思路是对 t_student_course 这张表自己对自己求笛卡尔积，然后再筛选。
求笛卡尔积时，因为不能直接在from中传入同名，用as关键词重命名一下。

select sc1.S#
from t_student_course as sc1, t_student_course as sc2
where sc1.S# = sc2.S# and sc1.C# = 'c001' and  sc2.C# = 'c002'

连接查询

之前多表查询是在from中做笛卡尔积后在where子句中给出连接条件。
我们还可以直接在from子句中书写连接操作，这样就不用在where中指定连接条件了。

连接查询操作包括：

• 内连接
• 自然连接
• 外连接（左外、右外、全外）
• 交叉连接

我们主要讲内连接和外连接。

实例

表结构：
t_student (S#, Sname, Sex, Age, Major)
t_teacher (T#, Tname, Age, Title)
t_course (C#, Cname, T#)
t_student_course (S#, C#, Score)

内连接：

例1：查询学生选课信息，查询结果展示学号、姓名、课程号、成绩。
①多表查询的解决方案：

select t_student.S#, Sname, C#, Score
from t_student, t_student_course
where t_student.S# = t_student_course.S#

②连接查询的解决方案：

select t_student.S#, Sname, C#, Score
from t_student inner join t_student_course on t_student.S# = t_student_course.S#

这里INNER JOIN为内连接。
默认为内连接，所以这里也可以将 INNER JOIN 直接写成 JOIN

例2：查询职称为 副教授 的教师的授课信息，展示教师姓名和课程名。

select Tname, Cname
from t_teacher inner join t_course on t_teacher.T# = t_course.T#
where Title='副教授'

例3：查询软件工程专业学生选课信息，展示学生姓名、课程名和成绩。
我们需要连接3张表。

select Sname, Cname, Score
from t_student inner join t_student_course on t_student.S# = t_student_course.S#
	inner join t_course on t_student_course.C# = t_course.C#
where Major = '软件工程'

外连接：
左外连接：
我们用例4来解释左外连接：

例4：查询每个学生的选课情况（含未选课学生信息）。

这里我们需要连接 t_student 和 t_student_course 两张表，但是因为有部分学生没有选课，如果我们用内连接将 S# 连接起来的话，没有选课的学生信息会漏掉。
因此，我们采用外连接：

select t_student.*, C#, Score
from t_student left outer join t_student_course 
	on t_student.S# = t_student_course.S#

这是一个左外连接，左边的表中的指定连接的信息有些并没有出现在右边的表中，右边的表自动填NULL。
也就是说，若左表多于右表，右边未匹配部分填NULL。
最终需要保全左表。

右外连接：
道理同左外连接，只不过右外连接需要保全的是右表。方向反了一下而已。
左表中不匹配的记录自动填NULL。

全外连接：
左表右表都保全。
即左表有右表匹配不到的记录，填NULL；右表有左表匹配不到的记录，也填NULL。