小白python入门 - 26. 增删改查与过滤

1. 增删改查是什么？

上一课会了建表（定表头、主键、外键）。本课改的是：表里已经有的那些格子里的数据。

业界常把四件事合称 CRUD：

英文	中文	SQL 动词	人话
Create	增	INSERT	往表里加一行
Read	查	SELECT	按条件把行找出来
Update	改	UPDATE	改已有行的某些列
Delete	删	DELETE	删掉符合条件的行

继续用 Day25 的两张表想问题：

users（人）

id	name	email
1	alice	alice@example.com
2	bob	bob@example.com

ledger（账）

id	user_id	amount	note
1	1	12.5	lunch
2	2	30.0	taxi

• 增：再插入一笔账
• 查：找出金额 ≥ 10 的账
• 改：把 lunch 改成 15 元
• 删：删掉某一笔 snack

本课工具仍是 SQLite + Python sqlite3。多表 JOIN、分组汇总留给下一课；这里把单表的增删改查和 WHERE 过滤 练熟。

---

2. 插入 INSERT（增）

2.1 最常用写法

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

例子：给 users 加一个用户 carol。

INSERT INTO users (name, email) VALUES ('carol', 'carol@example.com');

修改前（users 只有 2 人）：

id	name	email
1	alice	alice@example.com
2	bob	bob@example.com

修改后（多了一行，id 自动变成 3）：

id	name	email
1	alice	alice@example.com
2	bob	bob@example.com
3	carol	carol@example.com

再给 carol 记一笔账：

INSERT INTO ledger (user_id, amount, note) VALUES (3, 8.0, 'coffee');

修改前（ledger 最后两行示意）：

id	user_id	amount	note
1	1	12.5	lunch
2	2	30.0	taxi

修改后（末尾多一行）：

id	user_id	amount	note
1	1	12.5	lunch
2	2	30.0	taxi
3	3	8.0	coffee

• 没写 id：若列是 INTEGER PRIMARY KEY AUTOINCREMENT，数据库会自动给 3、4、5…
• 列顺序要和 VALUES 一一对应。

2.2 用 Python 时务必参数绑定

conn.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("carol", "carol@example.com"),
)

? 是占位符，值放在后面的元组里。不要用 f-string 把用户输入拼进 SQL（防注入，Day24 已提过）。

2.3 插错了会怎样？（实例）

操作	可能结果
name 不填却声明了 NOT NULL	报错，插不进去
邮箱与别人重复（UNIQUE）	报错
ledger.user_id = 999 但用户表没有 999（开了外键）	FOREIGN KEY constraint failed

记住：约束是在保护数据，不是故意刁难你。表内容在报错后保持修改前的样子（该次插入不生效）。

3. 查询 SELECT 入门（查 · 基础）

最小句型：

SELECT 列1, 列2 FROM 表名;

写法	意思	例子
SELECT * FROM users	所有列都要	调试时方便，业务里更推荐写清列名
SELECT name, email FROM users	只要姓名和邮箱	少传无用数据
SELECT name AS 姓名 FROM users	给列起个别名	结果里表头变成「姓名」（可选）

人话：SELECT 决定看哪些列，FROM 决定从哪张表拿。

---

4. 过滤 WHERE（查 · 核心）

只想看「符合条件的行」时，加 WHERE：

SELECT 列... FROM 表名 WHERE 条件;

4.1 比较

运算符	含义	例子
=	等于	user_id = 1
!= 或 <>	不等于	note != 'taxi'
> < >= <=	大小比较	amount >= 10

SELECT id, amount, note FROM ledger WHERE amount >= 10;

表中全部流水（查询前，库里有这些行）：

id	user_id	amount	note
1	1	12.5	lunch
2	2	30.0	taxi
3	1	-5.0	refund
4	1	80.0	book
5	2	9.9	snack

WHERE amount >= 10 查出来（查询后你「看到」的结果）：

id	amount	note
1	12.5	lunch
2	30.0	taxi
4	80.0	book

注意：SELECT 不会改表，表里仍是 5 行；只是结果集变少了。snack（9.9）被滤掉。

4.2 并且 / 或者

写法	含义
AND	两边都要成立
OR	一边成立即可
NOT	取反

SELECT id, amount, note FROM ledger
WHERE user_id = 1 AND amount > 0;

同一张 5 行流水里，这条语句「看到」的结果：

id	amount	note
1	12.5	lunch
4	80.0	book

alice 的 refund（-5.0）因 amount > 0 被排除；bob 的账因 user_id = 1 被排除。

4.3 范围与列表

