DBPA · 3.4

数据更新

让关系实例按业务规则发生变化

VER. 2608.2 Built with impress.js

学习目标

INSERT、UPDATE 和 DELETE 分别增加、修改和删除关系实例中的元组

01区分 INSERT、UPDATE、DELETE 改变关系实例的方式
02解释省略列时 DEFAULT、NULL 与 NOT NULL 的关系
03用 INSERT SELECT 批量写入并检查列数、类型和约束
04用 UPDATE 的 SET、WHERE 和子查询表达新值与目标集合
05区分 DELETE 与 DROP，并判断主码、外码和业务约束失败
06在更新前预览目标集合、核对影响行数，并在更新后复查完整性

2/15

LEARNING OBJECTIVES

每次数据变更都要回答四个问题

三类操作都要明确目标表、受影响元组、变化内容和约束状态

INSERT、UPDATE、DELETE 共同遵循目标表、目标元组、新值和约束四问

四个判断点

先确定目标表，再确定受影响元组；然后说明变化内容和取值来源，最后检查约束能否保持

INSERT

新元组来自 VALUES 或 SELECT

UPDATE

目标行由 WHERE 选出，值由 SET 给出

DELETE

目标行由 WHERE 选出，不产生新值

3/15

UPDATE MODEL

插入时先明确属性列和值的对应关系

明确的属性列列表让语句不依赖表定义顺序

SQL

01
02INSERT INTO Student(Sno, Sname, Ssex, Smajor, Sbirthdate)
VALUES ('20180009', '陈冬', '男', '信息管理与信息系统', '2000-5-22');

SQL

01
02
03INSERT INTO SC(Sno, Cno, Semester, Teachingclass)
VALUES ('20180009', '81004', '20202', '81004-01');
-- Grade 未提供；当前 3.2 定义没有 DEFAULT，取 NULL

Takeaway

未列出的列有 DEFAULT 时取默认值；可空且无 DEFAULT 时取 NULL；NOT NULL 且无 DEFAULT 时会导致插入失败

4/15

INSERT ONE TUPLE

查询结果可以一次写入多个元组

INSERT ... SELECT 先形成查询结果，再按目标列把多个元组写入目标表；单独执行 SELECT 可以预览这些元组

SQL

01
02
03
04
05
06
07
08
09
10
11
12
13
14-- 先建立目标表，再执行批量插入
CREATE TABLE Smajor_age (
  Smajor VARCHAR(40) NOT NULL PRIMARY KEY,
  Avg_age DECIMAL(5,2)
);

-- 先单独执行下面的 SELECT，确认结果后再执行整条 INSERT
INSERT INTO Smajor_age(Smajor, Avg_age)
SELECT Smajor,
       AVG(extract(year from current_date) - extract(year from Sbirthdate))
FROM Student
WHERE Smajor IS NOT NULL
  AND Sbirthdate IS NOT NULL
GROUP BY Smajor;

Takeaway

先预览查询结果，再批量写入；目标列、列数、类型与约束必须对应

5/15

INSERT SELECT

插入后必须检查表约束

新元组必须符合表定义和完整性约束

情况	结果	需要检查
列表中提供值	按列名对应写入	类型与业务含义
省略可空且无 DEFAULT 的列	取 NULL	是否允许空值
省略 NOT NULL 且有 DEFAULT 的列	取 DEFAULT	默认值定义
省略 NOT NULL 且无 DEFAULT 的列	插入失败	是否补齐必需值
主码重复或外码不存在	DBMS 拒绝该行	实体与参照完整性

Takeaway

当前 3.2 的 SC.Grade 没有 DEFAULT；单行插入中省略 Grade 会取 NULL，不是 0

6/15

INSERT CHECK

UPDATE：SET 改值，WHERE 选行

SET 表达新值，WHERE 确定受影响的元组

SQL

01
02
03
04
05UPDATE SC
SET Grade = Grade - 5
WHERE Semester = '20201'
  AND Cno = '81002'
  AND Grade IS NOT NULL;

SET 表达改成什么，WHERE 决定改哪些行

Takeaway

本例只调整已经录入的成绩；NULL 表示尚未录入，不参与数值减法，0—100 范围需由约束或业务条件保证

