SIMPLE SELECT

更新时间：2023-07-18 17:25:59

SELECT 的语法相对比较复杂，本节将介绍 SIMPLE SELECT 语法结构。

描述

该语句用于从表或视图中查询数据。

语法

simple_select:
    SELECT [ hint_options ] [ DISTINCT | UNIQUE | ALL] select_expr_list 
    FROM from_list 
    [WHERE condition] 
    [GROUP BY group_expression_list
        [{ROLLUP | GROUPING SETS} group_expression_list]
        [HAVING condition]
     ]
    [ORDER BY order_expression_list]
    [FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]
  
from_list:
    table_reference [, table_reference...]

table_reference:
    simple_table
    | joined_table
    | pivot_clause
    | unpivot_clause 
    | table_name@dblink_name

simple_table:
    table_factor [partition_option] [[table_alias_name]
    | (select_stmt)  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]

pivot_clause：
    PIVOT
    (aggregate_function ( expr ) [[AS] alias ]
      [, aggregate_function ( expr ) [[AS] alias ]... ]
     pivot_for_clause
     pivot_in_clause
    )

pivot_for_clause：
    FOR { column| ( column [, column... ]) } 
 
pivot_in_clause
    IN
    ( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... } 
       [, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
     )
      
unpivot_clause ：
    UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
    ( { column | ( column [, column... ]) }
     pivot_for_clause
     unpivot_in_clause
     )

unpivot_in_clause：
    IN
    ( { column | ( column [, column... ]) }[ AS { literal | ( literal [, literal... ]) } ]
        [, { column | ( column [, column... ] ) }[ AS {literal | ( literal [, literal... ]) } ]]
    )

语法说明

字段	描述
hint_options	指定 Hint 选项，为可选项。
DISTINCT \| UNIQUE \| ALL	在查询的数据中，可能会包含重复行。指定 `DISTINCT` 或 `UNIQUE`，表示在查询结果中相同的行只返回一行。`DISTINCT` 和 `UNIQUE` 是同义词。指定 `ALL`，表示在查询结果中返回所有的行。默认为 `ALL`。
select_expr_list	指定从数据库列出要查询的表达式或列名，用","隔开。""表示所有列。 `table_name.`：指定从指定的表、视图中选择所有的列。 `table_alias_name.`：指定表、视图的别名。 `expr [[AS] column_alias_name]`：指定查询列或表达式的别名，`AS` 是可选的。
FROM table_references	指定选择数据的对象。
PARTITION(partition_list)	指定查询表的分区信息。例如：`partition(p0,p1...)`
table_alias_name	指定选择数据对象的别名，格式为原名称空格别名。
joined_table	指定多表查询的联接方式。 `[INNER] JOIN` 为内联接，`INNER` 为可选项。仅返回满足联接条件的数据。 `[OUTER] JOIN` 为外联接，`OUTER` 为可选项。 `LEFT [OUTER]` 表示左外联接。返回左表中的所有公共列值。 `RIGHT [OUTER]` 表示右外联接。返回右表中的所有公共列值。 `FULL [OUTER]` 表示全外联接。即除了内联接之外，两个表中未在内联接结果中返回的行将被保留并用空值扩展。
ON expression	指定多表联接的联接条件。
WHERE where_conditions	指定筛选条件，查询结果中仅包含满足条件的数据，为可选项。`where_conditions` 为表达式。
GROUP BY group_by_list	指定分组的字段，通常与聚合函数配合使用。说明 `SELECT` 子句后面的所有列中，没有使用聚合函数的列，必须出现在 `GROUP BY` 子句后面。
ROLLUP group_expression_list	合并 Group By 的分组，产生统计值。
GROUPING SETS group_expression_list	在一个查询中指定多个数据分组，分别产生统计值，并将指定的分组统计值聚合显示。`GROUPING SETS` 中可以指定单个字段或字段列表。
HAVING search_conditions	筛选分组后的各组数据。`HAVING` 子句与 `WHERE` 子句类似，但是 `HAVING` 子句可以使用累计函数（如 `SUM`、`AVG` 等）。
ORDER BY order_list	指定结果集按照一个列或者多个列用来 `ASC` 或 `DESC` 显示查询结果。不指定 `ASC` 或者 `DESC` 时，默认为 `ASC`。 `ASC` 表示升序。 `DESC` 表示降序。
FOR UPDATE	对查询结果所有行上排他锁，以阻止其他事务的并发修改，或阻止在某些事务隔离级别时的并发读取。 `OF column`：对于多表 Join 的场景，使用该子句可以指示仅锁定某些表（即 `column` 指定列所在的表）的查询结果行。 `WAIT`：等待 `Interer` 时间后再对查询结果行尝试加锁，如果查询结果行中已经有被其他会话加锁的行，则执行失败。 `NOWAIT`：立即对查询结果行尝试加锁如果查询结果行中已经有被其他会话加锁的行，则执行失败。 `SKIP LOCKED`：如果查询结果中已经有被其他会话加锁的行，则会跳过这些加锁行，返回未被加锁的行。说明 `SKIP LOCKED` 暂不支持多表 `JOIN` 加锁的场景。
pivot_clause	将行旋转为列的子句。
aggregate_function	指定聚合函数。
expr	指定计算结果为常量值的表达式。 `pivot_in_clause` 只支持常量表达式。
unpivot_clause	将列旋转为行的子句。
dblink_name	指定要访问的数据库链接（即 `dblink`）的名称。

