查询

📄字数 6.4K

👁️阅读量 加载中...

一、主要语法结构

1.1 语法格式

1.2 参数说明

• with_clauses：可选公共表表达式，未指定时，不执行任何操作，请参阅WITH子句
• select_no_parens：没有外部括号的简单选择语句，请参阅无括号查询-select_no_parens
• select_with_parens：带有外部括号的选择语句，可以用于嵌套查询，请参阅有括号查询-select_with_parens

二、无括号查询-select_no_parens

2.1 语法格式

2.2 参数说明

• simple_select：简单查询，请参阅简单查询
• select_with_parens：简单查询子句，请参阅有括号查询
• sort_clause：可选排序子句，请参阅ORDER BY子句
• opt_for_update_clause：指定可选的更新锁定子句，请参阅更新锁定
• opt_select_limit：指定可选的结果限制子句，请参阅结果集限定子句-LIMIT

三、简单查询-simple_select

3.1 语法格式

3.2 参数说明

• opt_hint：指定可选的查询提示，用于调整、优化访问路径，请参阅查询提示
• opt_top：指定可选的返回结果集中前N条记录，等同于LIMIT top_num，不可与LIMIT同时使用，示例请参阅结果集限定子句-TOP
  • 未指定：返回所有记录
  • TOP top_num：返回结果集中前top_num条记录
• opt_distinct：指定可选的如何处理重复记录，示例请参阅结果集限定子句-DISTINCT
  • 未指定：不排除重复记录，等同于 ALL
  • DISTINCT：排除重复记录
  • ALL：不排除重复记录
• target_list：目标项列表，请参阅目标项列表
• opt_bulk：指定可选的是否对查询结果批量收集
  • 未指定：对查询结果逐行收集
  • BULK COLLECT：对查询结果批量收集
• opt_into_list：指定可选的收集变量列表，使用,逗号分隔
  • 未指定：不将查询结果收集到变量列表中
  • variable_list：将查询结果收集到variable_list变量列表中
• opt_from_clause：指定可选的查询数据来源子句，请参阅FROM子句
• opt_where_clause：指定可选的查询数据过滤条件，请参阅WHERE子句
  • 未指定：不设置过滤条件，查询所有数据
  • bool_expr：使用bool_expr布尔表达式作为过滤条件查询数据，请参阅逻辑表达式
• opt_connect_by：指定可选的层次查询子句，请参阅CONNECT BY子句
• opt_group_clause：指定可选的分组查询子句，请参阅GROUP BY子句-分组查询
• opt_having_clause：指定可选的分组过滤子句，请参阅GROUP BY子句-分组过滤
• select_clause：查询子句
  • simple_select：简单查询子句，请参阅简单查询
  • select_with_parens：简单查询子句，请参阅有括号查询
• ( UNION | INTERSECT | EXCEPT | MINUS )：
  • UNION：两个查询的结果集做并集运算
  • INTERSECT：两个查询的结果集做交集运算
  • EXCEPT：两个查询的结果集做差集运算，等同于 MINUS
  • MINUS：两个查询的结果集做差集运算
• opt_all：指定可选的如何处理重复记录
  • 未指定：排除重复记录，等同于 DISTINCT
  • ALL：不排除重复记录
  • DISTINCT：排除重复记录
• opt_corresponding：指定可选的两个查询的结果集列对应关系
  • 未指定：按结果集位置依次匹配
  • CORRESPONDING：按结果集列名自动匹配同名列
  • CORRESPONDING BY name_list：按name_list列名列表匹配，使用,逗号分隔

四、查询提示-opt_hint

查询提示是一种以固定的格式和位置出现在SQL文本中的特殊注释，其作用是令优化器生成指定的执行计划，用户可通这种方式改变优化器对执行计划的规划，从而使SQL按用户期望的执行计划执行。

4.1 语法格式

4.2 参数说明

• HINT_TEXT：提示文本
  • FULL ( table_name )：指示对table_name目标表使用全表扫描
  • INDEX ( table_name index_name )：指示对table_name目标表使用index_name索引扫描
  • NOINDEX ( table_name index_name )：指示对table_name目标表不使用index_name索引扫描
  • INDEX_JOIN ( table_name , table_name )：指示对table_name（1）目标表与table_name（2）目标表执行索引连接
  • USE_HASH ( table_name , table_name )：指示对table_name（1）目标表与table_name（2）目标表执行哈希连接

注意

• 若HINT_TEXT中指定的表在查询语句中指定了别名，则在HINT_TEXT中需使用此别名，否则查询提示将不会生效
• +符号前不可存在空格，否则查询提示将不会生效

4.3 示例

两种不同的查询执行计划：

• 使用查询提示：/*+INDEX(tab_hint idx_hint)*/
• 不使用查询提示

