使用 SQL 对数据库执行读写操作

1. 准备工作

您使用的许多应用都直接在设备上存储数据。“时钟”应用可存储周期性闹钟，Google 地图应用可保存近期搜索记录的列表，而“通讯录”应用可允许您添加、编辑和移除联系人信息。

数据持久化（在设备上存储或持久保留数据）是 Android 开发的重要环节。持久性数据可确保关闭应用后不会丢失用户生成的内容，并保存从互联网下载的数据，因此日后无需重新下载。

SQLite 是 Android SDK 为 Android 应用提供的一种常见持久性数据保存方式。SQLite 提供了一个关系型数据库，可让您使用类似于使用 Kotlin 类设计数据结构的方式来表示数据。此 Codelab 介绍了 SQL（结构化查询语言）基础知识，虽然它不是实际编程语言，但只需使用几行代码，即可通过一种简单灵活的方式来读取和修改 SQLite 数据库。

在掌握 SQL 基础知识之后，您就可以在本单元的后面部分使用 Room 库为您的应用添加持久性功能。

2. 关系型数据库的主要概念

什么是数据库？

如果您熟悉 Google 表格等电子表格程序，那么应该已经对数据库有了基本的了解。

电子表格由单独的数据表格或同一工作簿中的各个电子表格组成。

53f9d2168dd215a.png

每个表格均由列和行组成，其中列定义了数据所表示的内容，而行用于呈现各个项目，内含各列的值。例如，您可以用列定义学生的 ID、姓名、专业和成绩。

10f380969af48ad7.png

每行包含单个学生的数据，以及每列的值。

d57c3aae74e36df7.png

关系型数据库的工作原理相同。

  • 表格定义了要代表的数据的简要分组，例如学生和教授。
  • 列用来定义表格中每一行包含的数据。
  • 行包含实际数据，这些数据由表中每列的值组成。

关系型数据库的结构也与您已了解的 Kotlin 类和对象对应。

data class Student(
    id: Int,
    name: String,
    major: String,
    gpa: Double
)
  • 类就好比表格，可为您要在应用中表示的数据建模。
  • 属性就好比列，用于定义类的每个实例应包含的特定数据。
  • 对象就好比行，它是实际数据。对象包含类中定义的每个属性的值，就像行包含数据表中定义的每个列的值一样。

就像一个电子表格可以包含多个工作表，一个应用可以包含多个类一样，一个数据库也可以包含多个表格。如果数据库可对表之间的关系进行建模，则称为关系型数据库。例如，研究生可以有一位教授作为博士顾问，而一位教授则是多名学生的博士顾问。

633f2ba54b3e6ed3.png

关系型数据库中的每个表均包含用于行的唯一标识符（例如，每行中的值都是自动递增整数的一个列）。此标识符称为主键。

如果某个表引用了另一个表的主键，则该主键称为外键。出现外键表示表之间存在关系。

什么是 SQLite？

SQLite 是一种常用的关系型数据库。具体而言，SQLite 指的是使用结构化查询语言（称为 SQL，有时简短发音为“sequel”）进行关系型数据库管理的一种轻量级 C 库。

您无需学习 C 或任何全新的编程语言即可处理关系型数据库。SQL 是一种使用几行代码向关系型数据库添加数据和从中检索数据的简单方法。

使用 SQLite 表示数据

在 Kotlin 中，您熟悉了 Int 和 Boolean 等数据类型。SQLite 数据库也使用数据类型！数据表列必须有具体的数据类型。下表列出了常见的 Kotlin 数据类型与对应的 SQLite 数据类型。

Kotlin 数据类型

SQLite 数据类型

Int

INTEGER

String

VARCHAR 或 TEXT

Boolean

BOOLEAN

Float、Double

REAL

数据库中的表和每个表中的列统称为架构。在下一部分中，您将下载起始数据集，并详细了解其架构。

3. 下载起始数据集

