第四章数据增删改（DML）

4.1 插入数据（INSERT）

单行插入

sql

-- 指定列名插入（推荐，顺序可以不同于表定义）
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@test.com', 25);

-- 按表定义顺序插入所有列（不推荐，表结构变了容易出错）
INSERT INTO users VALUES (NULL, '李四', 'lisi@test.com', 30);

注意：当 id 列是 INTEGER PRIMARY KEY 时，传 NULL 会自动分配下一个可用的 id 值。也可以直接省略 id 列。

多行插入

一条 INSERT 插入多行，比多条单行 INSERT 快得多（减少事务开销）：

sql

INSERT INTO users (name, email, age) VALUES
    ('王五', 'wangwu@test.com', 22),
    ('赵六', 'zhaoliu@test.com', 28),
    ('孙七', 'sunqi@test.com', 35);

INSERT OR REPLACE

当插入数据与已有行产生唯一约束冲突（PRIMARY KEY 或 UNIQUE）时，INSERT OR REPLACE 会删除旧行再插入新行：

sql

CREATE TABLE configs (
    key   TEXT PRIMARY KEY,
    value TEXT
);

INSERT INTO configs VALUES ('theme', 'dark');
INSERT INTO configs VALUES ('theme', 'light');
-- Error: UNIQUE constraint failed: configs.key

-- 用 INSERT OR REPLACE，冲突时替换
INSERT OR REPLACE INTO configs VALUES ('theme', 'light');
-- 现在 theme 的值变成了 'light'

INSERT OR REPLACE 的行为：

  有冲突 → DELETE 旧行 → INSERT 新行
  无冲突 → 正常 INSERT

  ⚠️ 注意：如果旧行有其他列的值，会被替换为新行的值。
  没有指定的列会变成 DEFAULT 值，不是保留旧值！

INSERT OR IGNORE

冲突时静默跳过，不报错也不插入：

sql

INSERT OR IGNORE INTO configs VALUES ('theme', 'blue');
-- 如果 'theme' 已存在，这行被跳过，不报错
-- 如果 'theme' 不存在，正常插入

从查询结果插入

sql

-- 把查询结果直接插入另一张表
INSERT INTO vip_users (name, email)
SELECT name, email FROM users WHERE age > 30;

-- 复制整张表的数据
INSERT INTO users_backup SELECT * FROM users;

冲突处理策略一览

SQLite 提供了 5 种冲突处理策略，INSERT OR 后面可以跟：

策略	冲突时行为	适用场景
`ABORT`	回滚当前语句，报错（默认）	常规插入
`ROLLBACK`	回滚整个事务，报错	需要撤销所有已操作的场景
`FAIL`	停止当前语句，已插入的行保留，报错	批量插入希望保留已成功的部分
`IGNORE`	跳过冲突行，继续后续行	批量导入，允许重复
`REPLACE`	删除冲突行，插入新行	覆盖式更新配置等

sql

-- 批量导入时忽略重复
INSERT OR IGNORE INTO users (name, email, age) VALUES
    ('张三', 'zhangsan@test.com', 25),   -- 已存在，跳过
    ('新用户', 'new@test.com', 20);      -- 不存在，插入

4.2 更新数据（UPDATE）

基本语法

sql

UPDATE users SET age = 26 WHERE name = '张三';

UPDATE 三要素：

  UPDATE 哪张表
  SET    改哪些列 = 改成什么值
  WHERE  改哪些行（⚠️ 不加 WHERE 会更新所有行！）

同时更新多列

sql

UPDATE users
SET age = 31, email = 'zhangsan_new@test.com'
WHERE name = '张三';

基于表达式更新

sql

-- 所有人年龄 +1
UPDATE users SET age = age + 1;

-- 价格打 8 折
UPDATE products SET price = price * 0.8 WHERE category = '清仓';

-- 用内置函数
UPDATE users SET name = upper(name) WHERE id = 1;

用子查询更新

sql

-- 根据订单表的统计结果更新用户表
UPDATE users
SET total_orders = (
    SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id
);

UPDATE 与 LIMIT

SQLite 支持 UPDATE ... LIMIT（需编译时启用 SQLITE_ENABLE_UPDATE_DELETE_LIMIT，大多数预编译版本已启用）：

sql

-- 只更新前 10 个符合条件的行
UPDATE users SET status = 'inactive'
WHERE last_login < '2023-01-01'
ORDER BY last_login ASC
LIMIT 10;

