期末速通 教程

数据库系统

关系数据库程序设计

SQL语言

参见此处

约束与触发器

约束是DBMS需要强制执行的数据元素之间的关系。

键与外键

键标识了表中一个或多个特定属性的值的唯一性。需要在数据类型声明之后定义：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE movieExec (
    name CHAR(30),
    address VARCHAR(255),
    cert INT PRIMARY KEY,
    netWorth INT
); 
-- 或者
CREATE TABLE movieExec (
    name CHAR(30),
    address VARCHAR(255),
    cert INT,
    netWorth INT,
    PRIMARY KEY(cert)
);

实际上，一个表的键可以有多个，例如

1
2
3
4
5
6
7
8
9

CREATE TABLE movies (
    title CHAR(100),
    year INT,
    length INT,
    genre CHAR(10),
    studioName CHAR(30),
    producerC INT,
    PRIMARY KEY (title, year)
);

其中，title和year都是表movies的键。

📝 备注

单值的键允许如上的两种定义方式，多值的键只有一种定义方式。

原则上PRIMARY KEY (title, year)和PRIMARY KEY (year,title)有差异。

一个关系只允许存在一个PRIMARY KEY，但可以有多个UNIQUE KEY。

声明为PRIMARY KEY的任何属性在任何元组中都不能为NULL。但是声明为UNIQUE的属性可能有NULL，并且可能有几个带NULL的元组。

出现在一个关系属性中的值必须出现在另一个关系的某些属性中时，可以使用外键（Foreign Key）。

定义外键时，使用关键字REFERENCES：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE movieExec (
    name CHAR(30),
    address VARCHAR(255),
    cert INT PRIMARY KEY,
    netWorth INT
);

CREATE TABLE studio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presC INT REFERENCES movieExec(cert)
);
-- 或者
CREATE TABLE studio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presC INT,
    FOREIGN KEY(presC) REFERENCES movieExec(cert)
);

外键尽管在一个关系中定义，但是涉及两个关系（其他关系上的key！），逻辑上两个关系的定义有先后。

外键的本质：studio.presC属性中出现的值必须出现在movieExec.cert中，可表示为 $\text{studio.presC} \subseteq \text{movieExec.cert} + \text{NULL}$

引用完整性

在含有外键约束的表中进行如下操作：

  1. 对studio的插入或更新可能引入movieExec中没有的值（操作子表）。
  2. 删除或更新movieExec可能会导致studio的某些元组的值受到影响（操作父表）。

DBMS对于操作1将直接拒绝（对父表不能无中生有），对于操作2则存在以下3种选择：

  1. 缺省原则：拒绝任何违反引用完整性的更新。
  2. 级联原则：被引用属性（组）的改变被仿造到外键上，即对外键执行同样的操作。
  3. 置空值原则：当被引用的关系上的更新影响到外键值时，后者被改为空值。

这些选项可以在删除或修改时独立选择，并且需要同外键一同声明。例如：

1
2
3
4
5
6
7

CREATE TABLE studio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presC INT REFERENCES movieExec(cert)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

延迟约束检查

若插入操作会违反外键约束，则必须执行两次插入操作，并且

  1. 首先，必须将两个插入操作组成一个单一事务。
  2. 然后，需要有一种方法通知DBMS不要检查其约束，直到整个事务执行完成并要提交为止。

为了通知DBMS第2点，任何约束的声明后面可以有DEFERRABLE或NOT DEFERRABLE选项。

  1. NOT DEFERRABLE是默认选项，表示每执行一条数据库更新语句时如果该更新可能违反外键约束，则随后立即检查该约束。
  2. DEFERRABLE则表示约束检查将推迟到当前事务完成时进行。

DEFERRABLE后面可能有INITIALLY DEFERRED或INITIALLY IMMEDIATE选项。前者表示检查仅被推迟到事务提交前执行，后者表示检查在每个语句后都立即执行，

延迟约束检查的两个关键点：

  1. 任何类型的约束都可以命名。
  2. 如果约束有名称（MyConstraint），可以使用如下的SQL语句将该约束从立即检查改为延迟检查：
1

SET CONSTRAINTS MyConstraint DEFERRED;

属性和元组上的约束

非空约束

NOT NULL是与属性相连的简单约束，作用是不允许元组的该属性取空值。声明方法为：

1
2
3
4
5

CREATE TABLE studio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presC INT REFERENCES movieExec(cert) NOT NULL
);

使用非空约束后，需要注意以下情况：

  1. 插入元组时不能只给出部分值。例如对studio关系插入元组时不能只给出名字和地址，因为此时它的PresC#值可能为空。
  2. 引用完整性中的置空值原则不能与非空约束一起使用。

Check约束

