自然连接 natural join#

natural join 会：

• 自动寻找两张表中所有同名属性
• 要求这些同名属性值相等
• 并且在结果中只保留一份公共列

例如：

1
select name, course_id
2
from instructor natural join teaches;

这里之所以合理，是因为：

• instructor 和 teaches 共同拥有 ID
• 而 ID 恰好就是应该拿来连接的属性

所以这条查询会得到：

• 教师姓名 name
• 他教授的课程号 course_id

1
select name, title
2
from instructor natural join teaches natural join course;

join ... using(...)#

如果你知道要按哪个同名属性连接，但又不想让其他同名属性偷偷参与连接，可以写：

1
select name, title
2
from (instructor natural join teaches)
3
     join course using (course_id);

这里的含义是：

• 前半段 instructor natural join teaches 仍然通过 ID 连接
• 后半段显式指定：只通过 course_id 把课程表连进来

using (course_id) 的好处是：

• 比 natural join 更安全
• 比 on instructor.course_id = course.course_id 更简洁
• 结果中公共列 course_id 只保留一份

join ... on#

如果连接条件不是简单的“同名列相等”，或者你想完全显式地控制条件，就用 on：

1
select name, title
2
from instructor join teaches
3
  on instructor.ID = teaches.ID
4
  join course
5
  on teaches.course_id = course.course_id;

这可以看作最通用、最清楚的写法。

你自己明确指定：

• 哪两张表连接
• 按什么条件连接

因此在真实开发里，join ... on 往往比 natural join 更稳妥。

外连接 outer join#

普通的连接（inner join）只保留匹配成功的元组。

但有时我们不希望丢掉那些“没有匹配上”的数据，这时就需要外连接。

用户自定义类型 create type#

例如：

1
create type Dollars as numeric(12,2) final;

然后建表时可以写：

1
create table department (
2
    dept_name varchar(20),
3
    building varchar(15),
4
    budget Dollars
5
);

它的含义是：

• Dollars 本质上还是 numeric(12,2)
• 但我们给这个类型起了一个更有业务含义的名字

这样做的核心价值是：

• 提高可读性
• 强化语义表达

域 domain#

相比 type，domain 更进一步。

它不只是“给类型起别名”，还可以附带约束。

例如：

1
create domain person_name char(20) not null;

表示：

• 只要某列使用 person_name
• 它就统一是 char(20)
• 并且不能为空

再例如：

1
create domain degree_level varchar(10)
2
constraint degree_level_test
3
check (value in ('Bachelors', 'Masters', 'Doctorate'));

这表示：

• 这个 domain 的值只能是三种学位等级之一

所以可以把 domain 理解成：

带规则的类型。

学号、工号、邮箱这类字段通常：

• 会在很多表里反复出现
• 格式规则相对固定
• 不希望每次建表都重复写一遍 varchar(...) + check(...)

例如学号就可以统一定义成：

1
create domain student_id varchar(10) not null;

之后凡是表示“学号”的列，都直接写 student_id。

要注意的是：

• 共用同一个 domain 表示“取值规则一致”
• 但不代表自动形成外键关系

外键仍然要单独写。

大对象 blob / clob#

数据库里有些数据不是小字符串或小数字，而是很大的对象，例如：

• 图片
• 音频
• 视频
• PDF
• 很长的文本文档

这类数据可以用 large object 存储。

not null 与 unique#

1
name varchar(20) not null,
2
budget numeric(12,2) not null

1
unique(email)

1
unique(course_id, sec_id, semester, year)

check (P)#

check 用来表达：

这个属性或这条记录必须满足某个逻辑条件。

例如：

1
check (semester in ('Fall', 'Winter', 'Spring', 'Summer'))

这表示：

• 学期字段只能取四个合法值之一

再比如：

1
check (salary > 29000)
2
check (year > 1759 and year < 2100)

这说明 check 特别适合表达：

• 范围限制
• 枚举值限制
• 同一行内部字段之间的逻辑关系

