PostgreSQL中的查询简介

介绍

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

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

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

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

先决条件

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

• 具有sudo权限的非root用户的Ubuntu 18.04计算机。 这可以使用我们的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 （ 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 ，他们喜欢的配菜和他们最喜欢的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子句中的WHERE定义应如何将指定列与值进行比较。 以下是一些常见的SQL比较运算符：

例如，如果您想查找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列，但是按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发出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子句没有任何其他参数，是一个内部 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的条目，而tourneys表没有Bettye的条目，因此这些记录不在此输出中。

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

LEFT JOIN子句返回“left”表中的所有记录，只返回右表中的匹配记录。 在外连接的上下文中，左表是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都不支持FULL JOIN子句。

作为使用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...

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

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

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语句仍然是一个广泛实践的工作流程，也可以为您提供更好的控制。

如果您刚开始使用SQL，我们建议您使用我们的SQL 备忘 单作为参考，并查看官方的PostgreSQL文档 。 此外，如果您想了解有关SQL和关系数据库的更多信息，您可能会对以下教程感兴趣：

• 了解SQL和NoSQL数据库以及不同的数据库模型
• 如何在Ubuntu 18.04上使用PostgreSQL 10设置逻辑复制
• 如何保护PostgreSQL免受自动攻击