SQL 数据更新、空值与视图

发表于 2025-04-03 Waline：阅读次数：本文字数： 6.5k 阅读时长 ≈ 24 分钟

数据更新

除了数据查询（SELECT），SQL 还提供了数据操作语言（DML, Data Manipulation Language）的另外三个核心功能：插入（INSERT）、更新（UPDATE）和删除（DELETE）数据。

数据插入（`INSERT`）

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

插入单行数据

这是最常见的插入方式，使用 VALUES 子句指定要插入的具体值。

基本语法：

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

INTO <表名>：指定要插入数据的目标表。
[(<列名1>, ...)]：可选的列名列表。
- 如果指定了列名列表：
  - VALUES 子句提供的值必须与列名列表在数量、顺序和数据类型上精确匹配。
  - 允许只为表中的部分列提供值，未在列表中的列将根据其定义获得值：
    - 如果有 DEFAULT 约束，则使用默认值。
    - 如果没有 DEFAULT 约束且允许 NULL，则插入 NULL。
    - 如果没有 DEFAULT 约束且有 NOT NULL 约束，则插入操作会失败（除非该列是自增列等特殊情况）。
- 如果省略了列名列表：
  - VALUES 子句必须为表中的所有列按其在表定义中的原始顺序提供值。
  - 这是一种便捷但不够健壮的写法，因为如果表结构发生变化（如添加、删除或重排了列），该语句可能会失败或插入错误的数据。
VALUES ( <值1>, ... )：提供要插入的具体数据值。值的顺序和类型必须与指定的列名列表（或表的原始列顺序）匹配。可以使用常量、表达式，或者使用 NULL 关键字表示空值。

示例：

假设我们有 Student 表

Student(
    Sno CHAR(9) PRIMARY KEY,
    Sname VARCHAR(20) NOT NULL,
    Ssex CHAR(1),
    Sage SMALLINT,
    Sdept VARCHAR(20) DEFAULT '待定'
)

插入一条完整的学生记录（指定列名）：

1 2	INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('2024001', '张三', '男', 20, '计算机科学');

插入一条完整的学生记录（省略列名，依赖表结构顺序）：

1
2
3

-- 假设表结构顺序是 Sno, Sname, Ssex, Sage, Sdept
INSERT INTO Student
VALUES ('2024002', '李四', '女', 19, '软件工程');

省略列名的风险

这种写法可读性较差，且对表结构的更改非常敏感。推荐显式指定列名。

插入部分列信息（使用默认值和 NULL）：

-- 只提供必须的 Sno 和 Sname，以及性别 Ssex
-- Sage 将为 NULL (假设允许 NULL)
-- Sdept 将为 '待定' (使用 DEFAULT 值)
INSERT INTO Student (Sno, Sname, Ssex)
VALUES ('2024003', '王五', '男');

插入选课记录，成绩暂缺（显式插入 NULL）：
假设 SC 表：

SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno, Cno)
)

1 2	INSERT INTO SC (Sno, Cno, Grade) VALUES ('2024001', 'C001', NULL);

或者，如果 Grade 列允许 NULL 且没有 DEFAULT 值，可以省略该列：

1 2	INSERT INTO SC (Sno, Cno) VALUES ('2024001', 'C002'); -- Grade 列将自动设为 NULL

插入子查询结果

INSERT 语句还可以将一个 SELECT 查询的结果集批量插入到表中。这对于数据迁移、汇总计算结果存储等场景非常有用。

基本语法：

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

子查询 SELECT 语句返回的列的数量、数据类型和顺序必须与 INSERT INTO 子句中指定的列（或目标表的原始列，如果省略列列表）相匹配。
子查询可以是一个复杂的查询，包含 WHERE, GROUP BY, JOIN 等。

示例：假设需要创建一个新表 Dept_Avg_Age 来存储每个系的平均学生年龄。

创建目标表：

CREATE TABLE Dept_Avg_Age (
    Department VARCHAR(20) PRIMARY KEY,
    AverageAge DECIMAL(4, 1) -- 假设平均年龄需要一位小数
);

使用 INSERT INTO ... SELECT ... 填充数据：

INSERT INTO Dept_Avg_Age (Department, AverageAge)
SELECT Sdept, AVG(CAST(Sage AS DECIMAL(5,1))) -- 计算平均年龄，可能需要类型转换
FROM Student
WHERE Sdept IS NOT NULL -- 排除系别未知的学生
GROUP BY Sdept;

完整性约束检查

无论是插入单行还是批量插入，DBMS 都会在执行 INSERT 时检查相关的完整性约束：

