Article

关系型数据库SQLite

更新于：2026-07-16

第一章：SQLite 基础入门

1.1 SQLite 简介与特点

概念名称	说明	注意事项
嵌入式数据库	SQLite 是一个无服务器、零配置、嵌入式的关系型数据库引擎，数据库以单个文件形式存储	不支持多用户并发写入高负载场景
无独立服务进程	不需要启动数据库服务，应用程序直接读写数据库文件	适合移动端、桌面应用、测试环境
ACID 兼容	支持原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）	在 WAL 模式下并发读性能更优
跨平台	支持 Windows、Linux、macOS、Android、iOS 等主流操作系统	数据库文件可在不同平台间直接迁移
公共领域许可	SQLite 源码属于公共领域（Public Domain），可自由用于商业和开源项目	无需担心许可证合规问题
标准 SQL 支持	支持大部分 SQL-92 标准，包括事务、触发器、视图、复合查询等	不支持 RIGHT JOIN 和 FULL OUTER JOIN

1.2 安装与环境配置

步骤名称	操作细节	注意事项
Windows 安装	1. 访问 https://www.sqlite.org/download.html 2. 下载 “Precompiled Binaries for Windows” 中的 `sqlite-tools-win-x64.zip` 3. 解压后将 `sqlite3.exe` 所在目录加入系统 PATH	若仅需使用命令行，无需安装额外依赖
macOS 安装	通常已预装；若未安装，可通过 Homebrew 执行：`brew install sqlite`	验证版本：`sqlite3 --version`
Linux 安装	Ubuntu/Debian: `sudo apt install sqlite3 sqlite3-dev` CentOS/RHEL: `sudo yum install sqlite sqlite-devel`	开发时如需编译 C 程序，需安装 `-dev` 或 `-devel` 包
验证安装	在终端执行：`sqlite3 --version`	应显示版本号，如 3.45.0
环境变量配置	将 `sqlite3` 可执行文件所在目录加入 PATH，确保全局可调用	修改 PATH 后需重启终端或执行 `source ~/.bashrc`

1.3 命令行工具基本使用

命令/操作名称	语法 / 使用方式	用途	代码示例	注意事项
启动 SQLite CLI	`sqlite3 [数据库文件名]`	打开或创建数据库文件并进入交互模式	`sqlite3 test.db`	若文件不存在则自动创建
退出命令行	`.exit` 或 `.quit`	退出 SQLite 命令行工具	`.exit`	也可使用 Ctrl+D（Linux/macOS）
执行 SQL 语句	直接输入 SQL 语句，以分号结尾	执行任意合法 SQL	`CREATE TABLE t(id INTEGER);` `SELECT * FROM t;`	必须以分号 `;` 结束，否则视为语句未完成
查看帮助	`.help`	显示所有点命令（dot commands）说明	`.help`	点命令不以分号结尾
显示表列表	`.tables`	列出当前数据库中所有表名	`.tables`	仅显示用户表，不含系统表
查看表结构	`.schema [表名]`	显示建表语句	`.schema users`	若省略表名，则显示所有表的 schema
开启表头显示	`.headers on`	查询结果包含列名	`.headers on`	默认关闭，建议开启以便阅读输出
设置输出模式	`.mode list \| column \| table \| csv \| json`	控制查询结果格式	`.mode column`	常用 `column` 模式对齐列，`csv` 适合导出
执行 SQL 脚本	`.read 文件名`	从文件读取并执行 SQL 语句	`.read init.sql`	文件需包含有效 SQL，每条语句以分号结束

第二章：数据库与表操作

2.1 创建与连接数据库

操作名称	操作细节	用途	代码示例	注意事项
创建新数据库	在命令行执行 `sqlite3` 后跟文件名	创建并打开一个新数据库文件	`sqlite3 myapp.db`	若文件已存在则直接打开；路径可为相对或绝对
连接现有数据库	同上，指定已有 `.db` 或 `.sqlite` 文件	打开已有数据库进行操作	`sqlite3 /path/to/existing.db`	SQLite 不区分扩展名，但建议使用 `.db` 或 `.sqlite`
内存数据库	使用特殊文件名 `:memory:`	创建临时内存数据库（程序退出后销毁）	`sqlite3 :memory:`	适用于测试或临时计算，无法持久化
验证当前数据库	在 CLI 中执行 `.database`	查看当前连接的数据库文件路径	`.database`	显示 main 数据库及其文件路径
自动创建机制	首次写入操作（如建表）触发物理文件创建	延迟创建数据库文件	`CREATE TABLE t(x);` — 此时 `my.db` 才真正生成	空数据库（无表）不会生成物理文件

2.2 创建、修改与删除表

操作名称	语法	用途	代码示例	注意事项
创建表	`CREATE TABLE 表名 (列定义 [, 约束])`	定义新表结构	`CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT NOT NULL);`	列类型为”亲和类型”，非强类型
添加列	`ALTER TABLE 表名 ADD COLUMN 列名类型 [约束]`	向现有表新增一列	`ALTER TABLE users ADD COLUMN email TEXT;`	SQLite 仅支持 ADD COLUMN，不支持 DROP/RENAME COLUMN（旧版本）
重命名表	`ALTER TABLE 旧表名 RENAME TO 新表名`	修改表名称	`ALTER TABLE users RENAME TO accounts;`	所有依赖该表的对象（如视图）可能失效
删除表	`DROP TABLE [IF EXISTS] 表名`	永久删除整张表及其数据	`DROP TABLE IF EXISTS logs;`	无法恢复，谨慎操作；IF EXISTS 避免报错
限制说明	SQLite 的 ALTER TABLE 功能有限	仅支持重命名表和添加列	—	如需复杂修改（如改列类型），需重建表：新建→迁移→删旧

