MySQL SQL语句语法详解

需积分: 9 87 浏览量更新于2024-07-17 收藏 3.89MB PDF 举报

"Chapter 13 SQL Statement Syntax.pdf 是MySQL 5.7的官方文档，详细介绍了SQL语句的各种语法结构。文档包含了数据定义、数据操作、事务与锁定以及复制等核心概念。" 在本章节中，MySQL 5.7的SQL语句语法主要涵盖了以下几个方面： 1. **数据定义语句**： - 包含创建和更改数据库、事件、函数、实例、日志文件组、程序、服务器、表、TABLESPACE和视图的语法。同时，也有删除这些对象的语法，如DROP DATABASE、DROP TABLE等。此外，还有重命名表和截断表的特殊操作。 2. **数据操作语句**： - 涉及到CALL（调用存储过程）、DELETE（删除记录）、INSERT（插入记录）、SELECT（查询数据）、UPDATE（更新记录）等基本操作。还包括了处理程序（HANDLER）用于交互式地处理表中的行，以及LOAD DATA INFILE和LOAD XML用于导入数据。 3. **交易语句和锁定语句**： - 如START TRANSACTION、COMMIT、ROLLBACK用于管理事务。还讨论了不可回滚的语句、隐式提交的情况、保存点（SAVEPOINT）的使用，以及锁表和解锁表的语法。这部分内容对于并发控制和数据一致性至关重要。 4. **复制语句**： - 提供了控制主从服务器和组复制的SQL语句，这对于数据库的高可用性和分布式环境中的数据同步非常重要。 5. **准备SQL语句语法**： - 预编译SQL语句（PREPARE）可以提高执行效率，EXECUTE语句用于执行预编译的语句，而DEALLOCATE PREPARE用于释放预编译的语句资源。 6. **复合语句句法**： - 使用BEGIN...END结构来编写复杂的多条语句块，这在存储过程和触发器中常见。这些内容构成了MySQL数据库管理系统中SQL语句的基础，对于数据库管理员和开发人员来说是必备的知识。通过理解和掌握这些语法，能够有效地设计、管理和操作数据库，确保数据的安全性、完整性和高性能。

mysql> SHOW CREATE TABLE p\G

*************************** 1. row ***************************

Table: p

Create Table: CREATE TABLE `p` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cd` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LINEAR KEY (id)

PARTITIONS 32 */

1 row in set (0.00 sec)

mysql> ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32;

Query OK, 0 rows affected (5.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE p\G

*************************** 1. row ***************************

Table: p

Create Table: CREATE TABLE `p` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cd` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LINEAR KEY (id)

PARTITIONS 32 */

1 row in set (0.00 sec)

降级使用MySQL 5.5及更高版本中使用的默认键哈希创建的表以使MySQL 5.1服务器能够使用该表与此类似，但在本

例中，您应该使用ALGORITHM=1强制使用MySQL5.1键散列函数重建表的分区。建议您不要这样做，除非需要与

MySQL 5.1服务器兼容，因为改进了KEY默认情况下，MySQL5.5和更高版本中使用的散列函数为旧实现中发现的许

多问题提供了修复。

注

通过以下方法升级的表ALTER TABLE ... PARTITION BY ALGORITHM=2 [LINEAR] KEY ...MySQL 5.1服务器

无法再使用。(这样的表格需要降级ALTER TABLE ... PARTITION BY ALGORITHM=1 [LINEAR] KEY ...在

MySQL5.1服务器再次使用它之前。)

使用ALTER TABLE ... PARTITION BY语句必须遵循与使用CREATE TABLE ... PARTITION BY..这包括规范表可

能具有的唯一键(包括任何主键)与分区表达式中使用的列之间关系的规则，如第22.6.1节，“分区键、主键和唯一键”..

这个CREATE TABLE ... PARTITION BY指定分区数目的规则也适用于ALTER TABLE ... PARTITION BY.

