MySQL数据库(8)----表的创建、删除、索引和更改

　　MySQL允许使用 CREATE TABLE 语句和 DROP TABLE 语句来创建、删除表，使用 ALTER TABLE 语句更改其结构。CREATE INDEX 语句和 DROP INDEX 语句则可以用来为已有表增加或删除索引。下面将详细解释这些语句。但先来了解一下MySQL支持的存储引擎的一些信息。

1.存储引擎的特性

　　MySQL支持多种存储引擎(以前被称为"表处理机器")。每一种存储引擎所实现的表都具有一组特定的属性或特性。下表简要描述了几种存储引擎。

存储引擎	描        述
ARCHIVE	用于数据存档的引擎(行被插入后就不能再修改了)
BLACKHOLE	这种存储引擎会丢弃写操作，读操作会返回空内容
CSV	这种存储引擎在存储数据时，会以逗号作为数据项之间的分隔符
FEDERATED	用来访问远程表的存储引擎
InnoDB	具备外键支持功能的事务处理引擎
MEMORY	用来管理由多个MyISAM表构成的表集合
MyISAM	主要的非事务处理存储引擎
MERGE	用来管理由多个MyISAM表构成的表集合
NDB	MySQL集群专用存储引擎

1.1查看有哪些存储引擎可用

mysql> SHOW ENGINES \G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.01 sec)

SUPPORT 栏的值分别为 YES 或 NO，分别代表了该存储引擎是否可用；DISABLED 的意思是该存储引擎存在，但它已被关闭；DEFAULT 表示它是服务器默认使用的存储引擎。Transactions栏表示的是存储引擎是否支持事务处理。XA和Savepoints栏表示的是存储引擎是否支持分布式事务和部分事务回滚。

　　在INFORMATION_SCHEMA 数据库的 ENGINES 表里，提供了与 SHOW ENGINES 语句所示内容完全一样的信息。不过由于需要使用 SELECT 来访问它，因此可以加上查询条件，以便只选择那些感兴趣的信息。例如，下面这条语句会使用 ENGINES 表来检查有哪些可用引擎支持事务处理：

mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES WHERE TRANSACTIONS='YES';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)

1.2表的磁盘存储方式

　　每次创建表时，MySQL都会创建一个磁盘文件，用于保存该表的格式(即它的定义)。这个格式文件的基本名与表明相同，扩展名为 .frm 。例如，有一个名为 tab 的表，其格式文件的名字将是 tab.frm 。服务器会在包含这个表的那个数据库所对应的数据库目录里，创建这个文件。.frm 文件不会变化，因为无论管理这个表的存储引擎是哪一种，每个表都只会有一个与之对应的 .frm 文件。如果表名包含的字符会引起文件名问题，那么 SQL 语句里使用的表名可能与相应的 .frm 文件的基本名并不一致。

　　各个存储引擎还可能会为表再创建几个特定的文件，用以存储表的内容。对于某个给定的表，与之相关的所有文件都集中存放在包含这个表的那个数据库所对应的目录里。下表给出了几种由某个存储引擎创建的与表相关的文件扩展名：

存储引擎	磁盘文件
InnoDB	.ibd(数据和索引)
MyISAM	.MYD(数据)、.MYI(索引)
CSV	.CSV(数据)、.CSM(元数据)

　　对某些存储引擎而言，格式文件是与某特定表相关联的唯一文件。其他存储引擎可能会把表的内容保存到磁盘上的其他地方，或者可能使用一个或多个表空间（有多个表共享的存储区域）。

 

2.表选项

　　一个表选项是 ENGINE= engine_name ， 它可以指定要用的存储引擎。例如，要创建一个 MyISAM 表，可以编写下面这样的语句：

CREATE TABLE myTable(...) ENGINE = MyISAM;

　　如果想要确定某个表使用的是哪一种存储引擎，那么可以执行 'SHOW CREATE TABLE table_name;' 语句，并查看其输出内容里的 ENGINE 选项：

mysql> SHOW CREATE TABLE student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `name` varchar(20) NOT NULL,
  `sex` enum('F','M') NOT NULL,
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

