PostgreSQL-02-入门篇-查询数据

1 简单查询

SELECT 语句简介

使用数据库时最常见的任务之一是使用SELECT语句从表中查询数据。

SELECT语句是 PostgreSQL 中最复杂的语句之一。它有许多子句，您可以使用它们来形成灵活的查询。

由于其复杂性，我们将其分解为许多简短且易于理解的教程，以便您可以更快地了解每个子句。

SELECT语句有以下子句：

• 使用DISTINCT运算符选择不同的行。
• 使用ORDER BY子句对行进行排序。
• 使用WHERE子句过滤行。
• 使用LIMIT或FETCH子句从表中选择行的子集。
• 使用GROUP BY子句将行分组。
• 使用HAVING子句过滤分组。
• 使用INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN等连接子句与其他表连接。
• 使用UNION、INTERSECT和EXCEPT执行集合运算。

SELECT 语句语法

让我们从从单个表中检索数据的SELECT语句的基本形式开始。

下面说明了SELECT语句的语法：

SELECTselect_list
FROMtable_name;

让我们更详细地观察该SELECT语句：

• 首先，指定一个选择列表，该列表可以是要从中检索数据的表中的一列或多列的列表。如果指定多列列表，则需要在两列之间放置逗号 (,) 来分隔它们。如果要从表的所有列中检索数据，可以使用星号 (*) 简写，而不用指定所有列名。选择列表还可以包含表达式或文字值。
• 其次，在FROM关键字后指定要从中查询数据的表的名称。

其中FROM子句是可选的。如果不从任何表查询数据，则可以在SELECT语句中省略FROM子句。

PostgreSQL 在SELECT语句中SELECT列表子句之前计算FROM子句：

请注意，SQL 关键字不区分大小写。这意味着SELECT等价于select或Select。按照惯例，我们将以大写方式使用所有 SQL 关键字，以使查询更易于阅读。

SELECT 示例

让我们看一下使用 PostgreSQL 的SELECT语句的一些示例。

1) 使用 SELECT 语句查询一列数据的示例

此示例使用以下SELECT语句从customer表中查找所有客户的名字：

SELECT first_name FROM customer;

请注意，我们在SELECT语句末尾添加了一个分号 (;)。分号不是 SQL 语句的一部分。它用于向 PostgreSQL 发出 SQL 语句结束的标记。分号还用于分隔两个 SQL 语句。

2) 使用 SELECT 语句查询多列数据的示例

假设您只想知道客户的名字、姓氏和电子邮件，您可以在SELECT列表子句中指定这些列名称，如以下查询所示：

SELECTfirst_name,last_name,email
FROMcustomer;

3) 使用 SELECT 语句查询表所有列数据的示例

以下查询使用SELECT语句从customer表的所有列中检索数据：

SELECT * FROM customer;

在此示例中，我们在SELECT列表子句中使用了星号 (*)，它是所有列的简写。我们没有在SELECT子句中列出所有列，而是使用星号 (*) 来简化查询语句。

但是，当您在python、java、Node.js 或 PHP 等应用程序代码中嵌入 SQL 语句时，在SELECT语句中使用星号 (*) 并不是一个好的做法，原因如下：

1. 数据库性能。假设您有一个包含许多列和大量数据的表，带有星号 (*) 简写的SELECT语句将从表的所有列中选择数据，这对于应用程序来说可能不是必需的。
2. 应用性能。从数据库中检索不必要的数据会增加数据库服务器和应用程序服务器之间的流量。因此，您的应用程序可能响应速度较慢且可扩展性较差。

由于这些原因，只要有可能，最好在SELECT列表子句中显式指定列名，以便仅从数据库获取必要的数据。

对于检查数据库数据的临时查询，您可以使用星号 (*) 简写。

4) 使用带有表达式的 SELECT 语句的示例

以下示例使用SELECT语句返回所有客户的全名和电子邮件：

SELECT first_name || ' ' || last_name,email
FROM customer;

输出：

在此示例中，我们使用串联运算符 ||来串联每个客户的名字、空格和姓氏。

5) 使用带有表达式的 SELECT 语句的示例

以下示例使用带有表达式的SELECT语句。它省略了FROM子句：

SELECT 5 * 3;

2 列别名

列别名简介

列别名允许您为SELECT语句的查询列表中的列或表达式分配临时名称。列别名只在查询执行期间临时性的存在。

下面说明了使用列别名的语法：

SELECT column_name AS alias_name
FROM table_name;

