CS145 Intro to databases 学习笔记4——索引、约束、触发器、事务、视图、授权

前言

已经是学习standford CS145 Introduction to Databases系列视频的第四篇笔记，这篇几乎要把SQL中重要的概念都涵盖了。。。

本文主要包括第十章到第十四章的内容：

1. 索引
2. 约束和触发器
3. 事务
4. 视图
5. 授权

个人博客页观感更加喵QWQ：CS145 Intro to databases 学习笔记4——索引、约束、触发器、事务、视图、授权 | Andrew的个人博客 (andreww1219.github.io)

相关参考资料：

视频链接：Introduction to Databases - Jennifer Widom - Stanford

黑马的视频也是很实用啊：黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括

---

一、索引(Indexes)

1. 基本介绍

对关系表的某个或某些属性构建索引，能够提高查询的效率。

然而，维护索引也需要开销：

• 索引需要占用空间(最微不足道的开销，空间换时间咱还是愿意的)
• 创建索引需要时间(中等的开销，创建索引确实是耗时的操作)
• 索引需要维护，特别是在频繁对数据做修改时开销很大(几乎可以抵消索引带来的好处)

因此评价索引需要考虑到查询和修改的负荷(Query vs. Update)

2. 索引的结构

索引可以有两种结构，分别是：

• B树或B+树(B trees or B+ trees)：可以处理属性的比较关系(=, <, >)，查询的时间复杂度是$O(\log n)$对数级(Logarithmic)
• 哈希表(Hash Tables)：只能处理属性的相等关系(=)，查询的时间复杂度是$O(1)$常数级(Constant)

因此，只处理相等关系时，尽量用哈希表，需要处理比较关系时可以用B树和B+树

3. 索引的语法

SQL标准下的索引的语法：

Create Index IndexName on T(A) --对一个属性创建索引
Create Index IndexName on T(Al,A2,..,An) --对多个属性创建索引
Create Unique Index IndexName on T(A)  --在创建索引的时候检查属性是否有重复值
Drop Index IndexName --删除索引

---

二、约束和触发器(Constraints and Triggers)

1. 基本介绍

(1) 约束和触发器的作用

约束和触发器，它们都允许我们在处理关系模型时，我们需要对属性的值的范围作限制，以及处理不同属性之间的限制关系

(2) 约束和触发器的区别

约束是静态的，而触发器是动态的。触发器不仅允许监控数据库的状态，而且能够作出即时的反馈，比约束更加expressive和powerful。

2. 约束(Constraints)

2.1 非空约束(Non-null Constraints)

添加了非空约束的属性，值不能为NULL

--在属性后面加上not null添加非空约束
create table student(sID int, sName text, GPA real not null, sizeHS int);

2.2 主键约束和唯一约束(Key Constraints)

添加了主键约束的属性，值不能重复，且不能为空
添加了唯一约束的属性，值不能重复，但是可以为空(MySQL 允许多个NULL值，其他数据库不清楚)

--在属性后面加上primary key添加主键约束
--在属性后面加上unique添加唯一约束
create table student(sID int primary key, sName text unique
        GPA real, sizeHS int);

可以为多个属性添加Key Constraints

--将多个属性作为整体添加约束时，放在所有属性的末尾
create table Apply(sID int, cName text, major text, decision text
        unique(sID, cName), unique(sID, major));

create table College(cName text, state text, enrollment int,
        primary key(cName, state));

2.3 基于属性和元组的约束(Attribute-based/Tuple-based Constraints)

Attribute-based Constraints对属性的值的范围作检查

--在属性后添加check( condition )限制值的范围
create table Student(sID int, sName text,
            GPA real check(GPA <= 4.0 and GPA > 0.0)
            sizeHS int check(sizeHS < 5000));

Tuple-based Constraints可以表示多个属性之间的限制关系

--可以在所有属性的末尾使用check( condition )指定属性间的限制关系
create table Apply(sID int, cName text, major text, decision text, 
    check(decision='N' or cName <> 'Stanford' or major <> 'CS'));

事实上，check()可以用来重写许多其他的约束，比如：

--用check重写非空约束
create table Student(sID int, sName text, GPA real check(GPA is not null), sizeHS int);

在SQL标准下，check()中编写子查询的有效的，但是实际还没有数据库实现这一功能

--check()中编写子查询实现外键的引用
create table Apply(sID int, cName text, major text, decision text,
    check(sID in(select sID from Student)));

2.4 外键约束(Foreign Key)

视频中也将其叫做引用完整性(Referential integrity)

表$R$中的属性$A$的值必须在表$S$中的属性$B$中出现，那么：

• 称$A$为外键(Foreign Key)，可以有多个属性作为外键
• $B$经常是主键primary key，至少应该是unique的，即不能有重复值

