PostgreSQL查询简介

介绍

数据库是许多网站和应用程序的关键组成部分，是数据在互联网上存储和交换的核心。数据库管理最重要的方面之一是从数据库中检索数据的实践，无论是临时的还是已编码到应用程序中的过程的一部分。从数据库中检索信息有多种方法，但最常用的方法之一是通过命令行提交查询。

在关系数据库管理系统中，query 是用于从表中检索数据的任何命令。在结构化查询语言 (SQL) 中，几乎总是使用 SELECT 语句进行查询。

在本指南中，我们将讨论 SQL 查询的基本语法以及一些更常用的函数和运算符。我们还将练习使用 PostgreSQL 数据库中的一些示例数据进行 SQL 查询。

PostgreSQL，通常缩写为“Postgres”，是一种采用面向对象方法的关系数据库管理系统，这意味着信息可以表示为 PostgreSQL 模式中的对象或类。 PostgreSQL 与标准 SQL 紧密结合，尽管它还包含其他关系数据库系统中没有的一些特性。

先决条件

通常，本指南中介绍的命令和概念可用于运行任何 SQL 数据库软件的任何基于 Linux 的操作系统。但是，它是专门为运行 PostgreSQL 的 Ubuntu 18.04 服务器编写的。要进行此设置，您将需要以下内容：

具有 sudo 权限的非 root 用户的 Ubuntu 18.04 机器。这可以使用我们的 Initial Server Setup guide for Ubuntu 18.04 进行设置。
PostgreSQL 安装在机器上。如需有关设置的帮助，请按照我们关于如何在 Ubuntu 18.04 上安装和使用 PostgreSQL 的指南中的“安装 PostgreSQL”部分进行操作。

有了这个设置，我们就可以开始教程了。

创建示例数据库

在开始使用 SQL 进行查询之前，我们将首先创建一个数据库和几个表，然后用一些示例数据填充这些表。这将使您在以后开始查询时获得一些实践经验。

对于我们将在本指南中使用的示例数据库，想象以下场景：

你和你的几个朋友都一起庆祝你的生日。每次，小组成员都会前往当地的保龄球馆，参加一场友谊赛，然后每个人都会前往您准备生日人最喜欢的饭菜的地方。

现在这个传统已经持续了一段时间，你决定开始追踪这些锦标赛的记录。此外，为了更轻松地计划晚餐，您决定创建朋友的生日以及他们最喜欢的主菜、配菜和甜点的记录。您决定通过将其记录在 PostgreSQL 数据库中来锻炼您的数据库技能，而不是将这些信息保存在物理分类账中。

首先，以您的 postgres 超级用户身份打开 PostgreSQL 提示符：

sudo -u postgres psql

注意：如果您按照在Ubuntu 18.04上安装PostgreSQL的先决条件教程的所有步骤，您可能已经为您的PostgreSQL安装配置了一个新角色。在这种情况下，您可以使用以下命令连接到 Postgres 提示符，将 sammy 替换为您自己的用户名：

sudo -u sammy psql

接下来，通过运行创建数据库：

CREATE DATABASE birthdays;

然后通过键入以下内容选择此数据库：

\c birthdays

接下来，在该数据库中创建两个表。我们将使用第一个表来跟踪您朋友在保龄球馆的记录。以下命令将创建一个名为 tourneys 的表格，其中包含您每个朋友的 name、他们赢得的锦标赛数量 (wins)、他们的所有-时间 best 得分，以及他们穿什么尺码的保龄球鞋（size）：

CREATE TABLE tourneys ( 
name varchar(30), 
wins real, 
best real, 
size real 
);

运行 CREATE TABLE 命令并使用列标题填充它后，您将收到以下输出：

OutputCREATE TABLE

用一些示例数据填充 tourneys 表：

INSERT INTO tourneys (name, wins, best, size) 
VALUES ('Dolly', '7', '245', '8.5'), 
('Etta', '4', '283', '9'), 
('Irma', '9', '266', '7'), 
('Barbara', '2', '197', '7.5'), 
('Gladys', '13', '273', '8');

您将收到以下输出：

OutputINSERT 0 5

在此之后，在同一数据库中创建另一个表，我们将使用它来存储有关您朋友最喜欢的生日餐的信息。以下命令创建一个名为 dinners 的表，其中包含您每个朋友的 name、他们的 birthdate、他们最喜欢的 entree、他们喜欢的 side菜，还有自己喜欢的dessert：

