"云数据库OceanBase入门教程"的目录
云数据库OceanBase入门教程 OceanBase SELECT
SELECT 的语法相对比较复杂。本节首先会介绍 SIMPLE SELECT 语法结构，然后介绍集合类 SELECT 的语法结构，最后介绍带有 with clause 的 SELECT。
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 | 在数据库表中，可能会包含重复值。
|
select_expr | 列出要查询的表达式或列名，用“,”隔开。也可以用“*”表示所有列。 |
AS othername | 为输出字段重新命名。 |
FROM table_references | 指名了从哪个表或哪些表中读取数据（支持多表查询）。 |
WHERE where_conditions | 可选项， |
GROUP BY group_by_list | 按一些字段进行分组，产生统计值。 |
ROLLUP group_expression_list | 合并 Group By 的分组，产生统计值。 |
HAVING search_confitions |
|
ORDER BY order_list order_list : colname [ASC | DESC] [,colname [ASC | DESC]…] | 用来按升序（ASC）或者降序（DESC）显示查询结果。不指定 ASC 或者 DESC 时，默认为 ASC。 |
FOR UPDATE | 对查询结果所有行上排他锁，以阻止其他事务的并发修改，或阻止在某些事务隔离级别时的并发读取。
|
PARTITION(partition_list) | 指定查询表的分区信息。例如： |
示例
以如下表 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数据。
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中查询 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中根据筛选条件“ name = 'a' ”，输出对应的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分组对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中查询id、name和num，根据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中查询id、name和num，根据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查询的结果进行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
参数解释
参数 | 描述 |
|---|---|
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);
- 计算
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)
- 计算
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)
- 计算
t1和t2的交集SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;
+------+------+
| C1 | C2 |
+------+------+
| 2 | -2 |
+------+------+
- 计算
t1和t2的差集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)