在此语法中，column_name被分配了一个别名alias_name。其中AS关键字是可选的，因此您可以像这样省略它：

SELECT column_name alias_name
FROM table_name;

以下语法说明了如何为SELECT子句中的表达式设置别名：

SELECT expression AS alias_name
FROM table_name;

列别名的主要目的是使查询输出的标题更有意义。

列别名示例

1) 为列分配列别名的示例

以下查询返回customer表中所有客户的名字和姓氏：

SELECT first_name, last_name
FROM customer;

如果要重命名last_name标题，可以使用列别名为其指定一个新名称，如下所示：

SELECT first_name, last_name AS surname
FROM customer;

此查询将surname指定为last_name列的别名：

或者您可以通过删除AS关键字来缩短它，如下所示：

SELECT first_name, last_name surname
FROM customer;

2) 为表达式分配列别名的示例

以下查询返回所有客户的全名。它通过连接名字、空格和姓氏来构造全名：

SELECT first_name || ' ' || last_name 
FROM customer;

请注意，在 PostgreSQL 中，您使用||用作连接运算符，将一个或多个字符串连接成一个字符串。

从输出中可以清楚地看到，该列的标题?column?没有意义。

要解决此问题，您可以为表达式first_name || ' ' || last_name分配一个列别名，例如full_name：

SELECTfirst_name || ' ' || last_name AS full_name
FROMcustomer;

3) 包含空格的列别名

如果列别名包含一个或多个空格，则需要用双引号将其引起来，如下所示：

column_name AS "column alias"

例如：

SELECTfirst_name || ' ' || last_name "full name"
FROMcustomer;

概括

• 使用语法column_name AS alias_name为列分配列别名，或使用expression AS alias_name为表达式分配列别名。

• 关键字AS是可选的。

• 使用双引号 (") 括住包含空格的列别名。

3 排序

排序简介

当您从表中查询数据时，SELECT语句以未指定的顺序返回行。要对结果集的行进行排序，请在SELECT语句中使用ORDER BY子句。

ORDER BY子句允许您根据排序表达式按升序或降序对SELECT语句返回的行进行排序。

下面说明了ORDER BY子句的语法：

SELECT
select_list
FROM
table_name
ORDER BY
sort_expression1 [ASC | DESC],...
sort_expressionN [ASC | DESC];

在这个语法中：

• 首先，指定一个排序表达式，可以是要在ORDER BY关键字之后排序的列或表达式。如果要根据多个列或表达式对结果集进行排序，则需要在两个列或表达式之间放置逗号 (,) 来分隔它们。
• 其次，您可以使用ASC选项对行进行升序排序，以及DESC选项对行进行降序排序。如果省略ASC或DESC选项，则ORDER BY默认使用ASC。

PostgreSQL 按以下顺序执行SELECT语句中的子句： FROM、SELECT和ORDER BY：

按照执行的顺序，如果SELECT语句中有列别名，则可以在ORDER BY子句中使用它。

让我们看一些使用 PostgreSQL 的ORDER BY子句的示例。

ORDER BY 示例

1) 使用 ORDER BY 子句按一列对行进行排序

以下查询使用ORDER BY子句按客户的名字对客户进行升序排序：

SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC;

由于ASC选项是默认选项，因此您可以在ORDER BY子句中省略它，如下所示：

SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name;

2) 使用 ORDER BY 子句按一列对行进行降序排序

以下语句从customer表中查询名字和姓氏，并按姓氏列中的值对行进行降序排序：

SELECTfirst_name,last_name
FROMcustomer
ORDER BYlast_name DESC;

3) 使用 ORDER BY 子句按多列对行进行排序

以下语句从客户表中查询名字和姓氏，并按名字升序和姓氏降序对行进行排序：

SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC,
last_name DESC;

在此示例中，ORDER BY 子句首先按名字列中的值对行进行排序。然后它按姓氏列中的值对已排序的行进行排序。

从输出中可以清楚地看到，两个具有相同名字Kelly的客户的按姓氏降序排列。

4) 使用 ORDER BY 子句按表达式对行进行排序

LENGTH()函数接受一个字符串并返回该字符串的长度。

以下语句查询名字及其长度。它按名字的长度对行进行排序：

SELECT 
first_name,
LENGTH(first_name) len
FROM
customer
ORDER BY 
len DESC;

由于ORDER BY子句是在SELECT列表之后求值的，因此列别名len可用并且可以在ORDER BY子句中使用。

ORDER BY 子句和 NULL

