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

SIMPLE SELECT

描述

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

格式

  1. simple_select:
  2. SELECT [/*+ hint statement */] [DISTINCT | UNIQUE | ALL] 
  3.     select_expr_list FROM from_list [WHERE condition] 
  4.   [GROUP BY group_expression_list] [ROLLUP group_expression_list] [HAVING condition]]
  5.   [ORDER BY order_expression_list]
  6.   [FOR UPDATE]
  8. select_expr:
  9.     table_name.*
  10.   | table_alias_name.*
  11.   | expr [[AS] column_alias_name]
  13. from_list:
  14.     table_reference [, table_reference ...]
  16. table_reference:
  17.       simple_table
  18.     | joined_table
  20. simple_table:
  21.     table_factor [partition_option] [[AS] table_alias_name]
  22.     | (select_stmt) [AS] table_alias_name
  23.     | (table_reference_list)
  25. joined_table:
  26.       table_reference [INNER] JOIN simple_table [join_condition]
  27.     | table_reference outer_join_type JOIN simple_table join_condition
  29. partition_option:
  30.     PARTITION (partition_name_list)
  32. partition_name_list:
  33.     partition_name [, partition_name ...]
  35. outer_join_type:
  36.     {LEFT | RIGHT | FULL} [OUTER]
  38. join_condition:
  39.     ON expression
  41. condition:
  42.     expression
  44. group_expression_list:
  45.     group_expression [, group_expression ...]
  47. group_expression:
  48.     expression [ASC | DESC]
  50. order_expression_list:
  51.     order_expression [, order_expression ...]
  53. order_expression:
  54.     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

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

PARTITION(partition_list)

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

示例

以如下表 a 为例。

image.png

  • 从表 a 中读取 name 数据。
  1. SELECT name FROM a;

image.png

  • 在查询结果中对 name 进行去重处理。
  1. SELECT DISTINCT name FROM a;

image.png

  • 从表 a 中查询 id，name，num，然后把 num 列除以2输出，输出的列名为 avg。
  1. SELECT id, name, num/2 AS avg FROM a;

image.png

  • 从表 a 中根据筛选条件“ name = ‘a’ ” ，输出对应的 id 、name 和 num 。
  1. SELECT id, name, num FROM a WHERE name = 'a';

image.png

  • 从表 a 中查询 id，name，按照 name 分组对 num 求和，并输出。
  1. SELECT id, name, SUM(num) FROM a GROUP BY name;

image.png

  • 从表 a 中查询 id，name，按照 name 分组对 num 求和，查询 num 总和小于160的行，并输出。
  1. SELECT id, name, SUM(num) as sum FROM a GROUP BY name HAVING SUM(num) < 160;

image.png

  • 从表 a 中查询 id，name，num，根据 num 按升序（ASC）输出查询结果。
  1. SELECT * FROM a ORDER BY num ASC;

image.png

  • 从表 a 中查询 id，name，num，根据 num 按降序（DESC）输出查询结果。
  1. SELECT * FROM a ORDER BY num DESC;

image.png

集合类 SELECT

描述

该语句用于对多个 SELECT 查询的结果进行 UNION，MINUS，INTERSECT。

格式

  1. select_clause_set:
  2.   simple_select [ UNION | UNION ALL |  | INTERSECT] select_clause_set_right
  3.   [ORDER BY sort_list_columns]
  5. select_clause_set_right:
  6.   simple_select | 
  7.   select_caluse_set

参数解释

参数

说明

UNION ALL

合并两个查询的结果

UNION

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

MINUS

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

INTERSECT

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

示例

以如下两表的数据为例：

  1. create table t1 (c1 int, c2 int);
  2. create table t2 (c1 int, c2 int);
  3. insert into t1 values (1, -1), (2, -2);
  4. insert into t2 values (1, 1), (2, -2), (3, 3);
  • 计算 T1, T2 的所有的记录
  1. SELECT C1, C2 FROM T1 UNION ALL SELECT C1, C2 FROM T2;
  2. +------+------+
  3. | C1   | C2   |
  4. +------+------+
  5. |    1 |   -1 |
  6. |    2 |   -2 |
  7. |    1 |    1 |
  8. |    2 |   -2 |
  9. |    3 |    3 |
  10. +------+------+
  • 计算 T1, T2 的去重后的所有记录
  1. SELECT C1, C2 FROM T1 UNION SELECT C1, C2 FROM T2;
  2. +------+------+
  3. | C1   | C2   |
  4. +------+------+
  5. |    1 |   -1 |
  6. |    2 |   -2 |
  7. |    1 |    1 |
  8. |    3 |    3 |
  9. +------+------+
  • 计算 T1 和 T2 的交集
  1. SELECT C1, C2 FROM T1 INTERSECT SELECT C1, C2 FROM T2;
  2. +------+------+
  3. | C1   | C2   |
  4. +------+------+
  5. |    2 |   -2 |
  6. +------+------+
  • 计算 T1 和 T2 的差集
  1. SELECT C1, C2 FROM T1 MINUS SELECT C1, C2 FROM T2;
  2. +------+------+
  3. | C1   | C2   |
  4. +------+------+
  5. |    1 |   -1 |
  6. +------+------+

带有 with clause 的 SELECT

描述

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

格式

  1. with_clause_select:
  2.     with_clause simple_select
  4. with_clause:
  5.     WITH table_name [opt_column_alias_name_list] AS ( select_clause )
  7. select_clause:
  8.     simple_select | select_clause_set
  10. opt_column_alias_name_list:
  11.     (column_name_list)
  13. column_name_list:
  14.     column_name | column_name , column_name_list

参数解释

无

示例

  • 以如下表格数据和 SELECT 查询为例。
  1. create table t1(c1 int, c2 int, c3 int);
  2. create table t2(c1 int);
  3. insert into t1 values(1,1,1);
  4. insert into t1 values(2,2,2);
  5. insert into t1 values(3,3,3);
  6. insert into t2 values(4);
  8. select * from t1 where c1 > (select count(*) from t2)
  9.                       and c2 > (select count(*) from t2)
  10.                       and c3 > (select count(*) from t2);
  11. +------+------+------+
  12. | C1   | C2   | C3   |
  13. +------+------+------+
  14. |    2 |    2 |    2 |
  15. |    3 |    3 |    3 |
  16. +------+------+------+

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

  1. with temp(cnt) as (select count(*) from t2)
  2. select t1.* from t1, temp where c1 > temp.cnt and c2 > temp.cnt and c3 > temp.cnt;
  3. +------+------+------+
  4. | C1   | C2   | C3   |
  5. +------+------+------+
  6. |    2 |    2 |    2 |
  7. |    3 |    3 |    3 |
  8. +------+------+------+