此 Codelab 的数据库适用于假想的电子邮件应用。此 Codelab 使用常见示例（例如对邮件进行排序和过滤，或按主题文本或发件人搜索）来演示 SQL 的所有强大功能。此示例还会确保您在下一个在线课程中使用 Room 之前，先了解可能会在应用中看到的各种场景。

请点击此处，从 SQL Basics GitHub 代码库的 compose 分支下载起始项目。

使用 Database Inspector

如需使用 Database Inspector，请执行以下步骤：

  1. 在 Android Studio 中运行 SQL Basics 应用。应用启动时，您会看到以下屏幕。

d690089213a4532a.png

  1. 在 Android Studio 中，依次点击 View > Tool Windows > App Inspection。

60fc0624e36ae5c5.png

现在，底部会显示一个名为 App Inspection 的新标签页，其中 Database Inspector 标签页处于选中状态。还有另外两个标签页，但您无需使用它们。加载过程可能需要几秒钟的时间，然后您会在左侧看到一个数据表列表，您可以选择对其中的数据表运行查询。

62e40b1283305adc.png

  1. 点击 Open New Query Tab 按钮打开一个窗格，以便对数据库运行查询。

240118470c9474a5.png

email 表格包含以下 7 列：

  • id：主键。
  • subject：电子邮件的主题行。
  • sender：电子邮件的来源电子邮件地址。
  • folder：邮件所在的文件夹，例如“收件箱”或“垃圾邮件”。
  • starred：用户是否为电子邮件添加了星标。
  • read：用户是否阅读了电子邮件。
  • received：收到电子邮件时的时间戳。

4. 使用 SELECT 语句读取数据

SQL SELECT 语句

SQL 语句（有时称为查询）可用于读取或操纵数据库。

您可使用 SELECT 语句从 SQLite 数据库读取数据。简单的 SELECT 语句包含 SELECT 关键字，后跟列名称，接着是 FROM 关键字，然后是表名称。每个 SQL 语句都以英文分号 (;) 结尾。

bf764b3332b1664c.png

SELECT 语句也可以返回多个列的数据。您必须使用英文逗号分隔列名。

d665cb1ed84a1364.png

如果要选择表中的每个列，请使用通配符 (*) 代替列名称。

c12711257d24fda2.png

无论是哪种情况，这样的简单 SELECT 语句都会返回表中的每一行。您只需指定希望其返回的列名称。

使用 SELECT 语句可读取电子邮件数据

电子邮件应用需要执行的一项主要操作是显示邮件列表。借助 SQL 数据库，您可以通过 SELECT 语句获取此信息。

  1. 确保在 Database Inspector 中选中 email 表。

128347218e04faa0.png

  1. 首先，尝试选择 email 表中各行的每一列。
SELECT * FROM email;
  1. 点击文本框右下角的 Run 按钮。您会发现系统会返回整个 email 表。

6c99eb1ea42233a7.png

  1. 现在，请尝试仅选择每行的主题。
SELECT subject FROM email;
  1. 请注意，查询再次返回每一行，但只有这一列的值。

dd3d50f00e05d506.png

  1. 您也可以选择多列。尝试选择主题和发件人。
SELECT subject, sender FROM email;
  1. 您会发现此查询会返回 email 表中的每一行，但仅返回 subject 和 sender 列的值。

68b714377cc3697f.png

恭喜！您刚刚执行了第一个查询。不错，您已经踏出第一步，就像完成了 SQL 领域的“Hello World”应用。

若要编写更具体的 SELECT 语句，您可以添加子句来指定数据的子集，甚至可以更改输出的格式。在以下部分中，您将了解 SELECT 语句的常用子句以及如何设置数据格式。

5. 使用 SELECT 语句搭配聚合函数和 DISTINCT 关键字

使用聚合函数减少列数

SQL 语句不限于返回行。SQL 提供多种函数，可对特定列执行操作或计算，例如查找最大值或计算特定列可能的唯一值数量。这些函数称为聚合函数。您可以返回特定列的单个值，而不是返回特定列的所有数据。

