12-3 RDBMS 与 SQL（中）

分子、原子

和 Kotlin 一样，MySQL 中也有许多种不同的数据类型，用于存储各种类型的数据，下面列出一些常用的：

CHAR(长度) 和 VARCHAR(长度)：字符数组，即 Kotlin 中的 String，但通常用于存储较短的字符串。
TEXT：文本，同样对应 Kotlin 中的 String，但可以存储通常规模的文本（最长 64 KiB）。
BOOLEAN：逻辑值，即 Kotlin 中的 Boolean。
INTEGER：整数，即 Kotlin 中的 Int。
FLOAT 和 DOUBLE：小数，即 Kotlin 中的 Float 和 Double。

为所要存储的数据选择合适的类型很重要，因为尽管 MySQL 中的数据项在创建后可以修改，但这项操作对于较大的表来说非常耗时，并且在不完全兼容的数据类型之间转换（例如由 TEXT 向 INTEGER）可能会造成数据丢失。

值得一提的是，RDBMS 以其极高的可靠性和数据一致性而著称，为此有时甚至不惜付出性能的代价。如果因为设计之初的考虑不善而造成数据丢失，那可太划不来了！

数据的摇篮

前面已经提到，MySQL 的主要组成部分是各种各样的表。由于一个 MySQL 服务器可能会由多个客户端使用，而每个客户端又可能使用多张表，因此 MySQL 允许我们把多张表收集到一个数据库（Database） 中，以供单独的客户端使用。那么，在我们能创建表之前，就需要先创建数据库：

CREATE DATABASE 数据库名;

提示

不同的 SQL 解析器使用的注释也不尽相同。在本书中，我们使用 MySQL 的风格，即使用两个减号 -- 开头的一行表示注释。

MySQL 中的关键字（例如 CREATE DATABASE）通常使用大写，而各类名称（标识符）则使用小写字母加上下划线（例如 snake_case）。SQL 中的关键字是不区分大小写的，但对于标识符而言，不同的 RDBMS，或者同一个 RDBMS 运行在不同的操作系统上，对于大小写的处理方法都不尽相同。因此，我们通常认为 SQL 对标识符是区分大小写的，这样做比较保险。

需要注意的是，如果你在 MySQL 终端中尝试多次执行上述的命令，MySQL 会报告错误：

ERROR 1007 (HY000): Can't create database 'it'; database exists

这是因为 CREATE 的含义在 SQL 中就是『创建』，而不是『若不存在则创建』。当目标数据库存在时，SQL 就会认为『不能重复创建这个数据库』，也就是操作失败。如果想要让 SQL 忽略已经存在的数据库，就需要明确告诉它：

CREATE DATABASE IF NOT EXISTS 数据库名;

把上面语句中的 CREATE 换成 DROP，就可以用来删除数据库：

DROP DATABASE 数据库名;
DROP DATABASE IF NOT EXISTS 数据库名;

没有回头路

SQL 中所有的操作都是永久性的，各类 DROP 也不例外。一旦 DROP 成功完成，对应的内容就已经被删除。不论是 MySQL 还是其它的 RDBMS，都不存在诸如 UNDO 之类的语句来撤销所做的操作。或许你已经习惯了使用回收站找回删除的文件，或者使用 Ctrl + Z 来撤销错误的字符，但在 SQL 这里，你必须对所键入的语句负责 —— 一旦你按下 Enter，操作就不可撤回。

有鉴于此，在任何生产环境的数据库中，都不应该随意地访问数据库控制台，更不要出于所谓的『好奇心』去尝试执行 DROP DATABASE 这样的语句，如果因此造成了服务器数据丢失，你可能会吃苦头。出于同样的原因，需要使用 SQL 的插件也必须对相关功能进行缜密的测试，以免错误的代码造成不可挽回的后果。

当创建了数据库中，我们可以使用 USE 来选定数据库：

USE 数据库名;

这样接下来的表操作都会在选定的数据库中进行。

我们需要更多的表

有了数据库，我们就可以开始创建所需要的表。创建表比数据库要复杂一些，因为我们需要指定每一列的属性：

