[数据库之四] 中级 SQL
1、连接表达式
（1）连接条件
nature join、join ... using
假设关系 A 和 B 有一个相同的属性 c 并且是用来关联关系的属性，则
A nature join B
等同于
A join B using(c)
用笛卡尔积来表示等同于
A, B where A.c = B.c
join 还可以使用 on 关键字来设置连接条件，类似于 where 的作用。
A join B using(c)
等同于
A join B on A.c = B.c
使用 on 关键字的优点：在 on 子句中指定连接条件，并在 where 子句中出现其余的条件，这样的 SQL 更易懂。
（2）外连接
关系 R
| A | B | C |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
关系 S
| A | E |
|---|---|
| 2 | 2 |
| 4 | 4 |
nature join 默认是内连接，不保留连接中丢失的元组。
R nature join S 得到的集合为：
| A | B | C | E |
|---|---|---|---|
| 2 | 2 | 2 | 2 |
跟内连接不同，外连接会在结果中创建包含空值元组的方式，保留那些在连接中丢失的元素，外连接有三种形式：
- 左外连接（left outer join）：只保留出现在左外连接运算之前的关系中的元组。
- 右外连接（right outer join）：只保留出现在右外连接运算之后的关系中的元组。
- 全外连接（full outer join）：保留出现在两个关系中的元组。
R left outer join S （= S right outer join R）得到的结果为：
| A | B | C | E |
|---|---|---|---|
| 1 | 1 | 1 | null |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | null |
R right outer join S （= S left outer join R）得到的结果为：
| A | B | C | E |
|---|---|---|---|
| 2 | 2 | 2 | 2 |
| null | null | 4 | 4 |
R full outer join S （= S full outer join R）得到的结果为：
| A | B | C | E |
|---|---|---|---|
| 1 | 1 | 1 | null |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | null |
| null | null | 4 | 4 |
（3）内连接
nature join = join ... using = inner join ... using
2、视图
直接对数据库模式中定义的表的数据进行操作，让所有用户读看到整个逻辑模式是不合适的，出于安全考虑可能需要向用户隐藏特定的数据，可以通过 “视图” 来实现。
SQL 允许通过查询来定义 “虚关系”，它在概念上包含查询的结果。虚关系并不预先计算并存储，而是在使用虚关系的时候才通过执行查询被计算出来。任何像这种不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图。
（1）视图的定义
使用 create view 命令定义视图，格式为：
create view v as <query expression>;
示例：创建一个视图，列出 Physics 系在 2009 年秋季学期所开设的所有课程段，以及每个课程段在哪栋建筑的哪个房间授课的信息：
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 course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = '2009';
（2）SQL 查询中使用视图
一旦定义了一个视图，就可以用视图名指代该视图生成的虚关系。可以把视图看成一张表来查询，如使用上面定义的视图 physics_fall_2009，查询找到所有于 2009 年秋季学期在 Watson 大楼开设的 Physics 课程：
select course_id
from physics_fall_2009
where building = 'Waston';
（3）视图的更新
视图一般用于查询不用于更新，因为插入的数据可能不合法、无法满足约束条件，或者无法被翻译为对数据库逻辑模型中实际关系的修改。
3、完整性约束
（1）单个关系上的约束
-
not null
限定属性不为空，eg: name varchar(20) not null
-
unique
unique( A1, A2, ..., An ) 声明指出属性 A1, A2, ..., An 形成了一个候选码。
-
check（<谓词>）
不是所有的数据库都支持，eg: check ( semester in ('Fall', 'Winter', 'Spring', 'Summer') ) // 限制了属性的枚举值
（2）参照完整性
声明外键使用 foreign key 关键字，语法如下：
eg:
foreign key(dept_name) reference department
简写形式
dept_name varchar(20) references department
默认情况下，当违反参照完整性约束时，通常是拒绝执行导致完整破坏的动作，但是也可以定义若被参照关系上的删除或更新动作违反了约束，系统要采取哪些步骤通过修改参照关系中的元组来恢复完整性约束。
create table course
( ...
foreign key(dept_name) references department
on delete cascade
on update cascade
);
on delete cascade 的意思是，若删除 department 中的元组导致此参照完整性约束被违反，则对 course 关系作级联删除；
on update cascade 的意思是，若更新 department 中的元素导致此参照完整性约束被违反，则将 course 中参照的元组的 dept_name 字段也改为新值。
4、授权
（1）权限的授予与收回
- 授权
grant < 权限列表 >
on < 关系名或视图名 >
to < 用户/角色列表 >
eg: 授予数据库用户 Amit 和 Satoshi 在 department 关系上的 select 权限
grant select on department to Amit, Satoshi;
授予用户 Amit 和 Satoshi 在 department 关系的 budget 属性上的更新权限
grant update(budget) on department to Amit, Satoshi;
- 回收权限
revoke < 权限列表 >
on < 关系名或视图名 >
from < 用户/角色列表 >
eg: 回收上述授予的权限
revoke select on department from Amit, Satoshi;
revoke update(budget) on department from Amit, Satoshi;
（2）角色
-
角色的创建以及给角色授权
角色起到一个分组的作用，给用户授予角色，再给角色授权，具有该角色的用户就间接被授权了，避免需要对用户逐个授权。
create role instructor;
grant select on takes to instructor;