SQL 聚合函数示例包括：

  • COUNT()：返回与查询匹配的行总数。
  • SUM()：返回所选列中所有行的值的总和。
  • AVG()：返回所选列中所有值的平均值。
  • MIN()：返回所选列中的最小值。
  • MAX()：返回所选列中的最大值。

您可以不直接使用列名称，而是调用聚合函数，并在圆括号里传入列名称作为参数。

6730a62d583a0d9.png

调用聚合函数会返回单个值，而不是该列在表中每一行的值。

当您不需要读取数据库中的所有数据时，聚合函数可以高效地计算值。例如，您可能想要计算某个列中的值的平均值，而无需将整个数据库加载到列表中并手动执行该操作。

让我们以 email 表格为例，了解一下一些聚合函数的实际运用：

  1. 应用可能想要获取所收到电子邮件的总数。您可以使用 COUNT() 函数和通配符 (*) 来实现此目的。
SELECT COUNT(*) FROM email;
  1. 查询会返回单个值。您可以完全使用 SQL 查询执行此操作，无需使用任何 Kotlin 代码来手动统计行数。

1717e824b948609d.png

  1. 如需获取最新邮件的时间，您可以对 received 列使用 MAX() 函数，因为最近的 Unix 时间戳是最大值。
SELECT MAX(received) FROM email;
  1. 该查询会返回单个结果，即 received 列中的最大（最新）时间戳。

d9778e3a3ef33931.png

使用 DISTINCT 过滤重复结果

选择一列时，您可以在前面添加 DISTINCT 关键字。如果您想从查询结果中移除重复项，此方法会很有用。

20fdd229ad44926b.png

例如，许多电子邮件应用都具有地址自动补全功能。您可能想要在一个列表中添加和显示所有发件人地址。

  1. 运行以下查询，以返回每行的 sender 列。
SELECT sender FROM email;
  1. 您会发现结果包含许多重复项。这绝对不是理想的体验！

752f20baacde5346.png

  1. 在 sender 列之前添加 DISTINCT 关键字并重新运行查询。
SELECT DISTINCT sender FROM email;
  1. 请注意，结果现在包含的内容少多了，而且每个值都是唯一的。

f7ec818c72d5c65b.png

您还可以使用 DISTINCT 关键字在聚合函数中的列名称前面。

23d94cacbdbb5be2.png

假设您想了解数据库中唯一发件人的数量。您可以使用 sender 列的 COUNT() 聚合函数和 DISTINCT 关键字统计唯一发件人的数量。

  1. 执行 SELECT 语句，并将 DISTINCT sender 传递给 COUNT() 函数。
SELECT COUNT(DISTINCT sender) FROM email;
  1. 您会发现该查询显示有 14 个唯一身份发件人。

6e1362de8bb3915f.png

6. 使用 WHERE 子句过滤查询结果

许多电子邮件应用都提供了根据特定条件（例如数据、搜索字词、文件夹、发件人等）对显示的邮件进行过滤的功能。对于这些类型的用例，您可以向 SELECT 查询添加 WHERE 子句。

您可以在表格名称后面另起一行添加 WHERE 关键字，后跟表达式。在编写更复杂的 SQL 查询时，通常每个子句各占一行，以提高可读性。

2c1a6a495ceb4ab5.png

此查询会对每个选定行执行布尔检查；如果检查返回 true，则会在查询结果中包含该行。如果检查返回 false，则不会在查询结果中包含该行。

例如，某个电子邮件应用可能会提供垃圾邮件过滤器、垃圾箱过滤器、草稿过滤器，或让用户自行创建过滤器。以下说明使用 WHERE 子句执行此操作：

  1. 运行 SELECT 语句以返回 email 表中的所有列 (*)，并添加 WHERE 子句来检查条件 folder = 'inbox'。这并不是拼写错误，您可以在 SQL 中使用单个等号来检查内容是否相等，并使用单引号（而不是双引号）来表示字符串值。
