附录
附录 A：常用 SQL 语句速查表
DDL（数据定义）
sql
-- 创建表
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER CHECK (age > 0),
bio TEXT DEFAULT ''
);
-- 创建 STRICT 表（3.37+）
CREATE TABLE configs (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
) STRICT;
-- 修改表
ALTER TABLE users RENAME TO members;
ALTER TABLE users RENAME COLUMN bio TO biography;
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users DROP COLUMN bio; -- 3.35+
-- 删除表
DROP TABLE IF EXISTS users;
-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_city_age ON users(city, age); -- 复合索引
CREATE INDEX idx_active ON users(name) WHERE status = 'active'; -- 部分索引
-- 删除索引
DROP INDEX IF EXISTS idx_users_email;DML（数据操作）
sql
-- 插入
INSERT INTO users (name, email, age) VALUES ('张三', 'test@test.com', 25);
-- 多行插入
INSERT INTO users (name, email, age) VALUES
('李四', 'li@test.com', 30),
('王五', 'wang@test.com', 22);
-- 冲突处理
INSERT OR IGNORE INTO users (name, email) VALUES ('张三', 'test@test.com');
INSERT OR REPLACE INTO users (name, email) VALUES ('张三', 'new@test.com');
-- UPSERT（3.24+）
INSERT INTO users (name, email, age)
VALUES ('张三', 'test@test.com', 26)
ON CONFLICT (email) DO UPDATE SET age = excluded.age;
-- 从查询插入
INSERT INTO vip_users SELECT * FROM users WHERE age > 30;
-- 更新
UPDATE users SET age = 26 WHERE name = '张三';
UPDATE users SET age = age + 1; -- 表达式
-- 删除
DELETE FROM users WHERE id = 5;
DELETE FROM users; -- 删除所有行SELECT（查询）
sql
-- 基础查询
SELECT name, age FROM users WHERE age > 20;
SELECT DISTINCT city FROM users;
SELECT name AS 姓名, age AS 年龄 FROM users;
-- 条件过滤
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE city IN ('北京', '上海');
SELECT * FROM users WHERE email IS NOT NULL;
-- 排序与分页
SELECT * FROM users ORDER BY age DESC, name ASC;
SELECT * FROM users ORDER BY age NULLS LAST; -- 3.30+
SELECT * FROM users LIMIT 10 OFFSET 20;
-- 聚合与分组
SELECT city, COUNT(*) AS cnt, AVG(age) AS avg_age
FROM users
GROUP BY city
HAVING cnt > 5
ORDER BY cnt DESC;
-- 多表联查
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);
-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- CTE
WITH high_spenders AS (
SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
)
SELECT u.name, h.total
FROM users u JOIN high_spenders h ON u.id = h.user_id
WHERE h.total > 1000;
-- 窗口函数（3.25+）
SELECT name, score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM students;事务
sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 回滚
BEGIN;
DELETE FROM users;
ROLLBACK;
-- 存档点
BEGIN;
INSERT INTO users VALUES (1, '张三', 25);
SAVEPOINT sp1;
INSERT INTO users VALUES (2, '李四', 30);
ROLLBACK TO sp1; -- 只回滚到 sp1
COMMIT;其他
sql
-- 附加数据库
ATTACH DATABASE 'other.db' AS other;
DETACH DATABASE other;
-- FTS5 全文搜索
CREATE VIRTUAL TABLE docs USING fts5(title, content);
SELECT * FROM docs WHERE docs MATCH 'SQLite AND 教程';
SELECT highlight(docs, 0, '<b>', '</b>') FROM docs WHERE docs MATCH 'SQLite';
-- JSON（3.9+）
SELECT json_extract(data, '$.name') FROM events;
SELECT data ->> '$.email' FROM users; -- 3.38+
UPDATE events SET data = json_set(data, '$.status', 'done');
-- VACUUM
VACUUM;
VACUUM INTO 'compact.db'; -- 3.27+附录 B：常用 PRAGMA 配置说明
性能与安全
| PRAGMA | 默认值 | 推荐值 | 说明 |
|---|---|---|---|
journal_mode | delete | WAL | WAL 模式：读写并发、写性能更好 |
synchronous | full（DELETE）/ full（WAL） | NORMAL | WAL 下 NORMAL 已足够安全，速度更快 |
busy_timeout | 0 | 5000 | 遇到锁时等待 5 秒再报错，避免频繁 database is locked |
foreign_keys | OFF | ON | 每次连接都要设，否则外键约束不生效 |
cache_size | -2000（2 MB） | -64000（64 MB） | 更大的页缓存减少磁盘 I/O |
mmap_size | 0 | 268435456（256 MB） | 内存映射 I/O，大文件读取提速明显 |
temp_store | DEFAULT | MEMORY | 临时表和排序存内存，加速 ORDER BY |
诊断与维护
| PRAGMA | 说明 | 用法 |
|---|---|---|
integrity_check | 完整性检查（慢但全面） | PRAGMA integrity_check; → 正常返回 ok |
quick_check | 快速完整性检查（不含索引） | PRAGMA quick_check; |
page_size | 查看页大小 | PRAGMA page_size; → 4096 |
page_count | 查看总页数 | PRAGMA page_count; |
freelist_count | 空闲页数（可 VACUUM 回收） | PRAGMA freelist_count; |
compile_options | 查看 SQLite 编译选项 | PRAGMA compile_options; |
table_info(表名) | 查看表结构 | PRAGMA table_info(users); |
index_list(表名) | 查看表的所有索引 | PRAGMA index_list(users); |
index_info(索引名) | 查看索引包含的列 | PRAGMA index_info(idx_users_email); |
空间回收
| PRAGMA | 说明 |
|---|---|
auto_vacuum = NONE | 不自动回收（默认） |
auto_vacuum = FULL | 每次删除后自动回收空闲页 |
auto_vacuum = INCREMENTAL | 手动调用 incremental_vacuum(N) 回收 N 页 |
wal_autocheckpoint = 1000 | WAL 文件每 1000 页自动 checkpoint |
wal_checkpoint(TRUNCATE) | 手动 checkpoint 并清空 WAL 文件 |
推荐的连接初始化模板
sql
-- 每次打开连接后执行
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;
PRAGMA mmap_size = 268435456;
PRAGMA temp_store = MEMORY;附录 C：SQLite 内置函数参考
字符串函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
length(s) | 字符数 | length('你好世界') | 4 |
upper(s) | 转大写 | upper('hello') | 'HELLO' |
lower(s) | 转小写 | lower('HELLO') | 'hello' |
trim(s) | 去两端空白 | trim(' hi ') | 'hi' |
ltrim(s) | 去左侧空白 | ltrim(' hi') | 'hi' |
rtrim(s) | 去右侧空白 | rtrim('hi ') | 'hi' |
substr(s, pos, len) | 截取子串 | substr('hello', 2, 3) | 'ell' |
replace(s, from, to) | 替换 | replace('abc', 'b', 'X') | 'aXc' |
instr(s, sub) | 查找子串位置 | instr('hello', 'lo') | 4 |
hex(x) | 转十六进制字符串 | hex('AB') | '4142' |
unicode(s) | 首字符 Unicode 码 | unicode('A') | 65 |
char(n1, n2, ...) | Unicode 码转字符 | char(72,73) | 'HI' |
printf(fmt, ...) | 格式化输出 | printf('%.2f', 3.1) | '3.10' |
quote(x) | SQL 字面量引用 | quote('it''s') | 'it''s' |
zeroblob(n) | n 字节零值 BLOB | zeroblob(4) | X'00000000' |
数学函数
| 函数 | 说明 | 版本要求 |
|---|---|---|
abs(x) | 绝对值 | 全版本 |
round(x, n) | 四舍五入到 n 位小数 | 全版本 |
max(a, b, ...) | 多值取最大 | 全版本 |
min(a, b, ...) | 多值取最小 | 全版本 |
random() | 随机 64 位整数 | 全版本 |
ceil(x) / ceiling(x) | 向上取整 | 3.35+ |
floor(x) | 向下取整 | 3.35+ |
trunc(x) | 截断小数 | 3.35+ |
sign(x) | 符号函数（-1/0/1） | 3.39+ |
ln(x) / log(x) | 自然对数 / 以 10 为底的对数 | 3.35+ |
log2(x) | 以 2 为底的对数 | 3.35+ |
pow(x, y) / power(x, y) | 幂运算 | 3.35+ |
sqrt(x) | 平方根 | 3.35+ |
mod(x, y) | 取模 | 3.35+ |
pi() | 圆周率 | 3.35+ |
聚合函数
| 函数 | 说明 | NULL 处理 |
|---|---|---|
COUNT(*) | 计算总行数 | 包含 NULL 行 |
COUNT(col) | 计算非 NULL 行数 | 忽略 NULL |
COUNT(DISTINCT col) | 不重复的非 NULL 值数 | 忽略 NULL |
SUM(col) | 求和 | 忽略 NULL |
AVG(col) | 平均值 | 忽略 NULL |
MAX(col) | 最大值 | 忽略 NULL |
MIN(col) | 最小值 | 忽略 NULL |
GROUP_CONCAT(col, sep) | 拼接为字符串 | 忽略 NULL |
TOTAL(col) | 求和（返回 REAL，空集返回 0.0） | 忽略 NULL |
日期与时间函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
date(t, mod...) | 提取日期 | date('now') | '2024-03-15' |
time(t, mod...) | 提取时间 | time('now') | '14:30:00' |
datetime(t, mod...) | 日期+时间 | datetime('now') | '2024-03-15 14:30:00' |
julianday(t) | 转儒略日 | julianday('now') | 2460385.1042 |
unixepoch(t) | 转 Unix 时间戳（3.38+） | unixepoch('now') | 1710510600 |
strftime(fmt, t) | 自定义格式 | strftime('%Y年%m月', 'now') | '2024年03月' |
常用修饰符（modifier）：
| 修饰符 | 说明 | 示例 |
|---|---|---|
'NNN days' | 加/减天数 | date('now', '+7 days') |
'NNN hours' | 加/减小时 | datetime('now', '-3 hours') |
'NNN months' | 加/减月数 | date('now', '+1 months') |
'NNN years' | 加/减年数 | date('now', '-1 years') |
'start of month' | 月初 | date('now', 'start of month') |
'start of year' | 年初 | date('now', 'start of year') |
'weekday N' | 下一个星期 N | date('now', 'weekday 0') |
'localtime' | 转本地时间 | datetime('now', 'localtime') |
'utc' | 转 UTC 时间 | datetime('now', 'utc') |
条件与类型函数
| 函数 | 说明 | 示例 |
|---|---|---|
iif(cond, t, f) | 三元表达式（3.32+） | iif(age >= 18, '成人', '未成年') |
coalesce(a, b, ...) | 返回第一个非 NULL 值 | coalesce(nickname, name, '匿名') |
nullif(a, b) | a=b 时返回 NULL | nullif(score, 0) |
ifnull(a, b) | a 为 NULL 时返回 b | ifnull(email, '无邮箱') |
typeof(x) | 返回存储类型名 | typeof(42) → 'integer' |
likelihood(x, p) | 查询优化提示 | likelihood(x > 10, 0.9) |
unlikely(x) | 提示条件不太可能为真 | 用于优化器 |
JSON 函数（3.9+）
| 函数 | 说明 |
|---|---|
json_extract(j, path) | 提取值（保留原类型） |
j -> path | 提取为 JSON 值（3.38+） |
j ->> path | 提取为 SQL 值（3.38+） |
json_set(j, path, val) | 设置/覆盖字段 |
json_insert(j, path, val) | 仅在字段不存在时插入 |
json_replace(j, path, val) | 仅在字段已存在时替换 |
json_remove(j, path) | 删除字段 |
json_type(j, path) | 返回 JSON 类型名 |
json_valid(j) | 检查是否为合法 JSON |
json_array(...) | 构造 JSON 数组 |
json_object(k, v, ...) | 构造 JSON 对象 |
json_each(j, path) | 展开数组为行（表值函数） |
json_tree(j, path) | 递归展开 JSON（表值函数） |
json_group_array(val) | 聚合为 JSON 数组 |
json_group_object(k, v) | 聚合为 JSON 对象 |
窗口函数（3.25+）
| 函数 | 说明 |
|---|---|
ROW_NUMBER() | 唯一行号 |
RANK() | 排名（并列跳号） |
DENSE_RANK() | 排名（并列不跳号） |
NTILE(n) | 分成 n 组 |
LAG(col, n, default) | 前 n 行的值 |
LEAD(col, n, default) | 后 n 行的值 |
FIRST_VALUE(col) | 窗口中第一行的值 |
LAST_VALUE(col) | 窗口中最后一行的值 |
NTH_VALUE(col, n) | 窗口中第 n 行的值 |
CUME_DIST() | 累积分布（0~1） |
PERCENT_RANK() | 百分比排名（0~1） |
附录 D：推荐学习资源与官方文档链接
官方资源
| 资源 | 链接 | 说明 |
|---|---|---|
| SQLite 官方首页 | https://www.sqlite.org/ | 权威信息源 |
| SQL 语法文档 | https://www.sqlite.org/lang.html | 完整 SQL 语法参考 |
| 语法图（铁路图） | https://www.sqlite.org/syntaxdiagrams.html | 可视化语法结构 |
| 内置函数列表 | https://www.sqlite.org/lang_corefunc.html | 所有核心函数 |
| JSON 函数文档 | https://www.sqlite.org/json1.html | JSON 扩展详情 |
| FTS5 文档 | https://www.sqlite.org/fts5.html | 全文搜索参考 |
| 窗口函数文档 | https://www.sqlite.org/windowfunctions.html | 窗口函数参考 |
| PRAGMA 列表 | https://www.sqlite.org/pragma.html | 所有 PRAGMA 说明 |
| 版本更新日志 | https://www.sqlite.org/changes.html | 每个版本的新功能 |
| SQLite 限制 | https://www.sqlite.org/limits.html | 各项上限参数 |
工具
| 工具 | 链接 | 说明 |
|---|---|---|
| DB Browser for SQLite | https://sqlitebrowser.org/ | 最流行的 GUI 工具，开源免费 |
| SQLiteStudio | https://sqlitestudio.pl/ | 功能丰富的 GUI，支持插件 |
| Litestream | https://litestream.io/ | SQLite 流式复制到 S3 |
| LiteFS | https://fly.io/docs/litefs/ | 分布式 SQLite（Fly.io） |
| datasette | https://datasette.io/ | 将 SQLite 发布为 Web API |
各语言 SDK
| 语言 | 推荐库 | 链接 |
|---|---|---|
| Python | sqlite3（标准库） | https://docs.python.org/3/library/sqlite3.html |
| Node.js | better-sqlite3 | https://github.com/WiseLibs/better-sqlite3 |
| Node.js | sql.js（Wasm） | https://sql.js.org/ |
| Go | mattn/go-sqlite3 | https://github.com/mattn/go-sqlite3 |
| Go | modernc.org/sqlite | https://pkg.go.dev/modernc.org/sqlite |
| Rust | rusqlite | https://github.com/rusqlite/rusqlite |
| Java | xerial/sqlite-jdbc | https://github.com/xerial/sqlite-jdbc |
| C# | Microsoft.Data.Sqlite | https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/ |
推荐阅读
| 文章 | 说明 |
|---|---|
| SQLite is not a toy database | 打破"SQLite 只是玩具"的误解 |
| Consider SQLite | Western Digital 万亿级 SQLite 实践 |
| Appropriate Uses For SQLite | 官方适用场景指南 |
| 35% Faster Than The Filesystem | 官方基准测试：SQLite 比直接读文件还快 |
┌─────────────────────────────────────────────────┐
│ │
│ "Small. Fast. Reliable. Choose any three." │
│ │
│ — SQLite 座右铭 │
│ │
└─────────────────────────────────────────────────┘