PostgreSQL中的查询简介

原创

不会飞的蝴蝶

修改于 2018-11-14 16:49:25

12.4K0

修改于 2018-11-14 16:49:25

介绍

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

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

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

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

准备

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

具有sudo权限的非root用户的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命令并使用列标题填充它后，您将收到以下输出：

CREATE 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');

您将收到以下输出：

INSERT 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) 
);

同样，对于此表，您将收到验证表已创建的反馈：

CREATE 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;

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

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

SELECT name, birthdate FROM dinners;

  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;

  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';

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

SQL允许使用通配符，这些在WHERE子句中使用时特别方便。百分号（%）表示零个或多个未知字符，下划线（_）表示单个未知字符。如果您尝试在表中查找特定条目，但不确定该条目是什么，则这些条目很有用。为了说明，让我们说你已经忘记了几个朋友最喜欢的主菜，但你确定这个特别的主菜以“t”开头。您可以通过运行以下查询找到它的名称：

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

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

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

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

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

    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';

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

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

SELECT AVG(best) FROM tourneys;

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

SUM用于查找给定列的总和。例如，如果你想看看你和你的朋友多年来有多少游戏，你可以运行这个查询：

SELECT SUM(wins) FROM tourneys;

 sum 
-----
  35
(1 row)

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

SELECT SUM(entree) FROM dinners;

ERROR:  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;

 min 
-----
   2
(1 row)

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

SELECT MAX(wins) FROM tourneys;

 max 
-----
  13
(1 row)

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

SELECT MIN(name) FROM dinners;

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

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

SELECT MAX(name) FROM dinners;

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

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

操作查询输出

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

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

例如，假设您想知道有多少朋友更喜欢您制作的三个主菜中的每一个。您可以使用以下查询找到此信息：

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

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

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

SELECT name, birthdate FROM dinners ORDER BY birthdate;

  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;

  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;

ERROR:  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;

 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;

  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;

  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的条目，因此这些记录不在此输出中。

但是，可以使用外部 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列将在该行中作为空白值返回：

  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;

  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条款略有不同，不是打印从多个表作为使用一个唯一的列结果的SELECT语句，而是用UNION将两个SELECT语句结果结合成一列。

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

SELECT name FROM tourneys UNION SELECT name FROM dinners;

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

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

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

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;

  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;

ERROR:  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'
);

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

此语句中的子查询只运行一次; 它只需要从名称列中与Barbara中的name行找到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));

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

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

结论

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

想要了解更多关于PostgreSQL中的查询简介的相关教程，请前往腾讯云+社区学习更多知识。

参考文献：《An Introduction to Queries in PostgreSQL》

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

postgresql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。