mysql 伪表查询语句_MySQL的SQL语句 - 数据操作语句（12）- SELECT 语句（1）

SELECT 语句

1. SELECT

2. [ALL | DISTINCT | DISTINCTROW ]

3. [HIGH_PRIORITY]

4. [STRAIGHT_JOIN]

5. [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

6. [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

7. select_expr [, select_expr] ...

8. [into_option]

9. [FROM table_references

1. [PARTITION partition_list]]

11. [WHERE where_condition]

12. [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]

13. [HAVING where_condition]

14. [WINDOW window_name AS (window_spec)

15. [, window_name AS (window_spec)] ...]

16. [ORDER BY {col_name | expr | position}

17. [ASC | DESC], ... [WITH ROLLUP]]

18. [LIMIT {[offset,] row_count | row_count OFFSET offset}]

19. [into_option]

20. [FOR {UPDATE | SHARE}

21. [OF tbl_name [, tbl_name] ...]

22. [NOWAIT | SKIP LOCKED]

23. | LOCK IN SHARE MODE]

24. [into_option]

25.

26. into_option: {

27. INTO OUTFILE 'file_name'

28. [CHARACTER SET charset_name]

29. export_options

30. | INTO DUMPFILE 'file_name'

31. | INTO var_name [, var_name] ...

32. }

SELECT 用于从一个或多个表中检索选择的行，可以包括 UNION 语句和子查询。

SELECT 语句最常用的子句如下：

● 每个 select_expr 都表示要检索的列。必须至少有一个 select_expr。

● table_references 指示要检索的一个或多个表。

● SELECT 支持在 table_reference 表名后面使用 PARTITION 以及分区和子分区列表来显式选择分区。在这种情况下，只从列出的分区中选择行，而忽略表的任何其他分区。

● WHERE 子句(如果给定)指示要选择行必须满足的条件。where_condition 是一个表达式，对于要选择的每一行，其计算结果为 true。如果没有 WHERE 子句，语句将选择所有行。

在 WHERE 表达式中，可以使用 MySQL 支持的任何函数和运算符，但聚合(group)函数除外。

SELECT 还可用于检索不引用任何表的计算行。

例如：

1. mysql> SELECT 1 + 1;

2. -> 2

在没有引用表的情况下，可以将 DUAL 指定为伪表名：

1. mysql> SELECT 1 + 1 FROM DUAL;

2. -> 2

DUAL 纯粹是为了方便那些要求所有SELECT语句都应该有 FROM 和其他子句的人。MySQL 可能会忽略这些子句。如果没有引用什么表，MySQL 不需要 FROM DUAL。

一般来说，使用的子句必须严格按照语法描述中显示的顺序给出。例如，HAVING 子句必须位于任何 GROUP BY 子句之后和 ORDER BY 子句之前。INTO 子句(如果存在)可以出现在语法描述指定的任何位置，但在给定语句中只能出现一次，而不能出现在多个位置。

select_expr 列表表达式组成了选择列表，该列表说明要检索的列，它指定列或表达式，或者可以使用简写 *：

● 如果选择列只包含一个无限制条件的 *，则可用作从所有表中选择所有列的简写：

1. SELECT * FROM t1 INNER JOIN t2 ...

● tbl_name.* 可用作有限定的简写，从命名表中选择所有列：

1. SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

● 在选中列中和其他项目一起使用没有限定条件的 可能会产生解析错误。要避免此问题，请使用有限定的 tbl_name. 引用

1. SELECT AVG(score), t1.* FROM t1 ...

以下列表提供了有关其他 SELECT 子句的其他信息：

● 可以使用 AS alias_name 给 select_expr 指定一个别名。别名用作表达式的列名，可以在 GROUP BY、ORDER BY 或 HAVING 子句中使用。例如：

1. SELECT CONCAT(last_name,', ',first_name) AS full_name

2. FROM mytable ORDER BY full_name;

使用标识符为 select_expr 设置别名时，AS 关键字是可选的。前面的例子可以这样写：

SELECT CONCAT(last_name,', ',first_name) full_name

FROM mytable ORDER BY full_name;

但是，由于 AS 是可选的，如果忘记了两个 select_expr 表达式之间的逗号，就会出现一个微妙的问题：MySQL 将第二个表达式解释为别名。例如，在以下语句中，columnb 被视为别名：

1. SELECT columna columnb FROM mytable;

因此，在指定列别名时，最好养成显式使用 AS 的习惯。

不允许在 WHERE 子句中引用列别名，因为在执行 WHERE 子句时可能尚未确定列值。

● FROM table_references 子句指示要从中检索行的一个或多个表。如果指定了多个表，则正在执行联合查询。对于指定的每个表，可以选择指定别名。

1. tbl_name [[AS] alias] [index_hint]

索引提示的使用为优化器提供了有关在查询处理期间如何选择索引的信息。

可以使用 SET max_seeks_for_key=value 作为另一种方法来强制 MySQL 选择键扫描而不是表扫描。

● 可以将默认数据库中的表引用为 tbl_name 或 db_name.tbl_name 显式指定数据库。引用列可以采取 col_name、tbl_name.col_name、或 db_name.tbl_name.col_name 等方式。不需要为列指定 tbl_name 或 db_name.tbl_name 引用前缀，除非引用不明确。

表引用可以使用 tbl_name AS alias_name 或 tbl_name alias_name 方式来设置别名。这些语句等价：

1. SELECT t1.name, t2.salary FROM employee AS t1, info AS t2

2. WHERE t1.name = t2.name;

3.

4. SELECT t1.name, t2.salary FROM employee t1, info t2

5. WHERE t1.name = t2.name;

● 可以使用列名、列别名或列位置在 ORDER BY 和 GROUP BY 子句中引用做为输出选择的列。列位置是整数，从 1 开始：

1. SELECT college, region, seed FROM tournament

2. ORDER BY region, seed;

3.

4. SELECT college, region AS r, seed AS s FROM tournament

5. ORDER BY r, s;

6.

7. SELECT college, region, seed FROM tournament

8. ORDER BY 2, 3;

要按倒序排序，请将 DESC(降序)关键字添加到要排序的 ORDER BY 子句中的列名称后。默认为升序排序；可使用 ASC 关键字显式指定。

如果 ORDER BY 发生在括号的查询表达式中，也应用于外部查询，那么结果是未定义的，并且在将来的 MySQL 版本中可能会发生更改。

不推荐使用列位置，因为该语法已从 SQL 标准中删除。

● 在 MySQL8.0.13 之前，MySQL 支持一个非标准的语法扩展，允许显式对 GROUP BY 列使用 ASC 或 DESC 指示符。MySQL8.0.12 及更高版本支持 ORDER BY 和分组函数，因此不再需要使用此扩展。这也意味着可以在使用 GROUP BY 时对任意一列或多个列进行排序，如下所示：

1. SELECT a, b, COUNT(c) AS t FROM test_table GROUP BY a,b ORDER BY a,t DESC;

从 MySQL 8.0.13 开始，不再支持 GROUP BY 扩展：不允许对 GROUP BY 列使用 ASC 或 DESC 指示符。

● 使用 ORDER BY 或 GROUP BY 对 SELECT 中的列进行排序时，服务器仅使用 max_sort_length 系统变量指示的初始字节数对值进行排序。

● MySQL 扩展了 GROUP BY 的使用，允许选择 GROUP BY 子句中没有提到的字段。

● GROUP BY 允许使用 WITH ROLLUP 修饰符。

以前，不允许在具有 WITH ROLLUP 修饰符的查询中使用 ORDER BY。这个限制从 MySQL 8.0.12 开始取消。

● HAVING 子句几乎最后才应用，在数据被发送到客户端之前，不进行任何优化。(LIMIT 应用在 HAVING 之后。)

SQL 标准要求 HAVING 必须只引用 GROUP BY 子句中的列或聚合函数中使用的列。但是，MySQL 支持对这种行为的扩展，允许引用 SELECT 列表中的列和外部子查询中的列。

如果 HAVING 子句引用的列不明确，则会出现警告。在下面的语句中，col2 是不明确的，因为它同时用作别名和列名：

1. SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

首选项是标准 SQL 行为，因此，如果在 GROUP BY 中使用 HAVING 列名，并在输出列列表中将其用作别名列，则优先选择 GROUP BY 列中的列。

● 对于应该在 WHERE 子句中的项，不要使用 HAVING。例如，不要写以下内容：

1. SELECT col_name FROM tbl_name HAVING col_name > 0;

改为这样写：

1. SELECT col_name FROM tbl_name WHERE col_name > 0;

● HAVING 子句可以引用聚合函数，WHERE 子句不能：

1. SELECT user, MAX(salary) FROM users

2. GROUP BY user HAVING MAX(salary) > 10;

(这在一些旧版本的 MySQL 中不起作用。)

● MySQL 允许重复的列名。也就是说，可以有多个具有相同名称的 select_expr。这是标准 SQL 的扩展。因为 MySQL 还允许 GROUP BY 和 HAVING 引用 select_expr 值，这可能会导致歧义：

1. SELECT 12 AS a, a FROM t GROUP BY a;

在该语句中，两列的名称都为 a。为了确保正确的列用于分组，请为每个 select_expr 使用不同的名称。

● WINDOW 子句(如果存在)定义了窗口函数可以引用的命名窗口。

● MySQL 通过在 select_expr 值中搜索，然后在 FROM 子句中的表的列中搜索，来解析未限定的列或别名引用。对于 GROUP BY 或 HAVING 子句，它在搜索 select_expr 值之前先搜索 FROM 子句。(对于 GROUP BY 和 HAVING，这与 MySQL 5.0 之前的行为不同，后者使用与 ORDER BY 相同的规则。)

● LIMIT 子句可用于约束 SELECT 语句返回的行数。LIMIT 接受一个或两个数字参数，它们必须都是非负整数常量，但以下情况除外：

■ 在预编译语句中，可以使用 ? 占位符标记。

■ 在存储程序中，可以使用整数值例程参数或局部变量指定 LIMIT 参数。

对于两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为0(不是1)：

1. SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

要检索从某个偏移量到结果集末尾的所有行，可以使用一些较大的数字作为第二个参数。此语句检索从第96行到最后一行的所有行：

1. SELECT * FROM tbl LIMIT 95,18446744073709551615;

使用一个参数，该值指定从结果集开始返回的行数：

1. SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

换句话说，LIMIT row_count 相当于 LIMIT 0, row_count。

对于预编译语句，可以使用占位符。以下语句将从 tbl 表中返回一行：

1. SET @a=1;

2. PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';

3. EXECUTE STMT USING @a;

以下语句将返回 tbl 表中的第二行到第六行：

1. SET @skip=1; SET @numrows=5;

2. PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';

3. EXECUTE STMT USING @skip, @numrows;

为了与 PostgreSQL 兼容，MySQL 还支持 LIMIT row_count OFFSET offset 语法。

如果 LIMIT 出现在圆括号的查询表达式中，也应用于外部查询，结果是未定义的，在将来的 MySQL 版本中可能会发生更改。

● SELECT 语句的 SELECT ... INTO 形式允许将查询结果写入文件或存储在变量中。

● 如果在使用页锁或行锁的存储引擎中使用 FOR UPDATE，则查询检查的行将被写锁定，直到当前事务结束。

在 CREATE TABLE new_table SELECT ... FROM old_table .... 之类的语句中，不能将 FOR UPDATE 用于 SELECT (如果尝试这样做，语句将被拒绝，并出现错误 Can't update table 'old_table' while 'new_table' is being created。)

FOR SHARE 和 LOCK IN SHARE MODE，设置共享锁，允许其他事务读取检查的行，但不更新或删除它们。FOR SHARE 和 LOCK IN SHARE MODE 是等效的。但是，FOR SHARE 和 FOR UPDATE 一样，支持 NOWAIT、SKIP LOCKED 和 OF tbl_name 选项。FOR SHARE 是 LOCK IN SHARE MODE 的替代品，但出于向后兼容性的考虑，LOCK IN SHARE MODE 仍然可用。

NOWAIT 指示 FOR UPDATE 或 FOR SHARE 查询立即执行，如果由于另一个事务持有的锁而无法获得行锁，则返回错误。

SKIP LOCKED 指示 FOR UPDATE 或 FOR SHARE 查询立即执行，排除结果集中被另一个事务锁定的行。

NOWAIT 和 SKIP LOCKED 选项对于基于语句的复制是不安全的。

注意

跳过锁定行的查询将返回不一致的数据视图。因此，SKIP LOCKED 不适用于一般事务性工作。但是，当多个会话访问同一个类似队列的表时，可以使用它来避免锁竞争。

OF tbl_name 应用于表的 FOR UPDATE 和 FOR SHARE 查询。例如：

1. SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;

当省略 OF tbl_name 时，查询块引用的所有表都将被锁定。因此，将不带 OF tbl_name 的锁定子句与另一个锁定子句结合使用将返回错误。在多个锁定子句中指定同一个表将返回错误。如果在 SELECT 语句中为表指定了别名，则锁定子句只能使用该别名。如果 SELECT 语句没有显式地指定别名，那么锁定子句只能指定实际的表名。

在 SELECT 关键字之后，可以使用许多影响语句操作的修饰符。HIGH_PRIORITY、STRAIGHTJOIN 和以 SQL 开头的修饰符是 MySQL 对标准 SQL 的扩展。

● ALL 和 DISTINCT 修饰符指定是否应返回重复行。ALL(默认)指定返回所有匹配的行，包括重复的行。DISTINCT 指定从结果集中删除重复行。同时指定这两个修饰符是错误的。DISTINCTROW 是 DISTINCT 的同义词。

在 MySQL 8.0.12 及更高版本中，DISTINCT 可以用于同时使用 WITH ROLLUP 的查询。

● HIGH_PRIORITY 赋予 SELECT 比更新表的语句更高的优先级。应该只对非常快且必须立即执行的查询使用此选项。在锁定表以进行读取时 SELECT HIGH_PRIORITY 查询将运行，即使有更新语句等待表释放。这只影响只使用表级锁定的存储引擎(如 MyISAM、MEMORY 和 MERGE)。

HIGH_PRIORITY 不能与作为 UNION 的一部分的 SELECT 语句一起使用。

● STRAIGHT_JOIN 强制优化器按照表在 FROM 子句中列出的顺序连接这些表。如果优化器以非最佳顺序联接表，则可以使用此选项加快查询速度。STRAIGHT_JOIN 也可以用在 table_references 列表中。

STRAIGHT_JOIN 不适用于优化器将其视为常量或系统表的任何表。这样的表生成一行，在查询执行的优化阶段读取，在查询执行继续之前，对其列的引用将替换为适当的列值。这些表将首先出现在 EXPLAIN 显示的查询计划中。此例外可能不适用于在外部联接的空补全侧使用的常量表或系统表(即，LEFT JOIN 的右侧表或 RIGHT JOIN 的左侧表)。

● SQL_BIG_RESULT 或 SQL_SMALL_RESULT 可与 GROUP BY 或 DISTINCT 一起使用，告诉优化器结果集有许多行或很小。对于 SQL_BIG_RESULT，如果创建了基于磁盘的临时表，MySQL 会直接使用它们，并且更喜欢使用具有 GROUP BY 元素上的键的临时表进行排序。对于 SQL_SMALL_RESULT，MySQL 使用内存中的临时表来存储结果表，而不是使用排序。这通常是不需要的。

● SQL_BUFFER_RESULT 强制将结果放入临时表中。这有助于 MySQL 尽早释放表锁，并在需要很长时间将结果集发送到客户端的情况下有所帮助。此修饰符只能用于顶级 SELECT 语句，不能用于子查询或后续 UNION 语句。

SQL_CALC_FOUND_ROWS 通知 MySQL 计算结果集中有多少行，而不考虑任何 LIMIT 子句。然后可以使用 SELECT FOUND_ROWS() 检索行数。

注意

从 MySQL 8.0.17 开始，SQL_CALC_FOUND_ROWS 查询修饰符和附带的 FOUND_ROWS() 函数已被弃用，并将在以后的 MySQL 版本中删除。

● 在 MySQL 8.0 之前的查询缓存中使用了 SQL_CACHE 和 SQL_NO_CACHE 修饰符。MySQL 8.0 中的查询缓存已被删除。SQL_CACHE 修饰符也被删除。SQL_NO_CACHE 已弃用，没有任何效果，会在将来的 MySQL 版本中删除。