SELECT * FROM ledger WHERE amount BETWEEN 10 AND 50;
SELECT * FROM ledger WHERE user_id IN (1, 2);

BETWEEN 10 AND 50 结果示例（在上述 5 行上）：

id	amount	note
1	12.5	lunch
2	30.0	taxi

80 太大、9.9 太小、-5 不在范围内，都不会出现。

4.4 模糊匹配 LIKE

通配符	含义	例子
%	任意多字符	note LIKE '%a%' → 备注里含字母 a
_	恰好一个字符	name LIKE '_ob' → 三字母且后两字是 ob

SELECT id, note FROM ledger WHERE note LIKE '%a%';

结果示例：

id	note
2	taxi
5	snack

lunch、refund、book 里没有字母 a，不会出现。

4.5 空值：只能用 IS NULL

假设某行备注还没填：

id	note
6	NULL（空）
7	taxi

SELECT * FROM ledger WHERE note IS NULL;      -- 只能查到 id=6
SELECT * FROM ledger WHERE note IS NOT NULL;  -- 查到有字的行
-- 错误：WHERE note = NULL  （筛不出空备注）

原因：在 SQL 里，NULL 表示「不知道」，任何值与 NULL 用 = 比较结果都不是「真」。

5. 排序与条数 ORDER BY / LIMIT

SELECT id, amount, note FROM ledger ORDER BY amount DESC;

关键字	含义
ASC	升序（从小到大，默认常是升序）
DESC	降序（从大到小）

SELECT id, amount, note FROM ledger
ORDER BY amount DESC
LIMIT 3;

表中金额乱序时「全部行」仍是 5 笔；查询结果变成金额最高的 3 笔：

id	amount	note
4	80.0	book
2	30.0	taxi
1	12.5	lunch

SELECT id, amount, note FROM ledger
ORDER BY id
LIMIT 2 OFFSET 2;

按 id 排好后：第 1、2 行跳过，取第 3、4 行：

id	amount	note
3	-5.0	refund
4	80.0	book

子句	人话
LIMIT n	最多返回 n 行
OFFSET m	先跳过 m 行再开始取

---

6. 修改 UPDATE（改）

UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

6.1 改一个人的邮箱

UPDATE users SET email = 'alice.new@example.com' WHERE name = 'alice';

修改前：

id	name	email
1	alice	alice@example.com
2	bob	bob@example.com

修改后（只有 alice 那一行变了，bob 不动）：

id	name	email
1	alice	alice.new@example.com
2	bob	bob@example.com

6.2 改一笔账的金额和备注

UPDATE ledger SET amount = 15.0, note = 'lunch-fixed' WHERE id = 1;

修改前：

id	user_id	amount	note
1	1	12.5	lunch
2	2	30.0	taxi

修改后（只动 id=1）：

id	user_id	amount	note
1	1	15.0	lunch-fixed
2	2	30.0	taxi

安全铁律：UPDATE 几乎总要带 WHERE

若误写成：

UPDATE ledger SET amount = 0;   -- 没有 WHERE！

修改前：

id	amount
1	15.0
2	30.0
4	80.0

修改后（灾难：每一行都变成 0）：

id	amount
1	0
2	0
4	0

所以：改之前可用同样 WHERE 先 SELECT，确认会影响哪些行，再 UPDATE。

---

7. 删除 DELETE（删）

DELETE FROM 表名 WHERE 条件;

7.1 删掉一笔流水

DELETE FROM ledger WHERE id = 5;

修改前（含 snack）：

id	user_id	amount	note
1	1	15.0	lunch-fixed
2	2	30.0	taxi
3	1	-5.0	refund
4	1	80.0	book
5	2	9.9	snack

修改后（id=5 整行消失，表还在）：

id	user_id	amount	note
1	1	15.0	lunch-fixed
2	2	30.0	taxi
3	1	-5.0	refund
4	1	80.0	book

7.2 DELETE 和 DROP TABLE 不是一回事

语句	删的是什么	修改后
DELETE FROM ledger WHERE id = 5	删一行数据	表还在，少一行
DELETE FROM ledger;（无 WHERE）	删所有行	表还在，0 行
DROP TABLE ledger	删整张表	表名都不存在了

7.3 有外键时，不能随便删「还被记账的人」

bob 在流水里还有 taxi 时：

DELETE FROM users WHERE name = 'bob';

修改前： users 里有 bob；ledger 里还有 user_id=2。
修改后： 往往两边都不变，并报错 FOREIGN KEY constraint failed（开了外键时）。
要先处理/删掉 bob 的流水，再删用户。

---

8. 事务小彩蛋（接 Day24）

