PostgreSQL 基础（一）-- 新手教程

12.16

官网

世界上最先进的开源关系数据库

1、新手入门 1.1 安装

当然，在使用之前，你需要安装它。可能已经安装在您的站点上，或者因为它包含在您的操作系统发行版中，或者因为系统管理员已经安装了它。如果是这种情况，您应该从操作系统文档或系统管理员那里获取有关如何访问的信息。

如果你不确定是否已经可用，或者你是否可以使用它进行实验，那么你可以自己安装它。这样做并不难，而且是一种很好的锻炼。可以由任何非特权用户安装;不需要超级用户(root)访问。

如果您自己安装，请参考第16章的安装说明，并在安装完成后返回本指南。一定要仔细阅读关于设置适当的环境变量的部分。

如果您的站点管理员没有以默认方式设置，那么您可能需要做更多的工作。例如，如果数据库服务器机器是远程机器，则需要将 环境变量设置为数据库服务器机器的名称。

可能还必须设置环境变量 。底线是:如果您尝试启动一个应用程序，而它抱怨无法连接到数据库，那么您应该咨询站点管理员，或者(如果是您的话)咨询文档，以确保正确设置了环境。如果你不理解前一段，那么请阅读下一节。

1.2 架构基础知识

在我们继续之前，您应该了解基本的系统架构。理解各部分是如何相互作用的，将使本章更加清晰。

在数据库术语中，使用客户机/服务器模型。一个会话（ ）由以下进程(程序)组成:

作为典型的客户机/服务器应用程序，客户机和服务器可以位于不同的主机上。在这种情况下，它们通过TCP/IP网络连接进行通信。您应该记住这一点，因为可以在客户机机器上访问的文件在数据库服务器机器上可能无法访问(或者只能使用不同的文件名访问)。

服务器可以处理来自客户端的多个并发连接。为此，它为每个连接启动(“forks”)一个新进程。从那时起，客户机和新的服务器进程进行通信，而不受原始进程的干预。因此，主服务器进程总是在运行，等待客户端连接，而客户端和关联的服务器进程来来往往。(当然，所有这些对用户来说都是不可见的。我们在这里提到它只是为了完整。)

1.3 创建数据库

查看是否可以访问数据库服务器的第一个测试是尝试创建一个数据库。一个运行中的服务器可以管理多个数据库。通常，为每个项目或每个用户使用一个单独的数据库。

可能您的站点管理员已经创建了一个数据库供您使用。在这种情况下，您可以省略这一步，直接跳到下一节。

要创建一个新的数据库，在本例中名为mydb，使用以下命令:

$ createdb mydb

如果没有产生响应，则此步骤成功，您可以跳过本节的其余部分。

如果您看到类似如下的消息:

createdb: command not found

那么没有正确安装。要么是根本没有安装它，要么是没有将shell的搜索路径设置为包含它。试着用绝对路径来调用命令:

$ /usr/local/pgsql/bin/createdb mydb

您站点上的路径可能不同。请联系您的站点管理员或检查安装说明以纠正这种情况。

另一种回应可能是:

createdb: could not connect to database postgres: could not connect to server: No such file or directoryIs the server running locally and acceptingconnections on Unix domain socket "/tmp/.s.PGSQL.5432"?

这意味着服务器没有启动，或者它没有在期望的位置启动。同样，请检查安装说明或咨询管理员。

另一种回应可能是:

createdb: could not connect to database postgres: FATAL:  role "joe" does not exist

其中提到了您自己的登录名。如果管理员没有为您创建用户帐户，则会发生这种情况。(用户帐户不同于操作系统用户帐户。)如果您是管理员，请参见第21章创建帐户。您需要成为安装的操作系统用户(通常是)才能创建第一个用户帐户。也可能是你被分配的用户名与你的操作系统用户名不同;在这种情况下，您需要使用-U开关或设置环境变量来指定您的用户名。

如果您有一个用户帐户，但它没有创建数据库所需的特权，您将看到以下内容:

createdb: database creation failed: ERROR:  permission denied to create database