这个partition_definition条款ALTER TABLE ADD PARTITION对象的相同名称的子句支持相同的选项。CREATE

TABLE声明。(见第13.1.18节，“创建表语法”，用于语法和描述。)假设您已经创建了分区表，如下所示：

CREATE TABLE t1 (

id INT,

year_col INT

)

PARTITION BY RANGE (year_col) (

PARTITION p0 VALUES LESS THAN (1991),

PARTITION p1 VALUES LESS THAN (1995),

PARTITION p2 VALUES LESS THAN (1999)

);

您可以添加一个新分区。p3来存储小于2002详情如下：

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

DROP PARTITION可用于丢弃一个或多个RANGE或LIST隔断。此语句不能与HASH或KEY分区；相反，使用COALESCE

PARTITION(见下文)。中指定的已删除分区中存储的任何数据。partition_names列表被丢弃。例如，给定表t1在

前面定义的分区中，可以删除名为p0和p1如下所示：

ALTER TABLE t1 DROP PARTITION p0, p1;

注

DROP PARTITION不使用NDB存储引擎。看见第22.3.1节，“范围分区和列表分区的管理”，和第21.1.7节，“已知NDB集

群的限制”.

ADD PARTITION和DROP PARTITION目前不支持IF [NOT] EXISTS.
DISCARD PARTITION ... TABLESPACE和IMPORT PARTITION ... TABLESPACE选项扩展可移动表空间特征到个
体InnoDB表分区。各InnoDB表分区有自己的表空间文件(.idb档案)。这个可移动表空间特性使表空间可以轻松地从
正在运行的MySQL服务器实例复制到另一个正在运行的实例，或者对同一个实例执行还原。这两个选项都采用一个或
多个逗号分隔分区名称的列表。例如：
ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE; 
ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE; 
跑步时DISCARD PARTITION ... TABLESPACE和IMPORT PARTITION ... TABLESPACE在子分区表上，允许分区
名称和子分区名称。当指定分区名称时，将包括该分区的子分区。
这个可移动表空间特性还支持复制或还原分区。InnoDB表(所有分区一次)。的附加信息。可移动表空间特征，见第
14.6.3.7节，“将表空间复制到另一个实例”..有关使用示例，请参见第14.6.3.7.1节，“可运输表空间示例”.
支持分区表的重命名。可以使用以下方法间接重命名各个分区ALTER TABLE ... REORGANIZE PARTITION但是，
此操作复制分区的数据。
若要从选定分区中删除行，请使用TRUNCATE PARTITION选择。此选项采用一个或多个分区名称的逗号分隔列表。例
如，考虑一下表t1在此定义如下：
CREATE TABLE t1 ( 
    id INT, 
    year_col INT 
) 
PARTITION BY RANGE (year_col) ( 
    PARTITION p0 VALUES LESS THAN (1991), 
    PARTITION p1 VALUES LESS THAN (1995), 
    PARTITION p2 VALUES LESS THAN (1999), 
    PARTITION p3 VALUES LESS THAN (2003), 
    PARTITION p4 VALUES LESS THAN (2007) 
); 
从分区中删除所有行p0，使用以下语句：
ALTER TABLE t1 TRUNCATE PARTITION p0; 
刚才显示的语句具有以下相同的效果DELETE声明：
DELETE FROM t1 WHERE year_col < 1991; 
当截断多个分区时，分区不必是连续的：这可以极大地简化对分区表的删除操作，否则就需要非常复杂的操作。
WHERE条件DELETE陈述。例如，此语句从分区中删除所有行。p1和p3:
ALTER TABLE t1 TRUNCATE PARTITION p1, p3; 
等价物DELETE如下所示：
DELETE FROM t1 WHERE 
    (year_col >= 1991 AND year_col < 1995) 
    OR 
    (year_col >= 2003 AND year_col < 2007); 