SELECT * FROM email
WHERE folder = 'inbox';
  1. 结果只会返回用户收件箱中邮件的行。

24dd8adef69a3a01.png

包含 WHERE 子句的逻辑运算符

SQL WHERE 子句不限于单个表达式。您可以使用 AND 关键字来仅包含同时满足这两个条件的结果，等同于 Kotlin and 运算符 (&&)。

81da61e4e3c52671.png

或者，您也可以使用 OR 关键字在结果中包含满足任一条件的行，等同于 Kotlin or 运算符 (|| )。

3a3ab0cea06f1949.png

为确保可读性，您还可以使用 NOT 关键字来否定表达式。

27300a0a38ef0343.png

许多电子邮件应用允许使用多个过滤器，例如仅显示未读邮件。

对 email 表格尝试运行以下更复杂的 WHERE 子句：

  1. 除了仅返回用户收件箱中的邮件之外，还可以尝试将结果限制为未读邮件，即 read 列中的值为 false。
SELECT * FROM email
WHERE folder = 'inbox' AND read = false;
  1. 您会发现运行查询后，结果将仅包含用户收件箱中的未读电子邮件。

14e1e74c282d7939.png

  1. 返回位于 important 文件夹 OR（或者）已加星标（即 starred = true）的所有电子邮件。这样的话，各个文件夹中的邮件只要已加星标，就会纳入结果中。
SELECT * FROM email
WHERE folder = 'important' OR starred = true;
  1. 观察结果。

733c8450f35bf71.png

使用 LIKE 搜索文字

使用 WHERE 子句可以执行的一项超级实用操作是搜索特定列中的文本。如需实现此结果，您需要指定列名称，后跟 LIKE 关键字，然后是搜索字符串。

6692c0d491b6f9af.png

搜索字符串以百分号 (%) 开头，接着是要搜索的文本（搜索字词），再后跟百分号 (%)。

c69c15f654645ee2.png

如果要搜索前缀（以特定文本开头的结果），请省略第一个百分号 (%)。

fbe6a94daaf173ae.png

或者，如果您要搜索后缀，请省略最后一个百分号 (%)。

141f567c9cbc4029.png

在很多情况下，应用都可以使用文本搜索，例如搜索主题行中包含特定文本的电子邮件，或者在用户输入内容时更新自动补全建议。

按照以下说明操作，可以在查询 email 表格时使用文本搜索。

  1. 这个数据库里的发件人和莎士比亚笔下的角色一样，也喜欢谈论蠢货 (fool)。运行以下查询来获取主题行中包含“fool”文本的电子邮件总数。
SELECT COUNT(*) FROM email
WHERE subject LIKE '%fool%';
  1. 观察结果。

22ebd3ef0876f552.png

  1. 运行以下查询，返回主题以 fool 一词结尾的所有行中的所有列。
SELECT * FROM email
WHERE subject LIKE '%fool';
  1. 您会发现该查询会返回两行。

709561928f955053.png

  1. 运行以下查询，返回 sender 列中以字母 h 开头的独特值。
SELECT DISTINCT sender FROM email
WHERE sender LIKE 'h%';
  1. 请注意，查询将返回三个值：helena@example.com、hyppolytus@example.com 和 hermia@example.com。

7b21887c665288a3.png

7. 对结果进行分组、排序和限制结果数量

使用 GROUP BY 对结果进行分组

您刚刚学习了如何使用聚合函数和 WHERE 子句过滤和减少结果。SQL 提供了一些其他子句，可以帮助您设置查询结果的格式。这些子句包括对结果进行分组、排序和限制结果数量。

您可以使用 GROUP BY 子句对结果进行分组，找出在特定列有相同值的所有行，并在结果中将这些行归为一组。此子句不会更改结果，而只会更改返回结果的顺序。

如需向 SELECT 语句添加 GROUP BY 子句，请添加 GROUP BY 关键字，后跟要作为结果分组依据的列名称。

6be095e981498bbf.png

