Chapter 4 Intermediate SQL¶
1 Join Expressions¶
连接操作是将两个关系（表）处理后返回新关系，通常作为 from 子句的子查询使用。
基本概念
- 连接类型（Join type）：定义两个关系中不匹配元组的处理方式。
- 连接条件（Join condition）：定义两个关系的元组匹配规则，以及结果关系中包含的属性。
- 自然连接（Natural join）：会自动消除重复属性，是基础但存在使用风险。
示例
注意
- CS-315 存在于 course 表，但无对应先修课信息（prereq 表无记录）
- CS-347 存在于 prereq 表，但无对应课程信息（course 表无记录）
- 内连接（inner join）：仅返回两边表都满足连接条件的匹配元组，
on子句不会自动去重同名列
SELECT *
FROM course
INNER JOIN prereq
ON course.course_id = prereq.course_id;
- 左外连接（left outer join）：保留左表所有元组，右表中不匹配的元组对应列补为
null
SELECT *
FROM course
LEFT OUTER JOIN prereq
ON course.course_id = prereq.course_id;
- 右外连接（natural right outer join）：保留右表所有元组，左表中不匹配的元组对应列补为
null，自动匹配同名属性并去重
SELECT *
FROM course
NATURAL RIGHT OUTER JOIN prereq;
- 全外连接（natural full outer join）：保留左右两张表的所有元组，匹配的部分合并，不匹配的部分补
null
写法 1
SELECT *
FROM course
NATURAL FULL OUTER JOIN prereq;
写法 2：USING (course_id) 显式指定连接列，比 NATURAL 更安全，避免隐式匹配其他同名属性的风险
SELECT *
FROM course
FULL OUTER JOIN prereq
USING (course_id);
2 Transactions¶
事务是数据库中不可分割的工作单元，由一系列查询/更新语句组成，是保证数据一致性的核心机制。
- 核心特性
- 原子性（Atomic）：事务要么完全执行，要么回滚。
- 隔离性（Isolation）：事务与并发执行的其他事务相互隔离。
- 事务的开始与结束
- 开始：隐式开始，无需显式语句
- 结束：
commit work（事务的更新操作永久化到数据库）或rollback work（撤销事务中所有SQL语句的更新操作）
- 多数数据库默认每条 SQL 语句自动提交，可通过 API 关闭会话的自动提交， SQL:1999 支持
begin atomic … end显式定义事务块，但多数数据库未实现。
3 Views¶
视图是数据库的虚拟关系，不实际存储数据，仅保存查询表达式，核心作用是隐藏敏感数据，为不同用户提供定制化的数据访问视角。
create view 视图名 as <合法SQL查询表达式> 仅保存查询表达式，不生成新的物理关系，定义后，视图名可直接作为关系名用于后续查询。
创建不包含薪资信息的教师视图并查找生物系的所有教师
create view faculty as
select ID, name, dept_name
from instructor
select name
from faculty
where dept_name = 'Biology'
创建各系薪资总额视图并查询薪资总额高于平均值的系
create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_name;
select dept_name from departments_total_salary
where total_salary > (select avg(total_salary) from departments_total_salary)
- 视图可嵌套定义，即一个视图的查询表达式中引用另一个视图。
基于 2009 年秋季物理系的课程视图创建 2009 年秋季物理系在 Watson 楼的课程视图
create view physics_fall_2009 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
and dept_name = 'Physics'
and semester = 'Fall'
and year = '2009';
create view physics_fall_2009_watson as
select course_id, room_number
from physics_fall_2009
where building= 'Watson';
- 视图展开（View Expansion）：将视图引用替换为其定义的查询表达式，重复替换直到无视图引用为止，其中视图定义必须是非递归的，否则替换会无限循环。
示例
create view physics_fall_2009_watson as (
select course_id, room_number
from (
select course.course_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = '2009')
where building = 'Watson');
递归视图（Recursive view）
- 一个视图可以被用于定义另一个视图的表达式中，如果视图关系 \(v_2\) 被用于定义视图关系 \(v_1\) 的表达式中，则称 \(v_1\) 直接依赖（depend directly on）于 \(v_2\)。
- 如果视图关系 \(v_1\) 直接依赖于 \(v_2\)，或者存在一条从 \(v_1\) 到 \(v_2\) 的依赖路径（path of dependencies），则称 \(v_1\) 依赖（depend on）于 \(v_2\)。
- 如果视图关系 \(v\) 依赖于自身，则称其为递归视图。
- 视图更新（Update of a View）：视图的更新最终会映射到底层物理关系，仅简单视图支持更新，复杂视图易出现歧义或意外结果。
简单视图更新：仅基于单表、无聚合/表达式/分组的视图，插入时未指定的列会设为null
insert into faculty values ('30765', 'Green', 'Music');
更新的歧义问题：多表连接的视图无法唯一映射到底层表，多数 SQL 实现禁止此类更新
create view instructor_info as
select ID, name, building
from instructor, department
where instructor.dept_name = department.dept_name;
insert into instructor_info values ('69987', 'White', 'Taylor');
引发的问题：如果 Taylor 楼存在多个院系，这条插入应该归属到哪个院系？如果 Taylor 楼没有任何院系，这条插入该如何处理？
更新的意外结果：如按条件定义的视图，插入不符合条件的元组会成功存入底层表
create view history_instructors as
select *
from instructor
where dept_name = 'History';
为了避免将生物系的教师插入到历史系视图中，我们可以在创建视图时使用 with check option 子句，强制插入/更新的元组满足视图的查询条件。
- 物化视图（Materialized Views）：创建物理表存储视图查询的结果，而非仅保存表达式。
- 问题：底层关系更新后，物化视图的结果会过期。
- 解决：需维护物化视图，即底层关系更新时同步更新视图数据。
4 Index¶
索引是为加速数据查询设计的特殊数据结构，通过索引属性快速定位记录，避免全表扫描。
create table student
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0) default 0,
primary key (ID));
create index studentName_index on student(Name);
-- 可通过索引直接找到目标记录，无需遍历 student 表的所有数据，大幅提升查询效率
select * from student where Name = 'Mike';
5 Integrity Constraints¶
完整性约束用于防止数据库意外损坏，保证授权的数据库修改不会导致数据不一致，是数据库数据质量的保障。
5. 1 Integrity Constraints on a Single Relation¶
| 约束类型 | 核心特点 | 示例 |
|---|---|---|
not null |
强制属性值非空 | name varchar(20) not null |
primary key |
主键，非空且唯一，一个表仅一个 | primary key (course_id, sec_id, semester, year) |
unique |
候选键，唯一但可空，一个表可多个 | unique (ID) |
check(P) |
强制元组满足谓词 \(P\) | check (semester in ('Fall', 'Winter', 'Spring', 'Summer')) |
5. 2 Referential Integrity¶
保证一个关系中的外键属性值，必须出现在另一个关系的主键属性中（外键所在表为从表，主键所在表为主表），外键值可设为 null。
create table course (
...
dept_name varchar(20),
-- 定义外键：院系名称，参照 department 表
foreign key (dept_name) references department
-- 级联删除：若被参照的院系记录被删除，则同步删除所有关联的课程记录
on delete cascade,
-- 级联更新：若被参照的院系记录被更新，则同步更新所有关联的课程记录
on update cascade,
...
);
-- set null：将从表的外键值设为 null
-- set default：将从表的外键值设为默认值
5. 3 Integrity Constraint Violation During Transactions¶
create table person (
ID char(10),
name char(40),
mother char(10),
father char(10),
primary key ID,
foreign key father references person,
foreign key mother references person
);
person 表的 father/mother 是外键，插入新元组时需先插入父母元组，或先将父母设为 null（若属性未定义 not null）。
5. 4 Complex Check Clauses¶
check子句：多数数据库不支持子查询（如check (time_slot_id in (select time_slot_id from time_slot))）- 断言（Assertion）：
create assertion <断言名> check <谓词>，可定义复杂跨表约束，目前所有数据库均未支持 - 替代方案：使用触发器（Triggers） 实现复杂约束
6 More on SQL Data Types¶
6. 1 Date and Time Related Types in SQL¶
支持日期、时间的存储和运算，相减返回时间间隔，间隔可与日期/时间相加。
| 类型 | 含义 | 示例 |
|---|---|---|
date |
日期（4 位年份+月+日） | date '2005-7-27' |
time |
时间（时+分+秒，支持小数秒） | time '09:00:30.75' |
timestamp |
时间戳（date+time） | timestamp '2005-7-27 09:00:30.75' |
interval |
时间间隔 | interval '4 days 3 hours 2 minutes 1 second' |
6. 2 User-Defined Types¶
通过 create type 自定义专属类型，基于基础 SQL 类型封装。
-- 定义美元类型
create type Dollars as numeric (12,2) final;
-- 使用自定义类型
create table department(
dept_name varchar(20),
building varchar(15),
budget Dollars
);
6. 3 Domains¶
SQL-92 提出，与自定义类型类似，可直接为域添加约束（not null/check），是属性的通用约束模板。
-- 定义非空的人名域
create domain person_name char(20) not null;
-- 定义学历域，加取值约束
create domain degree_level varchar(10)
constraint degree_level_constraint
check (value in ('Bachelors', 'Masters', 'Doctorate'));
6. 4 Large-Object Types¶
用于存储超大数据（图片、视频、CAD文件等），查询时仅返回数据指针，而非实际大对象内容。
blob：二进制大对象（Binary Large Object），存储无解释的二进制数据。clob：字符大对象（Character Large Object），存储大量字符数据。
7 Authorization¶
数据库为不同用户分配不同操作权限，保证数据的访问安全，核心通过 grant（授予）和 revoke（撤销）语句实现。
7. 1 权限分类¶
| 权限类别 | 具体权限 | 核心作用 |
|---|---|---|
| 数据操作 | read（读）/insert（插）/update（改）/delete（删）/all privileges（所有可用权限） |
操作数据库中的数据 |
| 模式修改 | Index（索引）/Resources（建表）/Alteration（改列）/Drop（删表） |
修改数据库的逻辑结构 |
7. 2 权限授予¶
grant <权限列表>
on <表名/视图名> to <用户列表/角色/public>;
用户列表：可指定单个用户、public（所有有效用户）、角色（Role）- 授予者前提：必须已持有该权限，或为数据库管理员（DBA）；
- 视图授权：授予视图的权限不隐含其底层表的权限。
-- 授予 U1/U2/U3 查询 instructor 表的权限
grant select on instructor to U1, U2, U3;
-- 授予 geo_staff 查询 geo_instructor 视图的权限
grant select on geo_instructor to geo_staff;
7. 3 权限撤销¶
revoke <权限列表/all>
on <表名/视图名> from <用户列表/角色/public>;
- 如果 <被撤销用户列表> 中包含 public，除了被显式单独授予该权限的用户外，其他所有用户都将失去该权限。
- 权限依赖：所有基于该权限的派生权限会被一并撤销。
- 重复授予：若同一权限由不同授予者多次授予，撤销其中一次后，用户仍保留该权限。
7. 4 角色（Roles）¶
为批量管理用户权限设计，将一组权限绑定到角色，再将角色分配给用户/其他角色，实现权限的层级化管理。
-- 创建角色
create role instructor;
create role teaching_assistant;
-- 为角色分配权限
grant select on takes to instructor;
-- 给用户 Amit 分配讲师角色
grant instructor to Amit;
-- 讲师继承助教的所有权限
grant teaching_assistant to instructor;
支持角色链（Chain of roles）：通过角色嵌套实现权限的层级传递，高层角色继承所有低层角色的权限。
create role dean;
grant instructor to dean;
grant dean to Satoshi;
7. 5 其他授权特性¶
- 对视图授权 \(\neq\) 对底层基表授权，视图创建者必须对基表有足够权限
Authorization on Views
create view geo_instructor as
select * from instructor
where dept_name = 'Geology';
grant select on geo_instructor to geo_staff;
references 权限：允许用户在自己的表中，引用其他表的字段作为外键
grant reference (dept_name) on department to Mariano;
权限传递：允许被授权者将获得的权限再次转授给其他用户
grant select on department to Amit with grant option;
级联撤销：适用需要彻底清理权限链的场景
revoke select on department from Amit, Satoshi cascade;
限制撤销：仅在没有衍生权限时才执行撤销
revoke select on department from Amit, Satoshi restrict;