如果您使用ALL关键字代替分区名称列表，该语句作用于所有表分区。
TRUNCATE PARTITION只是删除行；它不改变表本身或其任何分区的定义。
若要验证行是否已被删除，请检查INFORMATION_SCHEMA.PARTITIONS表，使用类似于此的查询：
SELECT PARTITION_NAME, TABLE_ROWS 
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_NAME = 't1'; 
TRUNCATE PARTITION仅支持使用MyISAM, InnoDB，或MEMORY存储引擎。它也工作在BLACKHOLE表(但没有效果)。
不支持ARCHIVE桌子。
COALESCE PARTITION可与由HASH或KEY若要减少分区的数量，请执行以下操作：number..假设您已经创建了表t2
详情如下：
CREATE TABLE t2 ( 
    name VARCHAR (30), 

    started DATE 
) 
PARTITION BY HASH( YEAR(started) ) 
PARTITIONS 6; 
若要减少t2从6到4，使用以下语句：
ALTER TABLE t2 COALESCE PARTITION 2; 
最后一个文件中包含的数据number分区将合并到其余的分区中。在这种情况下，分区4和5将合并到前4个分区(编号
为0、1、2和3的分区)。
若要更改已分区表使用的某些分区(但不是所有分区)，可以使用REORGANIZE PARTITION..该语句可以通过以下几种
方式使用：
若要将一组分区合并为单个分区，请执行以下操作。这是通过在partition_names列出并提供单个定义
partition_definition.
将现有分区拆分为多个分区。通过将单个分区命名为partition_names并提供多个partition_definitions.
若要更改分区子集的范围，请执行以下操作：VALUES LESS THAN定义的分区子集的值列表。VALUES IN.
此语句也可以不使用partition_names INTO (partition_definitions)自动分区的表上的HASH分区强制重新分
配数据。(目前，只有NDB)表以这种方式自动分区。)这在NDB群集中很有用，在将新的NDB群集数据节点在线添加到
现有的NDB群集之后，希望将现有的NDB群集表数据重新分发到新的数据节点。在这种情况下，您应该使用
ALGORITHM=INPLACE选项；换句话说，如下所示：
ALTER TABLE table ALGORITHM=INPLACE, REORGANIZE PARTITION; 
不能与联机表重组同时执行其他DDL-也就是说，在ALTER TABLE ... ALGORITHM=INPLACE, REORGANIZE
PARTITION语句正在执行。有关联机添加NDB群集数据节点的详细信息，请参阅第21.5.15节，“在线添加NDB群集数
据节点”.
注
ALTER TABLE ... ALGORITHM=INPLACE, REORGANIZE PARTITION类创建的表不起作用。MAX_ROWS选项，
因为它使用常量。MAX_ROWS值中指定的CREATE TABLE语句来确定所需分区的数量，因此不会创建新的分区。
相反，您可以使用ALTER TABLE ... ALGORITHM=INPLACE, MAX_ROWS=rows若要增加此类表的最大行数，
请执行以下操作：ALTER TABLE ... ALGORITHM=INPLACE, REORGANIZE PARTITION不需要(如果执行会导
致错误)。价值rows必须大于为MAX_ROWS原版CREATE TABLE声明才能起作用。
雇用MAX_ROWS若要强制使用表分区的数目，请在NDB7.5.4及更高版本中使用；PARTITION_BALANCE相反(见设
置NDB_TABLE选项).
试图使用REORGANIZE PARTITION没有partition_names INTO (partition_definitions)选项在显式分区表上
导致错误。没有参数的重新组织分区只能在使用散列分区的自动分区表上使用。.
注
对于未显式命名的分区，mysql自动提供默认名称。p0, p1, p2等等。子分区也是如此。
的更详细信息和示例ALTER TABLE ... REORGANIZE PARTITION声明，见第22.3.1节，“范围分区和列表分区的管
理”.
若要将表分区或子分区与表交换，请使用ALTER TABLE ... EXCHANGE PARTITION语句-也就是说，将分区或子分
区中的任何现有行移动到非分区表，将非分区表中的任何现有行移动到表分区或子分区。
有关使用信息和示例，请参见第22.3.3节，“与表交换分区和子分区”.
有几种选项提供分区维护和修复功能，类似于由语句为非分区表实现的功能，如CHECK TABLE和REPAIR TABLE(也
支持分区表；有关更多信息，请参见第13.7.2款，“表维持费报表”)。这些包括ANALYZE PARTITION, CHECK
PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION，和REPAIR PARTITION..这些选项中的每一个都需要一
个partition_names子句，由一个或多个分区名称组成，用逗号分隔。分区必须已存在于要更改的表中。您还可以
使用ALL关键字代替partition_names在这种情况下，语句作用于所有表分区。有关更多信息和示例，请参见第
22.3.4款，“分区的维护”.
一些MySQL存储引擎，如InnoDB，不支持每个分区的优化。对于使用这样一个存储引擎的分区表，ALTER TABLE
... OPTIMIZE PARTITION导致重新生成和分析整个表，并发出适当的警告。(bug#11751825，Bug#42822)
若要解决此问题，请使用以下语句ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE
PARTITION相反。
这个ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION，和REPAIR PARTITION不允许对未分区的
表设置选项。
REMOVE PARTITIONING使您能够在不影响表或其数据的情况下删除表的分区。此选项可与其他选项相结合。ALTER
TABLE选项，如用于添加、删除或重命名列或索引的选项。

使用ENGINE选择与ALTER TABLE更改表使用的存储引擎，而不影响分区。

什么时候ALTER TABLE ... EXCHANGE PARTITION或ALTER TABLE ... TRUNCATE PARTITION是针对已分区表

运行的，该分区表使用MyISAM(或另一个使用表级锁定的存储引擎)，只有那些实际读取的分区被锁定。(这不适用于使

用行级锁定的存储工程的分区表，如InnoDB)看见第22.6.4节，“分区和锁定”.

这是有可能的ALTER TABLE语句包含PARTITION BY或REMOVE PARTITIONING子句中添加到其他更改规范中，但是

PARTITION BY或REMOVE PARTITIONING子句必须在任何其他规格之后最后指定。

这个ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, ANALYZE PARTITION,

CHECK PARTITION，和REPAIR PARTITION选项不能与单一的其他更改规范组合在一起。ALTER TABLE，因为所列

出的选项对单个分区起作用。有关更多信息，请参见第13.1.8.1节，“更改表分区操作”.

在给定的ALTER TABLE声明：PARTITION BY, ADD PARTITION, DROP PARTITION, TRUNCATE PARTITION,

EXCHANGE PARTITION, REORGANIZE PARTITION，或COALESCE PARTITION, ANALYZE PARTITION, CHECK

PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, REMOVE PARTITIONING.

例如，以下两条语句无效：

ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;

ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;

在第一种情况下，您可以分析分区。p1和p2表中t1同时使用单个语句和ANALYZE PARTITION选项列出要分析的两个

分区，如下所示：

ALTER TABLE t1 ANALYZE PARTITION p1, p2;

在第二种情况下，不可能执行ANALYZE和CHECK在同一表的不同分区上并发地进行操作。相反，您必须发出两个单独

的语句，如下所示：

ALTER TABLE t1 ANALYZE PARTITION p1;

ALTER TABLE t1 CHECK PARTITION p2;

REBUILD子分区当前不支持操作。这个REBUILD关键字明显不允许使用子分区，并导致ALTER TABLE如果使用错误，

则失败。

CHECK PARTITION 和 REPAIR PARTITION当要检查或修复的分区包含任何重复的键错误时，操作将失败。

有关这些语句的更多信息，请参见第22.3.4款，“分区的维护”.

13.1.8.2更改表和生成的列

ALTER TABLE生成的列允许的操作如下ADD, MODIFY，和CHANGE.

可以添加生成的列。

CREATE TABLE t1 (c1 INT);

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

可以修改生成列的数据类型和表达式。

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);

