3.3. 连接查询
往往涉及到多个关系的查询,需用到连接运算或子查询。关系数据库中使用最广泛的一种运算,包括等值连接、自然连接、非等值连接、自表连接和外连接等。
3.3.1 等值与非等值连接
连接运算在WHERE子句中加入连接多个关系的连接条件。
WHERE [<表1>.]<属性名1> <比较运算符> [<表2>.]<属性名2>
[ <逻辑运算符>
[<表3>.]<属性名3> <比较运算符> [<表4>.]<属性名4> ... ]
- 比较运算符
- />、>=、<、<=、=、<>(或!=)
- 当比较运算符为 = 时,表示等值连接
- 其他运算为非等值连接
- WHERE子句的连接谓词中的属性称为连接属性
- 连接属性之间必须具有可比性,相同属性比较
等值连接
分析案例:
案例1
1. 查找会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称。
+ 查询的结果为学号、姓名、籍贯、班级编号和班级名称,在SELECT子句中必须包含这些属性;
+ 班级名称和所属学院在班级表Class中,学号、姓名、籍贯、班级编号在学生表Student中,FROM子句必须包含Class表和Student表;
+ 班级编号classNo既是班级表的主码,也是学生表的外码,这2个表的连接条件是classNo相等,在WHERE子句中必须包含连接条件:Student.classNo=Class.classNo
+ 查询会计学院的学生记录,在WHERE子句中还必须包括选择条件:institute='会计学院'
--查询语句
SELECT studentNo, studentName, native, b.classNo, className
FROM Student AS a, Class AS b // 取别名,也可以不取。AS不一定要,有可以写成 Student a
WHERE Student.classNo=Class.classNo --连接条件(两个表比较连接)
AND institute='会计学院'--选择条件(属性值)
在连接操作中,如果涉及到多个表的相同属性名,必须在相同的属性名前加上表名加以区分。
案例2
2. 查找选修了课程名称为“计算机原理”的同学学号、姓名。
+ 查询结果:学号、姓名,在SELECT子句中必须包含这些属性;
+ 学号和姓名在学生表中,课程名称在课程表中,FROM子句必须包含学生表Student、课程表Course
+ 学生表与课程表之间是多对多联系,需通过成绩表转换为两个多对一的联系,FROM子句必须包含成绩表Score
+ 课程号既是课程表的主码,也是成绩表的外码,这2个表的连接条件是课程号相等;学号既是学生表的主码,也是成绩表的外码,这2个表的连接条件是学号相等。在WHERE子句中涉及三个关系的连接,其连接条件: Course.courseNo=Score.courseNo AND Score.studentNo=Student.studentNo AND Course.courseName='计算机原理'
-- 查询语句
SELECT a.studentNo, studentName
FROM Student a, Course b, Score c
WHERE b.courseNo=c.courseNo // 表b与表c的连接条件
AND c.studentNo=a.studentNo // 表c与表a的连接条件
AND b.courseName=‘计算机原理’ // 选择条件
在连接操作中,如果属性对应的表没有联系,通过一个中间表进行连接
案例3
--3.查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩,并按学号排序输出。
--查询第一种方法
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
FROM Student a, Score b, (SELECT * FROM Score WHERE courseNo='002') c --查找出选修了编号为“002”课程的所有同学构造一个查询表c
WHERE b.courseNo='001'
AND a.studentNo=b.studentNo // 把所有选择了001课程的同学找出来
AND a.studentNo=c.studentNo // 把所有选择了002课程的同学找出来
ORDER BY a.studentNo
--查询第二种方法
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
FROM Student a,(SELECT * FROM Score WHERE courseNo='001')b, (SELECT * FROM Score WHERE courseNo='002') c
WHERE a.studentNo=b.studentNo // 表a与表b的连接条件
AND a.studentNo=c.studentNo // 表a与表c的连接条件
ORDER BY a.studentNo
注意:不能在同一个表b上同时表示选修“001”和“002”
AND b.courseNo=‘001’
AND b.courseNo=‘002’
案例4
--4.查询获得的总学分(注:只有成绩合格才能获得该课程的学分)大于或等于28的同学的学号、姓名和总学分,并按学号排序输出。
SELECT a.studentNo, studentName, sum(creditHour)
FROM Student a, Course b, Score c
WHERE a.studentNo=c.studentNo
AND c.courseNo=b.courseNo
AND score>=60
GROUP BY a.studentNo, studentName
HAVING sum(creditHour)>=28
ORDER BY a.studentNo
本例输出结果中需要同时包含学号和姓名,所以GROUP BY子句需要按“a.studentNo, studentName”进行聚合,不能仅按“a.studentNo”进行聚合,否则无法输出studentName。
上例条件分析:
既使用WHERE子句,又使用HAVING子句,都是选择满足条件的元组,但选择范围不一样。
(1) WHERE子句:作用整个查询对象,对元组过滤。
(2) HAVING子句:仅作用于分组,对分组过滤。
自然连接
SQL不直接支持自然连接,完成自然连接的方法是在等值连接的基础上消除重复列。
--实现成绩表Score和课程表Course的自然连接。
SELECT studentNo, a.courseNo, score, courseName, creditHour, courseHour, priorCourse
FROM Score a, Course b
WHERE a.courseNo=b.courseNo // 表a与表b的连接条件
3.3.2 自表连接
若某个表与自己进行连接,称为自表连接
上述案例3还有另一种自表连接做法
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
FROM Student a, Score b, Score c
WHERE a.studentNo=b.studentNo // 表a与表b的连接条件
AND a.studentNo=c.studentNo // 表a与表c的连接条件
AND b.courseNo=‘001’ // 表b上的选择条件
AND c.courseNo='002' // 表c上的选择条件
ORDER BY a.studentNo
3.3.3 外连接
在一般的连接中,只有满足连接条件的元组才被检索出来,对于没有满足连接条件的元组是不作为结果被检索出来的。在实际应用中,往往需要将不满足连接条件的元组也检索出来,只是在相应的位置用空值替代,这种查询称为外连接查询。分为左外连接、右外连接和全外连接。
- 左外连接:包含左关系中的所有元组,对于左关系中没有连接上的元组,其右关系中的相应属性用空值替代。
--使用左外连接,查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名,按班级名称和学号排序输出。
SELECT className, institute, studentNo, studentName
FROM Class a LEFT OUTER JOIN Student b ON a.classNo=b.classNo
WHERE grade=2015
ORDER BY className, studentNo
- 右外连接:包含右关系中的所有元组,对于右关系中没有连接上的元组,其左关系中的相应属性用空值替代。
SELECT className, institute, studentNo, studentName
FROM Class a RIGHT OUTER JOIN Student b ON a.classNo=b.classNo
WHERE grade=2015
ORDER BY className, studentNo
- 全外连接:包含左、右关系中的所有元组
左关系中没有连接的元组,其右关系中的相应属性用空值替代
右关系中没有连接的元组,其左关系中的相应属性用空值替代
SELECT className, institute, studentNo, studentName
FROM Class a FULL OUTER JOIN Student b ON a.classNo=b.classNo
WHERE grade=2015
ORDER BY className,studentNo