MYSQL语句语法.docx-道客多多_道客多多docduoduo.com

资源描述

1、13.1.1. ALTER DATABASE 语法ALTER DATABASE | SCHEMA db_namealter_specification , alter_specification .alter_specification:DEFAULT CHARACTER SET charset_name| DEFAULT COLLATE collation_nameALTER DATABASE 用于更改数据库的全局特性。这些特性储存在数据库目录中的 db.opt文件中。要使用 ALTER DATABASE，您需要获得数据库 ALTER 权限。CHARACTER SET 子句用于更改默认的数据

2、库字符集。COLLATE 子句用于更改默认的数据库整序。在第 10 章：字符集支持中对字符集和整序名称进行了讨论。数据库名称可以忽略，此时，语句对应于默认数据库。也可以使用 ALTER SCHEMA。13.1.2. ALTER TABLE 语法ALTER IGNORE TABLE tbl_namealter_specification , alter_specification .alter_specification:ADD COLUMN column_definition FIRST | AFTER col_name | ADD COLUMN (column_definition,.

3、)| ADD INDEX index_name index_type (index_col_name,.)| ADD CONSTRAINT symbolPRIMARY KEY index_type (index_col_name,.)| ADD CONSTRAINT symbolUNIQUE index_name index_type (index_col_name,.)| ADD FULLTEXT|SPATIAL index_name (index_col_name,.)| ADD CONSTRAINT symbolFOREIGN KEY index_name (index_col_name

7、ABLE 用于更改原有表的结构。例如，您可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。您还可以更改表的评注和表的类型。允许进行的变更中，许多子句的语法与 CREATE TABLE 中的子句的语法相近。其中包括table_options 修改，选项有 ENGINE, AUTO_INCREMENT 和 AVG_ROW_LENGTH 等。请见 13.1.5 节，“CREATE TABLE 语法”。存储引擎不支持有些操作，如果进行这些操作，会出现警告。使用 SHOW WARNINGS 可以显示出这些警告。请参见 13.5.4.22 节，“SHOW WARNINGS 语法”。如果

8、您使用 ALTER TABLE 更改列规约，但是 DESCRIBE tbl_name 提示您列规约并没有改变，则可能是因为 MySQL 忽略了您所做的更改。忽略更改的原因见 13.1.5.1 节，“沉寂的列规格变更”。例如，如果您试图把 VARCHAR 列更改为 CHAR 列，此时，如果表包含其它长度可变的列，则 MySQL 仍会使用 VARCHAR。ALTER TABLE 运行时会对原表进行临时复制，在副本上进行更改，然后删除原表，再对新表进行重命名。在执行 ALTER TABLE 时，其它用户可以阅读原表，但是对表的更新和修改的操作将被延迟，直到新表生成为止。新表生成后，这些更新和修改信息

9、会自动转移到新表上。注意，如果您在执行 ALTER TABLE 时使用除了 RENAME 以外的选项，则 MySQL 会创建一个临时表。即使数据并不需要进行复制（例如当您更改列的名称时），MySQL 也会这么操作。对于 MyISAM 表，您可以通过把 myisam_sort_buffer_size 系统变量设置到一个较高的值，来加快重新创建索引（该操作是变更过程中速度最慢的一部分）的速度。要使用 ALTER TABLE，您需要获得表的 ALTER, INSERT 和 CREATE 权限。 IGNORE 是 MySQL 相对于标准 SQL 的扩展。如果在新表中有重复关键字，或者当STRICT

10、模式启动后出现警告，则使用 IGNORE 控制 ALTER TABLE 的运行。如果没有指定 IGNORE，当重复关键字错误发生时，复制操作被放弃，返回前一步骤。如果指定了IGNORE，则对于有重复关键字的行，只使用第一行，其它有冲突的行被删除。并且，对错误值进行修正，使之尽量接近正确值。您可以在一个 ALTER TABLE 语句里写入多个 ADD, ALTER, DROP 和 CHANGE子句，中间用逗号分开。这是 MySQL 相对于标准 SQL 的扩展。在标准 SQL 中，每个ALTER TABLE 语句中每个子句只允许使用一次。例如，在一个语句中取消多个列： mysql ALTER T

11、ABLE t2 DROP COLUMN c, DROP COLUMN d; CHANGE col_name, DROP col_name 和 DROP INDEX 是 MySQL 相对于标准SQL 的扩展。 MODIFY 是 Oracle 对 ALTER TABLE 的扩展。 COLUMN 只是自选项目，可以忽略。如果您使用 ALTER TABLE tbl_name RENAME TO new_tbl_name 并且没有其它选项，则 MySQL 只对与 tabletbl_name 相对应的文件进行重命名。不需要创建一个临时表。（您也可以使用 RENAME TABLE 语句对表进行重命名。请参

12、见 13.1.9 节，“RENAME TABLE 语法”。） column_definition 子句使用与 CREATE TABLE 中的 ADD 和 CHANGE 子句相同的语法。注意，此语法包括列名称，而不只是列类型。请参见 13.1.5 节，“CREATE TABLE语法”。您可以使用 CHANGE old_col_name column_definition 子句对列进行重命名。重命名时，需给定旧的和新的列名称和列当前的类型。例如：要把一个 INTEGER 列的名称从a 变更到 b，您需要如下操作： mysql ALTER TABLE t1 CHANGE a b INTEGER;如

13、果您想要更改列的类型而不是名称， CHANGE 语法仍然要求旧的和新的列名称，即使旧的和新的列名称是一样的。例如：mysql ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;您也可以使用 MODIFY 来改变列的类型，此时不需要重命名：mysql ALTER TABLE t1 MODIFY b BIGINT NOT NULL; 如果您使用 CHANGE 或 MODITY 缩短列长时，列中存在有索引，并且缩短后的列长小于索引长度，则 MySQL 会自动缩短索引的长度。当您使用 CHANGE 或 MODIFY 更改列的类型时，MySQL 会尽量把原有的列值转化

14、为新的类型。您可以使用 FIRST 或 AFTER col_name 在一个表行中的某个特定位置添加列。默认把列添加到最后。您也可以在 CHANGE 或 MODIFY 语句中使用 FIRST 和 AFTER。 AFTER COLUMN 用于指定列的新默认值，或删除旧的默认值。如果旧的默认值被删除同时列值为 NULL，则新的默认值为 NULL。如果列值不能为 NULL，MySQL 会指定一个默认值，请参见 13.1.5 节，“CREATE TABLE 语法”。 DROP INDEX 用于取消索引。这是 MySQL 相对于标准 SQL 的扩展。请参见13.1.7 节，“DROP INDEX 语法

15、 ”。如果列从表中被取消了，则这些列也从相应的索引中被取消。如果组成一个索引的所有列均被取消，则该索引也被取消。如果一个表只包含一列，则此列不能被取消。如果您想要取消表，应使用 DROP TABLE。 DROP PRIMAY DEY 用于取消主索引。注释：在 MySQL 较早的版本中，如果没有主索引，则 DROP PRIMARY KEY 会取消表中的第一个 UNIQUE 索引。在 MySQL 5.1 中不会出现这种情况。如果在 MySQL 5.1 中对没有主键的表使用 DROP PRIMARY KEY，则会出现错误信息。如果您向表中添加 UNIQUE KEY 或 PRIMARY KEY，则

16、 UNIQUE KEY 或 PRIMARY KEY会被储存在非唯一索引之前，这样 MySQL 就可以尽早地检查出重复关键字。 ORDER BY 用于在创建新表时，让各行按一定的顺序排列。注意，在插入和删除后，表不会仍保持此顺序。当您知道多数情况下您会按照特定的顺序查询各行时，可以使用这个选项；在对表进行了大的改动后，通过使用此选项，您可以提高查询效率。在有些情况下，如果表按列排序，对于 MySQL 来说，排序可能会更简单。如果您对一个 MyISAM 表使用 ALTER TABLE，则所有非唯一索引会被创建到一个单独的批里（和 REPAIR TABLE 相同）。当您有许多索引时，这样做可以使

17、ALTER TABLE 的速度更快。这项功能可以明确激活。ALTER TABLE.DISABLE KEYS 让 MySQL 停止更新 MyISAM表中的非唯一索引。然后使用 ALTER TABLE . ENABLE KEYS 重新创建丢失的索引。进行此操作时，MySQL 采用一种特殊的算法，比一个接一个地插入关键字要快很多。因此，在进行成批插入操作前先使关键字禁用可以大大地加快速度。使用 ALTER TABLE . DISABLE KEYS 除了需要获得以前提到的权限以外，还需要获得 INDEX 权限。 Innodb 存储引擎支持 FOREIGN KEY 和 REFERENCES 子句。Inn

18、odb 存储引擎执行 ADD CONSTRAINT symbol FOREIGN KEY (.) REFERENCES . (.)。请参见15.2.6.4 节，“FOREIGN KEY 约束” 。对于其它存储引擎，这些子句会被分析，但是会被忽略。对于所有的存储引擎，CHECK 子句会被分析，但是会被忽略。请参见 13.1.5 节，“CREATE TABLE 语法”。接受这些子句但又忽略子句的原因是为了提高兼容性，以便更容易地从其它 SQL 服务器中导入代码，并运行应用程序，创建带参考数据的表。请参见1.8.5 节，“MySQL 与标准 SQL 的差别” 。 InnoDB 支持使用 ALTER

19、TABLE 来取消外键： ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;要了解更多信息，请参见 15.2.6.4 节，“FOREIGN KEY 约束” 。 ALTER TABLE 忽略 DATA DIRECTORY 和 INDEX DIRECTORY 表选项。如果您想要把表默认的字符集和所有字符列（CHAR, VARCHAR, TEXT）改为新的字符集，应使用如下语句： ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;警告：前面的操作转换了字符集之间的列类型。如果

20、您有一列使用一种字符集（如latin1），但是存储的值实际上使用了其它的字符集（如 utf8），这种情况不是您想要的。此时，您必须对这样的列进行以下操作。ALTER TABLE t1 CHANGE c1 c1 BLOB;ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;这种方法能够实现此功能的原因是，当您转换到 BLOB 列或从 BLOB 列转换过来时，并没有发生转换。如果您指定 CONVERT TO CHARACTER SET 为二进制，则 TEXT 列被转换到相应的二进制字符串类型（BINARY, VARBINARY, BLOB）。这意

21、味着这些列将不再有字符集，接下来的 CONVERT TO 操作也将不适用于这些列。要仅仅改变一个表的默认字符集，应使用此语句：ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;词语 DEFAULT 为自选项。如果您在向表中添加一个新列时（例如，使用 ALTER TABLE.ADD column）没有指定字符集，则此时使用的字符集为默认字符集。警告：ALTER TABLE.DEFAULT CHARACTER SET 和 ALTER TABLE.CHARACTER SET 是等价的，只用于更改默认的表字符集。如果 InnoDB 表在创

22、建时，使用了.ibd 文件中的自己的表空间，则这样的文件可以被删除和导入。使用此语句删除.ibd 文件： ALTER TABLE tbl_name DISCARD TABLESPACE;此语句用于删除当前的.ibd 文件，所以应首先确认您有一个备份。如果在表空间被删除后尝试打开表格，则会出现错误。要把备份的.ibd 文件还原到表中，需把此文件复制到数据库目录中，然后书写此语句：ALTER TABLE tbl_name IMPORT TABLESPACE;见 15.2.6.6 节，“使用按表的表空间 ”。使用 mysql_info() C API 函数，您可以了解有多少记录已被复制，以及（当使

23、用IGNORE 时）有多少记录由于重复关键字的原因已被删除。请参见 25.2.3.34 节，“mysql_info()”。 ALTER TABLE 也可以用于对带分区的表进行重新分区，功能包括添加、取消、合并和拆分各分区，还可以用于进行分区维护。对带分区的表使用 partition_options 子句和 ALTER TABLE 可以对表进行重新分区，使用时依据 partition_options 定义的分区方法。本子句以 PARTITION BY 为开头，然后使用与用于 CREATE TABLE 的 partition_options 子句一样的语法和规则（要了解详细信息，请参见 13.1.

24、5 节，“CREATE TABLE 语法”）。注释：MySQL 5.1 服务器目前接受此语法，但是不实际执行；等 MySQL 5.1 开发出来后，将执行此语法。用于 ALTER TABLE ADD PARTITION 的 partition_definition 子句支持用于 CREATE TABLE 语句的 partition_definition 子句的同样名称的选项。（要了解语法和介绍，请参见13.1.5 节，“CREATE TABLE 语法”。）例如，假设您有一个按照以下方式创建的带分区的表：CREATE TABLE t1 (id INT,year_col INT)PARTITION

25、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 p3 VALUES LESS THAN (2002);注释：您不能使用 ALTER TABLE 向一个没有进行分区的表添加分区。DROP PARTITION 用于取消一个或多个 RANGE

26、或 LIST 分区。此命令不能用于 HASH或 KEY 分区；用于这两个分区时，应使用 COALESCE PARTITION（见后）。如果被取消的分区其名称列于 partition_names 清单中，则储存在此分区中的数据也被取消。例如，如果以前已定义的表 t1，您可以采用如下方法取消名称为 p0 和 p1 的分区：ALTER TABLE DROP PARTITION p0, p1;ADD PARTITION 和 DROP PARTITION 目前不支持 IF NOT EXISTS。也不可能对一个分区或一个已分区的表进行重命名。如果您希望对一个分区进行重命名，您必须取消分区，再重新建立；如果

27、您希望对一个已分区的表进行重新命名，您必须取消所有分区，然后对表进行重命名，再添加被取消的分区。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;包含

28、在最后一个 number 分区中的数据将被合并到其余的分区中。在此情况下，分区 4 和分区 5 将被合并到前 4 个分区中（编号为 0、1、2 和 3 的分区）。如果要更改部分分区，但不更改所有的分区，您可以使用 REORGANIZE PARTITION。这个命令有多种使用方法：o 把多个分区合并为一个分区。通过把多个分区的名称列入 partition_names 清单，并为 partition_definition 提供一个单一的定义，可以实现这个功能。o 把一个原有的分区拆分为多个分区。通过为 partition_names 命名一个分区，并提供多个 partition_definitio

29、ns，可以实现这个功能。o 更改使用 VALUES LESS THAN 定义的分区子集的范围或更改使用 VALUES IN 定义的分区子集的值清单。注释：对于没有明确命名的分区，MySQL 会自动提供默认名称 p0, p1, p2 等。要了解有关 ALTER TALBE.REORANIZE PARTITION 命令的详细信息，请参见 18.3 节，“分区管理” 。多个附加子句用于提供分区维护和修补功能。这些功能与用于非分区表的功能类似。这些功能由 CHECK TABLE 和 REPAIR TABLE 等命令（这些命令不支持用于分区表）执行。这些子句包括 ANALYZE PARTITION,

30、CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION 和 REPAIR PARTITION.每个选项均为一个partition_names 子句，包括一个或多个分区名称。需要更改的表中必须已存在这些分区。多个分区名称用逗号分隔。要了解更多信息，或要了解举例说明，请参见 18.3.3 节，“分区维护”。以下例子展示了 ALTER TABLE 的使用。首先展示表 t1。表 t1 采用如下方法创建：mysql CREATE TABLE t1 (a INTEGER,b CHAR(10);把表 t1 重新命名为 t2：mysql ALTER TAB

31、LE t1 RENAME t2;把列 a 从 INTERGER 更改为 TINYINT NOT NULL（名称保持不变），并把列 b 从CHAR(10)更改为 CHAR(20)，同时把列 b 重新命名为列 c：mysql ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);添加一个新的 TIMESTAMP 列，名称为 d：mysql ALTER TABLE t2 ADD d TIMESTAMP;在列 d 和列 a 中添加索引：mysql ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a)

