Postgresql INSERT 语句
Postgresql UPDATE 语句 

Postgresql SELECT 语句

在 PostgreSQL 中，SELECT 语句用于从数据库中检索数据。本节详细介绍SELECT语句的基本用法。

语法格式

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ { * | expression [ [ AS ] output_name ] } [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]where from_item can be one of:
    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]    from_item join_type from_item { ON join_condition | USING ( join_column [, ...] ) [ AS join_using_alias ] }    from_item NATURAL join_type from_item
    from_item CROSS JOIN from_itemand grouping_element can be one of:
    ( )    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )and with_query is:
    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
        [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
        [ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]
TABLE [ ONLY ] table_name [ * ]


参数说明

WITH 子句

  • WITH Clause -- WITH 子句允许您指定一个或多个可在主查询中按名称引用的子查询。子查询在主查询期间有效地充当临时表或视图。每个子查询可以是 SELECT、TABLE、VALUES、INSERT、UPDATE 或 DELETE 语句。在 WITH 中编写数据修改语句（INSERT、UPDATE 或 DELETE）时，通常包含 RETURNING 子句。它是 RETURNING 的输出，而不是语句修改的基础 table，它构成了主查询读取的临时 table。如果省略 RETURNING，则仍会执行该语句，但它不会产生任何输出，因此主查询无法将其引用为 table。


FROM 子句

  • table_name -- 现有表或视图的名称（可选架构限定）。如果在表名之前指定了 ONLY，则仅扫描该表。如果未指定 ONLY，则扫描该表及其所有后代表（如果有）。（可选）可以在表名后指定 *，以明确指示包含后代表。

  • alias -- 包含别名的 FROM 项的替代名称。别名用于简洁或消除自联接的歧义（其中多次扫描同一表）。提供别名后，它会完全隐藏 table 或函数的实际名称;例如，给定 FROM foo AS f，SELECT 的其余部分必须将此 FROM 项引用为 f 而不是 foo。如果写入了别名，则还可以编写列别名列表，以便为表的一列或多列提供替代名称。

  • select -- 子 SELECT 可以出现在 FROM 子句中。这就像它的输出是在这个 SELECT 命令的持续时间内作为临时表创建的。请注意，子 SELECT 必须用括号括起来，并且可以像 table 一样提供别名。此处也可以使用 VALUES 命令。

  • with_query_name -- 通过写入 WITH 查询的名称来引用 WITH 查询，就像查询的名称是表名一样。（实际上，WITH 查询会隐藏任何同名的实际表，以便进行主查询。如有必要，您可以通过对表的名称进行架构限定来引用同名的实际表。别名的提供方式与表的提供方式相同。

  • function_name -- 函数调用可以出现在 FROM 子句中。（这对于返回结果集的函数特别有用，但可以使用任何函数。这就像函数的输出是作为临时表创建的，用于此单个 SELECT 命令的持续运行期间。如果函数的结果类型是 composite （包括具有多个 OUT 参数的函数的情况），则每个属性都将成为隐式表中的单独列。

  • join_type -- 支持[ INNER ] JOIN、LEFT [ OUTER ] JOIN、RIGHT [ OUTER ] JOIN、FULL [ OUTER ] JOIN 四种联接方式。对于 INNER 和 OUTER 联接类型，必须指定联接条件，即 ON join_condition、USING （join_column [， ...]） 或 NATURAL 之一。一个 JOIN 子句结合了两个 FROM 项，为方便起见，我们将其称为“表”，尽管实际上它们可以是任何类型的 FROM 项。如有必要，请使用括号来确定嵌套的顺序。在没有括号的情况下，JOIN 从左到右嵌套。在任何情况下，JOIN 都比分隔 FROM 列表项的逗号绑定得更紧密。所有 JOIN 选项都只是一种符号上的方便，因为它们没有你不能用普通的 FROM 和 WHERE 做的事情。LEFT OUTER JOIN 返回合格笛卡尔积中的所有行（即，通过其连接条件的所有组合行），以及左侧表中没有通过连接条件的右侧行的每一行的一个副本。通过为右侧 - 插入 null 值，此左侧行将扩展到连接表的整个宽度。

  • ON join_condition -- join_condition 是一个表达式，可生成 boolean 类型的值（类似于 WHERE 子句），该值指定将联接中的哪些行视为匹配。

  • USING ( join_column [, ...] ) [ AS join_using_alias ] -- USING （ a， b， ... ） 形式的子句是 ON left_table.a = right_table.a AND left_table.b = right_table.b ....此外，USING 意味着每对等效列中只有一个将包含在联接输出中，而不是同时包含两者。

  • NATURAL -- NATURAL 是 USING 列表的简写，它提到两个表中具有匹配名称的所有列。如果没有公共列名，则 NATURAL 等效于 ON TRUE。

  • CROSS JOIN -- CROSS JOIN 等价于 INNER JOIN ON （TRUE），即不通过限定删除任何行。它们生成一个简单的笛卡尔积，与在 FROM 的顶层列出两个表得到的结果相同，但受连接条件（如果有）的限制。

  • LATERAL -- LATERAL 关键字可以位于子 SELECT FROM 项之前。这允许子 SELECT 引用 FROM 列表中出现在它前面的 FROM 项列。（如果没有 LATERAL，则每个子 SELECT 都是独立计算的，因此不能交叉引用任何其他 FROM 项。


WHERE Clause 子句

可选的 WHERE 子句具有如下通用格式：

WHERE condition

其中 condition 是计算结果为 boolean 类型结果的任何表达式。任何不满足此条件的行都将从输出中消除。如果行在实际行值替换为任何变量引用时返回 true，则行满足条件。


GROUP BY 子句

可选的 GROUP BY 子句具有如下通用格式：

GROUP BY [ ALL | DISTINCT ] grouping_element [, ...]

GROUP BY 会将共享分组表达式相同值的所有选定行压缩为一行。grouping_element 中使用的表达式可以是输入列名，也可以是输出列（SELECT 列表项）的名称或序号，也可以是由输入列值组成的任意表达式。如果存在歧义，则 GROUP BY 名称将被解释为 input-column 名称，而不是输出列名称。


HAVING 子句

可选的 HAVING BY 子句具有如下通用格式：

HAVING condition

HAVING 会消除不满足条件的组行。HAVING 与 WHERE 不同：WHERE 在应用 GROUP BY 之前过滤单个行，而 HAVING 过滤由 GROUP BY 创建的组行。condition 中引用的每个列都必须明确引用一个分组列，除非该引用出现在聚合函数中，或者未分组的列在功能上依赖于分组列。


WINDOW 子句

可选的 WINDOW 子句具有如下通用格式：

WINDOW window_name AS ( window_definition ) [, ...]

其中 window_name 是可以从 OVER 子句或后续窗口定义中引用的名称，window_definition 

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]