并非每个用户都有创建新数据库的授权。如果拒绝为你创建数据库，那么站点管理员需要授予你创建数据库的权限。如果发生这种情况，请咨询您的站点管理员。如果您自己安装了，那么为了本教程的目的，您应该以启动服务器时的用户帐户登录。[1]（解释一下为什么这样做:用户名与操作系统用户帐户是分开的。当你连接到数据库时，你可以选择连接的用户名;如果不这样做，它将默认使用与当前操作系统帐户相同的名称。碰巧的是，总是有一个用户帐户与启动服务器的操作系统用户具有相同的名称，并且该用户总是具有创建数据库的权限。你也可以在任何地方指定-U选项来选择要连接的用户名，而不是以该用户登录。）

您还可以使用其他名称创建数据库。允许您在给定站点上创建任意数量的数据库。数据库名称的第一个字符必须是字母，长度限制为63字节。一个方便的选择是创建一个与当前用户名同名的数据库。许多工具都假定数据库名称为默认值，因此它可以节省一些输入。要创建该数据库，只需输入:

$ createdb

如果不想再使用数据库，可以将其删除。例如，如果您是数据库mydb的所有者(创建者)，您可以使用以下命令销毁它:

$ dropdb mydb

(对于该命令，数据库名称不默认为用户帐户名称。您总是需要指定它。)此操作将物理地删除与数据库关联的所有文件，并且无法撤消，因此只有在进行大量预先考虑的情况下才能执行此操作。

关于和的更多信息可以分别在和中找到。

1.4 访问数据库

一旦你创建了一个数据库，你可以通过以下方式访问它:

您可能希望启动psql来尝试本教程中的示例。它可以通过输入以下命令来激活mydb数据库:

$ psql mydb

如果您不提供数据库名称，那么它将默认为您的用户帐户名称。在前一节中，您已经使用发现了这个方案。

在psql中，您将看到以下消息:

psql (12.15)
Type "help" for help.mydb=>

最后一行也可以是:

mydb=#

这意味着您是数据库超级用户，如果您自己安装了实例，则很可能是这种情况。作为超级用户意味着您不受访问控制的约束。对于本教程而言，这并不重要。

如果在启动psql时遇到问题，请回到上一节。和psql的诊断是相似的，如果前者可以，后者也应该可以。

psql打印出的最后一行是提示符，它表明psql正在监听您，并且您可以在psql维护的工作空间中键入SQL查询。试试这些命令:

psql程序有许多不是SQL命令的内部命令。它们以反斜杠字符“\”开头。例如，你可以通过输入以下命令获得各种 SQL命令的语法帮助:

mydb=> \h

要退出psql，输入:

mydb=> \q

psql将退出并返回到命令shell。(更多内部命令，键在PSQL提示符下入\?)在psql中记录了psql的全部功能。在本教程中，我们不会显式地使用这些特性，但是您可以在有帮助的时候自己使用它们。

2、SQL语言 2.1 介绍

本章概述如何使用SQL执行简单的操作。本教程只是为了给您一个介绍，并不是关于SQL的完整教程。有很多关于SQL的书，包括[]和[]。您应该知道，一些语言特性是对标准的扩展。

在下面的示例中，我们假设您已经创建了一个名为mydb的数据库，如前一章所述，并且已经能够启动psql。

本手册中的示例也可以在的源代码分发目录src//中找到。(的二进制发行版可能不提供这些文件。)要使用这些文件，首先要切换到该目录并运行make:

$ cd .../src/tutorial
$ make

这将创建脚本并编译包含用户定义函数和类型的C文件。然后，要开始教程，请执行以下操作:

$ psql -s mydb...mydb=> \i /home/ubuntu/postgresql-12.15/src/tutorial/basics.sql

\i命令从指定的文件中读取命令。psql的-s选项将您置于单步模式，在将每个语句发送到服务器之前暂停。本节中使用的命令在.sql文件中。

2.2 概念

是一个关系数据库管理系统(RDBMS)。这意味着它是一个用于管理以关系（）存储的数据的系统。关系本质上是表(table )的数学术语。如今，在表中存储数据的概念非常普遍，似乎是显而易见的，但是还有许多其他组织数据库的方法。类unix操作系统上的文件和目录构成了分层数据库的一个例子。更现代的发展是面向对象的数据库。

每个表都是一个命名的行(rows)集合。给定表的每一行都有一组相同的命名列，每一列()都有一个特定的数据类型。尽管列在每行中都有固定的顺序，但重要的是要记住，SQL并不以任何方式保证表中行的顺序(尽管可以显式地对它们进行排序以便显示)。

表被分组到数据库中，由单个服务器实例管理的数据库集合构成数据库集群。

2.3 创建新表

