PostgreSQL 05 - DQL

发表于 更新于 分类于 ， 评论数： 阅读次数：

  PostgreSQL: The World's Most Advanced Open Source Relational Database.

  DQL (Data Query Language) 是 SQL (Structured Query Language) 的一部分，主要用于从数据库中检索和查询数据。DQL 主要包含 SELECT 语句及其相关的子句和操作。

一般语法

  SQL 中，SELECT 命令用于指定查询，SELECT 命令的一般语法为：

[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]

  一种简单的查询形式为：

SELECT * FROM table1;

  此命令将检索 table1 中的所有行和用户定义的所有列，选择列表 * 表示选中表中用户定义的所有列。选择列表的元素还可以是引用列名的表达式，例如：

SELECT a, b + c FROM table1;

  FROM table1 是一种简单的表表达式：它只读取一张表。通常，表表达式可以是基本表、联接和子查询的复杂构造。但也可以完全省略表表达式，并将 SELECT 命令用作计算器：

SELECT 3 * 4;

  选择列表的元素除了可以是常量表达式，还可以是函数调用：

SELECT random();

表表达式

  表表达式计算一个表：表表达式包含一个 FROM 子句，该子句后面可以跟 WHERE、GROUP BY 和 HAVING 子句。简单的表表达式仅引用磁盘上的表，即所谓的基表，但可以使用更复杂的表达式以各种方式修改或组合基表。

  表表达式中可选的 WHERE、GROUP BY 和 HAVING 子句指定在 FROM 子句中派生的表上执行的连续转换管道。所有这些转换都会生成一个虚拟表，该表提供传递给选择列表以计算查询的输出行的行。

FROM 子句

  FROM 子句从用逗号分隔的表引用列表 (包含一个或多个其他表) 中派生一个表：

FROM table_reference [, table_reference [, ...]]

  表引用可以是表名 (可能是模式限定的)，或派生表，例如子查询、JOIN 构造或这些的复杂组合。如果在 FROM 子句中列出了多个表引用，则这些表将进行交叉联接 (形成它们行的笛卡尔积)。FROM 子句的结果是一个中间虚拟表，然后可以由 WHERE、GROUP BY 和 HAVING 子句进行转换，最终成为整个表表达式的结果。

联接表

  联接表是根据特定联接类型规则从另外两个 (真实或派生) 表派生的表，联接表的通用语法为：

T1 join_type T2 [ join_condition ]

  所有类型的联接都可以串联或嵌套在一起：T1 和 T2 都可以是联接表。可以在 JOIN 子句周围使用括号来控制联接顺序。如果没有括号，JOIN 子句将从左到右嵌套。

  1. 交叉联接
T1 CROSS JOIN T2

  联接表将包含 T1 和 T2 中行的所有组合 (即笛卡儿积)，行由 T1 中的所有列后跟 T2 中的所有列组成。如果两个表分别有 N 和 M 行，则联接表将有 N * M 行。

  FROM T1 CROSS JOIN T2等效于FROM T1 INNER JOIN T2 ON TRUE，还等效于FROM T1, T2。

  注意：当出现两张以上表时，后一种等效关系并不完全成立，因为 JOIN 的绑定比逗号更紧密。例如FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition与FROM T1, T2 INNER JOIN T3 ON condition不同，因为在第一种情况下，condition 可以引用 T1，而在第二种情况下则不能。

  1. 限定连接
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

  在所有形式中，单词 INNER 和 OUTER 均为可选。INNER 为默认值，LEFT、RIGHT 和 FULL 意味着外部连接。连接条件在 ON 或 USING 子句中指定，或由单词 NATURAL 隐式指定。连接条件确定了来自两个源表的哪些行被认为匹配。

  • INNER JOIN：对于 T1 的每一行 R1，连接表都有 T2 中每一行的行，该行满足与 R1 的连接条件。

  • LEFT OUTER JOIN：首先，执行内部连接。然后，对于 T1 中的每一行，如果该行不满足与 T2 中任何行的连接条件，则添加一个连接行，其中 T2 的列中包含空值。因此，连接表始终至少为 T1 中的每一行包含一行。

  • RIGHT OUTER JOIN：首先，执行内部连接。然后，对于 T2 中的每一行，如果该行不满足与 T1 中任何行的连接条件，则添加一个连接行，其中 T1 的列中包含空值。这是左连接的逆运算：结果表将始终为 T2 中的每一行包含一行。

  • FULL OUTER JOIN：首先，执行内联接。然后，对于 T1 中不满足与 T2 中任何行联接条件的每一行，在 T2 的列中添加一个联接行，其中包含空值。此外，对于 T2 中不满足与 T1 中任何行联接条件的每一行，在 T1 的列中添加一个联接行，其中包含空值。

  ON 子句是最通用的联接条件类型：它采用与 WHERE 子句中使用的相同类型的布尔值表达式。如果 ON 表达式计算结果为真，则来自 T1 和 T2 的一对行匹配。

  USING 子句是一种简写形式，允许利用联接两侧对联接列使用相同名称的特定情况。它采用共享列名的逗号分隔列表，并形成一个联接条件，其中包含对每个列名的相等性比较。例如，使用USING (a, b)联接 T1 和 T2 会生成联接条件ON T1.a = T2.a AND T1.b = T2.b。

  此外，JOIN USING的输出会抑制冗余列：无需打印两个匹配的列，因为它们必须具有相等的值。虽然JOIN ON会生成来自 T1 的所有列，后跟来自 T2 的所有列，但 JOIN USING 会针对每个列对列表 (按列出的顺序) 生成一个输出列，后跟来自 T1 的任何剩余列，后跟来自 T2 的任何剩余列。

  最后，NATURAL 是 USING 的简写形式：它形成一个 USING 列表，其中包含出现在两个输入表中的所有列名。与 USING 一样，这些列只在输出表中出现一次。如果没有公共列名，NATURAL JOIN的行为类似于JOIN ... ON TRUE，生成一个笛卡尔积联接。

  USING 相对安全：不会受到联接关系中列的更改影响，因为只有列出的列会组合在一起。NATURAL 的风险更大，因为对任一关系的任何架构更改 (导致出现新的匹配列名) 都会导致联接也组合该新列。

