SELECT

功能描述

SELECT用于从表或视图中取出数据。

SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。

注意事项

• 表的所有者、拥有表SELECT权限的用户或拥有SELECT ANY TABLE权限的用户，有权读取表或视图中数据，当三权分立开关关闭时，系统管理员默认拥有此权限。
• 必须对每个在SELECT命令中使用的字段有SELECT权限。

语法格式

查询数据

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */]
    [ALL | DISTINCT | DISTINCTROW]
    [STRAIGHT_JOIN]
    [SQL_NO_CACHE]
    [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM from_item [,...]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position} [WITH ROLLUP]]
    [HAVING condition]
    [WINDOW {window_name AS (window_definition)} [, ...]]
    [ORDER BY {col_name | expression | position}
      [ASC | DESC][ NULLS { FIRST | LAST } ], ...]
    [[LIMIT {[offset,] row_count | row_count OFFSET offset}] | FETCH {FIRST | NEXT} [(expr)] {ROW| ROWS} ONLY]
    [into_option]
    [ FOR READ ONLY | 
        [ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT N | SKIP LOCKED ]} [...] ] |
        LOCK IN SHARE MODE]

select_expr：
    expression [ [AS] output_name ]

where_condition:
expr

into_option:
INTO {
  [[[LOCAL | GLOBAL] TEMPORARY] [TABLE] table_name] |
  OUTFILE file_name [CHARACTER SET encoding_name] [FIELDS fields_items] [LINES lines_items] |
  DUMPFILE file_name
}

SELECT.. UNION/EXCEPT语法：

SELECT ... 
     UNION [ALL | DISTINCT] SELECT ... 
     [UNION [ALL | DISTINCT] SELECT ...]
SELECT ...
     EXCEPT[ALL | DISTINCT] SELECT ... 
     [EXCEPT[ALL | DISTINCT] SELECT ...]

WHERE子句、HAVING子句、GROUP BY子句中使用SELECT子句中的别名，存在如下限制：

• 初始化建表语句。

  m_db=# DROP TABLE IF EXISTS t0,t1;
  m_db=# CREATE TABLE t0 (c0 INT, c1 INT);
  m_db=# CREATE TABLE t1 (c0 INT, c1 INT);

• 只能同一层引用。

  m_db=# SELECT r FROM t0 WHERE (select c0 as r from t0) = 1; -- error

• 只能引用targetlist中的别名。

  m_db=# SELECT * FROM t0 WHERE (SELECT c0 AS r FROM t0) = 1 AND r = 2; -- error

• 只能是后面的表达式引用前面的表达式。

  m_db=# SELECT r AS d, c0 AS r FROM t0; -- error

• 不能包含volatile函数。

  m_db=# SELECT uuid() AS r FROM t0 WHERE r = 1; -- error

• 不能包含Window function函数。

  m_db=# SELECT pg_catalog.rank() over(PARTITION BY c0 ORDER BY c1) AS r FROM t0 WHERE r = 1; -- error

• 不支持在join on条件中引用别名。

  m_db=# SELECT uuid() AS r FROM t0 JOIN t1 ON r = 1; -- error

• targetlist中有多个要引用的别名则报错。

  m_db=# SELECT c0 AS r AS u AS v FROM t0; -- error

• 其中子查询with_query为：

  with_query_name [ ( column_name [, ...] ) ]
      AS ( {select} )

• 其中INTO子句为：

  into_option: {
          INTO [[[LOCAL | GLOBAL] TEMPORARY] [TABLE]] table_name
  	| INTO OUTFILE 'file_name'
  		[CHARACTER SET charset_name]
  		export_options
  	| INTO DUMPFILE 'file_name'
  }
  export_options:
      [FIELDS
   [TERMINATED BY 'string']
   [[OPTIONALLY] ENCLOSED BY 'char']
   [ESCAPED BY 'char' ]
      ]
      [LINES
   [STARTING BY 'string']
   [TERMINATED BY 'string']
      ]
  }