CREATE TABLE 表名 (
    列名 类型 约束,
    列名 类型 约束,
    -- 更多的列
);

与 Kotlin 类似，换行和缩进并不是必要的，在不拆分和连接单词的情况下可以任意增加或减少，不过像上面这样的写法会更易读。

类型就是先前提到过的数据类型，例如 INTEGER。约束（Constraint） 则可以指定这一列应当具备的一些额外要求，下面列出一些常用的：

NOT NULL：值不可为空。默认情况下，MySQL 中的每个『格子』中可以存有数据，也可以没有（值为 NULL），使用 NOT NULL 会禁止这一列中存在 NULL 值。
UNIQUE：值不可重复。
KEY：对当前列编制索引，加快在当前列上的查找速度。
DEFAULT 值：指定默认值。要注意的是，即使指定了 DEFAULT，其值仍然可以被删除（从而变为 NULL），除非同时使用 NOT NULL。

一旦指定了约束，RDBMS 就会严格执行它，确保指定的条件在任何情况下都成立。例如，如果给代表玩家名的列指定了 UNIQUE，那么当试图插入同名的数据时，RDBMS 就会拒绝该操作。选择合适的约束很重要，例如，一个存储玩家货币的表可能像这样：

CREATE TABLE deposit (
    name    VARCHAR(255) NOT NULL UNIQUE KEY,   -- 玩家名不可重复，也不能为空，同时还要方便查找
    diamond INTEGER NOT NULL DEFAULT 0,         -- 钻石不可为空，默认值为 0
    gold    INTEGER NOT NULL DEFAULT 0
);

这样当我们查询数据时，我们就可以非常自信地断言，『gold 绝不可能是 null！』，这种确定性正是 RDBMS 提供的诸多便利之一，它值得我们（思维上和代码上）为此付出的许多努力。

类似数据库，我们可以使用 IF NOT EXISTS 来选择性地创建表，以及 DROP 来永久删除表。

插入数据

在创建了表之后，我们可以向其中插入数据：

INSERT INTO 表名 (列名, 列名, 列名) VALUES (值, 值, 值);

插入数据会创建新的一行（不管相同的数据是否已经存在），并且将所指定的每个列设置为指定的值。要注意的是，所有 NOT NULL 的列都必须被指定值，否则 RDBMS 会拒绝该操作。

列的顺序只需要和值对应即可，并不需要和表的定义顺序相同 —— 还记得吗？表中的列是无序的。

由于多数情况下，插入新行的时候都会指定全部的值，因此对于这种用法，可以省略列名：

INSERT INTO 表名 VALUES (值, 值, 值);

在大多数情况下，要插入的值都会由客户端的代码来传递，不过如果要插入字面量也是可以的：

INSERT INTO deposit VALUES ('Aluka', 64, 100);

字面量的写法与 Kotlin 非常相似，唯一的区别是字符串要使用单引号 '。

选择数据

简单查询

使用 SELECT 可以从数据表中查询数据：

SELECT 列名, 列名, 列名 FROM 表名 WHERE 条件;

SELECT 从指定的表中找出所有满足条件的行，然后保留其中指定的列，并将其返回给程序。如果想要让 SQL 返回所有的列，不需要一个个地列出列名，而可以使用 *：

SELECT * FROM 表名 WHERE 条件;

Nyaci：不是说 RDBMS 是独立的程序吗？它要怎么把结果『返回』给我呢？

这是一个有趣的问题，概括地来说，你通过什么方式将 SQL 语句发送给 RDBMS，它们就以相同的方式将结果发送回来。根据数据库的配置不同，这个通信渠道可能是网络、文件或者共享内存等方式。不过，我们不需要关心这一点，对于插件开发而言，我们所使用的 SQL 库会为我们搞定一切的，我们稍后将看到具体的例子。

筛选数据所使用的条件与 Kotlin 的 if 和 while 条件很相似：

WHERE id = 'A1234'
WHERE diamond >= 100
WHERE gold > 0 AND NOT id IS NULL

