数据库系统原理：多表连接查询

大家好，欢迎来到IT知识分享网。

SQL命令包括数据定义、查询、操纵和控制四大类，其中数据查询是数据库的核心操作。连接查询是关系数据库最主要的查询

数据查询中最常用的是多表连接查询。多表连接查询是指一个查询同时涉及两个及以上的表。

一、多表连接查询

SOL的查询语句基本语法结构

Select A1,A2….An

From Rl,R2,…,Rn

Where
<查询条件表达式< p="">

若一个查询同时涉及两个以上的表时(From 子句中有多个表名)称为连接查询。需要注意的是，连接的字段类型必须可比，但不必相同。

例1：查询有一门课程成绩等于95分的学号、姓名、课程号和成绩。

Select S.sno,sname,cno,grade

From S, SC

Where S.sno=SC.sno and grade=95

在例1的查询中，课程号、选课成绩来源于SC表、学生的姓名来源于S表，因此在“FROM”后需要给出S、SC两个表名。这两个表有一个连接条件即“表S的学号等于表SC的学号”、一个查询条件“成绩是95” 。输出的列有四列，分别是学号列、姓名列、课程号列和成绩列。

例1中还需要注意，如果连接的两个表中有相同的属性名，在输出的时候需要在前面加上表名，但不拘于是哪一个表的表名。如果，属性名唯一的话，就不需要增加表名了。

例1中还需要注意，如果连接条件不给出的话，系统会执行笛卡尔积。

DBMS执行连接的过程：

首先在表1中找到第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后将表1中的第1个元组与该元组拼接，形成结果表中的一个元组。当表2中的全部元组找完后，再回到表1中找表1中的第2个元组、第3个元组等.…重复上述步骤，直到表1中的全部元组处理完毕。

例1的SQL命令执行结果图

例2：查询女学生的学号、姓名、成绩。

Select S.sno,sname,grade

From S,SC

Where S.sno=SC.sno and sex=’女’

在例2的查询中，成绩来源于SC表、学生的姓名、性别来源于S表，因此在“FROM”后需要给出S、SC两个表名。这两个表有一个连接条件即“表S的学号等于表SC的学号”、一个选择条件“性别是女生” 。输出的列有三列，分别是学号列、姓名列和成绩列。

同上，需要注意的两点：连接的两个表中有相同的属性名，在输出的时候需要在前面加上表名；同时两个表做连接查询的话，有一个连接条件。

关系代数表达：

首先，S表和SC表做笛卡尔积，再在笛卡尔积的基础上做选择，选择的条件是“S表的学号等于SC表的学号并且性别是女”，最后在选择的基础上做投影运算，投影三列即S表中的学号列、姓名列和成绩列。

从上述内容可以看出，SQL表达的查询和关系代数表达的查询是相似的。

例3：找出平均成绩80以上的女生姓名。

Select sname

From S,SC

Where S.sno=SC.sno and sex=’女’

Group by sname

Having avg（grade）>80

例3的查询涉及到两张表即S表、SC表、一个连接条件即“表S的学号等于表SC的学号”、一个查询条件“性别是女生”，由于要计算每个学生的平均成绩，所以需要一个按照学生学号或者是姓名做分组，分组之后继续进行进一步的选择即“平均成绩大于80” ；输出的是学生的姓名。

还需要注意的是，如果在查询中有Group by子句，那么Select后面只能出现Group by中的出现的分组属性和聚集函数。因此，输出的是姓名，意味着Select后面一定是姓名列，那么在Group by子句中必须出现姓名；如果Group by子句中出现的是学号，就会出现错误。

例3的SQL命令执行结果图

例4：求刘正老师所授的每门课程的课程号、课程名和学生平均成绩。

Select C.cno,cname,avg(grade)

From C,SC

Where C.cno=SC.cno and tname=’刘正’

Group by C.cno,cname

例4的查询涉及到两张表即C表、SC表、一个连接条件即“表C的课程号等于表SC的课程号”、一个选择条件“教师名是刘正”，输出的是课程号、课程名和学生的平均成绩。由于输出有课程名、课程号两列，因此在后面的分组列里面要都给出来课程号和课程名。课程号在表C和表SC中都有出现，是共有属性，需要附加表名，Group by子句中附加的表名也需要相应在Select子句中附加的表名上。

SQL命令执行结果

例5：查询选修c2或c3课程的学生学号和姓名。

