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
语句中使用星号 (*
) 并不是一个好的做法,原因如下:
- 数据库性能。假设您有一个包含许多列和大量数据的表,带有星号 (
*
) 简写的SELECT
语句将从表的所有列中选择数据,这对于应用程序来说可能不是必需的。 - 应用性能。从数据库中检索不必要的数据会增加数据库服务器和应用程序服务器之间的流量。因此,您的应用程序可能响应速度较慢且可扩展性较差。
由于这些原因,只要有可能,最好在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
:
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
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
表中查询名字和姓氏,并按姓氏列中的值对行进行降序排序:
SELECT
first_name,
last_name
FROM
customer
ORDER BY
last_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
子句的语法:
SELECT
DISTINCT column1
FROM
table_name;
在此语句中,column1
列中的值用于计算重复项。
如果指定多个列,DISTINCT
子句将根据这些列值的组合计算重复项。
SELECT
DISTINCT column1, column2
FROM
table_name;
在这种情况下,column1
和column2
列中的值的组合将用于计算重复项。
PostgreSQL 还提供了DISTINCT ON (expression)
来保留每组重复项的第一行的功能,使用以下语法:
SELECT
DISTINCT ON (column1) column_alias,
column2
FROM
table_name
ORDER BY
column1,
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 transactions
WHERE id = ? -- 这里的 '?' 是占位符,表示上一页最后一个元素的 id
ORDER BY created_at DESC, id DESC
LIMIT 1
),
current_page_and_next_cursor AS (
SELECT *
FROM transactions
WHERE (created_at, id) >= (SELECT * FROM cursor)
ORDER BY created_at DESC, id DESC
LIMIT 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 transactions
WHERE id = ? -- 这里的 '?' 是占位符,表示上一页最后一个元素的 id
ORDER BY created_at DESC, id DESC
LIMIT 1
),
previous_cursor AS (
SELECT *
FROM transactions
WHERE (created_at, id) < (SELECT * FROM cursor)
ORDER BY created_at DESC, id DESC
LIMIT 10 -- 假设每页显示 10 条记录
)
SELECT * FROM previous_cursor;
这里,previous_cursor
CTE 用来获取上一页的游标,我们使用 <
来确保从上一页的最后一个元素之前获取数据。
最后,我们可以将这些 CTE 结合起来,以一次性获取当前页、下一页游标和上一页游标:
WITH cursor AS (
SELECT created_at, id FROM transactions
WHERE id = ? -- 这里的 '?' 是占位符,表示上一页最后一个元素的 id
ORDER BY created_at DESC, id DESC
LIMIT 1
),
current_page_and_next_cursor AS (
SELECT *
FROM transactions
WHERE (created_at, id) >= (SELECT * FROM cursor)
ORDER BY created_at DESC, id DESC
LIMIT 11 -- 包含当前页和下一页游标
),
previous_cursor AS (
SELECT *
FROM transactions
WHERE (created_at, id) < (SELECT * FROM cursor)
ORDER BY created_at DESC, id DESC
LIMIT 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
来选择特定的行。
总结
键集分页是一种高效的分页方法,它使用排序键来定位数据,而不是使用偏移量。这种方法可以避免随着偏移量增加而导致的性能下降问题。虽然键集分页不支持随机访问,但它在大多数应用场景中提供了更好的性能和一致性。