跳转至

第 4 章：进阶的 SQL 语言

自定义数据类型和域

1
2
3
4
5
6
create type Credit as numeric(12, 2);
create domain Money as numeric(12, 2) not null;
create table account (
    account_name varchar(20),
    credit Credit,
    balance Money);

巨型文件

  • blob：二进制的大文件。
  • clob：文本式的大文件。

当有查询涉及它们的时候，返回的会是一个指向它们的指针。

完整性约束

对单个关系可以有下面这些约束：

  • not null
  • primary key (属性名)
  • foreign key (属性名) references 表名
  • unique
  • check(P)
1
2
3
4
5
6
7
8
create table teammates (
  id char(5) primary key,
  name varchar(20) unique not null,
  species integer not null,
  hp integer not null,
  foreign key (species) references pokedex,
  check(hp >= 0)
);

另外可以在自定义域里面声明。

1
2
create domain hourly_wage numeric(5, 2)
  constraint value_test check (value >= 4.00);

参照完整性约束

现在 \(r_1(R_1)\) 和 \(r_2(R_2)\) 分别有主键 \(K_1\) 和 \(K_2\)，\(r_2\) 里面有一行 \(a\)，对于所有 \(t_2 \in r_2\)，肯定存在一个 \(t_1 \in r_1\) 满足 \(t_1[K_1] = t_2[a]\)；更简单地来说，可以写成 \(\Pi_a(r_2) \subseteq \Pi_{K_1}(r_1)\)。

参照关系中外码的值必须在被参照关系中实际存在，或为 null。

SQL 的级联

1
2
foreign key (teammate) references pokedex
[on delete cascade] [on update cascade] ...

断言 Assertions

1
2
create assertion assertion_name
  check ...;

触发器 Triggers

1
2
3
4
5
create trigger hp_check after insert on teammate
  referencing new row as nrow
  if (nrow.hp <= 0) begin
    rollback
  end;

授权 Authorization

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

<user list> 里面可以放一个用户 ID、一个角色（role）或者 public（所有人）。

1
2
3
4
5
create role instructor;
grant select on teammate to instructor; -- instructor 有看 teammate 的权限
create role dean;
grant dean to Arceus; -- 阿尔宙斯现在是院长
grant instructor to dean; -- 院长现在有 instructor 的权限

1
2
3
revoke select
on teammate
from instructor [restrict | cascade]; -- restrict：仅限这个角色；cascade：连带授权关系

revoke all：收回所有权限。

... from public：收回所有人通过 public 拿到的权限。通过其他指明拿到的权限还会在。

审计跟踪 Audit Trails

1
2
3
4
audit table -- view, role, index, ...
by Hoopa
by access
whenever successful;

审计用户胡帕所有的成功执行的有关 table 的语句（create table、drop table、alter table 等）。

1
2
3
audit update, delete -- update, select, grant, ...
by Hoopa
on character_list;

审计胡帕对 character_list 表的 update 和 delete 操作。比如会发现这个：

1
2
3
4
update character_list
set alive = true
where name = '拉帝欧斯'
  and native_place = '奥多马雷';