MySQL创建数据表和MySQL数据类型

最新推荐文章于 2024-09-04 10:55:05 发布

aweiname2008

最新推荐文章于 2024-09-04 10:55:05 发布

阅读量154

点赞数

分类专栏： MySQL

原文链接：https://cloud.tencent.com/developer/article/1406420

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

https://cloud.tencent.com/developer/article/1406420

源自MySQL 5.7 官方手册：13.2.9 SELECT Syntax

SELECT的语法如下：

复制代码
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr …]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], … [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE ‘file_name’
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE ‘file_name’
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
复制代码
一、SELECT解析

SELECT用于从一个或者多个表中取回数据行，也可以包括UNION语句和子查询。UNION语句参考后续文章，而子查询在手册13.2.10节。

SELECT语句最常用的子句有这些：

每个select_expr指示要检索的列。所以必须至少有一个select_expr。
table_references指示要从中取回行数据的一个或多个表。其语法在第13.2.9.2节“JOIN语法”中描述。
SELECT支持使用PARTITION显式分区选择，其中包含table_reference中表的名称后面的分区或子分区（或两者）列表。关于分区的更多信息在Section 22.6.4, “Partitioning and Locking”
如果存在WHERE子句，其中的条件对行数据进行筛选。where_condition是一个表达式，对于要选择的每一行，其计算结果为true。如果没有WHERE子句，该语句将选择所有行。在WHERE表达式中，您可以使用除聚合函数之外的任何MySQL支持的函数和运算符。See Section 9.5, “Expressions”, and Chapter 12, Functions and Operators.
　　SELECT也可用于检索计算的行而不引用任何表。

mysql> SELECT 1 + 1;
-> 2
　　在没有引用表的情况下，允许将DUAL指定为虚拟表名：

mysql> SELECT 1 + 1 FROM DUAL;
-> 2
　　DUAL的存在只是为了方便。MySQL可能会忽略这些子句。如果没有引用表，MySQL不需要FROM DUAL。

通常，使用的子句必须完全按照语法描述中展示的顺序给出。

例如，HAVING子句必须位于任何GROUP BY子句之后和任何ORDER BY子句之前。例外情况是INTO子句可以如语法描述中所示出现，也可以紧跟在select_expr列表之后出现。SELECT…INTO后续文章会讲。

select_expr项列表包括了要取回的列，该项可以指定一个字段、一个表达式或者使用*号。

SELECT列表中只包含一个号，即非限定，意味着从所有表中取回所有的列。
而tb.，限定，表示取回一个指定表中的所有列。
在SELECT列表中使用非限定的*，可能会产生解析错误。要注意使用场合，

二、关于SELECT子句的一些附加知识：

2.1

可以使用AS alias_name为select_expr指定别名。别名用作表达式的列名，可用于GROUP BY，ORDER BY或HAVING子句。

AS关键字是可选项，在指定列的别名时养成使用AS是一种好习惯。

MySQL不允许在WHERE子句中引用列的别名，因为当WHERE子句执行时，列值可能还尚未确定。See Section B.4.4.4, “Problems with Column Aliases”.

2.2

FROM后的table_references指示参与查询的一个或者多个表。如果列出了多个表，就会执行JOIN操作。而对于每一个指定表，都可以为其定义别名。

tbl_name [[AS] alias] [index_hint]
　　使用index_hint，会为优化器提供有关如何在查询处理期间选择索引的信息。关于它，see Section 8.9.4, “Index Hints”。

当然，也可以使用SET max_seeks_for_key = value作为替代方法，强制MySQL更优先使用键扫描而不是表扫描。See Section 5.1.7, “Server System Variables”

2.3

不使用AS也能为表定义别名，直接空格后填写即可。

2.4

SELECT后被选择的列，可以在ORDER IN和GROUP BY中，通过列名、列别名或者代表列位置的整数（从1开始）来引用：

复制代码
SELECT college, region, seed FROM tournament
ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament
ORDER BY r, s;

SELECT college, region, seed FROM tournament
ORDER BY 2, 3;
复制代码
　　如果ORDER BY出现在子查询中，并且也应用于外部查询，则最外层的ORDER BY优先。

例如，以下语句的结果按降序排序，而不是按升序排序：

(SELECT … ORDER BY a) ORDER BY a DESC;
　　不推荐使用代表列位置的数字的方法，因为这已经从标准SQL中删除。

2.5

如果使用了GROUP BY，被分组的列会自动按升序排列（就好像有一个ORDER BY语句后面跟了同样的列）。如果要避免GROUP BY因为自动排序生成的开销，添加ORDER BY NULL可以解决：

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
　　虽然在GROUP BY语句上也有一些对字段指定排序的用法，但是他们现在都已经被抛弃。正确的做法是，使用ORDER BY子句来进行排序。

2.6

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

2.7

MySQL扩展了GROUP BY的用法，它允许在Select列表中出现没有在GROUP BY中进行分组的字段。这点在前面的聚合函数的文章中已经讲得很清楚。

2.8

GROUP BY子句允许添加WITH ROLLUP修饰符

2.9

Having子句一般应用在最后，恰好在结果集被返回给MySQL客户端前，且没有进行优化。（而LIMIT应用在HAVING后）

SQL标准要求：HAVING必须引用在GROUP BY列表中或者聚合函数使用的列。然而，MySQL对此进行了扩展，它允许HAVING引用Select子句列表中的列，还有外部子查询的列。

如果HAVING引用的列具有歧义，会有警告产生。下面的语句中，col2具有歧义：

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
　　这里没报错只有警告是为什么呢？因为MySQLS虽然对标准SQL进行了扩展，但是在这种情况下，标准SQL的选择具有优先权。即MySQL把HAVING中的col2优先指向GROUP BY中的col2。

2.11

切记不要在该使用WHERE的地方使用HAVING。HAVING是和GROUP BY搭配的。

2.12

HAVING子句可以引用聚合函数，而WHERE不能。

SELECT user, MAX(salary) FROM users
GROUP BY user HAVING MAX(salary) > 10;
　　上面的查询在老版本的MySQL可能无效。

2.13

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

SELECT 12 AS a, a FROM t GROUP BY a;
　　在这个语句中，两个列都名为a（实际应该是不同）。（着应该是被允许的）

那么为了不引起歧义以对正确的列进行分组，切记对select_expr使用不同的别名。

2.14

对于ORDER BY子句中的非限定列或别名引用，MySQL是这样进行解析的：先搜索SELECT子句列表中的select_expr值，然后在FROM后的表中的列。

而对于GROUP BY和HAVING子句中的非限定列或别名，MySQL先搜索FROM子句，再搜索SELECT子句。

2.15

LIMIT子句可用于约束SELECT语句返回的行数。

LIMIT可以有一个或者两个参数，都必须为非负整数。

但是也有例外情况：

在预编译的语句中，LIMIT参数可以使用占位符标记——“?” 进行指定。
在存储的程序（存储过程？）中，可以使用整数值例程参数（integer-valued routine parameters）或局部变量指定LIMIT参数。
　　若LIMIT有两个参数，第一个指定相对于第一行的偏移量，第二个参数指定应该返回的行数。第一行自己的偏移量是0，而不是1：

/取回结果集中的6~15行/
SELECT * FROM tbl LIMIT 5,10;
　　那如果要取回一个设定某个偏移量之后的所有行，可以为第二参数设定一个非常大的常量。以下查询取回从第96行起的所有数据：

SELECT * FROM tbl LIMIT 95,18446744073709551615;
　　若LIMIT只有一个参数，则参数指定应该取回的行数，偏移量默认为0，即从第一行起。

对于预编译SQL语句，可以使用占位符：

复制代码
/取回第一行数据/
SET @a=1;
PREPARE STMT FROM ‘SELECT * FROM tbl LIMIT ?’;
EXECUTE STMT USING @a;

/取回第2~6行数据/
SET @skip=1;
SET @numrows=5;
PREPARE STMT FROM ‘SELECT * FROM tbl LIMIT ?, ?’;
EXECUTE STMT USING @skip, @numrows;
复制代码
　　为了与PostgreSQL兼容，MySQL还支持LIMIT row_count OFFSET offset 的语法。

如果子查询中有LIMIT，而外部的查询同样有LIMIT，此时最外层查询的LIMIT优先。例如，以下语句返回的行应该是2条，而不是1条：

(SELECT … LIMIT 1) LIMIT 2;

2.16

PROCEDURE子句命名了一个对结果集进行处理的存储过程。Section 8.4.2.4, “Using PROCEDURE ANALYSE” 这一节，描述了ANALYSE关键字的使用。一个存储过程，可用于获取有助于减少表大小的最佳列数据类型的建议。（点进去才发现从MySQL 5.7.18开始，PROCEDURE ANALYZE()已被弃用，并在MySQL 8.0中被删除。）

2.17

SELECT…INTO可以让查询结果写入到文件或者保存到变量中，后续的文章会讲这个。

2.18

如果使用在一个使用了页级锁或者行级锁的存储引擎中使用FOR UPDATE，被某个查询所检查的行将会处于“写锁定”中，直到当前事务结束。使用LOCK IN SHARE MODE设置共享锁，允许其他事务读取已检查的行，但不允许更新或删除它们。See Section 14.7.2.4, “Locking Reads”.

此外，你不能在下面的语句中将FOR UPDATE作为SELECT查询的一部分:

CREATE TABLE new_table SELECT … FROM old_table …
　　如果尝试这么做，该语句会被拒绝，并报错——Can’t update table ‘old_table’ while ‘new_table’ is being created。在MySQL 5.5以及更早的版本中有所不同。（This is a change in behavior from MySQL 5.5 and earlier, which permitted CREATE TABLE … SELECT statements to make changes in tables other than the table being created.）

三、SELECT关键字的修饰符

紧跟SELECT关键字，你可以使用一些列修饰符，来影响语句的操作。

HIGH_PRIORITY，STRAIGHT_JOIN和以SQL_开头的修饰符是标准SQL的MySQL扩展。

3.1

ALL和DISTINCT修饰符指定是否对结果集中的行（应该不是某个列）去重。

ALL是默认修饰符，即满足要求的所有行都要被取回来。

DISTINCT删除重复的行。

3.2

HIGH_PRIORITY修饰符会让SELECT语句比更改表（这里英文用得是update，不晓得是更新还是更改）的语句有更高的优先级。

这个修饰符只应该在非常快且能被立即执行的查询当中使用。

在表被锁定以进行读取时发出的SELECT HIGH_PRIORITY查询也会运行，即使有一个更新语句正在等待表被释放。这仅影响仅使用表级锁定的存储引擎（例如MyISAM，MEMORY和MERGE）。

HIGH_PRIORITY不能与属于UNION的SELECT语句一起使用。

3.3

STRAIGHT_JOIN强制优化器按照FROM子句中列出的顺序连接表。如果优化程序以非最佳顺序连接表，则可以使用此方法加速查询.STRAIGHT_JOIN也可以在table_references列表中使用。JOIN语法中会讲到。

STRAIGHT_JOIN不适用于被优化器视为const或system的任何表。这种表只产生一行数据，在查询执行的优化阶段读取，并且在查询执行进行之前用适当的列值替换对其列的引用。这些表将首先出现在EXPLAIN显示的查询计划中。See Section 8.8.1, “Optimizing Queries with EXPLAIN”.

This exception may not apply to const or system tables that are used on the NULL-complemented side of an outer join (that is, the right-side table of a LEFT JOIN or the left-side table of a RIGHT JOIN.

3.4

SQL_BIG_RESULT或SQL_SMALL_RESULT可以与GROUP BY或DISTINCT一起使用，分别告诉优化器结果集有很多行或者很小。

对于SQL_BIG_RESULT，MySQL直接使用基于磁盘的临时表（如果已创建），并且更偏向使用在GROUP BY中元素的key的临时表来进行排序。

对于SQL_SMALL_RESULT，MySQL使用内存中的临时表来存储生成的表而不是使用排序。通常不需要这样做。

3.5

SQL_BUFFER_RESULT强制将结果放入临时表中.

这有助于MySQL尽早释放表锁，在需要很长时间将结果集发送到客户端的情况下，这样也更好。

此修饰符只能用于顶级SELECT语句，不能用于子查询或UNION之后。

3.6

SQL_CALC_FOUND_ROWS告诉MySQL计算结果集中将有多少行，忽略任何LIMIT子句。

然后可以使用SELECT FOUND_ROWS()检索行数。See Section 12.15, “Information Functions”.

这个参数就是为了方便统计在不使用LIMIT时的结果集。