SQLite 数据库的表结构与增删改查
将来要反复查询和修改的数据，放进数据库。SQLite 的整个库就是一个 .db 文件，要和程序一起搬。
| 存成文件（.txt / .csv） | 存进数据库（.db） | |
|---|---|---|
| 查一条 | 把整个文件读进来，再一行行判断 | 写一句条件，由数据库负责筛 |
| 改一条 | 把整个文件重写一遍 | 只动那一行，其余部分不受影响 |
| 出意外 | 写到一半程序中断，文件就废了；两个人同时改，后保存的盖掉先保存的 | 一次改动要么全部生效，要么全部不生效，不会留下改了一半的状态 |
不需要安装、不需要单独启动服务的数据库，整个库就是磁盘上的一个文件，随 Python 一同提供（标准库 sqlite3）。
双击打不开，用记事本打开是乱码，开头十六个字节写着 SQLite format 3。
代码在 .py 里，数据在 .db 里。代码丢了可以让 AI 重写，数据丢了没有别的办法，所以 .db 最需要备份。
把脚本拷到另一台电脑
程序能跑，一条记录都查不到
两个文件放在一起拷走
数据都在
写死某台电脑上的完整路径
换一台电脑就连不上库，报错说"没有这张表"
Path(__file__).with_name('items.db')
数据库就在脚本旁边，换台机器照样找得到
1 登记借出 2 登记归还 3 查在谁手上 4 列出未归还 5 删除物资 0 退出 请选择：1 物资名称：无线话筒 A 借用人：林晓 已登记： 林晓 借走「无线话筒 A」，时间 2026-05-14 15:20 请选择：1 物资名称：无线话筒 A 借用人：赵鹏 「无线话筒 A」还在 林晓 手上，不能重复借出。 请选择：4 未归还共 2 件： 无线话筒 A — 林晓 — 2026-05-14 15:20 拖线板 — 周敏 — 2026-05-14 15:22
标黄的一行是查数据库得出的，程序自己并不记着谁借了什么。先运行建库脚本，再运行借还登记。
按部署数量算，SQLite 很可能是世界上使用最广的数据库，手机应用的本地数据、浏览器的书签与浏览记录、许多桌面软件的配置与缓存都用它。它的文件格式有完整的公开文档，开发者把长期兼容作为正式承诺，美国国会图书馆也把它列入推荐的数据集长期保存格式。挑存储格式时，除了现在好不好用，还要看十年后还能不能打开。
数据存在表里：一行一条记录，一列一个字段。建表时为每个字段规定类型和约束。
一行是一条记录，一列是一个字段，这一点与表格软件相同。
TEXT 存文字，INTEGER 存整数。类型在建表时定下。SQLite 默认不强制检查，往整数字段里填汉字也能存进去，所以输入要在程序里先校验。
NOT NULL 不允许为空，UNIQUE 不允许重复。写在建表语句里，此后由数据库自己把关。
name TEXT
台账里可以出现两件同名物资，查在谁手上时分不清是哪一件
name TEXT NOT NULL UNIQUE
重名的物资插不进去，由数据库拦下
-- 物资表：一件物资一行。id 是主键，name 不允许重复 CREATE TABLE items ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, note TEXT ) -- 借还流水表：借一次记一行。item_id 指向 items 表的 id CREATE TABLE records ( id INTEGER PRIMARY KEY AUTOINCREMENT, item_id INTEGER NOT NULL, borrower TEXT NOT NULL, borrow_at TEXT NOT NULL, return_at TEXT )
运行建库脚本得到装好表结构和五件示例物资的 items.db。用 DB Browser for SQLite 打开最直观；机房没装的，运行看表脚本，同样能把两张表原样打印出来。
主键唯一标识一条记录，同一张表里绝不重复。有了主键，别的地方才能准确指认某一条记录。
唯一标识一条记录的字段。物资可以重名，借用人可以同名，id 一定不同，就像全校可能有三个张伟，学号只有一个。
新增一行时由数据库给号，不必自己想编号，也不会重复。
两行内容完全相同
再也分不开，改一条会连带改到另一条
id INTEGER PRIMARY KEY AUTOINCREMENT
每行编号不同，按 id 改只动一行
流水表不写物资名称，只存物资编号 item_id，指向物资表的主键。物资改名只改一处，所有借还记录跟着对。
数据分成多张表之后，靠一个字段互相指认。
用来指向另一张表主键的字段，本例是 records.item_id。
查询时把两张表按编号对起来，一句话同时取出物资名称与借用人。
一张表，每条流水都写"无线话筒 A"
改名要改几十行，漏掉几条就对不上
records.item_id = 1
改名只改 items 表里的一行
SQL 只做增、删、改、查四类事。本章不必记语法，能读懂一句 SQL 动哪张表、动哪几行就够了。
结构化查询语言，与数据库沟通的语言。
SQL 要做的四类事。都是固定句式，换掉表名和条件就能用在别处。
SQL 里先写问号，实际的值由后面那个括号提供。字段个数与问号个数必须一一对应，对不上会立刻报错。
-- 增：登记借出，往流水表插一行 INSERT INTO records (item_id, borrower, borrow_at) VALUES (?, ?, ?) -- 查：这件物资现在在谁手上 SELECT records.borrower, records.borrow_at FROM records JOIN items ON items.id = records.item_id WHERE items.name = ? AND records.return_at IS NULL -- 改：登记归还，把归还时间填上 UPDATE records SET return_at = ? WHERE item_id = ? AND return_at IS NULL -- 删：把一件物资从台账删掉 DELETE FROM items WHERE name = ?
把借用人的名字直接接进 SQL
借用人输入一段 SQL，就会被当成命令执行，这叫 SQL 注入
VALUES (?, ?, ?)
输入的内容只当作值，不会被执行
不 JOIN 物资表
只能查到 item_id 这个数字，看不出是哪件物资
JOIN items ON items.id = records.item_id
一句话取出物资名称和借用人
每按一次按钮，下方显示执行的 SQL 和它动了哪几行。点「删除这件物资」，可以看到 13.7 讲的孤儿记录。
WHERE 后面写筛选条件，数据库只处理符合条件的行。UPDATE 和 DELETE 漏掉 WHERE，动的是整张表，而且没有撤销。
WHERE 后面跟筛选条件，数据库据此只交回、只改动符合条件的记录。
字段没有填值。用 IS NULL 判断；查未归还时漏掉这个条件，已还回来的也会被算进去。
DELETE FROM items
整张表被清空，语句完全合法，数据库不拦，屏幕上没有提示
先用同样的条件 SELECT，看命中几行
只动该动的行。这与第 10 章的预演是同一个动作
归还时间和是否归还各记一处
两处可能对不上，要同时维护
return_at 不写 NOT NULL，空就是没还
同一件事只记在一处
改动执行 commit() 之后才写进文件。删掉还被流水引用的物资，会留下查不出来的孤儿记录。这两种问题都不报错。
把一组改动当作一个整体：要么全部生效，要么全部不生效。
conn.commit() 把事务里的改动真正写进 .db 文件。只查询不修改时不需要提交。
表与表之间的数据互相对得上。本章的代码没有防这个问题。
指向的那一行已经被删除的记录。JOIN 时找不到对应行，查询结果里就没有这条记录。
查询函数正常
修改函数不生效，没有任何报错
并打印一行中文结果，说明改了什么、影响了几条
打印的条数是能察觉漏提交的线索
只看屏幕上的提示
关掉再打开，数据不见了
改一条，关掉程序，重新打开再查一次
数据还在，才说明提交写全了
提问分两步：第一步只要表结构，不要任何代码；读懂并确认之后，第二步才让 AI 按这份结构写代码。
"帮我做一个借还登记工具"
表结构藏在代码里，跑起来才发现字段不合适，返工要连数据一起搬
先要表结构，确认后再要代码
字段在动手之前就定下来
约束没有单独成行
AI 不等确认就把表结构和代码一起交出来，注意力被代码吸走
【不要写任何 Python 代码】
只拿到字段表、建表语句和分表理由
我要做一个社团物资借还登记工具，用 SQLite 存数据。 使用场景：登记某件物资被谁借走、登记归还、查某件物资现在在谁手上、 列出所有还没还回来的、把报废的物资从台账里删掉。 请先只给我表结构：需要几张表、每张表有哪些字段、每个字段是什么类型、 哪个是主键、哪些字段允许为空、哪个字段用来关联另一张表。 用表格列出来，再附上建表的 SQL 语句。 【不要写任何 Python 代码】我确认之后再让你写。 另外用一段中文说明：为什么这样分表，而不是全部塞进一张表； 以及"还没归还"这件事，你打算用哪个字段来表示。
表结构我已确认，就按上面那一版，不要再调整字段。 请写 Python 脚本，用标准库 sqlite3，不要引入任何需要 pip 安装的库。 【要求】 ① 五个功能各写成一个独立函数：登记借出、登记归还、查在谁手上、列出未归还、删除物资； ② 数据库文件名 items.db，按脚本自身所在的位置查找，不存在时自动建库建表； ③ 查询一律用问号占位符传参，不要用字符串拼接拼 SQL； ④ 每一次改动之后都要提交，并打印一行中文结果，说明改了什么、影响了几条； ⑤ 一件物资已经借出且没有归还时，不允许重复借出，打印提示告知在谁手上； ⑥ 最后写一段简单的命令行菜单，输入数字选功能。 每一段代码前加一行中文注释。
现在要加一个功能：查某个人一共借了哪些东西，还没还的排在前面。 【不要改动】已有的表结构与已经写好的五个函数。 新功能写成一个独立的函数，通过 SQL 查询实现，不要把整张表读进来再用 Python 筛。 如果这个功能确实需要新增字段才能做到，请先说明需要加什么、为什么， 等我确认之后再写代码。 只给出新增的那几行。
| 提示词中的规定 | 缺少时的表现 | 见 |
|---|---|---|
| 先只给表结构，不要写任何 Python 代码 | 表结构定错，后面每个功能都要返工，数据也要跟着搬 | 13.8 |
| 说明为什么这样分表，用哪个字段表示未归还 | 分表理由和空值的用法没想清楚就动手 | 13.4、13.6 |
| 按脚本自身所在的位置查找 items.db | 换台机器就连不上库 | 13.1 |
| 一律用问号占位符传参 | 使用者输入的内容被当成 SQL 执行 | 13.5 |
| 每次改动之后都要提交，并打印影响了几条 | 修改不生效，不报错、不留痕 | 13.7 |
| 用标准库 sqlite3，不引入需要安装的库 | AI 常用功能更全的数据库工具库，要先安装，机房网络下可能装不上 | 13.1 |
发提示词一，只要表结构。出现 Python 代码，说明那句约束没起作用，重发一次
产出：表结构设计稿加"预计归还日期"。先自己判断放哪张表：放物资表，一件物资借十次只能记一个日期；放流水表，每次借出各记各的。再问 AI，对照理由
产出：一处经过讨论的修改发提示词二，运行建库脚本。文件夹里出现 items.db，只有表结构和几件示例物资，流水表是空的
产出：items.db登记两条借出，再把同一件物资借一次。第二次应被拒绝，并说明在谁手上。这是判断程序有没有真的去查数据库的最快办法
产出：两条流水与一次拒绝用 DB Browser for SQLite 或看表脚本打开 .db。重点看 records：item_id 是数字，return_at 是空的
产出：表内容截图再看一次表：那一行的 return_at 从空变成时间，其余各行没有变化
产出：改动前后的对照.py 与 .db 一起拷到另一个位置，数据都在；再只拷 .py 试一次，看程序会怎样
产出：两次拷贝的对照先拿表结构、自己改一处，再生成代码。四类操作每做一次，都打开数据库看一眼。
用提示词一拿到表结构，抄进文档。逐个字段问自己：什么类型、能不能为空、为什么放在这张表。答不上来的回去问清楚
产出：字段表加一个字段，或把某个字段换一张表放。改动不必高明，但要说得出理由。这一处是验收项
产出：一处修改与一句理由用提示词二生成代码，建库并录入至少六条数据。已归还和未归还的都要有，否则查询功能验不出来
产出：.db 与六条以上记录增、查、改、删各一次。每次操作之后打开数据库，确认表里确实变了，不要只看屏幕上的提示
产出：四张操作截图关掉程序，重新打开再查一次。数据不见了，回去检查每个修改函数末尾有没有提交
产出：数据留存的验证把 .py 与 .db 一起发给同组同学，请他在自己的机器上做一次增、一次查，确认路径没有写死
产出：一行互测记录| 表名 | 字段 | 类型 | 可空 | 主键 / 关联 |
|---|---|---|---|---|
| items | id | INTEGER | 否 | 主键，自动编号 |
| items | name | TEXT | 否，不允许重复 | |
| items | note | TEXT | 是 | |
| records | id | INTEGER | 否 | 主键，自动编号 |
| records | item_id | INTEGER | 否 | 关联 items.id |
| records | borrower | TEXT | 否 | |
| records | borrow_at | TEXT | 否 | |
| records | return_at | TEXT | 是，空表示未归还 |
以社团物资借还为例，换成自己题目的表和字段。标黄的两行分别是关联字段和表示未归还的空值。
先按现象在下表找到对应的一行，再照处理一栏修改。
| 现象 | 原因 | 处理 |
|---|---|---|
| 程序说登记成功，关掉再开数据不见了；或查询正常、修改不生效 | 漏掉了 conn.commit()，或只在部分函数里提交 | 每一处改动之后补上提交，查询语句不需要 |
database is locked | 另一个程序正开着这个库，多半是 DB Browser 没关 | 关掉查看工具，或在其中点一下写入更改，再运行脚本 |
no such table: items | 库还没建，或连到了另一个目录下的空库 | 先运行建库脚本；确认路径用的是脚本自身所在位置 |
no such column: 某个字段名 | 表结构改了，旧的 .db 还在，里面仍是老结构 | 删掉旧库重新建；已有数据不能删的，用 ALTER TABLE 加字段 |
UNIQUE constraint failed: items.name | 往有唯一约束的字段里插了重复的值 | 插入之前先查一次，已存在就走另一条分支，不要直接插 |
| 查出来是一串数字，看不出是哪件物资 | 只查了流水表，没有 JOIN 物资表 | 加上 JOIN 把两张表按编号对起来，再取名称字段 |
| 执行一次修改，整张表都被改了 | UPDATE 或 DELETE 漏掉了 WHERE | 改之前先用同样的条件 SELECT 一次，看会命中几行 |
| 拷给同学，程序能跑但一条记录都没有 | 只拷了 .py，没有拷 .db | 两个文件一起拷，并确认程序按自身所在位置找数据库 |
| AI 没等确认就给了完整代码 | "不要写代码"没有单独成行 | 单独成行写【不要写任何 Python 代码】，重发提示词一 |
| 代码里用了需要 pip 安装的库 | AI 选了功能更全的数据库工具库 | 要求只用标准库 sqlite3，重新生成 |
提交四项内容，按七条标准验收；选做一项。
按教师提供的作业模板填写，文档开头注明所选题目；提交渠道见课堂通知，当堂提交。
零代码应用开发 · 第 13 章 连上数据库