⚠️ 危险操作提醒

sql

-- 这会更新所有行！！
UPDATE users SET age = 0;

-- 正确做法：一定带 WHERE
UPDATE users SET age = 0 WHERE id = 5;

安全建议：执行 UPDATE 前，养成先用同样的 WHERE 条件跑一遍 SELECT 的习惯，确认影响的行数符合预期。

4.3 删除数据（DELETE）

基本语法

sql

DELETE FROM users WHERE name = '张三';

同样，不加 WHERE 会删除所有行：

sql

-- ⚠️ 删除整张表的所有数据
DELETE FROM users;

带条件的删除

sql

-- 删除年龄小于 18 岁的用户
DELETE FROM users WHERE age < 18;

-- 删除没有邮箱的用户
DELETE FROM users WHERE email IS NULL;

-- 用子查询删除：删除没有任何订单的用户
DELETE FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

清空表的三种方式对比

方式	语法	速度	自增 id 重置？	触发器执行？
`DELETE`	`DELETE FROM users;`	较慢（逐行删除）	❌ 不重置	✅ 执行
`DELETE` + 重置	见下方	较慢	✅ 重置	✅ 执行
删除文件重建	删文件 + 重建表	最快	✅ 重置	❌ 不执行

sql

-- DELETE 后手动重置自增 id（如果使用了 AUTOINCREMENT）
DELETE FROM users;
DELETE FROM sqlite_sequence WHERE name = 'users';

注意：SQLite 没有 MySQL 的 TRUNCATE TABLE 语法。但当 DELETE FROM 不带 WHERE 且没有触发器时，SQLite 会自动优化为类似 TRUNCATE 的操作（清空整个 B-Tree），速度很快。

软删除（推荐实践）

实际项目中，很多时候不会真正删除数据，而是用软删除——标记为已删除：

sql

-- 表设计时加一个 deleted_at 列
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT UNIQUE,
    deleted_at TEXT DEFAULT NULL  -- NULL 表示未删除
);

-- "删除" 操作 → 其实是更新
UPDATE users SET deleted_at = datetime('now') WHERE id = 5;

-- 查询时过滤已删除的数据
SELECT * FROM users WHERE deleted_at IS NULL;

-- 真正需要时才物理删除
DELETE FROM users WHERE deleted_at IS NOT NULL
  AND deleted_at < datetime('now', '-30 days');  -- 30 天前的才删

4.4 UPSERT —— INSERT ... ON CONFLICT（SQLite 3.24+）

UPSERT 是 "UPDATE or INSERT" 的缩写——如果数据存在就更新，不存在就插入。这是 SQLite 3.24（2018 年 6 月）引入的现代语法，比 INSERT OR REPLACE 更灵活、更安全。

基本语法

sql

INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...)
ON CONFLICT (冲突列) DO UPDATE SET 列 = 新值;

实战示例

场景一：配置表——存在就更新值，不存在就插入

sql

CREATE TABLE configs (
    key        TEXT PRIMARY KEY,
    value      TEXT,
    updated_at TEXT
);

INSERT INTO configs (key, value, updated_at)
VALUES ('theme', 'dark', datetime('now'))
ON CONFLICT (key) DO UPDATE SET
    value = excluded.value,
    updated_at = excluded.updated_at;

excluded 是一个特殊关键字，代表本次试图插入但被冲突阻止的那行数据：

┌─────────────────────────────────────────────────┐
│   excluded 关键字                                │
│                                                 │
│   INSERT INTO configs VALUES ('theme', 'dark')  │
│                                                 │
│   如果 'theme' 已存在，触发 ON CONFLICT：        │
│   excluded.key   = 'theme'   ← 新插入的值       │
│   excluded.value = 'dark'    ← 新插入的值       │
│                                                 │
│   configs.key    = 'theme'   ← 已有行的值        │
│   configs.value  = 'light'   ← 已有行的值        │
└─────────────────────────────────────────────────┘

场景二：计数器——存在就 +1，不存在就初始化为 1

sql

CREATE TABLE page_views (
    url   TEXT PRIMARY KEY,
    count INTEGER DEFAULT 0
);

-- 每次访问执行这条语句
INSERT INTO page_views (url, count)
VALUES ('/home', 1)
ON CONFLICT (url) DO UPDATE SET count = count + 1;