　　存储引擎还可以在 SHOW TABLE STATUS 语句或 INFORMATION_SCHEMA.TABLES 表的输出内容里看到。

　　如果想要修改某个已有表的存储特性，可以在 ALTER TABLE 语句里使用表选项。例如，想把 mytbl 表的当前存储引擎更改为 InnoDB，那么可以执行下面这条语句：

ALTER TABLE mytbl ENGINE = InnoDB;

 

3.删除表

　　删除表要比创建表容易很多，因为不需要给出任何有关其内容格式的信息。如果你拥有这个表的 DROP 权限，那么只要给出其名字即可：

DROP TABLE tbl_name;

　　在MySQL里，DROP TABLE 语句提供了几种有用的扩展。如果想要删除多个表，则可以把它们依次列在同一条语句里：

DROP TABLE tbl_name1, tbl_name2, ... ;

　　在默认情况下，当试图删除一个并不存在的表时，系统将会报错。为了不让这个错误出现，并在表不存在时只是生成一条警告信息，可以在语句里加上 IF EXISTS 子句：

DROP TABLE IF EXISTS tbl_name;

　　如果语句产生了警告信息，那么可以使用 SHOW WARNINGS 来查看它们。

 

4.索引表

4.1 创建索引

　　索引是加快访问表内容的基本手段，尤其是在涉及多个表的关联查询里。这是一个非常重要的话题。

　　使用 ALTER TABLE 语句可以创建MySQL所支持的任何一种索引：

ALTER TABLE tbl_name ADD INDEX index_name (index_columns);
ALTER TABLE tbl_name ADD UNIQUE index_name(index_columns);
ALTER TABLE tbl_name ADD PRIMARY KEY (index_columns);
ALTER TABLE tbl_name ADD FULLTEXT index_name(index_columns);
ALTER TABLE tbl_name ADD SPATIAL index_name(index_columns);

　　其中，tbl_name 是要添加索引的那个表的名字，index_columns 是要进行索引的列(如果要索引多个列，则需要使用逗号隔开)。索引名 index_name 是可选的。如果没有指定它，那么 MySQL 会根据第一个索引列的名字选取一个名字。

　　如果某个索引列在索引时使用了 PRIMARY KEY 或 SPATIAL，则它必须为 NOT NULL 的。其他索引列允许包含 NULL 值。

　　如果使用逗号把它们隔开，则可以在同一条 ALTER TABLE 语句里包括多个对表的改动。因此，你可以同时创建多个索引，这会比使用多条 ALTER TABLE 语句逐个添加索引快很多。

　　除了 PRIMARY KEY 以外，大部分索引类型都可以用 CREATE INDEX 语句来添加：

CREATE INDEX index_name ON tbl_name (index_columns);
CREATE UNIQUE index_name ON tbl_name(index_columns);
CREATE FULLTEXT INDEX index_name ON tbl_name(index_columns);
CREATE SPATIAL INDEX index_name ON tbl_name(index_columns);

　　其中，tbl_name、index_name、index_columns 的含义与 ALTER TABLE 语句里的一样。与 ALTER TABLE 语句不同的是，CREATE INDEX 语句里的索引名不是可选的，并且不能在一条语句里创建多个索引。

　　如果想在 CREATE TABLE 语句创建新表时创建索引，那么相应的语法与 ALTER TABLE 语句所用语法很相似，只不过需要在定义各列的基础上再增加一些索引创建子句：

CREATE TABLE tbl_name
(
    ...列定义...
    INDEX index_name (index_columns),
    UNIQUE index_name (index_columns),
    PRIMARY KEY (index_columns),
    FULLTEXT index_name (index_columns),
    SPATIAL index_name (index_columns),
    ...
);

　　与 ALTER TABLE 语句相类似，其中的 index_name 也是可选的。如果省略它，那么 MySQL 会从索引名里挑选一个。

　　作为一个特殊情况，通过在某列的定义末尾加上 PRIMARY KEY 或 UNIQUE 子句，可以创建一个单列的 PRIMARY KEY 或 UNIQUE 索引。例如，下面两条 CREATE TABLE 语句时等价的：

