使用 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 指的是使用结构化查询语言（称为 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. 下载入门数据集

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

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

使用数据库检查器

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

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

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 子句过滤查询

许多电子邮件应用程序提供了基于特定条件过滤显示的消息的功能，例如日期、搜索词、文件夹、发件人等。对于这些类型的用例，您可以将 WHERE 子句添加到您的 SELECT 查询中。

在表名之后，在新行上，您可以添加 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" 的邮件总数。
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 子句对结果进行分组，以便对给定列具有相同值的的所有行在结果中彼此相邻分组。此子句不会更改结果，而只会更改返回它们的顺序。

要将 GROUP BY 子句添加到 SELECT 语句中，请添加 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

默认情况下，排序方向是升序，您可以在 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', '[email protected]', 'inbox', false, false, CURRENT_TIMESTAMP
);
  1. 请注意，结果将插入到数据库中，其 id 为 44。
SELECT * FROM email
WHERE sender = '[email protected]';

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 还有许多其他功能。请参考以下资源作为对您所学内容的额外参考，或进一步了解此主题。