《SQLite零基础深入研习指南：从入门到精通全涵盖》
并非是MySQL不适用，而是SQLite具有更高的性价比，绝大多数的应用场景都能够由SQLite来达成。
SQLite是一款由C语言编写的开源、轻量级、运行快速、独立且可靠性高的SQL数据库引擎，它提供了功能完备的数据库解决方案。SQLite几乎能够在所有手机和计算机上运行，被嵌入到众多每日被人们使用的应用程序之中。
此外，SQLite还具备稳定的文件格式、跨平台能力以及向后兼容性等特性。SQLite的开发者承诺，至少在2050年之前会保持该文件格式不变。
本文将对SQLite的基础知识和使用方法进行介绍。
SQLite安装
在SQLite官方页面1下载适配目标系统的压缩包。
下载并解压后，无论在Windows、Linux还是Mac OS系统上，都能得到一个sqlite3命令行工具。例如在Mac OS上解压后得到的命令行工具示例如下：
➜ sqlite-tools-osx-x64-3450100 ls -l total 14952 -rwxr-xr-x@ 1 darcy staff 1907136 1 31 00:27 sqldiff -rwxr-xr-x@ 1 darcy staff 2263792 1 31 00:25 sqlite3 -rwxr-xr-x@ 1 darcy staff 3478872 1 31 00:27 sqlite3_analyzer
SQLite使用场景
SQLite与客户端/服务器类型的SQL数据库引擎（像MySQL、Oracle、PostgreSQL或SQL Server）不同，它们解决的问题也不一样。服务器端的SQL数据库引擎旨在实现企业级数据的共享存储，着重于可扩展性、并发性、集中化和控制性。而SQLite通常用于为个人应用程序和设备提供本地数据存储，注重经济、高效、可靠、独立和简单。
SQLite的使用场景：
- 嵌入式设备与物联网：SQLite无需额外管理或启动服务，非常适合用在手机、电视、机顶盒、游戏机、相机、手表等智能设备上。
- 网站：多数低流量网站可使用SQLite作为数据库，官方称通常每天访问量少于10万次的网站能良好运行，SQLite官方网站自身就用SQLite作为数据库引擎，每天处理约50万HTTP请求，其中约15-20%的请求涉及数据库查询。
- 数据分析：SQLite3命令行工具能便捷地与CSV和Excel文件交互，适合分析大数据集，且许多语言如Python内置SQLite支持，可轻松编写脚本操作数据。
- 缓存：SQLite可作为应用服务的缓存，减轻中心数据库压力。
- 内存或临时数据库：得益于SQLite的简单快速，非常适用于程序演示或日常测试。
SQLite不适合的场景：
- 需要通过网络访问数据库的情况，SQLite是本地文件数据库，无远程访问功能。
- 要求高可用性和可扩展性的场合，SQLite简单易用但不可扩展。
- 数据量极大时，尽管SQLite数据库大小限制高达281 TB，但所有数据需存储在单个磁盘上。
- 写入操作高并发时，SQLite任何时刻只允许一个写入操作执行，其他写入需排队。
SQLite3命令操作
SQLite提供sqlite3（在Windows上为sqlite3.exe）命令行工具，通过该工具可执行SQLite数据库操作和SQL语句。
直接在命令提示符下执行./sqlite3启动sqlite3程序，然后可输入.help查看帮助指南，或输入.help 关键词获取特定关键词帮助信息。部分命令列表如下：
sqlite> .help .databases List names and files of attached databases .dbconfig ?op? ?val? List or change sqlite3_db_config() options .dbinfo ?DB? Show status information about the database .excel Display the output of next command in spreadsheet .exit ?CODE? Exit this program with return-code CODE .expert EXPERIMENTAL. Suggest indexes for queries .explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto .help ?-all? ?PATTERN? Show help text for PATTERN .hex-rekey OLD NEW NEW Change the encryption key using hexadecimal .indexes ?TABLE? Show names of indexes .mode MODE ?OPTIONS? Set output mode .open ?OPTIONS? ?FILE? Close existing database and reopen FILE .output ?FILE? Send output to FILE or stdout if FILE is omitted .quit Exit this program .read FILE Read input from FILE or command output .schema ?PATTERN? Show the CREATE statements matching PATTERN .show Show the current values for various settings .tables ?TABLE? List names of tables matching LIKE pattern TABLE .......
sqlite3只是读取输入行信息，然后传递给SQLite库来执行，SQL语句都要以分号;结尾才会开始执行，因此你可以自由的分行输入。在sqlite3中，SQL语句需以分号;结尾才会执行，允许跨行输入。特殊的点命令（如.help和.tables）以小数点.开头，不需要分号。
SQLite新建数据库
直接执行sqlite3 filename打开或创建一个SQLite数据库。如果文件不存在，SQLite会自动创建它。
示例：打开或创建名为my_sqlite.db的SQLite数据库文件。
$ sqlite3 my_sqlite.db SQLite version 3.39.5 2022-10-14 20:58:05 Enter ".help" for usage hints. sqlite>
也可以首先创建一个空白文件，然后使用sqlite3命令打开它。接下来使用CREATE TABLE命令创建一个名为user的表，用.tables命令查看现有表格，使用.exit退出sqlite3工具。
$ touch test.db $ sqlite3 test.db SQLite version 3.39.5 2022-10-14 20:58:05 Enter ".help" for usage hints. sqlite> create table user(name text,age int); sqlite> .tables user sqlite>
SQLite查看当前数据库
使用点命令.databases查看当前打开的数据库。
sqlite> .databases main: /Users/darcy/develop/sqlite-tools-osx-x86-3420000/my_sqlite.db r/w sqlite>
SQLite增删改查
SQLite几乎完全兼容常见的SQL语句规范，因此可直接编写和执行标准SQL语句。
创建表：
sqlite> create table user(name text,age int); sqlite>
插入数据：
sqlite> insert into user values('aLang',20); sqlite> insert into user values('Darcy',30); sqlite> insert into user values('XiaoMing',40);
查询数据：
sqlite> select * from user; aLang|20 Darcy|30 XiaoMing|40
添加索引，为user表的name创建名为user_name的索引：
sqlite> create index user_name on user(name);
SQLite更改输出格式
在查询数据时，SQLite默认用|分割每列数据，可能不便阅读。实际上，sqlite3工具支持多种输出格式，默认是list模式。可用的输出格式有ascii、box、csv、column、html、insert、json、line、list、markdown、quote、table。可使用.mode命令更改输出格式。
Box格式：
sqlite> .mode box sqlite> select * from user; ┌──────────┬─────┐ │ name │ age │ ├──────────┼─────┤ │ aLang │ 20 │ │ Darcy │ 30 │ │ XiaoMing │ 40 │ └──────────┴─────┘
json格式：
sqlite> .mode json sqlite> select * from user; [{"name":"aLang","age":20}, {"name":"Darcy","age":30}, {"name":"XiaoMing","age":40}]
column格式：
sqlite> .mode column sqlite> select * from user; name age -------- --- aLang 20 Darcy 30 XiaoMing 40
table格式：
sqlite> .mode table sqlite> select * from user; +----------+-----+ | name | age | +----------+-----+ | aLang | 20 | | Darcy | 30 | | XiaoMing | 40 | +----------+-----+ sqlite>
查询Schema
sqlite3工具提供几个方便命令用于查看数据库的schema，这些命令作为快捷方式提供。例如，.table查看数据库中的所有表：
sqlite> .table user
点命令.table相当于下面的查询语句。
sqlite> SELECT name FROM sqlite_schema ...> WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ...> ; user
sqlite_master是SQLite中的一个特殊表，其中包含了数据库的schema信息。你可以查询这个表以获取表的创建语句和索引信息。
sqlite> .mode table sqlite> select * from sqlite_schema; +-------+-----------+----------+----------+--------------------------------------+ | type | name | tbl_name | rootpage | sql | +-------+-----------+----------+----------+--------------------------------------+ | table | user | user | 2 | CREATE TABLE user(name text,age int) | | index | user_name | user | 3 | CREATE INDEX user_name on user(name) | +-------+-----------+----------+----------+--------------------------------------+
使用.indexes查看索引，使用.schema查看schema详情。
sqlite> .indexes user_name sqlite> .schema CREATE TABLE user(name text,age int); CREATE INDEX user_name on user(name);
结果写出到文件
使用.output filename命令将查询结果写入指定文件。下面是示例，先使用.mode json更改输出为JSON格式，然后查询表写出到sql_result.json。
sqlite> .output sql_result.json sqlite> .mode json sqlite> select * from user; sqlite> .exit $ cat sql_result.json [{"name":"aLang","age":20}, {"name":"Darcy","age":30}, {"name":"XiaoMing","age":40}]
写出并打开EXCEL
使用.excel会让下一个查询语句输出到Excel中。
sqlite> .excel sqlite> select * from sqlite_schema;
结果写出到文件
sqlite> .output sql_result.txt sqlite> select * from sqlite_schema; sqlite> select * from user;
读取运行SQL脚本
使用.read可以读取指定文件中的SQL语句并运行，这在需要批量执行SQL脚本的场景中非常有用。
创建SQL文件：
$ echo "select * from user" > sql_query.sql $ cat sql_query.sql select * from user $ ./sqlite3 my_sqlite.db SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for usage hints. sqlite> .mode table sqlite> .read sql_query.sql +----------+-----+ | name | age | +----------+-----+ | aLang | 20 | | Darcy | 30 | | XiaoMing | 40 | +----------+-----+ sqlite>
SQLite备份与恢复
在涉及数据库操作时，备份和恢复至关重要，用于防止数据丢失和确保数据持续性。SQLite提供简单方法备份和恢复数据库。在SQLite中可通过导出整个数据库为一个SQL脚本来备份，此功能用.dump命令实现。
$ ./sqlite3 my_sqlite.db SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for usage hints. sqlite> .output backup.sql sqlite> .dump sqlite> .exit $ cat backup.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE user(name text,age int); INSERT INTO user VALUES('aLang',20); INSERT INTO user VALUES('Darcy',30); INSERT INTO user VALUES('XiaoMing',40); CREATE INDEX user_name on user(name); COMMIT;
这将导出整个my_sqlite.db数据库到backup.sql文件中。此SQL文件包含了重建数据库所需的所有SQL语句。要恢复数据库，只需在sqlite3中运行这个脚本。
示例：恢复数据到库my_sqlite_2中。
$ ./sqlite3 my_sqlite_2.db SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for usage hints. sqlite> .read backup.sql sqlite> select * from user; aLang|20 Darcy|30 XiaoMing|40
这将执行backup.sql文件中的所有SQL语句，重建数据库。通过以上的备份与恢复方法，你可以确保你的SQLite数据库资料得到可靠的保护，且在需要时能够迅速恢复。
SQLite可视化工具
命令行操作不太直观，若喜欢可视化操作，可下载SQLite Database Browser进行操作，下载页面：https://sqlitebrowser.org/dl/
附录
SQLite常用函数列表，见名知意不写注释了。
| Function 1 | Function 2 | Function 3 | Function 4 |
|---|---|---|---|
| abs(X) | changes() | char(X1,X2,…,XN) | coalesce(X,Y,…) |
| concat(X,…) | concat_ws(SEP,X,…) | format(FORMAT,…) | glob(X,Y) |
| hex(X) | ifnull(X,Y) | iif(X,Y,Z) | instr(X,Y) |
| last_insert_rowid() | length(X) | like(X,Y) | like(X,Y,Z) |
| likelihood(X,Y) | likely(X) | load_extension(X) | load_extension(X,Y) |
| lower(X) | ltrim(X) | ltrim(X,Y) | max(X,Y,…) |
| min(X,Y,…) | nullif(X,Y) | octet_length(X) | printf(FORMAT,…) |
| quote(X) | random() | randomblob(N) | replace(X,Y,Z) |
| round(X) | round(X,Y) | rtrim(X) | rtrim(X,Y) |
| sign(X) | soundex(X) | sqlite_compileoption_get(N) | sqlite_compileoption_used(X) |
| sqlite_offset(X) | sqlite_source_id() | sqlite_version() | substr(X,Y) |
| substr(X,Y,Z) | substring(X,Y) | substring(X,Y,Z) | total_changes() |
| trim(X) | trim(X,Y) |
参考
- SQLite开源代码：https://www.sqlite.org/cgi/src/dir?ci=trunk
- SQLite文件格式介绍：https://sqlite.org/fileformat2.html
- SQLite可视化工具：https://sqlitebrowser.org/dl/
- SQL函数文档：https://www.sqlite.org/lang_corefunc.html
引用链接
[1] SQLite官方页面: https://sqlite.org/download.html