foreign key 与参照完整性#

外键表达的是：

一张表中的某些值，必须引用另一张表中真实存在的值。

例如：

1
create table course (
2
    course_id char(5) primary key,
3
    title varchar(20),
4
    dept_name varchar(20),
5
    foreign key (dept_name) references department(dept_name)
6
);

意思是：

• course.dept_name 的取值必须能在 department.dept_name 中找到

这就是参照完整性。

它能防止出现这种脏数据：

• 课程表里写了一个根本不存在的院系名

on delete / on update#

当被引用表中的记录被删除或修改时，引用它的表怎么处理？

常见策略有：

• cascade
• set null
• set default
• restrict

例如：

1
foreign key (dept_name) references department(dept_name)
2
    on delete set null
3
    on update cascade

可以理解为：

• cascade：跟着删 / 跟着改
• set null：把外键列改成 null
• set default：改成默认值
• restrict：不允许这样删除或修改

自引用外键#

外键不一定引用别的表，也可以引用自己这张表。

例如：

1
create table person (
2
    ID char(10),
3
    name char(40),
4
    mother char(10),
5
    father char(10),
6
    primary key (ID),
7
    foreign key (father) references person,
8
    foreign key (mother) references person
9
);

这里：

• person.ID 是主键
• father 和 mother 也都是某个人的 ID
• 它们引用的仍然是 person 表本身

这种结构很常见，例如：

• 员工表中的 manager_id
• 评论表中的 reply_to
• 树结构中的 parent_id

WARNING

自引用外键带来的插入顺序问题

因为如果你先插入孩子：

• father = 'F001'
• mother = 'M001'

而 F001、M001 还没插入表中，那么外键约束就会失败。

常见解决办法有：

1. 先插父母，再插孩子
2. 先把 father、mother 设为 null，之后再更新
3. 把约束检查延迟到事务结束时统一检查

这说明完整性约束不仅影响“能不能这么写”，还影响“应该按什么顺序操作数据”。

复杂约束、assertion 与 trigger#

前面几种约束都比较“局部”：

• 限制某一列
• 限制某一行
• 限制某张表对另一张表的引用

但现实中还有更复杂的约束，例如：

• 每个 section 至少要有一位老师教
• 学生的 tot_cred 必须等于所有已通过课程学分之和

这种约束往往需要跨表检查。

1
check (time_slot_id in (
2
    select time_slot_id from time_slot
3
))

1
check ((course_id, sec_id, semester, year) in (
2
    select course_id, sec_id, semester, year
3
    from teaches
4
))

1
create assertion <assertion-name>
2
check <predicate>;

什么是 view#

view 可以理解成：

用一个查询结果“伪装”出来的一张表。

它看起来像表，但通常不是把数据重新存了一份，而是保存了查询定义。

因此 view 常被称为：

• virtual relation
• 虚拟表

view 有三个核心用途：

1. 隐藏不想给某些用户看的数据

   例如：

   • 不让普通用户看 ,到教师工资
   • 不让某些用户看到完整的个人信息

2. 封装复杂查询

   • 把一条经常要写的复杂 SQL 封装成一个名字，以后像查表一样去查它。

3. 在逻辑结构改变时，给旧程序保留兼容接口

create view#

基本形式：

1
create view v as <query expression>;

例如：

1
create view faculty as
2
select ID, name, dept_name
3
from instructor;

这个 view 的含义是：

• 从 instructor 表中取出三列
• 构成一个名为 faculty 的虚拟表

以后就可以写：

1
select name
2
from faculty
3
where dept_name = 'Biology';

这里的 faculty 用起来就像一张普通表。

复杂条件 view#

例如：

1
create view physics_fall_2009 as
2
select course.course_id, sec_id, building, room_number
3
from course, section
4
where course.course_id = section.course_id
5
  and course.dept_name = 'Physics'
6
  and section.semester = 'Fall'
7
  and section.year = '2009';