实体完整性：主键值不能为空且必须唯一。
参照完整性：外键值必须在被参照表的主键中存在，或者为 NULL（如果外键列允许 NULL）。
用户定义的完整性：如 NOT NULL 约束、UNIQUE 约束、CHECK 约束（值域约束）等。

任何违反约束的插入操作都会失败，并通常会返回错误信息。

数据修改（`UPDATE`）

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

基本语法：

1
2
3

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

UPDATE <表名>：指定要更新数据的目标表。
SET <列名> = <表达式>：指定要修改的列及其新值。
- 可以同时修改一个或多个列，用逗号分隔。
- <表达式> 可以是常量、包含列名的算术/字符串表达式、或者（在某些 DBMS 中支持）来自子查询的结果。
[WHERE <条件表达式>]：可选的条件子句，用于指定要更新哪些行。
- 只有满足 WHERE 条件的行才会被更新。
- 如果省略 WHERE 子句，则表中所有行的指定列都会被更新！这是一个非常危险的操作，务必谨慎使用。

示例：

修改单个元组的单个属性值：

-- 将学号为「2024001」的学生的年龄改为 21 岁
UPDATE Student
SET Sage = 21
WHERE Sno = '2024001';

修改多个元组的多个属性值：

-- 将所有「计算机科学」系学生的年龄增加 1 岁，并将系名改为「智能科学与技术」
UPDATE Student
SET Sage = Sage + 1, Sdept = '智能科学与技术'
WHERE Sdept = '计算机科学';

修改所有元组的某个属性值（无 WHERE 子句）：

1
2
3

-- 假设所有学生都升了一级，年龄加 1
UPDATE Student
SET Sage = Sage + 1;

省略 WHERE 的危险

再次强调，没有 WHERE 子句的 UPDATE 会影响整个表。执行前请务必确认意图。

带子查询的 UPDATE（通常在 WHERE 子句中使用）：

-- 将所有选修了「C001」课程的学生的成绩设为 0（假设这是某种重置操作）
UPDATE SC
SET Grade = 0
WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = 'C001'); -- 这里的子查询其实可以简化条件

更常见的场景是根据其他表的信息来更新：

-- 将 '计算机科学' 系所有学生的选课成绩置为 0
UPDATE SC
SET Grade = 0
WHERE Sno IN (SELECT Sno
              FROM Student
              WHERE Sdept = '计算机科学');

在 SET 子句中使用子查询

一些 DBMS 支持在 SET 子句中使用标量子查询（返回单个值的子查询）来赋新值，语法可能类似：

1 2	-- 示例：将每个学生的年龄更新为某个计算值（语法可能因 DBMS 而异） -- UPDATE Student s SET s.Sage = (SELECT ... FROM ... WHERE ...);

这种用法相对复杂，且语法不完全标准，需查阅具体 DBMS 文档。

完整性约束检查

UPDATE 操作同样会触发完整性约束检查：

更新主键或唯一键列时，新值不能与现有值冲突。
更新外键列时，新值必须在参照表中存在或为 NULL。
更新后的值必须满足 NOT NULL, CHECK 等约束。
如果更新操作导致参照完整性问题（例如，更新了被外键引用的主键值），其行为取决于外键约束定义的 ON UPDATE 规则（如 RESTRICT, CASCADE, SET NULL, SET DEFAULT）。

数据删除（`DELETE`）

DELETE 语句用于从表中删除行（元组）。

基本语法：

1 2	DELETE FROM <表名> [WHERE <条件表达式>];

DELETE FROM <表名>：指定要删除数据的目标表。
[WHERE <条件表达式>]：可选的条件子句，用于指定要删除哪些行。
- 只有满足 WHERE 条件的行才会被删除。
- 如果省略 WHERE 子句，则表中所有行都会被删除！这同样是一个非常危险的操作。

示例：

删除单个元组：

1
2
3

-- 删除学号为「2024003」的学生记录
DELETE FROM Student
WHERE Sno = '2024003';

删除满足条件的多个元组：

1
2
3

-- 删除所有年龄小于 18 岁的学生记录
DELETE FROM Student
WHERE Sage < 18;

删除所有元组（无 WHERE 子句）：

1 2	-- 删除 SC 表中的所有选课记录 DELETE FROM SC;

删除所有行的后果

没有 WHERE 子句的 DELETE 会清空整个表的数据，但表的结构（定义）仍然存在。

带子查询的 DELETE：

-- 删除所有「计算机科学」系学生的选课记录
DELETE FROM SC
WHERE Sno IN (SELECT Sno
              FROM Student
              WHERE Sdept = '计算机科学');

完整性约束检查