DISTINCT 子句

如果指定了 SELECT DISTINCT ，则从结果集中删除所有重复的行（每组重复项中保留一行）。SELECT ALL 指定相反的情况：保留所有行;这是默认设置。SELECT DISTINCT ON （ expression [， ...] ） 仅保留给定表达式计算结果相等的每组行的第一行。


UNION 子句

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement 是任何没有 ORDER BY、LIMIT、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE 或 FOR KEY SHARE 子句的 SELECT 语句。（如果 ORDER BY 和 LIMIT 括在括号中，则可以将其附加到子表达式。如果没有括号，这些子句将被视为应用于 UNION 的结果，而不是其右侧的 input 表达式。UNION 运算符计算所涉及的 SELECT 语句返回的行的集合并集。如果行至少出现在一个结果集中，则该行位于两个结果集的集合并集中。表示 UNION 的直接操作数的两个 SELECT 语句必须生成相同数量的列，并且相应的列必须具有兼容的数据类型。


INTERSECT 子句

INTERSECT 运算符计算所涉及的 SELECT 语句返回的行的集合交集。如果行出现在两个结果集中，则该行位于两个结果集的交集。

除非指定了 ALL 选项，否则 INTERSECT 的结果不包含任何重复的行。使用 ALL 时，左表中有 m 个重复项，右表中有 n 个重复项的行将在结果集中出现 min（m，n） 次。可以编写 DISTINCT 以显式指定消除重复行的默认行为。


EXCEPT 子句

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

EXCEPT 运算符计算位于左侧 SELECT 语句的结果中但不在右侧 SELECT 语句的结果中的行集。


ORDER BY 子句

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

ORDER BY 子句使结果行根据指定的表达式进行排序。如果根据最左侧的表达式，两行相等，则根据下一个表达式对它们进行比较，依此类推。如果根据所有指定的表达式，它们相等，则按依赖于实现的顺序返回它们。


LIMIT 子句

LIMIT { count | ALL }
OFFSET start

参数 count 指定要返回的最大行数，而 start 指定在开始返回行之前要跳过的行数。如果同时指定了这两者，则在开始计算要返回的 count 行之前，将跳过 start rows。如果 count 表达式的计算结果为 NULL，则将其视为 LIMIT ALL，即无限制。如果 start 的计算结果为 NULL，则将其视为与 OFFSET 0 相同的处理方式。

SQL：2008 引入了不同的语法来实现相同的结果，PostgreSQL 也支持该语法。是的：

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

在此语法中，标准要求 start 或 count 值为文本常量、参数或变量名称;作为 PostgreSQL 扩展，允许使用其他表达式，但通常需要用括号括起来以避免歧义。如果在 FETCH 子句中省略 count，则默认为 1。WITH TIES 选项用于根据 ORDER BY 子句返回在结果集中并列最后一位的任何其他行;在这种情况下，ORDER BY 是必需的，并且不允许使用 SKIP LOCKED。ROW 和 ROWS 以及 FIRST 和 NEXT 是不影响这些子句效果的干扰词。根据标准，如果两者都存在，则 OFFSET 子句必须位于 FETCH 子句之前;但 PostgreSQL 更宽松，允许任何 Sequences。

锁定子句

FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE 和 FOR KEY SHARE 是锁定子句;它们会影响 SELECT 在从 table 中获取行时锁定行的方式。

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

其中 lock_strength 可以是

  • UPDATE

  • NO KEY UPDATE

  • SHARE

  • KEY SHARE


使用示例

  • 查询 students 表中年龄大于 24的用户

select * from students where age > 24;

  • 查询各个班级的学生人数

select class_id, count(name) from students group by class_id;

  • 按学号升序排序

select * from students order by sno asc;

查询 students 表中姓名字段含有"国"字的记录

select * from students where name like '%国%';