云数据库 GAUSSDB-SELECT:语法格式

时间：2024-11-02 18:49:51

云数据库 GAUSSDB

语法格式

查询数据

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ into_option ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW {window_name AS ( window_definition )} [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT { [offset,] count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ into_option ]
[ {FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT n | SKIP LOCKED ]}[...] ]
[into_option];

condition和expression中可以使用targetlist中表达式的别名。

只能同一层引用。
只能引用targetlist中的别名。
只能是后面的表达式引用前面的表达式。
不能包含volatile函数。
不能包含Window function函数。
不支持在JOIN ON条件中引用别名。
targetlist中有多个要应用的别名则报错。

缓存SELECT语句计划的场景下，WHERE IN候选子集不易过大，建议条件个数不要超过100，防止引发动态内存过高问题：

WHERE IN 候选子集过大时，生成计划的内存占用会增大。
当拼接SQL构造的WHERE IN 子集不同，缓存计划的SQL模板无法复用。会生成大量不同的计划，且计划无法共享，占用大量内存。

其中子查询with_query为：

with_query_name [ ( column_name [, ...] ) ]
    AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )

其中into子句为：

into_option: {
        INTO var_name [, var_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为：

{[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
[ TIMECAPSULE {TIMESTAMP |  CS N} expression ]
|( 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 [, ...] )
|xmltable_clause
|from_item unpivot_clause
|from_item pivot_clause
|from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

其中group子句为：

| expression
| ( expression [, ...] )
| ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
| CUBE ( { expression | ( expression [, ...] ) } [, ...] )
| GROUPING SETS ( grouping_element [, ...] )

from_item中指定分区partition_clause为：
```
PARTITION { ( { partition_name | subpartition_name } [, ...] ) | FOR (  partition_value [, ...] ) } |
SUBPARTITION { ( subpartition_name ) | FOR (  subpartition_value [, ...] )}
```
- 指定分区只适合分区表。
- PARTITION指定多个分区名时，一级分区名和二级分区名可同时存在，且可以存在相同的分区名，最终分区范围取其并集。
其中设置排序方式nlssort_expression_clause为：
```
NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )
```
第二个参数可选generic_m_ci，仅支持纯英文不区分大小写排序。
简化版查询语法，功能相当于SELECT * FROM table_name。
```
TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};
```

上一篇：云数据库 GAUSSDB-备份恢复控制函数:备份控制函数

下一篇：云数据库 GAUSSDB-ALTER RESOURCE POOL:参数说明

新客秒杀 2核2G 3M L实例

68元/年

普惠上云领千元上云礼券

立即前往

企业专享 X实例 4核8G 5M

888元/年

热门域名 1元随心购

1元/年起

云数据库 GAUSSDB-SELECT:语法格式

意见反馈

0/200

提交取消

提交成功！非常感谢您的反馈，我们会继续努力做到更好反馈提交失败！请稍后重试！

云数据库 GAUSSDB-SELECT:语法格式

云数据库 GAUSSDB-SELECT:语法格式

语法格式

7*24

备案

专业服务

退订

建议反馈

售前咨询热线