DELETE 操作主要关注参照完整性：

如果被删除的行包含了被其他表的外键引用的主键值，其行为取决于外键约束定义的 ON DELETE 规则：
- RESTRICT / NO ACTION（默认）：如果存在引用，则阻止删除操作。
- CASCADE：如果存在引用，则同时删除引用这些值的相关行。
- SET NULL：如果存在引用，则将引用这些值的外键列设置为 NULL（前提是外键列允许 NULL）。
- SET DEFAULT：如果存在引用，则将引用这些值的外键列设置为其默认值（前提是外键列有默认值）。

`DELETE` vs. `TRUNCATE TABLE`

SQL 还提供了一个 TRUNCATE TABLE 语句，它也能删除表中的所有行，但与 DELETE FROM table_name;（无 WHERE）有显著区别：

特性	`DELETE FROM table_name;`	`TRUNCATE TABLE table_name;`
操作类型	DML(数据操作语言)	DDL(数据定义语言)
执行方式	逐行删除	通常是回收数据页，速度更快
事务日志	记录每行删除操作，日志量大	日志记录量少（通常只记录页回收）
触发器	会触发 `DELETE` 触发器	通常不会触发 `DELETE` 触发器
回滚（`ROLLBACK`）	可以回滚（在事务中）	通常不能回滚，或回滚机制不同
`WHERE` 子句	可以使用 `WHERE` 删除特定行	不能使用 `WHERE`，只能删除所有行
自增计数器	通常不重置	通常会重置为初始值（依赖于 DBMS）
权限	需要表的 `DELETE` 权限	通常需要更高的权限（如 `ALTER` 或 `DROP`）

何时使用 TRUNCATE

当需要快速清空一个大表的所有数据，并且不需要逐行处理（如触发器），不关心回滚能力，且希望重置自增计数器时，TRUNCATE 是一个更高效的选择。但因其破坏性更大且通常无法回滚，使用时需更加小心。

SQL 中的 `NULL` 值

NULL 是 SQL 中一个非常特殊的值，表示缺失信息（Missing Information）或不适用（Not Applicable）。它不等于 0，也不等于空字符串 ''，它甚至不等于它自己。理解 NULL 的行为对于编写正确的 SQL 查询和数据操作至关重要。

`NULL` 的含义与产生

含义：
- 未知值（Unknown Value）：该属性应该有一个值，但我们当前不知道它是什么（例如，新生的成绩 Grade）。
- 不适用值（Not Applicable）：该属性对于这个元组没有意义（例如，未婚人士的配偶姓名）。
- 未填写/缺失值（Missing Value）：由于某种原因数据没有被提供。
产生：
- 显式插入：INSERT INTO ... VALUES (..., NULL, ...);
- 隐式插入：INSERT 时未指定允许 NULL 且没有 DEFAULT 值的列。
- 显式更新：UPDATE ... SET column = NULL WHERE ...;
- 外连接（OUTER JOIN）：当一侧的表没有匹配行时，另一侧的列会出现在结果中。
- 聚合函数（AVG, SUM, COUNT(column), etc.）：通常会忽略 NULL 值进行计算（COUNT(*) 是例外，它计算行数，不忽略 NULL）。
- 算术运算：任何涉及 NULL 的算术运算（+, -, *, /）的结果通常也是 NULL。

判断 NULL 值（`IS NULL`, `IS NOT NULL`）

由于 NULL 代表未知，它不等于任何值，包括它自身。因此，不能使用标准的比较运算符（=, !=, <>, <, >) 来检查一个值是否为 NULL。

column = NULL 永远不会为真（TRUE）。
column != NULL 或 column <> NULL 永远不会为真（TRUE）。

SQL 提供了专门的操作符来判断 NULL：

IS NULL：如果列的值是 NULL，则条件为真。
IS NOT NULL：如果列的值不是 NULL，则条件为真。

示例

查找信息填写不完整的学生（姓名、性别、年龄或系别中有缺失）：

-- 从 Student 表中找出漏填了数据的学生信息
SELECT *
FROM Student
WHERE Sname IS NULL
   OR Ssex IS NULL
   OR Sage IS NULL
   OR Sdept IS NULL;

NULL 与完整性约束

NULL 值的存在与数据库的完整性约束密切相关：