您可以通过指定表名，以及所有列名和它们的类型来创建一个新表:

CREATE TABLE weather (city            varchar(80),temp_lo         int,           -- low temperaturetemp_hi         int,           -- high temperatureprcp            real,          -- precipitationdate            date
);

您可以使用换行符将其输入到psql中。psql将识别到直到分号才结束命令。

空白(即空格、制表符和换行符)可以在SQL命令中自由使用。这意味着您可以键入与上述命令对齐方式不同的命令，甚至可以在一行中键入所有命令。两个破折号(“--”)表示注释。它们后面的内容将被忽略，直到行尾。SQL对关键字和标识符不区分大小写，除非标识符被双引号(-)括起来以保持大小写(上面没有这样做)。

(80)指定了一种数据类型，可以存储长度不超过80个字符的任意字符串。int是普通的整数类型。real是用于存储单精度浮点数的类型。date 应该不言自明。(是的，date类型的列也被命名为date。这可能是方便的，也可能是令人困惑的——你选择。)

支持标准的SQL类型int、、real、 、char(N)、(N)、date、time、和，以及其他类型的通用实用程序和丰富的几何类型集。可以使用任意数量的用户定义数据类型进行定制。因此，类型名在语法中不是关键字，除非需要在SQL标准中支持特殊情况。

第二个例子将存储城市及其相关的地理位置:

CREATE TABLE cities (name            varchar(80),location        point
);

point类型是特定数据类型的一个例子。

最后，应该提到的是，如果你不再需要一个表，或者想要以不同的方式重新创建它，你可以使用以下命令删除它:

DROP TABLE tablename;

2.4 用行填充表

语句用于向表中填充行:

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

