《七周七数据库》一一2.2 第1天:关系、CRUD和联接-阿里云开发者社区

开发者社区> 异步社区> 正文

《七周七数据库》一一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是一样的。
image

$ 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

.

image

当然,这是一次成功的插入。但是我们输入了错误的邮政编码。波特兰(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 vvenues 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_idtitlestartsends(类型为时间戳),以及venue_id(引用venues的外键)。图2-4展示了一个数据库的定义图,它涵盖了到目前为止我们创建的所有表。

image

创建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。

image

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

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

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

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

image

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

CREATE INDEX events_title
 ON events USING hash (title);

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

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

image

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

CREATE INDEX events_starts
 ON events USING btree (starts);

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

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

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

2.2.6 第1天总结

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

image

四十多年来,关系数据库已经成为事实上的数据管理策略,我们中的很多人在其发展的中途,开始了自己的职业生涯。因此,我们通过一些基本的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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10072 0
异步导入导出Redis数据(利用Hiredis、Libevent)
最近工作中需要用到一个将数据从Redis导出到文本(或从文本导入Redis)的工具。找到一个用Ruby写的开源软件redis-dump(http://delanotes.com/redis-dump/)。
1238 0
【阿里云 MVP 月度分享】DRDS分布式数据库的CRUD调优指南
DRDS 分布式数据库作为阿里自主研发的分布式数据库,解决了一些传统单库 RDS 数据库的痛点,本文侧重的不是 DRDS 的原理和如何使用,而是侧重从实际应用的角度如何进行调优。
4705 0
「免费开源」基于Vue和Quasar的crudapi前端SPA项目实战之数据库逆向(十二)
数据库逆向就是通过读取数据库物理表schema信息,然后生成表单元数据,可以看成“dbfirst”模式,即先有数据库表,然后根据表生成元数据,逆向表单后续操作和普通动态表单类似。本文主要介绍了数据库逆向功能,在数据库表单已经存在的基础上,通过数据库逆向功能,快速生成元数据,不需要一行代码,我们就可以得到已有数据库的基本crud功能,包括API和UI。类似于phpmyadmin等数据库UI管理系统,但是比数据库UI管理系统更灵活,更友好。
54 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13882 0
MySQL---数据库从入门走向大神系列(九)-用Java向数据库读写大文本/二进制文件数据
介绍MySQL的文本和图形数据类型: Text 类型: 数据类型:描述 ------------------------------------------------------ char(size):保存固定长度的字符串(可包含字母、数字以及特殊字符)。
998 0
数据库也能自动驾驶?DAS全天候给你保驾护航!
2020年6月9日,“全速重构”2020阿里云·线上峰会即将隆重召开。
527 0
+关注
异步社区
异步社区(www.epubit.com)是人民邮电出版社旗下IT专业图书旗舰社区,也是国内领先的IT专业图书社区,致力于优质学习内容的出版和分享,实现了纸书电子书的同步上架,于2015年8月上线运营。公众号【异步图书】,每日赠送异步新书。
12049
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载