第4章 中级SQL

4.1 连接表达式

4.1.1 连接条件

• join...using子句，是一种自然连接的形式，只需要在指定属性（两个关系中具有相同名称的属性）上的取值相等。
• on条件允许在参与连接(内连接、左连接、右连接、全连接)的关系上设置通用的谓词，写法与where子句谓词类似。

4.1.2 外连接

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

对应的，SQL中把常规连接称作内连接(inner join)，inner关键字可选，当join子句没有使用outer前辍，默认的连接类型就是inner join。

4.1.3 连接类型和条件

连接类型
inner join
left outer join
right outer join
full outer join

连接条件
natural
on <谓词>
using(col1, col2,...,coln)

4.2 视图

4.2.1 视图定义

create view v as <query expression>

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

4.2.3 物化视图

特定数据库系统允许存储视图关系，视图的结果会存放在数据库中。但是它们保证：如果用于定义视图的实际关系改变，视图也跟着修改。
这样的视图被称为物化视图(materialized view)。

4.3 事务

事务(transaction)由查询和（或）更新语句的序列组成。

• Commit work：提交当前事务，将该事务所做的更新在数据库中持久保存。事务被提交后，一个新的事务自动开始。
• Rollback work：回滚当前事务，即撤销该事务中所有的SQL语句对数据库的更新。 关键词work在两条语句中都是可选的。

4.4 完整性约束

4.4.1 单个关系上的约束

create table命令上可以包括完整性约束语句。包括：

• not null
• unique
• check(<谓词>)

4.4.5 参照完整性

在一个关系给定属性集上的取值也在另一关系特定属性集的取值中出现，称为参照完整性(referential integrity)。
更一般地，令关系r1和r2的属性集分别为R1和R2，主码分别为K1和K2。如果要求对r2中任意元组t2，均存在r1中元组t1，
使得t1.K1 = t2.a，我们称R2的子集a为参照关系r1中K1的外码(foreign key)。
这种要求称为参照完整性约束(referential integrity constraint)或子集依赖(subset dependency)。

foreign key (dept_name) references department

级联删除，级联更新
create table course(
...
foreign key (dept_name) references department
    on delete cascade
    on update cascade,
...);

4.4.7 复杂check条件与断言

一个断言(assertion)就是一个谓词，它表达了我们希望数据库总能满足的一个条件。域约束和参照完整性约束是断言的特殊形式。
SQL中的断言为如下形式：

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

4.5 SQL的数据类型与模式

4.5.3 创建索引

在关系的属性上所创建的索引(index)是一种数据结构，它允许数据库系统高效的找到关系中，
那些在索引属性上取给定值的那些元组，而不用扫描关系中的所有元组。 创建索引的语法形式：

create index student_index on student(ID);

4.5.7 模式、目录与环境

数据库中为解决命名空间问题，提供了三层关系命名机制：最顶层由目录(catalog)构成，每个目录都可以包含模式(schema)。（一些数据库实现用术语"数据库"代替术语"目录"）
为了唯一标识一个关系，它包含三部分：

catalog_name.schema_name.table_name

创建和删除模式
create schema
drop schema

4.6 授权

对数据的授权包括：

• 授权读取数据(select)
• 授权插入新数据(insert)
• 授权更新数据(update)
• 授权删除数据(delete)

每种类型的授权都称为一个权限(previlege)。

4.6.1 权限的授予与收回

SQL标准包括select、insert、update和delete权限。 grant语句用来授予权限。

grant <权限列表>
on <关系名或视图名>
to <用户/角色列表>;

grant select on department to User1, User2;
grant update (budget) on department to User1, User2;

revoke语句用来收回权限。

revoke <权限列表>
on <关系名或视图名>
from <用户/角色列表>;

revoke select on department from User1, User2;
revoke update (budget) on department from User1, User2;

4.6.2 角色

可以为某一类用户设定一个角色，然后给角色授予权限，这类用户就会拥有相同的权限。

create role instructor;
grant select on dependent to instructor;

角色可以授予给用户，也可以授予给其他角色
create role dean;
grant instructor to dean;
grant dean to User1;