一种常见用例是将 GROUP BY 子句与聚合函数搭配使用，以便按照不同的分区（例如列的值）划分聚合函数的结果。下面是一个示例。假设您想要获取以下每个文件夹中的电子邮件数量：'inbox'、'spam' 等。您可以同时选择 folder 列和 COUNT() 聚合函数，并在 GROUP BY 子句中指定 folder 列。

  1. 执行以下查询，以选择 folder 列和 COUNT() 聚合函数的结果。使用 GROUP BY 子句按 folder 列中的值对结果进行划分。
SELECT folder, COUNT(*) FROM email
GROUP BY folder;
  1. 观察结果。查询会返回每个文件夹的电子邮件总数。

9971ecbdcaf4d3c1.png

使用 ORDER BY 对结果进行排序

您还可以使用 ORDER BY 子句对查询结果进行排序，更改查询结果的顺序。添加 ORDER BY 关键字，后跟列名称，然后是排序方向。

9cf561c6346ed6e0.png

默认情况下，排序方向是升序，您可以在 ORDER BY 子句中将其省略。如果您希望结果按降序排序，请在列名称后面添加 DESC。

您可能会希望电子邮件应用首先显示最新的电子邮件。您可以按照以下说明使用 ORDER BY 子句执行此操作。

  1. 添加 ORDER BY 子句，以根据 received 列对未读电子邮件进行排序。由于默认为升序（由低到高或由旧到新），因此需要使用 DESC 关键字。
SELECT * FROM email
ORDER BY received DESC;
  1. 观察结果。

d5149fcf49b32034.png

您可以将 ORDER BY 子句与 WHERE 子句一起使用。假设用户希望搜索包含文本“fool”的旧电子邮件。他们可以对结果进行排序，首先按时间升序显示最早的电子邮件。

  1. 选择主题中包含文本“fool”的所有电子邮件，然后按升序对结果排序。由于顺序是升序，这是未指定任何顺序时的默认顺序，因此将 ASC 关键字与 ORDER BY 子句搭配使用是可选操作。
SELECT * FROM email
WHERE subject LIKE '%fool%'
ORDER BY received ASC;
  1. 您会发现系统返回过滤后的结果，并优先显示最早（received 列的值最小）的电子邮件。

b37f2910a587ffa1.png

使用 LIMIT 限制结果数量

到目前为止，所有示例都从数据库中返回符合查询的单一结果。在许多情况下，您只需要显示数据库中有限数量的行。您可以在查询中添加 LIMIT 子句，以仅返回特定数量的结果。添加 LIMIT 关键字，后跟要返回的行数上限。如果同时使用排序和限制数量功能，则 LIMIT 子句在 ORDER BY 子句之后。

122152adf15a9fca.png

您也可以选择添加 OFFSET 关键字，后跟另一个数字，表示要跳过的行数。例如，如果共有二十条结果，但您想忽略前十条结果，只返回后十条结果，则可以使用 LIMIT 10 OFFSET 10。

37ad836862573d55.png

在应用中，您可以仅返回用户收件箱中的前十封电子邮件，从而更快地加载电子邮件。然后，用户可以滚动查看后续电子邮件页面。以下说明使用 LIMIT 子句来实现此行为。

  1. 执行以下 SELECT 语句，按降序获取用户收件箱中的所有电子邮件，并仅显示前 10 条结果。
SELECT * FROM email
WHERE folder = 'inbox'
ORDER BY received DESC
LIMIT 10;
  1. 您会发现系统仅返回 10 调结果。

61a7c38c0c7d545c.png

  1. 修改并重新运行查询，以添加值为 10 的 OFFSET 关键字。
SELECT * FROM email
WHERE folder = 'inbox'
ORDER BY received DESC
LIMIT 10 OFFSET 10;
  1. 查询以降序返回 10 条结果。不过，查询会跳过前 10 条结果。

7d1d31276ad8cfeb.png

8. 在数据库中插入、更新和删除数据

将数据插入数据库

除了从数据库读取数据外，还有其他 SQL 语句可将数据写入数据库。数据库中首先必须有数据，才能读取数据，对吧？

