使用 SQL 读取和写入数据库

1. 在开始之前

您使用的许多应用都会将数据直接存储在设备上。时钟应用会存储您设定的重复闹钟，Google 地图应用会保存您最近的搜索列表，而通讯录应用可让您添加、修改和移除联系人信息。

数据持久性 - 即将数据存储或持久保留在设备上 - 是 Android 开发的重要组成部分。持久数据可确保用户生成的内容在应用关闭时不会丢失，或从互联网下载的数据得到保存，以便以后无需重新下载。

SQLite 是 Android SDK 为 Android 应用提供的一种常见的数据持久化方式。SQLite 提供了一个关系型数据库，允许您以类似于使用 Kotlin 类来构建数据的方式来表示数据。本 Codelab 将教授 SQL（结构化查询语言）的基础知识，SQL 虽然不是一种真正的编程语言，但它提供了一种简单灵活的方式，只需几行代码即可读取和修改 SQLite 数据库。

在掌握了 SQL 的基础知识后，您就可以准备在本单元的后续内容中使用 Room 库为您的应用添加持久性。

2. 关系型数据库的关键概念

什么是数据库？

如果您熟悉 Google Sheets 等电子表格程序，那么您就已经熟悉数据库的基本类比。

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

1f2b00d3ca083c4a.png

每个表由列组成，这些列定义了数据表示什么，以及表示具有每个列值的单个项目的行。例如，您可以定义学生的 ID、姓名、专业和年级的列。

a441da5cc7be346b.png

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

6131d8a59996f521.png

关系型数据库的工作方式相同。

  • 表定义了您想要表示的数据的高级别分组，例如学生和教授。
  • 列定义了表中的每一行包含的数据。
  • 行包含实际数据，这些数据由表中每个列的值组成。

关系型数据库的结构也反映了您已经了解的 Kotlin 中的类和对象。

data class Student(
    id: Int,
    name: String,
    major: String,
    gpa: Double
)
  • 类就像表一样，对您希望在应用中表示的数据进行建模。
  • 属性就像列一样，定义了类的每个实例应包含的特定数据片段。
  • 对象就像行一样，是实际数据。对象包含类中定义的每个属性的值，就像行包含数据表中定义的每个列的值一样。

就像电子表格可以包含多个工作表，应用可以包含多个类一样，数据库可以包含多个表。当数据库可以对表之间的关系进行建模时，它就被称为关系型数据库。例如，一名研究生可能只有一位教授作为博士导师，而该教授是多名学生的博士导师。

7f1b56e05520dc3.png

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

当一个表引用另一个表的主键时，它被称为外键。外键的存在意味着表之间存在关系。

什么是 SQLite？

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

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

使用 SQLite 表示数据

在 Kotlin 中，您熟悉 Int 和 Boolean 等数据类型。SQLite 数据库也使用数据类型！数据表列必须具有特定的数据类型。下表将常见的 Kotlin 数据类型映射到其 SQLite 等效项。

Kotlin 数据类型

SQLite 数据类型

Int

INTEGER

String

VARCHAR 或 TEXT

Boolean

BOOLEAN

Float, Double

REAL

数据库中的表和每个表中的列统称为 schema（模式）。在下一部分，您将下载入门数据集并详细了解其模式。

3. 下载入门数据集

本 Codelab 的数据库适用于一个假想的电子邮件应用。本 Codelab 使用了熟悉的示例，例如对邮件进行排序和过滤，或按主题文本或发件人进行搜索，以演示您可以使用 SQL 完成的所有强大操作。此示例还确保您在下一个学习路径中处理 Room 之前，对应用中可能遇到的场景类型有所了解。

从 SQL Basics GitHub 仓库的 compose 分支下载入门项目此处。

使用 Database Inspector

要使用 Database Inspector，请执行以下步骤

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

76e94dfe2234c2b1.png

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

cd5dd859d31cbab3.png

现在，您会在底部看到一个新标签页，标签页标题为 App Inspection，其中已选择 Database Inspector 标签页。还有另外两个标签页，但您无需使用它们。加载可能需要几秒钟，但随后您会在左侧看到数据表列表，您可以选择这些表来运行查询。

5ace24ac5cc15abc.png

  1. 点击打开新的查询标签页按钮以打开一个窗格，用于对数据库运行查询。

277ecff401ca5f1a.png

email 表有 7 列

  • id: 主键。
  • subject: 电子邮件的主题行。
  • sender: 电子邮件的原始发件人邮箱地址。
  • folder: 邮件所在的文件夹，例如收件箱或垃圾邮件。
  • starred: 用户是否已加星标此电子邮件。
  • read: 用户是否已阅读此电子邮件。
  • received: 收到电子邮件的时间戳。

4. 使用 SELECT 语句读取数据