这个 view 不是来自单表，而是来自两张表：

• course
• section

它本质上做的是：

1. 把 course 和 section 按 course_id 连接
2. 只保留 Physics 系课程
3. 只保留 2009 年 Fall 学期开设的 section
4. 输出 course_id / sec_id / building / room_number

这个例子非常典型，因为它体现了 view 的另一大用途：

把一个经常要写的多表筛选查询封装成“一个名字”。

带聚合的 view#

例如：

1
create view departments_total_salary(dept_name, total_salary) as
2
select dept_name, sum(salary)
3
from instructor
4
group by dept_name;

这个 view 表示：

• 对每个系统计工资总和

这里显式写 (dept_name, total_salary) 是因为：

• 聚合表达式 sum(salary) 默认没有一个很自然的列名
• 所以要手动给 view 的列命名

View Expansion#

view expansion 的核心思想是：

查询中一旦出现 view 名，数据库会把它替换回原来的定义，再继续执行。

例如先定义：

1
create view physics_fall_2009_watson as
2
select course_id, room_number
3
from physics_fall_2009
4
where building = 'Watson';

这里的 physics_fall_2009 其实不是一张真实表，而是另一个 view。

数据库处理时会把它展开成原始定义，相当于：

1
select course_id, room_number
2
from (
3
    select course.course_id, building, room_number
4
    from course, section
5
    where course.course_id = section.course_id
6
      and course.dept_name = 'Physics'
7
      and section.semester = 'Fall'
8
      and section.year = '2009'
9
)
10
where building = 'Watson';

进一步理解，它最后还是在查底层真实表：

• course
• section

所以：

普通 view 存的通常不是“数据副本”，而是“查询定义”。

View 可以基于 View 再定义#

上面的 physics_fall_2009_watson 就是一个典型例子：

• physics_fall_2009 基于真实表定义
• physics_fall_2009_watson 再基于前一个 view 定义

这说明 view 的本质确实更像“命名查询”。

View 的更新问题#

既然 view 用起来像表，那么能不能对 view 做：

• insert
• update
• delete

答案是：

有时可以，但很多时候不行。

1
create view faculty as
2
select ID, name, dept_name
3
from instructor;

1
insert into faculty
2
values ('30765', 'Green', 'Music');

1
insert into instructor
2
values ('30765', 'Green', 'Music', null);

1
create view instructor_info as
2
select ID, name, building
3
from instructor, department
4
where instructor.dept_name = department.dept_name;

1
insert into instructor_info
2
values ('69987', 'White', 'Taylor');

什么是 updatable view#

大多数数据库系统只允许对 simple view 进行更新。

一般来说，一个 view 可更新，通常需要满足：

1. from 子句中只有一张真实表
2. select 子句中只出现该表的属性名，不出现表达式
3. 没有 group by
4. 没有 having
5. 没有聚合函数
6. 没有 distinct
7. 没出现在 view 中的底层列必须允许填 null

也就是说，只有当：

• view 和底层表行之间还有比较直接的对应关系

更新它才有可能被正确翻译回底层表。

with check option#

再看一个典型 view：

1
create view history_instructors as
2
select *
3
from instructor
4
where dept_name = 'History';

如果你通过这个 view 插入：

1
('25566', 'Brown', 'Biology', 100000)

从底层表角度看，这条记录完全可以插入 instructor。

但问题是：

• 它不属于 History
• 所以插进去后，它不会出现在 history_instructors 这个 view 里

这就很奇怪：

• 你明明是通过这个 view 插入的
• 插完之后却在这个 view 中看不到它

为了解决这个问题，可以写：

1
create view history_instructors as
2
select *
3
from instructor
4
where dept_name = 'History'
5
with check option;

其含义是：

通过这个 view 进行插入或更新后，结果仍然必须满足这个 view 自己的筛选条件。

所以 with check option 可以理解为：

• 不允许你通过 view 引入“改完后不再属于该 view”的数据

Materialized View#

