SQLite3 实践教程

图片来源

Opensource.com

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

[下载我们的 SQLite3 速查表]

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

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

安装

您的系统上可能已经安装了 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，您可以稍后动态地将它们关联起来。