SELECT 命令

<subquery> [ <for_update_clause> ];

subquery

子查询常见用法：

  1. 返回一行一列的子查询：select ... where expr = ( subquery )
  2. 返回多行一列的子查询：select ... where expr in ( subquery )
  3. 返回多行多列的子查询：insert ... ( subquery ); create table ... AS ( subquery )
[ <subquery_factoring_clause> ]

SELECT [ <hint> ] [ ALL | DISTINCT | UNIQUE ] <select_list>

FROM <table_reference> [,...]

[ <where_clause> ]

[ <hierarchical_query_clause> ]

[ <group_by_clause> ]

[ HAVING condition ]

[ { UNION [ALL] | INTERSECT | MINUS } ( <subquery> ) ]

[ <order_by_clause> ]
关键词 描述
ALL 返回所有查询的记录集，默认
DISTINCT | UNIQUE 返回的记录集消除重复行

（不能用在含 LOB 字段的查询）
UNION 返回两个子查询记录的合并
INTERSECT 返回两个子查询记录的相同部分
MINUS 返回前个子查询中，不同于后个子查询的记录

subquery_factoring_clause

将一个名称赋予子查询块，在其后用该名称代替子查询块。

不允许嵌套命名子查询，即在被命名的子查询中又命名子块。

在有 SET 操作的查询中，SET 操作的对象不能为命名子查询。

WITH

   query_name AS ( <subquery> ) [, ...]

示例

WITH 
  dept_costs AS (
    SELECT department_name, SUM(salary) dept_total
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    GROUP BY department_name),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM dept_costs
  )
  SELECT * FROM dept_costs
    WHERE dept_total > (SELECT avg FROM avg_cost)
    ORDER BY department_name;

hint

指定返回的记录集不重复（重复部分删除）。

{ DISTINCT | UNIQUE }

select_list

{ * |

    {   query_name.*

      | [schema.]{ table | view | materialized view }.*

      | expr [[AS] c_alias]

    } , ...

}

关键词 描述
c_alias 列的别名，（可用于 ORDER BY 子句）
如果指定了 GROUP BY 子句，select_list 限制如下：
  1. 常量
  2. USER，UID，SYSDATE
  3. 聚集函数（SUM，MAX 等）
  4. 同时在 ORDER BY 子句中出现的表达式
  5. 对每一行得出的值相同的表达式
当不同表有相同的列表时，必须限定列来自哪个表
当从一个只连接有一个 key-preserved 表的视图时，可以选择 rowid
如果指定了 GROUP BY 子句，select_list 限制如下：
  1. 常量
  2. USER，UID，SYSDATE
  3. 聚集函数（SUM，MAX 等）
  4. 同时在 ORDER BY 子句中出现的表达式
  5. 对每一行得出的值相同的表达式

table_reference

{    ONLY ( <query_table_expression> ) [ <flashback_clause> ] [ t_alias ]

   | <query_table_expression> [ flashback_clause] [ t_alias ]

   | ( <joined_table> )

   | joined_table

}
关键词 描述
ONLY 仅用于视图，Specify ONLY if the view in the FROM clause is a view belonging to a hierarchy and you do not want to include rows from any of its subviews.

flashback_clause

回闪子句，可以查询记录更新以前的值。

AS OF { SCN | TIMESTAMP } expr
关键词 描述
SCN expr 必须为 number 类型
TIMESTAMP expr 必须为 TIMESTAMP 类型
expr （时间）

示例，查询一天以前的值

SELECT salary FROM employees

  AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)

  WHERE last_name = 'Chung';

query_table_expression

{

     query_name

   | [schema.]

      {

         table

         {

             {

                 PARTITION ( <partition> ) | <subpartition> ( subpartition ) 

             }

             [ <sample_clause> ]

           | <sample_clause>

           | @dblink

         }

         |

         {

             view | metarialized view

         } [ @dblink ]

      }

   | ( <subquery> [ <subquery_restriction_clause> ] )

   | <table_collection_expression>

}
关键词 描述
schema 拥有对象的架构名,与用户名一对一
若省略,则为当前登陆用户名
table 表名
PARTITION 表的分区名
SUBPARTITION 表的子分区名
dblink 数据库链接，不要求必须是 Oracle 数据库