• 其中指定查询源from_item为：

  { table_name  [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
  |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
  |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
  |joined_table

  

  table_name包含dual虚拟表。M-Compatibility允许在没有引用表的情况下，将dual指定为虚拟表名。

• 其中joined_table为：

  joined_table: {
     table_reference [INNER | CROSS] JOIN table_factor [join_specification]
    | table_reference STRAIGHT_JOIN table_factor [join_specification]
    | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
    | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
  }
  join_specification: {
     ON join_condition
    | USING (join_column_list)
  }
  join_column_list:
      column_name [, column_name] ...

• 其中group子句为：

  | expression
  | ( expression [, ...] )

• from_item中指定分区partition_clause为：

  PARTITION { ( { partition_name | subpartition_name } [, ...] ) | FOR (  partition_value [, ...] ) } |
  SUBPARTITION { ( subpartition_name ) | FOR (  subpartition_value [, ...] )}

  

  • 指定分区只适合分区表。
  • PARTITION指定多个分区名时，一级分区名和二级分区名可同时存在，且可以存在相同的分区名，最终分区范围取其并集。

参数说明

• plan_hint

  以/*+ */的形式在SELECT关键字后，用于对SELECT对应的语句块生成的计划进行hint调优。每条语句中只有第一个/*+ plan_hint */注释块会作为hint生效，里面可以写多条hint。

• WITH [ RECURSIVE ] with_query [, ...]

  用于声明一个或多个可以在主查询中通过名称引用的子查询，相当于临时表。这种子查询语句结构称为CTE（Common Table Expression）结构，应用这种结构时，执行计划中将存在CTE SCAN的内容。

  如果声明了RECURSIVE，那么允许SELECT子查询通过名称引用其本身。

  其中with_query的详细格式为：

  with_query_name [ ( column_name [, ...] ) ] AS ( {select} )

  • with_query_name指定子查询生成的结果集名称，在查询中可使用该名称访问子查询的结果集。
  • column_name指定子查询结果集中显示的列名。
  • 每个子查询支持SELECT语句。
  • RECURSIVE只能出现在WITH后面，多个CTE的情况下，只需要在第一个CTE处声明RECURSIVE。
  • 使用RECURSIVE时，CTE子查询中UNION ALL和EXCEPT ALL或UNION [DISTINCT]和EXCEPT [DISTINCT]两侧的子查询结果，其数据类型必须使用cast函数转换成相同的数据类型，且两侧子查询结果的精度和字符序也要相同。如：WITH RECURSIVE cte (n) AS ( SELECT cast(id as signed int) from table_1 UNION ALL SELECT cast((n + 1) as signed int) FROM cte WHERE n < 5 ) SELECT * FROM cte。由操作符产生的类型转换具体请参见逻辑操作符规格约束、位运算操作符规格约束和算术操作符规格约束。
• ALL

  声明返回所有符合条件的行，是默认行为，可以省略该关键字。

• DISTINCT | DISTINCTROW

  从SELECT的结果集中删除所有重复的行，使结果集中的每行都是唯一的。

  

  DISTINCT|DISTINCTROW表达式是使用与ORDER BY相同的规则进行解释的。除非使用了ORDER BY来保证需要的行首先出现，否则，"第一行" 是不可预测的。

• STRAIGHT_JOIN

  对于内联接，可改变优化器对于联表查询的执行顺序，使左边的表始终在右右边表之前读取。

• SQL_CALC_FOUND_ROWS

  使用found_rows函数计算结果集中有多少行时，忽略任何LIMIT子句。

• SELECT列表

  指定查询表中列名，可以是部分列或者是全部（使用通配符*表示）。

  通过使用子句AS output_name可以为输出字段取个别名，这个别名通常用于输出字段的显示。支持关键字name、value和type作为列别名。

  列名可以用下面几种形式表达：

  • 手动输入列名，多个列之间用英文逗号“,”分隔。
  • 可以是FROM子句里面计算出来的字段。
• INTO子句

  将SELECT出的结果输出到指定用户自定义变量或文件。

  • OUTFILE
    • CHARACTER SET 指定编码格式。
    • FIELDS 指定每个字段的属性：

      TERMINATED 指定间隔符。

      [OPTIONALLY] ENCLOSED 指定引号符，指定OPTIONALLY时只对字符串数据类型起作用。

      ESCAPED 指定转义符。

    • LINES 指定行属性：

      STARTING 指定行开头。

      TERMINATED 指定行结尾。

  • DUMPFILE

    导出无间隔符，无换行符的单行数据到文件。

  • file_name

    指定文件的绝对路径。

    导出到文件：
    m_db=# SELECT * FROM t;
     a | b
    ---+---
     1 | a
    (1 row)
    --导出数据到outfile文件。
    m_db=# SELECT * FROM t INTO OUTFILE '/home/gaussdb/t.txt' FIELDS TERMINATED BY '~' ENCLOSED BY 't' ESCAPED BY '^' LINES STARTING BY '$' TERMINATED BY '&\n';
    文件内容：$t1t~tat&，其中LINES STARTING BY($),FIELDS TERMINATED BY(~),ENCLOSED BY(t),LINES TERMINATED BY(&\n)。
    --导出数据到dumpfile文件。
    m_db=# SELECT * FROM t INTO DUMPFILE '/home/gaussdb/t.txt';
    文件内容：1a

• FROM子句

  为SELECT声明一个或多个源表。

  FROM子句涉及的元素如下所示。

  • table_name

    表名或视图名，名称前可加上模式名，如：schema_name.table_name。

  • alias

    给表或复杂的表引用起一个临时的表别名，以便被其余的查询引用。

    别名用于缩写或者在自连接中消除歧义。如果提供了别名，它就会完全隐藏表的实际名称。

  • column_alias

    列别名

  • PARTITION

    查询分区表的某个分区的数据。

  • partition_name

    分区名。

  • partition_value

    指定的分区键值。在创建分区表时，如果指定了多个分区键，可以通过PARTITION FOR子句指定的这一组分区键的值，唯一确定一个分区。

  • SUBPARTITION

    查询分区表的某个二级分区的数据。

  • subpartition_name

    二级分区名。

  • subpartition_value

    指定的一级分区和二级分区键值。可以通过SUBPARTITION FOR子句指定的两个分区键的值，唯一确定一个二级分区。

  • subquery

    FROM子句中可以出现子查询，创建一个临时表保存子查询的输出。

  • with_query_name

    WITH子句同样可以作为FROM子句的源，可以通过WITH查询的名称对其进行引用。

  • function_name

    函数名称。函数调用也可以出现在FROM子句中。

  • join有以下类型：
    • [ INNER ] JOIN

      一个JOIN子句组合两个FROM项。可使用圆括弧以决定嵌套的顺序。如果没有圆括弧，JOIN从左向右嵌套。

      在任何情况下，JOIN都比逗号分隔的FROM项绑定得更紧。

    • LEFT [ OUTER ] JOIN

      返回笛卡尔积中所有符合连接条件的行，再加上左表中通过连接条件没有匹配到右表行的那些行。这样，左边的行将扩展为生成表的全长，方法是在那些右表对应的字段位置填上NULL。请注意，只在计算匹配的时候，才使用JOIN子句的条件，外层的条件是在计算完毕之后施加的。

    • RIGHT [ OUTER ] JOIN

      返回所有内连接的结果行，加上每个不匹配的右边行（左边用NULL扩展）。

      这只是一个符号上的方便，因为总是可以把它转换成一个LEFT OUTER JOIN，只要把左边和右边的输入互换位置即可。

    • CROSS JOIN

      CROSS JOIN等效于INNER JOIN ON(TRUE)，即没有被条件删除的行。这种连接类型与简单的FROM和WHERE的效果相同，相对来说在符号使用上更加便捷。

      

      必须为INNER和OUTER连接类型声明一个连接条件，即NATURAL ON、join_condition、USING (join_column [， ...]) 之一。

    • STRAIGHT_JOIN

      与INNER JOIN功能相似。STRAIGHT_JOIN场景下，左边的表始终在右右边的表之前读取，可改变优化器对于联表查询的执行顺序。

    其中CROSS JOIN和INNER JOIN生成一个简单的笛卡尔积，和在FROM的顶层列出两个项的结果相同。

    允许连续使用多次JOIN。

  • ON join_condition

    连接条件，用于限定连接中的哪些行是匹配的。如：ON left_table.a = right_table.a。不建议使用int等数值类型作为join_condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。

  • USING(join_column[，...])

    ON left_table.a = right_table.a AND left_table.b = right_table.b ... 的简写。要求对应的列必须同名。

  • NATURAL

    NATURAL是具有相同名称的两个表的所有列的USING列表的简写。

  • from item

    用于连接的查询源对象的名称。

• WHERE子句

  WHERE子句构成一个行选择表达式，用来缩小SELECT查询的范围。condition是返回值为布尔型的任意表达式，任何不满足该条件的行都不会被检索。不建议使用int等数值类型作为condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。

  

  对于WHERE子句的LIKE操作符，当LIKE中要查询特殊字符“%”、“_”、“\”的时候需要使用反斜杠“\”来进行转义。

• GROUP BY子句

  将查询结果按某一列或多列的值分组，值相等的为一组。

  WITH ROLLUP :

  若想对分组的查询结果进行统计，GROUP BY语句结尾添加WITH ROLLUP语句。WITH ROLLUP也可以对多列分组查询结果进行统计

  

  若sql_mode中包含ONLY_FULL_GROUP_BY选项。

  1. SELECT列表中非聚合函数列与GROUP BY字段一致时，不会报错。
  2. SELECT列表中非聚合函数列与GROUP BY字段不一致时，分两种情况：

     1）GROUP BY列表包含主键或唯一非空键时，SQL语句不会报错

     2）SELECT列表中非聚合函数列，都出现GROUP BY列表或WHERE列表中。且WHERE子句中的列需要等于某一常量的形式，不会报错

  3. 对于同时含有join子句和GROUP BY子句时，

     1）LEFT JOIN

     语法的ON子句中，若条件为两列等值判断，如t1.col1=t2.col2 ...，则GROUP BY子句中可无右连接表列，不报错。如t1 LEFT JOIN t2，GROUP BY 子句只需包含t1.col1

     2）RIGHT JOIN

     语法的ON子句中，若条件为两列等值判断，如t1.col1=t2.col2 ...，则GROUP BY子句中可无左连接表列，不报错。如t1 RIGHT JOIN t2，GROUP BY 子句只需包含t2.col2

     3）INNER JOIN/CROSS JOIN/STRAIGHT_JOIN

     语法的ON子句中，若条件为两列等值判断，如t1.col1=t2.col2 ...，则GROUP BY子句中包含任一列即可，不报错。

  若sql_mode中不包含ONLY_FULL_GROUP_BY选项。无上述约束，以上报错场景均不报错。

  当查询表为空表时，带有WITH ROLLUP语句会查询出一条空行数据。

• HAVING子句

  与GROUP BY子句配合用来选择特殊的组。HAVING子句将组的一些属性与一个常数值比较，只有满足HAVING子句中的逻辑表达式的组才会被提取出来。HAVING必须且只能引用GROUP BY子句中的列或聚合函数中使用的列。

• WINDOW子句

  一般形式为WINDOW window_name AS ( window_definition ) [, ...]，window_name是可以被随后的窗口定义所引用的名称，window_definition可以是以下的形式：

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

  frame_clause为窗函数定义一个窗口框架window frame，窗函数（并非所有）依赖于框架，window frame是当前查询行的一组相关行。frame_clause可以是以下的形式：

  [ RANGE | ROWS ] frame_start
  [ RANGE | ROWS ] BETWEEN frame_start AND frame_end

  frame_start和frame_end形式为：

  UNBOUNDED PRECEDING
  VALUE PRECEDING
  CURRENT ROW
  VALUE FOLLOWING
  UNBOUNDED FOLLOWING

  

  针对使用到VALUE场景存在如下约束：

  • VALUE的值一定为非负数。
  • VALUE PRECEDING/FOLLOWING仅支持与ROWS连用，不支持与RANGE连用。
• UNION子句

  UNION计算多个SELECT语句返回行集合的并集。

  UNION子句有如下约束条件：

  • 除非声明了ALL子句，否则缺省的UNION结果不包含重复的行。
  • 同一个SELECT语句中的多个UNION操作符是从左向右计算的，除非用圆括弧进行了标识。
  • FOR UPDATE，FOR SHARE不能在UNION的结果或输入中声明。
  • 通过GUC参数enable_union_all_order控制UNION ALL子查询是否保序：
    • 开启方式：set enable_union_all_order to on。
    • 关闭方式：set enable_union_all_order to off。
    • 查询方式：show enable_union_all_order。

    在开启enable_union_all_order参数时，在UNION声明了ALL并且主查询未排序的情况下，支持子查询保序。其他情况均不支持子查询保序。

  一般表达式：

  select_statement UNION [ALL] select_statement

  • select_statement可以是任何没有ORDER BY、LIMIT、FOR UPDATE，FOR SHARE子句的SELECT语句。
  • 如果用圆括弧包围，ORDER BY和LIMIT可以附着在子表达式里。
• EXCEPT子句

  从第一个查询结果中去除两个查询结果的交集。

  EXCEPT子句有以下约束条件:

  • 仅在声明了ALL子句情况下，查询的结果不进行去重操作；缺省的EXCEPT会对查询结果进行去重操作。
  • 同一个SELECT语句中，如果包含多个EXCEPT操作符，运算逻辑从左向右计算。仅当在语句中使用圆括号进行标识后，运算逻辑会根据优先级进行查询。
  • EXCEPT的查询结果或输入中，不能指定FOR UPDATE或FOR SHARE操作。

  一般表达式：

  select_statement EXCEPT [ALL | DISTINCT] select_statement

  • select_statement可以是任何没有ORDER BY、LIMIT、FOR UPDATE或FOR SHARE子句的SELECT语句。
  • 当select_statement中包含ORDER BY或LIMIT子句时，需在select_statement前后添加圆括号。
• ORDER BY子句

  对SELECT语句检索得到的数据进行升序或降序排序。对于ORDER BY表达式中包含多列的情况：

  • 首先根据最左边的列进行排序，如果这一列的值相同，则根据下一个表达式进行比较直至比较完成。
  • 如果对于所有声明的表达式都相同，则按随机顺序返回。
  • 在与DISTINCT关键字一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。
  • 在与GROUP BY子句一起使用的情况下，ORDER BY中排序的列必须包括在GROUP BY子句所分组的列中。

  对于不与GROUP BY子句一起使用，且在SELECT结果集中包含聚合函数时，ORDER BY只对 返回集合的函数 所在的列生效：

  m_db=# CREATE TABLE t1(c1 int, c2 int);
  m_db=# EXPLAIN SELECT sum(c1) c FROM t1 ORDER BY c, c2; --忽略orderby。
  m_db=# EXPLAIN SELECT sum(c1) c, generate_series(1,5) s FROM t1 ORDER BY c, c2; --忽略orderby。
  m_db=# EXPLAIN SELECT sum(c1) c, generate_series(1,5) s FROM t1 ORDER BY c, s; --结果对s列进行排序。

  • NULLS FIRST

    指定空值在排序中排在非空值之前，当指定DESC排序时，本选项为默认的。

  • NULLS LAST

    指定空值在排序中排在非空值之后，未指定DESC排序时，本选项为默认的。

  

  如果要支持中文拼音排序，需要在初始化M-Compatibility数据库时指定gb18030、GBK字符集，详见：库级字符集和字符序；并在比较时指定gb18030_chinese_ci, gbk_chinese_ci字符序。

• LIMIT子句

  LIMIT子句由两个独立的子句组成：

  LIMIT { count | ALL } 限制返回行数，count为指定行数，LIMIT ALL的效果和省略LIMIT子句一样。

  LIMIT start, count声明返回的最大行数，而start声明开始返回行之前忽略的行数。如果两个都指定了，会在开始计算count个返回行之前先跳过start行。

  LIMIT子句不支持ROWNUM作为count或者offset。

• FETCH {FIRST | NEXT} [(expr)] {ROW| ROWS} ONLY

  如果不指定count，默认值为1，FETCH子句限定返回查询结果从第一行开始的总行数。

  

  该属性在数据库M-Compatibility模式兼容控制开关s1及以上版本时不支持（如m_format_dev_version = 's1'）。

• OFFSET子句

  SQL：2008开始提出一种不同的语法：

  OFFSET start { ROW | ROWS }

  start声明开始返回行之前忽略的行数。

• 锁定子句

  FOR UPDATE子句将对SELECT检索出来的行进行加锁。这样避免它们在当前事务结束前被其他事务修改或者删除，即其他企图UPDATE、 DELETE、 SELECT FOR UPDATE、SELECT FOR SHARE这些行的事务将被阻塞，直到当前事务结束。任何在一行上的DELETE命令也会获得FOR UPDATE锁模式，在主键列上修改值的UPDATE也会获得该锁模式。反过来，SELECT FOR UPDATE将等待已经在相同行上运行以上这些命令的并发事务，并且接着锁定并且返回被更新的行（或者没有行，因为行可能已被删除）。

  FOR SHARE的行为类似，只是它在每个检索出来的行上要求一个共享锁，而不是一个排他锁。一个共享锁阻塞其它事务执行UPDATE、DELETE、SELECT FOR UPDATE，不阻塞SELECT FOR SHARE。

  为了避免操作等待其他事务提交，可使用NOWAIT选项，如果被选择的行不能立即被锁住，将会立即汇报一个错误，而不是等待；WAIT n选项，如果被选择的行不能立即被锁住，等待n秒（其中，n为int类型，取值范围：0 <= n <= 2147483），n秒内获取锁则正常执行，否则报错；SKIP LOCKED选项，对表加锁时跳过已经加锁的行，SKIP LOCKED只能跳过行锁，对不同事务中锁与锁不互相阻塞的场景（如SELECT FOR SHARE - SELECT FOR SHARE, 指定SKIP LOCKED时）不会跳过锁。

  如果在锁定子句中明确指定了表名称，则只有这些指定的表被锁定，其他在SELECT中使用的表将不会被锁定。否则，将锁定该命令中所有使用的表。

  如果锁定子句应用于一个视图或者子查询，它同样将锁定所有该视图或子查询中使用到的表。

  多个锁定子句可以用于为不同的表指定不同的锁定模式。

  如果一个表中同时出现（或隐含同时出现）在多个子句中，则按照最强的锁处理。类似的，如果影响一个表的任意子句中出现了NOWAIT，该表将按照NOWAIT处理。

  

  • 对ustore表的查询只支持FOR SHARE/FOR UPDATE/FOR READ ONLY/LOCK IN SHARE MODE。
  • 对于子查询是stream计划的FOR UPDATE/SHARE语句，不支持加锁的同一行被并发更新。
  • SELECT FOR UPDATE、SELECT FOR SHARE不支持与UNION/EXCEPT/DISTINCT/GROUP BY/HAVING一起使用。
• PARTITION子句

  查询某个分区表中相应分区的数据。