普通 view 通常只保存定义，不保存结果。

而 materialized view 则不同，它会：

把定义该 view 的查询结果真的计算出来并存成一个物理表。

例如：

1
create materialized view departments_total_salary(dept_name, total_salary) as
2
select dept_name, sum(salary)
3
from instructor
4
group by dept_name;

这表示：

• 先把“每个系工资总额”的结果实际存下来

之后如果再查询：

1
select dept_name
2
from departments_total_salary
3
where total_salary > (
4
    select avg(total_salary)
5
    from departments_total_salary
6
);

就不需要每次都重新对 instructor 做 group by + sum。

普通 view vs materialized view#

Materialized view 的维护问题#

如果底层关系被更新：

• 新老师加入了
• 工资变了
• 旧老师被删除了

那么物化视图中的结果就可能变旧。

因此需要做 materialized view maintenance。

维护方式可能包括：

• 立即更新
• 查询时再刷新
• 周期性刷新

所以 materialized view 是一个典型的“性能换维护成本”的机制。

View 与 Logical Data Independence#

这是 view 的一个更高层作用。

假设原来有一个关系：

1
S(a, b, c)

后来为了更合理的设计，你把它拆成：

• S1(a, b)
• S2(a, c)

这时如果旧程序原来都写：

1
select * from S where ...

那它们岂不是全要改？

为了避免这种情况，可以重新定义一个 view：

1
create view S(a, b, c) as
2
select a, b, c
3
from S1 natural join S2;

这样：

• 底层逻辑结构已经变了
• 但外层程序仍然可以像以前一样使用 S

这就体现了 logical data independence（逻辑数据独立性）：

修改逻辑模式时，尽量不影响用户看到的外部接口。

所以 view 不只是“方便查询”，还是数据库模式演化时的重要兼容层。

索引的代价#

索引能提升查询速度，但代价是：

• 额外占用存储空间
• 插入、删除、更新数据时，索引本身也要维护

因此不是每一列都适合建索引。

commit 与 rollback#

事务通常隐式开始，以两种方式之一结束：

MySQL 中的一个典型例子#

1
SET AUTOCOMMIT=0;
2
UPDATE account SET balance = balance - 100 WHERE ano = '1001';
3
UPDATE account SET balance = balance + 100 WHERE ano = '1002';
4
COMMIT;

这里：

• 先关闭自动提交
• 两条 UPDATE 共同组成一次转账事务
• 最后统一 COMMIT

如果中途出错，就应该 ROLLBACK。

AUTOCOMMIT

• 每条 SQL 语句都会自动提交

这意味着：

• 你写一条 update
• 它往往立刻就生效了

而Transaction的关键，恰恰是把多条语句捆成一个整体来提交或回滚。

ACID#

事务的四个经典性质是 ACID。

Transaction Boundaries#

事务边界指的是：

哪些操作应该被视为同一个不可分割的工作单元。

这个问题很重要，因为事务并不是越大越好，也不是越小越好。

• 如果划得太小，原本必须一起成功的操作被拆开，就会出错
• 如果划得太大，会增加并发冲突，降低效率

所以事务边界的核心原则是：

把必须一起成功或失败的操作放进同一个事务。

基本权限类型#

对数据最常见的权限有：

• select
• insert
• update
• delete

对模式（schema）和对象管理的权限还有：

• create
• alter
• drop
• index
• create view

所以数据库权限大致分成两层：

grant#

SQL 用 grant 授予权限：

1
grant <privilege list>
2
on <relation name or view name>
3
to <user list>;

例如：

1
grant select on instructor to U1, U2, U3;
2
grant select on department to public;
3
grant update (budget) on department to U1, U2;
4
grant all privileges on department to U1;

含义分别是：

• U1/U2/U3 可以查询 instructor
• 所有用户都可以查询 department
• U1/U2 只能更新 department 表中的 budget 列
• U1 拥有 department 上的全部权限

public#

public 表示：

• 所有当前用户
• 以及未来新加入的用户