NOT NULL 约束：
- 在属性定义（或域定义）中指定 NOT NULL，意味着该列不允许包含 NULL 值。尝试插入 NULL 或将现有值更新为 NULL 会失败。
主码（PRIMARY KEY）约束：
- 主键列的值必须唯一且不能为 NULL。这是实体完整性的核心要求。DBMS 会自动为主键列强制实施 NOT NULL 约束。
唯一性（UNIQUE）约束：
- UNIQUE 约束确保列（或列组）中的所有非 NULL 值是唯一的。
- 对于 NULL 的处理，标准 SQL 允许多个 NULL 值存在于具有 UNIQUE 约束的列中，因为 NULL 不等于任何值，包括其他 NULL。但是，具体行为可能因 DBMS 而异（例如，某些系统可能只允许一个 NULL）。
- 如果需要确保某列的值唯一且非空，应同时施加 UNIQUE 和 NOT NULL 约束。这通常用于定义候选键（Candidate Key）。

UNIQUE + NOT NULL

组合使用 UNIQUE 和 NOT NULL 约束可以确保表中某个属性（或属性组）的值既唯一又非空，从而达到类似主键的唯一标识效果，常用于定义候选键。

`NULL` 与运算

NULL 的特殊性对 SQL 中的运算（算术、比较、逻辑）产生了重要影响。

算术运算

任何涉及 NULL 的算术运算（如 +, -, *, /）的结果总是 NULL。

1 2	-- 示例：如果 Sage 为 NULL，则 Sage + 1 的结果也是 NULL UPDATE Student SET Sage = Sage + 1; -- 如果某行 Sage 是 NULL，更新后仍然是 NULL

比较运算与三值逻辑

标准的布尔逻辑只有 TRUE 和 FALSE 两种结果。但当引入 NULL 时，SQL 采用三值逻辑（Three-Valued Logic），包含三种可能的真值：

TRUE：条件为真。
FALSE：条件为假。
UNKNOWN：由于存在 NULL，无法确定条件是真还是假。

任何涉及 NULL 的比较运算（=, !=, <, >, <=, >=）的结果都是 UNKNOWN。

5 = NULL 结果是 UNKNOWN。
NULL = NULL 结果也是 UNKNOWN。
NULL > 0 结果是 UNKNOWN。

逻辑运算（AND, OR, NOT）在三值逻辑下的规则扩展如下：

`p`	`q`	`p AND q`	`p OR q`
`TRUE`	`TRUE`	`TRUE`	`TRUE`
`TRUE`	`FALSE`	`FALSE`	`TRUE`
`TRUE`	`UNKNOWN`	`UNKNOWN`	`TRUE`
`FALSE`	`FALSE`	`FALSE`	`FALSE`
`FALSE`	`UNKNOWN`	`FALSE`	`UNKNOWN`
`UNKNOWN`	`UNKNOWN`	`UNKNOWN`	`UNKNOWN`
`p`	`NOT p`
:-:	:-:
`TRUE`	`FALSE`
`FALSE`	`TRUE`
`UNKNOWN`	`UNKNOWN`

`WHERE` 和 `HAVING` 子句中的处理

这是三值逻辑最关键的应用场景：

在 WHERE 子句和 HAVING 子句中，只有条件判断结果为 TRUE 的行（或分组）才会被保留在结果集中。
条件判断结果为 FALSE 或 UNKNOWN 的行（或分组）都会被丢弃。

UNKNOWN 的实际效果

因为 WHERE/HAVING 只接受 TRUE，所以 UNKNOWN 的实际效果等同于 FALSE —— 导致行被过滤掉。这是理解 NULL 在查询中行为的关键。

示例：`NULL` 对查询结果的影响

查找不及格学生（不包括缺考）

-- 找出选修 1 号课程不及格的学生
SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1';

此查询不会返回 Grade 为 NULL（例如缺考）的学生。因为对于 Grade 为 NULL 的行，Grade < 60 的结果是 UNKNOWN，该行会被 WHERE 子句过滤掉。

查找不及格或缺考的学生

-- 选出选修 1 号课程的不及格学生以及缺考的学生

-- 解法 1: 使用 UNION 合并两个查询
SELECT Sno FROM SC WHERE Grade < 60 AND Cno = '1'
UNION
SELECT Sno FROM SC WHERE Grade IS NULL AND Cno = '1';

-- 解法 2: 使用 OR 和 IS NULL
SELECT Sno
FROM SC
WHERE Cno = '1' AND (Grade < 60 OR Grade IS NULL);

这两种方法都能正确地包含 Grade 为 NULL 的情况。

NOT 运算符与 NULL

能否用 NOT (Grade >= 60) 来代替 Grade < 60 OR Grade IS NULL？

-- 尝试查找课程 1 中非及格（>=60）的学生
SELECT Sno
FROM SC
WHERE Cno = '1' AND NOT (Grade >= 60);

这个查询仍然不会包含 Grade 为 NULL 的学生。

