chap04 SQL - 数据库设计与实践

课程信息

• 课程：0A102 数据库设计与实践
• 章节：第4章 SQL
• 讲师：陈立军（ljchen）
• 来源：田浩然上传的资料
• 教材关联：《数据库系统概念》

---

一、SQL 概述

1.1 历史

• SQL (Structured Query Language)：1974年由 Boyce 和 Chamber 提出
• 1975-1979年，在 System R 上实现，由 IBM San Jose 研究室研制，称为 Sequel

1.2 体系结构

三级模式结构：

• 视图 (VIEW)：外模式，用户可见
• 基本表 (Base Table)：模式，逻辑结构
• 存储文件 (Stored file)：内模式，物理存储

1.3 标准化历程

标准	主要内容
SQL-86	”数据库语言SQL”
SQL-89	增加完整性约束支持
SQL-92	SQL-89 超集，新数据类型、更丰富的数据操作、更强的完整性/安全性
SQL-99	增加面向对象模型支持

1.4 特点

1. 一体化：集 DDL、DML、DCL 于一体；单一结构——关系，带来数据操作符的统一
2. 面向集合的操作方式：一次一集合
3. 高度非过程化：用户只需提出”做什么”，无须告诉”怎么做”，不必了解存取路径
4. 两种使用方式，统一的语法结构：既是自含式（用户使用），又是嵌入式（程序员使用）
5. 语言简洁，易学易用

1.5 核心操作符

功能	动词
数据查询	SELECT
数据定义	CREATE, ALTER, DROP
数据操纵	INSERT, UPDATE, DELETE
数据控制	GRANT, REVOKE

1.6 示例关系模式