create table College(cName text primary key, state text, enrollment int);

create table Student(sID int primary key, sName text, GPA real, sizeHS int);

--attName references tableName(attName) 设置外键
create table Apply(sID int references student(sID),
                    cName text references College(cName), 
                    major text, decision text);

处理外键约束往往可以归类为两个问题：

(1) 对$R.A$的修改和更新：需要检查新的值是否在$S.B$中存在

对于$S.B$中不存在的值，数据库会直接拒绝该修改和更新操作

(2) 对$S.B$的更新和删除：需要处理引用了旧的值的$R.A$

处理这一类问题有三个方案：

1. CASCADE：级联，对$S.B$更新时将$R.A$一同更新为新的值；对$S.B$删除时将$R.A$的元组一同删除
2. SET NULL：在对$S.B$的更新和删除操作时，将引用了旧的值的$R.A$设为NULL
3. RESTRICT：默认是RESTRICT，当存在引用了旧的值的$R.A$，不允许对$S.B$的更新和删除操作

实际上，我们可以分别指定更新和删除要用那个方案

--ON UPDATE <CASCADE/SET NULL/RESTRICT>：指定更新的方案
--ON DELETE <CASCADE/SET NULL/RESTRICT>：指定删除的方案
create table Apply(sID int references Student(sID) on delete set null,
                    cName text references College(cName) on update cascade,
                    decision text, major text);

2.5 通用断言(General Assertions)

General Assertions目前只存在于SQL标准中，未被数据库所实现。

创建一个断言，它会在我们对数据库就修改时，检查条件是否成立：

--注意!!以下代码仅为解释General Assertions展示, 无法真实执行
--创建一个断言检查表T中属性A是否存在重复值
create assertion Key
check((select count(distinct A) from T)
    = select count(*)from T)));

--创建一个断言检查外键约束
create assertion ReferentialIntegrity
check(not exists(select * from Apply
    where sID not in(select sID from student)));

3. 触发器(Triggers)

3.1 SQL标准下的触发器

SQL标准下的触发器语法如下：

Create Trigger name             --创建触发器，为触发器命名
Before|After|Instead of events  --指定触发器触发的时机，events可以为插入、更新和删除操作
[referencing-variables]         --将修改前后的行或表引用为变量
[For Each Row]                  --指定触发器是Row-Level还是Statement-Level
When ( condition )              --满足条件时，执行以下动作
action

events可以为插入、更新和删除操作：
(1) 插入: Insert on tableName
(2) 删除：Delete on tableName
(3) 更新：Update [of (attName1, attName2, …)] on tableName

[referencing-variables] 可以引用行，也可以引用表：
old row as var, new row as var, old table as var, new table as var
(1) 对于插入insert操作，只有new
(2) 对于删除delete操作，只有old
(3) 对于更新update操作，既有old也有new

[For Each Row] 有For Each Row表示触发器是行级触发器，否则则是语句级触发器：
(1) 行级触发器代表操作有n行，就会执行n次触发器的动作
(2) 语句及触发器代表只会在语句末尾执行一次动作
需要注意的是，在SQL标准中，无论是行级还是语句级，动作都是攒到语句末尾执行，而SQLite和MySQL的行级触发器都是对每一行操作即刻时执行

--示例：R.A引用到S.B，级联删除触发器
--行级触发器写法
Create Trigger Cascade
After Delete on S
Referencing Old Row As O
For Each Row
[ no condition ]
Delete From R where A = 0.B

--语句级触发器写法
Create Trigger Cascade
After Delete on S
Referencing Old Table As OT
[ For Each Row ]
[ no condition ]
Delete From R where A in (select B from OT )

3.2 触发器的语法

不同的数据库对SQL标准的实现程度不同，在触发器的语法和功能上也有不同：

Postgre：完全实现了SQL标准，包括行级、语句级触发器，可以引用行，也可以引用表。但是语法更晦涩
SQLite：只有行级触发器，且在对每行操作时就触发
MySQL：只有行级触发器，且在对每行操作时就触发，且每个事件只能有一个触发器，且对触发器的链式触发有限制

Postgres >

• Expressiveness/behavior = full standard
  row-level+ statement-level, old/new row & table
• Cumbersome & awkward syntax

SQLite >>

• Row-level only,immediate activation => no old/new table

MySQL

• Row-level only, immediate activation => no old/new table
• Only one trigger per event type
• Limited trigger chaining

SQLite和MySQL都预定义了Old、New两个变量用于引用旧行和新行，并且将动作action编写在begin和end之间的语句块中。

下面是SQLite的代码示例：

(1) 基本操作