重建表示例（间接修改列）：

CREATE TABLE new_users(id INTEGER, full_name TEXT);
INSERT INTO new_users SELECT id, name FROM users;
DROP TABLE users;
ALTER TABLE new_users RENAME TO users;

2.3 表结构查看与元数据查询

操作名称	语法 / 命令	用途	代码示例	注意事项
查看所有表	`.tables`	列出当前数据库中所有用户表	`.tables`	不显示 `sqlite_` 开头的系统表
查看特定表结构	`.schema 表名`	显示建表 SQL 语句	`.schema products`	若省略表名，显示全部表的 schema
查询表信息（SQL）	`SELECT * FROM sqlite_master WHERE type='table';`	通过 SQL 获取表元数据	`SELECT name, sql FROM sqlite_master WHERE type='table' AND name='users';`	`sqlite_master` 是元数据表，记录所有对象
查询列信息	`PRAGMA table_info(表名);`	获取表的列名、类型、是否为主键等	`PRAGMA table_info(users);`	返回字段：cid, name, type, notnull, dflt_value, pk
查看索引信息	`PRAGMA index_list(表名);`	列出某表上的所有索引	`PRAGMA index_list(users);`	可结合 `PRAGMA index_info(index_name)` 查看索引列
查看外键信息	`PRAGMA foreign_key_list(表名);`	显示表的外键约束定义	`PRAGMA foreign_key_list(orders);`	需确保外键支持已启用（默认关闭）
启用外键支持	`PRAGMA foreign_keys = ON;`	开启外键约束检查	`PRAGMA foreign_keys = ON;`	每次连接需重新设置，除非编译时默认开启

第三章：数据操作语言（DML）

3.1 插入数据（INSERT）

方法名称	语法	用途	代码示例	注意事项
基本插入	`INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);`	向表中插入一行数据	`INSERT INTO users (id, name) VALUES (1, 'Alice');`	列名可省略，但需按建表顺序提供全部值
多行插入	`INSERT INTO 表名 VALUES (...), (...), ...;`	一次插入多行	`INSERT INTO users VALUES (2, 'Bob'), (3, 'Carol');`	SQLite 3.7.11+ 支持此语法
插入默认值	`INSERT INTO 表名 DEFAULT VALUES;`	插入全为默认值的一行	`INSERT INTO logs DEFAULT VALUES;`	表必须所有列均有默认值或允许 NULL
从查询插入	`INSERT INTO 表名 SELECT ...;`	将查询结果插入目标表	`INSERT INTO backup_users SELECT * FROM users WHERE id > 10;`	目标表与 SELECT 列数和类型需兼容
忽略冲突插入	`INSERT OR IGNORE INTO 表名 ...;`	遇主键/唯一约束冲突时跳过	`INSERT OR IGNORE INTO users (id, name) VALUES (1, 'Duplicate');`	不报错，静默忽略冲突行
冲突替换插入	`INSERT OR REPLACE INTO 表名 ...;`	遇冲突时删除旧行并插入新行	`INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Updated Alice');`	等效于”先删后插”，可能触发 DELETE 触发器

3.2 查询数据（SELECT）

方法名称	语法	用途	代码示例	注意事项
全表查询	`SELECT * FROM 表名;`	返回表中所有行和列	`SELECT * FROM users;`	生产环境慎用，性能差且冗余
指定列查询	`SELECT 列1, 列2 FROM 表名;`	仅返回指定列	`SELECT id, name FROM users;`	推荐明确指定列名
条件过滤	`SELECT ... FROM 表名 WHERE 条件;`	按条件筛选行	`SELECT * FROM users WHERE age >= 18;`	支持 AND/OR/NOT、LIKE、IN、BETWEEN 等
去重查询	`SELECT DISTINCT 列 FROM 表名;`	返回唯一值	`SELECT DISTINCT city FROM users;`	对组合列也适用：`DISTINCT col1, col2`
限制结果数量	`SELECT ... LIMIT 数量 [OFFSET 起始];`	分页或限制返回行数	`SELECT * FROM users LIMIT 5 OFFSET 10;`	OFFSET 从 0 开始；常用于分页
排序	`SELECT ... ORDER BY 列 [ASC\|DESC];`	按列排序结果	`SELECT * FROM users ORDER BY name DESC;`	可多列排序：`ORDER BY col1 ASC, col2 DESC`
别名	`SELECT 列 AS 别名 FROM 表名;`	为列或表设置临时名称	`SELECT name AS username FROM users;`	AS 可省略，但建议保留以提高可读性

3.3 更新数据（UPDATE）