• conn.commit() → 确认写入
• 出错或 conn.rollback() → 撤销未提交修改
• 好习惯：先 SELECT 确认 → 再 UPDATE/DELETE → 再 commit

小例子：改错了还没 commit

时刻	你在程序里做的	磁盘上 ledger 里 id=1 的 amount
开始	—	12.5
UPDATE ... amount=999（未 commit）	内存里像改了	多数情况下别人再开连接仍看到 12.5
conn.rollback()	放弃修改	仍是 12.5
或 conn.commit()	确认	变成 999

人话：没 commit 的改动，可以当成「还没盖章」，一 rollback 就回到修改前。

---

9. 实践：一条龙走完 CRUD

9.0 运行环境说明

项目	要求
Python	3.10+（推荐 3.12）；标准库 sqlite3
工作目录	自拟，下文以 ~/python-lab/src/day26 为例
Shell	Bash 兼容（mkdir、cat <<'EOF'）

各系统建议：

系统	建议
Linux / macOS	系统终端 + python3
Windows	推荐 Cygwin（或 WSL）

mkdir -p ~/python-lab/src/day26
cd ~/python-lab/src/day26
python3 --version

9.1 一键生成并运行

cat > crud_demo.py << 'EOF'
# Day26: CRUD + WHERE / ORDER / LIMIT
import sqlite3
from pathlib import Path

DB = Path(__file__).with_name("shop.db")


def show(title, rows):
    print(f"--- {title} ---")
    for row in rows:
        print(row)