您可以使用 INSERT 语句向数据库添加新行。INSERT 语句以 INSERT INTO 开头，后跟要插入新行的表名称。VALUES 关键字另起一行，后跟一对圆括号，其中包含以英文逗号分隔的值列表。您需要按照数据库列的相同顺序列出值。

33563f4ed898959a.png

假设用户收到新电子邮件，我们需要将其存储在应用的数据库中。我们可以使用 INSERT 语句向 email 表添加新行。

  1. 执行 INSERT 语句，并提供新邮件的以下数据。由于该电子邮件是新邮件，因此未读并最初显示在收件箱 folder 中。为 id 列提供 NULL 值，这意味着将使用下一个可用的自动递增整数自动生成 id。
INSERT INTO email
VALUES (
    NULL, 'Lorem ipsum dolor sit amet', 'sender@example.com', 'inbox', false, false, CURRENT_TIMESTAMP
);
  1. 您会发现该结果已插入数据库，并且 id 为 44。
SELECT * FROM email
WHERE sender = 'sender@example.com';

98eda3e5822acc52.png

更新数据库中的现有数据

将数据插入表格后，您仍可以在日后进行更改。您可以使用 UPDATE 语句更新一个或多个列的值。UPDATE 语句以 UPDATE 关键字开头，后跟表名称，然后是 SET 子句。

15a2c309405c1442.png

SET 子句包含 SET 关键字，后跟要更新的列的名称。

bc255ece789859f.png

UPDATE 语句通常包含一个 WHERE 子句，用于指定要更新的单行或多行，这行/这些行会在各列更新为指定的“列-值”对。

b861ca0e8cdbdf37.png

例如，如果用户想要将电子邮件标记为已读，您可以使用 UPDATE 语句来更新数据库。按照以下说明操作可将上一步中插入的电子邮件标记为已读。

  1. 执行以下 UPDATE 语句，对 id 为 44 的行进行设置，使 read 列的值为 true。
UPDATE email
SET read = true
WHERE id = 44;
  1. 对该行运行 SELECT 语句以验证结果。
SELECT read FROM email
WHERE id = 44;
  1. 您会发现 read 列的值现在为 1（表示“true”），而不是 0（表示“false”）。

803d0287d4360947.png

从数据库中删除行

最后，您可以使用 SQL DELETE 语句从表中删除一行或多行。DELETE 语句以 DELETE 关键字开头，后跟 FROM 关键字，然后是表名称，接着是 WHERE 子句（用于指定要删除哪一行或哪些行）。

2b11c74c90ba9b60.png

以下说明使用 DELETE 语句从数据库中删除先前插入后续经过更新的行。

  1. 执行以下 DELETE 语句，从数据库中删除 id 为 44 的行。
DELETE FROM email
WHERE id = 44;
  1. 使用 SELECT 语句验证您的更改。
SELECT * FROM email
WHERE id = 44;
  1. 您会发现，id 为 44 的行已不存在。

5cc91726691debfc.png

9. 总结

恭喜！您学到了很多知识！您现在可以使用 SELECT 语句从数据库读取数据，包括使用 WHERE、GROUP BY、ORDER BY 和 LIMIT 子句过滤结果。您还了解了常用的聚合函数、用于指定唯一结果的 DISTINCT 关键字，以及对列中的值执行文本搜索的 LIKE 关键字。最后，您学习了如何使用 INSERT、UPDATE 和 DELETE 语句在数据表中插入、更新和删除行。

这些技能可以直接运用到 Room 中；在掌握了 SQL 知识之后，您就能在未来开发的应用中更得心应手地使数据持久化。

SELECT 语句语法：

2db1588943ae3bf5.png

10. 了解更多内容

虽然我们主要关注 SQL 基础知识以及一些 Android 开发常见用例，但 SQL 还有很多其他用途。您可以参阅以下资源，当做强化学习成果的补充参考资料，也可以进一步了解该主题。