SQL SELECT 语句

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

您可以使用 SELECT 语句从 SQLite 数据库读取数据。一个简单的 SELECT 语句由 SELECT 关键字开头，后跟列名，再后跟 FROM 关键字，最后是表名。每个 SQL 语句都以分号 (;) 结尾。

2d7ff99736b072b9.png

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

cf94edd5de825043.png

如果要从表中选择所有列，您可以使用通配符 (*) 来代替列名。

fb75d3033c59949a.png

在这两种情况下，像这样的简单 SELECT 语句都会返回表中的每一行。您只需指定要返回的列名即可。

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

电子邮件应用需要完成的一项主要任务是显示邮件列表。使用 SQL 数据库，您可以使用 SELECT 语句获取此信息。

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

ffc77f938ea09071.png

  1. 首先，尝试从 email 表中的每一行选择所有列。
SELECT * FROM email;
  1. 点击文本框右下角的运行按钮。观察到整个 email 表已返回。

4c3ea237c6ed2b57.png

  1. 现在，尝试仅选择每行的主题。
SELECT subject FROM email;
  1. 注意，查询再次返回了每一行，但仅返回了那一单个列。

69a20935721dcc2.png

  1. 您还可以选择多个列。尝试选择主题和发件人。
SELECT subject, sender FROM email;
  1. 观察到查询返回了 email 表中的每一行，但仅返回了主题和发件人列的值。

4ae739dad54397ea.png

恭喜！您刚刚执行了您的第一个查询。还不错，但这仅仅是个开始；您可以将其视为 SQL 的“Hello World”。

通过添加子句来指定数据的子集，甚至更改输出的格式，您可以使 SELECT 语句更加具体。在以下各节中，您将了解 SELECT 语句的常用子句以及如何格式化数据。

5. 将 SELECT 语句与聚合函数和 distinct 值一起使用

使用聚合函数减少列

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

SQL 聚合函数的示例包括：

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

您可以通过调用聚合函数并将列名作为参数传递到括号内，而不是使用列名。

6730a62d583a0d9.png

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

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

让我们看看一些聚合函数在 email 表中的实际应用

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

5d49b987545184bb.png

  1. 要获取最新邮件的时间，您可以使用 MAX() 函数作用于 received 列，因为最新的 Unix 时间戳是最高数字。
SELECT MAX(received) FROM email;
  1. 查询返回单个结果，即 received 列中的最高（最新）时间戳。

d0241dce845c3955.png

使用 DISTINCT 过滤重复结果

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

4f02533256302f26.png

例如，许多电子邮件应用都有地址自动完成功能。您可能希望包含所有收到电子邮件的地址，并将它们显示在列表中。

  1. 运行以下查询以返回每一行的 sender 列。
SELECT sender FROM email;
  1. 观察到结果包含许多重复项。这肯定不是理想的体验！

4f0489d1668dbede.png

  1. 在 sender 列之前添加 DISTINCT 关键字并重新运行查询。
SELECT DISTINCT sender FROM email;
  1. 注意，结果现在要小得多，并且每个值都是唯一的。

43a47ad8d18fee6e.png

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

55c45cb9c258e882.png

假设您想知道数据库中有多少唯一的发件人。您可以使用 COUNT() 聚合函数以及 DISTINCT 关键字作用于 sender 列来计算唯一发件人的数量。

  1. 执行一个 SELECT 语句，将 DISTINCT sender 传递给 COUNT() 函数。
SELECT COUNT(DISTINCT sender) FROM email;
  1. 观察到查询告诉我们共有 14 位唯一的发件人。

19ae43b0bc9a927e.png

6. 使用 WHERE 子句过滤查询

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

在表名之后，可以在新行上添加 WHERE 关键字，后跟一个表达式。在编写更复杂的 SQL 查询时，为了提高可读性，通常将每个子句放在新行上。

707b0641aa2de0f.png

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

例如，电子邮件应用可能有针对垃圾邮件、已删除邮件、草稿或用户创建的过滤器的过滤器。以下说明使用 WHERE 子句执行此操作

  1. 运行一个 SELECT 语句，从 email 表返回所有列 (*)，包括一个 WHERE 子句来检查条件 folder = 'inbox'。没错，这不是拼写错误：在 SQL 中使用单个等号 (=) 检查相等性，并使用单引号而不是双引号来表示字符串值。
SELECT * FROM email
WHERE folder = 'inbox';
  1. 结果仅返回用户收件箱中的邮件行。

6e9f2a17186d7faa.png

WHERE 子句中的逻辑运算符

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

d8a698416e55d11b.png

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

f3cecac289e7650d.png

为了提高可读性，您还可以使用 NOT 关键字否定表达式。

27300a0a38ef0343.png

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

