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

语句的语法：

SELECT
   select_list
FROM
   table_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

列表子句中指定这些列名称，如以下查询所示：

SELECT
   first_name,
   last_name,
   email
FROM
   customer;

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

子句：

SELECT5*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

：

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

3) 包含空格的列别名

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

column_name AS"column alias"

例如：

SELECT
    first_name ||' '|| last_name "full name"FROM
    customer;

概括

• 使用语法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
ORDERBY
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
ORDERBY
first_name ASC;

由于

ASC

选项是默认选项，因此您可以在

ORDER BY

子句中省略它，如下所示：

SELECT
first_name,
last_name
FROM
customer
ORDERBY
first_name;

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

以下语句从

customer

表中查询名字和姓氏，并按姓氏列中的值对行进行降序排序：

SELECT
       first_name,
       last_name
FROM
       customer
ORDERBY
       last_name DESC;

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

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

SELECT
first_name,
last_name
FROM
customer
ORDERBY
first_name ASC,
last_name DESC;

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

从输出中可以清楚地看到，两个具有相同名字

Kelly

的客户的按姓氏降序排列。

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

LENGTH()

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

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

SELECT 
first_name,
LENGTH(first_name) len
FROM
customer
ORDERBY 
len DESC;

由于

ORDER BY

子句是在

SELECT

列表之后求值的，因此列别名

len

可用并且可以在

ORDER BY

子句中使用。

ORDER BY 子句和 NULL

在数据库世界中，

NULL

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

对包含

NULL

的行进行排序时，可以使用

ORDER BY

子句的

NULLS FIRST

或

NULLS LAST

选项，指定

NULL

与其他非空值的顺序：

ORDERBY sort_expresssion [ASC|DESC][NULLS FIRST| NULLS LAST]

NULLS FIRST

选项将

NULL

放置在其他非空值之前，

NULL LAST

选项将

NULL

放置在其他非空值之后。

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

-- create a new tableCREATETABLE sort_demo(
num INT);-- insert some dataINSERTINTO sort_demo(num)VALUES(1),(2),(3),(null);

以下查询返回

sort_demo

表中的数据：

SELECT num
FROM sort_demo
ORDERBY num;

在此示例中，

ORDER BY

子句按升序对

sort_demo

表的

num

列中的值进行排序。它将

NULL

置于其他值之后。

因此，如果您使用

ASC

选项，

ORDER BY

子句默认使用

NULLS LAST

选项。因此，以下查询返回相同的结果：

SELECT num
FROM sort_demo
ORDERBY num NULLS LAST;

要放置

NULL

在其他非空值之前，可以使用

NULLS FIRST

选项：

SELECT num
FROM sort_demo
ORDERBY num NULLS FIRST;

以下语句对

sort_demo

表的

num

列中的值进行降序排序：

SELECT num
FROM sort_demo
ORDERBY num DESC;

从输出中可以清楚地看到，带有

DESC

选项的

ORDER BY

子句默认使用

NULLS FIRST

。

要反转顺序，您可以使用

NULLS LAST

选项：

SELECT num
FROM sort_demo
ORDERBY 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
FROM
   table_name;

在此语句中，

column1

列中的值用于计算重复项。

如果指定多个列，

DISTINCT

子句将根据这些列值的组合计算重复项。

SELECTDISTINCT column1, column2
FROM
   table_name;

在这种情况下，

column1

和

column2

列中的值的组合将用于计算重复项。

PostgreSQL 还提供了

DISTINCT ON (expression)

来保留每组重复项的第一行的功能，使用以下语法：

SELECTDISTINCTON(column1) column_alias,
   column2
FROM
   table_name
ORDERBY
   column1,
   column2;

从

SELECT

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

最好始终使用带有

DISTINCT ON(expression)

的ORDER BY子句，以使结果集可预测。

请注意，

DISTINCT ON

表达式必须与

ORDER BY

子句中最左边的表达式匹配。

SELECT DISTINCT 示例

让我们创建一个新表，名为

distinct_demo

，并向其中插入数据来练习

DISTINCT

子句。

CREATETABLE distinct_demo (
id serialNOTNULLPRIMARYKEY,
bcolor VARCHAR,
fcolor VARCHAR);INSERTINTO 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 单列示例

SELECTDISTINCT bcolor
FROM
distinct_demo
ORDERBY
bcolor;

DISTINCT 多列示例

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