def main():
    if DB.exists():
        DB.unlink()
    conn = sqlite3.connect(DB)
    conn.execute("PRAGMA foreign_keys = ON")

    conn.executescript(
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE
        );
        CREATE TABLE ledger (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            amount REAL NOT NULL,
            note TEXT,
            FOREIGN KEY (user_id) REFERENCES users(id)
        );
        """
    )

    conn.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        ("alice", "alice@example.com"),
    )
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        ("bob", "bob@example.com"),
    )
    for row in [
        (1, 12.5, "lunch"),
        (2, 30.0, "taxi"),
        (1, -5.0, "refund"),
        (1, 80.0, "book"),
        (2, 9.9, "snack"),
    ]:
        conn.execute(
            "INSERT INTO ledger (user_id, amount, note) VALUES (?, ?, ?)",
            row,
        )
    conn.commit()
    show(
        "after insert: all ledger",
        conn.execute(
            "SELECT id, user_id, amount, note FROM ledger ORDER BY id"
        ).fetchall(),
    )

    show(
        "WHERE amount >= 10",
        conn.execute(
            "SELECT id, user_id, amount, note FROM ledger WHERE amount >= ?",
            (10,),
        ).fetchall(),
    )
    show(
        "WHERE user_id = 1 AND amount > 0",
        conn.execute(
            "SELECT id, amount, note FROM ledger "
            "WHERE user_id = ? AND amount > ?",
            (1, 0),
        ).fetchall(),
    )
    show(
        "WHERE note LIKE '%a%'",
        conn.execute(
            "SELECT id, note FROM ledger WHERE note LIKE ?",
            ("%a%",),
        ).fetchall(),
    )

    show(
        "ORDER BY amount DESC LIMIT 3",
        conn.execute(
            "SELECT id, amount, note FROM ledger "
            "ORDER BY amount DESC LIMIT 3"
        ).fetchall(),
    )
    show(
        "LIMIT 2 OFFSET 2",
        conn.execute(
            "SELECT id, amount, note FROM ledger "
            "ORDER BY id LIMIT 2 OFFSET 2"
        ).fetchall(),
    )

    conn.execute(
        "UPDATE users SET email = ? WHERE name = ?",
        ("alice.new@example.com", "alice"),
    )
    conn.execute(
        "UPDATE ledger SET amount = ?, note = ? WHERE id = ?",
        (15.0, "lunch-fixed", 1),
    )
    conn.commit()
    show(
        "after update: alice + ledger id=1",
        conn.execute(
            "SELECT id, name, email FROM users WHERE name = ?",
            ("alice",),
        ).fetchall()
        + conn.execute(
            "SELECT id, amount, note FROM ledger WHERE id = ?",
            (1,),
        ).fetchall(),
    )

    conn.execute("DELETE FROM ledger WHERE id = ?", (5,))
    conn.commit()
    show(
        "after delete id=5: all ledger",
        conn.execute(
            "SELECT id, user_id, amount, note FROM ledger ORDER BY id"
        ).fetchall(),
    )

    print("--- delete bob while ledger exists ---")
    try:
        conn.execute("DELETE FROM users WHERE name = ?", ("bob",))
        conn.commit()
        print("unexpected success")
    except sqlite3.IntegrityError as e:
        print("IntegrityError:", e)

    conn.close()


if __name__ == "__main__":
    main()
EOF

python3 crud_demo.py

9.2 实测输出

--- after insert: all ledger ---
(1, 1, 12.5, 'lunch')
(2, 2, 30.0, 'taxi')
(3, 1, -5.0, 'refund')
(4, 1, 80.0, 'book')
(5, 2, 9.9, 'snack')
--- WHERE amount >= 10 ---
(1, 1, 12.5, 'lunch')
(2, 2, 30.0, 'taxi')
(4, 1, 80.0, 'book')
--- WHERE user_id = 1 AND amount > 0 ---
(1, 12.5, 'lunch')
(4, 80.0, 'book')
--- WHERE note LIKE '%a%' ---
(2, 'taxi')
(5, 'snack')
--- ORDER BY amount DESC LIMIT 3 ---
(4, 80.0, 'book')
(2, 30.0, 'taxi')
(1, 12.5, 'lunch')
--- LIMIT 2 OFFSET 2 ---
(3, -5.0, 'refund')
(4, 80.0, 'book')
--- after update: alice + ledger id=1 ---
(1, 'alice', 'alice.new@example.com')
(1, 15.0, 'lunch-fixed')
--- after delete id=5: all ledger ---
(1, 1, 15.0, 'lunch-fixed')
(2, 2, 30.0, 'taxi')
(3, 1, -5.0, 'refund')
(4, 1, 80.0, 'book')
--- delete bob while ledger exists ---
IntegrityError: FOREIGN KEY constraint failed

---

总结

• CRUD = INSERT / SELECT / UPDATE / DELETE。
• WHERE 决定动哪些行；改、删时漏写 WHERE 等于动整表，非常危险。
• 过滤常用：比较、AND/OR、IN/BETWEEN、LIKE、IS NULL。
• ORDER BY 排序，LIMIT / OFFSET 控制条数与分页直觉。
• DELETE 删行，DROP TABLE 删表；有外键时可能无法直接删「还被引用」的父行。
• Python 里改完要 commit；值用 ? 绑定，不要拼 SQL。
• 下一课：连接、聚合与子查询。

---

小练笔

题 1

执行 UPDATE ledger SET amount = 0;（没有 WHERE）会怎样？

A. 只改 id 最小的一行
B. 所有行的 amount 都变成 0
C. 语法错误，不会执行
D. 只改主键为 0 的行

题 2

写出 SQL：从 ledger 中查出 金额大于等于 10 的 id, amount, note。

题 3

判断：WHERE note = NULL 可以正确找出备注为空的行。

（对 / 错）

题 4

简答：DELETE FROM users WHERE id = 1 和 DROP TABLE users 有什么区别？

题 5（可选实践）

在本课 day26 目录、已跑过 crud_demo.py 的 shop.db 上：
再插入用户 carol，给她加一笔金额 8.0、备注 coffee，然后查出 name = 'carol' 的用户行。写出命令与关键输出。

---

小练笔参考答案

题 1

B
没有 WHERE 的 UPDATE 会更新表中所有行。

题 2

SELECT id, amount, note FROM ledger WHERE amount >= 10;

题 3

错
应使用 WHERE note IS NULL（或 IS NOT NULL）。

题 4

• DELETE ... WHERE：删掉符合条件的数据行，表结构还在。
• DROP TABLE：删掉整张表（结构和所有数据）。

题 5（示例）

在 day26 目录：

python3 - <<'PY'
import sqlite3
conn = sqlite3.connect('shop.db')
conn.execute('PRAGMA foreign_keys = ON')
conn.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ('carol', 'carol@example.com'),
)
uid = conn.execute(
    "SELECT id FROM users WHERE name = ?", ('carol',)
).fetchone()[0]
conn.execute(
    'INSERT INTO ledger (user_id, amount, note) VALUES (?, ?, ?)',
    (uid, 8.0, 'coffee'),
)
conn.commit()
print(conn.execute(
    'SELECT id, name, email FROM users WHERE name = ?', ('carol',)
).fetchall())
print(conn.execute(
    'SELECT id, user_id, amount, note FROM ledger WHERE user_id = ?',
    (uid,),
).fetchall())
conn.close()
PY

在「刚跑完本课 demo、未删库」时，用户行类似：

[(3, 'carol', 'carol@example.com')]

流水会多出一行 user_id=3, amount=8.0, note='coffee'（具体 id 以你库中为准）。