因此把权限授给 public，相当于全面开放。

revoke#

SQL 用 revoke 收回权限：

1
revoke <privilege list>
2
on <relation name or view name>
3
from <user list>;

例如：

1
revoke select on branch from U1, U2, U3;

表示收回这些用户对 branch 的查询权限。

一个用户可能通过多条路径获得同一权限#

这点非常重要。

如果：

• U1 把某权限授给了 U5
• U2 也把同一权限授给了 U5

那么即使后来撤销 U1 → U5 这条授权：

• U5 仍然可能保留该权限
• 因为他还可以通过 U2 那条路径继续获得它

所以权限系统并不是简单的一对一关系，而是一张传播网络。

Roles#

role 可以理解成：

一组权限的打包名字。

例如：

1
create role instructor;
2
grant instructor to Amit;
3
grant select on takes to instructor;

意思是：

• 创建一个角色 instructor
• 这个角色拥有 select on takes 的权限
• 再把这个角色授给 Amit

于是 Amit 就拥有了这个角色对应的权限。

TIP

为什么要用 role，而不是直接把权限一条条授给每个人？

因为现实中很多用户其实扮演的是相同角色，例如：

• 学生
• 教师
• 助教
• 系主任
• 院长

如果每来一个新老师都手写几十条 grant，会非常麻烦。

而角色的好处是：

• 先把权限绑定到角色上
• 再把角色分配给人

于是权限管理就更像现实世界中的“职位管理”。

role 可以继承#

例如：

1
create role teaching_assistant;
2
grant teaching_assistant to instructor;

这表示：

• instructor 角色继承 teaching_assistant 的权限

再例如：

1
create role dean;
2
grant instructor to dean;
3
grant dean to Satoshi;

说明：

• dean 包含 instructor 的权限
• Satoshi 被授予 dean
• 所以也拥有 instructor 那部分权限

View 与权限控制#

view 不只是查询工具，也是权限控制工具。

例如：

1
create view geo_instructor as
2
select *
3
from instructor
4
where dept_name = 'Geology';
5

6
grant select on geo_instructor to geo_staff;

这表示：

• 先创建一个只包含地质系教师的 view
• 再只把这个 view 的查询权限授给 geo_staff

这样：

• geo_staff 并没有完整 instructor 表的访问权
• 但他们可以看到自己被允许看到的那一部分数据

所以 view 在安全控制中的一个重要作用是：

把“整张表”裁剪成“可安全开放的窗口”。

references 权限#

除了读写数据外，还有一种容易忽略的权限：

1
grant references (dept_name) on department to Mariano;

这个权限的含义是：

• 允许 Mariano 引用 department.dept_name
• 例如拿它作为别的表中的外键参照目标

也就是说：

• select 是“我能看你的数据”
• references 是“我能把我的表结构建立在你的列之上”

它属于一种“结构依赖级”的权限。

with grant option#

默认情况下，一个用户即使得到了某项权限，也不能继续把它转授给别人。

如果你写：

1
grant select on department to Amit with grant option;

表示：

• Amit 自己可以 select department
• 并且 Amit 还可以把这个权限继续授给别人

这就让权限可以继续传播。

权限传播图#

一旦允许转授，就会形成：

• DBA → U1
• U1 → U4
• U2 → U5
• …

这样的一张授权图。

一个用户是否拥有某个权限，不只是看“有没有人给过他”，而要看：

从根授权者到他之间是否还存在一条有效路径。

cascade 与 restrict#

当你收回权限时，就会遇到一个问题：

如果被收回者又把这个权限授给了别人，该怎么办？

1
revoke select on department from Amit cascade;

1
revoke select on department from Amit restrict;

只撤销转授权，不撤销实际权限#

还可以写：

1
revoke grant option for select on department from Amit;

它表示：

• Amit 仍然保留 select department 的权限
• 但 Amit 以后不能再继续把这个权限授给别人

这个语义非常细，但很重要。