当 Grade 为 NULL 时，Grade >= 60 的结果是 UNKNOWN。
根据三值逻辑，NOT UNKNOWN 的结果也是 UNKNOWN。
由于 WHERE 子句只接受 TRUE，UNKNOWN 的结果导致这些行被过滤。

因此，NOT (Grade >= 60) 不等于 Grade < 60 OR Grade IS NULL。

要显式处理 NULL，必须使用 IS NULL 或 IS NOT NULL。

聚合函数与 `NULL`

大多数聚合函数（如 AVG, SUM, MAX, MIN, COUNT(column)）在计算时会忽略 NULL 值。

AVG(Grade)：计算的是非 NULL 成绩的平均值。
COUNT(Grade)：计算的是 Grade 列中非 NULL 值的数量。
COUNT(*)：计算表中的总行数，不会忽略 NULL 值（因为它计算的是行本身，而不是特定列的值）。

-- 示例：计算课程 C001 的平均分和选课人数
SELECT AVG(Grade) AS AverageGrade,     -- 只计算有成绩的学生的平均分
       COUNT(Grade) AS GradedStudents, -- 计算有成绩的学生人数
       COUNT(*) AS TotalStudents       -- 计算所有选修该课的学生人数（包括缺考）
FROM SC
WHERE Cno = 'C001';

视图（View）

视图（View）在 SQL 中是一个非常重要的概念，它本质上是一个虚拟表（Virtual Table）。与存储数据的基本表（Base Table）不同，视图本身不包含物理数据，它包含的是一个 SQL 查询（SELECT 语句），这个查询定义了视图的结构（列）和内容（行）。

视图的核心特性

虚拟性：视图是从一个或多个基本表（或其他视图）导出的，其内容由查询动态生成。
定义存储：数据库只存储视图的定义（即其对应的 SELECT 查询），而不存储视图查询结果的数据。
数据同步：当底层基本表的数据发生变化时，通过视图查询到的数据也会实时反映这些变化。

定义视图：`CREATE VIEW`

使用 CREATE VIEW 语句来创建新的视图。

基本语法：

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

<视图名>：要创建的视图的名称，需符合标识符命名规则。
[(<列名1>, ...)]：可选的视图列名列表。
- 如果省略此列表：视图的列名将直接继承自 AS 子查询 SELECT 列表中的列名（或别名）。
- 如果指定此列表：
  - 必须为视图的每一列提供一个名称。
  - 列表中的列名数量必须与子查询 SELECT 列表返回的列数完全一致。
  - 这允许你为视图的列指定不同于底层表列的、更具描述性或更合适的名称。
- 必须指定列名列表的情况：
  1. 子查询的 SELECT 列表中包含计算字段（如聚合函数 AVG(Grade)、算术表达式 2024 - Sage）且没有使用 AS 指定别名。
  2. 子查询涉及多个表，且 SELECT 列表中包含了来自不同表的同名列。
  3. 需要为视图列提供全新的、不同于子查询列的名称。
AS <子查询>：定义视图内容的 SELECT 语句。
- 子查询可以是任何合法的 SELECT 语句，可以包含连接、分组、过滤等操作。
- 标准 SQL 限制：通常情况下，子查询中不允许包含 ORDER BY 子句（视图本身是无序的集合，排序应在查询视图时进行）。但可以使用 DISTINCT 关键字。具体 DBMS 的实现可能有所不同。
[WITH CHECK OPTION]：可选子句，用于对通过视图进行的 INSERT 和 UPDATE 操作施加约束。
- 如果指定了 WITH CHECK OPTION，则任何试图通过该视图插入或更新的行，都必须满足视图定义中 WHERE 子句的条件。
- 换句话说，修改后的行必须仍然能通过该视图被查询出来。
- 这有助于维护数据的逻辑一致性，确保通过特定视图修改的数据仍然符合该视图所代表的数据子集。

视图定义的存储

执行 CREATE VIEW 语句时，数据库管理系统（DBMS）仅仅是将视图的定义（包括视图名、列名、子查询等）存储在数据字典（Data Dictionary）或系统目录中。它并不执行子查询，也不存储任何数据。

视图类型与示例

根据定义视图的子查询的复杂程度，可以将视图分为几类：

行列子集视图：从单个基本表中选取部分行和部分列，通常保留了主键。

-- 示例：创建只包含信息科学系（IS）学生的视图
CREATE VIEW IS_Student_Info (StudentID, Name, Age) -- 指定了列名
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS';