CREATE TABLE dinners ( 
name varchar(30), 
birthdate date, 
entree varchar(30), 
side varchar(30), 
dessert varchar(30) 
);

与此表类似，您将收到验证表已创建的反馈：

OutputCREATE TABLE

用一些示例数据填充此表：

INSERT INTO dinners (name, birthdate, entree, side, dessert) 
VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'), 
('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'), 
('Irma', '1941-02-18', 'tofu', 'fries', 'cake'), 
('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'), 
('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

OutputINSERT 0 5

一旦该命令成功完成，您就完成了数据库的设置。接下来，我们将介绍 SELECT 查询的基本命令结构。

了解 SELECT 语句

正如介绍中提到的，SQL 查询几乎总是以 SELECT 语句开头。 SELECT 在查询中用于指定应在结果集中返回表中的哪些列。查询也几乎总是包括 FROM，它用于指定语句将查询哪个表。

通常，SQL 查询遵循以下语法：

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

例如，以下语句将从 dinners 表中返回整个 name 列：

SELECT name FROM dinners;

Output  name   
---------
 Dolly
 Etta
 Irma
 Barbara
 Gladys
(5 rows)

您可以通过用逗号分隔它们的名称来从同一个表中选择多个列，如下所示：

SELECT name, birthdate FROM dinners;

Output  name   | birthdate  
---------+------------
 Dolly   | 1946-01-19
 Etta    | 1938-01-25
 Irma    | 1941-02-18
 Barbara | 1948-12-25
 Gladys  | 1944-05-28
(5 rows)

您可以在 SELECT 运算符后面加上一个星号 (*)，而不是命名一个特定的列或一组列，它用作代表表中所有列的占位符。以下命令返回 tourneys 表中的每一列：

SELECT * FROM tourneys;

Output  name   | wins | best | size 
---------+------+------+------
 Dolly   |    7 |  245 |  8.5
 Etta    |    4 |  283 |    9
 Irma    |    9 |  266 |    7
 Barbara |    2 |  197 |  7.5
 Gladys  |   13 |  273 |    8
(5 rows)

WHERE 用于在查询中过滤满足指定条件的记录，并将任何不满足该条件的行从结果中剔除。 WHERE 子句通常遵循以下语法：

. . . WHERE column_name comparison_operator value

WHERE 子句中的比较运算符定义应如何将指定列与值进行比较。以下是一些常见的 SQL 比较运算符：

操作员	它能做什么
`=`	相等性检验
`!=`	不平等检验
`<`	测试小于
`>`	测试大于
`<=`	测试小于或等于
`>=`	测试大于或等于
`BETWEEN`	测试一个值是否在给定范围内
`IN`	测试行的值是否包含在一组指定值中
`EXISTS`	在给定的条件下测试行是否存在
`LIKE`	测试一个值是否与指定的字符串匹配
`IS NULL`	测试 `NULL` 值
`IS NOT NULL`	测试除 `NULL` 以外的所有值

例如，如果您想查找 Irma 的鞋码，可以使用以下查询：

SELECT size FROM tourneys WHERE name = 'Irma';

Output size 
------
    7
(1 row)

SQL 允许使用通配符，这些在 WHERE 子句中使用时特别方便。百分号 (%) 表示零个或多个未知字符，下划线 (_) 表示单个未知字符。如果您尝试在表中查找特定条目，但不确定该条目到底是什么，这些功能很有用。举例来说，假设您忘记了一些朋友最喜欢的主菜，但您确定这道主菜以“t”开头。您可以通过运行以下查询找到它的名称：

SELECT entree FROM dinners WHERE entree LIKE 't%';

Output entree  
-------
 tofu
 tofu
(2 rows)

根据上面的输出，我们看到我们忘记的主菜是 tofu。

有时您使用的数据库中的列或表的名称相对较长或难以阅读。在这些情况下，您可以通过使用 AS 关键字创建别名来使这些名称更具可读性。使用 AS 创建的别名是临时的，仅在创建它们的查询期间存在：

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;

Output    n    |     b      |     d     
---------+------------+-----------
 Dolly   | 1946-01-19 | cake
 Etta    | 1938-01-25 | ice cream
 Irma    | 1941-02-18 | cake
 Barbara | 1948-12-25 | ice cream
 Gladys  | 1944-05-28 | ice cream
(5 rows)

在这里，我们告诉 SQL 将 name 列显示为 n，birthdate 列显示为 b，dessert 列显示为d。

到目前为止，我们所经历的示例包括 SQL 查询中一些更常用的关键字和子句。这些对于基本查询很有用，但如果您尝试基于你的数据。这就是聚合函数发挥作用的地方。

聚合函数

通常，在处理数据时，您不一定希望看到数据本身。相反，您需要关于 数据的信息 。 SQL 语法包含许多函数，只需发出 SELECT 查询，您就可以对数据进行解释或运行计算。这些被称为 聚合函数 。

COUNT 函数计算并返回符合特定条件的行数。例如，如果您想知道有多少朋友喜欢豆腐作为生日主菜，您可以发出以下查询：

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu';

Output count 
-------
     2
(1 row)

AVG 函数返回列的平均值。使用我们的示例表，您可以通过以下查询在您的朋友中找到平均最高分：

SELECT AVG(best) FROM tourneys;

Output  avg  
-------
 252.8
(1 row)

SUM 用于查找给定列的总和。例如，如果您想查看这些年来您和您的朋友打了多少场比赛，您可以运行以下查询：

SELECT SUM(wins) FROM tourneys;

Output sum 
-----
  35
(1 row)

请注意，AVG 和 SUM 函数只有在与数值数据一起使用时才能正常工作。如果您尝试在非数字数据上使用它们，则会导致错误或仅导致 0，具体取决于您使用的 RDBMS：

SELECT SUM(entree) FROM dinners;

OutputERROR:  function sum(character varying) does not exist
LINE 1: select sum(entree) from dinners;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

MIN 用于查找指定列中的最小值。您可以使用此查询来查看迄今为止最差的整体保龄球记录（就获胜次数而言）：

SELECT MIN(wins) FROM tourneys;

Output min 
-----
   2
(1 row)

类似地，MAX 用于查找给定列中的最大数值。以下查询将显示最佳整体保龄球记录：

SELECT MAX(wins) FROM tourneys;

Output max 
-----
  13
(1 row)

与 SUM 和 AVG 不同，MIN 和 MAX 函数可用于数字和字母数据类型。在包含字符串值的列上运行时，MIN 函数将按字母顺序显示第一个值：

SELECT MIN(name) FROM dinners;

Output   min   
---------
 Barbara
(1 row)

同样，当在包含字符串值的列上运行时，MAX 函数将按字母顺序显示最后一个值：

SELECT MAX(name) FROM dinners;

Output max  
------
 Irma
(1 row)

聚合函数有许多超出本节描述的用途。它们在与 GROUP BY 子句一起使用时特别有用，下一节将介绍该子句以及影响结果集排序方式的其他几个查询子句。

操作查询输出

除了 FROM 和 WHERE 子句之外，还有其他几个子句用于处理 SELECT 查询的结果。在本节中，我们将解释一些更常用的查询子句并提供示例。

除了 FROM 和 WHERE 之外，最常用的查询子句之一是 GROUP BY 子句。它通常在您对一列执行聚合函数时使用，但与另一列中的匹配值有关。

例如，假设您想知道有多少朋友喜欢您制作的三道主菜。您可以通过以下查询找到此信息：

SELECT COUNT(name), entree FROM dinners GROUP BY entree;

Output count | entree  
-------+---------
     1 | chicken
     2 | steak
     2 | tofu
(3 rows)

ORDER BY 子句用于对查询结果进行排序。默认情况下，数值按升序排序，文本值按字母顺序排序。为了说明，以下查询列出了 name 和 birthdate 列，但按生日对结果进行排序：

SELECT name, birthdate FROM dinners ORDER BY birthdate;

Output  name   | birthdate  
---------+------------
 Etta    | 1938-01-25
 Irma    | 1941-02-18
 Gladys  | 1944-05-28
 Dolly   | 1946-01-19
 Barbara | 1948-12-25
(5 rows)

请注意，ORDER BY 的默认行为是按升序对结果集进行排序。要反转这一点并使结果集按降序排序，请使用 DESC 关闭查询：

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;

Output  name   | birthdate  
---------+------------
 Barbara | 1948-12-25
 Dolly   | 1946-01-19
 Gladys  | 1944-05-28
 Irma    | 1941-02-18
 Etta    | 1938-01-25
(5 rows)

如前所述，WHERE 子句用于根据特定条件过滤结果。但是，如果您将 WHERE 子句与聚合函数一起使用，它将返回错误，就像以下尝试找出至少三个朋友最喜欢的边的情况一样：

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;

OutputERROR:  aggregate functions are not allowed in WHERE
LINE 1: SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3...

HAVING 子句被添加到 SQL 以提供类似于 WHERE 子句的功能，同时还与聚合函数兼容。将这两个子句之间的区别视为 WHERE 适用于单个记录，而 HAVING 适用于组记录是有帮助的。为此，每当您发出 HAVING 子句时，GROUP BY 子句也必须存在。

下面的例子是另一种尝试找出至少三个朋友最喜欢的配菜，尽管这个会返回没有错误的结果：

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;

Output count | side  
-------+-------
     3 | fries
(1 row)

聚合函数对于汇总给定表中特定列的结果很有用。但是，在很多情况下需要查询多个表的内容。我们将在下一节中介绍一些可以做到这一点的方法。

查询多个表

一个数据库通常包含多个表，每个表包含不同的数据集。 SQL 提供了几种不同的方法来对多个表运行单个查询。

JOIN 子句可用于在查询结果中组合来自两个或多个表的行。它通过查找表之间的相关列并在输出中适当地对结果进行排序来做到这一点。

包含 JOIN 子句的 SELECT 语句通常遵循以下语法：

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.related_column=table2.related_column;

请注意，因为 JOIN 子句比较多个表的内容，所以前面的示例通过在列名前加上表名和句点来指定从哪个表中选择每一列。您可以像这样为任何查询指定应从哪个表中选择列，尽管从单个表中选择时没有必要，正如我们在前面部分中所做的那样。让我们使用我们的示例数据来演示一个示例。

想象一下，你想给你的每个朋友买一双保龄球鞋作为生日礼物。因为有关您朋友的生日和鞋码的信息保存在不同的表中，所以您可以分别查询这两个表，然后比较每个表的结果。但是，使用 JOIN 子句，您可以通过单个查询找到所需的所有信息：

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
JOIN dinners ON tourneys.name=dinners.name;

Output  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
(5 rows)

此示例中使用的 JOIN 子句不带任何其他参数，是 inner JOIN 子句。这意味着它会选择两个表中具有匹配值的所有记录并将它们打印到结果集中，而排除任何不匹配的记录。为了说明这个想法，让我们在每个表中添加一个没有对应条目的新行：

INSERT INTO tourneys (name, wins, best, size) 
VALUES ('Bettye', '0', '193', '9');

INSERT INTO dinners (name, birthdate, entree, side, dessert) 
VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

然后，使用 JOIN 子句重新运行之前的 SELECT 语句：

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
JOIN dinners ON tourneys.name=dinners.name;

Output  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
(5 rows)

请注意，由于 tourneys 表没有 Lesley 的条目，而 dinners 表没有 Bettye 的条目，因此输出中没有这些记录。

但是，可以使用 outer JOIN 子句从其中一个表中返回所有记录。外部 JOIN 子句写成 LEFT JOIN、RIGHT JOIN 或 FULL JOIN。

LEFT JOIN 子句返回“左”表中的所有记录，仅返回右表中的匹配记录。在外连接的上下文中，左表是 FROM 子句引用的表，右表是 JOIN 语句之后引用的任何其他表。

再次运行之前的查询，但这次使用 LEFT JOIN 子句：

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
LEFT JOIN dinners ON tourneys.name=dinners.name;

此命令将返回左表中的每条记录（在本例中为 tourneys），即使它在右表中没有对应的记录。只要右表中没有匹配的记录，它就会作为空白值或 NULL 返回，具体取决于您的 RDBMS：

Output  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
 Bettye  |    9 | 
(6 rows)

现在再次运行查询，这次使用 RIGHT JOIN 子句：

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
RIGHT JOIN dinners ON tourneys.name=dinners.name;

这将返回右表 (dinners) 中的所有记录。因为 Lesley 的生日记录在右表中，但左表中没有她的对应行，所以 name 和 size 列将在该行中返回为空白值：

Output  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
         |      | 1946-05-02
(6 rows)

请注意，左右连接可以写成 LEFT OUTER JOIN 或 RIGHT OUTER JOIN，尽管子句的 OUTER 部分是隐含的。同样，指定 INNER JOIN 将产生与仅写入 JOIN 相同的结果。

有一个称为 FULL JOIN 的第四个连接子句可用于某些 RDBMS 发行版，包括 PostgreSQL。 FULL JOIN 将返回每个表中的所有记录，包括任何空值：

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
FULL JOIN dinners ON tourneys.name=dinners.name;

Output  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
 Bettye  |    9 | 
         |      | 1946-05-02
(7 rows)

注意： 在撰写本文时，MySQL 或 MariaDB 都不支持 [X39X] 子句。

作为使用 FULL JOIN 从多个表中查询所有记录的替代方法，您可以使用 UNION 子句。

UNION 运算符的工作方式与 JOIN 子句略有不同：UNION 不是使用单个 SELECT 语句将来自多个表的结果打印为唯一列，而是将将两个 SELECT 语句的结果放在一个列中。

为了说明，运行以下查询：

SELECT name FROM tourneys UNION SELECT name FROM dinners;

此查询将删除所有重复条目，这是 UNION 运算符的默认行为：

Output  name   
---------
 Irma
 Etta
 Bettye
 Gladys
 Barbara
 Lesley
 Dolly
(7 rows)

要返回所有条目（包括重复项），请使用 UNION ALL 运算符：

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;

Output  name   
---------
 Dolly
 Etta
 Irma
 Barbara
 Gladys
 Bettye
 Dolly
 Etta
 Irma
 Barbara
 Gladys
 Lesley
(12 rows)

结果表中的列名和列数反映了第一个SELECT语句查询到的列名和列数。注意，当使用UNION查询多张表的多列时，每条SELECT语句必须查询相同数量的列，各列必须具有相似的数据类型，且每条中的列SELECT 语句的顺序必须相同。以下示例显示了如果在查询不同列数的两个 SELECT 语句上使用 UNION 子句可能产生的结果：

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;

OutputERROR:  each UNION query must have the same number of columns
LINE 1: SELECT name FROM dinners UNION SELECT name, wins FROM tourne...

查询多个表的另一种方法是使用 子查询 。子查询（也称为内部或 嵌套查询 ）是包含在另一个查询中的查询。这些在您尝试根据单独的聚合函数的结果过滤查询结果的情况下很有用。

为了说明这个想法，假设你想知道你的哪些朋友比芭芭拉赢得了更多的比赛。与其查询 Barbara 赢了多少场比赛，然后运行另一个查询来查看谁赢了更多比赛，您可以使用一个查询来计算两者：

SELECT name, wins FROM tourneys 
WHERE wins > (
SELECT wins FROM tourneys WHERE name = 'Barbara'
);

Output  name  | wins 
--------+------
 Dolly  |    7
 Etta   |    4
 Irma   |    9
 Gladys |   13
(4 rows)

该语句中的子查询只运行一次；只需要在name列中与Barbara在同一行的wins列中查找值，子查询和外查询返回的数据是独立的另一个。但是，在某些情况下，外部查询必须首先读取表中的每一行并将这些值与子查询返回的数据进行比较，以便返回所需的数据。在这种情况下，子查询被称为 相关子查询 。

以下语句是相关子查询的示例。此查询旨在找出您的哪些朋友赢得的比赛比鞋码相同的朋友的平均数多：

SELECT name, size FROM tourneys AS t 
WHERE wins > (
SELECT AVG(wins) FROM tourneys WHERE size = t.size
);

为了完成查询，它必须首先从外部查询中收集 name 和 size 列。然后，它将该结果集中的每一行与内部查询的结果进行比较，从而确定具有相同鞋码的个人的平均获胜次数。因为你只有两个鞋码相同的朋友，所以结果集中只能有一行：

Output name | size 
------+------
 Etta |    9
(1 row)

如前所述，子查询可用于从多个表中查询结果。举最后一个例子来说明这一点，假设您想为该团体历史上最好的投球手举办一顿惊喜晚餐。您可以通过以下查询找到您的哪些朋友拥有最好的保龄球记录并返回他们最喜欢的一餐：

SELECT name, entree, side, dessert 
FROM dinners 
WHERE name = (SELECT name FROM tourneys 
WHERE wins = (SELECT MAX(wins) FROM tourneys));

Output  name  | entree | side  |  dessert  
--------+--------+-------+-----------
 Gladys | steak  | fries | ice cream
(1 row)

请注意，该语句不仅包含子查询，而且还包含该子查询中的子查询。

结论

发出查询是数据库管理领域中最常执行的任务之一。有许多数据库管理工具，例如 phpMyAdmin 或 pgAdmin，它们允许您执行查询和可视化结果，但从命令发出 SELECT 语句line 仍然是一种广泛使用的工作流程，它还可以为您提供更好的控制。

如果您是使用 SQL 的新手，我们鼓励您使用我们的 SQL Cheat Sheet 作为参考，并查看 official PostgreSQL 文档。此外，如果您想了解有关 SQL 和关系数据库的更多信息，您可能会对以下教程感兴趣：