--insert：只有New
create trigger R1
after insert on Student 
for each row
when New.GPA > 3.3 and New.GPA <= 3.6
begin
    insert into Apply values(New.sID, 'Stanford', 'geology', nu11);
    insert into Apply values(New.sID, 'MIT', 'biology', nul1);
end;

--delete：只有Old
--触发器实现外键的级联删除
create trigger R2
after delete on Student
for each row
begin
        delete from Apply where sID = Old.sID;
end;

--update：既有New, 也有Old
--触发器实现外键的级联更新
create trigger R3
after update of cName on College
for each row
begin
    update Apply set cName = New.cName
    where cName = Old.cName;
end;

(2) 拦截错误操作

--用before events + select raise(ignore)拦截错误的操作
--触发器实现唯一约束(还需要一个before update的触发器就不多赘述了)
create trigger R4
before insert on College
for each row
when exists(select * from College where cName = New.cName)
begin
    select raise(ignore);
end;

(3) 实现复杂操作

--触发器能够实现约束不能实现的东西
--比如check中不能编写子查询和聚合函数，和General Assertions不被数据库支持的问题
--我们用when + select语句来监控并采取action
create trigger R6
after insert on Apply
for each row
when (select count(*) from Apply where cName = New.cName) > 10
begin
    update College set cName = cName || "-Done"  -- ||运算符表示连接两个字符串
    where cName = New.cName;
end;

(4) 递归调用

--默认情况下，SQLite要求触发器不能被递归调用，
--也就是说，一个事件的触发器在一次操作中只能触发一次
--下列语句将允许触发器的递归调用
PRAGMA recursive_triggers = on;

--一个或多个触发器互相调用时，需要有终止条件防止死循环
create trigger R1
after insert on T1
for each row
begin
    insert into T2 values(New.A + 1);
end;

create trigger R2
after insert on T2
for each row
begin
    insert into T3 values(New.A + 1);
end;

create trigger R3
after insert on T3
for each row
--设置终止条件防止死循环
when select(count(*) from T1) < 100 
begin
    insert into T1 values(New.A + 1);
end;

(5) 同个事件多个触发器的执行顺序

--当一个事件有多个触发器时，率先触发的是较晚定义的触发器
create trigger R1
after insert on T1
for each row
begin
    update T1 set A = 2;
end;

create trigger R2
after insert on T1
for each row
when exists (select * from T1 where A = 2)
begin
    update T1 set A = 3;
end;

--执行insert into T1 value(1);
--得到T1中属性A的值为2，说明R2先触发，R1晚触发

---

三、事务(Transactions)

1. 基本介绍

通俗来讲，事务就是把许多语句当作一个整体来处理。

语句一般分为三个操作：get(取出数据); modify(修改数据); put(放回数据);
然而，当多个语句交叉在一起时，可能会出现属性、元组以及关系表上的结果不一致。
因此，我们需要将语句隔离开来。

同时，在进行大批量的语句操作时，如果系统突然崩溃，只有一半的语句被执行，我们需要有一个机制将剩余的语句执行，或者是将已执行的语句撤销，而不是将全部的语句再执行一遍。因此，我们在业务上将一批语句看作一个整体，称作事务，每个事务要么完全成功，要么完全失败

在SQL标准中，用"commit"语句来表示一个事务的结束，同时也是下一个事务的开始。启动"Autocommit"模式时，每一条语句就是一个事务

2. ACID特性

事务具有四大特性，可以总结为:

1. 原子性(Atomicity)：一个事务要么完全成功，要么完全失败
2. 一致性(Consistency)：每个事务执行前后都一直满足预定的约束
3. 隔离性(Isolation)：事务中的操作可以交错，但是事务之间应该是串行执行的
4. 持久性(Durability)：就算系统崩溃，事务做的操作能够保留在数据库中

3. 隔离级别(Isolation Level)

隔离级别越高，并发性越低，有时我们愿意牺牲一定的一致性去换取并发性。

隔离级别是针对每个事务而言，我们可以给不同的事务设置不同的隔离级别。

隔离级别从低到高依次为：

1. 读未提交(Read Uncommitted)：允许该事务读取其他事务中，未commit的数据。我们称这种现象为脏读(Dirty reads)
2. 读提交(Read Committed)：不允许脏读，但是允许多次读取同一组数据。尽管不会出现脏读，但是不同时间读到的同一组数据的值可能会被另一个事务更新或删除，导致两次读取的值不同，我们称这种现象为不可重复读(Nonrepeatable reads)
3. 可重复读(Repeatable Read)：要求被多次读取的同一组数据不能被修改。但是我们在不同时间对表做查询操作时，可能会有另一个事务对该表做插入操作，导致第二次查询出现了原来没有的元组，我们称之为幻影元组(Phantom Tuples)，称这种现象为幻读(Phantom read)
4. 串行化(Serializable)：每个事务之间按照一定顺序执行

