SQLite 入门教程

不是 MySQL 用不起，而是 SQLite 更有性价比，绝大多数的 Web 应用 SQLite 都可以满足。

SQLite Logo

SQLite 是一个用 C 语言编写的开源、轻量级、快速、独立且高可靠性的 SQL 数据库引擎，它提供了功能齐全的数据库解决方案。SQLite 几乎可以在所有的手机和计算机上运行，它被嵌入到无数人每天都在使用的众多应用程序中。

此外，SQLite 还具有稳定的文件格式、跨平台能力和向后兼容性等特点。SQLite 的开发者承诺，至少在 2050 年之前保持该文件格式不变。

本文将介绍 SQLite 的基础知识和使用方法。

SQLite 安装

在 SQLite 官方页面(https://sqlite.org/download.html) 下载适合你目标系统的压缩包。

下载并解压后，无论是在 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 的使用场景：

  1. 嵌入式设备和物联网

    SQLite 不需要额外的管理或服务启动，非常适合用在手机、电视、机顶盒、游戏机、相机、手表等智能设备上。

  2. 网站

    多数低流量网站可以使用 SQLite 作为数据库。根据官方网站的介绍，通常每天访问量少于 10 万次的网站都可以很好地运行 SQLite。SQLite 的官方网站（https://www.sqlite.org/）自身就使用 SQLite 作为数据库引擎，每天处理大约 50 万 HTTP 请求，其中约 15-20% 的请求涉及数据库查询

  3. 数据分析

    SQLite3 命令行工具能方便地与 CSV 和 Excel 文件进行交互操作，适合分析大数据集。同时，许多语言（如 Python）都内置了 SQLite 支持，可以轻松编写脚本进行数据操作。

  4. 缓存

    SQLite 可以作为应用服务的缓存，减轻对中心数据库的压力。

  5. 内存或者临时数据库

    得益于 SQLite 的简单快速，非常使用程序演示或者日常测试。

SQLite 不适合的场景包括：

  1. 需要通过网络访问数据库的情况。SQLite 是一个本地文件数据库，没有提供远程访问功能。
  2. 要求高可用性和可扩展性的场合。SQLite 简单易用，但不可扩展。
  3. 数据量极大时。尽管 SQLite 数据库大小的限制高达 281 TB，但所有数据都必须存储在单个磁盘上。
  4. 写入操作高并发时。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 数据类型

SQLite3 数据库中的每一列都被指定为以下类型中的一种：

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

SQLite 会将传统 SQL 实现中的常见数据类型名称转换为自己的类型，下表展示了SQLite能够接受的数据类型名称中的一小部分。

CREATE TABLE语句或CAST表达式中的类型名示例 转换结果 Rule Used To Determine Affinity
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED
BIG
INT
INT2
INT8		 INTEGER 1
CHARACTER(20)
VARCHAR(255)
VARYING
CHARACTER(255)
NCHAR(55)
NATIVE
CHARACTER(70)
NVARCHAR(100)
TEXT CLOB		 TEXT 2
BLOB no datatype specified BLOB 3
REAL DOUBLE DOUBLE PRECISION FLOAT REAL 4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME NUMERIC 5

同时要注意的是，SQLite 会忽略跟随在类型名称后面的带括号的数值参数（例如："VARCHAR(255)"，因为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)

参考

  1. SQLite 开源代码：https://www.sqlite.org/cgi/src/dir?ci=trunk
  2. SQLite 文件格式介绍：https://sqlite.org/fileformat2.html
  3. SQLite 可视化工具：https://sqlitebrowser.org/dl/
  4. SQL 函数文档：https://www.sqlite.org/lang_corefunc.html
目录

CSS Powered by Bulma.css | Copyright © 2018-2023 程序猿阿朗

本站文章谢绝转载