请注意，所有数据类型都使用相当明显的输入格式。非简单数值的常量通常必须用单引号(')括起来，如示例中所示。date 类型接受的内容实际上非常灵活，但在本教程中，我们将坚持使用这里所示的明确格式。

point类型需要一个坐标对作为输入，如下所示:

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

到目前为止使用的语法要求您记住列的顺序。另一种语法允许您显式列出列:

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

如果您愿意，您可以以不同的顺序列出这些列，甚至可以省略一些列，例如，如果降水未知:

INSERT INTO weather (date, city, temp_hi, temp_lo)VALUES ('1994-11-29', 'Hayward', 54, 37);

许多开发人员认为显式列出列比隐式地依赖顺序更好。

请输入上面显示的所有命令，以便在接下来的部分中使用一些数据。

您还可以使用COPY从纯文本文件加载大量数据。这通常更快，因为COPY命令针对此应用程序进行了优化，但灵活性低于。一个例子是:

COPY weather FROM '/home/user/weather.txt';

源文件的文件名必须在运行后端进程的机器上可用，而不是在客户机上可用，因为后端进程直接读取文件。您可以在COPY中了解更多关于COPY命令的信息。

2.5 查询表

要从表中检索数据，表是必须的。使用SQL 语句来执行此操作。该语句被分为选择列表(列出要返回的列的部分)、表列表(列出要从中检索数据的表的部分)和可选限定(指定任何限制的部分)。例如，要检索表的所有行，输入:

SELECT * FROM weather;

这里*是“所有列”的简写。[2]所以，同样的结果也适用于:

(虽然 *对于即兴查询很有用，但它在生产代码中被普遍认为是不好的样式，因为向表中添加一列会改变结果。)

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------San Francisco |      46 |      50 | 0.25 | 1994-11-27San Francisco |      43 |      57 |    0 | 1994-11-29Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

您可以在选择列表中编写表达式，而不仅仅是简单的列引用。例如，你可以这样做:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

请注意如何使用AS子句来重新标记输出列。(AS从句是可选的。)

可以通过添加WHERE子句来“限定()”查询，该子句指定需要哪些行。WHERE子句包含一个布尔(真值)表达式，并且只返回布尔表达式为真的行。通常的布尔运算符(AND、OR和NOT)在限定中是允许的。例如，下面检索旧金山在雨天的天气:

SELECT * FROM weatherWHERE city = 'San Francisco' AND prcp > 0.0;

你可以请求一个查询的结果按排序顺序返回:

SELECT * FROM weatherORDER BY city;

在本例中，没有完全指定排序顺序，因此您可能以任意一种顺序得到旧金山的行。但如果你这样做，你总是会得到如上所示的结果:

SELECT * FROM weatherORDER BY city, temp_lo;

你可以请求从查询结果中删除重复的行:

SELECT DISTINCT cityFROM weather;

这里，结果行顺序可能会有所不同。你可以通过同时使用和ORDER BY来确保结果的一致性:[3](在一些数据库系统中，包括旧版本的, 的实现会自动对行排序，因此ORDER BY是不必要的。但是这不是SQL标准所要求的，并且当前的不能保证会导致行排序。)

SELECT DISTINCT cityFROM weatherORDER BY city;

2.6 表间连接

到目前为止，我们的查询一次只访问了一个表。查询可以一次访问多个表，或者以同时处理表的多行的方式访问同一个表。一次访问相同或不同表的多行查询称为连接查询（join query）。例如，假设您希望列出所有天气记录以及相关城市的位置。为此，我们需要比较 表中每行的city 列与 表中所有行的name 列，并选择这些值匹配的行对。

这只是一个概念模型。连接通常以比实际比较每个可能的行对更有效的方式执行，但这对用户是不可见的。

这将通过以下查询完成:

SELECT *FROM weather, citiesWHERE city = name;

观察结果集的两件事:

SELECT city, temp_lo, temp_hi, prcp, date, locationFROM weather, citiesWHERE city = name;

练习:当省略WHERE子句时，尝试确定该查询的语义。

由于列都有不同的名称，解析器会自动发现它们属于哪个表。如果两个表中有重复的列名，您需要限定( )列名以显示您指的是哪个，如下所示:

SELECT weather.city, weather.temp_lo, weather.temp_hi,weather.prcp, weather.date, cities.locationFROM weather, citiesWHERE cities.name = weather.city;

人们普遍认为，在连接查询中限定所有列名是一种很好的样式，这样，如果稍后将重复的列名添加到其中一个表中，查询就不会失败。

到目前为止，我们看到的联接查询也可以用这种形式编写:

SELECT *FROM weather INNER JOIN cities ON (weather.city = cities.name);

这种语法并不像上面的语法那样常用，但是我们在这里展示它是为了帮助您理解以下主题。

现在我们要想办法把海沃德( )的记录拿回来。我们希望查询做的是扫描表，并为每一行查找匹配的 行。如果没有找到匹配的行，我们希望用一些“空值”替换城市表的列。这种查询称为外部连接(outer join)。(到目前为止，我们看到的连接都是内连接。)命令看起来像这样:

SELECT *FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

这个查询被称为左外连接(left outer join)，因为连接操作符左侧提到的表的每一行至少会在输出中出现一次，而右侧的表只会输出与左表的某些行匹配的行。当输出没有与右表匹配的左表行时，将用空(null)值替换右表列。

练习:也有右外连接(right outer joins )和全外连接( full outer joins)。试着找出它们的作用。

我们也可以将一个表与它本身对立起来。这被称为自连接(self join)。例如，假设我们希望找到在其他天气记录温度范围的所有天气记录。因此，我们需要将每个 行的和列与所有其他行的和列进行比较。我们可以用下面的查询来做到这一点:

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,W2.city, W2.temp_lo AS low, W2.temp_hi AS highFROM weather W1, weather W2WHERE W1.temp_lo < W2.temp_loAND W1.temp_hi > W2.temp_hi;

这里我们将天气表重新标记为W1和W2，以便能够区分连接的左侧和右侧。你也可以在其他查询中使用这些类型的别名来节省一些输入，例如:

SELECT *FROM weather w, cities cWHERE w.city = c.name;

你会经常遇到这种缩写形式。

2.7 聚合函数

像大多数其他关系数据库产品一样，支持聚合函数( )。聚合函数从多个输入行计算单个结果。例如，存在用于计算一组行的count、sum、avg(平均值)、max(最大值)和min(最小值)的聚合。

SELECT max(temp_lo) FROM weather;

如果我们想知道最大的低温发生在哪个城市(或几个城市)，我们可以试试:

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     # WRONG

但这将不起作用，因为聚合max不能在WHERE子句中使用。(这个限制的存在是因为WHERE子句决定哪些行将包含在聚合计算中;所以很明显，它必须在计算聚合函数之前进行评估。)然而，通常情况下，可以通过使用子查询()来再写查询以实现期望的结果:

SELECT city FROM weatherWHERE temp_lo = (SELECT max(temp_lo) FROM weather);

这是可以的，因为子查询是一个独立的计算，它与外部查询中发生的事情分开计算自己的聚合。

在与GROUP BY子句结合使用时，聚合也非常有用。例如，我们可以得到每个城市观测到的数量和最高低温:

SELECT city, count(*), max(temp_lo)FROM weatherGROUP BY city;

每个城市有一个输出行。每个聚合结果都是在匹配该城市的表行上计算的。我们可以使用过滤这些分组行:

SELECT city, count(*), max(temp_lo)FROM weatherGROUP BY cityHAVING max(temp_lo) < 40;

这只会为所有值低于40的城市提供相同的结果。最后，如果我们只关心名字以“S”开头的城市，我们可能会这样做:

SELECT city, count(*), max(temp_lo)FROM weatherWHERE city LIKE 'S%'            -- (1)GROUP BY city;

LIKE操作符进行模式匹配，将在第9.7节中解释。

理解聚合和SQL的WHERE和子句之间的交互是很重要的。WHERE和之间的根本区别在于:WHERE在分组和聚合计算之前选择输入行(因此，它控制哪些行进入聚合计算)，而在分组和聚合计算之后选择被分组后行（group rows）。因此，WHERE子句不能包含聚合函数;尝试使用聚合来确定哪些行将作为聚合的输入是没有意义的。另一方面，子句总是包含聚合函数。(严格地说，你可以写一个不使用聚合的子句，但它很少有用。在WHERE阶段可以更有效地使用相同的条件。)

在前面的示例中，我们可以在WHERE中应用城市名称限制，因为它不需要聚合。这比在中添加限制更有效，因为我们避免了对所有没有通过WHERE检查的行进行分组和聚合计算。

选择进入聚合计算的行的另一种方法是使用，这是一个per- 选项:

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)FROM weatherGROUP BY city;