-- 示例：创建信息科学系学生的视图，并添加 WITH CHECK OPTION
CREATE VIEW IS_Student_Checked
AS
SELECT Sno, Sname, Sage, Sdept -- 继承子查询列名
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION;

WITH CHECK OPTION 的作用

对于 IS_Student_Checked 视图：

下面的 SQL 会失败，因为 Sdept = 'CS' 不满足 WHERE Sdept = 'IS'：

1 2	INSERT INTO IS_Student_Checked(Sno, Sname, Sage, Sdept) VALUES ('2024004', '赵六', 21, 'CS');

下面的 SQL 也会失败：

UPDATE IS_Student_Checked SET Sdept = 'CS' WHERE Sno = 'some_IS_student_sno';

直接在 Student 表上执行下面的 SQL 则不受此视图的 WITH CHECK OPTION 影响：

UPDATE Student SET Sdept = 'CS' WHERE Sno = 'some_IS_student_sno';

基于多表连接的视图：子查询涉及多个表的连接操作。

-- 示例：创建信息科学系学生及其选修课程 1 的成绩视图
CREATE VIEW IS_Course1_Grade (StudentID, Name, Grade)
AS
SELECT S.Sno, S.Sname, SC.Grade
FROM Student S
JOIN SC ON S.Sno = SC.Sno
WHERE S.Sdept = 'IS' AND SC.Cno = '1';

基于视图的视图：子查询的数据来源是另一个（或多个）已存在的视图。

-- 示例：基于 IS_Course1_Grade 视图，创建成绩优秀的 IS 学生视图
CREATE VIEW IS_Excellent_Course1
AS
SELECT StudentID, Name, Grade
FROM IS_Course1_Grade
WHERE Grade >= 90;

带计算列（表达式）的视图：SELECT 列表中包含计算字段。

-- 示例：创建包含学生姓名和出生年份的视图（假设当前年份为 2025）
CREATE VIEW Student_BirthYear (StudentID, Name, BirthYear) -- 必须指定列名
AS
SELECT Sno, Sname, 2025 - Sage
FROM Student;

分组视图（带聚合函数）：子查询包含 GROUP BY 子句和聚合函数。

-- 示例：创建包含每个学生学号和平均成绩的视图
CREATE VIEW Student_Avg_Grade (StudentID, AverageGrade) -- 必须指定列名
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;

使用 SELECT * 定义视图的风险

虽然语法上允许：

CREATE VIEW V AS SELECT * FROM T;

但这是一种不推荐的做法。如果基表 T 的结构发生变化（如添加、删除、重命名或重排了列），视图 V 的定义可能不再有效，或者其行为变得不可预测，破坏了视图提供的抽象层。

最佳实践是在 CREATE VIEW 中显式列出所有需要的列。

删除视图：`DROP VIEW`

使用 DROP VIEW 语句可以删除一个或多个已定义的视图。

基本语法：

DROP VIEW <视图名1> [, <视图名2>, ...] [CASCADE | RESTRICT];

<视图名>：要删除的视图的名称。可以一次删除多个，用逗号分隔。
CASCADE（级联删除）：如果指定了 CASCADE，那么在删除该视图的同时，所有依赖于该视图的其他视图或其他对象（如存储过程、触发器等，具体依赖关系视 DBMS 而定）也会被一并删除。
RESTRICT（限制删除，通常是默认行为）：如果指定了 RESTRICT 或省略此选项，那么只有当没有任何其他对象依赖于要删除的视图时，删除操作才能成功执行。如果存在依赖对象，DBMS 会拒绝删除并报错。

示例：

-- 删除视图 Student_BirthYear (假设没有其他对象依赖它)
DROP VIEW Student_BirthYear; -- 或 DROP VIEW Student_BirthYear RESTRICT;

-- 尝试删除 IS_Course1_Grade 视图，但 IS_Excellent_Course1 依赖它
DROP VIEW IS_Course1_Grade; -- 如果默认是 RESTRICT，此操作会失败

-- 使用 CASCADE 删除 IS_Course1_Grade 及其依赖视图 IS_Excellent_Course1
DROP VIEW IS_Course1_Grade CASCADE;
-- 执行后，IS_Course1_Grade 和 IS_Excellent_Course1 都被删除了

删除基表与视图的关系

当删除一个基本表时，所有基于该表创建的视图都会变得无效。

如果使用 DROP TABLE <表名> RESTRICT;（默认），且存在依赖该表的视图，则删除操作会失败。必须先手动 DROP VIEW 删除所有相关视图。
如果使用 DROP TABLE <表名> CASCADE;，则在删除表的同时，所有依赖该表的视图也会被自动删除。

查询视图

从用户的角度看，查询视图的操作与查询基本表几乎完全相同。

