数据库原理与选型 / 系统课时
实现增删改查与搜索
实现增删改查与搜索
本课在现有数据库上完成笔记维护、标签关联、条件筛选、分页和全文搜索,最终形成一组可直接复用的 SQL 操作。
学习目标
- 用事务完成笔记与标签关系的原子写入
- 实现筛选、排序和稳定分页
- 使用 FTS5 建立可维护的全文搜索索引
新增、修改与删除笔记
下面的脚本新增笔记、关联标签、修改内容,并删除一条临时记录。固定使用 id = 3,便于重复验证前先清理现场。
sql
PRAGMA foreign_keys = ON;
BEGIN;
DELETE FROM notes WHERE id = 3;
INSERT INTO notes
(id, title, content, status, created_at, updated_at)
VALUES
(3, '查询计划笔记', '记录 EXPLAIN QUERY PLAN 的用法。', 'active',
'2026-07-30 08:30:00', '2026-07-30 08:30:00');
INSERT INTO note_tags(note_id, tag_id)
SELECT 3, id
FROM tags
WHERE name IN ('sqlite', 'database');
UPDATE notes
SET content = '记录 EXPLAIN QUERY PLAN 与索引优化方法。',
updated_at = '2026-07-30 08:40:00'
WHERE id = 3;
COMMIT;
SELECT id, title, status, updated_at
FROM notes
WHERE id = 3;预期输出:
text
3|查询计划笔记|active|2026-07-30 08:40:00新增笔记和写入关联关系放在同一个事务中,任何一步失败都应回滚,避免出现“笔记存在但标签缺失”的部分成功状态。
条件筛选与排序分页
分页必须有稳定排序。若多个记录的 updated_at 相同,再使用主键 id 作为第二排序键,避免翻页时重复或遗漏。
sql
SELECT
n.id,
n.title,
GROUP_CONCAT(t.name, ',') AS tags
FROM notes AS n
LEFT JOIN note_tags AS nt ON nt.note_id = n.id
LEFT JOIN tags AS t ON t.id = nt.tag_id
WHERE n.status = 'active'
GROUP BY n.id, n.title, n.updated_at
ORDER BY n.updated_at DESC, n.id DESC
LIMIT 2 OFFSET 0;预期输出:
text
3|查询计划笔记|sqlite,database
1|SQLite 外键使用记录|sqlite,databaseGROUP_CONCAT 中标签的顺序未显式指定,因此不同版本可能显示为 database,sqlite;这不影响关联结果。生产接口若要求固定顺序,应在子查询中先排序。
建立全文搜索
FTS5 比在大文本字段上使用 %关键词% 更适合全文检索。以下脚本创建外部内容表和同步触发器,然后重建一次索引。
sql
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
title,
content,
content = 'notes',
content_rowid = 'id'
);
CREATE TRIGGER IF NOT EXISTS notes_ai AFTER INSERT ON notes BEGIN
INSERT INTO notes_fts(rowid, title, content)
VALUES (new.id, new.title, new.content);
END;
CREATE TRIGGER IF NOT EXISTS notes_ad AFTER DELETE ON notes BEGIN
INSERT INTO notes_fts(notes_fts, rowid, title, content)
VALUES ('delete', old.id, old.title, old.content);
END;
CREATE TRIGGER IF NOT EXISTS notes_au AFTER UPDATE ON notes BEGIN
INSERT INTO notes_fts(notes_fts, rowid, title, content)
VALUES ('delete', old.id, old.title, old.content);
INSERT INTO notes_fts(rowid, title, content)
VALUES (new.id, new.title, new.content);
END;
INSERT INTO notes_fts(notes_fts) VALUES ('rebuild');
SELECT rowid, title
FROM notes_fts
WHERE notes_fts MATCH '索引'
ORDER BY rank;预期输出:
text
3|查询计划笔记若 SQLite 构建未启用 FTS5,创建虚拟表时会出现 no such module: fts5。此时需要更换包含 FTS5 的 SQLite 构建,而不是退回到无法利用普通索引的前导通配符搜索。
删除与级联验证
删除笔记时只操作主表,关联表记录由外键级联清理。
sql
PRAGMA foreign_keys = ON;
DELETE FROM notes WHERE id = 3;
SELECT COUNT(*)
FROM note_tags
WHERE note_id = 3;预期输出:
text
0按自己的节奏,确认真正理解。