《数据库系统概念》学习笔记——中级SQL

中级SQL

连接表达式

SQL提供了连接运算的其他形式，包括能够指定显式的连接谓词（join predicate），能够在结果中包含被自然连接排除在外的元组。

例子：student 和 takes 两个关系。

连接条件

join…using 子句，它是一种自然连接的形式，只需要在指定属性上的取值匹配。

on 条件允许在参与连接的关系上设置通用的谓词。
该谓词的写法与 where 子句谓词类似，只不过使用的是关键词 on 而不是 where。
与 using 条件一样， on 条件出现在连接表达式的末尾。

包含 on 条件的连接表达式：

select *
from student join takes on student.ID = takes.ID;

上述 on 条件表明：如果一个来自 student 的元组和一个来自 takes 的元组在ID上的取值相同，那么它们是匹配的。

等价于

select *
from student, takes
where student.ID = takes.ID;

只显示一次ID值的查询如下：

select student.ID as ID, name, dept_name, tot_cred, 
	course_id, sec_id, semester, year, grade
from student join takes on student.ID = takes.ID;

查询结果如下：

on 条件可以表示任何SQL谓词，从而使用on条件的连接表达式就可以表示比自然连接更为丰富的连接条件。

• 被称为外连接的这类连接来说，on条件的表现与where条件不同。
• 如在on子句中指定连接条件，并在where子句出现其余条件，这样更易读。

外连接

假设显示一个所有学生的列表，显示他们的ID，name，dept_name和tot_cred，及他们选修的课程。

select *
from student natural join takes;

外连接（outer join）运算与我们已经学过的连接运算类似，但通过在结果中创建包含空值元组的方式，保留了那些在连接中丢失的分组。

上述例子在图1和图2的情形下，snow学生未选课将不会出现在natual join结果中。
为保证snow出现在结果中，可在连接结果中加入一个元组，它在来自student关系的所有属性上的值被设置为学生snow的相应值。

在所有余下的来自takes关系属性上的值被设为null。
这些属性是course_id, sec_id, semester, year
有三种外连接：

• 左外连接（left outer join），只保留出现在左外连接运算之前(左边)的关系中的元组
• 右外连接（right outer join），只保留出现在右外连接运算之后(右边)的关系中的元组。
• 全外连接（full outer join），保留出现在两个关系中的元组。

此前学习的不保留未匹配元组的连接运算称为内连接（inner join）运算。

计算左外连接运算：
首先，计算出内连接的结果；
然后，对内连接的左侧关系中任意一个与右侧关系中任何元组都不匹配的元组 t ，向连接结果中加入一个元组r。
r的构造如下：

• 元组r从左侧关系得到的属性被赋为t中的值
• r的其他属性被赋为空值

// 结果包含snow，在snow对应的元组中，在那些只出现在takes关系模式中的属性上取空值
select *
from student natural left outer join takes;

使用外连接运算的例子：

// 找出一门课程也没有选修的学生
	select ID
	from student natural left outer join takes
	where course id is null;

右外连接和左外连接是对称的。

等价的右外连接

	select *
	from takes natural right outer join student;

得到的结果是一样的，差别是元组中各个属性出现顺序不一致。

全外连接是左外连接与右外连接类型的组合。

在内连接结果计算出来后，左侧关系中不匹配右侧关系任何元组的元组被添上空值并加到结果中。
右侧关系中不匹配左侧关系任何元组的元组也被添上空值并加到结果中。

全外连接的例子：
查询 “显示Comp. Sci.系所有学生及他们在2009年春季选修的所有课程段的列表。2009年春季开设的所有课程段都必须显示，即使没有Comp. Sci.系的学生选修这些课程段 ”

select *
from (select *
	// 学生集合
	  from student
	// 选出Comp. Sci系的
	  where dept_name= ’Comp. Sci’)
	// 全外连接
	natural full outer join
	(select *
	// 选课信息
	from takes
	// 选出2009,Spring的被选课程
	where semester = ’Spring’ and year = 2009);

on 子句可以和外连接一起使用。

select *
from student left outer join takes on student.ID = takes.ID;

on 和 where 在外连接中的表现是不同的。
其原因是外连接只为那些对相应内连接结果没有贡献的元组补上空值并加入结果。
on 条件是外连接声明的一部分，但where子句却不是。

把前述查询中的 on 子句谓词换成 where子句，并使用on条件 true：

select *
from student left outer join takes on true
where student.ID = takes.ID;

连接类型和条件

SQL中把常规连接称作内连接。
这样连接子句可用 inner join 来替换 outer join ，说明使用的是常规连接。
关键词inner是可选，当 join 子句中没有使用 outer 前缀，默认的连接类型是 inner join 。

select *
from student join takes using (ID);

等价于

select *
from student inner join takes using (ID);

类似地，natural join 等价于 natural inner join 。

视图

考虑一个职员需要知道教师的标识，姓名，所在系名，但没权限看到教师的工资值。

select ID, name, dept name
from instructor;

除了安全考虑，还可能希望创建一个比逻辑模型更符号特定用户直觉的个人化的关系集合。

// 希望有一个关于Physics系在2009秋季学期开设的所有课程段的列表
// 包括每个课程段在那栋建筑，那个房间授课
select course.course_id, sec_id, building, room_number
// 课程和开设课程笛卡尔积
from course, section
// 针对每个课程，
// 此课程的每个开设信息，与课程结合形成一个结果元组
// 结果元组满足物理系，2009，秋季，则被选入结果元组
where course.course_id = section.course_id
	and course.dept_name = ’Physics’
	and section.semester = ’Fall’
	and section.year = ’2009’;

可