根据EXPLAIN语句的结果，对目标表tab_hint使用查询提示，SQL语句的执行计划为BtIdxScan，表示数据库使用B树索引，能够快速查找特定值。不使用查询提示，SQL语句的执行计划为SeqScan，表示数据库使用全表扫描。全表扫描会逐行读取整个表，直到找到符合条件的记录。这种方式在小表或没有合适索引时可能是合理的，但在大表上性能较差。

SQL> CREATE TABLE tab_hint(id INT,name VARCHAR(20));

Execute successful.
Use time:45 ms.

SQL> CREATE INDEX idx_hint ON tab_hint(id);

Execute successful.
Use time:104 ms.

-- 默认情况下的SQL执行计划
SQL> EXPLAIN SELECT * FROM tab_hint WHERE id=3;

+--------------------------------------------------------+
|                       plan_path                        |
+--------------------------------------------------------+
| 1   SeqScan[(1 1) cost=0,result_num=1](table=TAB_HINT) |
+--------------------------------------------------------+

(1 row)
Use time:4 ms.

-- 使用HINT查看SQL的执行计划
SQL> EXPLAIN SELECT /*+INDEX(tab_hint idx_hint)*/* FROM tab_hint WHERE id=3;

+----------------------------------------------------------------------------+
|                                 plan_path                                  |
+----------------------------------------------------------------------------+
| 1   BtIdxScan[(1 1) cost=300,result_num=1](table=TAB_HINT)(index=IDX_HINT) |
+----------------------------------------------------------------------------+

(1 row)
Use time:1 ms.

五、目标项列表-target_list

5.1 语法格式

5.2 参数说明

• bool_expr：布尔表达式，请参阅逻辑表达式
• target_el_alias：指定可选的别名
  • 未指定：不设置别名
  • alias_name：指定别名为alias_name，请参阅标识符
  • column_name：指定布尔表达式别名为column_name，请参阅标识符
• table_name：可选指定table_name表名，则仅输出此表的所有列，否则输出所有表的所有列

六、有括号查询-select_with_parens

6.1 语法格式

(select_no_parens::=, with_clauses::=)

6.2 参数说明

• parallel_opt：指定可选的查询并行数
  • 未指定：指定并行数为1，等同于 NOPARALLEL
  • NOPARALLEL：指定并行数为1
  • PARALLEL parallel_num：
    • 未指定：若启用auto_use_eje，则指定为auto_eje_parallel，若未启用则指定并行数为1，等同于 NOPARALLEL，请参阅系统配置参数auto_use_eje与auto_eje_parallel
    • parallel_num：指定并行数为 parallel_num，取值范围为[0,2147483647]

    注意

    • 当为小于2值时，若启用auto_use_eje，则指定为auto_eje_parallel，若未启用则指定并行数为1，等同于 NOPARALLEL，请参阅系统配置参数auto_use_eje与auto_eje_parallel
    • 当为大于1024值时，将强制使用1024值作为并行数
• opt_wait：指定查询超时时间
  • 未指定：若未获取到锁，永远等待，直到获取到资源锁，等同于 WAIT
  • NOWAIT：若未获取到锁，不等待，立即报告错误
  • WAIT：若未获取到锁，永远等待，直到获取到资源锁
  • WAIT wait_ms：若未获取到锁，最多等候 wait_ms 毫秒，取值范围为[0,2147483647]，超时报告错误
  • 嵌套使用：
    • 在虚谷数据库语法设计中，内层SELECT无法设置自己的wait等待时间，统一以外层设置的等待时间为准
    • 嵌套用例说明

      SQL> CREATE TABLE tab_lock(id INT,name VARCHAR(20));
      
      SQL> INSERT INTO tab_lock VALUES(1,'one')(2,'two')(3,'three')(4,'four');
      
      SQL> SET auto_commit OFF;
      
      SQL> LOCK TABLE tab_lock IN EXCLUSIVE MODE;
      
      -- 在另一个session执行
      SQL> SELECT * FROM (SELECT * FROM tab_lock WHERE name='two' nowait) wait 2000;
      -- 等待2s后报错
      Error: [E14012 L1 C30] 资源忙(TAB_LOCK加锁超时)

      提示

      内层SELECT语句设置了NOWAIT，但是外层SELECT语句设置了等待时间200毫秒，因此内层SELECT语句会等待200毫秒，若200毫秒内未获取到锁，则报告错误。

七、更新锁定-opt_for_update_clause

7.1 语法格式

7.2 参数说明

• for_update_clause：
  • 未指定：不对查询结果集设置更新锁定，等同于 FOR READ ONLY
  • FOR UPDATE ( OF update_list )：在事务内对查询结果集加行排他锁，并可选的指定update_list锁定列，使用,逗号分隔，若未指定则锁定所有列
  • FOR READ ONLY：不对查询结果集设置更新锁定