Select distinct S.sno,sname

From S, SC

Where S.sno=SC.sno and（cno=’c2’orcno=’c3’）;

例5的查询涉及到两张表即S表、SC表、一个连接条件即“表S的学号等于表SC的学号”、一个选择条件“课程号是c2或者课程号是c3”，输出的是学生的学号和姓名。可以使用“distinct”去掉重复的学号和姓名，因为有的同学可能会都选了c2、c3两门课，可能会输出两次，所以可以去重。

在WHERE子句中，可以使用括号来改变查询条件的优先级。

二、自身连接（特殊连接类型）

同一个表的不同元组之间的连接称为自身连接，即一个表与其自己进行连接。自身连接要求给表取不同的名字，即当作两个不同的表来处理。

例6：查询选修c2和c3课程的学生学号和姓名。

错误方式：

Select S.sno,sname

From S，SC

Where S.sno=SC.sno and（cno=’c2′ and cno=’c3’）

例6和例5中不同的地方是把例5中的“或”改成了例6中的“和”，是“与”的关系。如果直接引用例5中的查询、将其中的或预算符“or”改成与运算符“and”来实现例6的查询，会得到一个语法正确、但是结果错误的查询。

从下图可以看到学生s1都选择了课程c2和c3，所以s1应该被输出。但是该查询是没有结果的。SC表中的任何一行数据都不可能满足条件“课程号是c2并且课程号是c3” ，所以选择条件的结果为假，所以输出的结果为空。

关系代数：

回顾关系代数的知识，用关系代数实现例6的查询的话，需要用到一种称为“自身连接”的连接方式，就是SC和SC自己做笛卡尔积，这样由SC原来的三列变成了六列，如下图所示。在这六列中，去做“第一列等于第四列”的选择即第一列的学号等于第四列的学号、同时去做“第二列等于c2、第五列是c3”即课程号筛选，最后去投影第一列即学号列。

Select X.sno

From SC as X , SC as Y

Where X.sno=Y.sno and X.cno=’c2′ and Y.cno= ‘c3’

同样，用SQL实现的话，需要SC表做自身连接，但是需要将它们看做两个表，因此需要分别给两个SC取别名，第一个SC取别名X、第二个SC取别名Y。as既可以为列名而存在，也可以为表的别名所使用。

例6的SQL的条件是“表X的学号等于表Y的学号、并且表X的课程号是c2、并且表Y的课程号是c3” ，输出的是X的学号。需要注意的是输出的学号所附加的表名要么是X、要么是Y。

SQL正误结果对比图

例7：查询选修了课程’c1”并且选修课程在三门以上的同学学号。

错解一:Selectsno

From SC

Where cno= ‘cl’ and count（cno）>3Group by sno

错解二:Select sno

From SC

Where cno= ‘c1’Group by sno

Having count（cno）>3

由于例7的查询中只涉及到选课信息，所以只需要SC一张表。在表SC中找同学的学号，条件是“课程号等于c1并且同学的选课门数的聚集函数大于3” 、并且通过学号对学生进行分组。但是聚集函数不能出现在WHERE子句中，所以该查询语法错误。

如果将条件“同学的选课门数的聚集函数大于3”放到Having子句中，该查询虽然语法正确但是结果错误。其中的原因是当进行WHERE子句中的第一层选择的时候，只把表SC中选了“课程号是c1”的学生的学号输出，之后再分组的话，计数的时候学生的选修课程的门数均为1，无法满足条件“同学的选课门数的聚集函数大于3” ，输出为空。

Select X.sno

From SC X,SC Y

Where X.sno=Y.sno and X.cno=’c’

Group by X.sno

Having count（ Y.cno）>=3

所以，在例7中需要使用SC表的自身连接来实现。需要用SC表和SC表自己和自己连接，第一个SC表起名为X、第二个SC表起名为Y；连接条件是“表X的学号等于表Y的学号、并且表X的课程号是c1”；然后对表X的学号进行分组，对表Y的课程号进行count的聚集函数计数。且如果分组GROUP子句后面出现的是表X的学号，那么在SELECT子句中输出的也是X的学号。

SQL正确命令执行结果图

三、总结

多表连接查询是最常用的数据查询形式。SOL是非过程化语言，在执行连接查询时表名的顺序和条件的顺序均无关。在多表连接查询的时候，一般查询条件由连接条件和选择条件组成。