7/15

UPDATE TARGET

WHERE 是更新安全性的边界

省略 WHERE 不是少写一个条件，而是选择全表更新

精确更新

以主码或业务键定位一个元组

批量更新

用学期、课程和状态组合出目标集合

全表更新

省略 WHERE 后表中所有元组都会受影响

Takeaway

执行 UPDATE 前，应使用相同的 WHERE Semester = '20201' AND Cno = '81002' 做 SELECT 预览

8/15

UPDATE SCOPE

子查询可以构造 UPDATE 的目标集合

外层更新表，内层查询负责找出符合条件的键

SQL

01
02
03
04
05
06
07UPDATE SC
SET Grade = 0
WHERE Grade IS NOT NULL
  AND Sno IN
      (SELECT Sno
       FROM Student
       WHERE Smajor = '计算机科学与技术');

Takeaway

先运行内层 SELECT，确认它返回的键确实是要修改的对象；不要把尚未录入的 NULL 无提示地改成零分

9/15

UPDATE SUBQUERY

DELETE 删除元组，不改变表的定义

DELETE 改变表实例，不改变表对象、列和索引的定义

SQL

01
02
03
04
05
06
07
08-- 先检查 SC 是否仍有引用记录
SELECT Sno, Cno
FROM SC
WHERE Sno = '20180007';

-- 确认按业务规则处理引用后，再判断是否删除 Student
DELETE FROM Student
WHERE Sno = '20180007';

操作	删除元组	删除表定义
`DELETE ... WHERE`	指定元组	否
`DELETE FROM 表`	全部元组	否
`DROP TABLE`	连同对象一起移除	是

Takeaway

若 SC 仍引用该 Student，外码可能拒绝 DELETE；只有约束明确声明 ON DELETE CASCADE，才讨论级联处理

10/15

DELETE DATA

删除条件也可以来自另一个关系

删除条件确定目标元组；外码引用和业务后果还需要单独检查

SQL

01
02
03
04
05DELETE FROM SC
WHERE Sno IN
      (SELECT Sno
       FROM Student
       WHERE Smajor = '计算机科学与技术');

Takeaway

本例删除的是 SC 子行，不删除 Student；若删除被引用的 Student，是否同步删除 SC 由外码上的 ON DELETE 规则决定

11/15

DELETE SUBQUERY

更新前后都要检查目标集合与关系状态

变更正确性取决于目标集合、影响范围和更新后的关系状态

操作闭环

先用 SELECT 预览目标集合，再估计影响行数；执行变更后复查关键列

实体完整性

主码唯一且不为空

参照完整性

外码引用有效的被参照值

用户定义完整性

业务范围和状态规则仍然成立

Takeaway

客户端显示的影响行数是辅助证据，不能替代变更后的 SELECT 复查

12/15

VERIFY AND CONSTRAIN

用目标集合和约束检查数据更新

INSERT、UPDATE 和 DELETE 都要先确定目标集合，再检查约束和变更结果

插入

INSERT 需要明确属性列，处理 DEFAULT 与 NULL，并检查 INSERT SELECT 的结果和约束

修改

UPDATE 用 SET 表达新值，用 WHERE 或子查询确定目标集合，并防止误触发全表更新

删除

DELETE 只删除元组；DROP TABLE 删除表对象，外码后果和 CASCADE 必须按定义确认

Takeaway

安全的数据更新遵循“先预览、后变更、再复查”的顺序

13/15

RECAP

本节知识地图

14/15

KNOWLEDGE MAP

本节问题

01INSERT 为什么应显式列出属性？省略 SC.Grade 时，DEFAULT、NULL 与 NOT NULL 如何决定结果？
02INSERT SELECT 执行前如何检查列数、类型和约束？目标列 2 列而 SELECT 返回 3 列会怎样？
03SET 与 WHERE 分别决定什么？Grade 为 NULL 时，Grade - 5 与 Grade = 0 有何区别？省略 WHERE 会怎样？
04DELETE 与 DROP TABLE 有何不同？删除被 SC 引用的 Student 为何可能失败？如何用执行前后 SELECT 与影响行数避免误操作？

15/15

CHECK YOUR UNDERSTANDING