SQLite3 实战教程

应用程序经常保存数据。无论您的用户创建简单的文本文档、复杂的图形布局、游戏进度，还是复杂的客户和订单号列表，软件通常都意味着数据的生成。有很多方法可以存储数据以供重复使用。您可以将文本转储到配置文件格式，例如 INI、YAML、XML 或 JSON，您可以写出原始二进制数据，或者您可以将数据存储在结构化数据库中。SQLite 是一种自包含、轻量级的数据库，可以轻松地创建、解析、查询、修改和传输数据。

[下载我们的 SQLite3 速查表]

SQLite 已被奉献给公共领域，这在技术上意味着它不受版权保护，因此不需要许可证。如果您需要许可证，您可以购买所有权保证。SQLite 非常普及，估计有 1 万亿 个 SQLite 数据库正在使用中。这包括每个 Android 和 iOS 设备、每台 macOS 和 Windows 10 计算机、大多数 Linux 系统、每个基于 Webkit 的 Web 浏览器、现代电视、汽车多媒体系统以及无数其他软件应用程序中的多个数据库。

总而言之，这是一个可靠且简单的系统，用于存储和组织数据。

安装

您的系统上可能已经安装了 SQLite 库，但您需要安装其命令行工具才能直接使用它。在 Linux 上，您可能已经安装了这些工具。这些工具提供的命令是 sqlite3（不仅仅是 sqlite）。

如果您在 Linux 或 BSD 上没有安装 SQLite，您可以从您的软件存储库或端口树中安装它，或者从源代码或编译后的二进制文件下载并安装它。

在 macOS 或 Windows 上，您可以从 sqlite.org 下载并安装 SQLite 工具。

使用 SQLite

通常通过编程语言与数据库进行交互。因此，有适用于 Java、Python、Lua、PHP、Ruby、C++ 和许多其他语言的 SQLite 接口（或“绑定”）。但是，在使用这些库之前，了解数据库引擎实际发生的情况以及您选择数据库的重要性很有帮助。本文向您介绍 SQLite 和 sqlite3 命令，以便您熟悉此数据库如何处理数据的基本知识。

与 SQLite 交互

您可以使用 sqlite3 命令与 SQLite 交互。此命令提供了一个交互式 shell，因此您可以查看和更新您的数据库。

$ sqlite3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

该命令将您置于 SQLite 子 shell 中，因此您的提示符现在是 SQLite 提示符。您常用的 Bash 命令在这里不起作用。您必须使用 SQLite 命令。要查看 SQLite 命令列表，请键入 .help

sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
[...]

其中一些命令是二进制的，而另一些命令则需要唯一的参数（如文件名、路径等）。这些是您的 SQLite shell 的管理命令，而不是数据库查询。数据库以结构化查询语言 (SQL) 接受查询，许多 SQLite 查询与您可能已经从 MySQL 和 MariaDB 数据库中了解到的相同。但是，数据类型和函数有所不同，因此如果您熟悉其他数据库，请密切注意细微的差异。

创建数据库

启动 SQLite 时，您可以选择在内存中打开提示符，也可以选择要打开的数据库

$ sqlite3 mydatabase.db

如果您还没有数据库，您可以在 SQLite 提示符下创建一个

sqlite> .open mydatabase.db

您现在在硬盘驱动器上有一个空文件，可以将其用作 SQLite 数据库。文件扩展名 .db 是任意的。您也可以使用 .sqlite，或任何您想要的。

创建表

数据库包含表，可以将其可视化为电子表格。有一系列行（在数据库中称为记录）和列。行和列的交集称为字段。

结构化查询语言 (SQL) 以其提供的功能命名：一种以可预测且一致的语法查询数据库内容以接收有用结果的方法。SQL 的阅读方式很像普通的英语句子，如果有点机械化。目前，您的数据库是空的，没有任何表。

您可以使用 CREATE 查询创建表。将此与 IF NOT EXISTS 语句结合使用很有用，它可以防止 SQLite 覆盖现有表。

您无法在 SQLite 中创建空表，因此在尝试 CREATE 语句之前，您必须考虑表将存储哪种数据。在此示例中，我将创建一个名为 member 的表，其中包含以下列

• 唯一标识符
• 人员姓名
• 数据条目的日期和时间

唯一 ID

始终最好通过唯一的数字引用记录，幸运的是 SQLite 识别到这一点，并在名为 rowid 的列中自动为您执行此操作。

创建此字段不需要 SQL 语句。

数据类型

对于我的示例表，我正在创建一个 name 列来保存 TEXT 数据。为了防止在未在指定字段中输入数据的情况下创建记录，您可以添加 NOT NULL 指令。

创建此字段的 SQL 是：name TEXT NOT NULL

SQLite 中有五种数据类型（实际上是存储类）