更复杂的约束是将保留字CHECK和用圆括号括起来的条件附加在属性声明上，使得该条件成为该属性的每个值都应该满足的条件。CHECK约束将在元组为该属性获得新值时触发检查。如果新值违反约束，则该修改被拒绝。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE studio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presc INT REFERENCES movieexec(cert)
    CHECK (presc >= 100000)
);

CREATE TABLE moviestar (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR(1) CHECK (gender IN ('F', 'M')),
    birthdate CHAR(10)
);

条件可以通过表达式中的属性名称引用被约束的属性。但是如果条件要引用其他关系或属性，则该关系必须是子查询的FROM子句中出现的关系（即该关系式被检查属性的关系）。但是该约束条件对于其他关系不可见。

对于如下所示的关系：

1
2
3
4
5

CREATE TABLE Studio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presc INT CHECK (presC# IN (SELECT cert# FROM movieExec))
);

当向Studio引入一个不在MovieExec中cert#的presC#的值时，这样的修改将会被拒绝。

更重要的是，如果改变MovieExec的关系（例如删除电影公司经理元组），该变化对于上述的CHECK约束不可见。即使违反了presC#上的CHECK约束，删除动作仍被执行。

为了对单个表 $R$ 的元组声明约束，可以在定义表时在属性列表、键、外键声明上附加CHECK，其约束条件用括号括起。

与基于属性的CHECK约束类似，约束条件可以通过表达式中的属性名称引用被约束的属性。但是如果条件要引用其他关系或属性，则该关系必须是子查询的FROM子句中出现的关系（即该关系式被检查属性的关系）。但是该约束条件对于其他关系不可见。

1
2
3
4
5
6
7

CREATE TABLE moviestar (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR(1),
    birthdate CHAR(10),
    CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')
);

每次向关系 $R$ 中插入元组以及当 $R$ 的元组被修改时，都要检查基于元组的CHECK约束条件。

触发条件更强、更频繁了，对元组的任意属性的修改都会触发约束。

修改约束

命名约束

约束的命名可以在约束前加上关键字CONSTRAINT和该约束的名字。

1
2
3
4
5
6
7

CREATE TABLE movieStar (
    name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR(1) CONSTRAINT NoAndro CHECK (gender IN ('F', 'M')),
    birthdate CHAR(10),
    CONSTRAINT rightTitle CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')
);

修改约束

实现约束检查立即执行与延期执行的相互转换：

1

SET CONSTRAINT constraintName DEFERRED (or IMMEDIATE);

删除指定的约束：

1

ALTER TABLE relationName DROP CONSTRAINT constraintName;

添加约束：

1

ALTER TABLE relationName ADD CONSTRAINT constraintName CHECK(...)

断言

断言是SQL逻辑表达式，并且总是为真。它们是数据库模式的一部分，等同于表。

创建断言：

1

CREATE ASSERTION <断言名> CHECK (<条件>)

当断言创立时，断言的条件必须为真并且永远保持为真。任何引起断言条件为假的数据库更新均会被拒绝。

使用断言：断言条件中引用的任何属性都必须要介绍，特别是在SELECT-FROM-WHERE表达式中的关系。

触发器

触发器（Trigger）也称为“事件——条件——动作规则”。它与之前的约束有以下的不同：

  1. 仅当声明的事件发生时，触发器才被激活。
  2. 当触发器被事件激活时，触发器测试触发的条件。如果条件不成立，则相应该事件的触发器不进行任何操作。
  3. 如果触发器声明的条件被满足，则与该触发器相连的动作由DBMS执行。

SQL中的触发器 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
    OLD ROW AS OldTuple,
    NEW ROW AS NewTuple
