1. 零基础学 SQLite3 的第一课它到底是什么、能干什么1.1 一个文件就是一个数据库这不是比喻第一次接触 SQLite3 的人最容易卡在一个惯性思维里数据库是不是都要安装一个服务端要配置端口、账户、权限还要担心服务挂了SQLite3 完全不是这样。它是一个用 C 语言写成的嵌入式关系型数据库整个数据库就是一个普通文件你只要把命令行工具下载下来一条命令就能打开一个库文件建表、写数据、查数据全部在本地进程内完成不需要任何后台服务。正因为这个特性它被广泛用在移动 App、桌面软件、嵌入式设备、单片机、本地工具和自动化脚本里几乎是“轻量数据存储”的默认答案。你完全可以把 SQLite3 理解成一个“有组织地存放数据的文件”。很多实际场景比如本地待办清单、个人记账本、爬虫采集结果的落库、自动化脚本的临时数据中转根本不需要启动 MySQLSQLite3 一个文件就能全部搞定。而且这个文件可以直接拷贝、压缩、备份、发给同事别人拿到手用命令行打开就能查跨操作系统也没有障碍。对于零基础的人来说这是接触 SQL 语法入门门槛最低的一条路径。1.2 它和 MySQL、PostgreSQL 的本质区别有个误区流传很广把 SQLite3 当成“精简版 MySQL”。其实两者架构根本不是一条路线。MySQL 和 PostgreSQL 是典型的客户端-服务器模式需要常驻服务进程、监听端口、通过网络连接SQLite3 则是进程内数据库直接在本地进程中读写文件没有网络层没有用户名密码体系默认也不支持并发多用户写入。这个设计带来两个结果一是零配置、零维护不会出现“服务没起来导致应用挂掉”的情况二是单机场景下读写性能反而很快因为省掉了网络通信和权限校验的开销。但这不代表 SQLite3 是玩具。官方宣称单库上限约 281 TB支持标准 SQL支持事务、索引、视图、触发器、公共表表达式功能覆盖绝大多数常规业务。它真正不适合的场景是大量用户同时并发写入、高并发 Web 后端、需要细粒度权限控制的企业级系统。在这些场景里你确实应该用 MySQL 或 PostgreSQL。搞清这条边界之后你会越来越喜欢 SQLite3因为它在自己擅长的领域里几乎从不添乱。1.3 学完这套语法能换来什么能力SQLite3 的 SQL 方言整体和标准 SQL 高度兼容。在 SQLite3 里养成的建表、查询、关联、聚合的习惯换到 MySQL、PostgreSQL、SQL Server基本可以直接平移只需要调整少量数据类型和函数名。也就是说它是入门 SQL 的一个低门槛入口不需要装服务端不会被权限配置劝退打开文件就能写第一条查询。同时SQLite3 又够实用。我见过不少后端开发人员在本地调试时用它当轻量库只在测试环境切 MySQL也见过数据分析师处理几十万行 CSV 时用编程语言自带的 sqlite3 模块建临时表跑 SQL效率和体验比手工用列表硬算强很多。这套语法学完你得到的不仅是一个数据库的使用经验而是一整套“用结构化方式处理数据”的思考方式。2. 下载安装与命令行配置先把环境跑起来2.1 官网下载与历史版本选择的正确姿势下载这块我建议只认官方页面。SQLite 官网提供了各个平台预编译好的命令行工具Windows 用户下载 sqlite-tools-win-x64 压缩包macOS 用户下载 sqlite-tools-osx-x64Linux 用户下载 sqlite-tools-linux-x64。选当下最新稳定版就足够不需要纠结。搜索引擎里“sqlite3 历史版本下载”的需求一般出现在两种场景一种是在某个老系统上部署官方新版本对系统库版本有要求另一种是企业内部强制使用固定版本。从零开始学完全不需要追旧版最新版最省心。解压后的压缩包里有三个可执行文件sqlite3、sqlite3_analyzer、sqldiff。核心工具就是 sqlite3sqlite3_analyzer 用来分析数据库碎片和空间使用情况sqldiff 用来比对两个数据库文件的差异日常学习阶段暂时用不上。下载完成后把 sqlite3 所在目录加进系统 PATH这样你在任意目录下都能直接敲 sqlite3 命令。2.2 三大平台的安装配置速查平台推荐方式安装后验证备注Windows解压 zip配置环境变量 Pathsqlite3 --version注意区分 64 位和 32 位macOS系统自带旧版建议brew install sqlite3sqlite3 --versionHomebrew 装完可能是 keg-only需按提示加入 PATHLinuxsudo apt install sqlite3或sudo yum install sqlitesqlite3 --version部分发行版包名是 sqlite3安装完成后的第一件事不是急着建表而是执行一次sqlite3 --version确认能输出版本号。如果你在 Windows 上遇到“不是内部或外部命令”的提示多半是环境变量没生效重开终端或者手动把解压目录加进 Path 即可。macOS 自带 sqlite3 但版本往往偏旧如果你打算长期用建议通过 Homebrew 装一个干净的现代版本避免老版本缺某些新特性。2.3 第一次用命令行把数据库敲出来安装好之后直接在一级目录运行sqlite3 demo.db就会进入 sqlite3 的交互式命令行界面此时数据库文件 demo.db 其实还没真正创建等到你执行第一条建表或写数据命令文件才会在磁盘上出现。这是 SQLite3 的一个特点创建库和打开库是同一个动作文件存在就打开文件不存在就等你写入数据时再生成。进入交互界面后可以立刻体验几个最基本的操作输入.tables会列出当前库里的所有表输入.schema可以查看建表语句输入.headers on让查询结果显示列名输入.mode column让输出按列对齐。很多初学者一上来就敲SELECT结果查询结果是乱的其实先设置好.headers on和.mode column可读性会立刻提升。最后用.quit退出程序。这些以点开头的命令叫“点命令”是命令行工具专有的管理命令不属于 SQL 语法本身但实战中天天都要用。2.4 常用点命令先背熟这几个就够用点命令作用使用频率.open 文件名.db新建或打开数据库文件高.tables查看全部表名高.schema [表名]查看建表语句高.headers on/off显示或关闭列名高.mode column按列对齐显示结果高.import 文件 表名把 CSV 等文本文件导入表中.read 文件.sql执行 SQL 脚本文件中.quit退出命令行高这些点命令和后面要讲的 SQL 语句是两套东西点和点开头是给命令行工具用的控制命令主要影响交互体验SQL 语句才是真正操作数据的语法。建议刚开始时把上面表格里的命令都手打一遍尤其是.open配合多库切换实际工作中很常用。如果你在 SQL 脚本和命令行之间来回切换感到混乱记住一条原则点命令不需要加分号SQL 语句一般用分号结尾两者不会冲突。3. 建库建表语法先把数据的地基打扎实3.1 数据库文件的打开与 ATTACH 的差异SQLite3 官方 SQL 里没有CREATE DATABASE这种语句。你要新建一个数据库最直接的方式是在命令行启动时指定文件名比如sqlite3 myapp.db也可以进入交互界面后用.open myapp.db。这个文件一开始可能是 0 字节直到你执行建表才会写入内容这是正常现象不是安装出错。如果需要在同一个命令行会话里同时操作多个数据库文件就要用ATTACH DATABASE。语法很简单ATTACH DATABASE another.db AS db2; SELECT * FROM db2.books;这样你可以在查询里通过库名.表名的方式跨文件访问数据。DETACH DATABASE db2;则用于解除关联。这个能力在日常调试中真的很好用比如你想对比两个库的同一张表数据结构ATTACH之后就变得非常轻量。3.2 CREATE TABLE 完整语法拆解建表是最重要的 DDL 语法。我先给一个贯穿全文的示例一个书籍数据库包含作者表和图书表后面讲增删改查、连接查询都用这两张表。CREATE TABLE authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, country TEXT ); CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author_id INTEGER, published_year INTEGER, price REAL, created_at TEXT DEFAULT (datetime(now)), FOREIGN KEY (author_id) REFERENCES authors(id) );CREATE TABLE后面是表名括号里是字段定义。每个字段由“字段名 类型 约束”组成字段之间用逗号分隔最后一个字段末尾不需要逗号。上面这个例子里id INTEGER PRIMARY KEY AUTOINCREMENT是自增主键的标准写法title TEXT NOT NULL表示标题字段是文本类型且不能为空price REAL表示浮点价格DEFAULT (datetime(now))则给created_at字段设置了自动填充当前时间的默认值。关于数据类型SQLite3 里你可以写INTEGER、TEXT、REAL、BLOB、NUMERIC这些类型名也可以像 MySQL 一样写VARCHAR(255)、DATETIMESQLite3 不会强制报错它会根据类型名称自动做亲和性匹配。从零开始学建议先老老实实用 INTEGER、TEXT、REAL、BLOB 这四种逻辑最清晰排查问题也方便。3.3 约束条件主键、非空、唯一、默认值、检查约束是让数据保持“干净”的第一道防线。最常见的几个约束如下PRIMARY KEY标识主键逻辑上唯一定位一行AUTOINCREMENT用于让主键自增注意它不是主键本身只是给 INTEGER 主键加一个“自动递增”的行为NOT NULL让字段不能为空UNIQUE让字段值不能重复DEFAULT给字段设默认值CHECK校验字段值是否满足条件。给几个典型写法CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT UNIQUE NOT NULL, age INTEGER CHECK (age 0 AND age 150), level TEXT DEFAULT normal, bio TEXT );那么AUTOINCREMENT到底要不要写如果你的主键只是用来做唯一标识直接用INTEGER PRIMARY KEY就够了SQLite 内部会隐式生成一个递增的 rowid。显式写AUTOINCREMENT多了一层保证它不允许复用已经删除过的最大 id好处是 id 不会被回收代价是稍微多一点点额外开销。我个人的做法是如果数据删除也是常规操作就加 AUTOINCREMENT避免历史记录因为 id 复用而混乱如果只是临时缓存表就不用加。3.4 ALTER TABLE改表结构的语法边界建表之后想改表结构用ALTER TABLE。SQLite 支持的操作比 MySQL 少最常见的三个场景是改表名、加字段、删除字段。ALTER TABLE old_name RENAME TO new_name; ALTER TABLE books ADD COLUMN rating REAL; ALTER TABLE books DROP COLUMN rating;RENAME在重命名表时会自动更新这个表里定义的外键引用。ADD COLUMN很好用但有限制新加的字段不能有PRIMARY KEY或UNIQUE约束不能有非空的DEFAULT以外的复杂默认值。DROP COLUMN是较新版本才支持的能力而且如果某个字段被索引、被视图引用、或在触发器里用到就不能直接删。SQLite 的 ALTER TABLE 相当保守一旦遇到不能直接改的情况通用方案是“建新表 插入旧数据 删旧表 重命名”这个思路你在实战中迟早会用到。4. 数据操作语法INSERT、UPDATE、DELETE 与 UPSERT 实战4.1 INSERT 的三种写法与批量插入性能对比插入数据语法是INSERT INTO 表名 (字段列表) VALUES (值列表)。先插入基础数据INSERT INTO authors (name, country) VALUES (刘慈欣, 中国); INSERT INTO authors (name, country) VALUES (J.R.R. Tolkien, 英国);如果要对所有字段按顺序赋值可以省略字段列表直接写成INSERT INTO authors VALUES (1, 刘慈欣, 中国)但这种写法对字段顺序极其敏感生产环境强烈不推荐因为只要表结构一变你的插入语句就可能悄悄错位。多条数据可以一次性插入用逗号分隔多个值列表INSERT INTO books (title, author_id, published_year, price) VALUES (三体, 1, 2008, 29.9), (三体2黑暗森林, 1, 2008, 32.0), (The Hobbit, 2, 1937, 45.0);批量插入和逐条插入性能差很多。在命令行里一条一条执行每一句都要经过解析、编译、执行三步多条 VALUES 一次提交是同一语句内的多条记录解析开销只算一次。如果你需要用程序往 SQLite 里灌一批测试数据更推荐使用带参数的预编译语句循环绑定参数一万条数据往往在几百毫秒内完成。INSERT 还有一种形态叫INSERT INTO ... SELECT直接查出另一个表的数据插入当前表在数据迁移、构造测试数据、临时表备份时非常常用INSERT INTO books_archive (title, author_id, price) SELECT title, author_id, price FROM books WHERE published_year 2020;4.2 UPDATE 与 DELETE随时记住 WHERE 的分量准备万全了才开始更改数据。更新语法是UPDATE books SET price 39.9 WHERE id 1;一次更新多个字段用逗号隔开UPDATE books SET price 29.9, published_year 2006 WHERE title 三体;UPDATE最危险的写法是没有WHERE或者WHERE条件写得过于宽泛。UPDATE books SET price 0会把所有图书价格全部清零而且 SQLite 不会像 MySQL 那样在命令行里默认提示你确认。我在实际使用中有一条习惯写 UPDATE 前先用同条件 SELECT 看一眼影响范围。比如先SELECT id FROM books WHERE title LIKE %三体%确认是这几行再把它改成UPDATE能省掉不少返工。删除同理DELETE FROM books WHERE id 1;只删一行DELETE FROM books;清空整张表这是高危动作。SQLite 没有 MySQL 的TRUNCATE TABLE想清空表时也可以用DELETE FROM但更纯粹的做法是DROP TABLE后重新建表速度更快、文件空间释放更彻底。4.3 ON CONFLICT 与 UPSERT冲突处理的高级写法在真实项目中经常遇到“如果这条记录已经存在就更新不存在就插入”的需求。SQLite 提供两种方案REPLACE INTO和INSERT ... ON CONFLICT DO UPDATE。REPLACE INTO写法简单但它的实现方式是先删除冲突的旧行再插入新行如果表里有自增主键主键 id 会发生变化如果表里有外键或触发器删除再插入还会引发连锁反应。所以更推荐用标准的 UPSERT 语法INSERT INTO books (id, title, price) VALUES (1, 三体新版, 49.9) ON CONFLICT(id) DO UPDATE SET title excluded.title, price excluded.price;这里的excluded是一个特殊引用代表“本次 INSERT 本来想插入的值”用于覆盖旧值。这条语句在 id 为 1 的记录存在时执行更新不存在时执行插入。相比REPLACE INTO它的 id 保持不变其他字段的关联约束也能安然无恙。从零基础阶段起就直接培养“冲突处理优先考虑 ON CONFLICT”的习惯后面写程序时你会感谢这个决定。4.4 小实战把书籍库存管理过一遍不妨把刚才的语法串起来做一个小任务。先建一个库存表再插入、更新、删除一些数据CREATE TABLE inventory ( book_id INTEGER PRIMARY KEY, quantity INTEGER NOT NULL DEFAULT 0 ); INSERT INTO inventory (book_id, quantity) VALUES (1, 10), (2, 3), (3, 25); UPDATE inventory SET quantity quantity 5 WHERE book_id 1; DELETE FROM inventory WHERE quantity 0; SELECT b.title, i.quantity FROM books b JOIN inventory i ON b.id i.book_id;这个例子看起来简单但已经把插入、更新、删除、连接查询四个核心操作全部串起来了。学 SQL 最大的误区是只看不练因为语法本身并不难难的是在不同约束条件下组合使用它们。建议自己往表里加些数据再试几次“把价格上调 10%”“删除 2000 年以前的书”“查出库存低于 5 本的图书”这类真实问题。5. SELECT 查询语法从取数到统计分析的完整套路5.1 SELECT 基础语法字段、别名、去重SQL 里出现频率最高的就是 SELECT。最简单的形态是SELECT 1;它能直接返回一行数据常用来测试连接。真正的查询是SELECT 字段列表 FROM 表名;例如SELECT title, price FROM books;如果照片表字段太多可以用SELECT * FROM books;这条语句在开发期方便但上线后的代码里尽量少用因为你没法控制返回哪些字段一旦表结构变更程序很容易出现隐性崩溃。给字段起别名用 ASSELECT title AS 书名, price AS 价格 FROM books;去重用DISTINCTSELECT DISTINCT author_id FROM books;它的作用是去掉查询结果中重复的行。注意DISTINCT作用于整行不是只作用于第一个字段如果你SELECT DISTINCT author_id, published_year那么只有当两个字段都相同时才去重。5.2 WHERE 条件语法比较、逻辑、模糊匹配过滤数据的基本结构是SELECT ... FROM ... WHERE 条件。WHERE 后面的条件可以是比较运算、逻辑运算、范围判断和模糊匹配SELECT title, price FROM books WHERE price 30; SELECT title FROM books WHERE published_year BETWEEN 2000 AND 2020; SELECT title FROM books WHERE author_id IN (1, 2);模糊匹配用LIKE%代表任意多个字符_代表单个字符SELECT title FROM books WHERE title LIKE %三体%; SELECT title FROM books WHERE title LIKE 三_;LIKE在 SQLite 中默认不区分英文大小写这个行为与 MySQL 部分配置不同跨库迁移时需要留意。逻辑运算用AND、OR、NOT优先级上NOT最高接着是AND最后是OR拿不准时就加括号。实际开发中最容易出问题的不是运算符而是对 NULL 的判断WHERE price NULL永远不会返回真值判断空值必须写WHERE price IS NULL这条规则以后在 MySQL、PostgreSQL 里也通用。5.3 ORDER BY 与 LIMIT给结果排好序再分页排序语法是ORDER BY 字段 方向SELECT title, price FROM books ORDER BY price DESC; SELECT title, price FROM books ORDER BY price DESC, published_year ASC;方向只有ASC升序和DESC降序。多字段排序时从左到右依次生效比如上面这个例子先按价格降序价格相同的再按出版年份升序。NULL 在排序中的默认行为需要记住升序时 NULL 排在最前降序时 NULL 排在最后。限制返回行数用LIMIT配合OFFSET可以做分页SELECT title FROM books ORDER BY price DESC LIMIT 5; SELECT title FROM books ORDER BY price DESC LIMIT 5 OFFSET 10;第二条表示跳过 10 行返回后 5 行这是常见的第 3 页数据。分页一定要有 ORDER BY否则每次查询顺序不稳定翻页会出现重复或漏数据。5.4 聚合函数与 GROUP BY统计分析和分组汇总聚合函数把多行数据汇总成一行COUNT计数SUM求和AVG求平均MAX和MIN求最值。SELECT COUNT(*) FROM books; SELECT AVG(price) FROM books; SELECT MAX(price) AS max_price FROM books;想分别统计每个作者的图书数量就用GROUP BYSELECT author_id, COUNT(*) AS book_count FROM books GROUP BY author_id;使用 GROUP BY 时SELECT 里的非聚合字段必须来自分组字段。上面的例子可以但如果SELECT title, COUNT(*)且GROUP BY author_id在 SQLite 里并不会报错它会从该作者的记录中“随便挑”一本书的标题这种行为不可控也不该依赖。过滤分组结果要用 HAVINGWHERE 做不到这一点。WHERE 是在分组之前过滤原始行HAVING 是在分组之后过滤聚合结果SELECT author_id, COUNT(*) AS book_count FROM books GROUP BY author_id HAVING COUNT(*) 2;这条语句返回的是“出版过至少 2 本书的作者”。理解 WHERE 和 HAVING 的执行顺序是区分你会不会用 SQL 的一个明显分界线。6. 多表 JOIN 与子查询语法组合技查询能力的分水岭6.1 INNER JOIN 与 LEFT JOIN 的差异和写法只查一张表的场景远远不够实际业务里数据往往拆在多张表里。把多张表按关联条件拼起来这就是 JOIN。以 books 和 authors 为例每本书都有 author_id作者表里有对应的 nameSELECT b.title, a.name FROM books b INNER JOIN authors a ON b.author_id a.id;INNER JOIN只返回两边都匹配上的记录。如果某本书的 author_id 是空或者作者被删了这本书就不会出现在结果里。LEFT JOIN不同它保留左边 books 表的所有行右边就算匹配不上也会返回 NULL 填充SELECT b.title, a.name FROM books b LEFT JOIN authors a ON b.author_id a.id;在这个例子里如果一本书没有作者INNER JOIN 会直接把它丢掉LEFT JOIN 会把它列出来且作者名显示 NULL。初学者经常困惑该用哪个我有个简单的判断方法主要信息是哪张表就把哪张表放在左边如果查不到关联数据依然要保留主表记录用 LEFT JOIN关联字段必须存在且匹配才是 INNER JOIN。6.2 表别名与多表连查的简化写法JOIN 查询的字段往往来自多张表写全表名非常啰嗦。SQL 支持给表起别名上面的例子已经用了books b这样b.title里的 b 就是 books 的别名。表别名不是可有可无的加分项而是在表名过长或同表多次连接时保持可读性的必需手段。多个表可以连续 JOINSELECT b.title, a.name, i.quantity FROM books b JOIN authors a ON b.author_id a.id LEFT JOIN inventory i ON b.id i.book_id;这种链式连接在真实系统里很常见。注意从左到右的顺序逻辑先 JOIN 作者再 LEFT JOIN 库存每一步都有明确的关联条件。如果关联条件写错或者丢失结果会瞬间变成一个笛卡尔积行数爆炸式增长几万条数据能膨胀到几百万行这是新手排查问题时常常忽略的隐蔽根源。6.3 子查询的三种形态标量、行、表子查询就是把一个完整的 SELECT 嵌在另一个语句里。按返回结果不同可以分成三种形态。第一种是标量子查询返回单个值常用于 WHERE 条件SELECT title FROM books WHERE price (SELECT AVG(price) FROM books);这个查询返回价格高于平均价的书。第二种是行子查询返回一行多列不过实际使用频率不高。第三种是表子查询把子查询结果当成一张临时表放在 FROM 后面SELECT author_id, cnt FROM ( SELECT author_id, COUNT(*) AS cnt FROM books GROUP BY author_id ) WHERE cnt 2;这个写法本质上是先做一次分组统计再从结果里进行二次筛选。如果数据量很大子查询的性能需要小心在 SQLite3 里EXPLAIN 分析这类复杂 SELECT 时要注意临时表的创建和销毁代价。能用 JOIN 完成的查询不一定非要套子查询两者各有优劣子查询步骤清晰JOIN 在能够直接关联时通常性能更稳定。6.4 关联子查询与 EXISTS 的实战应用关联子查询是子查询里比较高级的一类内层查询使用了外层查询的字段因此每一行都要重新执行一次。一个典型的例子是“找出每本比同作者平均价格高的书”SELECT title, price, author_id FROM books b WHERE price ( SELECT AVG(price) FROM books WHERE author_id b.author_id );这种写法非常灵活但对性能考验也大数据量大时可能产生大量重复计算。判断“存在性”时很多情况可以用EXISTS替代更复杂的 JOINSELECT name FROM authors a WHERE EXISTS ( SELECT 1 FROM books b WHERE b.author_id a.id );SELECT 1只是为了满足子查询的语法要求实际返回 1 还是返回任意字段都无所谓EXISTS 只关心子查询是否有结果返回。这套组合语法的核心价值是让你能表达“按组比较”“存在与否”的业务逻辑而不需要先把所有数据拉进内存再逐行判断。7. 事务、索引、视图与触发器让数据库稳定高效的高级语法7.1 事务的 ACID 与 BEGIN、COMMIT、ROLLBACK事务把一组操作打包成一个原子整体要么全部成功要么全部失败。SQLite 的默认行为是每条语句自动开启并提交一个事务但要处理多步骤的一致性问题就必须显式使用事务语法BEGIN; UPDATE inventory SET quantity quantity - 1 WHERE book_id 1; UPDATE inventory SET quantity quantity - 2 WHERE book_id 2; COMMIT;如果在 COMMIT 之前发现第二步逻辑错误执行ROLLBACK;前面所有变更都会撤销。这就是事务的原子性。SQLite 同时支持BEGIN TRANSACTION、COMMIT、ROLLBACK等更完整的关键字。事务还有一个额外好处把多步写操作包在同一个事务里比逐条自动提交快得多因为磁盘刷新的次数大幅减少。我印象很深的一件事是用 Python 循环往 SQLite 里插 5 万条测试数据逐条自动提交需要十几秒包成事务后只要一两秒。注意在事务开启的状态下要时刻留意有没有忘记 COMMIT。长时间占用写事务不下发会导致其他写操作全部被阻塞甚至出现“数据库 is locked”的报错。7.2 索引语法性能提速和存储代价的平衡索引相当于给字段做了一个排序目录。语法很简单CREATE INDEX idx_books_author ON books(author_id); CREATE UNIQUE INDEX idx_users_email ON users(email);第一个语句给 author_id 建普通索引加速按作者查书的场景第二个语句创建唯一索引让 email 字段的值不能重复。唯一索引和 UNIQUE 约束效果类似可以直接替代。索引不是建得越多越好。每个索引都要占用存储空间并在每次 INSERT、UPDATE、DELETE 时同步维护写操作因此变慢。实战经验是先根据查询条件分析哪些字段频繁出现在 WHERE 或 JOIN 的 ON 条件里再针对性建索引对低基数字段比如性别、状态这类取值很少的列索引收益通常不高。想验证查询用没用索引可以在 SELECT 前面加EXPLAIN QUERY PLAN会得到查询计划能看到是否走了指定索引。7.3 视图语法把复杂查询封装成一张逻辑表视图是一个虚拟表它本身不存储数据执行查询时动态展开背后的 SELECT 语句。CREATE VIEW book_summary AS SELECT b.id, b.title, a.name AS author_name, b.price FROM books b JOIN authors a ON b.author_id a.id;创建之后你可以像查普通表一样查它SELECT * FROM book_summary WHERE price 30;视图最大的意义是封装。复杂 JOIN 写一次后续业务层只用视图名换一个字段名、调一张关联表只改视图定义不改业务代码。注意 SQLite 里的视图默认是普通视图不是 MySQL 里的物化视图每次查询都会重新执行背后的完整逻辑数据量大时不一定省性能但它大大简化了语义层的表达。7.4 触发器语法让数据库替你自动做动作触发器是数据库里的“守护程序”满足条件时自动执行一段 SQL。语法CREATE TRIGGER trg_books_audit AFTER INSERT ON books BEGIN INSERT INTO books_log (book_id, action, log_time) VALUES (NEW.id, insert, datetime(now)); END;这个触发器会在每次往 books 表插入数据后自动把新书的 id 记入 books_log 表。NEW对象代表新插入行的数据OLD对象代表被更新或删除前的旧行数据。触发器可以指定BEFORE或AFTER也可以在INSERT、UPDATE、DELETE任一事件前触发。它的代价和索引类似每次触发都会额外增加写操作不要在热路径上放太多触发器否则写性能会明显下降。7.5 外键语法别让“数据关系”变成一纸空文从语法上看建表阶段就可以声明外键FOREIGN KEY (author_id) REFERENCES authors(id)但这句声明在 SQLite 里默认并不会生效除非每次连接后显式执行PRAGMA foreign_keys ON;我见过不少新手建了外键却好奇为什么删掉作者后图书表里的 author_id 还留在原地就是因为这个开关没打开。外键约束生效后你可以控制删除和更新时的级联行为FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE;这样删除作者时他名下的图书记录也会级联删除。实际业务里要不要用 CASCADE 要谨慎自动删除可能连带掀起其他表的数据变化。比较稳妥的做法是先用 ON DELETE SET NULL让关联字段置空保留历史数据再根据业务需求决定是否清理。8. 数据类型与内置函数语法里最容易被忽视却天天要用的细节8.1 类型亲和性为什么 TEXT 字段里能存数字这是 SQLite3 最独特也最容易让人迷惑的设计。SQLite 使用动态类型系统每个值本身有自己的存储类别而不是由字段类型严格决定。官方把字段类型归类成五种“亲和类型”TEXT、NUMERIC、INTEGER、REAL、BLOB。当你往 TEXT 字段里插入数字时如果该数字能被无损转换SQLite 会按数字原样保存查询出来时可能还是数字形态。简单理解字段类型更像一个“建议”而不是强制限制。为什么这样设计SQLite 的初衷是轻量灵活不想像传统数据库那样严格到分毫不差。这类行为的后果是查询比对时可能出现类型转换。为了减少意外我在建表时会坚持字段类型写清楚整数存 INTEGER文本存 TEXT这样虽然数据库不强制但至少让阅读代码的人知道字段含义。8.2 字符串与数学函数的体系化用法字符串处理在 SQL 里任务不轻。SQLite 提供了一批很常用的函数SELECT UPPER(title), LOWER(name) FROM books; SELECT SUBSTR(title, 1, 3) FROM books; SELECT TRIM( hello ), LENGTH(hello); SELECT REPLACE(title, 三体, 三体典藏版) FROM books;SUBSTR(字段, 起始位置, 长度)的起始位置从 1 开始而不是程序员的 0。LENGTH对中文按字符数计算返回“三体”的长度是 2。REPLACE常用于清洗脏数据把旧值批量替换成新值比如把数据里的中文全角空格替换成普通空格。数学函数常用的有ABS绝对值、ROUND四舍五入、RANDOM随机数SELECT ROUND(price * 0.9, 2) AS discounted FROM books; SELECT ABS(-10), RANDOM();这些函数可以直接在 SELECT 里对字段做加工。字符串拼接在 SQLite 里用||例如SELECT title || || published_year || FROM books;绝大多数数据库都支持这个双竖线拼写法一次学会到处适用。8.3 日期时间函数与默认时间戳SQLite 没有真正的日期时间类型推荐用文本形式存yyyy-mm-dd hh:mm:ss。函数层面的主力是date()、time()、datetime()、strftime()、julianday()。SELECT date(now); SELECT datetime(now); SELECT strftime(%Y-%m-%d %H:%M:%S, now);date(now)返回今天的日期datetime(now)返回当前时间。strftime是格式化最强的一个%Y是四位年份%m是月份%d是日%H:%M:%S是时分秒。计算日期差异可以直接用 juliandaySELECT julianday(2024-12-31) - julianday(2024-01-01);这个值就是两个日期相差的天数。建表时给字段设计默认值可以写成DEFAULT (datetime(now))注意要加括号否则语法不认。8.4 NULL 处理IFNULL、COALESCE 与排序陷阱NULL 含义是“未知”或“缺失”它不是空字符串也不是 0。任何 NULL 参与的算术运算结果都是 NULL。处理 NULL 有三个常用函数IFNULL(值, 替代值)、COALESCE(值1, 值2, ...)返回第一个非 NULL 值、NULLIF(值1, 值2)当两值相等时返回 NULL。SELECT COALESCE(country, 未知国家) FROM authors; SELECT * FROM books ORDER BY price NULLS LAST;SQLite 支持NULLS FIRST和NULLS LAST用来明确控制 NULL 在 ORDER BY 中的位置。这个细节在报表里特别重要否则你可能会看到价格缺失的图书排在最前面用户会以为是数据 bug。数据库的默认行为和 MySQL 不同跨库迁移时一定要检查排序结果。9. 实战踩坑复盘从“语法能跑”到“稳得一批”的必修课9.1 WAL 模式与并发写入的锁问题SQLite 天然适合单机读写但多进程同时写入时会出现database is locked报错。解决思路不是改用 MySQL而是先检查代码里的写操作是否有事务累积。如果多个连接频繁交替写入推荐开启 WAL 日志模式PRAGMA journal_mode WAL;WAL 模式允许多个读操作和一个写操作同时进行写操作不会阻塞读操作这对应用体验的提升是肉眼可见的。这个设置是持久化的作用于整个数据库文件。配合PRAGMA busy_timeout 5000;可以让进程等待锁最多 5 秒而不是立刻报错。踩过几次“锁死”的坑之后我再也没用过默认的 delete 日志模式凡是需要程序反复读写同一个 SQLite 文件的场景第一件事就是开 WAL。9.2 外键开关为什么默认关着我在第 7 章专门讲外键语法时可以默认生效但 SQLite 为了保证向后兼容性以及让老数据库文件在升级后不会被新约束绊倒。这带来的实际问题是建了外键不写 PRAGMA约束就形同虚设。我个人的做法是把PRAGMA foreign_keys ON;写进 SQL 脚本文件的最顶部每次新建连接时也通过命令行参数或代码配置执行一次。如果用了 Python、Go、Node 等编程语言的驱动在建立连接对象后立刻执行这一条 PRAGMA否则你就等于主动放弃外键保护脏数据会在你意想不到的地方冒出来。9.3 命令行中文乱码与编码处理SQLite 存储的是 UTF-8 文本大部分情况下很省心但 Windows 命令行控制台默认代码页可能不是 UTF-8直接查询中文表名或中文字段值时显示会出现乱码。最简单的处理办法是执行.mode quote或.mode box前先设置控制台代码页Windows 下可以输入chcp 65001切换到 UTF-8 编码。macOS 和 Linux 终端一般不存在这个困扰。另外数据导入时如果遇到乱码多半是源文件本身不是 UTF-8 编码CSV 文件先转好编码再导入不要指望数据库自动帮你转换。9.4 把语法学透的三条学习路线建议第一条跟着官方文档和大纲里的语法逐一验证。SQLite 官方的语法图非常清晰但读起来枯燥比较好的办法是打开命令行把每一条语法都敲进去观察输出是否符合预期。第二条给自己找一个小项目比如做一个个人图书馆管理系统要有作者表、图书表、借阅记录表再用 JOIN、聚合、视图把核心报表做一遍这个过程中 DDL、DML、查询、事务全都会用到。第三条至少用一门编程语言连一次 SQLitePython 内置 sqlite3 模块是零依赖的写一个增删改查脚本你才算真正把命令行语法带进了工程世界。我自己平时接触 SQLite3 最多的场景是写自动化工具和处理离线数据。它不像大型数据库那样需要花大量精力维护语法也足够接近标准 SQL这让它成了我桌面级应用和脚本任务里的默认配置。踩过外键和内建锁的坑之后我现在每次新建数据库文件第一件事就是执行PRAGMA foreign_keys ON;和PRAGMA journal_mode WAL;这几乎已经成了肌肉记忆。如果你的目标是快速掌握一套能迁移到各大数据库的 SQL 基础从 SQLite3 开始绝对是性价比最高的路线。