方法名称	语法	用途	代码示例	注意事项
基本更新	`UPDATE 表名 SET 列1=值1, 列2=值2 WHERE 条件;`	修改满足条件的行	`UPDATE users SET name='Alice Smith' WHERE id=1;`	必须加 WHERE，否则全表被更新
更新多列	同上，在 SET 子句中逗号分隔多个赋值	同时修改多个字段	`UPDATE users SET age=25, city='Beijing' WHERE id=1;`	所有赋值在同一事务中完成
使用子查询更新	`UPDATE 表名 SET 列=(SELECT ...) WHERE ...;`	根据其他表或聚合结果更新	`UPDATE orders SET total=(SELECT SUM(price) FROM items WHERE items.order_id=orders.id);`	子查询必须返回单值
忽略冲突更新	`UPDATE OR IGNORE 表名 SET ... WHERE ...;`	遇约束冲突时跳过该行	`UPDATE OR IGNORE users SET email='test@example.com' WHERE id=999;`	若 id=999 不存在，无影响；若更新违反 UNIQUE，跳过
替换式更新	`UPDATE OR REPLACE 表名 SET ... WHERE ...;`	遇唯一约束冲突时执行 REPLACE 逻辑	（较少用，通常用 INSERT OR REPLACE）	行为复杂，一般不推荐用于 UPDATE

3.4 删除数据（DELETE）

方法名称	语法	用途	代码示例	注意事项
条件删除	`DELETE FROM 表名 WHERE 条件;`	删除满足条件的行	`DELETE FROM users WHERE id=5;`	必须加 WHERE，否则删除全表数据
清空全表	`DELETE FROM 表名;`	删除表中所有数据	`DELETE FROM logs;`	保留表结构；比 DROP+CREATE 慢，因记录日志
快速清空（TRUNCATE）	使用 VACUUM 或重建表实现	高效清空大表	（无直接 TRUNCATE 命令） `DELETE FROM t; VACUUM;`	SQLite 无 TRUNCATE 语句，DELETE + VACUUM 可释放空间
带子查询删除	`DELETE FROM 表名 WHERE 列 IN (SELECT ...);`	根据子查询结果删除	`DELETE FROM orders WHERE user_id NOT IN (SELECT id FROM users);`	子查询应返回单列值列表
安全删除建议	先 SELECT 验证条件	避免误删	`SELECT * FROM users WHERE status='inactive';` — 确认后再 DELETE	强烈建议在 DELETE 前先用相同 WHERE 做 SELECT 验证

第四章：查询进阶与函数

4.1 条件查询与排序（WHERE, ORDER BY）

操作名称	语法	用途	代码示例	注意事项
基本条件过滤	`WHERE 列比较运算符值`	筛选满足条件的行	`SELECT * FROM products WHERE price > 100;`	支持 `=`, `!=`, `<`, `<=`, `>`, `>=`
多条件组合	`WHERE 条件1 AND/OR 条件2`	组合多个筛选条件	`SELECT * FROM users WHERE age >= 18 AND city = 'Shanghai';`	注意 AND 优先级高于 OR，必要时用括号
范围查询	`WHERE 列 BETWEEN 值1 AND 值2`	查询某范围内的值	`SELECT * FROM orders WHERE amount BETWEEN 50 AND 200;`	包含边界值
成员判断	`WHERE 列 IN (值1, 值2, ...)`	判断列值是否在给定集合中	`SELECT * FROM users WHERE role IN ('admin', 'editor');`	可替换为多个 OR，但 IN 更简洁
模糊匹配	`WHERE 列 LIKE '模式'`	字符串模糊匹配	`SELECT * FROM users WHERE name LIKE 'A%';`	`%` 表示任意字符（包括空），`_` 表示单个字符
空值判断	`WHERE 列 IS NULL / IS NOT NULL`	判断是否为空	`SELECT * FROM users WHERE email IS NULL;`	不能用 `= NULL`，必须用 `IS NULL`
升序/降序排序	`ORDER BY 列 [ASC \| DESC]`	对结果集排序	`SELECT * FROM products ORDER BY price DESC;`	默认 ASC（升序）；可多列排序
按表达式排序	`ORDER BY 表达式`	按计算结果排序	`SELECT name, salary*12 AS annual FROM employees ORDER BY annual DESC;`	表达式或别名可用于 ORDER BY

4.2 聚合函数与分组（GROUP BY, HAVING）

操作名称	语法	用途	代码示例	注意事项
计数	`COUNT(*)` 或 `COUNT(列)`	统计行数	`SELECT COUNT(*) FROM users;`	`COUNT(*)` 包含 NULL 行；`COUNT(列)` 忽略 NULL
求和	`SUM(列)`	对数值列求和	`SELECT SUM(amount) FROM orders;`	非数值列返回 0 或 NULL
平均值	`AVG(列)`	计算平均值	`SELECT AVG(score) FROM exams;`	自动忽略 NULL 值
最大/最小值	`MAX(列)` / `MIN(列)`	获取最大或最小值	`SELECT MAX(price), MIN(price) FROM products;`	适用于数值、日期、字符串
分组统计	`SELECT 聚合函数 FROM 表 GROUP BY 列`	按列分组后聚合	`SELECT city, COUNT(*) FROM users GROUP BY city;`	SELECT 中非聚合列必须出现在 GROUP BY 中
分组后过滤	`HAVING 聚合条件`	对分组结果进行筛选	`SELECT city, COUNT() FROM users GROUP BY city HAVING COUNT() > 5;`	WHERE 用于行过滤，HAVING 用于组过滤
多列分组	`GROUP BY 列1, 列2`	按多列组合分组	`SELECT dept, role, AVG(salary) FROM staff GROUP BY dept, role;`	分组粒度更细

