知识点

• 3.3. 连接查询
• • 3.3.1 等值与非等值连接
  • • 等值连接
    • • 分析案例:
      • • 案例1
        • 案例2
        • 案例3
        • 案例4
    • 自然连接
  • 3.3.2 自表连接
  • 3.3.3 外连接

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, classNameFROM Student AS a, Class AS b // 取别名，也可以不取。AS不一定要，有可以写成 Student aWHERE 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.scoreFROM Student a, Score b, (SELECT * FROM Score WHERE courseNo='002') c --查找出选修了编号为“002”课程的所有同学构造一个查询表cWHERE 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.scoreFROM Student a,(SELECT * FROM  Score WHERE  courseNo='001')b,  (SELECT * FROM  Score WHERE  courseNo='002') cWHERE 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 cWHERE a.studentNo=c.studentNoAND c.courseNo=b.courseNoAND score>=60GROUP BY a.studentNo, studentNameHAVING sum(creditHour)>=28ORDER 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, priorCourseFROM Score a, Course bWHERE  a.courseNo=b.courseNo     // 表a与表b的连接条件

3.3.2 自表连接

若某个表与自己进行连接，称为自表连接
上述案例3还有另一种自表连接做法

SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.scoreFROM Student a, Score b, Score cWHERE 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, studentNameFROM Class a LEFT OUTER JOIN Student b ON a.classNo=b.classNoWHERE grade=2015ORDER BY className, studentNo

• 右外连接:包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代。

SELECT className, institute, studentNo, studentNameFROM Class a RIGHT OUTER JOIN Student b ON a.classNo=b.classNoWHERE grade=2015 ORDER BY className, studentNo

• 全外连接:包含左、右关系中的所有元组
  左关系中没有连接的元组，其右关系中的相应属性用空值替代
  右关系中没有连接的元组，其左关系中的相应属性用空值替代

SELECT className, institute, studentNo, studentNameFROM Class a FULL OUTER JOIN  Student  b ON a.classNo=b.classNoWHERE grade=2015ORDER BY className,studentNo