ALTER TABLE t1 MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;

生成的列可以重命名或删除，如果没有其他列引用它们。

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);

ALTER TABLE t1 CHANGE c2 c3 INT GENERATED ALWAYS AS (c1 + 1) STORED;

ALTER TABLE t1 DROP COLUMN c3;

不能将虚拟生成的列更改为存储的生成列，反之亦然。若要解决此问题，请删除该列，然后将其添加到新定义中。

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL);

ALTER TABLE t1 DROP COLUMN c2;

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

未生成的列可以更改为已存储的列，但不能更改为虚拟生成的列。

CREATE TABLE t1 (c1 INT, c2 INT);

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

存储但不是虚拟生成的列可以更改为非生成列。所存储的生成值将成为非生成列的值。
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED); 
ALTER TABLE t1 MODIFY COLUMN c2 INT;             
ADD COLUMN不是存储列的就地操作(不使用临时表完成)，因为表达式必须由服务器计算。对于存储的列，索引更改已
到位，而表达式更改未执行到位。对列注释的更改已完成。
对于非分区表，ADD COLUMN和DROP COLUMN是虚拟列的就地操作。但是，添加或删除虚拟列不能与其他ALTER
TABLE行动。
对于分区表，ADD COLUMN和DROP COLUMN不是针对虚拟列的就地操作。
InnoDB支持虚拟生成列上的辅助索引。在虚拟生成的列上添加或删除辅助索引是一种就地操作.有关更多信息，请参
见第13.1.18.9节，“次级索引和生成列”.
当VIRTUAL生成的列被添加到表中或修改后，不能确保由生成的列表达式计算的数据不会超出列的范围。这可能导致
返回不一致的数据和意外失败的语句。若要允许控制是否对此类列进行验证，ALTER TABLE支座WITHOUT
VALIDATION和WITH VALIDATION条款：
带着WITHOUT VALIDATION(如果没有指定缺省子句)，将执行就地操作(如果可能的话)，不检查数据完整性，语句完
成得更快。但是，如果值超出范围，以后从表中读取的数据可能会报告列的警告或错误。
带着WITH VALIDATION, ALTER TABLE复印桌子。如果出现超出范围的错误或任何其他错误，则语句将失败。因为要
执行表副本，所以语句需要更长的时间。
WITHOUT VALIDATION和WITH VALIDATION只允许在ADD COLUMN, CHANGE COLUMN，和MODIFY COLUMN行动。否
则，ER_WRONG_USAGE发生错误。
从MySQL 5.7.10开始，如果表达式计算导致截断或向函数提供不正确的输入，则ALTER TABLE语句以错误终止，
DDL操作被拒绝。
阿ALTER TABLE语句，该语句更改列的默认值。col_name还可能更改引用该列的生成列表达式的值。
DEFAULT(col_name)..因此，从MySQL 5.7.13开始，ALTER TABLE更改列定义的操作将导致表重新生成，如果生成
的列表达式使用DEFAULT().
13.1.8.3更改表示例
从桌子开始t1创建如下所示：
CREATE TABLE t1 (a INTEGER, b CHAR(10)); 
将表重命名为t1到t2:
ALTER TABLE t1 RENAME t2; 
更改列a从…INTEGER到TINYINT NOT NULL(保持名称不变)，并更改列b从…CHAR(10)到CHAR(20)以及将其重命名
为b到c:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); 
若要添加新的TIMESTAMP列名d:
ALTER TABLE t2 ADD d TIMESTAMP; 
在列上添加索引d和一个UNIQUE列索引a:
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a); 
删除列c:
ALTER TABLE t2 DROP COLUMN c; 
若要添加新的AUTO_INCREMENT整数列c:
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  ADD PRIMARY KEY (c); 
我们索引c(作为PRIMARY KEY)因为AUTO_INCREMENT必须对列进行索引，并且我们声明c如NOT NULL因为主键列不
能是NULL.
为NDB表，也可以更改表或列使用的存储类型。例如，考虑NDB创建的表如下所示：