表别名

  可以为表和复杂表引用指定一个临时名称 (表别名)，用于在查询的其余部分中引用派生表，例如：

FROM table_reference [AS] alias

  AS 关键字是可选的噪音，alias 可以是任何标识符。

  一旦指定了表别名，就不允许在查询的其他地方通过原始名称引用表，因此，这是无效的：

SELECT * FROM my_table AS m WHERE my_table.a > 5;

  表别名主要用于符号方便，但在将表联接到自身时必须使用它们，例如：

SELECT * FROM p AS m JOIN p AS c ON m.id1 = c.id2;

  括号用于解决歧义，思考：

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

  另一种形式的表别名可以同时为表的列以及表本身指定临时名称：

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

  如果指定的列别名少于实际表的列数，则不会重命名剩余的列。此语法特别适用于自联接或子查询。

  当别名应用于 JOIN 子句的输出时，别名将隐藏 JOIN 中的原始名称，例如：

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

  第二条语句是无效的：表别名 a 在别名 c 外不可见。

子查询

  指定派生表的子查询必须用括号括起来。可以为它们分配表别名，还可以选择分配列别名。例如：

FROM (SELECT * FROM table1) AS alias_name

  此示例等效于FROM table1 AS alias_name。子查询还可以是VALUES列表：

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

  SQL 标准规定必须为子查询提供表别名，但是 PostgreSQL 允许省略别名。

WHERE 子句

  WHERE 子句的语法为：

WHERE search_condition

  其中 search_condition 可以是任何返回 boolean 值的表达式。

  在 FROM 子句处理完成后，会根据 search_condition 检查派生虚拟表中的每一行。如果条件结果为真，则将该行保留在输出表中，否则 (如果结果为假或 NULL) 则将其丢弃。搜索条件通常至少引用 FROM 子句中生成的表的一列 (这不是必需的，否则 WHERE 子句将相当无用)。

  内连接的连接条件可以写在 WHERE 子句中，也可以写在 JOIN 子句中。这些表表达式是等效的：

FROM a, b WHERE a.id = b.id AND b.val > 5
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
FROM a NATURAL JOIN b WHERE b.val > 5

  对于外部连接，没有选择：它们必须在 FROM 子句中完成。ON 或 USING 外部连接的子句不等效于 WHERE 条件，因为它会导致添加行 (对于不匹配的输入行) 以及删除最终结果中的行。

GROUP BY 和 HAVING 子句

  通过 WHERE 筛选后，派生的输入表可能会使用 GROUP BY 子句进行分组，并使用 HAVING 子句删除组：

SELECT select_list
    FROM ...
    [WHERE ...]
    GROUP BY grouping_column_reference [, grouping_column_reference]...

  GROUP BY 子句用于将表中具有所有列中相同值的行分组在一起。列的列出顺序无关紧要。其效果是将具有公共值的每组行合并到一个代表组中所有行的组行中。这样做是为了消除输出中的冗余或计算适用于这些组的聚合。

  一般来说，如果对表进行分组，则未在 GROUP BY 中列出的列不能引用，除非在聚合表达式中：