列类型限制：用户定义类型，对象引用，AnyType, AnyData, AnyDataSet

sample_clause

随机数据抽样。

SAMPLE [BLOCK] ( <sample_percent> )
关键词 描述
BLOCK 随机块抽取，默认为随机行
sample_percent 抽取百分比, 从 0.000001 至 <100

subquery_restriction_clause

WITH 

{  

     READ ONLY

   | CHECK OPTION [ CONSTRAINT <constraint> ]

}
关键词 描述
READ ONLY 指示表或视图不允许被更新
CHECK OPTION 指示更新时必须满足的条件
constraint 约束名称（条件）

示例：约束条件缺省（插入失败）

INSERT INTO (SELECT department_id, department_name, location_id
FROM departments WHERE location_id < 2000 WITH CHECK OPTION)
VALUES (9999, 'Entertainment', 2500);

table_collection_expression

TABLE ( <collection_expression) [ (+) ]
参数 描述
(+) 左/右连接使用，扩充的记录字段全部为 null

joined_table

<table_reference>

{

   [ <join_type> ] JOIN <table_reference> { ON <condition> | USING ( column [,...] ) }

 |

   { CROSS JOIN | NATURAL [ <join_type> ] JOIN } 

   <table_reference>

}
关键词 描述
ON 指定连接条件
USING 简化语法，（当连接的字段名/别名相同，且为等于条件时）

待确认：

该语法导致输出时，相同名称的列仅输出一个，否则尽量取两者之中非空的那个
CROSS 相当于 INNER

join_type

{

    INNER

  |

    { LEFT | RIGHT | FULL }

    [OUTER]

}
关键词 描述
INNER 连接，记录满足指定条件
LEFT 左连接，左表的记录全部取出，不满足条件时，右表字段值全部为 null
RIGHT 右连接，右表的记录全部取出，不满足条件时，左表字段值全部为 null
FULL 全连接
OUTER 可选，只是看起来比较明确

where_clause

WHERE <condition>

hierarchical_query_clause

分级查询，级别通过 LEVEL 返回。

第一个条件确定第一级的记录。

第二个条件确定上下级的关系。

[ START WITH <condition> ] CONNECT BY PRIOR <condition>
关键词 描述
START WITH 第一级的记录
CONECT BY 下一级的条件

group_by_clause

分组表达式及分组后的条件

GROUP BY

{ 

    <expr>

  | <rollup_cube_clause>

  | <grouping_sets_clause> 

} [,...]

[HAVING <condition> ]

rollup_cube_clause

{

    ROLLUP

  | CUBE

} 

( <grouping_expression_list> )

group_sets_clause

GROUPING SETS ( 

  {

      <rollup_cube_clause>

    | <grouping_expression_list> 

  } 

)

grouping_expression_list

<expression_list> [,...]

expression_list

{

    expr [,...]

  | ( expr [,...] )

}

order_by_clause

ORDER [SIBLINGS] BY

{

   { <expr> | <position> | <c_alias> }

   [ ASC | DESC ] 

   [ NULLS { FIRST | LAST } ]

} [, ...]
关键词 描述
SIBLINGS 用于分层查询
expr 按表达式的值排序
position 按结果列号排序，从 1 起
c_alias 按别名对应的列排序
ASC 升序，默认
DESC 降序
FIRST 空值排在前
LAST 空值排在后，默认

for_update_clause

指定查询可更新。

FOR UPDATE

[

   OF [ [schema.]{ table | view }.]column [,...]

]

[ { NOWAIT | WAIT integer } ]
关键词 描述
OF 仅锁定给出的真实列所在的表或视图，默认为所有表或视图
NOWAIT 当记录被其他用户锁定时，不等待
WAIT n 当记录被其他用户锁定时，等待 n 秒
无 WAIT 系统会等待其他用户释放锁定，默认