CREATE TABLE mytbl
(
    i INT NOT NULL PRIMARY KEY,
    j CHAR(10) NOT NULL UNIQUE
);

CREATE TABLE mytbl
(
    i INT NOT NULL,
    j CHAR(10) NOT NULL,
    PRIMARY KEY (i),
    UNIQUE (j)
);

 4.2 删除索引

　　删除索引的方法是，使用 DROP INDEX 或 ALTER TABLE 语句。如果要使用 DROP INDEX 语句，则必须给出那个要被删除的索引的名字：

DROP INDEX index_name ON tbl_name;

　　如果要使用 DROP INDEX 语句来删除 PRIMARY KEY，则必须以带引号标识符的形式给指定名字 PRIMARY：

DROP INDEX 'PRIMARY' ON tbl_name;

　　这条语句没有任何歧义，因为一个表只有一个 ‘PRIMARY KEY’，其名字也始终为 PRIMARY。

　　与 CREATE INDEX 语句类似，DROP INDEX 语句会被内部处理成 ALTER TABLE 语句。前面的那条 DROP INDEX 语句等价于下面的 ALTER TABLE 语句：

ALTER TABLE tbl_name DROP INDEX index_name;
ALTER TABLE tbl_name DROP PRIMARY KEY;

　　如果不知道表的索引名字，则可以使用 SHOW CREATE TABLE 或 SHOW INDEX 来查询它们。

　　当从表里删除列时，索引也会隐式地受到影响。如果删除的列是某个索引的组成部分，那么也会从该索引里删除这个列。如果你删除了某个索引的所有列，那么MySQL 会删除整个索引。

5. 更改表结构

下面是 ALTER TABLE 语句的语法：

ALTER TABLE tbl_name action [, action] ... ;

　　其中的每个动作 (action) 指的是对表所做的更改。有些数据库系统只允许一条 ALTER TABLE 语句带一个动作，但是 MySQL 支持带上过个动作，以逗号隔开。

5.1 更改列的数据类型

ALTER TABLE mytbl   MODIFY  i  MEDIUMINT  UNSIGNED;
ALTER TABLE mytbl   CHANGE  i  i  MEDIUMINT  UNSIGNED;

　　在使用 CHANGE 子句时，为何需要把列名写两遍呢？这是因为 CHANGE 子句能做到一件 MODIFY 子句无法做到的事情，即在更改数据类型的同时将该列重新命名。如果想在改变其数据类型的同时，把 i 重新命名为 k ，可以这样做：

ALTER TABLE mytbl  CHANGE  i  k  MEDIUMINT  UNSIGNED;

　　在使用 CHANGE 子句时，是先给出想要更改的那个列的名字，然后再指定其新的名字和定义。即使不想重新命名那个列，也需要把它的名字写两遍。

5.2 让表改用另一种存储引擎

ALTER TABLE  tbl_name  ENGINE = engine_name;

其中， engine_name 是一个诸如 InnoDB、MyISAM 或 MEMORY 之类的名字。不区分大小写。

5.3 重新命名表

ALTER TABLE  tbl_name  RENAME TO  new_tbl_name;

另一种重新命名表的的办法是使用 RENAME TABLE 语句。语法如下：

RENAME TABLE tbl_name TO new_tbl_name;

　　有一件事情是 RENAME TABLE 语句可以做、但 ALTER TABLE 语句无法做到的，即它每次可以对多个表进行重新命名。例如，你可以像下面这样交换两个表的名字：

RENAME TABLE t1 TO tmp , t2 TO t1 , tmp TO t2;

　　如果在表名前面使用了数据库名进行限定，就可以通过对它重新命名达到把它从一个数据库移动到另一个数据库的目的。下面两条语句都可以把表 t 从sampdb 数据库移动到 test 数据库：

ALTER TABLE sampdb.t  RENAME  TO  test.t;
RENAME TABLE  sampdb.t  TO test.t;

　　重新命名表名时不能使用已有的名字。