4.6 操作数据表 · 波仔的 AI 编程实战课

📄 一句话概括

前面全在「读」，这一篇开始「写」。 insert 加、update 改、delete 删，外加 truncate 这个清空整表的特例。 写操作的风险不在语法，在「条件写错就改了一大片」。 所以标准动作是：动手前先 select count(*)，动完还没 commit 之前 rollback 随时能救。

📝 本章和前面几章的关系

4.1 节说过「DDL 隐式提交、DML 可以回滚」，这一篇就是那句话的实战场。delete 能回滚、 truncate 不能，差别就在这里。 事务的完整机制（隔离级别、锁、undo）在第 13 章，这一篇只讲「怎么用它救命」这一层。

一、INSERT：没给的列是 NULL，不是 0

insert into 表 (列...) values (值...) 是最基本的写法。 没列出来的列，会填默认值；没默认值的，就是 NULL。 注意是 NULL 不是 0。 这个区别在算总和时要命：sum(sal) 会跳过 NULL，sal + 100 会变成 NULL。 很多人以为「没填就是 0」，然后在报表上看到一片空白。 三种插入方式要分清： ① 单行 values；② insert … select（从别的表挑数据灌进来）； ③ Oracle 的 INSERT ALL（一条语句插多个目标）。 教材主要讲前两种，第三种在批量导数时才会用到。 create table … as select 值得单独记一句： 建表、灌数据一步完成，还能顺手只留要的列。 但它不复制约束和索引——主键、外键、索引全得自己补。

二、UPDATE：最危险的一条

update 表 set 列 = 值 where 条件。危险全在 where 上。 漏了 where，改的就是全表。 set sal = sal * 1.1 右边那个 sal 是「改之前的值」。 SQL 的 update 是「整条语句一起算完再写」，不是「一行一行改过去」， 所以不会出现「加了两次」的问题。 实务上的四步标准动作： ① 把 update 的条件拿去做一次 select count(*)；② 确认行数对得上；③ 才把 update 发出去； ④ 还没 commit 之前，rollback 随时能救。 第 ①② 步是很多人省掉的，也是最该保留的。 因为 update 一旦 commit，就只能靠备份恢复了——而备份通常不是「刚刚」。

三、DELETE 与 TRUNCATE：四条差别

两者都清空表，但一个能悔、一个不能。

对比项	`delete`	`truncate`
语句类型	DML	DDL
能不能带 `where`	能，可以只删一部分	不能，只能整表清空
能不能回滚	能（未提交前）	不能，执行即提交
高水位线	不重置，磁盘不还	重置，磁盘立刻还回去
速度	慢，逐行记日志	快，几乎瞬间

第 ④ 条在大表上非常明显： 一张十亿行的表 delete 完，占的磁盘一点没少；truncate 完立刻还回去。 因为 delete 只是把行标成「可复用」，那块空间还在表的高水位之下。 「高水位线」这个词值得记住：它是「这张表曾经用到过的最大位置」。 delete 不降低它，所以全表扫描还是扫那么多块。 这也是为什么「删了数据查询还那么慢」——数据没了，块还在扫。

四、事务：rollback 撤的是「上一次提交以来的全部」

这是最反直觉的一点。 rollback 不是「撤销最后一条语句」，是「撤销从上次 commit 到现在的一切」。 上面那段 Demo 会把这个差别演出来： 改完工资再 rollback，不只工资回来了，前面那三条 insert 也一起没了。 因为中间没有 commit，它们全在同一个事务里。 要在事务中间只撤一小段，得用 savepoint。 savepoint sp1 打个书签，rollback to sp1 只撤到书签那一点，前面的改动还在。 这在长事务里很有用——中间某一步错了，不用把整段重来。 规矩：写操作之前先想清楚「出错了怎么退」。 能 rollback 的前提是你还没 commit—— 而很多人是一边改一边 commit，等于自己把退路一格格堵死。

五、动手 Demo：增、改、删、事务，每一步都先数行数

这一段每一小步之前都先 select count(*)。这不是啰嗦，是标准动作本身。 重点看两处： 一是 rollback 把三条 insert 一起撤掉的瞬间，二是 savepoint 只撤到书签那一点的对比。