场景三：批量同步——存在就更新，不存在就插入

sql

INSERT INTO products (id, name, price, stock)
VALUES
    (1, '键盘', 299, 100),
    (2, '鼠标', 99, 200),
    (3, '显示器', 1999, 50)
ON CONFLICT (id) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price,
    stock = excluded.stock;

DO NOTHING —— 冲突时什么都不做

sql

INSERT INTO users (email, name)
VALUES ('test@test.com', '测试用户')
ON CONFLICT (email) DO NOTHING;
-- 等效于 INSERT OR IGNORE，但语法更明确

UPSERT vs INSERT OR REPLACE

特性	`ON CONFLICT DO UPDATE`（UPSERT）	`INSERT OR REPLACE`
冲突行为	更新已有行的指定列	删除旧行 + 插入新行
未指定的列	保留旧值 ✅	变成 DEFAULT 值 ❌
行的 rowid	不变	变了（因为是新行）
触发器	触发 UPDATE 触发器	触发 DELETE + INSERT 触发器
推荐程度	⭐⭐⭐ 推荐	⚠️ 有副作用，谨慎使用

sql

-- 对比演示
CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    bio  TEXT DEFAULT '无'
);

INSERT INTO users VALUES (1, '张三', '程序员');

-- INSERT OR REPLACE：bio 会变成 DEFAULT 值 '无'！
INSERT OR REPLACE INTO users (id, name) VALUES (1, '张三');
SELECT * FROM users;  -- 1 | 张三 | 无  ← bio 丢了！

-- UPSERT：bio 保留原值
INSERT INTO users (id, name) VALUES (1, '张三')
ON CONFLICT (id) DO UPDATE SET name = excluded.name;
SELECT * FROM users;  -- 1 | 张三 | 程序员  ← bio 还在！

结论：如果你的 SQLite 版本 ≥ 3.24，优先使用 ON CONFLICT DO UPDATE。它比 INSERT OR REPLACE 更安全、更直观。

本章小结

知识点	要点
INSERT	指定列名插入（推荐）；多行插入更高效
冲突处理	5 种策略：ABORT / ROLLBACK / FAIL / IGNORE / REPLACE
UPDATE	三要素：表 + SET + WHERE；不带 WHERE 会更新所有行
DELETE	不带 WHERE 会删除所有行；SQLite 无 TRUNCATE
软删除	用 `deleted_at` 列标记删除，查询时 `WHERE deleted_at IS NULL`
UPSERT	`ON CONFLICT DO UPDATE`（3.24+），比 REPLACE 更安全
excluded	UPSERT 中引用本次插入的新值

下一章预告：进入最核心的 SELECT 查询——从基础过滤到多表联查、子查询和 CTE，覆盖日常 90% 的查询需求。

第四章 数据增删改（DML） ​

4.1 插入数据（INSERT） ​

单行插入 ​

多行插入 ​

INSERT OR REPLACE ​

INSERT OR IGNORE ​

从查询结果插入 ​

冲突处理策略一览 ​

4.2 更新数据（UPDATE） ​

基本语法 ​

同时更新多列 ​

基于表达式更新 ​

用子查询更新 ​

UPDATE 与 LIMIT ​

⚠️ 危险操作提醒 ​

4.3 删除数据（DELETE） ​

基本语法 ​

带条件的删除 ​

清空表的三种方式对比 ​

软删除（推荐实践） ​

4.4 UPSERT —— INSERT ... ON CONFLICT（SQLite 3.24+） ​

基本语法 ​

实战示例 ​

DO NOTHING —— 冲突时什么都不做 ​

UPSERT vs INSERT OR REPLACE ​

本章小结 ​

第四章数据增删改（DML）

4.1 插入数据（INSERT）

单行插入

多行插入

INSERT OR REPLACE

INSERT OR IGNORE

从查询结果插入

冲突处理策略一览

4.2 更新数据（UPDATE）

基本语法

同时更新多列

基于表达式更新

用子查询更新

UPDATE 与 LIMIT

⚠️ 危险操作提醒

4.3 删除数据（DELETE）

基本语法

带条件的删除

清空表的三种方式对比

软删除（推荐实践）

4.4 UPSERT —— INSERT ... ON CONFLICT（SQLite 3.24+）

基本语法

实战示例

DO NOTHING —— 冲突时什么都不做

UPSERT vs INSERT OR REPLACE

本章小结