《七周七数据库》一一2.2 第1天：关系、CRUD和联接

本节书摘来自异步社区出版社《七周七数据库》一书中的第2章，第2.2节，作者： 【美】Eric Redmond，更多章节内容可以访问云栖社区“异步社区”公众号查看。

2.2　第1天：关系、CRUD和联接

七周七数据库
我们虽然不会把你当作是一个关系数据库专家，但是确实会假设你曾用过一两个数据库。这些数据库很可能是关系型的。我们将开始创建自己的数据表，并填充数据。然后尝试查询一些行。最后探讨关系数据库中非常重要的表联接。

就像大多数数据库一样，Postgres提供一个后台服务进程（Backend），它完成所有数据处理工作，还提供一个命令行客户端程序，通过它连接到运行中的服务进程。服务进程默认监听5432端口，可以用psql这个命令行工具连接。

数学关系

关系数据库的名称源于它们包含关系（即表），它们是元组（即行）的集合，元组又将属性映射到原子值（例如，{name: 'Genghis Khan'，p.died_at_age: 65}）。

可用的属性通过头部的属性元组来定义，这些属性映射到某个域或限制的类型（即列；例如，{name: string，age: int}）。这是关系结构的要点。

尽管听起来数学味很浓，但是实现比名字所暗示的更具有现实意义。那么，为什么要提到这些？我们正试图说明，关系数据库的关系是因为它的数学基础，不是因为表通过外键彼此“关联”。这样的限制是否存在并不是关键。

虽然许多数学关系你看不到，但模型的力量肯定是蕴藏在数学之中。这种魔法允许用户提出功能强大的查询，然后让系统基于预定义的模式进行优化。RDBMS基于集合理论的一个分支，名为关系代数，它包括选择（WHERE...）、投影（SELECT...）、笛卡尔积（JOIN...）等操作，如图2-1所示。

如果将关系想象为一张物理表（数组的数组，在数据库入门课中无数次重复过），可能在实践中造成痛苦，如编写遍历所有行的代码。关系查询的描述性远胜于此，它源于一个数学分支，名为元组关系演算，可以转换为关系代数。PostgreSQL和其他的RDBMS通过执行这个转换优化了查询，简化了代数运算。你可以看到，图2-2中的SQL，与图2-1中的SQL是一样的。

$ psql book

以管理员用户运行的话，PostgreSQL的提示符是数据库的名字后面跟一个‘#’，如果是普通用户，后面跟的是‘$’。这个命令行程序的内置文档是所有命令行程序中最好的。输入‘/h’，可以列出有关SQL命令的信息，?列出以反斜杠开始的psql特有命令的帮助信息。可以使用下列方式找到每个SQL命令的使用详细信息：