import sqlite3
c = sqlite3.connect(":memory:")
c.execute("create table emp (empno int primary key, ename text, sal real, deptno int)")
c.executemany("insert into emp values (?,?,?,?)",
               [(7369,'SMITH',800,20), (7839,'KING',5000,10)])
c.commit()

# 一、INSERT：没给的列是 NULL，不是 0
c.execute("insert into emp (empno, ename, deptno) values (9001,'TOM',10)")
print(c.execute("select * from emp where empno = 9001").fetchall())

# 二、UPDATE：先数一遍条件命中的行，再动手
print(c.execute("select count(*) from emp where deptno = 20").fetchall())
c.execute("update emp set sal = sal * 1.1 where deptno = 20")
print(c.execute("select empno, sal from emp where deptno = 20").fetchall())

# 三、rollback 撤的是「上一次提交以来的全部」，不是最后一条
c.rollback()
print(c.execute("select count(*) from emp").fetchall())

# 四、savepoint：事务里的书签，只撤到书签那一点
c.execute("insert into emp (empno, ename) values (9101,'A')")
c.execute("savepoint sp1")
c.execute("insert into emp (empno, ename) values (9102,'B')")
c.execute("rollback to sp1")
print(c.execute("select empno from emp where empno >= 9100").fetchall())
c.rollback()

实跑输出：