4.3 连接查询（JOIN）

操作名称	语法	用途	代码示例	注意事项
内连接（INNER JOIN）	`SELECT ... FROM A INNER JOIN B ON A.id = B.a_id`	返回两表匹配的行	`SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id;`	可简写为 JOIN，默认即 INNER
左外连接（LEFT JOIN）	`SELECT ... FROM A LEFT JOIN B ON A.id = B.a_id`	返回左表全部行，右表无匹配则 NULL	`SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id;`	SQLite 不支持 RIGHT/FULL OUTER JOIN
自连接	`SELECT ... FROM 表 A JOIN 表 B ON A.列 = B.列`	同一表内关联（如上下级关系）	`SELECT e.name, m.name AS manager FROM emp e LEFT JOIN emp m ON e.manager_id = m.id;`	需使用别名区分同一表的不同实例
使用 USING	`SELECT ... FROM A JOIN B USING (公共列名)`	简化等值连接写法	`SELECT * FROM orders JOIN customers USING (customer_id);`	要求两表有同名列；结果中该列只出现一次
多表连接	`SELECT ... FROM A JOIN B ON ... JOIN C ON ...`	连接三个及以上表	`SELECT u.name, p.title, o.date FROM users u JOIN orders o ON u.id=o.user_id JOIN products p ON o.product_id=p.id;`	注意连接顺序和性能

4.4 内置函数（字符串、日期、数学等）

函数类别	函数名称	语法	用途	代码示例	注意事项
字符串	LENGTH(str)	`LENGTH(列或字符串)`	返回字符串字节长度	`SELECT LENGTH(name) FROM users;`	UTF-8 下中文字符占 3 字节
字符串	UPPER(str)	`UPPER(列)`	转大写	`SELECT UPPER(name) FROM users;`	仅处理 ASCII 字母，除非编译时启用 ICU
字符串	LOWER(str)	`LOWER(列)`	转小写	`SELECT LOWER(email) FROM users;`	同上
字符串	SUBSTR(str, start, len)	`SUBSTR(列, 起始, 长度)`	截取子串（起始从 1 开始）	`SELECT SUBSTR(phone, 1, 3) FROM contacts;`	起始为负数表示从末尾开始
字符串	REPLACE(str, old, new)	`REPLACE(列, 旧, 新)`	替换子串	`SELECT REPLACE(description, 'old', 'new') FROM items;`	全局替换
日期	DATE(‘now’)	`DATE(表达式)`	获取当前日期（YYYY-MM-DD）	`SELECT DATE('now');`	支持 `'+1 day'`, `'-1 month'` 等修饰符
日期	DATETIME(‘now’)	`DATETIME(表达式)`	获取当前日期时间	`SELECT DATETIME('now', 'localtime');`	`'localtime'` 转为本地时区
日期	STRFTIME(fmt, t)	`STRFTIME('%Y-%m', 'now')`	格式化日期	`SELECT STRFTIME('%Y年%m月', created_at) FROM logs;`	类似 strftime C 函数
数学	ABS(x)	`ABS(数值)`	绝对值	`SELECT ABS(-10);`	—
数学	ROUND(x, d)	`ROUND(数值, 小数位)`	四舍五入	`SELECT ROUND(price, 2) FROM products;`	d 默认为 0
数学	RANDOM()	`RANDOM()`	返回随机整数	`SELECT RANDOM() % 100;` — 生成 0-99 随机数	用于测试或简单随机抽样
类型	TYPEOF(x)	`TYPEOF(列或值)`	返回存储类型（null, integer, real, text, blob）	`SELECT TYPEOF(name) FROM users;`	反映 SQLite 实际存储类型，非声明类型

第五章：约束、索引与事务

5.1 主键、外键与唯一性约束

约束类型	语法 / 定义方式	用途	代码示例	注意事项
主键（PRIMARY KEY）	列定义中添加 `PRIMARY KEY`，或表级约束	唯一标识每一行，隐含 NOT NULL	`CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);`	SQLite 中 `INTEGER PRIMARY KEY` 自动成为 rowid 别名，性能更优
复合主键	`PRIMARY KEY (列1, 列2)`	多列联合唯一标识	`CREATE TABLE order_items(order_id INT, product_id INT, PRIMARY KEY(order_id, product_id));`	不能包含 NULL（因主键隐含 NOT NULL）
唯一性约束（UNIQUE）	列级：`列名类型 UNIQUE` 表级：`UNIQUE (列1, 列2)`	保证列或列组合值唯一	`CREATE TABLE emails(email TEXT UNIQUE);`	允许存在多个 NULL（SQLite 视 NULL 为不相等）
外键约束（FOREIGN KEY）	`FOREIGN KEY (列) REFERENCES 主表(主键列) [ON DELETE/UPDATE 动作]`	维护引用完整性	`CREATE TABLE orders(user_id INT, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);`	默认关闭，需执行 `PRAGMA foreign_keys = ON;` 才生效
外键动作	`ON DELETE SET NULL / CASCADE / RESTRICT / NO ACTION`	定义主表删除时从表行为	`FOREIGN KEY(dept_id) REFERENCES depts(id) ON DELETE SET NULL`	CASCADE 表示级联删除；SET NULL 需从表列允许 NULL
启用外键	`PRAGMA foreign_keys = ON;`	全局开启外键检查	`PRAGMA foreign_keys = ON;`	每次连接数据库后需重新设置（除非编译时默认开启）