使用 AND、OR 和 NOT 可以以对应的逻辑连接多个条件，而特殊的 IS NULL 可以判断一个值是否为空（因为并没有 id = NULL 这种写法）。顺便一提，NOT id IS NULL 也可以写作 id IS NOT NULL，功能上是一致的，就像 Kotlin 中的 !(a == b) 与 a != b 一样。

注意

在 SQL 中，相等关系使用一个等号 =，这可能是因为 SQL 中并没有传统的赋值概念，所以也就不需要进行这样的区分。不过，由于大多数工程师都已经习惯了使用两个等号（TypeScript 甚至是三个），SQL 的这种设计有时还是会引起一些混乱，大家可要当心呀！

玩家名	钻石	金币
Aluka	64	100
Nyaci	128	0
Ted	-65536	0.3

时间	转出玩家名	转入玩家名	货币类型	数额
...	Ted	NULL	金币	65536
...	Aluka	Nyaci	钻石	64

无关子查询

有相关子查询，自然也就有无关子查询。上面的这个任务可以用无关子查询改写如下：

SELECT name FROM deposit
WHERE gold > 1000
AND name IN (
    SELECT receiver FROM trans
);

你看出区别了吗？除了谓词由 EXISTS 改为了 IN，子查询的 SELECT 也发生了变化。这一步有些微妙，你可以想象一下，EXISTS 的执行方式大概是：

查看 deposit 中的一行，并提取出 name。
执行子查询，在 trans 中搜索满足 receiver = name 的结果。

这样看上去是不是不太高效？对于每个玩家，我们都需要搜索一次 trans 表！然而，『最近有过转入的玩家』是与当前的玩家名无关的，也许我们可以换一种解决问题的方式：

执行子查询，找出 trans 表中的所有 receiver（即最近有过转入的玩家）。
查看 deposit 中的一行，如果它的 name 在上述的结果中，就返回它。

这正是 IN 的工作方式：它先执行子查询，然后再用这个结果作为条件，用来判断上一级查询。换而言之，EXISTS 是消极查询的，它只在被问到『name 在 trans 中有作为 receiver 的记录吗？』的时候才去寻找答案，而 IN 则是积极查询的，它先找出这个问题的答案，然后在每次被问到时，就使用预先准备好的答案直接做出回答。

尺短寸长

两种子查询各有适用的场景，没有哪一种一定比另一种更加优秀（这也是我们刚才说 EXISTS 只是『看上去』不太高效的原因）。如果 trans 中有十万条数据，或者更多，而金币数超过 1000 的玩家却没有几个呢？在这种情况下，EXISTS 只会被提问很少的次数，而提前『准备答案』的 IN 可就要累坏了！

最后要说明的一点是，由于 IN 是在『被提问』前执行的，因此它的子句中不能使用外围被 SELECT 的表中的数据，子查询是独立执行的，这正是它被称作无关子查询的原因。

SELECT 可能是 SQL 中最为复杂的语句（没有之一），本书毕竟不是 SQL 教程，因此也不可能在这里把 SELECT 的每种用法都一一列举出来。不过对于插件开发而言，掌握了一般查询和两种子查询，基本上也就能满足 80% 的用途了。如果大家还需要更高级的内容（例如聚集函数、联机视图等），可以看看 W3Schools 的 SQL 指南，里面包含了每种 SQL 语句的详细使用方法。

更新数据

使用 UPDATE 可以修改表中的数据：

UPDATE 表名
SET 列名 = 值, 列名 = 值, 列名 = 值
WHERE 条件;

UPDATE 更新一行或者很多行，将每个指定的列设置为新的值。一般来说，UPDATE 和 WHERE 一起使用，用来选择要更新的行（因为很少会需要更新整个表）。需要注意的是，SQL 中所有的 WHERE 都具备同等强大的功能，也就是说，相关子查询和无关子查询在这里同样可以使用，UPDATE 会正常更新被『选中』的数据。

为列指定新值时，可以使用字面量，或者由程序来传递，但除此之外还可以直接让 SQL 来完成一些简单的计算，例如：

