建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
創(chuàng)新互聯(lián)OceanBase教程：OceanBaseSELECT
?SELECT? 的語法相對(duì)比較復(fù)雜。本節(jié)首先會(huì)介紹 ?SIMPLE SELECT? 語法結(jié)構(gòu)，然后介紹集合類 ?SELECT? 的語法結(jié)構(gòu)，最后介紹帶有 ?with clause? 的 ?SELECT?。
SIMPLE SELECT
描述
該語句用于查詢表中的內(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ù)解釋
|
參數(shù) |
描述 |
|---|---|
|
DISTINCT | UNIQUE | ALL |
在數(shù)據(jù)庫表中，可能會(huì)包含重復(fù)值。
|
|
select_expr |
列出要查詢的表達(dá)式或列名，用“,”隔開。也可以用“*”表示所有列。 |
|
AS othername |
為輸出字段重新命名。 |
|
FROM table_references |
指名了從哪個(gè)表或哪些表中讀取數(shù)據(jù)（支持多表查詢）。 |
|
WHERE where_conditions |
可選項(xiàng)， |
|
GROUP BY group_by_list |
按一些字段進(jìn)行分組，產(chǎn)生統(tǒng)計(jì)值。 |
|
ROLLUP group_expression_list |
合并 Group By 的分組，產(chǎn)生統(tǒng)計(jì)值。 |
|
HAVING search_confitions |
|
|
ORDER BY order_list order_list : colname [ASC | DESC] [,colname [ASC | DESC]…] |
用來按升序（ASC）或者降序（DESC）顯示查詢結(jié)果。不指定 ASC 或者 DESC 時(shí)，默認(rèn)為 ASC。 |
|
FOR UPDATE |
對(duì)查詢結(jié)果所有行上排他鎖，以阻止其他事務(wù)的并發(fā)修改，或阻止在某些事務(wù)隔離級(jí)別時(shí)的并發(fā)讀取。
|
|
PARTITION(partition_list) |
指定查詢表的分區(qū)信息。例如： |
示例
以如下表 ?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)
集合類 SELECT
描述
該語句用于對(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ù) |
描述 |
|---|---|
|
UNION ALL |
合并兩個(gè)查詢的結(jié)果 |
|
UNION |
合并兩個(gè)查詢的結(jié)果，并去重 |
|
MINUS |
從左查詢結(jié)果集中去重出現(xiàn)在右查詢中的結(jié)果，并去重 |
|
INTERSECT |
保留左查詢結(jié)果集中出現(xiàn)在右查詢中的結(jié)果，并去重 |
示例
以如下兩表的數(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
描述
如果查詢語句中有多個(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ù)解釋
無
示例
以如下表格數(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)當(dāng)前標(biāo)題：創(chuàng)新互聯(lián)OceanBase教程：OceanBaseSELECT
分享URL：http://fisionsoft.com.cn/article/ccdioph.html