5.2 默认值、非空与检查约束

约束类型	语法 / 定义方式	用途	代码示例	注意事项
默认值（DEFAULT）	列定义中添加 `DEFAULT 值`	插入时若未提供值则使用默认值	`CREATE TABLE logs(msg TEXT, created_at TEXT DEFAULT (DATETIME('now')));`	默认值可为常量或函数（如 CURRENT_TIME、RANDOM()）
非空约束（NOT NULL）	列定义中添加 `NOT NULL`	禁止该列为空	`CREATE TABLE users(name TEXT NOT NULL);`	插入或更新时若为 NULL 将报错
检查约束（CHECK）	列级：`CHECK (条件)` 表级：`CHECK (条件)`	限制列值必须满足逻辑条件	`CREATE TABLE products(price REAL CHECK (price > 0));`	条件为布尔表达式；插入/更新时验证
多条件 CHECK	`CHECK (age >= 0 AND age <= 150)`	组合多个验证逻辑	`CREATE TABLE persons(age INT CHECK (age BETWEEN 0 AND 150));`	可引用同一行的其他列
约束命名	`CONSTRAINT 名称约束`	为约束指定名称（便于调试）	`CREATE TABLE t(x INT CONSTRAINT positive_x CHECK (x > 0));`	SQLite 支持但不强制使用；错误信息中会显示名称

5.3 创建与管理索引

操作名称	语法	用途	代码示例	注意事项
创建普通索引	`CREATE INDEX 索引名 ON 表名(列1, 列2);`	加速 WHERE、ORDER BY 查询	`CREATE INDEX idx_users_city ON users(city);`	索引名需唯一；多列索引顺序影响查询效率
创建唯一索引	`CREATE UNIQUE INDEX 索引名 ON 表名(列);`	强制列值唯一并加速查询	`CREATE UNIQUE INDEX idx_emails_email ON emails(email);`	插入重复值时报错
查看索引	`.indexes [表名]` 或 `PRAGMA index_list(表名);`	列出表上的所有索引	`.indexes users`	`.indexes` 为 CLI 命令；PRAGMA 可在 SQL 中使用
删除索引	`DROP INDEX 索引名;`	移除不再需要的索引	`DROP INDEX idx_users_city;`	不影响表数据，仅移除索引结构
部分索引（Partial Index）	`CREATE INDEX ... WHERE 条件`	仅对满足条件的行建索引	`CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';`	SQLite 3.8.0+ 支持；节省空间，提升特定查询性能
索引使用建议	在高频查询的 WHERE、JOIN、ORDER BY 列上建索引	优化查询性能	—	过多索引会降低 INSERT/UPDATE/DELETE 性能；避免在低区分度列（如性别）建索引

5.4 事务控制（BEGIN, COMMIT, ROLLBACK）

操作名称	语法	用途	代码示例	注意事项
开启事务	`BEGIN [DEFERRED \| IMMEDIATE \| EXCLUSIVE];`	显式启动一个事务	`BEGIN IMMEDIATE;`	默认为 DEFERRED（延迟加锁）；IMMEDIATE 立即获取保留锁
提交事务	`COMMIT;`	永久保存事务中所有更改	`COMMIT;`	等价于 `END TRANSACTION;`
回滚事务	`ROLLBACK;`	撤销事务中所有未提交的更改	`ROLLBACK;`	数据恢复到 BEGIN 前状态
自动提交模式	每条 DML 语句自动包裹在独立事务中	默认行为	`INSERT INTO t VALUES (1);` — 自动 COMMIT	性能差，批量操作应显式使用事务
保存点（SAVEPOINT）	`SAVEPOINT 名称; ... ROLLBACK TO 名称; RELEASE 名称;`	在事务内设置回滚点	`SAVEPOINT sp1;` `INSERT INTO t VALUES (2);` `ROLLBACK TO sp1;`	SQLite 3.6.8+ 支持；支持嵌套
WAL 模式与事务	`PRAGMA journal_mode = WAL;`	启用写前日志模式，提升并发读性能	`PRAGMA journal_mode = WAL;`	事务行为不变，但允许多个读 + 一个写并发

批量操作事务示例：

BEGIN;
INSERT INTO logs VALUES ('a');
INSERT INTO logs VALUES ('b');
COMMIT;

第六章：SQLite 命令行高级操作

6.1 导入与导出数据（.import / .output / .dump）