DEPT(D# , DNAME , DEAN)      -- 系
S(S# , SNAME , SEX , AGE , D#)  -- 学生
C(C# , CN , PC#, CREDIT)     -- 课程
SC(S# , C# , GRADE)          -- 选课
PROF(P# , PNAME, AGE, D# , SAL)  -- 教师
PC(P# , C#)                  -- 任课

---

二、SQL 数据定义功能

2.1 域类型（SQL-92）

• char(n)：固定长度字符串
• varchar(n)：可变长字符串
• int / smallint：整数
• numeric(p, d)：定点数
• real / double precision：浮点数
• date / time：日期/时间
• interval：两个 date/time 之间的差

2.2 基本表定义（CREATE TABLE）

语法：

CREATE TABLE 表名 (
  列名  数据类型  [DEFAULT 缺省值]  [NOT NULL] [UNIQUE]
  [, 列名 数据类型  ...]
  [, PRIMARY KEY (列名 [, 列名] ...)]
  [, FOREIGN KEY (列名 [, 列名] ...) REFERENCES ...]
)

示例——学生表：

CREATE TABLE S (
  S#    CHAR(8),
  SNAME CHAR(8) NOT NULL DEFAULT 'Unknown',
  AGE   TINYINT,
  SEX   CHAR(1),
  PRIMARY KEY (S#),
  CHECK (SEX='M' OR SEX='F')
)

示例——课程表（自参照外码）：

CREATE TABLE C (
  C#    CHAR(4) PRIMARY KEY,
  CNAME CHAR(8) NOT NULL UNIQUE,
  PC#   CHAR(4) FOREIGN KEY REFERENCES C(C#)
)

示例——选课表（复合主码 + 双外码）：

CREATE TABLE SC (
  S#    CHAR(8),
  C#    CHAR(4),
  GRADE TINYINT,
  PRIMARY KEY (S#, C#),
  FOREIGN KEY (S#) REFERENCES S(S#),
  FOREIGN KEY (C#) REFERENCES C(C#),
  CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100))
)

2.3 修改基本表（ALTER TABLE）

-- 增加新列
ALTER TABLE S ADD LOCATION CHAR(30)
ALTER TABLE S ADD RESUME CHAR(100) NOT NULL
 
-- 修改列定义
ALTER TABLE S ALTER COLUMN RESUME CHAR(80)
 
-- 删除列/约束
ALTER TABLE S DROP COLUMN LOCATION

思考题：如何定义两个相互参照的表？——使用延迟约束或分步骤创建

2.4 撤消基本表（DROP TABLE）

DROP TABLE 表名

注意事项：

• 删除表定义及所有数据、索引、触发器、约束和权限规范
• 不能用于除去由 FOREIGN KEY 约束引用的表，必须先除去引用的约束或引用的表

2.5 临时表

• 草稿簿，试验中间的数据处理
• 只记录回滚信息，不记录重做信息，更新速度比普通表快4倍
• 私有临时表：CREATE TABLE #my_table（# 前缀）
• 全局临时表：CREATE TABLE ##my_table（## 前缀）
• 存储在 tempdb 数据库中

2.6 用户定义数据类型（UDDT）

-- SQL 标准
CREATE DOMAIN person-name CHAR(20)
 
-- SQL Server 方式
EXEC sp_addtype phone_number, 'varchar(20)', 'not null'

类似 C 语言中的 typedef，保证数据一致性

2.7 char vs varchar 的选择

变长之利： 减少存储开销 + 每页元组数高 变长之弊： 查询计算偏移 + 更新挪移数据 变长适用场景： 长短显著不一 + 很少发生变化

主码选型注意：

• 主码需要唯一性字符匹配
• 主码经常是其他表的外码，太长会占用大量空间
• 连接基于主外码，索引项太长会增加磁盘 I/O

2.8 IDENTITY 自增列

CREATE TABLE customer1 (
  cust_id   SMALLINT IDENTITY NOT NULL,
  cust_name VARCHAR(50) NOT NULL
)

相关函数：

• IDENT_SEED(表名)：返回种子值
• IDENT_INCR(表名)：返回增量值
• IDENT_CURRENT(表名)：返回指定表最后生成的标识值
• SCOPE_IDENTITY()：返回同一作用域最后生成的标识值
• @@IDENTITY：返回当前会话中任何作用域内最后生成的标识值

DBCC CHECKIDENT： 检查并更正标识值

• NORESEED：不重置，仅报告
• RESEED：用最大值重置

2.9 索引定义

CREATE [UNIQUE] [CLUSTERED] INDEX 索引名
ON 表名 (列名 [ASC/DESC] [, 列名 ASC/DESC] ...)

索引类型：

• UNIQUE：唯一性索引，不允许重复值
• CLUSTERED（聚簇索引）：表中元组按索引项的值排序并物理地聚簇在一起

索引的作用：

1. 查找元组
2. 表连接
3. 排序
4. 分组
5. 保证唯一性
6. 支持约束（主键、唯一约束）

创建选项：

• FILLFACTOR：索引页填满程度（100=完全填满，适合静态表；低填充适合频繁插入）
• IGNORE_DUP_KEY：创建唯一聚簇索引时，忽略重复行而非回滚整个 INSERT

组合索引： 建立在 A+B+C 上的索引，只对检索 A、A+B、A+B+C 的查询起作用（最左前缀原则）

索引的删除：

DROP INDEX 索引名

注意：不适用于通过 PRIMARY KEY 或 UNIQUE 约束创建的索引

索引设计原则：

• 可以动态定义/删除索引
• 用户不能直接引用索引，由系统决定使用方式（物理独立性）
• 在使用频率高、经常用于连接的列上建索引
• 索引过多：耗费空间 + 降低增删改效率 → 权衡取舍

2.10 约束定义

约束类型：

• PRIMARY KEY
• UNIQUE
• FOREIGN KEY
• CHECK
• DEFAULT

PRIMARY KEY vs UNIQUE 的区别：

• 一个表只能有一个 PRIMARY KEY，可以有多个 UNIQUE
• PRIMARY KEY 不允许 NULL，UNIQUE 允许 NULL（数量因数据库而异）

外键参照动作：

操作	RESTRICT	CASCADE	SET NULL
删除基本关系元组	有依赖则拒绝	级联删除依赖元组	依赖元组外码置空
修改基本关系主码	有依赖则拒绝	级联修改依赖外码	依赖元组外码置空

SQL Server 支持：NO ACTION、CASCADE、SET DEFAULT、SET NULL

全局约束： 涉及多个属性或多个关系的 CHECK 约束

注意：如果 S 中删除元组，不会触发 SC 表的 CHECK 子句——只有对 SC 表的更新才会触发

约束的命名与管理：

-- 命名约束
S# CHAR(4) CONSTRAINT S_PK PRIMARY KEY
AGE SMALLINT CONSTRAINT AGE_VAL CHECK(AGE >= 15 AND AGE <= 25)
 
-- 域约束
CREATE DOMAIN AGE_DOMAIN SMALLINT
  CONSTRAINT DC_AGE CHECK(value <= 25 AND value >= 15)
 
-- 添加/删除约束
ALTER TABLE S DROP CONSTRAINT S_PK
ALTER TABLE SC ADD CONSTRAINT SC_CHECK CHECK(S# IN (SELECT S# FROM S))

延迟约束 (ANSI SQL)：

CREATE TABLE emp (
  e# CHAR(10) PRIMARY KEY,
  ename CHAR(20),
  mgr CHAR(10) CONSTRAINT FK_Constraint FOREIGN KEY
    REFERENCES emp(e#)
    DEFERRABLE INITIALLY IMMEDIATE
)
 
-- 设置延迟约束
SET CONSTRAINT FK_Constraint DEFERRED

用途：解决自参照表的插入问题、相互参照表的插入问题

SQL Server 的未验证约束：

-- 添加不验证现有数据的约束
ALTER TABLE t1 WITH NOCHECK
  ADD CONSTRAINT skip_check CHECK (col_a > 1)

2.11 SQL 数据定义特点

• 随时修改：任何时候都可以执行数据定义语句，随时修改数据库结构
  • 非关系型数据库：必须在装入前完成全部定义，修改需卸出-重装
• 数据库定义不断增长：不必一开始就定义完整
• 数据库定义随时修改：不必一开始就完全合理
• 可实验：增加/撤消索引，检验对效率的影响

---

三、SQL 数据查询功能

3.1 基本结构

SELECT A1, A2, ..., An    -- 投影
FROM   r1, r2, ..., rm    -- 笛卡尔积/连接
WHERE  P                   -- 选择

对应关系代数：Π_A1,…,An(σ_P(r1 ⋈ r2 ⋈ … ⋈ rm))

3.2 SELECT 子句

• 目标列形式：列名、*、算术表达式、聚集函数
• *：所有属性
• 算术表达式：SELECT SNAME, 2008 - AGE FROM S
• 列拼接：SELECT pname + '老师的工资是' + salary + ... FROM professor

3.3 FROM 子句

• 列出查询的对象表
• 多表连接：

SELECT SNAME, CNAME, GRADE
FROM S, C, SC
WHERE S.S# = SC.S# AND C.C# = SC.C#

当目标列取自多个表时，需要显式指明来自哪个关系

3.4 WHERE 子句

• 比较运算符：>、<、>=、<=、=、<>
• 逻辑运算符：AND、OR、NOT
• BETWEEN：SAL BETWEEN 500 AND 800 等价于 SAL >= 500 AND SAL <= 800

3.5 重复元组处理

• SQL 缺省保留重复元组（ALL）
• 去重用 DISTINCT

SELECT DISTINCT S# FROM SC

思考题： 两个表 R(A, B), S(A, C)，A 是主码，哪些查询中的 DISTINCT 可以去掉？

• 如果选择列中包含主码，则结果必然唯一，DISTINCT 可省

3.6 元组显示顺序

ORDER BY 列名 [ASC | DESC]

示例：

SELECT * FROM S ORDER BY AGE ASC, SNAME DESC
-- 按列序号排序
SELECT fname, sal * 0.2 FROM faculty ORDER BY 2

3.7 更名运算（别名）

-- 属性更名
SELECT SNAME '姓名', SEX '性别', 2007-AGE '出生日期'
FROM S ORDER BY 出生日期
 
-- 关系更名（自连接）
SELECT S2.S#
FROM SC AS S1, SC AS S2
WHERE S1.S# = 's1' AND S1.C# = 'c1'
  AND S2.C# = 'c1' AND S1.GRADE < S2.GRADE

3.8 字符串操作

LIKE 匹配规则：

• %：匹配零个或多个字符
• _：匹配任意单个字符
• [a-f] / [abcdef]：指定范围内的字符
• [^a-f]：不在指定范围内的字符

ESCAPE 转义：

-- 用 x 作为转义字符
WHERE c1 LIKE 'x%%xx' ESCAPE 'x'

索引利用：

• LIKE 'd%'（前缀匹配）→ 可能用到索引
• LIKE '%d'（后缀匹配）→ 用不到索引（需要全文检索）

3.9 全文检索

创建：

CREATE FULLTEXT CATALOG catalog_name
CREATE FULLTEXT INDEX ON table_name (column_name)
  KEY INDEX index_name
  ON catalog_name

查询：

-- 精确匹配
SELECT * FROM documents WHERE CONTAINS(*, 'database and dataspace')
SELECT * FROM documents WHERE CONTAINS(author, 'Jim Gray AND NOT Jeff Ullman')
 
-- 自由文本匹配
SELECT * FROM documents WHERE FREETEXT(content, 'Adaptive Query Processing')

CONTAINS 支持：AND、OR、NOT、NEAR、短语精确匹配

3.10 连接类型

连接条件分类：

• 自然连接 (NATURAL)：公共属性相等，公共属性只出现一次
• ON <谓词>：满足谓词条件，公共属性出现两次
• USING (A1, A2, …)：指定属性相等，这些属性只出现一次

连接类型分类：

类型	说明
INNER JOIN（内连接）	舍弃不匹配的元组
LEFT OUTER JOIN（左外连接）	内连接 + 左边失配元组（右补 NULL）
RIGHT OUTER JOIN（右外连接）	内连接 + 右边失配元组（左补 NULL）
FULL OUTER JOIN（全外连接）	内连接 + 两边失配元组
CROSS JOIN（交叉连接）	笛卡尔积
UNION JOIN（并连接）	左边失配 + 右边失配

外连接必须有连接条件；内连接没有连接条件等价于笛卡尔积

3.11 空值（NULL）

空值测试：

WHERE GRADE IS NULL   -- 不能写 GRADE = NULL

空值运算规则：

1. 除 IS [NOT] NULL 之外，空值不满足任何查找条件
2. NULL 参与算术运算 → 结果为 NULL
3. NULL 参与比较运算 → 结果视为 FALSE（SQL-92 中为 UNKNOWN）
4. DISTINCT 对 NULL 的处理：两行全 NULL 是否算重复？（SQL Server 算重复）

空值处理函数：

-- 单值替换
SELECT S#, C#, ISNULL(GRADE, '缺考') FROM SC
 
-- 多值取第一个非空
SELECT S#, C#, COALESCE(GRADE, 0) FROM SC

排序时的空值：

• 升序：空值最后输出
• 降序：空值最先输出

这是 SQL Server 的默认行为，不同数据库可能不同

3.12 聚集函数

函数	作用
AVG	平均值
MIN	最小值
MAX	最大值
SUM	总和
COUNT	计数

易错点： 聚集函数不能直接出现在 WHERE 子句中

-- 错误！
SELECT S# FROM SC WHERE GRADE = MAX(GRADE)
-- 正确（用子查询）
SELECT S# FROM SC WHERE GRADE = (SELECT MAX(GRADE) FROM SC)

COUNT(*) vs COUNT(列名)：

• COUNT(*)：统计所有行（包括 NULL）
• COUNT(列名)：统计该列非 NULL 的行数

3.13 分组（GROUP BY）

GROUP BY 列名 [HAVING 条件表达式]

• GROUP BY 按指定列分组，每组使用聚集函数
• HAVING 对分组进行筛选，只将聚集函数作用于满足条件的分组

示例：

-- 每个学生的最高、最低、平均成绩
SELECT S#, MAX(GRADE), MIN(GRADE), AVG(GRADE)
FROM SC
GROUP BY S#
 
-- 最低成绩及格的学生的平均成绩
SELECT S#, AVG(GRADE)
FROM SC
GROUP BY S#
HAVING MIN(GRADE) >= 60

火眼金睛： SELECT 子句中的非聚集列，必须出现在 GROUP BY 子句中

-- 错误！A 不在 GROUP BY 中
SELECT A FROM R GROUP BY B
-- 正确
SELECT A FROM R GROUP BY A

WHERE vs HAVING 的区别：

• WHERE：在分组前过滤元组，不能用聚集函数
• HAVING：在分组后过滤分组，能用聚集函数

“白马非马”——WHERE 过滤的是行，HAVING 过滤的是组

3.14 嵌套子查询

（1）集合成员资格 — IN 子查询

-- 选修了c1号课程的学生姓名
SELECT SNAME FROM S
WHERE S# IN (SELECT S# FROM SC WHERE C# = 'c1')

（2）集合比较 — SOME / ALL 子查询

表达式 比较运算符 SOME (子查询)   -- 至少一个满足
表达式 比较运算符 ALL (子查询)    -- 全部满足

等价关系：

• = SOME ↔ IN
• <> ALL ↔ NOT IN
• < SOME：小于最大值
• < ALL：小于最小值

示例——平均成绩最高的学生：

SELECT S#
FROM SC
GROUP BY S#
HAVING AVG(GRADE) >= ALL (
  SELECT AVG(GRADE) FROM SC GROUP BY S#
)

（3）存在性测试 — EXISTS 子查询

-- 选修了c1号课程的学生姓名（相关子查询）
SELECT SNAME FROM S
WHERE EXISTS (
  SELECT * FROM SC
  WHERE C# = 'c1' AND S# = S.S#
)

IN 子查询与外层无关，每个子查询执行一次； EXISTS 子查询与外层有关，需要执行多次 → 相关子查询

（4）除法的 SQL 表达

选修了全部课程的学生姓名： 不存在任何一门课程，该学生没有选

SELECT SNAME FROM S S1
WHERE NOT EXISTS (
  SELECT C# FROM C C1
  WHERE NOT EXISTS (
    SELECT * FROM SC
    WHERE C# = C1.C# AND S# = S1.S#
  )
)

至少选修了s1号学生选修的所有课程的学生名： 不存在一门课程s1选了而所求学生没选

SELECT SNAME FROM S S2
WHERE NOT EXISTS (
  SELECT C# FROM SC SC1
  WHERE SC1.S# = 's1' AND NOT EXISTS (
    SELECT * FROM SC
    WHERE C# = SC1.C# AND S# = S2.S#
  )
)

（5）重复元组测试 — UNIQUE

-- 只教授一门课程的老师
SELECT PNAME FROM PROF
WHERE UNIQUE (
  SELECT P# FROM PC WHERE PC.P# = PROF.P#
)

3.15 派生关系

SQL-92 中允许在 FROM 子句中使用子查询：

SELECT SNAME, AVG_GRADE
FROM (
  SELECT SNAME, AVG(GRADE)
  FROM S, SC
  WHERE SC.S# = S.S#
  GROUP BY SC.S#
) AS result(SNAME, AVG_GRADE)
WHERE AVG_GRADE >= 60

派生关系 vs 视图：派生关系是临时的，仅在当前查询中有效

3.16 公用表表达式（CTE / WITH）

SQL Server 语法：

WITH
  Sum_T(S#, Sum_G) AS (
    SELECT S#, SUM(grade) FROM sc GROUP BY S#
  ),
  Avg_Sum_T(avg_sum_G) AS (
    SELECT AVG(sum_G) FROM Sum_T
  )
SELECT S#
FROM Sum_T, Avg_Sum_T
WHERE Sum_G > avg_sum_G

3.17 集合操作

操作	说明
UNION [ALL]	并
INTERSECT [ALL]	交
EXCEPT [ALL]	差

• 缺省去除重复元组
• INTERSECT 优先级高于 UNION 和 EXCEPT

UNION vs UNION ALL：

• UNION：去重，可能排序
• UNION ALL：保留重复，更快

注意： UNION 和 UNION ALL 在一起不满足结合律！ 顺序不同，结果可能不同（因为 UNION 会去重）

EXCEPT ALL 的实现技巧： 用标记列 + COUNT 实现

3.18 常用函数

日期函数：

• GETDATE()：当前日期
• DATEPART(datepart, date)：提取日期部分
• DAY/MONTH/YEAR(date)：日/月/年
• DATEDIFF(datepart, startdate, enddate)：日期差
• DATEADD(datepart, number, date)：日期加减

字符串函数：

• LEN(string)：字符个数
• LOWER/ UPPER(string)：大小写转换
• REVERSE(string)：反转
• SUBSTRING(string, start, length)：截取

技巧： 计算字符出现次数 = LEN(s) - LEN(REPLACE(s, 'a', ''))

---

四、SQL 数据修改功能

4.1 插入（INSERT）

-- 插入单条元组
INSERT INTO PROF (P#, PNAME, D#)
VALUES ('P123', '王明', 'D08')
 
-- 插入子查询结果
INSERT INTO EXCELLENT (S#, GRADE)
SELECT S#, AVG(GRADE)
FROM SC
GROUP BY S#
HAVING AVG(GRADE) > 90
 
-- 批量插入
BULK INSERT 表名 FROM 数据文件
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', ...)

@@ROWCOUNT：返回受上一语句影响的行数

4.2 删除（DELETE）

DELETE FROM 表名 [WHERE 条件]

示例：

-- 删除王明老师的任课记录
DELETE FROM PC
WHERE P# IN (SELECT P# FROM PROF WHERE PNAME = '王明')

TRUNCATE TABLE：

• 删除所有行，不记录单个行删除操作
• 比 DELETE 速度快，使用更少的系统和事务日志资源
• IDENTITY 计数器重置为种子值
• 不能用于有外键引用的表

4.3 更新（UPDATE）

UPDATE 表名
SET 列名 = 表达式 [, 列名 = 表达式 ...]
[WHERE 条件]

示例——条件更新：

-- 工资超过2000的缴10%所得税，其余缴5%
UPDATE PROF
SET SAL = CASE
  WHEN SAL > 2000 THEN SAL * 0.9
  ELSE SAL * 0.95
END

注意写法①②（两次 UPDATE）可能导致错误：先将 >2000 的调整后，可能部分变成 <=2000 又被二次调整 使用 CASE 表达式一次性更新才正确

---

五、视图

5.1 定义与撤消

CREATE VIEW view_name [(列名 [, 列名] ...)]
AS 查询表达式
[WITH CHECK OPTION]
 
DROP VIEW view_name

• WITH CHECK OPTION：对视图进行 INSERT/UPDATE 时，检查是否满足视图定义中的条件

5.2 示例

-- 计算机系教师视图
CREATE VIEW COMPUTER_PROF AS
  SELECT P#, PNAME, SAL
  FROM PROF, DEPT
  WHERE PROF.D# = DEPT.D# AND DEPT.DNAME = '计算机系'
 
-- 系工资统计视图
CREATE VIEW DEPTSAL(D#, LOW, HIGH, AVERAGE, TOTAL) AS
  SELECT D#, MIN(SAL), MAX(SAL), AVG(SAL), SUM(SAL)
  FROM PROF
  GROUP BY D#

5.3 视图更新

可更新视图的条件（行列子集视图）：

1. SELECT 子句不能包含聚集函数
2. 不能使用 UNIQUE 或 DISTINCT
3. 不能包含 GROUP BY
4. 不能包含经算术表达式计算出来的列
5. 从单个基本表使用选择、投影操作导出
6. 包含基本表的主码

思考题：列举一种不可更新的行列子集视图？ 答案：带 DISTINCT 的单表视图（消除了重复，无法确定更新哪一行）

WITH CHECK OPTION 的作用：

CREATE VIEW SC_V AS (SELECT * FROM SC WHERE GRADE > 85)
WITH CHECK OPTION
 
INSERT INTO SC_V VALUES ('s2', 'c4', 82)  -- 被拒绝！

---

六、数据库安全与存取控制

6.1 安全性控制层次

• 物理级：机房安全
• 人际级：人员管理
• 操作系统级：OS 安全
• 网络级：网络安全
• 数据库系统级：存取控制

6.2 存取控制措施

1. 准入：标识 + 口令
2. 权限控制：只能干什么 / 不能干什么
3. 资源控制：配额、DOS/DDOS 防护
4. 跟踪：监视、审计

6.3 授权与回收

-- 授权
GRANT 表级权限 ON {表名 | 视图名}
TO {用户 [, 用户]... | PUBLIC}
[WITH GRANT OPTION]
 
-- 回收
REVOKE 表级权限 ON {表名 | 视图名}
FROM {用户 [, 用户]... | PUBLIC}

表级权限： SELECT, UPDATE, INSERT, DELETE, INDEX, ALTER, DROP, RESOURCE, ALL

WITH GRANT OPTION：允许用户将权限转授给其他用户

权限图：

• 结点是用户，根结点是 DBA
• 有向边 Ui→Uj 表示 Ui 把权限授给 Uj
• 用户拥有权限的充要条件：权限图中有一条从根到该用户的路径

数据库级权限（SQL Server）：

• CONNECT：允许连接数据库
• RESOURCE：CONNECT + 建表、删表及索引
• DBA：RESOURCE + 授予/撤消其他用户权限

不允许 DBA 撤消自己的 DBA 权限

6.4 角色（Role）

• 角色是一组相关权限的集合
• 将多个权限打包成角色，便于批量授权管理

6.5 资源控制（Oracle Profile）

参数	限制
CPU_PER_SESSION	CPU 使用时间
LOGICAL_READS_PER_SESSION	逻辑读个数
SESSION_PER_USER	用户会话数
IDLE_TIME	会话空闲时间
CONNECT_TIME	会话持续时间
PRIVATE_SGA	会话专用 SGA 空间
PASSWORD_LIFE_TIME	口令有效期

6.6 审计（Audit）

审计作用：

• 监控可疑活动（非授权删除、越权管理）
• 收集性能数据（哪些表经常被修改、I/O 次数）

审计级别：

• 语句级：审计某种类型的 SQL 语句
• 权限级：审计某个系统权限的使用
• 实体级：对指定模式上的实体的指定语句审计

审计类别：

• 按成功与否：只审计成功 / 只审计不成功 / 都审计
• 按执行次数：会话审计（每次会话一次） / 存取方式审计（每次执行一次）

语法：

AUDIT [NOAUDIT] SQL语句或选项
  [BY 用户名]
  [BY SESSION | ACCESS]
  [WHENEVER [NOT] SUCCESSFUL]

6.7 统计数据库安全性

用户只能查询聚集值，不能访问个体数据

漏洞一：个体太少

• 查询”选修古典哲学史的学生的平均成绩” → 只有1人选修 → 直接泄露

漏洞二：多次查询，交叠太多

• 查询 n 个学生的总成绩为 x
• 查询 n 个学生 + A 的总成绩为 y
• → A 的成绩 = y - x

防范措施：

• 查询引用的数据不能少于 n 条
• 两个查询的交不能多于 m 条
• 推出个体信息至少需要 1 + (n-2)/m 次查询

---

七、嵌入式 SQL

7.1 为什么需要嵌入式 SQL

1. SQL 表达能力有限：有些操作无法用交互式 SQL 完成
   • SQL 在扩展能力，但扩展太多会降低优化能力和执行效率
2. 非声明性动作：与用户交互、图形化显示数据等只能用高级语言实现

7.2 需要解决的问题

1. 区分 SQL 与宿主语言语句：EXEC SQL ... ;
2. 数据传递：宿主变量
3. 操作方式协调：游标（SQL 一次一集合 vs C 一次一记录）
4. 执行信息反馈：SQLCA

7.3 宿主变量

EXEC SQL BEGIN DECLARE SECTION
    int    prof_no;
    char   prof_name[30];
    int    salary;
EXEC SQL END DECLARE SECTION

• SQL 语句中使用宿主变量时前面加 :，如 :prof_name
• 可用在 DML 语句中可出现常数的任何地方
• 可用在 SELECT … INTO 子句中

7.4 指示变量

• 指示返回值是否为 NULL，或字符串是否截断
• 返回值：=0（正常）、=-1（NULL）、>0（截断）

EXEC SQL SELECT PNAME, SAL
  INTO :prof_name :name_id, :salary :sal_id
  FROM PROF WHERE PNO = :prof_no;

7.5 游标（Cursor）

不需要游标： 返回单个元组的 SELECT、INSERT、DELETE、UPDATE

需要游标： 返回多个元组的 SELECT

游标分类：

• 滚动游标：位置可来回移动
• 非滚动游标：只能顺序取下一个
• 更新游标：对当前行加锁

游标使用流程：

声明游标 → 打开游标 → 检索游标 → 关闭游标 → 释放游标

-- 声明
DECLARE 游标名 [INSENSITIVE] [SCROLL] CURSOR FOR
  SELECT 语句 [FOR UPDATE [OF 列名]]
 
-- 打开
OPEN 游标名
 
-- 提取
FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]
  游标名 INTO 宿主变量表
 
-- 关闭
CLOSE 游标名
 
-- 释放
FREE 游标名

定位更新/删除：

UPDATE SC SET GRADE = GRADE * 1.05
WHERE CURRENT OF my_curs

7.6 SQLCA（SQL 通讯域）

• 结构类型，记录每条 SQL 语句的执行情况
• 包括错误代码、警告信息
• 应用程序根据 SQLCA 做错误处理

---

八、T-SQL 编程

8.1 批处理（Batch）

• 从应用程序一次性发送到服务器执行的一组 SQL 语句
• 编译成一个执行计划
• 编译错误：整个批处理都不执行
• 运行时错误：
  • 多数：停止当前语句及之后的语句
  • 少数（如违反约束）：仅停止当前语句，继续执行后面的

8.2 注释

/* 块注释 */
-- 行注释

注意：GO 语句在注释中仍会生效？——块注释中的 GO 会被忽略，但行注释后的 GO 要看具体工具

8.3 局部变量

DECLARE @变量名称 数据类型
SET @变量名称 = SQL表达式
-- 或
SELECT @变量名称 = 列名 FROM ...

• 作用域：从声明处到批处理或存储过程的结尾
• 跨批处理的变量引用会报错（每个批处理独立编译）

8.4 控制流语句

语句	作用
BEGIN…END	语句块
IF…ELSE	条件分支
WHILE + BREAK/CONTINUE	循环
GOTO	跳转（尽量少用）
RETURN	无条件退出
WAITFOR	等待指定时间
CASE	多分支表达式

CASE 表达式：

SELECT S#, C#,
  'classification' = CASE
    WHEN GRADE < 80 THEN 'poor'
    WHEN GRADE BETWEEN 80 AND 90 THEN 'middle'
    WHEN GRADE > 90 THEN 'excellent'
    ELSE 'Unknown'
  END
FROM SC

COALESCE： 返回第一个非 NULL 参数

8.5 存储过程

优点：

1. 执行效率高（预编译）
2. 重复使用
3. 统一操作流程
4. 维护业务逻辑
5. 安全性（通过存储过程间接访问表）

CREATE PROC procedure_name [@参数 数据类型 [OUTPUT]]
AS
  sql_statement

8.6 用户定义函数（UDF）

与存储过程的区别：

特性	存储过程	用户定义函数
返回值	只能返回整数	可返回各种数据类型
修改数据库	可以	不可以
调用方式	EXEC 执行	可用于表达式中
用途	数据库操作/设置	计算/查询封装

三种函数：

1. 标量函数：返回单个值
2. 内嵌表值函数：返回 TABLE，类似参数化视图
3. 多语句表值函数：返回 TABLE，函数体内有多条语句

8.7 断言（Assertion）

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

• 谓词，表达数据库总应该满足的条件
• 对每个可能违反断言的更新都进行检查
• 检查开销巨大 → 谨慎使用
• 实现方式：检查 “NOT EXISTS X，¬P(X)”

示例：

-- 不允许男同学选修张老师的课
CREATE ASSERTION ASSE2 CHECK (
  NOT EXISTS (
    SELECT * FROM SC
    WHERE C# IN (SELECT C# FROM C WHERE TEACHER = '张')
      AND S# IN (SELECT S# FROM S WHERE SEX = 'M')
  )
)

8.8 触发器（Trigger）

ECA 模型： Event-Condition-Action（事件-条件-动作）

• 主动数据库：pull vs push

触发器事件： INSERT、DELETE、UPDATE

触发器作用：

1. 维护约束
2. 商业规则
3. 监控（如传感器数据）
4. 辅助缓存/物化视图维护
5. 简化应用设计

触发器语法（ANSI）：

CREATE TRIGGER trigger-name
  BEFORE/AFTER INSERT/DELETE/UPDATE
  ON table-name [OF column-name]
  REFERENCING OLD/NEW ROW/TABLE AS identifier
  FOR EACH ROW / STATEMENT
  WHEN (search-condition)
  triggered-SQL-statement

行级触发器 vs 语句级触发器：

• FOR EACH ROW：每行触发一次，可用 OLD ROW / NEW ROW
• FOR EACH STATEMENT：每语句触发一次，可用 OLD TABLE / NEW TABLE

SQL Server 触发器：

• 使用 inserted 和 deleted 逻辑表
• AFTER 触发器（默认）
• INSTEAD OF 触发器（替代触发器，常用于视图更新）

CREATE TRIGGER S_SC_Delete
ON S AFTER DELETE
AS
  IF @@ROWCOUNT = 0 RETURN
  DELETE FROM SC WHERE S# IN (SELECT S# FROM deleted)

BEFORE 触发器示例：

-- 成绩不及格则改为60分
CREATE TRIGGER pass_grade_trigger
BEFORE INSERT ON SC
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (nrow.GRADE < 60)
  BEGIN SET nrow.GRADE = 60 END

替代触发器（INSTEAD OF）：

• 用于不可更新视图的更新操作
• 替代原操作，执行触发器定义的动作

触发器冲突： 一个事件激活多个触发器时的执行顺序

• 有序方案：轮流计算，条件为真则执行，然后考虑下一个
• 分组方案：同时计算所有条件，调度所有条件为真的触发器
• SQL Server：sp_settriggerorder 指定 first/last

触发器计算过程：

1. 事件发生，将激活的触发器放入队列 Q
2. 延迟原语句 S 的执行
3. 计算 OLD/NEW ROW 或 OLD/NEW TABLE
4. 处理所有 BEFORE 触发器，条件为真的执行；将 AFTER 触发器放入队列 Q
5. 将 S 的更新应用到数据库
6. 按优先级处理 Q 中的 AFTER 触发器；如果触发了新触发器，从第1步重复
7. 继续语句 S 的执行

---

九、重要思考问题汇总

1. 如何定义两个相互参照的表？ → 延迟约束 / 分步创建
2. char 还是 varchar？ → 看长度变化频率和更新频率
3. PRIMARY KEY 与 UNIQUE 的区别？ → 数量、NULL 处理
4. 聚集函数为什么不能写在 WHERE 里？ → WHERE 在分组前执行
5. WHERE vs HAVING？ → 过滤行 vs 过滤组
6. DISTINCT 什么时候可以省？ → 结果列包含主码时
7. 视图什么时候可更新？ → 行列子集视图 + 包含主码
8. 内连接 vs 外连接？ → 不匹配行的处理方式
9. NOT IN vs NOT EXISTS？ → NULL 语义不同，效率不同
10. 触发器 vs 约束？ → 约束是声明式，触发器是过程式

---

关联笔记

• chap02 ER模型 - 数据库设计与实践
• chap03 关系模型 - 数据库设计与实践
• chap05 SQL实践 - 数据库设计与实践
• chap07 关系规范化 - 数据库设计与实践
• 《数据库系统概念》
• 《SQL必知必会》