SELECT
的语法相对比较复杂。本节首先会介绍 SIMPLE SELECT
语法结构,然后介绍集合类 SELECT
的语法结构,最后介绍带有 with clause
的 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);
obclient> SELECT name FROM a;
+------+
| NAME |
+------+
| a |
| b |
| a |
+------+
3 rows in set (0.00 sec)
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)
/* 在会话 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
查询的结果进行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
作为公共表达式,在主体查询中直接引用即可。
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)
分享文章:创新互联OceanBase教程:OceanBaseSELECT
转载来源:http://www.shufengxianlan.com/qtweb/news3/459503.html
网站建设、网络推广公司-创新互联,是专注品牌与效果的网站制作,网络营销seo公司;服务项目有等
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联