操作名称	语法 / 命令	用途	代码示例	注意事项
导出为 SQL 脚本	`.dump [表名]`	生成可重建数据库的完整 SQL 脚本	`.dump users`	若省略表名，则导出整个数据库；包含表结构和数据
导出查询结果	`.output 文件名` `SELECT ...;` `.output stdout`	将查询结果写入文件	`.output report.txt` `SELECT * FROM sales;` `.output stdout`	默认输出格式受 `.mode` 影响；记得恢复 stdout
导入 CSV/TSV 数据	`.mode csv` 或 `.mode tabs` `.import 文件名表名`	从分隔文件批量导入数据	`.mode csv` `.import data.csv users`	表必须已存在；首行为列名时需跳过或预处理
导出为 CSV	`.headers on` `.mode csv` `.output file.csv` `SELECT * FROM t;`	生成标准 CSV 文件	`.headers on` `.mode csv` `.output users.csv` `SELECT * FROM users;`	推荐用于与其他系统交换数据
备份数据库	`sqlite3 原库.db .dump > backup.sql`	在 shell 中直接导出	`sqlite3 app.db .dump > app_backup.sql`	可跨平台恢复；文本格式便于版本控制
导入注意事项	确保目标表结构与文件列数匹配	避免导入失败	—	`.import` 不自动创建表；不处理引号内换行（简单解析）

6.2 格式化输出与模式切换（.mode / .headers / .width）

操作名称	语法 / 命令	用途	代码示例	注意事项
切换输出模式	`.mode 模式名`	控制查询结果展示格式	`.mode column`	支持：column, list, csv, table, json, line, tabs 等
开启列标题	`.headers on \| off`	是否在结果中显示列名	`.headers on`	默认 off；建议开启以便阅读
设置列宽	`.width 宽度1 宽度2 ...`	手动指定各列显示宽度（仅 column 模式）	`.width 5 15 10` `SELECT id, name, email FROM users;`	超出宽度会截断；负数表示左对齐
表格模式美化	`.mode table`	以 ASCII 表格形式输出	`.mode table` `SELECT * FROM products;`	自动对齐，视觉清晰，适合终端查看
行模式输出	`.mode line`	每列一行，格式：`列名 = 值`	`.mode line` `SELECT * FROM users WHERE id=1;`	适合查看单行详细信息
JSON 输出	`.mode json`	将结果输出为 JSON 数组	`.mode json` `SELECT id, name FROM users LIMIT 2;`	SQLite 3.38.0+ 支持；便于程序解析

6.3 数据库信息查看（.tables / .schema / .database）

操作名称	语法 / 命令	用途	代码示例	注意事项
列出所有表	`.tables [匹配模式]`	显示当前数据库中的用户表	`.tables`	支持通配符：`.tables user%` → 匹配 user 开头的表
查看建表语句	`.schema [表名]`	显示表的 CREATE TABLE 语句	`.schema orders`	若省略表名，显示所有表、索引、触发器的定义
查看数据库路径	`.database`	显示当前连接的数据库文件信息	`.database`	输出 main 和 temp 数据库的文件路径
查看索引	`.indexes [表名]`	列出指定表的所有索引	`.indexes users`	若省略表名，列出所有索引
查看视图定义	`SELECT sql FROM sqlite_master WHERE type='view' AND name='视图名';`	获取视图的 SELECT 语句	`SELECT sql FROM sqlite_master WHERE name='active_users';`	视图定义存储在 `sqlite_master` 表中
查看触发器	`.schema 触发器名` 或查询 `sqlite_master`	显示触发器定义	`SELECT sql FROM sqlite_master WHERE type='trigger';`	`.schema` 也可用于触发器

6.4 执行脚本与批处理（.read / .load）

操作名称	语法 / 命令	用途	代码示例	注意事项
执行 SQL 脚本	`.read 文件名`	从文件读取并执行 SQL 语句	`.read init_db.sql`	文件中每条 SQL 必须以分号 `;` 结尾
加载扩展模块	`.load 文件名 [入口函数]`	动态加载 C 编写的 SQLite 扩展	`.load ./spellfix.so`	需编译为共享库；入口函数默认为 `sqlite3_extension_init`
错误处理	脚本中某条语句失败，默认继续执行	需人工检查日志	—	可在脚本开头加 `.bail on` 使出错即退出
启用错误立即退出	`.bail on`	遇 SQL 错误立即终止	`.bail on` `.read risky_script.sql`	默认 `.bail off`；调试脚本时建议开启
注释支持	SQL 脚本中可使用 `--` 单行注释或 `/* ... */` 多行注释	提高脚本可读性	`-- 创建用户表` `CREATE TABLE users (...);`	CLI 和 `.read` 均支持标准 SQL 注释

批量初始化示例：

创建 init.sql：

CREATE TABLE t(...);
INSERT INTO t VALUES (...);

然后执行 .read init.sql

第七章：性能与维护

7.1 VACUUM 与数据库压缩

操作名称	语法 / 命令	用途	代码示例	注意事项
执行 VACUUM	`VACUUM;`	重建数据库文件，释放未使用空间	`VACUUM;`	会创建临时副本，需足够磁盘空间；耗时较长
指定 VACUUM 到新文件	`VACUUM INTO '新文件路径';`	将压缩后的数据库写入新文件	`VACUUM INTO 'backup_compact.db';`	SQLite 3.27.0+ 支持；原库不变，适合在线备份
自动 VACUUM 模式	`PRAGMA auto_vacuum = [NONE \| FULL \| INCREMENTAL];`	控制删除数据后是否自动回收空间	`PRAGMA auto_vacuum = INCREMENTAL;`	默认为 NONE；FULL 模式在事务提交时自动整理，但仍有碎片；INCREMENTAL 需配合 `PRAGMA incremental_vacuum(n)` 手动触发
查看当前模式	`PRAGMA auto_vacuum;`	查询当前 auto_vacuum 设置	`PRAGMA auto_vacuum;`	返回 0=NONE, 1=FULL, 2=INCREMENTAL
使用建议	定期对频繁 UPDATE/DELETE 的数据库执行 VACUUM	防止数据库文件无限膨胀	—	移动端或嵌入式设备存储有限时尤为重要