SELECT x, sum(y) FROM test1 GROUP BY x;

  在严格的 SQL 中，GROUP BY 只能按源表的列进行分组，但 PostgreSQL 将其扩展为还允许 GROUP BY 按选择列表中的列进行分组。还允许按值表达式而不是简单列名进行分组。

  如果表已使用 GROUP BY 进行分组，但只想关注某些特定的组，则可以使用 HAVING 子句消除结果中的组，类似于 WHERE 子句，语法为：

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

  HAVING 子句中的表达式可以同时引用分组表达式和未分组表达式（必然涉及聚合函数）：

SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';

  如果查询包含聚合函数调用，但没有 GROUP BY 子句，则仍会进行分组：结果为单组行（或可能没有行，如果单行随后被 HAVING 消除）。如果查询包含 HAVING 子句，即使没有任何聚合函数调用或 GROUP BY 子句，情况也是如此。

选择列表

  FROM 子句中的表表达式通过可能组合表、视图、消除行、分组等方式构建一个中间虚拟表。此表最终传递给选择列表进行处理，选择列表确定中间表的哪些列实际输出。

选择列表项

  最简单的选择列表是*，它会输出表表达式产生的所有列。否则，选择列表是值表达式的逗号分隔列表，如列名列表：

SELECT a, b, c FROM ...

  列名 a、b 和 c 要么是 FROM 子句中引用的表的列的实际名称，要么是表别名中赋予它们的别名。选择列表中可用的名称空间与 WHERE 子句中的相同，除非使用分组，在这种情况下，它与 HAVING 子句中的相同。

  如果多个表具有同名的列，则还必须给出表名，如下所示：

SELECT tbl1.a, tbl2.a, tbl1.b FROM ...

  在使用多张表时，还可以要求获取特定表的所有列：

SELECT tbl1.*, tbl2.a FROM ...

  如果在选择列表中使用了任意的值表达式，则从概念上讲，它会向返回的表中添加一个新的虚拟列。对于每行结果，值表达式会评估一次，并用行的值替换任何列引用。但是，选择列表中的表达式不必引用 FROM 子句的表表达式中的任何列。

列标签

  选择列表中的条目可以分配名称以供后续处理，如用于 ORDER BY 子句或由客户端应用程序显示。例如：

SELECT a AS value, b + c AS sum FROM ...

  如果没有使用 AS 指定输出列名称，系统会分配一个默认列名称。对于简单的列引用，这是所引用列的名称。对于函数调用，这是函数的名称。对于复杂表达式，系统将生成一个通用名称。

  通常情况下，AS 关键字是可选的，但在某些情况下，如果所需的列名称与 PostgreSQL 关键字匹配，则必须编写 AS 或对列名称使用双引号以避免歧义。如 FROM：

SELECT a from, b + c AS sum FROM ...

  应改为：

SELECT a AS from, b + c AS sum FROM ...
SELECT a "from", b + c AS sum FROM ...

  为了最大程度地防止将来添加关键字，建议始终编写 AS 或对输出列名称使用双引号。

  列标签不同于 FROM 子句中所做的命名：同一列可以被重命名两次，但选择列表中分配的名称将是传递的名称。

DISTINCT

  可以在 SELECT 之后指定 DISTICNT 来消除结果中的重复行：

SELECT DISTINCT select_list ...

  如果两行至少在一个列值上不同，则它们被认为是不同的。在此比较中，空值被视为相等。

  或者，可以指定一个任意表达式来确定哪些行被认为是不同的：

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

  expression 是一个任意值表达式，它针对所有行进行评估。一组所有表达式都相等的行的被视为重复项，并且仅保留该组的第一行作为输出。请注意，除非对查询按足够多的列进行排序以保证到达 DISTINCT 过滤器的行的唯一排序，否则一组的第一行是不可预测的（DISTINCT ON 处理在 ORDER BY 排序后发生）。

  DISTINCT ON 子句不是 SQL 标准的一部分，有时由于其结果的潜在不确定性而被认为是不良风格。明智地使用 GROUP BY 和 FROM 中的子查询，可以避免这种构造，但它通常是最方便的替代方案。

