第三章 关系数据库标准语言SQL

概述

SQL的特点

1. 综合统一:
   SQL 将多种数据库操作语言的功能集于一身，使用统一的语法完成不同任务。

   • 数据查询语言 (DQL): SELECT - 用于检索数据。

   • 数据定义语言 (DDL): CREATE, DROP, ALTER - 用于定义和管理数据库对象（如表、视图、索引）。

   • 数据操纵语言 (DML): INSERT, UPDATE, DELETE - 用于修改表中的数据。

   • 数据控制语言 (DCL): GRANT, REVOKE - 用于用户授权和访问控制。

2. 高度非过程化:
   用户只需指明“做什么”，即需要检索或操作什么样的数据，底层的数据库管理系统会负责解析SQL语句，并自动优化生成最高效的执行计划。

3. 面向集合的操作方式:
   SQL 的操作对象和返回结果都是元组的集合。

4. 以同一种语法结构提供两种使用方式：
   即交互式和嵌入式。

5. 语言简捷，易学易用（?）

重要基本概念

1. 基本表 vs. 导出表

   • 基本表:

     • 是实际存在的表，在数据库中对应独立的存储文件，是数据存储的基础。

   • 导出表:

     • 是从基本表中派生出来的表，是一个逻辑概念。有两种主要的导出表：

     • ① 视图 (View):

       • 这是一个虚拟表。数据库中只存储视图的定义（即一条 SELECT 语句），而不存储视图的数据。

       • 当查询视图时，DBMS会将其定义与用户的查询合并，最终转化为对基本表的操作。

     • ② 快照 (Snapshot):

       • （在许多现代数据库中也称为物化视图 Materialized View）

       • 与视图不同，快照是将查询结果物理存储起来的一个副本，并会定期从基本表刷新数据。主要用于提高复杂查询的性能。

2. SQL与三级模式结构

   • 外模式 → 视图: 为不同用户提供定制化的数据视图。

   • 模式 → 基本表: 定义了数据库的全局逻辑结构。

   • 内模式 → 存储文件: 定义数据的物理存储，对用户透明。

SQL数据查询

示例用表说明：