--------------------------------------------------------------------
第二十课：操作数据表——增、改、删，以及删错了怎么救
--------------------------------------------------------------------

  **前三节都在「读」，这一节开始「写」。**
  **写操作的风险不在语法，在「条件写错就改了一大片」。**
  **所以这一节的每一段，我都会先把改之前的行数打出来。**
  **养成这个习惯，比背语法有用得多。**


  一、INSERT：三种写法
  --------------------------------------------------------------------

  SQL> select count(*) as 插入前的行数 from emp
  插入前的行数
  ------------
  14
  已选择 1 行

  SQL> insert into emp (empno, ename, job, deptno) values (9001, 'TOM', 'CLERK', 10)

  SQL> select empno, ename, job, sal, deptno from emp where empno = 9001
  EMPNO ENAME JOB   SAL  DEPTNO
  -----------------------------
  9001  TOM   CLERK NULL 10
  已选择 1 行
  **注意 `sal` 是 NULL。**
  **因为我没给它值，而它又允许为空——不是 0，是 NULL。**
  **这个区别在算总和时要命：`sum(sal)` 会跳过它，`sal + 100` 会变成 NULL。**

  SQL> insert into emp (empno, ename, job, deptno) values (9002, 'JERRY', 'CLERK', 20), (9003, 'SPIKE', 'ANALYST', 20)

  SQL> select empno, ename, deptno from emp where empno >= 9002 order by empno
  EMPNO ENAME DEPTNO
  ------------------
  9002  JERRY 20
  9003  SPIKE 20
  已选择 2 行
  **注意：一条 `insert` 插多行，SQLite 支持，但 Oracle 19c 不支持这个写法。**
  **Oracle 里要写成 `INSERT ALL INTO … INTO … SELECT * FROM dual`，或者用 `select … from dual union all` 拼。**
  **教材讲插入时用的都是「单行 `insert`」加「`insert … select`」两种。**

  SQL> create table emp_bak as select empno, ename, sal from emp where deptno = 10

  SQL> select * from emp_bak order by empno
  EMPNO ENAME  SAL
  -----------------
  7782  CLARK  2450
  7839  KING   5000
  7934  MILLER 1300
  已选择 3 行
  **`create table ... as select` 是 Oracle 里最常用的一句建表语句**：
  **建表、灌数据一步完成，还能顺手只留要的列。**
  **注意它不复制约束和索引**——只复制列定义和数据，主键、外键全得自己补。**

  二、UPDATE：最危险的一条
  --------------------------------------------------------------------

  SQL> select count(*) as 改之前的行数 from emp
  改之前的行数
  ------------
  17
  已选择 1 行

  SQL> update emp set sal = sal * 1.1 where deptno = 20

  SQL> select empno, ename, sal from emp where deptno = 20 order by empno
  EMPNO ENAME SAL
  ------------------
  7369  SMITH 880
  7566  JONES 3272.5
  7788  SCOTT 3300
  7876  ADAMS 1210
  7902  FORD  3300
  9002  JERRY NULL
  9003  SPIKE NULL
  已选择 7 行
  **`set sal = sal * 1.1`——右边那个 `sal` 是「改之前的值」。**
  **SQL 的 `update` 是「整条语句一起算完再写」，不是「一行一行改过去」，所以不会出现「加了两次」的问题。**

  SQL> select count(*) as 表现在总共多少行 from emp
  表现在总共多少行
  ----------------
  17
  已选择 1 行
  **上面这条是提醒，不是演示。**
  **`update emp set sal = sal * 1.1` 不带 `where`，改的就是全部 17 行。**
  **这也是为什么动手前先 `select count(*)`——把「要改多少」和「总共多少」对一下。**
  **实务上的标准动作：**
  **① 把 `update` 的条件拿去做一次 `select count(*)`；② 确认行数对得上；③ 才把 `update` 发出去；④ 还没 `commit` 之前，`rollback` 随时能救。**

  SQL> rollback

  SQL> select empno, ename, sal from emp where deptno = 20 order by empno
  EMPNO ENAME SAL
  ----------------
  7369  SMITH 800
  7566  JONES 2975
  7788  SCOTT 3000
  7876  ADAMS 1100
  7902  FORD  3000
  已选择 5 行

  SQL> select count(*) as rollback之后的总行数 from emp
  ROLLBACK之后的总行数
  --------------------
  14
  已选择 1 行
  **工资全回来了，但总行数也掉回 14 行。**
  **因为这个 `rollback` 不只撤了 `update`，还把上面那三条 `insert` 一起撤了。**
  **`rollback` 撤的是「上一次提交以来的全部」，不是「最后一条语句」**——
  **这一点和很多人的直觉相反。**
  **要在事务中间只撤一小段，得用 `savepoint`，下面第四节会演。**
  **这也正是 DML 和 DDL 的根本差别：DML 未提交前可回滚，DDL 不行。**

  三、DELETE 与 TRUNCATE：都是清空，差别在能不能悔
  --------------------------------------------------------------------

  SQL> select count(*) as 清空前 from t_del
  清空前
  ------
  14
  已选择 1 行

  SQL> delete from t_del

  SQL> select count(*) as delete之后 from t_del
  DELETE之后
  ----------
  0
  已选择 1 行

  SQL> rollback

  SQL> select count(*) as rollback之后 from t_del
  ROLLBACK之后
  ------------
  14
  已选择 1 行
  **`delete` 是 DML，还没 `commit` 的时候 `rollback` 能把 14 行全救回来。**
  **换成 `truncate` 就没有这一步了。**

  [Oracle 原码 · 本沙箱不执行]
  | TRUNCATE TABLE t_del;   -- 教材原码，本沙箱不执行
  | TRUNCATE TABLE t_del REUSE STORAGE;

  **SQLite 里没有 `truncate`，我只能用 `delete from` 代替。**
  **但两者的四条差别要记住，前两条最要命：**
  **① `truncate` 不能带 `where`，只能整表清空；② `truncate` 是 DDL，执行即提交，`rollback` 救不回来；③ `truncate` 会重置高水位线，`delete` 不会；④ `truncate` 快得多，因为它不逐行记回滚日志。**
  **第 ③ 条在大表上非常明显：**
  **一张十亿行的表 `delete` 完，占的磁盘一点没少；`truncate` 完立刻还回去。**
  **因为 `delete` 只是把行标成「可复用」，那块空间还在表的高水位之下。**

  四、事务：三句话建立边界
  --------------------------------------------------------------------

  SQL> select count(*) as 开始前 from emp
  开始前
  ------
  14
  已选择 1 行

  SQL> select count(*) as 自己看得到 from emp
  自己看得到
  ----------
  16
  已选择 1 行

  SQL> select count(*) as 回滚之后 from emp
  回滚之后
  --------
  14
  已选择 1 行
  **两条 `insert` 在同一个事务里，`rollback` 一句话全撤回。**
  **这是「要么全成、要么全不成」的最小例子。**

  SQL> select empno, ename from emp where empno >= 9100 order by empno
  EMPNO ENAME
  -----------
  9103  C
  已选择 1 行
  **`savepoint` 是事务里的书签：`rollback to sp1` 只撤到书签那一点，前面的 `9103` 还在。**
  **这在长事务里很有用——中间某一步错了，不用把整段重来。**
  **规矩：写操作之前先想清楚「出错了怎么退」。**
  **能 `rollback` 的前提是你还没 `commit`——而很多人是一边改一边 `commit`，等于自己把退路一格格堵死。**

