数据库中的数据查询——连接查询

发表于 更新于 分类于

数据查询(连接查询)

  • 不像关系代数中”连接”是用一种特殊符号来表达的,在SQL中”连接”是用”连接条件”来表达的.

连接条件或连接谓词: 用来连接两个表的条件
一般格式: [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

连接字段: 连接谓词中的列名称
连接条件中的各连接字段类型必须是可比的, 但名字不同.

等值与非等值连接查询

  • 等值连接 : 连接运算符为 ‘=’

例子-1:

1
2
3
4
#查询每个学生及其选修课程的情况
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student,Sno = SC.Sno;

上述查询将把两个表的字段首尾相接同时显示出来(类似关系代数中的自然连接).
与关系代数中的自然连接不同的是,遇到不同表中拥有相同字段时,SQL将其都显示出来.
例如Student.Sno / SC.Sno
上述查询语句的结果为:
avatar
若想要实现真正的自然连接,则在SELECT中显式指定要列出的字段即可:

1
2
3
4
#查询每个学生及其选修课程的情况
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student,Sno = SC.Sno;

例子-2:

1
2
3
4
5
#查询选修2号课程且成绩咋90以上的所有学生的学号和姓名.
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND    #连接谓词
      SC.Cno='2' AND SC.Grade>90; #选择谓词

可见,一条SQL语句可以同时完成选择和连接查询, 这时WHERE子句是由选择谓词和连接谓词组成的复合条件.

连接操作的执行过程

  1. 嵌套循环法 (NESTED-LOOP)
    • 先在表1中找到第1个元组, 再从头开始扫描表2, 逐一找到满足连接的元组.
      找到后将表1的第1个元组与该元组拼接起来, 形成结果表中的一个元组.
    • 表2全部查找完后, 再找表1中第2个元组. 从头开始扫描表2, 逐一找到满足连接的元组.
      找到后将表1中第2个元组与该元组拼接起来, 形成结果表中的一个元组.
    • 重复上述操作,直到表1中的全部元组都处理完毕.
  2. 排序合并法 (SORT-MERGE)
    • 常用于等值连接
    • 首先按连接属性对表1表2排序.
    • 设置指针, 分别指向表1和表2的第一个元组.
      如果这两个元组满足连接条件, 则进行元组连接(若有多个连接元组满足条件,则一一拼接)
      , 并将指针分别后移一个元组. 否则, 将具有较小值的指针后移一个元组.
    • 重复上述操作, 直到表1或表2的全部元组都处理完毕位为止.
  3. 索引连接 (INDEX-JOIN)
    • 对表2按连接字段建立索引
    • 对表1的每个元组, 一次根据其连接字段值查询表2的索引, 从中找到满足条件的元组.
      找到后就将表1的第1个元组与该元组拼接起来, 形成结果表中的一个元组.
    • (该方法可以视作嵌套循环法的一个变种)

自身连接

自身连接即将一个表于其自身进行连接,是一种特殊的连接.

  • 需要给表起别名以示区别
  • 由于所有属性名都是同名属性, 因此必须使用别名前缀

例子:

1
2
3
4
#查询每一门课的直接先修课程的名称
SELECT FIRST.Cname, SECOND.Cname
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno - SECOND.Cno;

假设Course表的内容如下:
avatar
则上例查询结果如下:
avatar

外连接

外连接与普通连接的区别:

  • 普通连接操作只输出满足连接条件的元组
  • 外连接操作指定表为连接主体, 将主体表中不满足连接条件的元组一并输出
  • 左外连接: 列出左边关系中所有的元组
  • 右外连接: 列出右边关系中所有的元组

例子:

1
2
3
4
5
6
7
8
#查询每个学生及其选修课程的情况
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student,Sno = SC.Sno;
#改写上例,将其用外连接实现
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUT JOIN SC #LEFT OUT JOIN可以简写为LEFT JOIN
ON (Student.Sno = SC.Sno);

上述例子中外连接实现的查询结果(蓝色部分是与等值连接不同的地方):
avatar

多表连接

多表连接: 两个以上的表进行连接

例子

1
2
3
4
#查询每个学生的学号, 姓名, 选修的课程名 以及 成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course #多表连接
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;