FOR EACH ROW
WHEN (OldTuple.netWorth > NewTuple.netWorth)
    UPDATE MovieExec
    SET netWorth = OldTuple.netWorth
    WHERE (cert# = NetTuple.cert#);

视图与索引

虚拟视图

虚拟视图不以物理的形式存在，通过类似查询的表达方式定义，可以将视图当作物理存在进行查询，在某些情况下，视图也可以更新。

定义视图 

1
2
3
4

CREATE VIEW ParamountMovies AS
    SELECT title, year
    FROM Movies
    WHERE studioName = 'Paramount';

查询视图

视图可以像一个被真正存储的表一样来查询，只需要FROM后接上视图名称。

1

SELECT title FROM ParamountMovies WHERE year = 1979;

它与以下的SQL语句等效：

1

SELECT title FROM Movies WHERE studioName = 'Paramount' AND year = 1979;

视图也可以作为查询的子表：

1
2
3

SELECT DISTINCT starName 
FROM ParamountMovies, StarsIn
WHERE title = movieTitle AND year = movieYear;

视图更新

在特定条件下可以对视图进行插入、删除和修改操作。对于一些充分简单的视图，可以将对视图的更新转变为一个等价的对基本表的更新。

修改视图

删除视图：

1

DROP VIEW ParamountMovies;

删除视图不会影响基本关系Movies中的任何信息，但是此后不能在对该视图进行任何查询或修改操作。

特别地，若直接删除关系Movies：

1

DROP TABLE Movies;

则表Movies和视图ParamountMovies都将不可使用。

可更新视图

SQL仅允许这样的视图更新操作：该视图是由单个关系 $R$ 使用 SELECT关键字选取出的一些属性组成，满足以下要求：

  1. WHERE子句在关系中不能使用关系 $R$。
  2. FROM语句只能包含一个关系 $R$，不能再有其他关系。
  3. SELECT语句中的属性必须足够多，保证插入数据时可以使用NULL或其他默认值填充未指明的属性。

对可更新视图的修改会被翻译成对基表的修改：

  • 向视图插入：向基表插入视图中的值，其他属性填NULL或默认值。
  • 从视图删除：从基表删除满足视图定义条件和删除条件的元组。
  • 更新视图：更新基表中同时满足视图定义条件和更新条件的元组。

视图中的触发器

当视图上定义了一个触发器时，可以使用INSTEAD OF代替BEFORE或AFTER。这样，当一个事件激活了触发器后，触发器的操作将会代替事件本身而被执行。

例如：

1
2
3
4
5
6

CREATE TRIGGER ParamountInsert
INSTEAD OF INSERT ON ParamountMovies
REFERENCING NEW ROW AS NewRow
FOR EACH ROW
INSERT INTO Movies(title, year, studioName)
VALUES(NewRow,.title, NewRow.year, 'Paramount');

ParamountMovies视图本来可更新，但插入时可能因为studioName缺失而导致插入后在视图中看不到。因此可以用INSTEAD OF触发器把视图插入转换为对 Movies的正确插入。

物化视图

如果一个视图经常使用，则可以将其物化。物化视图会把查询结果存储起来，在任何时间都保存它的值。

1
2
3
4

CREATE MATERIALIZED VIEW MovieProd AS
SELECT title, year, name 
FROM Movies, MovieExec
WHERE producerC# = cert#;

对物化视图的更改都是增量式的，不需要从定义重新计算和构造整个视图。只需要少量的对基本表的查询和对物化视图的修改。

然而，使用物化视图后，每次基表变化都可能导致物化视图变化。如果每次都重新构建，成本太高。解决方案是周期性重建，例如每天或每晚统一刷新。

索引

索引是一种数据结构，能提高在属性上的查找具有某个特定值的元组的效率。

索引的声明

创建索引：

1

CREATE INDEX KeyIndex ON Movies(title, year);

删除索引：

1

DROP INDEX KeyIndex;

索引的选择

索引的选择是衡量数据库设计的一个重要因素。如果该属性接近键，索引通常更有用。但索引会增加插入、删除和更新的维护成本。

通常，关系被存储在很多的磁盘块上，而查询或更新操作的主要代价来自于将所需的磁盘块读入到主存的数目。使用索引后，对关系的更新还需要对索引进行修改。

服务器环境下的SQL

三层体系结构

大型数据库具有通用的体系结构，称为三层（三阶）结构，区分了三种不同而又相互关联的功能：

  1. Web服务器：连接客户端与数据库系统的进程。
  2. 应用服务器：执行系统所有操作的进程。
  3. 数据库服务器：运行DBMS并执行应用服务器请求的查询和更新。

三层体系结构

集中式数据库：数据集中存储，优点是管理简单，但访问速度、扩展性和并发能力可能受限。

分布式数据库：把数据分散存储在多个通过网络连接的节点上，以获得更大容量、更高访问速度、更强扩展性和更高并发。包括 DDBS（物理上分布，逻辑上集中）和 FDBS（物理上分布，逻辑上也分布）两种架构。

SQL环境

SQL环境可以看作安装并运行在系统上的DBMS，它包含以下几个部分：

要素 内容
模式（schema） 表、视图、断言、触发器和其他信息类型的集合，是组织的基本单元
目录（catalog） 模式的集合，是支持唯一的可访问术语的基本单元，目录中的模式名不可重复
簇（cluster） 目录的集合，是被提交的查询的最大范围

SQL环境

SQL程序接口

  1. 把专门语言写的代码存储在数据库内部，如PSM、PL/SQL。
  2. 把SQL嵌入宿主语言中，如C。
  3. 使用连接工具让普通语言访问数据库，如CLI、JDBC、PHP/DB。

存储过程

持久性存储模块PSM是SQL最新标准的一部分，它允许用简单通用的语言编写过程，并将它们存储在数据库中作为模式的一部分。

创建PSM函数和过程

基本过程和函数的声明：

1
2
3
4
5
6
7
8
9

-- 声明过程
CREATE PROCEDURE <name> (<parameter>)
    <local declarations>
    <body>

-- 声明函数
CREATE FUNCTION <name> (<parameter>) RETURNS <type>
    <local declarations>
    <body>

PSM的参数是模式-名字-类型的三元组，不仅要在参数名后添加对应的类型，还需要一个模式的前缀：

  • IN：参数仅输入，不改变值。默认可省略。
  • OUT：参数仅输出。
  • INOUT：参数即可输入又可输出。

PSM的简单语句

功能 格式
调用语句 CALL <过程名> (<参数>)
返回语句 RETURN <表达式>
局部变量声明 DECLARE <名称> <类型>
赋值语句 SET <变量> = <表达式>
语句组 BEGIN...END

分支语句 

1
2
3
4
5
6
7
8
9

IF <condition> THEN
    <statement list>
ELSEIF <condition> THEN
    <statement list>
ELSEIF
    ...
ELSE
    <statement list>
END IF;

循环语句 

1
2
3

LOOP
    <statement list>
END LOOP;

SQL的安全机制与授权

权限

SQL中定义了九种类型的权限，一些重要权限如下：

权限 作用
SELECT 查询关系，可限定只能查询某些属性
INSERT 插入元组，也可以只允许插入某些属性
DELETE 删除元组
UPDATE 更新元组，可限定只能更新某些属性
REFERENCES 允许某关系/属性被外键等约束引用
USAGE 允许在自己的声明中使用某个非关系、非断言的模式元素
TRIGGER 允许在关系上定义触发器
EXECUTE 允许执行 PSM 过程或函数等代码
UNDER 允许创建给定类型的子类型

其他未给值的属性使用默认值或NULL。

创建权限

所有SQL元素都有一个属主，他拥有其所属事务的所有权限。SQL中有三种建立属主身份的情况：

  1. 创建模式时，所创建的模式中的元素的所有权都属于创建者。
  2. 会话通过connect语句初始化时，可使用AUTHORIZATION子句指定用户。
  3. 创建模块时，可使用AUTHORIZATION子句选择它的属主。

只有当前授权 ID 拥有执行该 SQL 操作所需的全部权限时，操作才可执行。

权限来源可以是：数据所有者身份、所有者授予、授予给 PUBLIC、通过可执行模块间接获得

授予权限

属主对其创建的对象拥有所有权限。也可以把权限授予其他用户。

授权语句的格式如下：

1

GRANT <权限列表> ON <数据库元素> TO <用户列表> (WITH GRAND OPTION);

若使用WITH GRANT OPTION，被授权者还可以继续把该权限授给别人。

收回权限

被授予的权限可以随时收回，收回权限遵循级联原则。

1

REVOKE <权限列表> ON <数据库元素> FROM <用户列表> (选项);

可选项：

  • CASCADE：撤销会级联传播，凡是基于该权限继续授出的权限都会失效。
  • RESTRICT：如果权限已经被继续授权给别人，则撤销失败，用来提醒必须先处理下游授权。

授权图

授权图是一种表示用户之间权限传递的图。

节点表示用户、权限、是否带grant option、是否为所有者。不同权限对应不同节点。

边 $X \rightarrow Y$ 表示 $Y$ 是由 $X$ 授权得到的。

例如：UPDATE ON R和UPDATE(a) ON R是不同的节点。

授权图的含义

元素 含义
$AP$ 表示授权 ID A 拥有权限 $P$
$P^*$ 表示权限 $P$ 带grant option
$P^{**}$ 表示权限 $P$ 的源头，即对象所有者。** 蕴含grant option

只要从某个 $XP^{**}$ 节点到 $CQ$、$CQ^*$ 或 $CQ^{**}$ 有路径，用户 $C$ 就拥有权限 $Q$。这里 $P$ 必须是 $Q$ 的超权限。$P$ 可以等于 $Q$，$X$ 也可以等于 $C$。

授权图的修改

  • 如果 $A$ 用CASCADE从 $B$ 撤销 $P$，则删除 $AP$ 到 $BP$ 的边。
  • 如果 $A$ 使用RESTRICT，且 $BP$ 还有指向其他节点的边，则拒绝撤销，图不变。

修改边之后，必须检查每个节点是否还能从某个 ** 所有者节点到达。如果某节点没有这样的路径，就表示该权限已被撤销，应从图中删除。

© 2025 - 2026 RunicDolphin806
网站总访客数：Loading

使用 Hugo 构建
主题 Stack 由 Jimmy 设计