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 系统会等待其他用户释放锁定,默认