UPDATE deposit
SET diamond = diamond + 99999
WHERE name = 'Ted';

和 Kotlin 中一样，我们可以使用变量先前的值来计算新的值。大家可能会觉得『什么嘛！我也可以把值读到 Kotlin 里面，加上 99999，然后再设定回去，不是吗？』，问题在于，当你的程序在操作钻石数的同时，也可能有其它程序正在修改钻石数，而这可能会出问题。例如，假设 Aluka 现在有 200 钻石，在同一时间，她收入了 100 钻石，又花费了 50 钻石，如果没有原子操作，就可能发生下面这样的事情：

客户端 A 读取 Aluka 的钻石数，得到 200。
客户端 B 读取 Aluka 的钻石数，得到 200。
客户端 A 给钻石数加上 100，得到 300。
客户端 B 给钻石数减去 50，得到 150。
客户端 A 设置 Aluka 的钻石数为 300。
客户端 B 设置 Aluka 的钻石数为 150。（B 并不知道 A 已经修改了数据！）
最终，Aluka 的钻石数为 150。

这就是丢失更新（Lost Update） —— Aluka 本应收入的 100 钻石就这么凭空消失了！RDBMS 内部其实有防范这种事情发生的措施，但问题在于，在上面的流程中，RDBMS 只是接到了『读取钻石数』和『设置钻石数为 XX』的命令，它并不理解这些数值背后的含义。如果想要让 RDBMS 解决这种冲突，就需要使用它所理解的操作：

客户端 A 告诉 RDBMS：『Aluka 的钻石数增加 100』。
客户端 B 告诉 RDBMS：『Aluka 的钻石数减少 50』。
RDBMS 把这两条信息综合在一起，最终，Aluka 的钻石数为 250。

信息

不仅仅是数据库，相同的问题在任何多线程的环境下都完全可能发生，这一点我们会在本书的后续部分介绍。RDBMS 被设计为即使在非常极端的条件下也能最大限度地保证数据的完整性，而『大量客户端同时访问』正是其中之一。

删除数据

作为数据操作的最后一部分，让我们用 DELETE 来结束这一节：

DELETE FROM 表名 WHERE 条件;

DELETE 从表中删除所有满足条件的行。

Nyaci：不能单独删除某一格吗？

哈，在 SQL 中，『删除』和『设置为 NULL』是完全不同的两件事情。SQL 中不存在不存在的东西 —— 如果表中有一行，那一行就必须是完整的，即使其中的某些列可能是 NULL，但它们确实存在，如果用 SELECT 查询，也能读出这些值。相比之下，如果一行被删除了，那么它在这张表上就不会再有任何痕迹。

危险

对于所有 SQL 语句，如果不指定条件，SQL 会认为你指的就是『所有数据』，所以不带任何条件的 DELETE 不是『什么也不做』，而是会删除整张表！更要命的是，DELETE 和 DROP 一样，一旦执行就不可撤销。所以，将来如果你见到 DELETE FROM some_table;，就赶快告诉自己：『你写错代码了！这样不行！』

介绍了这么多 SQL 操作，我们总算是不再对着空白的数据库，或者填好数据的表不知所措了！那么事不宜迟，既然已经学习了 SQL 语句的基本用法，接下来要介绍的就是如何通过 Kotlin 来使用它们。虽说是使用 SQL，但其实大家也不用觉得『能操作 SQL 这么高级的语言，会不会很复杂啊？』，调用 SQL 库的过程，其实也和先前使用诸如 MapDB 和 Java TOTP 一样，没有什么复杂的。

话虽如此，由于和数据库的交互要经过诸如网络这样不太稳定的介质，而且 RDBMS 可能会拒绝一条不正确的 SQL 语句，因此错误处理（Error Handling） 是 SQL 编程中的重要一环，在 Kotlin 中，错误处理的方式是异常处理（Exception Handling），我们将在下一小节对此做个简要的介绍。

分子、原子​

数据的摇篮​

我们需要更多的表​

插入数据​

选择数据​

简单查询​

相关子查询​

无关子查询​

更新数据​

删除数据​