在 email 表上尝试以下更复杂的 WHERE 子句

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

d9ebd307a146d320.png

  1. 返回所有位于 important 文件夹或已加星标 (starred = true) 的电子邮件。这意味着结果将包含位于不同文件夹但已加星标的电子邮件。
SELECT * FROM email
WHERE folder = 'important' OR starred = true;
  1. 观察结果。

fd2f0dc7b6444956.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. 观察结果。

fd2ff96969824b0d.png

  1. 运行以下查询以返回主题以“fool”结尾的所有行的所有列。
SELECT * FROM email
WHERE subject LIKE '%fool';
  1. 观察到返回了两行。

a23379e507e39c0b.png

  1. 运行以下查询以返回 sender 列中以字母 h 开头的唯一值。
SELECT DISTINCT sender FROM email
WHERE sender LIKE 'h%';
  1. 观察到查询返回了三个值：helena@example.com、hyppolytus@example.com 和 hermia@example.com。

47ada07aee5cd8d9.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. 观察结果。查询返回每个文件夹中的电子邮件总数。

13b9eb8f5c8230c4.png

使用 ORDER BY 排序结果

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

9cf561c6346ed6e0.png

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

您很可能期望电子邮件应用先显示最新的电子邮件。以下说明允许您使用 ORDER BY 子句来实现此目的。

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

6e28aef784a16d1b.png

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

  1. 选择所有主题包含文本“fool”的电子邮件，并按升序对结果进行排序。由于顺序是升序（未指定顺序时的默认顺序），因此在 ORDER BY 子句中使用 ASC 关键字是可选的。
SELECT * FROM email
WHERE subject LIKE '%fool%'
ORDER BY received ASC;
  1. 观察到返回了过滤后的结果，其中最旧的（received 列中的最低值）最先显示。

77ada71b663afab6.png

使用 LIMIT 限制结果数量

到目前为止，所有示例都返回数据库中与查询匹配的每个结果。在许多情况下，您只需要显示数据库中的有限行数。您可以在查询中添加 LIMIT 子句以仅返回特定数量的结果。添加 LIMIT 关键字，后跟您要返回的最大行数。如果适用，LIMIT 子句应在 ORDER BY 子句之后。

122152adf15a9fca.png

您还可以选择包含 OFFSET 关键字，后跟另一个数字，表示要“跳过”的行数。例如，如果您想要第一个十个结果之后的下十个结果，但不想返回所有二十个结果，您可以使用 LIMIT 10 OFFSET 10。

37ad836862573d55.png

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

  1. 执行以下 SELECT 语句，以获取用户收件箱中按降序排列并限制为前十个结果的所有电子邮件。
SELECT * FROM email
WHERE folder = 'inbox'
ORDER BY received DESC
LIMIT 10;
  1. 观察到仅返回了十个结果。

5b228d8053956489.png

  1. 修改并重新运行查询，使其包含 OFFSET 关键字，值为 10。
SELECT * FROM email
WHERE folder = 'inbox'
ORDER BY received DESC
LIMIT 10 OFFSET 10;
  1. 查询按降序返回十个结果。然而，查询跳过了第一组十个结果。

83a6ddbf6ef92b89.png

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

将数据插入数据库

除了从数据库读取数据外，还有用于向数据库写入数据的不同 SQL 语句。首先需要一种方法将数据放入其中，对吧？

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

97b93929d6de2d0e.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';

12a3e77309771dd8.png

更新数据库中的现有数据

将数据插入表后，您仍然可以稍后更改它。您可以使用 UPDATE 语句更新一个或多个列的值。UPDATE 语句以 UPDATE 关键字开头，后跟表名，再后跟 SET 子句。

8ee88a5985aec77e.png

一个 SET 子句由 SET 关键字开头，后跟您要更新的列名。

bc255ece789859f.png

一个 UPDATE 语句通常包含一个 WHERE 子句，用于指定要使用指定的列值对更新的单行或多行。

e64b7b343feb6224.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”）。

74e9af167fa49ba3.png

从数据库删除一行

最后，您可以使用 SQL DELETE 语句从表中删除一行或多行。DELETE 语句以 DELETE 关键字开头，后跟 FROM 关键字，再后跟表名，最后是 WHERE 子句，用于指定要删除的行或多行。

a7e56405c5e5aaab.png

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

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

b026810cf2fd6e44.png

9. 总结

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

这些技能将直接应用于 Room，并且凭借您对 SQL 的了解，您将为未来的应用中的数据持久化做好充分准备。

SELECT 语句语法

346bed4fda774ca7.png

10. 了解详情

虽然我们重点介绍了 SQL 的基础知识以及 Android 开发的一些常见用例，但 SQL 还有很多其他功能。请参考以下资源作为您所学内容的额外参考，或进一步了解该主题。