合并查询

  可以使用集合运算联合、交集和差集来合并两个查询的结果，语法为：

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

  其中 query1 和 query2 可以是之前提到的任何查询。

  UNION 实际上将 query2 的结果附加到 query1 的结果（尽管无法保证这是实际返回行的顺序）。此外，它会从其结果中消除重复行，就像 DISTINCT 一样，除非使用了 UNION ALL。

  INTERSECT 返回既在 query1 的结果中又在 query2 的结果中的所有行。除非使用了 INTERSECT ALL，否则会消除重复行。

  EXCEPT 返回所有在 query1 的结果中但不在 query2 的结果中的行（两个查询之间的差异）。除非使用了 EXCEPT ALL，否则会消除重复项。

  要计算两个查询的并集、交集或差集，则这两个查询必须并集兼容：它们返回相同数量的列，且相应的列具有兼容的数据类型。

  集合运算可以组合，建议使用括号来控制计算顺序。如果没有括号，UNION 和 EXCEPT 从左到右关联，但 INTERSECT 比这两个运算符绑定得更紧密。例如：

query1 UNION query2 INTERSECT query3

  意味着：

query1 UNION (query2 INTERSECT query3)

  还可以用括号包围单个 query：如果没有括号，可能会导致语法错误，或者该子句将被理解为应用于集合运算的输出，而不是其输入之一。

对行排序

  查询生成输出表（处理完选择列表后）后，可以对其进行排序。如果不选择排序，则将按未指定顺序返回行。只有在明确选择排序步骤时，才能保证特定的输出顺序。

  ORDER BY 子句指定排序顺序：

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

  排序表达式可以是查询选择列表中有效的任何表达式，例如：

SELECT a, b FROM table1 ORDER BY a + b, c;

  当指定多个表达式时，将使用后面的值对根据前面的值相等的行进行排序。每个表达式后面都可以跟一个可选的 ASC 或 DESC 关键字，以将排序方向设置为升序或降序。ASC 是默认顺序，升序顺序会将较小的值放在前面，较小是根据 < 运算符定义的。同样，降序顺序由 > 运算符确定。

  可以使用NULLS FIRST和NULLS LAST选项来确定在排序顺序中空值出现在非空值之前还是之后。默认情况下，空值排序就像大于任何非空值一样。也就是说，NULLS FIRST 是 DESC 顺序的默认值，否则为 NULLS LAST。

  注意：排序选项对每列排序都独立作用。例如，ORDER BY x, y DESC表示ORDER BY x ASC, y DESC，而不是ORDER BY x DESC, y DESC不同。

  sort_expression 还可以是输出列的列标签或编号，如下所示：

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

  两者都按第一输出列排序。注意：输出列名必须独立存在，也就是说，不能在表达式中使用它。一个错误示例：

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;

  此限制是为了减少歧义。如果 ORDER BY 项是一个可以匹配输出列名或表表达式中列的简单名称，则仍然存在歧义。在这种情况下，使用输出列。只有当使用 AS 将输出列重命名为与其他表列的名称匹配时，才会造成混淆。

  ORDER BY 可以应用于 UNION、INTERSECT 或 EXCEPT 组合的结果，但在这种情况下，只允许按输出列名或编号排序，而不允许按表达式排序。

LIMIT 和 OFFSET

  LIMIT 和 OFFSET 允许仅检索查询结果的部分行：

SELECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number ]

  如果给出了限制计数，则仅返回小于等于该限制的行数，LIMIT ALL等同于LIMIT NULL等同于没有 LIMIT。OFFSET 表示跳过开始的行数，OFFSET 0等同于OFFSET NULL等同于没有 OFFSET。

  如果 OFFSET 和 LIMIT 同时出现，则先跳过 OFFSET，然后再计算 LIMIT。

  使用 LIMIT，一般都会用 ORDER BY 约定唯一顺序，否则每次查询结果可能不一致。

  OFFSET 子句跳过的行仍然必须在服务器内部计算。因此，较大的 OFFSET 可能会效率低下。

VALUES 列表

  VALUES 提供了一种生成常量表的方法，该表可在查询中使用，而无需实际创建和填充磁盘上的表。语法为:

VALUES ( expression [, ...] ) [, ...]

  每个带括号的表达式列表在表中生成一行。这些列表必须具有相同数量的元素，并且每个列表中的相应条目必须具有兼容的数据类型。为结果的每一列分配的实际数据类型与 UNION 的规则相同。例如：

VALUES (1, 'one'), (2, 'two'), (3, 'three');

  它实际上等同于：

SELECT 1 AS num, 'one' AS letter
UNION ALL SELECT 2, 'two'
UNION ALL SELECT 3, 'three';

  或者使用表别名：

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);

  VALUES 可以出现在任何 SELECT 可以出现的位置。但其最常在 INSERT 命令中用作数据源，其次是作为子查询。