32、;删除列 c：mysql ALTER TABLE t2 DROP COLUMN c;添加一个新的 AUTO_INCREMENT 整数列，名称为 c：mysql ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,- ADD PRIMARY KEY (c);注意我们为 c 编制了索引（作为 PRIMARY KEY），因为 AUTO_INCREMENT 列必须编制索引。同时我们定义 c 为 NOT NULL，因为主键列不能为 NULL。当您添加一个 AUTO_INCREMENT 列时，列值被自动地按序号填入。对于 MyISAM 表，您

33、可以在 ALTER TABLE 之前执行 SET INSERT_ID=value 来设置第一个序号，也可以使用 AUTO_INCREMENT=value 表选项来设置。请参见 13.5.3 节，“SET 语法”。如果值大于 AUTO_INCREMENT 列中的最大值，则您可以使用用于 InnoDB 表的 ALTER TALBE.AUTO_INCREMENT=value 表选项，来为新行设置序号。如果值小于列中当前的最大值，不会出现错误信息，当前的序列值也不改变。使用 MyISAM 表时，如果您不更改 AUTO_INCREMENT 列，则序列号不受影响。如果您取消一个 AUTO_INCREMEN

34、T 列，然后添加另一个 AUTO_INCREMENT 列，则序号重新排列，从 1 开始。见 A.7.1 节，“与 ALTER TABLE 有关的问题” 。13.1.3. CREATE DATABASE 语法CREATE DATABASE | SCHEMA IF NOT EXISTS db_namecreate_specification , create_specification .create_specification:DEFAULT CHARACTER SET charset_name| DEFAULT COLLATE collation_nameCREATE DATABASE 用于创

