数据库原理与选型 / 系统课时

实现增删改查与搜索

实现增删改查与搜索

本课在现有数据库上完成笔记维护、标签关联、条件筛选、分页和全文搜索,最终形成一组可直接复用的 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,database

GROUP_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
按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

实现增删改查与搜索

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通