与WHERE非常相似，不同之处在于它只从它所附加的特定聚合函数的输入中删除行。这里，count聚合只计算低于45的行;但是max 聚合仍然应用于所有行，因此它仍然发现读数为46。

2.8 更新

可以使用 命令更新现有行。假设你发现11月28日之后的温度读数都差了2度。您可以对数据进行如下更正:

UPDATE weatherSET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2WHERE date > '1994-11-28';

2.9 删除

可以使用 命令从表中删除行。假如你对海沃德()的天气不再感兴趣。然后，您可以执行以下操作从表中删除这些行:

DELETE FROM weather WHERE city = 'Hayward';

海沃德的所有天气记录都被删除了。

SELECT * FROM weather;

人们应该警惕这种形式的语句：

DELETE FROM tablename;

如果没有限定条件，将从给定表中删除所有行，使其为空。在此之前，系统不会请求确认!

3、高级特性 3.1 介绍

在前一章中，我们已经介绍了在中使用SQL存储和访问数据的基础知识。现在我们将讨论SQL的一些更高级的特性，它们可以简化管理并防止数据丢失或损坏。最后，我们将看看一些扩展。

本章有时会引用第2章中的例子来修改或改进它们，所以读过第2章会很有用。本章中的一些例子也可以在前面教程目录中的.sql找到。该文件还包含一些要加载的示例数据，这里不再重复。(关于如何使用该文件，请参阅2.1节。)

3.2 视图（Views）

请参考2.6节中的查询。假设您的应用程序对天气记录和城市位置的组合列表特别感兴趣，但是您不希望每次需要时都键入查询。您可以在查询上创建一个视图( view)，该视图为查询提供一个名称，您可以像引用普通表一样引用该查询:

CREATE VIEW myview ASSELECT name, temp_lo, temp_hi, prcp, date, locationFROM weather, citiesWHERE city = name;SELECT * FROM myview;

自由使用视图是良好SQL数据库设计的一个关键方面。视图允许您将表结构的细节封装在一致的接口后面，这些细节可能会随着应用程序的发展而改变。

视图几乎可以在任何可以使用真实表的地方使用。在其他视图上构建视图并不罕见。

3.3 外键 （ Keys）

回想一下第2章中的 和 表。考虑以下问题:您希望确保没有人可以在 表中插入在 表中没有匹配条目的行。这被称为维护数据的引用完整性( )。在简单的数据库系统中，这将首先通过查看表来检查是否存在匹配的记录，然后插入或拒绝新的记录来实现(如果有的话)。这种方法有很多问题，而且非常不方便，所以可以为你做到这一点。