SQL 入门指南

SQLite 入门指南

“使用 Kotlin 开发 Android 应用”课程假定您了解以下主题：

  • 数据库（一般性了解）
  • SQL 数据库（具体了解）
  • 用于与数据库交互的 SQL 语言

本页用于对此前学习的内容进行回顾和快速参考。

SQL 数据库

SQL 数据库以行列表的形式存储数据：

  • 行和列的交叉称为字段。
  • 字段包含数据、对其他字段的引用或对其他表的引用。
  • 每行包含一个实体。实体由唯一 ID 标识，此 ID 通常用作其主键。
  • 每列都由在表格中唯一的名称来标识。

SQLite

SQLite 可实现具有以下特征的 SQL 数据库引擎：

  • 独立（无需其他组件）
  • 无服务器（无需服务器后端）
  • 零配置（无需为您的应用进行配置）
  • 事务性（SQLite 中单个事务内的更改要么完全执行，要么完全不执行）

SQLite 是全球部署最广泛的数据库引擎。SQLite 的源代码位于公共域中。如需详细了解 SQLite 数据库，请参阅 SQLite 网站。

示例表格

  • 名为 DATABASE_NAME 的数据库
  • 名为 WORD_LIST_TABLE 的表格
  • _id、word 和 definition 列

在插入 alpha 和 beta 字词后（其中 alpha 有两个定义），表格可能如下所示：

DATABASE_NAME

WORD_LIST_TABLE

_id

word

definition

1

"alpha"

"first letter"

2

"beta"

"second letter"

3

"alpha"

"particle"

如需查找特定行中的内容，请使用 _id，或制定相关查询（通过指定限制条件来从表格中选择行）来检索行。

事务

事务是指作为单个逻辑工作单元执行的一系列操作。为符合成为事务的条件，逻辑工作单元必须具备四个属性：原子性、一致性、隔离性和持久性 (ACID)。

  • 原子性。要么执行事务的所有数据修改，要么不执行任何修改。即使程序崩溃、操作系统崩溃或电源故障中断向磁盘写入更改的行为，原子性仍然不变。
  • 一致性。事务完成后，必须让所有数据处于一致的状态。
  • 隔离性。并发事务所做的修改之间必须隔离。事务要么识别数据在另一个并发事务修改它之前所处的状态，要么识别数据在另一个事务完成之后所处的状态。事务无法识别中间状态。
  • 持久性。事务完成后，其影响将永久保留在系统中。即使系统发生故障，修改也会保留。

事务示例：

  • 将资金从储蓄账户转到支票账户。
  • 在字典中输入术语和定义。
  • 将更改列表提交到 master 分支。

如需详细了解事务，请参阅 SQLite 中的原子提交。

查询语言

您可以使用 SQL 查询语言与数据库进行交互。查询可能非常复杂，但有四种基本操作：

  • 插入行
  • 删除行
  • 更新行中的值
  • 检索满足给定条件的行

在 Android 中，数据访问对象 (DAO) 提供了插入、删除和更新数据库的便捷方法。有关查询语言的完整说明，请参阅 SQLite 所理解的 SQL。

查询结构

SQL 查询是高度结构化的。示例查询：

  • SELECT word, definition FROM WORD_LIST_TABLE WHERE word="alpha"

示例查询的通用版本：

  • SELECT columns FROM table WHERE column="value"

示例查询的组成部分：

  • SELECT columns：选择要返回的列。使用 * 可返回所有列。
  • FROM table：指定从中获取结果的表格。
  • WHERE：位于必须符合的条件之前的可选关键字，例如 column="value"。常见的运算符有 =、LIKE、< 和 >。如需连接多个条件，可使用 AND 或 OR。

查询的其他组成部分：

  • ORDER BY：用于按列对结果进行排序的可选关键字短语。指定 ASC 表示升序，指定 DESC 表示降序。如果未指定顺序，则会以默认顺序排序，这可能是未经排序的。
  • LIMIT：用于指定结果数量限制的关键字。

示例查询和结果

以下查询使用了之前定义的表格：

SELECT * FROM WORD_LIST_TABLE

获取 WORD_LIST_TABLE 表格中的所有行。
SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2 选择 id 大于 2 的所有项的 word 和 definition 列。
返回

[["alpha", "particle"]]

SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"

返回定义中包含子字符串 art 的字词 alpha 的 id。

[["3"]]

SELECT definition FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1

选择所有定义。反向排序并获取列表排序后的第一行。排序按指定的 word 列进行。请注意，我们可以按不返回的列进行排序！

[["second letter"]]

SELECT * FROM WORD_LIST_TABLE LIMIT 2,1

返回从位置 2 开始的 1 个项。位置计数从 1（而非零！）开始。返回 [["2", "beta", "second letter"]]

您可以在此 SQL Fiddle 网站上练习创建和查询数据库。

针对 Android SQLite 执行的查询

您可以将查询作为原始查询或参数发送到 Android 系统的 SQLite 数据库。

rawQuery(String sql, String[] selectionArgs) 方法运行提供的 SQL。此方法返回结果集 Cursor。下表显示了上述前两个示例查询作为原始查询是什么样的：

1

String query = "SELECT * FROM WORD_LIST_TABLE";
rawQuery(query, null);

2

query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";
String[] selectionArgs = new String[]{"2"}
rawQuery(query, selectionArgs) ;

query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) 方法会查询给定的表格。该方法在结果集中返回 Cursor。下面的查询显示了如何填充参数：

SELECT * FROM WORD_LIST_TABLE
WHERE word="alpha"
ORDER BY word ASC LIMIT 2,1;

查询返回以下内容：

[["alpha", "particle"]]

您可以使用的参数示例：

String table = "WORD_LIST_TABLE"
String[] columns = new String[]{"*"};
String selection = "word = ?"
String[] selectionArgs = new String[]{"alpha"};
String groupBy = null;
String having = null;
String orderBy = "word ASC"
String limit = "2,1"

query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

注意：在实际代码中，您无法为 null 值创建变量。如需查看此方法在采用不同参数时的各种版本，请参阅 Android SQLiteDatabase 文档。

游标

游标是指向结构化数据行的指针。您可以将游标视为指向表格行的指针。

查询会返回指向查询结果中的第一个元素的 Cursor 对象。Cursor 类提供了在查询结果中移动游标的方法，以及从结果中每行的列中获取数据的方法。

当某个方法返回 Cursor 对象时，您可以遍历结果、提取数据、对数据执行某些操作，以及关闭游标以释放内存。

了解详情