示例

创建示例表 tbl1。

obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

从表 tbl1 中读取 name 数据。

obclient> SELECT name FROM tbl1;
+------+
| NAME |
+------+
| a    |
| b    |
| a    |
+------+
3 rows in set

从表 tbl1 中读取 name 数据并进行去重处理。

obclient> SELECT DISTINCT name FROM tbl1;
+------+
| NAME |
+------+
| a    |
| b    |
+------+
2 rows in set

从表 tbl1 中查询 id、name 和 num，然后把 num 列除以 2 输出，输出的列名为 avg。

obclient> SELECT id, name, num/2 AS avg FROM tbl1;
+------+------+------+
| ID   | NAME | AVG  |
+------+------+------+
|    1 | a    |   50 |
|    2 | b    |  100 |
|    3 | a    |   25 |
+------+------+------+
3 rows in set

从表 tbl1 中根据筛选条件 name = 'a'，输出对应的 id 、name 和 num。

obclient> SELECT id, name, num FROM tbl1 WHERE name = 'a';
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    3 | a    |   50 |
+------+------+------+
2 rows in set

从表 tbl1 中查询 name，按照 name 分组对 num 求和，查询 num 总和小于 160 的行，并输出。

obclient> SELECT name,SUM(num)  sum 
                FROM tbl1 
                GROUP BY name 
                HAVING SUM(num) < 160;
+------+------+
| NAME | SUM  |
+------+------+
| a    |  150 |
+------+------+
1 row in set

从表 tbl1 中查询 id、name 和 num，根据 num 按升序输出查询结果。

obclient> SELECT * FROM tbl1 ORDER BY num ASC;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
|    1 | a    |  100 |
|    2 | b    |  200 |
+------+------+------+
3 rows in set

从表 tbl1 中查询所有列，根据 name 按降序，num 按升序输出查询结果。

obclient> SELECT * FROM tbl1 ORDER BY name DESC,num ASC;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    2 | b    |  200 |
|    3 | a    |   50 |
|    1 | a    |  100 |
+------+------+------+
3 rows in set (0.00 sec)

从表 tbl1 中查询指定 id 的行，并使用 FOR UPDATE 子句把查询结果行进行锁定。

/* 在会话 1 中查询表 tbl1 中 id=1 的行并锁定 */
obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
+------+------+------+
1 row in set

/* 在会话 2 中查询表 tbl1 中 id=1 或 id=2 的行并锁定 */
obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE;
ORA-30006: resource busy; acquire with WAIT timeout expired

obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    2 | b    |  200 |
+------+------+------+
1 row in set

从表 tbl1 中按 name 和 num 分组查询并统计每个分组中的数量。

obclient> SELECT name, num, COUNT(*) from tbl1 GROUP BY GROUPING SETS(name, num);
+------+------+----------+
| NAME | NUM  | COUNT(*) |
+------+------+----------+
| a    | NULL |        2 |
| b    | NULL |        1 |
| NULL |  100 |        1 |
| NULL |  200 |        1 |
| NULL |   50 |        1 |
+------+------+----------+
5 rows in set (0.01 sec)

将表 emp_phone 中的行转为列，再将列转为行。

obclient> CREATE TABLE emp(name VARCHAR2(50), num CHAR, phone VARCHAR2(50));
Query OK, 0 rows affected

obclient> INSERT INTO emp VALUES('ZhangSan', '1', '1234-5678'),('ZhangSan', '2', '3219-6066'),('ZhangSan', '3', '5365-9583');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM emp;
+----------+------+-----------+
| NAME     | NUM  | PHONE     |
+----------+------+-----------+
| ZhangSan | 1    | 1234-5678 |
| ZhangSan | 2    | 3219-6066 |
| ZhangSan | 3    | 5365-9583 |
+----------+------+-----------+
3 rows in set

/* 将表 emp 中的行转为列 */
obclient> SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
+----------+-----------+-----------+-----------+
| NAME     | HOME      | OFFICE    | MOBILE    |
+----------+-----------+-----------+-----------+
| ZhangSan | 1234-5678 | 3219-6066 | 5365-9583 |
+----------+-----------+-----------+-----------+
1 row in set 

/* 将表 emp 中的列转为行 */
obclient> CREATE VIEW v_emp AS SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
Query OK, 0 rows affected

obclient>  SELECT * FROM v_emp UNPIVOT(phone FOR num IN (home AS 1, office AS 2, mobile AS 3));
+----------+-----+-----------+
| NAME     | NUM | PHONE     |
+----------+-----+-----------+
| ZhangSan |   1 | 1234-5678 |
| ZhangSan |   2 | 3219-6066 |
| ZhangSan |   3 | 5365-9583 |
+----------+-----+-----------+
3 rows in set

查询远端数据库的表中数据。

/*访问远端的 OceanBase 数据库查询数据*/ 
obclient> SELECT ID FROM tbl2@ob_dblink;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set

obclient> SELECT * FROM tbl2@ob_dblink;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set (0.03 sec)

/*同时查询本地数据库和远端数据库的数据*/
obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;