-- 示例：查询信息科学系所有年龄小于 20 岁的学生
SELECT StudentID, Name
FROM IS_Student_Info -- 查询视图，就像查询表一样
WHERE Age < 20;

-- 示例：查询选修了课程 1 且成绩大于等于 90 分的信息科学系学生
SELECT *
FROM IS_Excellent_Course1;

视图查询处理：视图消解（View Resolution）

当用户提交一个针对视图的查询时，DBMS 内部会执行一个称为视图消解（或视图展开）的过程：

有效性检查：检查查询中涉及的视图是否存在，以及用户是否具有查询该视图的权限。
定义替换：将查询中对视图的引用，替换为该视图在数据字典中存储的子查询定义。
合并与重写：将用户的查询条件与视图定义中的条件（WHERE 子句）、连接操作等合并，形成一个等价的、直接针对底层基本表的复杂查询。
优化与执行：对合并后的查询进行优化，并最终在基本表上执行该查询，返回结果给用户。

示例：

假设用户查询：

1
2
3

SELECT StudentID, Name
FROM IS_Student_Info
WHERE Age < 20;

视图 IS_Student_Info 的定义为：

1 2	CREATE VIEW IS_Student_Info (StudentID, Name, Age) AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 'IS';

视图消解后的等价查询（大致过程）：

SELECT Sno, Sname -- 对应视图的 StudentID, Name
FROM Student
WHERE Sdept = 'IS' -- 来自视图定义的条件
  AND Sage < 20;   -- 来自用户查询的条件

DBMS 最终执行的是这个针对 Student 基本表的查询。

更新视图（`INSERT`, `UPDATE`, `DELETE`）

对视图进行数据更新操作（插入、修改、删除）比查询要复杂得多，并且受到严格限制。并非所有视图都允许更新。

可更新视图（Updateable View）

一个视图被称为可更新视图，前提是 DBMS 能够明确地将对视图的更新操作（INSERT, UPDATE, DELETE）唯一地映射回对其底层单个基本表的相应操作。

虽然具体的规则因 DBMS 而异，但通常，一个视图可更新需要满足以下基本条件（可能不完全充分）：

单一基表来源：视图的 FROM 子句必须只引用一个基本表（不能是连接多个表，也不能基于不可更新的视图）。
包含基表主键：视图的 SELECT 列表必须包含其基表的主键（或其他能唯一标识行的列），以确保能定位到要更新的基表行。
非聚合/非 DISTINCT：视图的定义中不能包含 GROUP BY、HAVING 子句或聚合函数（AVG, SUM, COUNT, MAX, MIN），也不能使用 DISTINCT 关键字。这些操作使得视图行与基表行之间不再是一一对应的关系。
非计算列更新：视图 SELECT 列表中的任何列如果是通过表达式或函数计算得出的，那么该列通常是不可更新的。
基表约束允许：更新操作不能违反基表的任何约束（如 NOT NULL、UNIQUE、CHECK、外键约束等）。

行列子集视图（从单表选择部分行和列，且包含主键）通常是可更新的。

更新操作的转换

如果一个视图是可更新的，对其执行 INSERT, UPDATE, DELETE 操作时，DBMS 会将其转换为对底层基本表的相应操作，并自动附加视图定义中的 WHERE 条件（对于 UPDATE 和 DELETE）。

示例：假设 IS_Student_Checked 视图（定义见上文）是可更新的。

UPDATE 视图：

1
2
3

UPDATE IS_Student_Checked
SET Age = 21
WHERE StudentID = '2024001';

转换为对 Student 表的操作（大致过程）：

UPDATE Student
SET Sage = 21
WHERE Sno = '2024001'
  AND Sdept = 'IS'; -- 视图的 WHERE 条件隐式加入

如果 IS_Student_Checked 定义时带有 WITH CHECK OPTION，那么 SET Sdept = 'CS' 这样的更新会被阻止。

INSERT 视图：

1 2	INSERT INTO IS_Student_Checked (StudentID, Name, Age) VALUES ('2024005', '钱七', 19);

转换为对 Student 表的操作（大致过程）：

1 2	INSERT INTO Student (Sno, Sname, Sage, Sdept) -- Sdept 会被设为 'IS' VALUES ('2024005', '钱七', 19, 'IS'); -- 视图的 WHERE 条件决定了 Sdept 的值

如果 IS_Student_Checked 定义时带有 WITH CHECK OPTION，且插入的值不满足 Sdept = 'IS'（例如显式提供 Sdept = 'CS'），插入会失败。

DELETE 视图：