7.2 分析与优化查询（EXPLAIN QUERY PLAN）

操作名称	语法 / 命令	用途	代码示例	注意事项
查看执行计划	`EXPLAIN QUERY PLAN SELECT ...;`	显示 SQLite 如何执行查询	`EXPLAIN QUERY PLAN SELECT * FROM users WHERE id=1;`	输出包含搜索方式（SCAN/TABLE/INDEX）、是否使用索引等
计划解读关键词	`SEARCH TABLE ... USING INDEX ...` `SCAN TABLE ...`	判断是否高效使用索引	`SEARCH TABLE users USING INTEGER PRIMARY KEY (rowid=?)`	”SEARCH” 表示使用索引；“SCAN” 表示全表扫描，应避免
强制忽略索引	`SELECT * FROM t INDEXED BY 索引名 WHERE ...`	指定使用某索引	`SELECT * FROM users INDEXED BY idx_city WHERE city='BJ';`	调试时验证索引效果；一般不用于生产
统计信息更新	`ANALYZE;`	收集表和索引的统计信息供优化器使用	`ANALYZE;`	首次建索引后建议执行；统计信息存于 `sqlite_stat1` 表
查看统计信息	`SELECT * FROM sqlite_stat1;`	检查优化器使用的采样数据	`SELECT * FROM sqlite_stat1;`	若表数据分布变化大，需重新 ANALYZE
优化建议	对 WHERE、JOIN、ORDER BY 中的列建立合适索引	减少全表扫描	—	复合索引顺序应匹配查询条件顺序

7.3 WAL 模式与并发控制

操作名称	语法 / 命令	用途	代码示例	注意事项
启用 WAL 模式	`PRAGMA journal_mode = WAL;`	切换到写前日志（Write-Ahead Logging）模式	`PRAGMA journal_mode = WAL;`	返回 “wal” 表示成功；提升读并发性能
查看当前日志模式	`PRAGMA journal_mode;`	查询当前 journal mode	`PRAGMA journal_mode;`	可能返回 delete（默认）、wal、memory 等
WAL 优势	允许多个 reader + 一个 writer 并发	避免写操作阻塞读	—	传统 rollback 模式下写会锁全库
WAL 文件	数据库同目录生成 `.db-wal` 和 `.db-shm` 文件	存储未 checkpoint 的事务日志	—	不要手动删除 `.wal`/`.shm` 文件，否则可能丢数据
强制 Checkpoint	`PRAGMA wal_checkpoint;`	将 WAL 日志写回主数据库文件	`PRAGMA wal_checkpoint;`	自动 checkpoint 由 SQLite 触发（如 WAL 达 1000 页）
禁用 WAL	`PRAGMA journal_mode = DELETE;`	切回默认日志模式	`PRAGMA journal_mode = DELETE;`	切换时会自动执行 checkpoint
使用建议	高读低写场景（如 Web 应用、日志系统）推荐启用 WAL	提升响应速度	—	不适用于 NFS 等不支持共享内存的文件系统

7.4 备份与恢复策略

操作名称	方法 / 命令	用途	代码示例 / 操作步骤	注意事项
在线备份（SQL dump）	`sqlite3 db.db .dump > backup.sql`	生成可移植的文本备份	`sqlite3 app.db .dump > app_20250405.sql`	可跨版本、跨平台恢复；但大数据量时慢
在线备份（VACUUM INTO）	`VACUUM INTO 'backup.db';`	快速生成压缩后的二进制备份	`VACUUM INTO 'safe_copy.db';`	SQLite 3.27.0+；备份期间原库可读写
冷备份	直接复制 `.db` 文件	最简单备份方式	`cp myapp.db myapp_backup.db`	必须确保无写入进程，否则可能损坏
热备份（WAL 模式）	复制 `.db` + `.db-wal` + `.db-shm`（需原子性）	WAL 模式下的安全热备	—	实际操作复杂；推荐用 `.dump` 或 `VACUUM INTO`
恢复数据库	`sqlite3 new.db < backup.sql`	从 SQL 脚本恢复	`sqlite3 restored.db < app_backup.sql`	新数据库自动创建；注意字符编码
增量备份策略	定期 `.dump` + 记录最后备份时间戳，结合应用层日志	减少备份体积	—	SQLite 无内置 binlog，需应用层配合
备份验证	恢复到临时库并执行 `.tables` / `COUNT(*)`	确保备份完整可用	`sqlite3 test.db < backup.sql && sqlite3 test.db "SELECT COUNT(*) FROM users;"`	关键业务必须验证备份有效性

第八章：SQLite 应用集成（简要）

8.1 在 Python 中使用 SQLite（sqlite3 模块）