• TEXT：文本字符串
• INTEGER：整数
• REAL：浮点数（无限小数位）
• BLOB：二进制数据（例如，.jpeg 或 .webp 图像）
• NULL：空值

日期和时间戳

SQLite 包括一个方便的日期和时间戳函数。它本身不是数据类型，而是 SQLite 中的一个函数，它根据您所需的格式生成字符串或整数。在此示例中，我将其保留为默认值。

创建此字段的 SQL 是：datestamp DATETIME DEFAULT CURRENT_TIMESTAMP

表创建 SQL

在 SQLite 中创建此示例表的完整 SQL

sqlite> CREATE TABLE
...> IF NOT EXISTS
...> member (name TEXT NOT NULL,
...> datestamp DATETIME DEFAULT CURRENT_TIMESTAMP

在此代码示例中，我在语句的逻辑子句之后按了 Return 键，使其更易于阅读。除非以分号 (;) 结尾，否则 SQLite 不会运行您的命令。

您可以使用 SQLite 命令 .tables 验证表是否已创建

sqlite> .tables
member

查看表中的所有列

您可以使用 PRAGMA 语句验证表包含的列和行

sqlite> PRAGMA table_info(member);
0|name|TEXT|1||0
1|datestamp|CURRENT_TIMESTAMP|0||0

数据输入

您可以使用 INSERT SQL 关键字向新表中填充一些示例数据

> INSERT INTO member (name) VALUES ('Alice');
> INSERT INTO member (name) VALUES ('Bob');
> INSERT INTO member (name) VALUES ('Carol');
> INSERT INTO member (name) VALUES ('David');

验证表中的数据

> SELECT * FROM member;
Alice|2020-12-15 22:39:00
Bob|2020-12-15 22:39:02
Carol|2020-12-15 22:39:05
David|2020-12-15 22:39:07

一次添加多行

现在创建第二个表

> CREATE table IF NOT EXISTS linux (
...> distro TEXT NOT NULL)

使用一些示例数据填充它，这次使用一个小的 VALUES 快捷方式，以便您可以在一个命令中添加多行。VALUES 关键字需要括号中的列表，但可以接受以逗号分隔的多个列表

> INSERT INTO linux (distro)
...> VALUES ('Slackware'), ('RHEL'),
...> ('Fedora'),('Debian');

更改表

您现在有两个表，但到目前为止，两者之间没有关系。它们各自包含独立的数据，但您可能需要将第一个表的成员与第二个表中列出的特定项目关联起来。

为此，您可以为第一个表创建一个新列，该列对应于第二个表中的某些内容。由于这两个表都是使用唯一标识符设计的（自动的，感谢 SQLite），因此连接它们的最简单方法是将其中一个表的 rowid 字段用作另一个表的选择器。

在第一个表中创建一个新列，以表示第二个表中的值

> ALTER TABLE member ADD os INT;

使用 linux 表的唯一 ID，为每个成员分配一个发行版。由于记录已经存在，因此您使用 UPDATE SQL 关键字而不是 INSERT。具体来说，您想要选择一行，然后更新一列的值。在语法上，这有点相反，更新首先发生，选择匹配最后发生

> UPDATE member SET os=1 WHERE name='Alice';

对 member 表中的其他名称重复此过程，只是为了用数据填充它。为了多样性，在四行中分配三个不同的发行版（其中一个加倍）。

连接表

既然这两个表相互关联，您可以使用 SQL 显示关联的数据。数据库中有许多类型的连接，但一旦您了解基本知识，就可以尝试所有连接。这是一个基本的连接，用于将 member 表的 os 字段中找到的值与 linux 表的 id 字段关联起来

> SELECT * FROM member INNER JOIN linux ON member.os=linux.rowid;
Alice|2020-12-15 22:39:00|1|Slackware
Bob|2020-12-15 22:39:02|3|Fedora
Carol|2020-12-15 22:39:05|3|Fedora
David|2020-12-15 22:39:07|4|Debian

os 和 id 字段构成连接。

在图形应用程序中，您可以想象 os 字段可能由下拉菜单设置，下拉菜单的值从 linux 表的 distro 字段的内容中提取。通过为唯一但相关的数据集使用单独的表，您可以确保数据的一致性和有效性，并且由于 SQL，您可以在以后动态地关联它们。

了解更多

SQLite 是一款非常有用、自包含、可移植的开源数据库。学习以交互方式使用它是管理 Web 应用程序或通过编程语言库使用它的重要第一步。

如果您喜欢 SQLite，您也可以尝试同一作者 Richard Hipp 博士的 Fossil。

当您学习和使用 SQLite 时，手边有一份常用命令列表可能会有所帮助，因此请立即下载我们的 SQLite3 速查表！