35、建数据库，并进行命名。如果要使用 CREATE DATABASE，您需要获得数据库 CREATE 权限。有关合法数据库名称的规定列于 9.2 节，“ 数据库、表、索引、列和别名 ”。如果存在数据库，并且您没有指定 IF NOT EXISTS，则会出现错误。create_specification 选项用于指定数据库的特性。数据库特性储存在数据库目录中的db.opt 文件中。CHARACTER SET 子句用于指定默认的数据库字符集。 COLLATE 子句用于指定默认的数据库整序。字符集和整序名称在第 10 章：字符集支持中讨论。有些目录包含文件，这些文件与数据库中的表对应。MySQL

36、中的数据库的执行方法与这些目录的执行方法相同。因为当数据库刚刚被创建时，在数据库中没有表，所以 CREATE DATABASE 只创建一个目录。这个目录位于 MySQL 数据目录和 db.opt 文件之下。如果您手动在数据目录之下创建一个目录（例如，使用 mkdir），则服务器会认为这是一个数据库目录，并在 SHOW DATABASES 的输出中显示出来。也可以使用 CREATE SCHEMA。您还可以使用 mysqladmin 程序创建数据库。请参见 8.5 节，“mysqladmin：用于管理MySQL 服务器的客户端” 。13.1.4. CREATE INDEX 语法CREATE UNI