DISTINCT

子句：

SELECTDISTINCT bcolor,
fcolor
FROM
distinct_demo
ORDERBY
bcolor,
fcolor;

因为我们在

SELECT DISTINCT

子句中指定了

bcolor

和

fcolor

列，所以 PostgreSQL 组合了

bcolor

和

fcolor

列中的值来计算行的唯一性。

该查询返回

distinct_demo

表中

bcolor

和

fcolor

的唯一组合。请注意，

distinct_demo

表有两行，并且

bcolor

和

fcolor

列中都有

red

值 。当我们将

DISTINCT

应用于两列时，一行被从结果集中删除，因为它是重复的。

DISTINCT ON 示例

以下语句按

bcolor

和

fcolor

对结果集进行排序，然后对于每组重复项，它保留返回结果集中的第一行。

SELECTDISTINCTON(bcolor) bcolor,
fcolor
FROM
distinct_demo 
ORDERBY
bcolor,
fcolor;

5 分页查询

背景

在分页查询中，传统的做法是使用

LIMIT

和

OFFSET

来获取特定页的数据。例如，要获取第一页的数据，可以使用以下 SQL 语句：

SELECT*FROMtransactionsORDERBY created_at DESCLIMIT10OFFSET0;

对于第二页，可以使用：

SELECT*FROMtransactionsORDERBY created_at DESCLIMIT10OFFSET10;

这种方式的问题在于，随着偏移量（

OFFSET

）的增加，查询性能会逐渐下降。这是因为 PostgreSQL 必须扫描并跳过前面的所有行才能到达所需的行，这对于大数据集来说是非常低效的。

键集分页

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

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

示例

假设我们有一个名为

transactions

的表，其中包含交易记录，我们希望按照

created_at

列降序排序，并且使用主键

id

作为唯一键来保证排序的稳定性。

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

WITHcursorAS(SELECT created_at, id FROMtransactionsWHERE id = ? -- 这里的 '?' 是占位符，表示上一页最后一个元素的 idORDERBY created_at DESC, id DESCLIMIT1),

current_page_and_next_cursor AS(SELECT*FROMtransactionsWHERE(created_at, id)>=(SELECT*FROMcursor)ORDERBY created_at DESC, id DESCLIMIT10-- 假设每页显示 10 条记录)SELECT*FROM current_page_and_next_cursor;

这里，

cursor

CTE 用来定位上一页的最后一个元素。

current_page_and_next_cursor

CTE 用来获取当前页面的数据和下一个游标的值。我们使用

>=

来确保从上一页的最后一个元素开始获取数据。

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

WITHcursorAS(SELECT created_at, id FROMtransactionsWHERE id = ? -- 这里的 '?' 是占位符，表示上一页最后一个元素的 idORDERBY created_at DESC, id DESCLIMIT1),

previous_cursor AS(SELECT*FROMtransactionsWHERE(created_at, id)<(SELECT*FROMcursor)ORDERBY created_at DESC, id DESCLIMIT10-- 假设每页显示 10 条记录)SELECT*FROM previous_cursor;

这里，

previous_cursor

CTE 用来获取上一页的游标，我们使用

<

来确保从上一页的最后一个元素之前获取数据。

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

WITHcursorAS(SELECT created_at, id FROMtransactionsWHERE id = ? -- 这里的 '?' 是占位符，表示上一页最后一个元素的 idORDERBY created_at DESC, id DESCLIMIT1),

current_page_and_next_cursor AS(SELECT*FROMtransactionsWHERE(created_at, id)>=(SELECT*FROMcursor)ORDERBY created_at DESC, id DESCLIMIT11-- 包含当前页和下一页游标),

previous_cursor AS(SELECT*FROMtransactionsWHERE(created_at, id)<(SELECT*FROMcursor)ORDERBY created_at DESC, id DESCLIMIT1-- 只获取上一页游标)SELECT*FROM previous_cursor
UNIONALLSELECT*FROM current_page_and_next_cursor
LIMIT10-- 当前页UNIONALLSELECT*FROM current_page_and_next_cursor
OFFSET10LIMIT1-- 下一页游标ORDERBY created_at DESC, id DESC;

这个查询首先获取上一页的游标，然后获取当前页的数据，最后获取下一页的游标。注意，这里使用了

UNION ALL

来合并结果，并使用

LIMIT

和

OFFSET

来选择特定的行。

总结

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