1 2	DELETE FROM IS_Student_Checked WHERE StudentID = '2024005';

转换为对 Student 表的操作（大致过程）：

1
2
3

DELETE FROM Student
WHERE Sno = '2024005'
  AND Sdept = 'IS'; -- 视图的 WHERE 条件隐式加入

不可更新视图

以下类型的视图通常是不可更新的：

基于多表连接的视图（更新可能存在歧义）。
包含聚合函数、GROUP BY 或 HAVING 的视图（如 Student_Avg_Grade）。无法将对聚合结果（如平均分）的更新映射回具体的基表行。
包含 DISTINCT 的视图。
包含计算列的视图（如 Student_BirthYear），至少计算列本身不可更新。
基于不可更新视图创建的视图。

示例：

-- 尝试更新分组视图（通常会失败）
UPDATE Student_Avg_Grade
SET AverageGrade = 95
WHERE StudentID = '2024001'; -- 无法确定如何修改 SC 表中的具体成绩来达到这个平均分

更新限制

对视图的更新能力是数据库设计和使用中需要特别注意的地方。如果需要通过类似视图的结构进行更新，应确保视图设计满足可更新条件，或者考虑使用存储过程等其他机制来封装更新逻辑。

视图的作用与优势

使用视图可以带来多方面的好处：

简化复杂查询：
- 将复杂的连接、子查询、计算逻辑封装在视图中，用户只需编写简单的 SELECT 语句查询视图即可。
- 提高查询的可读性和易用性。
  示例：对于需要频繁查询「每个学生最高成绩及其对应课程」的需求，可以先创建一个视图 VMGRADE(Sno, MaxGrade) 找到每个学生的最高分，然后用户只需将 SC 表与此视图连接即可，简化了每次查询的逻辑。
数据访问控制与安全性：
- 可以为不同用户或角色创建不同的视图，只暴露他们有权访问的数据子集（特定的行或列）。
- 例如，可以创建一个视图隐藏员工的薪水列，只供普通查询使用。
- 这是实现基于内容的访问控制（Content-Based Access Control）的重要手段。
逻辑数据独立性：
- 视图提供了一个稳定的数据接口。即使底层基本表的结构发生某些变化（如表拆分、列重命名、添加列），只要能通过修改视图定义来保持视图接口不变，依赖该视图的应用程序就无需修改。
- 示例：如果将 Student 表垂直拆分为 Student_Personal(Sno, Sname, Ssex) 和 Student_Academic(Sno, Sage, Sdept)，可以创建一个名为 Student 的视图，通过连接这两个新表来模拟原始 Student 表的结构，从而对应用程序透明。

逻辑独立性的局限

虽然视图提高了逻辑独立性，但这种独立性并非绝对。如果基表结构变化过大，可能无法通过视图完全屏蔽。此外，如前所述，对视图的更新操作对基表结构的依赖性更强。

提供多角度数据视图：
- 同一份基础数据，可以通过不同的视图，以满足不同用户或应用场景的特定需求和理解方式，而无需数据冗余。
提高查询表达清晰度：
- 对于多步骤的复杂分析，可以将中间结果定义为视图，使最终查询逻辑更清晰、更易于理解和维护。

数据更新

数据插入（INSERT）

插入单行数据

插入子查询结果

数据修改（UPDATE）

数据删除（DELETE）

DELETE vs. TRUNCATE TABLE

SQL 中的 NULL 值

NULL 的含义与产生

判断 NULL 值（IS NULL, IS NOT NULL）

示例

NULL 与完整性约束

NULL 与运算

算术运算

比较运算与三值逻辑

WHERE 和 HAVING 子句中的处理

示例：NULL 对查询结果的影响

聚合函数与 NULL

视图（View）

定义视图：CREATE VIEW

视图类型与示例

删除视图：DROP VIEW

查询视图

视图查询处理：视图消解（View Resolution）

更新视图（INSERT, UPDATE, DELETE）

可更新视图（Updateable View）

更新操作的转换

不可更新视图

视图的作用与优势

数据插入（`INSERT`）

数据修改（`UPDATE`）

数据删除（`DELETE`）

`DELETE` vs. `TRUNCATE TABLE`

SQL 中的 `NULL` 值

`NULL` 的含义与产生

判断 NULL 值（`IS NULL`, `IS NOT NULL`）

`NULL` 与运算

`WHERE` 和 `HAVING` 子句中的处理

示例：`NULL` 对查询结果的影响

聚合函数与 `NULL`

定义视图：`CREATE VIEW`

删除视图：`DROP VIEW`

更新视图（`INSERT`, `UPDATE`, `DELETE`）