全部产品

SELECT

SELECT的语法相对比较复杂。本节首先会介绍SIMPLE SELECT语法结构,然后介绍集合类SELECT的语法结构,最后介绍带有with clauseSELECT

SIMPLE SELECT

描述

该语句用于查询表中的内容。

格式

simple_select:
SELECT [/*+ hint statement */] [DISTINCT | UNIQUE | ALL] 
    select_expr_list FROM from_list [WHERE condition] 
  [GROUP BY group_expression_list] [ROLLUP group_expression_list] [HAVING condition]]
  [ORDER BY order_expression_list]
  [FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]

select_expr:
    table_name.*
  | table_alias_name.*
  | expr [[AS] column_alias_name]
  
from_list:
    table_reference [, table_reference ...]

table_reference:
      simple_table
    | joined_table

simple_table:
    table_factor [partition_option] [[AS] table_alias_name]
    | (select_stmt) [AS] table_alias_name
    | (table_reference_list)

joined_table:
      table_reference [INNER] JOIN simple_table [join_condition]
    | table_reference outer_join_type JOIN simple_table join_condition

partition_option:
    PARTITION (partition_name_list)

partition_name_list:
    partition_name [, partition_name ...]

outer_join_type:
    {LEFT | RIGHT | FULL} [OUTER]

join_condition:
    ON expression

condition:
    expression

group_expression_list:
    group_expression [, group_expression ...]

group_expression:
    expression [ASC | DESC]

order_expression_list:
    order_expression [, order_expression ...]

order_expression:
    expression [ASC | DESC]

参数解释

参数

描述

DISTINCT | UNIQUE | ALL

在数据库表中,可能会包含重复值。

  • 指定“DISTINCT”,则在查询结果中相同的行只显示一行。

  • 指定 “UNIQUE”,则在查询结果中相同的行只显示一行。

  • 指定“ALL”,则在查询结果中显示所有的行(包含重复),默认为 ALL。

select_expr

列出要查询的表达式或列名,用“,”隔开。也可以用“*”表示所有列。

AS othername

为输出字段重新命名。

FROM table_references

指名了从哪个表或哪些表中读取数据(支持多表查询)。

WHERE where_conditions

可选项,WHERE子句用来设置一个筛选条件,查询结果中仅包含满足条件的数据。where_conditions 为表达式。

GROUP BY group_by_list

按一些字段进行分组,产生统计值。

ROLLUP group_expression_list

合并 Group By 的分组,产生统计值。

HAVING search_confitions

HAVING子句与WHERE子句类似,但是HAVING子句可以使用累计函数(如 SUM、AVG 等)。

ORDER BY order_list

order_list :

colname [ASC | DESC] [,colname [ASC | DESC]…]

用来按升序(ASC)或者降序(DESC)显示查询结果。不指定 ASC 或者 DESC 时,默认为 ASC。

FOR UPDATE

对查询结果所有行上排他锁,以阻止其他事务的并发修改,或阻止在某些事务隔离级别时的并发读取。

  • OF column:对于多表 Join 的场景,使用该子句可以指示仅锁定某些表(即 column 指定列所在的表)的查询结果行。

  • WAIT:等待 Interger 时间后再对查询结果行尝试加锁,如果查询结果行中已经有被其他会话加锁的行,则执行失败。

  • NOWAIT:立即对查询结果行尝试加锁如果查询结果行中已经有被其他会话加锁的行,则执行失败。

  • SKIP LOCKED:如果查询结果中已经有被其他会话加锁的行,则会跳过这些加锁行,返回未被加锁的行。

    说明

    SKIP LOCKED 暂不支持多表 JOIN 加锁的场景。

PARTITION(partition_list)

指定查询表的分区信息。例如:partition(p0,p1…)

示例

以如下表a为例。

image.png