在数据库世界中，NULL是一个标记，指示丢失的数据或数据在记录时未知。

对包含NULL的行进行排序时，可以使用ORDER BY子句的NULLS FIRST或NULLS LAST选项，指定NULL与其他非空值的顺序：

ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST]

NULLS FIRST选项将NULL放置在其他非空值之前，NULL LAST选项将NULL放置在其他非空值之后。

让我们创建一个表来进行演示。

-- create a new table
CREATE TABLE sort_demo(
num INT
);-- insert some data
INSERT INTO sort_demo(num)
VALUES(1),(2),(3),(null);

以下查询返回sort_demo表中的数据：

SELECT num
FROM sort_demo
ORDER BY num;

在此示例中，ORDER BY子句按升序对sort_demo表的num列中的值进行排序。它将NULL置于其他值之后。

因此，如果您使用ASC选项，ORDER BY子句默认使用NULLS LAST选项。因此，以下查询返回相同的结果：

SELECT num
FROM sort_demo
ORDER BY num NULLS LAST;

要放置NULL在其他非空值之前，可以使用NULLS FIRST选项：

SELECT num
FROM sort_demo
ORDER BY num NULLS FIRST;

以下语句对sort_demo表的num列中的值进行降序排序：

SELECT num
FROM sort_demo
ORDER BY num DESC;

从输出中可以清楚地看到，带有DESC选项的ORDER BY子句默认使用NULLS FIRST。

要反转顺序，您可以使用NULLS LAST选项：

SELECT num
FROM sort_demo
ORDER BY num DESC NULLS LAST;

概括

• 使用SELECT语句中的ORDER BY子句对行进行排序。

• 使用ASC选项对行进行升序排序和DESC选项对行进行降序排序。ORDER BY子句默认使用ASC选项。

• 使用NULLS FIRST和NULLS LAST选项显式指定NULL与其他非空值的顺序。

4 去重查询

去重简介

DISTINCT子句在SELECT语句中用于从结果集中删除重复行。DISTINCT子句为每组重复项保留一行。DISTINCT子句可以应用于SELECT语句的选择列表中的一列或多列。

下面说明了DISTINCT子句的语法：

SELECTDISTINCT column1
FROMtable_name;

在此语句中，column1列中的值用于计算重复项。

如果指定多个列，DISTINCT子句将根据这些列值的组合计算重复项。

SELECTDISTINCT column1, column2
FROMtable_name;

在这种情况下，column1和column2列中的值的组合将用于计算重复项。

PostgreSQL 还提供了DISTINCT ON (expression)来保留每组重复项的第一行的功能，使用以下语法：

SELECTDISTINCT ON (column1) column_alias,column2
FROMtable_name
ORDER BYcolumn1,column2;

从SELECT语句返回的行的顺序是未指定的，因此每组重复项的第一行也是未指定的。

最好始终使用带有DISTINCT ON(expression)的ORDER BY子句，以使结果集可预测。

请注意，DISTINCT ON表达式必须与ORDER BY子句中最左边的表达式匹配。

SELECT DISTINCT 示例

让我们创建一个新表，名为distinct_demo，并向其中插入数据来练习DISTINCT子句。

CREATE TABLE distinct_demo (
id serial NOT NULL PRIMARY KEY,
bcolor VARCHAR,
fcolor VARCHAR
);INSERT INTO distinct_demo (bcolor, fcolor)
VALUES
('red', 'red'),
('red', 'red'),
('red', NULL),
(NULL, 'red'),
('red', 'green'),
('red', 'blue'),
('green', 'red'),
('green', 'blue'),
('green', 'green'),
('blue', 'red'),
('blue', 'green'),
('blue', 'blue');SELECT
id,
bcolor,
fcolor
FROM
distinct_demo ;

DISTINCT 单列示例

SELECT
DISTINCT bcolor
FROM
distinct_demo
ORDER BY
bcolor;

DISTINCT 多列示例

以下语句演示了如何在多个列上使用DISTINCT子句：

SELECT
DISTINCT bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;

因为我们在SELECT DISTINCT 子句中指定了bcolor和fcolor列，所以 PostgreSQL 组合了bcolor和fcolor列中的值来计算行的唯一性。

该查询返回distinct_demo表中bcolor和fcolor的唯一组合。请注意，distinct_demo表有两行，并且bcolor和fcolor列中都有red值 。当我们将DISTINCT应用于两列时，一行被从结果集中删除，因为它是重复的。

DISTINCT ON 示例