• S (S#, SN, SA, SD): 学生表 (学号, 姓名, 年龄, 所在系)

• C (C#, CN, PC#): 课程表 (课程号, 课程名, 先修课号)

• SC (S#, C#, G): 选课表 (学号, 课程号, 成绩)

基本查询结构：SELECT-FROM-WHERE

SELECT [目标列名]
FROM   [基本表 或 视图]
WHERE  [检索条件];

• 执行逻辑:

  1. FROM: 首先确定要查询的数据源表。

  2. WHERE: 按照“检索条件”对表中的行（元组）进行水平方向的筛选。

  3. SELECT: 从筛选后的结果中，挑选出“目标列”，进行垂直方向的投影。（与投影运算不同，默认允许重复行！！！）

• 结果: 一个查询块的执行结果仍然是一个表。

单表查询

1. 投影查询

• 目的: 选择表中的特定列。

• 语法: SELECT后跟列名，省略WHERE子句。

• 去重: 使用 DISTINCT 关键字可以消除结果中的重复行。

-- 例1: 检索所有学生的姓名和年龄
SELECT SN, SA FROM S;
​
-- 例2: 检索所有被选修的课程的课程号（不重复）
SELECT DISTINCT C# FROM SC;

2. 选择查询

• 目的: 根据条件筛选表中的行。

• 语法: 使用 WHERE 子句指定条件。

• 常用运算符:

  • 比较: =, <>, !=, >, <, >=, <=

  • 逻辑: AND, OR, NOT

  • 范围: BETWEEN ... AND ... （闭区间）

  • 集合: IN, NOT IN (详见嵌套查询)

  • 模糊匹配: LIKE (详见其他技巧)

  • 空值判断: IS NULL, IS NOT NULL

-- 例1: 检索选修了'C2'课程的学生的学号和成绩
SELECT S#, G FROM SC WHERE C# = 'C2';
​
-- 例2: 检索选修'C1'或'C2'且成绩大于70分的记录
SELECT S#, C#, G FROM SC WHERE (C#='C1' OR C#='C2') AND G >= 70;
​
-- 例3: 检索成绩在70到85分之间的记录
SELECT S#, C#, G FROM SC WHERE G BETWEEN 70 AND 85;

3. 结果排序

• 目的: 将查询结果按指定列进行排序。

• 语法: 使用 ORDER BY <列名> [排序方式] 子句，它必须是查询块的最后一个子句。

• [排序方式] 是一个可选部分：

  • ASC: 升序 (缺省默认值)

  • DESC: 降序

• 支持多列排序。

-- 检索全体学生信息，按系号升序，同系学生按年龄降序
SELECT * FROM S ORDER BY SD ASC, SA DESC;

连接（连表）查询

• 目的: 同时查询多个表，实现多表数据的联合检索。

• 核心: 在 WHERE 子句中指定连接条件（通常是主键=外键）。

1. 内连接

• FROM 后列出所有要连接的表，WHERE 中写明连接条件和筛选条件。

-- 检索学生张华所学课程的成绩
-- 连接条件: S.S# = SC.S#
-- 筛选条件: SN = '张华'
SELECT SN, C#, G
FROM S, SC
WHERE S.S# = SC.S# AND SN = '张华';
-- 注意: 当不同表有同名列时，必须用"表名.列名"来区分。

2. 自身连接

• 目的: 将一个表与它自身进行连接，常用于比较同一表中不同行的数据。

• 实现: 在 FROM 子句中为同一个表定义两个别名 (Alias)，将其视为两个独立的表。

-- 检索所有比李勇年龄大的学生姓名和年龄
SELECT X.SN, X.SA
FROM S X, S Y
WHERE X.SA > Y.SA AND Y.SN = '李勇';

3. 外连接

问题： 内连接只会返回在两个表中都能找到匹配的记录。但如果我们想看一个表中的所有记录，以及它在另一个表中的匹配情况（即使没有匹配项），该怎么办？

例如： 我们想列出所有学生的选课成绩，这个名单里必须包括那些一门课都没选的学生。

解决方案： 使用外连接 (Outer Join)。

外连接通过在 WHERE 子句的连接条件中使用一个特殊的操作符 (*或+) 来实现。这个操作符的作用是，指定哪一个表是允许出现 NULL 值的可选表。

• 语法规则： 将 (*) 放在连接条件中某个表的列名一侧。另一个不带 (*) 的表就是主表，主表的所有记录都将被保留。

示例：列出所有学生的选课情况，包括未选课的学生

假设我们的表数据如下：

学生表 S

选课表 SC

SQL查询语句

SELECT S.S#, S.SN, SC.C#, SC.G
FROM S, SC
WHERE S.S# = SC.S#(*);

• WHERE S.S# = SC.S#(*):

  • (*) 被放在了 SC 表的 S# 列旁边。

  • 这表示 S 表是主表，其所有行都会被包含在结果中。

  • SC 表是可选表。如果某个学生在 SC 表中找不到匹配的选课记录，那么 SC 表的列将被 NULL 填充。

查询结果

补充： 实际上(*或+)是一种较久的语法。现在更常见的做法是在 FROM 子句中使用 JOIN 关键字。例如，LEFT JOIN 可以达到完全相同的效果，它明确指定了保留左侧表（S）的所有记录：

SELECT S.S#, S.SN, SC.C#, SC.G
FROM S LEFT JOIN SC ON S.S# = SC.S#;

嵌套查询

• 概念: 在一个查询块的WHERE、FROM 或 HAVING子句中嵌入另一个查询块。外层的叫主查询（或外部查询），内层的叫子查询（或嵌套查询）。

• 分类:

  • 不相关子查询: 子查询可独立执行，其结果被主查询使用。

  • 相关子查询: 子查询的执行依赖于主查询的当前行，主查询每处理一行，子查询就要重新执行一次。

1. 带有比较运算符的子查询

• 当子查询返回单个值时，可直接用 =, >, < 等比较。

• 当子查询返回多个值时，必须配合 ANY 或 ALL 使用。

  • > ANY: 大于子查询结果中的任意一个值（即大于最小值）。

  • > ALL: 大于子查询结果中的所有值（即大于最大值）。

-- 检索与李勇同岁的学生 (子查询返回单值)
SELECT SN FROM S WHERE S.SA = (SELECT SA FROM S WHERE SN='李勇');
​
-- 检索C2课程成绩最高的学生学号 (子查询返回多值，这里用ALL)
SELECT S# FROM SC WHERE C#='C2' AND G >= ALL (SELECT G FROM SC WHERE C#='C2');

2. 带有 IN 的子查询

• value IN (subquery): 判断 value 是否在子查询的结果集中。

• IN 等价于 = ANY。

• 否定形式为 NOT IN ，等价于 != ANY 。

-- 检索选修了C2课程的学生姓名
SELECT SN FROM S WHERE S# IN (SELECT S# FROM SC WHERE C#='C2');

3. 带有 EXISTS 的子查询

• EXISTS (subquery): 判断子查询的结果集是否为空。如果不为空，则返回 TRUE；否则返回 FALSE。

• EXISTS 通常用于相关子查询，效率较高。

-- 检索选修了C2课程的学生姓名 (用EXISTS实现)
SELECT SN
FROM S
WHERE EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#='C2');
-- 解释: 对于S表的每一行，检查SC表中是否存在对应的C2选课记录。

4. 使用 NOT EXISTS 实现全称量词 (逻辑除法)

• 由于 SQL 中不存在直接的全称量词，需要用 NOT EXISTS 来表达全称量词。

• 转换逻辑: ∀(x)P ⇔ ¬(∃x ¬P(x))

-- 检索选修了所有课程的学生姓名
-- 逻辑: "不存在一门课程，该学生没有选修"
SELECT SN
FROM S
WHERE NOT EXISTS ( -- 不存在这样的课程 C
    SELECT *
    FROM C
    WHERE NOT EXISTS ( -- 这门课 C，该学生 S 没有选修
        SELECT *
        FROM SC
        WHERE S#=S.S# AND C#=C.C#
    )
);

集合查询

• 目的: 将两个或多个 SELECT 语句的结果集合并。

• 要求: 参与操作的查询结果必须具有相同数量的列，且对应列的数据类型相容。

• 操作符:

  • UNION: 并集 (自动去重)。

  • UNION ALL: 并集 (保留所有重复行)。

  • INTERSECT: 交集。

  • MINUS (或标准SQL中的 EXCEPT): 差集。

-- 检索选修了C1课程或C2课程的学生学号
(SELECT S# FROM SC WHERE C#='C1')
UNION
(SELECT S# FROM SC WHERE C#='C2');

-- 检索无人选修的课程号和名称。
SELECT C#, CN
FROM C
WHERE C# IN (
    -- 用全部课程的集合 减去 已被选修课程的集合 得到 无人选修的课程的集合
    
    -- 获取所有课程号的集合
    SELECT C# FROM C
    
    MINUS
    
    -- 获取所有被选修过的课程号的集合
    SELECT DISTINCT C# FROM SC
);

聚合函数（库函数）与分组查询

1. 聚合函数

• 对一组值进行计算，返回单个值。只能在 SELECT 和 HAVING 子句中出现。

• 常用函数:

  • COUNT(*): 统计行数。

  • COUNT([DISTINCT] column): 统计某列的非空值个数（带 DISTINCT 则统计不重复非空值个数）。

  • SUM(column): 对列求和。

  • AVG(column): 对列求平均值。

  • MAX(column): 对列求最大值。

  • MIN(column): 对列求最小值。

-- 检索学生总人数
SELECT COUNT(*) FROM S;
​
-- 检索选修课程的学生总人数（去重）
SELECT COUNT(DISTINCT S#) FROM SC;
​
-- 求C1课程的最高分
SELECT MAX(G) FROM SC WHERE C#='C1';

2. 分组查询 GROUP BY

• 目的: 将表中具有相同值的行分到一组，然后对每一组使用聚合函数。

• SELECT子句中只能包含分组列和聚合函数。

3. 分组过滤 HAVING

• 目的: 对 GROUP BY 产生的分组结果进行筛选。

• 与WHERE的区别:

  • WHERE 子句作用于分组前的原始数据行。

  • HAVING 子句作用于分组后的组。

• 执行顺序: WHERE → GROUP BY → HAVING → SELECT 注意SELECT在最后执行！！！

-- 检索至少选修了3门课程的学生学号和选课门数
SELECT S#, COUNT(*)
FROM SC
GROUP BY S#
HAVING COUNT(*) >= 3;
​
-- 求选修四门以上且成绩及格的课程的学生学号和总成绩
SELECT S#, SUM(G)
FROM SC
WHERE G >= 60        -- 1. 先筛选出所有及格的记录
GROUP BY S#          -- 2. 按学号分组
HAVING COUNT(*) >= 4 -- 3. 筛选出选课数>=4的组
ORDER BY SUM(G) DESC;-- 4. 按总成绩排序

其他查询技巧

1. 算术表达式

• SELECT子句中可以使用 +, -, *, / 等运算符进行计算，并使用 AS 定义别名（ AS 可忽略）。

-- 假设有职工表EMP(EMPN, SALARY, BONUS)，查询年总收入
SELECT EMPN, 12 * (SALARY + BONUS) AS TOTAL_INCOME FROM EMP;

2. 模糊查询 LIKE

• 用于字符串的部分匹配。

• 通配符:

  • %: 代表0个或多个任意字符。

  • _: 代表单个任意字符。

-- 检索所有姓"刘"的学生
SELECT S#, SN FROM S WHERE SN LIKE '刘%';

3. 派生表查询

• 目的: 将子查询的结果作为一张临时的表，放在 FROM 子句中供主查询使用。

• 要求: 派生表必须指定一个别名（与 SELECT 字句一致）。

-- 检索每个学生超过自己平均成绩的课程号
SELECT T1.S#, T1.C#
FROM SC AS T1, (SELECT S#, AVG(G) AS avg_g FROM SC GROUP BY S#) AS T2
WHERE T1.S# = T2.S# AND T1.G > T2.avg_g;

-- FROM SC AS T1, (SELECT S#, AVG(G) AS avg_g FROM SC GROUP BY S#) AS T2
-- 也可写作 FROM SC AS T1, (SELECT S#, AVG(G) FROM SC GROUP BY S#) AS T2(S#, AVG(G))

注：当子查询内存在库函数时，必须为其指定列名，如 AVG(G) AS avg_g 或 AS T2(S#, AVG(G)) 。若不存在，可不指定列名，此时自动继承子查询 SELECT 中的列名。

SQL数据定义

SQL的DDL部分用于定义和管理数据库的逻辑结构，包括创建、修改和删除数据库中的各种对象。

主要操作对象：

• 基本表 (Table): 存储数据的核心结构。

• 视图 (View): 基于基本表的虚拟表。

• 索引 (Index): 用于加速数据检索的对象。

定义、修改和删除基本表

创建基本表 (CREATE TABLE)

• 基本语法：

   CREATE TABLE <表名> (
       <列名1> <数据类型> [<列级完整性约束>],
       <列名2> <数据类型> [<列级完整性约束>],
       ...
       [<表级完整性约束>]
   );

• 常用数据类型 (SQL92标准):

  • CHAR(n): 固定长度字符串。

  • VARCHAR(n): 可变长度字符串，n 代表最大长度。

  • INT, SMALLINT: 整数。

  • NUMERIC(p, q): 精确数值，总共p位，小数点后q位。

  • REAL, DOUBLE PRECISION: 浮点数与双精度浮点数。

  • DATE: 日期 (年-月-日)。

  • TIME: 时间 (时:分:秒)。

  • INTERVAL: 两个 DATE 或 TIME 类型数据之间的差。

• 完整性约束:

  • NOT NULL: 该列不能为空值。（缺省默认为 NULL ，即允许空值）

  • UNIQUE: 该列的值必须唯一。

  • PRIMARY KEY: 主键约束 (隐含 NOT NULL 和 UNIQUE)。

  • FOREIGN KEY: 外键约束，用于建立表与表之间的引用关系。

  • CHECK: 检查约束，定义列值必须满足的条件。

• 示例：创建 S 表和 SC 表

   -- 创建学生表 S
   CREATE TABLE S (
       S#    CHAR(5)    NOT NULL UNIQUE,
       SN    CHAR(20)   NOT NULL,
       SA    INT,
       SD    CHAR(15),
       PRIMARY KEY(S#), -- 表级主键约束
       CHECK (SA >= 18 AND SA <= 45) -- 表级检查约束
   );
   ​
   -- 创建选课表 SC
   CREATE TABLE SC (
       S#    CHAR(5)    NOT NULL,
       C#    CHAR(5)    NOT NULL,
       G     NUMERIC(4, 2), -- 成绩，总共4位，2位小数
       PRIMARY KEY (S#, C#), -- 复合主键
       FOREIGN KEY (S#) REFERENCES S(S#), -- 外键，引用S表的S#
       FOREIGN KEY (C#) REFERENCES C(C#)  -- 外键，引用C表的C#
   );

修改基本表 (ALTER TABLE)

• 用于对已存在的表结构进行修改。

• 基本语法：

   ALTER TABLE <表名>
   [ ADD <新列名> <数据类型> [完整性约束] ]  -- 增加新列
   [ MODIFY <列名> <新数据类型> ]           -- 修改列的数据类型
   [ DROP <完整性约束名> ]                 -- 删除约束
   [ DROP COLUMN <列名> ];                 -- 删除列 (标准SQL)

• 示例：

   -- 1. 为S表增加“入学时间”列
   ALTER TABLE S ADD Scome DATE;
   ​
   -- 2. 将S表中SA列的数据类型改为SMALLINT
   ALTER TABLE S MODIFY SA SMALLINT;

删除基本表 (DROP TABLE)

• 删除表的定义及其所有数据、索引等。此操作不可逆，需谨慎。

• 语法：

   DROP TABLE <表名>;

• 示例： DROP TABLE S;

定义和删除视图

创建视图 (CREATE VIEW)

• 视图是基于一个或多个基本表的查询结果定义的虚拟表。

• 基本语法：

   CREATE VIEW <视图名> [ (<列名1>, <列名2>, ...) ]
   AS
   <子查询>
   [WITH CHECK OPTION];

  • WITH CHECK OPTION: 对视图进行 INSERT 或 UPDATE 操作时，确保修改后的数据仍然满足视图定义中的 WHERE 条件。

• 示例：创建计算机系学生视图

   CREATE VIEW CS_Student (Sno, Sname, Sage)
   AS
   SELECT S#, SN, SA
   FROM S
   WHERE SD = 'CS'
   WITH CHECK OPTION;

  • 这个视图只包含计算机系学生的学号、姓名和年龄。

  • WITH CHECK OPTION 保证了你不能将一个CS系学生的系别改成非CS，也不能向该视图插入一个非CS系的学生。

删除视图 (DROP VIEW)

• 删除视图的定义，不影响基本表的数据。

• 语法： DROP VIEW <视图名>;

• 示例： DROP VIEW CS_Student;

定义和删除索引

• 作用： 索引是独立于表的数据库对象，主要目的是加快查询速度。它以空间换时间，会占用额外的存储空间。

• DBMS会自动为主键和唯一键创建索引。

创建索引 (CREATE INDEX)

• 语法：

   CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
   ON <表名> (<列名1> [ASC|DESC], <列名2> [ASC|DESC], ...);

  • UNIQUE: 创建唯一索引，保证索引列的值是唯一的。

  • CLUSTER: 创建聚簇索引，表的物理存储顺序将与索引顺序一致。

• 示例：为SC表的学号和课程号创建唯一索引

  CREATE UNIQUE INDEX Scno
  ON SC (S# ASC, C# DESC);

删除索引 (DROP INDEX)

• 语法： DROP INDEX <索引名>;

• 示例： DROP INDEX Scno;

SQL视图操作

视图的作用

1. 能够简化用户操作：

   • 将复杂的、多表连接的查询封装成一个简单的视图。用户只需查询这个视图，就像查询一张单表一样，无需关心底层复杂的表结构和连接逻辑。

2. 使用户能够以多种角度看待同一数据：

   • 可以为不同的用户或应用场景创建不同的视图，每个视图只展示他们关心的数据子集或经过处理的数据格式，满足个性化需求。

3. 提供了一定程度的逻辑独立性：

   • 当底层基本表的结构发生变化时（例如增加列），只要视图的定义不受影响，那么基于该视图的应用程序就无需修改。

4. 能够对数据提供安全保护：

   • 通过视图，可以向用户隐藏某些敏感的列或行。例如，可以创建一个视图，只包含员工的姓名和部门，而不包含薪资列，然后只将该视图的查询权限授予特定用户。

视图查询与视图消解

查询视图

• 对视图的查询操作与对基本表的查询操作完全相同。你可以像使用 SELECT ... FROM <表名> 一样使用 SELECT ... FROM <视图名>。

视图消解 (View Resolution)

• 这是DBMS处理视图查询的核心机制。当用户查询一个视图时，DBMS并不会先生成一个完整的临时表。

• 处理过程如下：

  1. DBMS从数据字典中找出该视图的定义（即创建视图时使用的 AS <子查询> 部分）。

  2. 将用户的查询语句与视图的定义合并、转换，重写成一个等价的、直接针对底层基本表的查询语句。

  3. 执行这个被重写和修正后的查询。

• 这个转换过程就称为“视图消解”。 对用户来说，这个过程是透明的。

• 示例：

   -- 1. 创建一个计算机系学生的视图
   CREATE VIEW CS_Student AS
   SELECT S#, SN, SA FROM S
   WHERE SD = 'CS';
   ​
   -- 2. 用户在视图上进行查询
   SELECT SN, SA FROM CS_Student WHERE SA < 20;
   ​
   -- 3. DBMS进行视图消解，将其转换为对基本表S的查询
   SELECT S.SN, S.SA 
   FROM S 
   WHERE S.SD = 'CS' AND S.SA < 20;

  • 可以看到，视图定义中的WHERE条件和用户查询的WHERE条件被合并到了一起。

SQL数据更新

SQL的数据更新功能用于对表中的数据进行插入、修改和删除操作。

插入数据 (INSERT)

INSERT 语句用于向表中添加新的行（元组）。

插入单个元组

• 语法：

   INSERT INTO <表名> [ (<列名1>, <列名2>, ...) ]
   VALUES (<值1>, <值2>, ...);

• 注意事项：

  • VALUES 子句中值的顺序和数据类型必须与 (<列名...>) 列表中的列一一对应。

  • 如果省略 (<列名...>) 列表，则 VALUES 必须为表中的所有列按其在表中定义的顺序提供值。

• 示例：

   -- 向学生表 S 中插入一条完整的记录
   INSERT INTO S (S#, SN, SA, SD)
   VALUES ('S10', '陈冬', 18, 'IS');
   ​
   -- 如果VALUES提供了所有列的值，可以省略列名列表
   INSERT INTO S 
   VALUES ('S11', '王明', 19, 'CS'); 

插入子查询结果

• 此方法可以一次性将一个查询的结果集（多行数据）插入到另一个表中。

• 语法：

   INSERT INTO <表名> [ (<列名1>, <列名2>, ...) ]
   <子查询>;

• 注意事项：

  • 子查询 SELECT 列表中的列数、顺序和数据类型必须与 INSERT INTO 的列列表匹配。

• 示例：

   -- 假设有一个表 Dept_Age(Sdept, Avgage) 用于存储每个系的平均年龄
   -- 现在将计算出的各系平均年龄插入该表
   INSERT INTO Dept_Age (Sdept, Avgage)
   SELECT SD, AVG(SA) 
   FROM S 
   GROUP BY SD;

修改数据 (UPDATE)

UPDATE 语句用于修改表中已存在行的数据。

• 语法：

   UPDATE <表名>
   SET <列名1> = <表达式1>, <列名2> = <表达式2>, ...
   [WHERE <条件>];

• 核心组成：

  • SET 子句：指定要修改哪些列，以及它们的新值。

  • WHERE 子句：非常重要！ 它指定了要修改哪些行。如果省略 WHERE 子句，表中所有行的指定列都将被更新，这通常是危险操作。

• 示例：

   -- 1. 将学生 S1 的年龄改为 22 岁
   UPDATE S
   SET SA = 22
   WHERE S# = 'S1';
   ​
   -- 2. 将所有学生的年龄增加 1 岁
   UPDATE S
   SET SA = SA + 1; -- 省略WHERE，将更新所有行
   ​
   -- 3. 将计算机系所有学生的年龄改为 20 岁
   UPDATE S
   SET SA = 20
   WHERE SD = 'CS';

删除数据 (DELETE)

DELETE 语句用于从表中删除一行或多行数据。

• 语法：

   DELETE FROM <表名>
   [WHERE <条件>];

• 核心组成：

  • WHERE 子句：同样非常重要！ 它指定了要删除哪些行。如果省略 WHERE 子句，表中所有行都将被删除，表会变空，但表结构依然存在。

• 示例：

   -- 1. 删除学号为 S19 的学生记录
   DELETE FROM S 
   WHERE S# = 'S19';
   ​
   -- 2. 删除所有选课记录
   DELETE FROM SC; -- 省略WHERE，将删除SC表中的所有数据
   ​
   -- 3. 删除计算机系所有学生的选课记录 (使用子查询)
   DELETE FROM SC
   WHERE 'CS' = (SELECT SD FROM S WHERE S.S# = SC.S#);
   -- 更常见的写法是使用 IN
   DELETE FROM SC
   WHERE S# IN (SELECT S# FROM S WHERE SD = 'CS');

SQL数据控制

SQL的数据控制功能（DCL）主要负责确保数据的一致性和安全性。它包括:

• 定义完整性约束

• 事务管理

权限控制：

即授予（GRANT）或收回（REVOKE）用户对数据库对象的特定操作权限。

1. 授权 (GRANT)

• 作用： 将对特定数据库对象的操作权限授予一个或多个用户。

• 语法：

   GRANT <权限列表> 
   ON <对象类型> <对象名>
   TO <用户列表>
   [WITH GRANT OPTION];

• 核心元素：

  • <权限列表>: 如 SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES (所有权限) 等。

  • <对象类型> <对象名>: 如 TABLE S, VIEW CS_Student。

  • <用户列表>: 用户名，或 PUBLIC (所有用户)。

  • WITH GRANT OPTION: 允许获得该权限的用户将同样的权限再授予给其他用户。

• 示例：

   -- 将对学生表 S 的查询权限授予用户 U1
   GRANT SELECT ON TABLE S TO U1;
   ​
   -- 将对选课表 SC 的所有操作权限授予用户 U2 和 U3，并允许他们再授权
   GRANT ALL PRIVILEGES ON TABLE SC TO U2, U3 WITH GRANT OPTION;

2. 收回权限 (REVOKE)

• 作用： 从一个或多个用户那里收回已经授予的权限。

• 语法：

   REVOKE <权限列表>
   ON <对象类型> <对象名>
   FROM <用户列表>;

• 注意事项： 如果权限是通过 WITH GRANT OPTION 级联授予的，收回权限时可能会产生连锁反应，级联收回由该用户授予出去的权限。

• 示例：

   -- 从用户 U1 手中收回对表 S 的查询权限
   REVOKE SELECT ON TABLE S FROM U1;
   ​
   -- 从用户 U2 和 U3 手中收回对表 SC 的所有权限
   REVOKE ALL PRIVILEGES ON TABLE SC FROM U2, U3;

空值的处理

空值（NULL）的概念

• 定义： NULL 是一个特殊的值，用来表示“缺失的未知信息”。它不等于 0，也不等于空字符串 ''。

• 含义： NULL 可以代表多种情况：

  • “不知道” (Unknown): 例如，一个新生的年龄尚未登记。

  • “不存在” (Non-existent) / “不适用” (Inapplicable): 例如，一个单身员工的配偶姓名。

  • “无意义” (Meaningless): 例如，某个字段的特定值没有业务含义。

• 约束： 定义了 PRIMARY KEY, NOT NULL, 或 UNIQUE 约束的列不能包含 NULL 值。

空值的运算

NULL 的核心特点是其不确定性，这种不确定性会“传染”到所有与它相关的运算中。

算术运算

• 规则： 任何值（包括另一个 NULL）与 NULL 进行算术运算（+, -, *, / 等），结果永远是 NULL。

• 示例：

  • 5 + NULL → NULL

  • 10 * NULL → NULL

  • NULL / 2 → NULL

  • NULL + NULL → NULL

比较运算与三值逻辑

• 问题： 传统逻辑只有“真”（TRUE）和“假”（FALSE）两种状态。但 NULL 的不确定性引入了第三种状态。

• 三值逻辑 (Three-Valued Logic): 在SQL中，逻辑判断的结果可以是以下三种之一：

  • TRUE (真)

  • FALSE (假)

  • UNKNOWN (未知)

• 规则：

  • 任何值（包括另一个 NULL）与 NULL 进行比较运算（=, >, <, <> 等），结果永远是 UNKNOWN。

  • 特别注意：NULL = NULL 的结果也是 UNKNOWN，而不是 TRUE！因为两个未知的值不一定相等。

• 示例：

  • SA = NULL → UNKNOWN (即使 SA 列的值真的是 NULL )

  • 10 > NULL → UNKNOWN

  • NULL <> NULL → UNKNOWN

逻辑运算 (AND, OR, NOT)

三值逻辑下的布尔运算规则如下表所示（U 代表 UNKNOWN）：

在查询中处理空值

如何判断空值 (IS NULL)

• 由于 column = NULL 的结果是 UNKNOWN，所以不能用 = 来判断一个值是否为空。

• 正确语法：

  • IS NULL: 判断一个值是否为 NULL。

  • IS NOT NULL: 判断一个值是否不为 NULL。

• 示例：

   -- 检索所有没有登记所在系的学生
   SELECT * FROM S WHERE SD IS NULL;
   ​
   -- 检索所有已经登记了所在系的学生
   SELECT * FROM S WHERE SD IS NOT NULL;

WHERE 和 HAVING 子句对 UNKNOWN 的处理

• 在 WHERE 和 HAVING 子句的筛选过程中，只有条件判断结果为 TRUE 的行（或分组）才会被保留并输出。

• 如果条件判断结果为 FALSE 或 UNKNOWN，相应的行（或分组）都将被丢弃。

• 包含 NULL 值的行在很多普通的比较查询中会被悄无声息地过滤掉，可能会导致意外的结果。

• 示例： 假设我们要查询成绩不及格（<60）或成绩优秀（>=90）的选课记录。

   SELECT * FROM SC WHERE G < 60 OR G >= 90;

  如果某条选课记录的成绩 G 是 NULL（例如，学生缺考），那么：

  1. G < 60 的判断结果是 UNKNOWN。

  2. G >= 90 的判断结果也是 UNKNOWN。

  3. UNKNOWN OR UNKNOWN 的结果是 UNKNOWN。

  4. 因为 WHERE 子句的最终结果不是 TRUE，所以这条成绩为 NULL 的记录将不会被查询出来。

嵌入式SQL

嵌入式SQL的意义

• 问题背景：

  • SQL 是一种功能强大的非过程化、面向集合的查询语言，擅长数据管理和复杂查询。

  • 高级语言 (如 C, C++, Java) 是过程化的，拥有强大的流程控制（循环、判断）、计算和业务逻辑处理能力。

  • 两者各有优势，但单独使用时都有局限。例如，SQL无法实现复杂的界面交互或业务流程，而高级语言直接操作文件来管理数据则非常复杂且低效。

• 解决方案： 将两者结合起来，取长补短。

  • 嵌入式SQL： 就是将SQL语句嵌入 到高级语言（称为宿主语言）的程序代码中。

  • 目的： 利用宿主语言的过程化能力来处理业务逻辑，同时利用SQL的强大功能来高效地访问和操纵数据库。这使得开发复杂的数据库应用程序成为可能。

嵌入式SQL的处理方式：预编译

由于宿主语言的编译器不认识SQL语法，因此嵌入式SQL程序不能直接编译。DBMS通常采用预编译 的方法来处理。

• 处理流程：

  1. 预编译器:

     • 源程序（包含宿主语言代码和嵌入式SQL语句）首先被送入DBMS的预编译器。

     • 预编译器会扫描代码，找出所有的嵌入式SQL语句。

     • 将这些SQL语句转换翻译成宿主语言能够识别的函数调用或API调用。

     • 同时，保留原始的宿主语言代码。

  2. 标准编译器:

     • 经过预编译后，源程序变成了一个纯粹的宿主语言程序（不含任何SQL语法）。

     • 这个纯净的程序可以被宿主语言的标准编译器（如 gcc for C）进行编译和连接，最终生成可执行文件。

嵌入式SQL的核心机制与语法

1. 区分SQL语句

• 为了让预编译器能识别出SQL语句，所有嵌入式SQL语句都必须加上一个前缀。

• 语法： EXEC SQL <SQL语句>;

2. 宿主语言与数据库之间的数据交换：主变量

• 问题： 如何将宿主语言程序中的变量值传递给SQL语句（例如作为查询条件），以及如何将SQL查询的结果存入宿主语言的变量中？

• 解决方案： 使用主变量。

  • 定义： 在宿主语言中定义的，可以被嵌入式SQL语句使用的变量。

  • 声明： 主变量必须在SQL的声明段中显式声明。

     EXEC SQL BEGIN DECLARE SECTION;
       int var_c1;
       char var_c2[21];
     EXEC SQL END DECLARE SECTION;

  • 使用： 在SQL语句中引用主变量时，必须在变量名前加上冒号 (:) 作为前缀，以区别于数据库中的列名。

     EXEC SQL FETCH foo_bar INTO :var_c1, :var_c2;

3. 处理单行与多行结果集：游标

• 问题： SQL是面向集合的，一条 SELECT 语句可能返回多行结果；而宿主语言通常是“一次一记录”的处理模式。这种不匹配如何协调？

• 解决方案： 使用游标 (Cursor)。

  • 概念： 游标可以看作是一个指向 SELECT 语句查询结果集中的一个指针或缓冲区。它允许程序逐行地从结果集中获取数据。

  • 使用步骤：

    1. DECLARE CURSOR (声明游标): 将一个游标与一条 SELECT 语句绑定起来。

        EXEC SQL DECLARE foo_bar CURSOR FOR SELECT c1, c2 FROM tbl;

    2. OPEN CURSOR (打开游标): 执行与游标绑定的 SELECT 语句，此时查询结果集被确定，游标指向第一行之前。

        // 1. DECLARE CURSOR (声明游标): 将一个游标与一条 SELECT 语句绑定起来。
        EXEC SQL DECLARE foo_bar CURSOR FOR SELECT c1, c2 FROM tbl;
        // 2. OPEN CURSOR (打开游标): 执行与游标绑定的 SELECT 语句，此时查询结果集被确定，游标指向第一行之前。
        EXEC SQL OPEN foo_bar;

    3. FETCH CURSOR (提取数据): 将游标指向的当前行的数据取出，并存入到主变量中。然后游标自动下移一行。通常在一个循环中执行此操作，直到取完所有数据。

        EXEC SQL FETCH foo_bar INTO :var_c1, :var_c2;

    4. CLOSE CURSOR (关闭游标): 释放游标占用的资源。

        EXEC SQL CLOSE foo_bar;

动态SQL

• 静态SQL： 前面讨论的嵌入式SQL，其SQL语句的结构（如表名、列名）在编译时就是固定的。

• 动态SQL： 允许程序在运行时根据用户输入或其他条件临时构建并执行SQL语句字符串。这提供了更大的灵活性，但也更复杂，并需要注意防止SQL注入等安全问题。

 SELECT * FROM users WHERE username = 'admin'

第四章 数据库保护

核心问题：

1. 数据库面临哪些安全威胁？如何进行保护？

2. 如何保证数据库中数据的正确、有效和一致性？

本章内容围绕以上两个核心问题展开，主要分为两大模块：数据库安全性控制 和 数据库完整性控制。

数据库安全性控制

1. 数据库安全性的含义

• 定义：保护数据库，防止因不合法的使用而导致的数据泄露、篡改和破坏。

• 两个层面：

  1. 对 授权用户：提供可靠的信息服务。

  2. 对 非授权用户：拒绝其存取请求，保证数据的可用性、完整性和一致性，保护数据所有者和使用者的合法权益。

2. 数据库安全性控制技术

一个完整的计算机系统安全模型包含多个层次的保护，从外到内依次是：

• 用户/应用系统 -> DBMS -> 操作系统/网络 -> 数据库(DB)

对应的安全控制技术包括：

• 用户标识与认证

• 存取控制

• 审计

• 数据加密

• 视图机制

• 推理控制、隐通道分析等

2.1 用户标识与鉴别

• 作用：系统提供的最外层安全保护措施。

• 标识：系统用一种方式（如用户名）来标记每个用户或应用程序。

• 鉴别/认证：系统在用户登录时，判断其是否为合法的授权用户。

• 常用方法：用户名 + 密码。

2.2 存取控制

• 目标：确保合法用户在 指定的权限范围 内使用DBMS和访问数据。

• 机制组成：

  1. 用户权限定义：将用户的权限（谁、能对什么对象、做什么操作）记录到数据字典中，形成授权规则。

  2. 合法权限检查：当用户发起操作时，DBMS依据数据字典中的授权规则进行检查，决定接受或拒绝该操作。

• 这两个部分共同构成了DBMS的 安全子系统。

• 存取控制方法分类：

  1. 自主存取控制 (DAC - Discretionary Access Control)

     • 特点：用户可以自主地将自己拥有的权限 转授 给其他用户。权限控制灵活。

     • 核心：权限由 (数据对象, 操作类型) 构成。

     • 授权 (Authorization)：定义用户存取权限的过程，需指明：用户名、数据对象名、允许的操作类型。

  2. 强制存取控制 (MAC - Mandatory Access Control)

     • 特点：系统强制执行安全策略，用户无法自主修改。安全性更高。

     • 核心概念：

       • 主体：系统中的活动实体，如用户、进程。

       • 客体：系统中的被动实体，如表、文件、视图。

       • 敏感度标记 (Label)：

         • 主体的标记称为 许可证级别。

         • 客体的标记称为 密级 (如：绝密 > 机密 > 秘密 > 公开)。

     • 存取规则：

       • 读权限：仅当 主体的许可证级别 ≥ 客体的密级 时，主体才能读取客体（向下读，向上不读）。

       • 写权限：仅当 主体的许可证级别 == 客体的密级 时，主体才能写入客体（平级写）。

2.3 基于角色的存取控制 (RBAC)

• 定义：角色 (Role) 是一组相关权限的集合。

• 工作方式：将权限授予角色，再将角色授予用户。

• 优点：大大简化了权限管理。

2.4 SQL中的数据安全性控制

SQL通过 GRANT 和 REVOKE 语句实现自主存取控制。

• 权限授予 (GRANT)

  • 语法：GRANT <权限列表> ON <对象名> TO <用户/角色/PUBLIC> [WITH GRANT OPTION];

  • WITH GRANT OPTION：允许接收权限的用户将该权限再次授予其他用户。

  • 示例:

    • 授予用户SCOTT连接数据库的权限：GRANT Create Session to SCOTT;

    • 授予用户Liming对Student表的查询和更新权限，并允许他再授权：

       GRANT Select, Update ON Student TO Liming WITH GRANT OPTION;

    • 将Student表的所有权限授予所有用户：

       GRANT ALL ON Student TO PUBLIC;

• 权限收回 (REVOKE)

  • 语法：REVOKE <权限列表> ON <对象名> FROM <用户/角色/PUBLIC>;

  • 特点：如果被收回权限的用户曾将此权限授予了其他用户，这些级联授予的权限也会被 一并收回。

  • 示例:

    • 取消用户SCOTT创建表的权限：REVOKE Create Table FROM SCOTT;

    • 收回用户Liming对Student表的所有权限：

       REVOKE ALL ON Student FROM Liming;

2.5 其他安全性控制方法

• 视图 (View) 机制

  • 原理：为不同用户定义不同的视图，将用户对数据的访问限制在视图定义的范围内（特定行、特定列）。

  • 示例：限制用户Wangping只能查询计算机系学生的信息。

     CREATE VIEW CS_Student AS
     SELECT Sno, Sname FROM Student WHERE Sdept = 'CS';
     ​
     GRANT SELECT ON CS_Student TO Wangping;

• 审计

  • 原理：将用户对数据库的所有操作自动记录到审计日志中。DBA可以利用日志追溯事件，发现非法操作。

• 数据加密

  • 原理：通过加密算法将原始数据（明文）变为不可识别的格式（密文），保护数据在存储和传输过程中的安全。

3. 可信计算机系统评测标准 (TCSEC)

• TCSEC (橙皮书)：1985年美国国防部制定，用于评估计算机系统的安全等级。

• TDI：TCSEC对数据库管理系统的解释和扩展。

• 安全级别 (从低到高)：

  • D：最低保护。

  • C1：自主存取控制 (DAC)。

  • C2：比C1更强的DAC，并包含审计功能。

  • B1：标记安全保护，开始引入强制存取控制 (MAC)。

  • B2：结构化保护，有形式化的安全模型。

  • B3：安全域，安全内核。

  • A1：可验证设计，最高级别。

数据库完整性控制

1. 数据完整性的含义

• 定义：指数据的 正确性 和 相容性。

  • 正确性：数据符合现实世界的语义，如类型、格式、取值范围正确。

  • 相容性：同一对象的不同数据之间逻辑自洽，没有矛盾。

• 与安全性的区别：

  • 安全性：防止 非法用户 和 非法操作 对数据库的恶意破坏。

  • 完整性：防止 合法用户 通过 合法操作 向数据库中添加不符合语义的（错误）数据。

2. 完整性约束条件

• 定义：施加在数据库数据之上的语义约束，是数据库系统进行完整性检查的依据。

• 作用对象：列、元组（行）、关系（表）。

• 关系模型中的三类完整性：

  1. 实体完整性：

     • 规则：主键 (Primary Key) 的值必须唯一且不能为空 (NOT NULL)。

     • SQL支持：PRIMARY KEY 约束。

  2. 参照完整性：

     • 规则：外键 (Foreign Key) 的值要么为空，要么必须等于其参照关系中某个元组的主键值。

     • SQL支持：FOREIGN KEY 约束。

  3. 用户自定义完整性：

     • 针对具体应用场景定义的约束。

     • SQL支持：NOT NULL、UNIQUE、CHECK 约束。

3. 完整性控制机制

DBMS的完整性控制应具备三个功能：

1. 定义功能：提供定义完整性约束条件的机制。

2. 检查功能：在用户执行INSERT、UPDATE、DELETE等操作时，检查是否违反了已定义的约束。

3. 违约响应：如果操作违反了约束，则采取相应措施，如 拒绝 (REJECT) 执行操作、级联 (CASCADE) 执行等。

4. SQL中的数据完整性支持

4.1 CREATE TABLE中的约束定义

在创建表时可以直接定义完整性约束。

• 完整性约束关键字：

  • NULL / NOT NULL

  • UNIQUE

  • PRIMARY KEY

  • FOREIGN KEY

  • CHECK

• 示例:

   CREATE TABLE S (
       S#   CHAR(5) NOT NULL UNIQUE,       -- 列级约束：非空且唯一
       SN   CHAR(20) NOT NULL,
       SS   CHAR(2) CHECK (SS IN ('M', 'F')), -- 列级约束：性别只能是'M'或'F'
       SA   INT CHECK (SA >= 18 AND SA <= 45),-- 列级约束：年龄范围
       SD   CHAR(15),
       PRIMARY KEY(S#),                      -- 表级约束：定义主键
       CHECK (SS = 'F' OR SN NOT LIKE 'Ms.%')-- 表级约束：一个复杂的元组约束
   );

4.2 断言 (Assertion)

• 作用：用于定义涉及多个表或需要使用聚合函数的复杂约束。

• 语法：CREATE ASSERTION <断言名> CHECK (<条件>);

• 示例：要求每一门课程的选修人数不能超过60人。

   CREATE ASSERTION ASSE-SC-SNUM1
   CHECK (60 >= ALL (SELECT COUNT(*) FROM SC GROUP BY C#));

4.3 触发器 (Trigger)

• 定义：一种由 事件驱动 的特殊过程，当对表的特定操作（INSERT, UPDATE, DELETE）发生时自动执行。

• 作用：可以实现比CHECK和断言更复杂的完整性控制逻辑。

• 语法结构:

   CREATE TRIGGER <触发器名>
   {BEFORE | AFTER} <触发器事件> ON <表名>
   FOR EACH {ROW | STATEMENT}
   [WHEN <触发条件>]
   <触发动作体>

5.补充概念

5.1 完整性约束条件的分类

除了按照实体、参照、用户自定义来划分，完整性约束还可以从数据状态的角度分为两类：

• 静态约束

  • 定义：指数据库在 任何一个确定的时间点（状态） 下，数据都必须满足的约束。它反映的是数据库状态的 合理性。

  • 示例：

    • 静态列级约束：学生的年龄必须在15到30岁之间。

    • 静态元组约束：对于一条订单记录，发货日期不能早于下单日期。

    • 静态关系约束：在一个学生表中，学号（主键）必须唯一。

• 动态约束

  • 定义：指数据库从一个状态 转变为 另一个状态时，新旧数据值之间需要满足的约束。它反映的是数据库状态 变迁的合理性。

  • 示例：

    • 动态列级约束：员工的工资只能增加，不能减少 (UPDATE 操作时，新工资值必须大于等于旧工资值)。

    • 动态元组约束：员工的职位级别只能平调或晋升，不能降级。

5.2 完整性检查的时机

根据完整性约束是在何时被检查，可以分为两类：

• 立即执行约束

  • 定义：在 每一条 INSERT、UPDATE、DELETE 语句执行完毕后，立即 检查该操作是否违反了完整性约束。

  • 行为：如果违反，系统会立刻拒绝该操作并报错。这是绝大多数数据库约束的 默认行为。

• 延迟执行约束

  • 定义：在一个 事务 (Transaction) 的执行过程中，完整性检查被 推迟到事务提交 (COMMIT) 时 才进行。

  • 行为：事务执行期间可以暂时处于不满足完整性约束的中间状态，只要在事务结束时数据恢复到满足约束的状态即可。如果事务提交时检查不通过，整个事务将被回滚 (ROLLBACK)。

  • 应用场景：处理复杂的、需要多个步骤才能完成的数据更新。例如，要交换两个部门的主管，需要先将一个主管的外键设置为空，再进行指派，这个中间状态可能会违反 NOT NULL 约束，但最终状态是合法的。

5.3 完整性规则的表示

一条完整的完整性规则，可以用一个形式化的 五元组 (D, O, A, C, P) 来描述，这有助于精确地定义和理解一个约束。

• D (Data)：约束所作用的 数据对象。例如，TEACHER 表的 工资 列。

• O (Operation)：触发完整性检查的 数据库操作。例如，对 TEACHER 表的 INSERT 或 UPDATE。

• A (Assertion)：数据对象必须满足的 断言 或语义约束。例如，工资 >= 10000。

• C (Condition)：一个谓词，用于选择断言 A 所作用的 数据对象值的范围。例如，职称 = '教授'。

• P (Procedure)：当违反完整性规则时，系统触发的 过程或动作。例如，拒绝执行该操作。

示例：

• 规则：TEACHER 表中，"教授"的"工资"不得低于10000元。

• 五元组表示：

  • D：TEACHER 表的 工资 列

  • O：INSERT, UPDATE

  • A：工资 >= 10000

  • C：职称 = '教授'

  • P：拒绝该操作