37、QUE|FULLTEXT|SPATIAL INDEX index_nameUSING index_typeON tbl_name (index_col_name,.)index_col_name:col_name (length) ASC | DESCCREATE INDEX 被映射到一个 ALTER TABLE 语句上，用于创建索引。请参见 13.1.2 节，“ALTER TABLE 语法” 。通常，当使用 CREATE TABLE 创建表时，也同时在表中创建了所有的索引。请参见13.1.5 节，“CREATE TABLE 语法”。CREATE INDEX 允许您向已有的表中添加索引。格式为

38、（col1, col2,.）的一个列清单创建出一个多列索引。通过串接给定列中的值，确定索引值的格式。对于 CHAR 和 VARCHAR 列，只用一列的一部分就可创建索引。创建索引时，使用col_name(length)语法，对前缀编制索引。前缀包括每列值的前 length 个字符。BLOB 和TEXT 列也可以编制索引，但是必须给出前缀长度。此处展示的语句用于创建一个索引，索引使用列名称的前 10 个字符。CREATE INDEX part_of_name ON customer (name(10);因为多数名称的前 10 个字符通常不同，所以此索引不会比使用列的全名创建的索引速度慢很多。另外

39、，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高 INSERT 操作的速度。前缀最长为 255 字节。对于 MyISAM 和 InnoDB 表，前缀最长为 1000 字节。注意前缀的限长以字节计，而 CREATE INDEX 语句中的前缀长度指的是字符的数目。对于使用多字节字符集的列，在指定列的前缀长度时，要考虑这一点。在 MySQL 5.1 中：只有当您正在使用 MyISAM, InnoDB 或 BDB 表类型时，您可以向有 NULL 值的列中添加索引。只有当您正在使用 MyISAM, BDB 或 InnoDB 表类型时，您可以向 BLOB 或 TEX

40、T列中添加索引。一个 index_col_name 规约可以以 ASC 或 DESC 为结尾。这些关键词将来可以扩展，用于指定递增或递减索引值存储。目前，这些关键词被分析，但是被忽略；索引值均以递增顺序存储。部分储存引擎允许在创建索引时指定索引类型。index_type 指定语句的语法是USING type_name。不同的储存引擎所支持的 type_name 值已显示在下表中。如果列有多个索引类型，当没有指定 index_type 时，第一个类型是默认值。存储引擎允许的索引类型MyISAM BTREEInnoDB BTREEMEMORY/HEAP HASH, BTREE示例：CREATE

41、TABLE lookup (id INT) ENGINE = MEMORY;CREATE INDEX id_index USING BTREE ON lookup (id);TYPE type_name 可以作为 USING type_name 的同义词，用于指定索引类型。但是，USING 是首选的格式。另外，在索引规约语法中，位于索引类型前面的索引名称不能使用TYPE。这是因为，与 USING 不同，TYPE 不是保留词，因此会被认为是一个索引名称。如果您指定的索引类型在给定的储存引擎中不合法，但是有其它的索引类型适合引擎使用，并且不会影响查询功能，则引擎应使用此类型。要了解更多有关 MyS

42、QL 如何使用索引的信息，请参见 7.4.5 节，“MySQL 如何使用索引” 。FULLTEXT 索引只能对 CHAR, VARCHAR 和 TEXT 列编制索引，并且只能在 MyISAM 表中编制。请参见 12.7 节，“全文搜索功能”。SPATIAL 索引只能对空间列编制索引，并且只能在 MyISAM 表中编制。空间列类型在第19 章：MySQL 中的空间扩展中进行了描述。13.1.5. CREATE TABLE 语法13.1.5.1. 沉寂的列规格变更CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name(create_definition,.)t

43、able_options select_statement或：CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name( LIKE old_tbl_name );create_definition:column_definition| CONSTRAINT symbol PRIMARY KEY index_type (index_col_name,.)| KEY index_name index_type (index_col_name,.)| INDEX index_name index_type (index_col_name,.)| CONSTRAINT

44、 symbol UNIQUE INDEXindex_name index_type (index_col_name,.)| FULLTEXT|SPATIAL INDEX index_name (index_col_name,.)| CONSTRAINT symbol FOREIGN KEYindex_name (index_col_name,.) reference_definition| CHECK (expr)column_definition:col_name type NOT NULL | NULL DEFAULT default_valueAUTO_INCREMENT UNIQUE

展开阅读全文