SQL标准的默认隔离级别是Serializable，许多系统默认的隔离级别是：Repeatable Read

除了设置事务的隔离级别，我们也可以设置事务为只读(Read Only)，使其不能对数据库做任何修改

示例：

Set Transaction Read Only;
Set Transaction Isolation Level Repeatable Read;
select Avg(GPA) From student;
select Max(GPA) From student

隔离级别对三个问题的解决程度如下：

隔离级别	脏读	不可重复读	幻读
读未提交	$\times$	$\times$	$\times$
读提交	$\surd$	$\times$	$\times$
可重复读	$\surd$	$\surd$	$\times$
串行化	$\surd$	$\surd$	$\surd$

---

四、视图(Views)

1. 基本介绍

视图是建立在关系模型之上的进一步抽象，它封装和隐藏底层的数据，只暴露需要的数据给用户。目的是简化查询，限制数据访问，提高安全性和提高性能。

视图包括两种：

1. 默认为虚拟视图(Virtual Views)，简称视图。视图是一个虚拟表，不存储任何数据
2. 物化视图(Materialized Views)，它占用存储空间，利用增量维护(incremental maintenance)算法使基表与视图保持同步

当频繁查询且每次查询需要耗费大量资源时，我们使用物化视图

SQL标准下的视图语法：

Create [Materialized] View vname(A1, A2, …, An) AS
<Query>

SQLite示例：

--视图示例
create view CSAccept as
select sID, cName
from Apply 
where major ='CS'and decision = 'Y';

--物化视图示例
Create Materialized View CA-AppTy AS
Select sID，cName, major 
From Apply A 
where cName In (select cName From College where state = 'CA')

存在物化视图时，数据库会自动重写一些复杂的查询，以提高查询效率，如下

Select Distinct S.sID，S.GPA
From College C, Student S, Apply A
Where c.cName = A.cName And S.sID = A.sID 
    And S.GPA > 3.5 And C.state = 'CA' And A.major='CS';
    
--以上查询可以被重写为
Select Distinct S.sID，S.GPA
From Student S, CA-Apply A
Where S.sID = A.sID 
    And S.GPA > 3.5 And A.major = 'CS'

2. 视图的修改操作

2.1 使用触发器

用INSTEAD OF接 对视图的修改操作，随后在指定动作中

示例：

create trigger CSAcceptDelete
instead of delete on CSAccept
for each row
begin
    delete from Apply 
    where sID = Old.sID and cName = Old.cName 
        and major='cs' and decision ='Y'
end;

使用触发器对视图修改，优点是能够处理所有的修改操作，只要我们对这些操作编写对应的触发器；缺点是我们无法保证这些修改就是有现实意义的。

2.2 自动视图修改

在SQL标准下，当视图满足以下条件时，可称为Updatable Views：

1. 只对一个表$T$查询，且不能使用DISTINCT
2. 未被查询的其他值不能有非空约束，或者具有默认值
3. 子查询中不能出现表$T$
4. 不能出现分组查询(GROUP BY)和聚合查询

Updatable Views保证了对视图做修改操作时不会出现歧义。

一般来说，我们对视图的修改会转化为对基表的修改，然后应该反映在视图中，而不是悄咪咪地在我们看不到的地方修改了基表的数据，简称，保证视图的一致性。

使用WITH CHECK OPTION可以让数据库自动为我们检查修改操作是否违背了一致性：

create view CSEE as
select sID, cName, major
from Apply 
where major = 'CS' or major = 'EE' 
with check option;

--当没有with check option时，
--执行insert into CSEE values(123, 'CMU', 'biology')是不被阻止的，但是是不安全的

使用自动的视图修改，优点是不需要用户的介入就能完成修改操作；缺点很明显，就是需要满足严格的限制条件

---

五、授权(Authorization)

不同的用户应该对数据库有不同的操作权限，这些权限应当由权限更大的用户进行授权

SQL标准下的授权和撤销权限操作如下：

--privs表示授予的权限：select(attName, ...), update(attName, ...)
--users表示获得权限的用户：public表示所有用户
Grant privs On R To users
[ with Grant option ]

--Cascade表示级联撤销下级的权限，但是下级经由其他上级得到的授权不会被撤销
--Restrict表示需要从下往上逐级撤销权限
Revoke privs on R From users
[Cascade / Restrict]

MySQL中的授权和撤销权限操作如下：

--查询权限
Show Grants For '用户名'@'主机名'

--授予权限
Grant privs On R To '用户名'@'主机名'

--撤销权限
Revoke privs on R From '用户名'@'主机名'