查询概述
查询是从一个表或多个表或视图检索数据的操作。查询是数据库的基本应用之一,GaussDB 100提供了丰富的查询方式,满足应用需求。
本章基于实际使用场景描述了查询和子查询的类型,以及如何使用它们。有关此语句的所有子句的完整语法和语义,请参阅SELECT。
前提条件
要从表或者视图中查看数据,这些表或视图必须在自己的schema下,或者需拥有这些表和视图的READ或SELECT权限。
语法格式
SELECT [hint_info] [SQL_CALC_FOUND_ROWS] [ DISTINCT ] { expression [ [ AS ] name ] } [ , ... ] [ FROM { table_reference [ AS OF {SCN(scn_number) | TIMESTAMP(date)} ] [ [AS] alias ] } [ , ... ] ] [ WHERE { condition | [ NOT ] EXISTS ( correlated subquery ) } ] [ [START WITH condition ] CONNECT BY [ NOCYCLE ] [ PRIOR ] condition ] [ HAVING condition [ , ... ] ] [ { UNION [ ALL ] | MINUS | EXCEPT } select ] [ ORDER [SIBLINGS] BY { column_name | number | expression } [ ASC | DESC ][ NULLS FIRST | NULLS LAST ] [ , ... ] ] [ LIMIT [ start, ] count | LIMIT count OFFSET start | OFFSET start[ LIMIT count ] ] [ FOR UPDATE [ OF col_list ] [ { WAIT sec | NOWAIT } ] ]
- hint_info子句:
{/*+ {access_method_hint | join_order_hint | join_method_hint | parallel_hint }[...] */}
- access_method_hint子句:
{ FULL(table_name [...]) | INDEX(table_name index_name[...]) | NO_INDEX(table_name index_name[...]) | INDEX_ASC(table_name index_name[...]) | INDEX_DESC(table_name index_name[...]) | INDEX_FFS(table_name index_name[...]) | NO_INDEX_FFS(table_name index_name[...]) }
- join_order_hint子句:
{ ORDERED | LEADING(table_name[...]) }
- join_method_hint子句:
{ USE_NL(table_name[...]) | USE_MERGE(table_name[...]) | USE_HASH(table_name[...]) }
- parallel_hint子句
{ parallel(degree) }
- table_reference 子句:
{ [ schema_name. ]table_name [partition(partition_name)][ [AS] alias ] | [ schema_name. ]view_name [ [AS] alias] | ( select query ) [ [AS] alias ] | join_table }
- join_table 子句:
默认为INNER JOIN。
选择INNER的时候,后面的ON条件可以省略。
table_reference [LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | INNER ] JOIN table_reference ON conditional_expr
- 外连接操作符(+)
外连接不仅可以使用LEFT/RIGHT关键字来描述,也可以使用外连接操作符(+)来表示。WHERE子句中含有(+)的条件属于外连接的关联条件,其中含有(+)一侧的表属于左连接的右节点,不含(+)一侧的表属于左连接的左节点。
- 外连接操作符的支持范围或约束条件如下:
- 存在join关键字时,不可以再出现(+);
- (+)前面仅可以修饰表字段;
- (+)仅支持出现在where子句中,且含(+)的条件不隶属OR字句;
- (+)的compare条件仅支持6种运算符:=、<>、>、<、>=、<=;
- (+)仅可以出现在compare条件的一侧;
- 若该compare条件的一侧出现(+),则该compare条件的每一侧最多允许存在一张表的字段;
- compare条件的一侧只要出现一个(+),外连接操作符就生效。如果出现多个(+),与出现一个(+)的效果相同;
- 当多个条件同时带(+)时,可能会生成不了关联关系而报错(例如,t1.f1=t2.f1(+) and t1.f1(+)=t2.f1);
- condition 子句:
{ predicate } [ { AND | OR } condition ] [ , ... n ]
- predicate 子句:
{ expression { = | <> | != | > | >= | < | <= } { ALL | ANY | SOME } expression | ( select ) | string_expression [ NOT ] LIKE string_expression | expression [ NOT ] BETWEEN expression AND expression | expression IS [ NOT ] NULL | ( select | expression [,...n] ) [ NOT ] IN ( select | expression [ , ... n ] ) | [ NOT ] EXISTS ( select ) }
参数说明
- hint_info
- hint_info是SQL语句中的特殊注释,用于将指令传递给数据库优化器。优化器使用这些提示为SQL语句选择执行计划,除非存在阻止优化器这样做的某些条件。
- 请谨慎使用hint_info。建议在收集了相关表的统计信息,并且通过EXPLAIN PLAN评估过不带hint_info的优化器执行计划后,使用hint_info。
- 在后续版本中的数据库条件变化及查询性能增强,都会对hint_info所起的作用产生影响。如果决定使用提示,需要注意任何使用提示所产生的短期利益不一定会产生长期改善。
- parallel hint目前只支持单表全表扫描,支持聚集函数并行、order by并行、group by并行、build hash table并行、单表扫描返回大量结果集并行。
- SQL_CALC_FOUND_ROWS
保留字。用于标识该SQL语句是否需要在执行时计算非LIMIT约束的结果集行数以便让后续的FOUND_ROWS()函数查询。SQL_CALC_FOUND_ROWS保留字只能指定在SELECT语句中的第一个SELECT关键字后。若该SELECT语句属于一个UNION / UNION ALL / MINUS语句,则必须保证只出现在第一个SELECT子集中。该保留字只有在用户使用FOUND_ROWS() 函数时才有意义。
详细请参见其他函数中关于FOUND_ROWS()的说明。
- DISTINCT
关键字,表示查询结果集是否需要去重。
取值范围:已存在的字段名,或字段表达式。
- AS OF {SCN(scn_number) | TIMESTAMP(date)}
查询指定SCN点或者指定时间点表的内容。
目前不支持查询临时表/系统视图。
- AS OF
关键字,闪回查询的标识。
- SCN(scn_number)
根据表的scn_numer,即数据库表记录scn号,闪回查询指定SCN点的结果集。
- TIMESTAMP(date)
根据date日期,闪回查找指定时间点的结果集。date日期必须是一个过去有效的时间戳(可使用TO_TIMESTAMP函数将字符串转换为时间类型)。
说明:查询时间应该小于建表时间,否则会报错。
- START WITH condition CONNECT BY [ NOCYCLE ] [ PRIOR ] condition
树形表数据的子查询语句。如果一个表包含树形结构的数据,可以使用本查询子句进行查询。
- START WITH
遍历的起始条件,指定查询开始的数据行。
- CONNECT BY
连接条件,用于指定父数据行和子数据行之间的关系,结合prior使用。
- NOCYCLE
即使数据中存在CONNECT BY LOOP,NOCYCLE参数也会指示数据库从查询中返回行。
- PRIOR
prior是一个一元操作符,和+、-符号具有相同优先级。prior放在等号的左边或者右边决定了检索是自下向上还是自上向下的。换句话说:prior跟父节点列parentid放在一起,就是往父结点方向遍历;prior跟子结点列subid放在一起,则往叶子结点方向遍历。
- CONNECT_BY_ISCYCLE伪列
该伪列表示当前元组是否会将层次数据形成环,该伪列只有在层次查询子句中表明NOCYCLE关键字时才有意义。如果当前行有一个子行,且子行又是当前行的祖先行,CONNECT_BY_ISCYCLE返回1,否则返回0。
- CONNECT_BY_ISLEAF伪列
如果当前行是CONNECT BY条件定义树的叶子节点,CONNECT_BY_ISLEAF伪列返回1,否则返回0。该信息也表明了一个给定的行是否可以进一步扩张,表现出更多的层次。
- LEVEL伪列
层级查询返回的每一行,根节点行LEVEL伪列返回1,之后其子孙节点的LEVEL依次递增。根节点行是倒置树的最高行。子节点行是任意非根节点行。父节点行是任意有子节点的行。叶子节点行是任何没有子节点行。
- expression
需要查询的字段或表达式。
- table_reference
查询表、视图、子查询。
[partition(partition_name)]
指定表的查询分区。partition_name为分区名称。
- condition
限制查询的记录必须满足的条件。
查询条件由表达式和操作符共同定义,支持多个条件通过AND和OR进行关联。GaussDB 100支持如下的条件定义方式:
- 使用比较操作符>, <, >=,<=, !=,<>, =指定的比较查询条件。
- 使用测试运算符LIKE/NOT LIKE、BETWEEN/NOT BETWEEN、NULL/NOT NULL、IN/NOT IN指定的范围查询条件。
- EXISTS ( select ) 指定的查询条件,要求select子查询所查询的列存在。
- NOT EXISTS ( select ) 指定的查询条件,要求select子查询所查询的列不存在。
- GROUP BY
指定结果集用于分组的字段。
- HAVING
指定限制分组结果的筛选条件。
- ORDER BY
指定结果集用于排序的字段。ORDER BY 后面的column_name可以是列名,也可以是别名。
ORDER SIBLINGS BY
指定兄弟节点之间用于排序的字段。必须指定了CONNECT BY子句时才可使用。
- ASC | DESC
指定ORDER BY排序的方向,是升序或降序排序。默认为ASC。
- NULLS FIRST | NULLS LAST
指定ORDER BY列中NULL值的排序位置,FIRST表示将NULL值排在最前面,LAST表示将NULL值排在最后面,若不指定该选项, ASC默认为NULLS LAST, DESC默认为NULLS FIRST。
- FOR UPDATE
表示查询的结果集用于UPDATE操作,需要加锁。
OF col_list
当涉及多个表操作时,可以使用OF子句指定需要更新的列。当表中的列出现在for update of后面,该表中满足条件的记录就会被锁定,如果表中的列没有出现在for update of列表中,该表则不会被锁定。
WAIT sec | NOWAIT
用于指定锁定等待模式。
- WAIT sec:表示等待sec秒数,超过时间没有获得锁就报错返回,可避免无限期等待。
- NOWAIT表示不等待,如果资源被锁定就立即返回。
- offset_expr,count_expr
限定返回查询结果集的偏移,以及返回数量。
- start,count
count指定要返回的最大行数,而start指定在返回行之前要跳过的行数。在两者都被指定时,在开始计算要返回的count行之前会跳过start行。
- UNION [ALL]
表示多个SELECT查询结果集的集合运算。
- FULL(table_name [...])
全表扫描。
- INDEX(table_name index_name[...])
索引扫描。
- NO_INDEX(table_name index_name[...])
非索引扫描。
- INDEX_ASC(table_name index_name[...])
按照正向顺序的索引扫描。
- INDEX_DESC(table_name index_name[...])
按照反向顺序的索引扫描。
- INDEX_FFS(table_name index_name[...])
快速索引扫描。
- NO_INDEX_FFS(table_name index_name[...])
非快速索引扫描。
- ORDERED
按照查询语句中的表的先后顺序来连接。
- LEADING(table_name[...])
按照指定顺序来连接。
- USE_NL(table_name[...])
按照NESTED LOOPS方式扫描表。
- USE_MERGE(table_name[...])
按照MERGE JOIN方式扫描表。
- USE_HASH(table_name[...])
按照HASH JOIN方式扫描表。
- join_table
用于关联查询的一组表集合。
- [INNER] JOIN 用于取两表的交集,此时可以省略后面的ON条件。
- LEFT [OUTER] JOIN 用于取左表的全集,右表不匹配的以null值代替。
- RIGHT [OUTER] JOIN 用于取右表的全集,左表不匹配的以null值代替。
- FULL [OUTER] JOIN 用于取两表的并集。作用和OUTER JOIN相同。
- {predicate } [ { AND | OR } condition]
指定查询结果集需要满足的条件集。
- AND
两边条件同时满足。
- OR
两边条件满足其一。
- predicate子句:
指定查询结果集需要满足的条件。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」关注作者【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。评论