OceanBase SELECT

?SELECT? 的語(yǔ)法相對(duì)比較復(fù)雜。本節(jié)首先會(huì)介紹 ?SIMPLE SELECT? 語(yǔ)法結(jié)構(gòu)，然后介紹集合類(lèi) ?SELECT? 的語(yǔ)法結(jié)構(gòu)，最后介紹帶有 ?with clause? 的 ?SELECT?。

SIMPLE SELECT

描述

該語(yǔ)句用于查詢表中的內(nèi)容。

格式

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]

參數(shù)解釋

示例

以如下表 ?a? 為例。

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數(shù)據(jù)。

  obclient> SELECT name FROM a; 
  +------+
  | NAME |
  +------+
  | a    | 
  | b    | 
  | a    | 
  +------+ 
  3 rows in set (0.00 sec)

• 在查詢結(jié)果中對(duì)name進(jìn)行去重處理。

  obclient> SELECT DISTINCT name FROM a;
  +------+
  | NAME | 
  +------+ 
  | a    | 
  | b    | 
  +------+ 
  2 rows in set (0.00 sec)

• 從表?a?中查詢 ?id?、?name?和?num?，然后把?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?中根據(jù)篩選條件“ name = 'a' ”，輸出對(duì)應(yīng)的?id? 、?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?分組對(duì)?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?分組對(duì)?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?中查詢?id?、?name?和?num?，根據(jù)?num?按升序（ASC）輸出查詢結(jié)果。

  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?中查詢?id?、?name?和?num?，根據(jù)?num?按降序（DESC）輸出查詢結(jié)果。

  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子句把查詢結(jié)果行進(jìn)行鎖定。

  /* 在會(huì)話 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) 
  
  /* 在會(huì)話 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)

集合類(lèi) SELECT

描述

該語(yǔ)句用于對(duì)多個(gè)?SELECT?查詢的結(jié)果進(jìn)行?UNION?、?MINUS?、?INTERSECT?。

格式

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

參數(shù)解釋

示例

以如下兩表的數(shù)據(jù)為例：

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);

• 計(jì)算?t1?、?t2?的所有的記錄

  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)

• 計(jì)算?t1?、?t2?的去重后的所有記錄

  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)

• 計(jì)算?t1?和?t2?的交集

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

• 計(jì)算?t1?和?t2?的差集

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

帶有 with clause 的 SELECT

描述

如果查詢語(yǔ)句中有多個(gè)相同的子查詢，可以把相同的子查詢放在?with clause?作為公共表達(dá)式，在主體查詢中直接引用即可。

格式

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

參數(shù)解釋

無(wú)

示例

以如下表格數(shù)據(jù)和?SELECT?查詢?yōu)槔?/p>

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)

可以抽取相同子查詢?yōu)?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)