book=# \h CREATE INDEX
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
　　( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | ...
　　[ WITH ( storage_parameter = value [, ... ] ) ]
　　[ TABLESPACE tablespace ]
　　[ WHERE predicate ]

在我们深入探讨PostgreSQL之前，最好先熟悉这个有用的工具。还可以熟悉一些常见命令值，如SELECT或CREATE TABLE。

2.2.1　从SQL开始

PostgreSQL遵循SQL惯例，称关系为表（TABLE），属性为列（COLUMN），元组为行（ROW）。虽然你可能会遇到一些数学术语，如关系、属性和元组，为了保持一致性，我们将使用这些术语，如关系、属性和元组。有关这些概念的更多信息，请参阅2.2节的“数学关系”。

关于CRUD

CRUD是一个助记符，帮助记忆数据管理基本操作：创建、读取、更新和删除（Create，Read，Update，Delete）。这些操作一般对应插入新记录（创建），修改现有记录（更新），删除不再需要的记录（删除）。你使用数据库时所有的其他操作（你可以梦想到的任何疯狂查询）都是读操作。如果能进行CRUD操作，你就能做任何事。

2.2.2　使用表

PostgreSQL是关系型的数据管理系统，所以需要事先进行设计。要先设计好数据库的表，然后插入符合数据库定义的数据。

创建表包括为它命名，定义所有列及其类型，以及定义（可选的）约束信息。每张表都应该指定唯一的标识符列，以标识特定的行。该标识符称为主键（PRIMARY KEY）。创建countries表的SQL语句如下所示：

CREATE TABLE countries (
　 country_code char(2) PRIMARY KEY,
　 country_name text UNIQUE
);

这张新表将存储一些行，其中每一行由两个字节的国家代码作为标识，国家名也是唯一的。这两列都有约束，主键约束country_code列不允许有重复的国家代码，所以只有一个us和一个gb可以存在表中。尽管country_name不是主键，但是明确地给予country_name类似的唯一性约束。可以用如下语句插入几行来填充这张counties表。

INSERT INTO countries (country_code, country_name)
VALUES ('us','United States'), ('mx','Mexico'), ('au','Australia'),
　　　　 ('gb','United Kingdom'), ('de','Germany'), ('ll','Loompaland');

让我们来测试一下唯一性约束。如果尝试添加包含重复的country_name的行，就会因为唯一性约束而不允许插入。约束是PostgreSQL这样的关系数据库用来确保数据完整的方法。

INSERT INTO countries
VALUES ('uk','United Kingdom');
ERROR: duplicate key value violates unique constraint "countries_country_name_key"
DETAIL: Key (country_name)=(United Kingdom) already exists.

通过SELECT...FROMtable语句进行查询，可以验证相关的行是否已经插入。

SELECT *
FROM countries;
country_code　  | country_name
----------------+---------------
us　　　　　　　| United States
mx　　　　　　　| Mexico
au　　　　　　　| Australia
gb　　　　　　　| United Kingdom
de　　　　　　　| Germany
ll　　　　　　　| Loompaland
(6 rows)

根据正规的地图，可以知道Loompaland不是真实存在的地方，所以让我们从表中删除它。用WHERE子句指定要删除的行，country_code等于ll的行将被删除。

DELETE FROM countries
WHERE country_code = 'll';

只有实际存在的国家留在了countries表中，让我们再添加一个cities表。为保证所有插入的country_code都在countries表中，将添加关键字REFERENCES。因为country_code列引用了另一张表的键，所以它称为外键约束。

CREATE TABLE cities (
　name text NOT NULL,
　postal_code varchar(9) CHECK (postal_code <> ''),
　country_code char(2) REFERENCES countries,
　PRIMARY KEY (country_code, postal_code)
);

这一次，cities表中的name列的约束是不允许其值为NULL的。postal_code列的约束，是其值不能是空字符串（<>表示不等于）。

此外，因为主键唯一地标识一行，所以定义了一个复合键：country_code + postal_code。它们共同作为一行的唯一的标识符。

Postgres也有丰富的数据类型，刚才看到了三种不同的字符串表示：text（任意长度的字符串），varchar（9）（长度可达9个字节的字符串）和char（2）（正好两个字节的字符串）。

定义了数据表后，让我们插入Toronto，CA。

INSERT INTO cities
VALUES ('Toronto','M4C1B5','ca');
ERROR: insert or update on table "cities" violates foreign key constraint
　 "cities_country_code_fkey"
DETAIL: Key (country_code)=(ca) is not present in table "countries".

这个操作失败并不是什么坏事！因为country_code需要参考countries，所以country_code必须存在于countries表中，这称为保持参照完整性，参见图2-3，它确保数据始终是正确的。值得指出的是，NULL对cities.country_code是有效的，因为NULL代表一个值空缺。如果你不想允许country_code引用为NULL，可以这样定义cities表的列：country_code char（2）REFERENCES countries NOT NULL。

现在我们再试试插入一个美国城市的数据。

INSERT INTO cities
VALUES ('Portland','87200','us');
INSERT 0 1

.

当然，这是一次成功的插入。但是我们输入了错误的邮政编码。波特兰（Portland）正确的邮政编码是97205，但我们不必删除并重新插入，可以直接更新这一行。

UPDATE cities
SET postal_code = '97205'
WHERE name = 'Portland';

现在已经可以创建、读取、更新、删除表中的行了。

2.2.3　使用联接的查询

在本书中学习的所有其他数据库，也都可以执行CRUD操作。但PostgreSQL这样的关系数据库有独特的能力，能够在读取表时对表进行联接。联接本质上是以某种方式联接两个独立的表，并返回一张结果表。这有点像拼字游戏，打散单词的字母卡片，重新拼接字母，从而得到新的词。

联接的基本形式是内联接（inner join）。最简单的形式就是，使用ON关键字指定匹配的两列（每张表一列）

SELECT cities.*, country_name
FROM cities INNER JOIN countries
　ON cities.country_code = countries.country_code;
country_code  |　　name　| postal_code | country_name
--------------+----------+-------------+----------------
us　　　　　　| Portland | 97205　　　 | United States

联接返回单张表，其中包含cities表的所有列的值，再加上匹配的countries表中country_name的值。

也可以联接诸如cities这样有复合主键的表。为了测试复合联接，我创建一张新表，来存储场地（venue）的列表。

某个国家和一个邮政编码组成一个场所。外键必须引用Cities表的两个主键列。（MATCH FULL是一个约束，确保两个值都存在，或两者均为NULL。）

CREATE TABLE venues (
　 venue_id SERIAL PRIMARY KEY,
　 name varchar(255),
　 street_address text,
　 type char(7) CHECK ( type in ('public','private') ) DEFAULT 'public',
　 postal_code varchar(9),
　 country_code char(2),
　 FOREIGN KEY (country_code, postal_code)
　　 REFERENCES cities (country_code, postal_code) MATCH FULL
);

其中venue_id列是一种常见的主键设置：设置为自动递增整数（1，2，3，4，…）。可以使用SERIAL关键字来定义这个标识符（MySQL有一个类似的构造，称为AUTO_INCREMENT）。

INSERT INTO venues (name, postal_code, country_code)
VALUES ('Crystal Ballroom', '97205', 'us');

虽然没有设置venue_id的值，但创建行时会填充它。

回到复合联接。联接enues表和cities表需要用到两个外键列。为了减少输入量，可以在表名后面直接加别名，它们中间的AS是可选的（例如，venues v或venues AS v）。

SELECT v.venue_id, v.name, c.name
FROM venues v INNER JOIN cities c
　 ON v.postal_code=c.postal_code AND v.country_code=c.country_code;
venue_id　 |　 name　 | name
-----------+----------+---------
　　　　 1 | Crystal Ballroom | Portland

可以选择指定PostgreSQL在插入后返回一些列，方法是让请求以RETURNING语句结尾。

INSERT INTO venues (name, postal_code, country_code)
VALUES ('Voodoo Donuts', '97205', 'us') RETURNING venue_id;
 id
--------

　2

无须执行另一个查询，就可以得到新插入的venue_id值。

2.2.4　外联接

除了内联接，PostgreSQL也可以执行外联接（outer join）。外联接是合并两张表的一种方式，不论另一张表中是否存在匹配的列值，第一张表的结果总是必须返回。

最简单的方法是举一个例子，但是首先我们需要创建一张名为events的新表。events表应该有这些列：SERIAL整数event_id、title、starts和ends（类型为时间戳），以及venue_id（引用venues的外键）。图2-4展示了一个数据库的定义图，它涵盖了到目前为止我们创建的所有表。

创建events表后，插入以下值（时间戳作为字符串插入，例如，2012-02-15 17:30），两个节日，以及我们不会详加讨论的一个俱乐部。

title　　　|　　　 starts　　 　 |　　 　　ends　　　　|  venue_id   | event_id
-------------------+---------------------+---------------------+-------------+--------
LARP Club　　　 　　| 2012-02-15 17:30:00 | 2012-02-15 19:30:00 |　　 2 　　|　　　　　1
April Fools Day 　　| 2012-04-01 00:00:00 | 2012-04-01 23:59:00 |　　　　 　|　　　　　2
Christmas Day　 　　| 2012-12-25 00:00:00 | 2012-12-25 23:59:00 |　　　 　　|　　　　　3

我们先来做一个查询，使用内联接返回一个事件的标题和场地名称（INNER JOIN中的INNER并不是必需的，所以这里省略它）。

SELECT e.title, v.name
FROM events e JOIN venues v
　ON e.venue_id = v.venue_id;
　　　title　 　|　　　name
----------------+--------------
LARP Club   　　| Voodoo Donuts

只有列值匹配，INNER JOIN才会返回一行。因为不能有空的venues.venue_id，所以两个空events.venue_id没有关联到任何事情。要查询所有的事件，不管它们是否有场地，我们需要一个左外连接（LEFT OUTER JOIN，简写为LEFT JOIN）。

SELECT e.title, v.name
FROM events e LEFT JOIN venues v
ON e.venue_id = v.venue_id;
　　　　title　　　　|　　　 name
---------------------+---------------
 LARP Club　　　　 　| Voodoo Donuts
 April Fools Day 　　|
 Christmas Day　 　　|

如果你需要反过来，返回所有的场地和匹配的事件，就要用RIGHT JOIN。最后，还有FULL JOIN，这是LEFT和RIGHT的联合；保证能得到每张表中的所有值，列匹配时就会联接。

2.2.5　使用索引快速查找

PostgreSQL的速度（和任何其他RDBMS一样）源于其高效的数据块管理、尽可能少的磁盘块读取、查询优化等技术。如果从events表选择title为Christmas Day的行，则需要进行全表扫描，以返回相关的结果。如果没有索引，就必须从磁盘读取每一行，才能知道是否是匹配行。参见图2-5。

索引是一个特殊的数据结构，目的是避免执行查询时进行全表扫描。当运行CREATE TABLE命令时，你可能注意到这样一条消息：

CREATE TABLE / PRIMARY KEY will create implicit index "events_pkey" \
for table "events"

PostgreSQL自动在主键上创建索引，以主键的列值为索引的键，索引的值则指向磁盘上的一行，如图2-6所示。采用UNIQUE关键字，是强制在表中一列上创建索引的另一种方式。

可以使用CREATE INDEX命令明确地添加一个哈希索引，其中每个值必须是唯一的（就像一个哈希或映射）。

对于这样的操作，树是一个完美的数据结构。要对starts列创建B树索引，使用下面的命令：

CREATE INDEX events_title
　ON events USING hash (title);

对于操作符为小于/大于/等于这样的匹配查询，我们希望索引比简单的哈希更灵活，如B树索引（见图2-7）。考虑用一个查询来查找4月1日或之后发生的所有事件。

SELECT *
FROM events
WHERE starts >= '2012-04-01';

对于这样的操作，树是一个完美的数据结构。要对starts列创建B树索引，使用下面的命令：

CREATE INDEX events_starts
　ON events USING btree (starts);

这样对日期范围的查询将可以避免全表扫描。当扫描数百万或数十亿行时，上述查询的性能差异会很大。

可以用下面的命令，列出数据模式中的所有索引：

book=# \di
值得注意的是，当对列创建一个FOREIGN KEY约束时，PostgreSQL将自动在目标列创建索引。即使你不喜欢使用数据库约束，也会经常发现自己需要在进行联接的列上创建索引，以便加快基于外键的表联接。

2.2.6　第1天总结

我们今天快速介绍了许多内容，涉及很多方面。总结如下：

四十多年来，关系数据库已经成为事实上的数据管理策略，我们中的很多人在其发展的中途，开始了自己的职业生涯。因此，我们通过一些基本的SQL查询，初步探讨了关系模型的一些核心概念。明天我们将详细说明这些基本概念。

2.2.7　第1天作业

查找
1．将PostgreSQL官网的常见问题集（FAQ）和官方文档保存为书签。

2．熟悉PSQL的命令行\?和h的输出信息。

3．在FOREIGN KEY的定义中文档中找到MATCH FULL是什么意思。

完成
1．从pg_class中查询我们创建的所有表（仅我们创建的）。

2．编写一个查询，找到LARP Club事件对应的国家名。

3．修改venues表，增加一个名为active的列，该列为布尔类型，默认值是TRUE。