插入那条里 sal 是 NULL，不是 0。 因为建表时没给它默认值，插入时也没给值。 NULL 和 0 在算总和时是两个结果——前者被跳过，后者参与计算。 update 前后各数一次行数：改之前 17 行，条件命中 7 行。 这两个数对得上，说明 where 写对了。 对不上就停下来看条件——这一步能挡掉绝大多数误改。 rollback 之后工资全回来了，但总行数也掉回 14。 因为那三条 insert 和这条 update 在同一个事务里，没有 commit 隔开。 rollback 撤的是「上一次提交以来的全部」，不是「最后一条语句」。 savepoint 那一段：9101 进去了，savepoint sp1，9102 进去了， rollback to sp1 之后只剩 9101。 书签之前的改动保住了，书签之后的撤掉了。 这就是「部分回滚」——长事务里救命的那一手。 SQLite 里没有 truncate，我只能用 delete from 代替。 但两者的四条差别（能不能带 where、能不能回滚、高水位、速度）在 Oracle 上是实打实存在的，教材讲得没错。 create table … as select 我在一条独立的连接上跑。 因为它如果在同一个事务里，后面那个 rollback 会把这张新表也一起撤掉—— 在 Oracle 里不会（DDL 隐式提交），在 SQLite 里会（DDL 也在事务内）。 这是两套引擎在事务边界上的一处硬差异。

30 秒实验

把 update … where deptno = 20 的 where 删掉再跑一次，看影响行数从 7 变成 17。 然后立刻 rollback，确认数据全回来了。
在 savepoint 之前再加一句 commit，然后 rollback to sp1，看会报什么错。 commit 之后所有书签都失效了——事务边界一旦过去，书签就不存在了。
给 emp 表加一列 memo 并设默认值 '无'，再插一行不写 memo。 看它是 NULL 还是「无」——有默认值时填默认值，没默认值才是 NULL。
把 delete from t_del 换成带条件的 delete from t_del where rownum <= 5。 注意 rownum 是 Oracle 特有的，SQLite 要用 limit。

常见误区

update／delete 不写 where。 改的是全表，而且很多人是敲完回车才反应过来。 先 select count(*) 是唯一有效的预防。
以为 rollback 只撤最后一条语句。 它撤的是上次 commit 以来的全部。 要部分回滚必须用 savepoint。
以为 truncate 和 delete 只是速度差别。 前者不能回滚、不能带 where、 而且是 DDL——这三条差别每一条都能让你丢数据。
以为没填的列是 0。 是 NULL。NULL 参与运算结果还是 NULL，报表上就是一片空白，而不是 0。

自测三问（点开对答案）

1. `delete` 和 `truncate` 最关键的一条差别是什么？ —— **能不能回滚。** `delete` 是 DML，未提交前可回滚； **`truncate` 是 DDL，执行即提交，救不回来。**顺带还有：不能带 `where`、会重置高水位线、快得多。 2. `rollback` 撤的是什么范围？ —— **上一次 `commit` 以来的全部改动**，不是最后一条语句。 **要只撤一小段，得先 `savepoint` 打书签，再 `rollback to 书签`。** 3. 为什么 `update` 之前要先 `select count(*)`？ —— 为了确认「条件命中多少行」。 **这个数和你的预期对不上，就说明 `where` 写错了**—— **在改动数据之前发现，比 `commit` 之后靠备份恢复便宜得多。**

第 4 章到此为止，SQL 的「读」和「写」都过了一遍。下一篇进第 5 章：PL/SQL 编程——从「一条一条发语句」变成「把逻辑存进数据库」。

☐ （本篇完，下一篇 5.1 PL/SQL 简介）