以下语句按bcolor和fcolor对结果集进行排序，然后对于每组重复项，它保留返回结果集中的第一行。

SELECT
DISTINCT ON (bcolor) bcolor,
fcolor
FROM
distinct_demo 
ORDER BY
bcolor,
fcolor;

5 分页查询

背景

在分页查询中，传统的做法是使用 LIMIT 和 OFFSET 来获取特定页的数据。例如，要获取第一页的数据，可以使用以下 SQL 语句：

SELECT * FROM transactions
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

对于第二页，可以使用：

SELECT * FROM transactions
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;

这种方式的问题在于，随着偏移量（OFFSET）的增加，查询性能会逐渐下降。这是因为 PostgreSQL 必须扫描并跳过前面的所有行才能到达所需的行，这对于大数据集来说是非常低效的。

键集分页

为了解决这个问题，我们可以采用键集分页的方法。键集分页是基于排序键的值来定位数据，而不是基于偏移量。这种方法可以快速定位到数据集中的特定位置，只需要知道上一页的最后一个元素的排序键即可。

键集分页的核心思想是使用排序键来确定查询的起始点。通常我们会使用一个或多个列作为排序键，其中至少有一个列是唯一的，比如主键。

示例

假设我们有一个名为 transactions 的表，其中包含交易记录，我们希望按照 created_at 列降序排序，并且使用主键 id 作为唯一键来保证排序的稳定性。

要获取当前页面的数据和下一页的游标，我们可以使用以下 SQL 语句：

WITH cursor AS (SELECT created_at, id FROM transactionsWHERE id = ? -- 这里的 '?' 是占位符，表示上一页最后一个元素的 idORDER BY created_at DESC, id DESCLIMIT 1
),current_page_and_next_cursor AS (SELECT *FROM transactionsWHERE (created_at, id) >= (SELECT * FROM cursor)ORDER BY created_at DESC, id DESCLIMIT 10 -- 假设每页显示 10 条记录
)SELECT * FROM current_page_and_next_cursor;

这里，cursor CTE 用来定位上一页的最后一个元素。current_page_and_next_cursor CTE 用来获取当前页面的数据和下一个游标的值。我们使用 >= 来确保从上一页的最后一个元素开始获取数据。

为了获取上一页的游标，我们可以使用类似的逻辑：

WITH cursor AS (SELECT created_at, id FROM transactionsWHERE id = ? -- 这里的 '?' 是占位符，表示上一页最后一个元素的 idORDER BY created_at DESC, id DESCLIMIT 1
),previous_cursor AS (SELECT *FROM transactionsWHERE (created_at, id) < (SELECT * FROM cursor)ORDER BY created_at DESC, id DESCLIMIT 10 -- 假设每页显示 10 条记录
)SELECT * FROM previous_cursor;

这里，previous_cursor CTE 用来获取上一页的游标，我们使用 < 来确保从上一页的最后一个元素之前获取数据。

最后，我们可以将这些 CTE 结合起来，以一次性获取当前页、下一页游标和上一页游标：

WITH cursor AS (SELECT created_at, id FROM transactionsWHERE id = ? -- 这里的 '?' 是占位符，表示上一页最后一个元素的 idORDER BY created_at DESC, id DESCLIMIT 1
),current_page_and_next_cursor AS (SELECT *FROM transactionsWHERE (created_at, id) >= (SELECT * FROM cursor)ORDER BY created_at DESC, id DESCLIMIT 11 -- 包含当前页和下一页游标
),previous_cursor AS (SELECT *FROM transactionsWHERE (created_at, id) < (SELECT * FROM cursor)ORDER BY created_at DESC, id DESCLIMIT 1 -- 只获取上一页游标
)SELECT * FROM previous_cursor
UNION ALL
SELECT * FROM current_page_and_next_cursor
LIMIT 10 -- 当前页
UNION ALL
SELECT * FROM current_page_and_next_cursor
OFFSET 10 LIMIT 1 -- 下一页游标
ORDER BY created_at DESC, id DESC;

这个查询首先获取上一页的游标，然后获取当前页的数据，最后获取下一页的游标。注意，这里使用了 UNION ALL 来合并结果，并使用 LIMIT 和 OFFSET 来选择特定的行。

总结

键集分页是一种高效的分页方法，它使用排序键来定位数据，而不是使用偏移量。这种方法可以避免随着偏移量增加而导致的性能下降问题。虽然键集分页不支持随机访问，但它在大多数应用场景中提供了更好的性能和一致性。