CREATE TABLE a (id INT,name VARCHAR(10),num INT);
INSERT INTO a VALUES (1, 'a',100);
INSERT INTO a VALUES (2, 'b',200);
INSERT INTO a VALUES (3, 'a',50);

  • 从表a中读取name数据。

    obclient> SELECT name FROM a; 
    +------+
    | NAME |
    +------+
    | a    |
    | b    |
    | a    |
    +------+
    3 rows in set (0.00 sec)
  • 在查询结果中对name进行去重处理。

    obclient> SELECT DISTINCT name FROM a;
    +------+
    | NAME |
    +------+
    | a    |
    | b    |
    +------+
    2 rows in set (0.00 sec)
  • 从表a中查询 idnamenum,然后把num列除以 2 输出,输出的列名为avg

    obclient> SELECT id, name, num/2 AS avg FROM a;
    +----+------+------+
    | ID | NAME | AVG  |
    +----+------+------+
    |  1 | a    |   50 |
    |  2 | b    |  100 |
    |  3 | a    |   25 |
    +----+------+------+
    3 rows in set (0.00 sec)
  • 从表a中根据筛选条件“ name = 'a' ”,输出对应的idnamenum

    obclient> SELECT id, name, num FROM a WHERE name = 'a';
    +----+------+------+
    | ID | NAME | NUM  |
    +----+------+------+
    |  1 | a    |  100 |
    |  3 | a    |   50 |
    +----+------+------+
    2 rows in set (0.00 sec)
  • 从表a中查询name,按照name分组对num求和,并输出。

    obclient> SELECT id, name, num FROM a WHERE name = 'a';
    +----+------+------+
    | ID | NAME | NUM  |
    +----+------+------+
    |  1 | a    |  100 |
    |  3 | a    |   50 |
    +----+------+------+
    2 rows in set (0.00 sec)
  • 从表a中查询name,按照name分组对num求和,查询num总和小于 160 的行,并输出。

    obclient> SELECT name, SUM(num) as sum FROM a GROUP BY name HAVING SUM(num) < 160;
    +------+------+
    | NAME | SUM  |
    +------+------+
    | a    |  150 |
    +------+------+
    1 row in set (0.00 sec)
  • 从表a中查询idnamenum,根据num按升序(ASC)输出查询结果。

    obclient> SELECT * FROM a ORDER BY num ASC;
    +----+------+------+
    | ID | NAME | NUM  |
    +----+------+------+
    |  3 | a    |   50 |
    |  1 | a    |  100 |
    |  2 | b    |  200 |
    +----+------+------+
    3 rows in set (0.00 sec)
  • 从表a中查询idnamenum,根据num按降序(DESC)输出查询结果。

    obclient> SELECT * FROM a ORDER BY num DESC;
    +----+------+------+
    | ID | NAME | NUM  |
    +----+------+------+
    |  2 | b    |  200 |
    |  1 | a    |  100 |
    |  3 | a    |   50 |
    +----+------+------+
    3 rows in set (0.00 sec)

  • 从表a中查询指定id的行,并使用FOR UPDATE子句把查询结果行进行锁定。

    /* 在会话 1 中查询表 a 中 id=1 的行并锁定 */
    obclient> SELECT * FROM a WHERE id=1 FOR UPDATE;
    +------+------+------+
    | ID   | NAME | NUM  |
    +------+------+------+
    |    1 | a    |  100 |
    +------+------+------+
    1 row in set (0.01 sec)
    
    /* 在会话 2 中查询表 a 中 id=1 或 id=2 的行并锁定 */
    obclient> SELECT * FROM a WHERE id=1 or id=2 FOR UPDATE;
    ORA-30006: resource busy; acquire with WAIT timeout expired
    
    obclient> SELECT * FROM a WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED;
    +------+------+------+
    | ID   | NAME | NUM  |
    +------+------+------+
    |    2 | b    |  200 |
    +------+------+------+
    1 row in set (0.01 sec)

集合类 SELECT

描述

该语句用于对多个SELECT查询的结果进行UNIONMINUSINTERSECT

格式

select_clause_set:
  simple_select [ UNION | UNION ALL |  | INTERSECT] select_clause_set_right
  [ORDER BY sort_list_columns]

select_clause_set_right:
  simple_select | 
  select_caluse_set

参数解释

参数

描述

UNION ALL

合并两个查询的结果

UNION

合并两个查询的结果,并去重

MINUS

从左查询结果集中去重出现在右查询中的结果,并去重

INTERSECT

保留左查询结果集中出现在右查询中的结果,并去重

示例

以如下两表的数据为例:

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (1, -1), (2, -2);
INSERT INTO t2 VALUES (1, 1), (2, -2), (3, 3);
  • 计算t1t2的所有的记录

    obclient>SELECT c1, c2 FROM t1 UNION ALL SELECT c1, c2 FROM t2;
    +------+------+
    | C1   | C2   |
    +------+------+
    |    1 |   -1 |
    |    2 |   -2 |
    |    1 |    1 |
    |    2 |   -2 |
    |    3 |    3 |
    +------+------+
    5 rows in set (0.01 sec)
  • 计算t1t2的去重后的所有记录

    obclient>SELECT c1, c2 FROM t1 UNION SELECT c1, c2 FROM t2;
    +------+------+
    | C1   | C2   |
    +------+------+
    |    1 |   -1 |
    |    2 |   -2 |
    |    1 |    1 |
    |    3 |    3 |
    +------+------+
    4 rows in set (0.01 sec)
  • 计算t1t2的交集

    SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;
    +------+------+
    | C1   | C2   |
    +------+------+
    |    2 |   -2 |
    +------+------+
  • 计算t1t2的差集

    obclient>SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;
    +------+------+
    | C1   | C2   |
    +------+------+
    |    2 |   -2 |
    +------+------+

带有 with clause 的 SELECT

描述

如果查询语句中有多个相同的子查询,可以把相同的子查询放在with clause作为公共表达式,在主体查询中直接引用即可。

格式

with_clause_select:
    with_clause simple_select

with_clause:
    WITH table_name [opt_column_alias_name_list] AS ( select_clause )

select_clause:
    simple_select | select_clause_set
  
opt_column_alias_name_list:
    (column_name_list)
  
column_name_list:
    column_name | column_name , column_name_list

参数解释

示例

以如下表格数据和SELECT查询为例。

CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
CREATE TABLE t2(c1 INT);
INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(2,2,2);
INSERT INTO t1 VALUES(3,3,3);
INSERT INTO t2 VALUES(4);

obclient>SELECT * FROM t1 WHERE c1 > (SELECT COUNT(*) FROM t2)
                      AND c2 > (SELECT COUNT(*) FROM t2)
                      AND c3 > (SELECT COUNT(*) FROM t2);
+------+------+------+
| C1   | C2   | C3   |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
2 rows in set (0.01 sec)

可以抽取相同子查询为with clause

obclient>WITH TEMP(cnt) AS (SELECT COUNT(*) FROM t2)
      SELECT t1.* FROM t1, temp WHERE c1 > temp.cnt AND c2 > temp.cnt 
      AND c3 > temp.cnt;
+------+------+------+
| C1   | C2   | C3   |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
2 rows in set (0.00 sec)