数据库应用技术课程辅导52

数据库应用技术课程辅导5-2

第 5 章数据操作语言之多表查询

5.1.3 多表查询

多表查询是指一个查询同时涉及两个或两个以上的表。多表查询是关系数据库中最主要的查询，多表查询需要对表进行连接操作，连接操作主要包括内连接、外连接和交叉连接等。

5.1.3.1 内连接

内连接的语法格式为：

FROM 表1 [ INNER ] JOIN 表2 ON <连接条件>

在连接条件中指明两个表按什么条件进行连接，连接条件中的比较运算符称为连接谓词。连接条件的一般格式为：

[<表名1.>]<列名1> <比较运算符> [<表名2.>]<列名2>

注意：进行比较运算的列必须是语义相同的列。

例1．查询每个学生及其所选课的详细信息。

由于学生基本信息存放在Student表中，学生选课信息存放在SC表中，因此这个查询实际涉及到两个表，这两个表之间进行连接的条件是两个表中的Sno相等。

SELECT * FROM Student INNER JOIN SC

ON Student.Sno = SC.Sno -- 将Student与 SC连接起来

例2．去掉例1中的重复列。

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno

例3．查询通信工程系学生的修课情况，列出学生的名字、所修课的课程号和成绩。

SELECT Sname, Cno, Grade

FROM Student JOIN SC ON Student.Sno = SC.Sno

WHERE Sdept = '通信工程系'

可以为表提供别名，为表取别名是在FROM子句中实现的。其格式为：

<原表名> [ AS ] <表别名>

例4．查询“信息管理系”选修“计算机文化学”课程的信息，列出学生姓名、课程名和成绩。

SELECT Sname, Cname, Grade

FROM Student s JOIN SC ON s.Sno = SC. Sno

JOIN Course c ON o = o

WHERE Sdept = '信息管理系' AND Cname = '计算机文化学'

注意：此查询涉及了三张表（系信息“信息管理系”在Student表中，课程信息“计算机文化学”在Course表中，“成绩”信息在SC表中）。每连接一张表，就需要用一个JOIN子句。

例5．查询选了数据结构课程的学生姓名和所在系。

SELECT Sname, Sdept FROM Student S JOIN SC

ON S.Sno = SC. Sno

JOIN Course C ON o = o

WHERE Cname = '数据结构'

注意，在这个查询语句中，虽然所要查询的列和数据的筛选条件均与SC表无关，但这里还是用了三张表进行连接，原因是Student表和Course表没有可以进行连接的列（语义相同的列），因此，这两张表的连接必须借助于第三张表：SC表。

例6．有分组的多表连接查询。查询每个系的学生考试平均成绩。

SELECT Sdept, AVG(grade) as AverageGrade

FROM student S JOIN SC ON S.Sno = SC.Sno

GROUP BY Sdept

5.1.3.2 自连接

自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以通过为表取别名的方法将其在逻辑上分为两张表。

使用自连接时必须为表取不同的别名，使之在逻辑上成为两张表。

例1．查询与刘晨在同一个系学习的学生姓名和所在系。

分析此查询的实现过程：首先应该找到刘晨在哪个系学习（在Student表中，可以将这个表称为S1表），然后再找出此系的所有其他学生（在Student表中，将这个表称为S2表），S1表和S2表的连接条件是两个表的系（Sdept）相同。那么，实现此查询的SQL语句为：SELECT S2.Sname, S2.Sdept

FROM Student S1 JOIN Student S2

ON S1.Sdept = S2.Sdept -- 是同一个系的学生

WHERE S1.Sname = '刘晨' -- S1表作为查询条件表

AND S2.Sname != '刘晨' -- S2表作为结果表，并从中去掉“刘晨”本人

例2．查询与“数据结构”在同一个学期开设的课程的课程名和开课学期。

这个例子与例1类似，只要将Course表想象成两张表，一张表作为查询条件，另一张表作为结果即可。

SELECT ame, C1.Semester

FROM Course C1 JOIN Course C2

ON C1.Semester = C2.Semester -- 是同一个学期开设的课程

WHERE ame = '数据结构' -- C2表作为查询条件表

5.1.3.3 外连接

在内连接操作中，只有满足连接条件的元组才能作为结果输出，但有时我们也希望输出那些不满足连接条件的元组信息，比如查看全部课程的选课情况，包括有学生选的课程和没有学生选的课程。如果用内连接实现（通过SC表和Course表的内连接），则只能找到有学生选的课程，因为内连接的结果首先是要满足连接条件：o = o。对于在Course 表中有，但在SC表中没有的课程（没有人选），由于不满足o = o条件，因

此是查找不出来的。这种情况就需要使用外连接来实现。

外连接是只限制一张表中的数据必须满足连接条

件，而另一张表中数据可以不满足连接条件。

外连接语法格式为：

FROM 表1 LEFT | RIGHT [OUTER]

JOIN 表2 ON <连接条件>

LEFT [OUTER] JOIN 称为左外连接，RIGHT

[OUTER] JOIN 称为右外连接。左外连接的含义是限制

表2中的数据必须满足连接条件，而不管表1中的数据

是否满足连接条件，均输出表1中的数据；右外连接的

含义是限制表1中的数据必须满足连接条件，而不管表2

中的数据是否满足连接条件，均输出表2中的数据。

例1．查询全体学生的选课情况，包括选课的学生和没选

课的学生。列出学号、姓名、课程号和成绩。

SELECT Student.Sno, Sname, Cno, Grade

FROM Student LEFT OUTER JOIN SC

ON Student.Sno = SC.Sno

查询结果如1例图所示。

注意，结果中学号为“201211103”、“201211104”

和“201221101”三行数据，这三行数据的Cno 和Grade

列的值均为NULL ，表明这三个学生没有选课，即他们

不满足表连接条件，但进行左外连接时也将他们显示出

来，并将不满足连接条件的结果在相应列上放置NULL 。

此查询也可以用右外连接实现，如下所示：

SELECT Student.Sno, Sname, Cno, Grade

FROM SC RIGHT OUTER JOIN Student

ON Student.Sno = SC.Sno

此句的查询结果同左外连接一样。 例2．查询没人选的课程，列出课程名。

分析：如果某门课程没有人选，则必定是在Course 表中有，在SC 表中不出现的课程，即在进行外连接时，没有人选的课程记录在SC 表相应的Sno 、Cno 或Grade 列上必定都是空值。因此在查询时只要在连接后的结果中选出SC 表中Sno 为空或者Cno 为空的行即可。（不选Grade 为空作为筛选条件的原因是，Grade 本身就允许NULL ，因此，当以Grade 是否为空来判断时，可能将有人选但还没有考试的课程列出来，而这些记录是不符合本查询要求的。）

完成此功能的查询语句为：

SELECT Cname FROM Course C LEFT JOIN SC

ON o = o

WHERE o is NULL

在外连接操作中，也可以使用WHERE 字句、GROUP BY 字句。

图 例1的查询结果