八、伪列

8.1 功能描述

伪列是数据库在执行查询时自动生成的系统字段，并不实际存在于表结构，也不会持久化于存储中。伪列可以像普通列一样查询和使用，但不能进行插入或更新操作。

8.2 ROWID

ROWID伪列由Base64编码得到，不进行实际的存储。不同数据行的ROWID是唯一的，可以作为数据行的唯一标识，并可以通过ROWID快速定位到表中的具体某一行。

8.2.1 示例

-- 查看ROWID伪列
SQL> CREATE TABLE tab_rowid(id INT,name VARCHAR(20));

Execute successful.
Use time:32 ms.

SQL> INSERT INTO tab_rowid VALUES(1,'one')(1,'two')(3,'three')(4,'four');

Total 4 records effected.
Use time:0 ms.

SQL> SELECT rowid, * FROM tab_rowid;

+--------------------------+----+-------+
|          ROWID           | ID | NAME  |
+--------------------------+----+-------+
| AAAAAOUAAAAAAAAAAAAAAA== | 1  | one   |
| AQAAAOUAAAAAAAAAAAAAAA== | 1  | two   |
| AgAAAOUAAAAAAAAAAAAAAA== | 3  | three |
| AwAAAOUAAAAAAAAAAAAAAA== | 4  | four  |
+--------------------------+----+-------+

(4 rows)
Use time:0 ms.

-- 使用ROWID执行更新操作
SQL> update tab_rowid set NAME = '123' where rowid = 'AAAAAOUAAAAAAAAAAAAAAA==';

Total 1 records effected.
Use time:8 ms.

SQL> SELECT rowid, * FROM tab_rowid;

+--------------------------+----+-------+
|          ROWID           | ID | NAME  |
+--------------------------+----+-------+
| AAAAAOUAAAAAAAAAAAAAAA== | 1  | 123   |
| AQAAAOUAAAAAAAAAAAAAAA== | 1  | two   |
| AgAAAOUAAAAAAAAAAAAAAA== | 3  | three |
| AwAAAOUAAAAAAAAAAAAAAA== | 4  | four  |
+--------------------------+----+-------+

(4 rows)
Use time:0 ms.

8.3 ROWNUM

ROWNUM伪列是对查询结果行的编号，其值为该行在查询结果中的位置，跟随查询结果生成，同样没有实际的物理存储。

提示

当使用ORDER BY对查询结果排序时，会先生成ROWNUM，再执行ORDER BY，因此查询结果中的ROWNUM顺序会跟随ORDER BY一同变动。如要使ROWNUM仍顺序排列，可以使用子查询，在子查询中执行ORDER BY。

8.3.1 示例

SQL> CREATE TABLE tab_rownum(id INT,name VARCHAR(20));

Execute successful.
Use time:33 ms.

SQL> INSERT INTO tab_rownum VALUES(1,'one')(1,'two')(3,'three')(4,'four')(2,'five');

Total 5 records effected.
Use time:4 ms.

-- 查看ROWNUM
SQL> SELECT rownum, * FROM tab_rownum;

+--------+----+-------+
| ROWNUM | ID | NAME  |
+--------+----+-------+
| 1      | 1  | one   |
| 2      | 1  | two   |
| 3      | 3  | three |
| 4      | 4  | four  |
| 5      | 2  | five  |
+--------+----+-------+

(5 rows)
Use time:2 ms.

-- 使用ROWNUM作为条件来筛选
SQL> SELECT rownum, * FROM tab_rownum WHERE rownum < 4;

+--------+----+-------+
| ROWNUM | ID | NAME  |
+--------+----+-------+
| 1      | 1  | one   |
| 2      | 1  | two   |
| 3      | 3  | three |
+--------+----+-------+

(3 rows)
Use time:3 ms.

-- 直接使用ORDER BY，ROWNUM跟随行一同变动
SQL> SELECT rownum, * FROM tab_rownum ORDER BY id;

+--------+----+-------+
| ROWNUM | ID | NAME  |
+--------+----+-------+
| 2      | 1  | two   |
| 1      | 1  | one   |
| 5      | 2  | five  |
| 3      | 3  | three |
| 4      | 4  | four  |
+--------+----+-------+

(5 rows)
Use time:0 ms.

-- ORDER BY位于子查询中，最终查询结果中ROWNUM为正常顺序
SQL> SELECT rownum,* FROM (SELECT * FROM tab_rownum ORDER BY id DESC);

+--------+----+-------+
| ROWNUM | ID | NAME  |
+--------+----+-------+
| 1      | 4  | four  |
| 2      | 3  | three |
| 3      | 2  | five  |
| 4      | 1  | two   |
| 5      | 1  | one   |
+--------+----+-------+

(5 rows)
Use time:0 ms.