方法/操作名称	语法 / 使用方式	用途	代码示例	注意事项
连接数据库	`sqlite3.connect(数据库路径)`	打开或创建数据库连接	`conn = sqlite3.connect('app.db')`	路径可为文件名或 `':memory:'`（内存库）
创建游标	`conn.cursor()`	获取执行 SQL 的游标对象	`cur = conn.cursor()`	游标用于执行查询和获取结果
执行 SQL	`cur.execute(sql, [参数])`	执行单条 SQL 语句	`cur.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))`	使用 `?` 占位符防 SQL 注入；不可拼接字符串
批量执行	`cur.executemany(sql, 参数序列)`	高效插入/更新多行	`cur.executemany("INSERT INTO t (x) VALUES (?)", [(1,), (2,)])`	自动开启事务，性能优于循环 execute
提交事务	`conn.commit()`	永久保存更改	`conn.commit()`	默认自动提交关闭，需显式 commit
查询结果获取	`cur.fetchall()` / `fetchone()` / `fetchmany(n)`	获取 SELECT 结果	`rows = cur.execute("SELECT * FROM users").fetchall()`	`fetchall()` 返回列表；大数据量建议分批 fetch
关闭连接	`conn.close()`	释放数据库连接	`conn.close()`	建议在 `finally` 块或使用 `with` 语句管理
上下文管理器	`with sqlite3.connect(...) as conn: ...`	自动提交或回滚	`with sqlite3.connect('db.db') as conn:` `conn.execute("...")`	正常退出自动 commit，异常自动 rollback

8.2 在 C/C++ 中嵌入 SQLite

操作名称	语法 / 函数调用	用途	代码示例（C 风格）	注意事项
初始化与打开	`sqlite3_open(const char filename, sqlite3 *ppDb)`	打开数据库连接	`sqlite3 *db;` `int rc = sqlite3_open("test.db", &db);`	返回 `SQLITE_OK` 表示成功；需链接 `-lsqlite3`
执行无结果 SQL	`sqlite3_exec(db, sql, callback, data, &errMsg)`	执行 CREATE/INSERT/UPDATE 等	`sqlite3_exec(db, "CREATE TABLE t(x);", 0, 0, &err);`	callback 为 NULL 时忽略结果；错误信息需 `free(errMsg)`
编译 SQL 语句	`sqlite3_prepare_v2(db, sql, -1, &stmt, 0)`	预编译带参数的 SQL	`sqlite3_prepare_v2(db, "INSERT INTO t VALUES (?)", -1, &stmt, 0);`	推荐使用 `prepare_v2` 而非 v1
绑定参数	`sqlite3_bind_text(stmt, idx, val, len, SQLITE_STATIC)`	为预编译语句绑定参数	`sqlite3_bind_text(stmt, 1, "hello", -1, SQLITE_TRANSIENT);`	idx 从 1 开始；`SQLITE_TRANSIENT` 表示复制字符串
执行预编译语句	`sqlite3_step(stmt)`	执行一条预编译语句	`sqlite3_step(stmt);`	返回 `SQLITE_DONE`（DML）或 `SQLITE_ROW`（有结果）
获取查询结果	`sqlite3_column_text(stmt, colIndex)`	从当前行获取列值	`const char name = (const char )sqlite3_column_text(stmt, 0);`	需先调用 `sqlite3_step()` 获取行
重置与释放	`sqlite3_reset(stmt); sqlite3_finalize(stmt);`	复用或销毁语句对象	`sqlite3_reset(stmt); // 可再次 bind 和 step`	`finalize` 释放资源；`reset` 保留编译状态
关闭数据库	`sqlite3_close(db)`	关闭连接并释放资源	`sqlite3_close(db);`	确保所有 stmt 已 finalize

8.3 与其他语言/框架的集成要点

语言/框架	集成方式 / 核心库	用途	典型用法示例	注意事项
JavaScript (Node.js)	使用 `better-sqlite3` 或 `sqlite3` npm 包	后端或 Electron 应用嵌入数据库	`const db = new Database('app.db'); db.prepare("SELECT ...").all();`	`better-sqlite3` 性能更高，同步 API；`sqlite3` 包支持异步
Java	使用 `org.xerial:sqlite-jdbc`	Android 或桌面 Java 应用	`Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");`	需将 `sqlite-jdbc.jar` 加入 classpath
Go	使用 `modernc.org/sqlite` 或 `go-sqlite3`	Go 应用内嵌数据库	`db, _ := sql.Open("sqlite", "file:test.db?cache=shared&mode=rwc")`	`modernc.org/sqlite` 是纯 Go 实现，无需 CGO
.NET (C#)	使用 `Microsoft.Data.Sqlite` 或 `System.Data.SQLite`	Windows 桌面或 ASP.NET Core	`using var conn = new SqliteConnection("Data Source=app.db;");`	`Microsoft.Data.Sqlite` 是官方轻量驱动
Rust	使用 `rusqlite` crate	系统级应用或 CLI 工具	`let conn = Connection::open("app.db")?; conn.execute("...", params![])?;`	基于 libsqlite3，需系统安装或静态链接
Web 浏览器	使用 `sql.js`（基于 Emscripten 编译的 SQLite）	前端浏览器内运行 SQLite	`const db = new SQL.Database(); db.run("CREATE TABLE t(x);");`	数据库存在于内存，刷新丢失；适合离线演示

通用集成原则：

使用参数化查询 — 保证安全与稳定性
管理连接生命周期
处理并发写限制 — SQLite 不适合高并发写场景；多进程访问需谨慎（建议 WAL + 文件锁）