使用 SQL 读取和写入数据库

1. 开始之前

您使用的许多应用程序都将数据直接存储在设备上。时钟应用程序存储您的重复闹钟，Google 地图应用程序保存您最近搜索的列表，通讯录应用程序允许您添加、编辑和删除联系人的信息。

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

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

在掌握 SQL 的基础知识后，您将为准备在该单元的后续部分使用 Room 库向您的应用程序添加持久性。

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

什么是数据库？

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

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

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

数据库中的表和每个表中的列统称为模式。在下一节中，您将下载入门数据集并了解有关其模式的更多信息。

3. 下载入门数据集

此代码实验室的数据库用于假设的电子邮件应用程序。此代码实验室使用熟悉的示例（例如，对邮件进行排序和筛选，或按主题文本或发件人进行搜索）来演示您可以使用 SQL 执行的所有强大功能。此示例还确保您在下一路径中使用 Room 之前，拥有处理您可能在应用程序中遇到的各种场景的经验。

从 SQL 基础知识 GitHub 存储库的compose分支下载入门项目此处。

使用数据库检查器

要使用数据库检查器，请执行以下步骤

  1. 在 Android Studio 中运行 SQL 基础知识应用程序。应用程序启动后，您将看到以下屏幕。

76e94dfe2234c2b1.png

  1. 在 Android Studio 中，点击查看>工具窗口>应用程序检查器。

cd5dd859d31cbab3.png

您现在会在底部看到一个名为应用程序检查器的新选项卡，其中选择了数据库检查器选项卡。还有另外两个选项卡，但您不需要使用它们。加载可能需要几秒钟，但随后您会在左侧看到一个包含数据表的列表，您可以选择这些表来针对其运行查询。

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. 确保在数据库检查器中选择了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语句

使用聚合函数减少列

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

SQL聚合函数的示例包括以下内容

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

您可以调用聚合函数并在括号之间传入列名作为参数，而不是列名。

6730a62d583a0d9.png

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

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

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

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

5d49b987545184bb.png

  1. 要获取最新消息的时间，您可以对received列使用MAX()函数，因为最新的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()聚合函数和sender列上的DISTINCT关键字来计算唯一发件人的数量。

  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. 返回所有位于重要文件夹OR已加星标(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”的电子邮件总数。
SELECT COUNT(*) FROM email
WHERE subject LIKE '%fool%';
  1. 观察结果。

fd2ff96969824b0d.png

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

a23379e507e39c0b.png

  1. 运行以下查询以返回以字母h开头的sender列的不同值。
SELECT DISTINCT sender FROM email
WHERE sender LIKE 'h%';
  1. 观察到查询返回三个值：[email protected]、[email protected]和[email protected]。

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

默认情况下，排序方向为 a升序，您可以从 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. 修改并重新运行查询以包含值为 10 的 OFFSET 关键字。
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 还可以做更多事情。请参考以下资源作为您所学内容的补充参考，或进一步了解此主题。