4.3 修改数据表
修改数据表是指修改数据库中已有数据表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有：修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表有关的操作进行讲解。
4.3.1 修改表名
MySQL是通过ALTER TABLE语句来实现表名的修改的，具体的语法规则如下：
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
其中，TO为可选参数，使用与否均不影响结果。
【例4.13】将数据表tb_dept3改名为tb_deptment3。
执行修改表名操作之前，先使用SHOW TABLES语句查看数据库中所有的表，结果如下：
mysql> SHOW TABLES; +---------------------+ | Tables_in_test_db | +---------------------+ | tb_dept | | tb_dept2 | | tb_dept3 |
使用ALTER TABLE语句将表tb_dept3改名为tb_deptment3，使用的SQL语句如下：
ALTER TABLE tb_dept3 RENAME tb_deptment3;
语句执行之后，使用SHOW TABLES语句查看数据库中的表，检查表tb_dept3是否改名成功，结果如下：
mysql> SHOW TABLES; +---------------------+ | Tables_in_test_db | +---------------------+ | tb_dept | | tb_dept2 | | tb_deptment3 |
经过比较可以看到，数据表列表中已经有了名称为tb_deptment3的表。
提示 读者可以在修改表名称时使用DESC命令查看修改前后两个表的结构，修改表名并不会改变表的结构，因此修改名称后的表和修改名称前的表的结构必然是相同的。
4.3.2 修改字段的数据类型
修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。在MySQL中修改字段的数据类型的语法规则如下：
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中“表名”指要修改数据类型的字段所在表的名称，“字段名”指需要修改的字段，“数据类型”指修改后字段的新数据类型。
【例4.14】将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。
执行修改表名操作之前，先使用DESC语句查看tb_dept1表结构，结果如下：
mysql> DESC tb_dept1; +----------+---------------+-------+------+--------+-------+ | Field | Type | Null | Key |Default | Extra | +----------+---------------+-------+------+--------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | YES | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+---------------+-------+------+--------+-------+ 3 rows in set (0.00 sec)
可以看到现在name字段的数据类型为VARCHAR(22)，下面修改其类型。输入如下SQL语句并执行：
ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
再次使用DESC语句查看表，结果如下：
mysql> DESC tb_dept1; +----------+--------------+------+------+---------+-------+ | Field | Type | Null | Key |Default | Extra | +----------+--------------+------+------+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+--------------+------+------+---------+-------+ 3 rows in set (0.00 sec)
上述语句执行之后，检查会发现表tb_dept1表中name字段的数据类型已经修改成了VARCHAR(30)，修改成功。
4.3.3 修改字段名
MySQL中修改表字段名的语法规则如下：
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
其中，“旧字段名”指修改前的字段名；“新字段名”指修改后的字段名；“新数据类型”指修改后的数据类型。如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样即可，但数据类型不能为空。
【例4.15】将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，使用的SQL语句如下：
ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
使用DESC语句查看表tb_dept1，会发现字段的名称已经修改成功，结果如下：
mysql> DESC tb_dept1; +--------+--------------+------+-------+--------+-------+ | Field | Type | Null | Key |Default | Extra | +--------+--------------+------+-------+--------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | loc | varchar(50) | YES | | NULL | | +--------+--------------+-----+--------+--------+-------+ 3 rows in set (0.00 sec)
【例4.16】将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，使用的SQL语句如下：
ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);
使用DESC语句查看表tb_dept1，会发现字段的名称和数据类型均已经修改成功，结果如下：
mysql> DESC tb_dept1; +----------+-------------+------+------+--------+-------+ | Field | Type | Null | Key |Default | Extra | +----------+-------------+------+------+--------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | +----------+-------------+------+------+--------+-------+ 3 rows in set (0.00 sec)
使用CHANGE语句也可以只修改数据类型，实现和使用MODIFY语句同样的效果，方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。
提示 由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录。因此，当数据库表中已经有数据时，不要轻易修改数据类型。
4.3.4 添加字段
随着业务需求的变化，可能需要在现有的表中添加新的字段。一个完整字段包括字段名、数据类型、完整性约束。添加字段的语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型>
[约束条件] [FIRST | AFTER已存在字段名];
新字段名为需要添加的字段的名称；FIRST为可选参数，其作用是将新添加的字段设置为表的第一个字段；AFTER为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。
提示 FIRST或“AFTER已存在字段名”用于指定新增字段在表中的位置，如果SQL语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。
1．添加无完整性约束条件的字段
【例4.17】在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号），使用的SQL语句如下：
ALTER TABLE tb_dept1 ADD managerId INT(10);
使用DESC语句查看表tb_dept1，会发现在表的最后添加了一个名为managerId的INT类型的字段，结果如下：
mysql> DESC tb_dept1; +-----------+-------------+--------+-------+----------+--------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+--------+-------+----------+--------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | managerId | int(10) | YES | | NULL | | +-----------+-------------+--------+-------+----------+--------+ 4 rows in set (0.03 sec)
2．添加有完整性约束条件的字段
【例4.18】在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，使用SQL语句如下：
ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;
使用DESC语句查看表tb_dept1，会发现在表的最后添加了一个名为column1的VARCHAR(12)类型且不为空的字段，结果如下：
mysql> DESC tb_dept1; +------------+--------------+-------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+-------+------+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | managerId | int(10) | YES | | NULL | | | column1 | varchar(12) | NO | | NULL | | +------------+--------------+-------+------+---------+-------+ 5 rows in set (0.00 sec)
3．在表的第一列添加一个字段
【例4.19】在数据表tb_dept1中添加一个INT类型的字段column2，使用的SQL语句如下：
ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
使用DESC语句查看表tb_dept1，会发现在表的第一列添加了一个名为column2的INT(11)类型的字段，结果如下：
mysql> DESC tb_dept1; +------------+--------------+-------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+-------+------+---------+-------+ | column2 | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | managerId | int(10) | YES | | NULL | | | column1 | varchar(12) | NO | | NULL | | +------------+--------------+-------+------+---------+-------+ 6 rows in set (0.00 sec)
4．在表的指定列之后添加一个字段
【例4.20】在数据表tb_dept1中name列后添加一个INT类型的字段column3，使用的SQL语句如下：
ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;
使用DESC语句查看表tb_dept1，结果如下：
mysql> DESC tb_dept1; +------------+--------------+-------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+-------+------+---------+-------+ | column2 | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | column3 | int(11) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | managerId | int(10) | YES | | NULL | | | column1 | varchar(12) | NO | | NULL | | +------------+--------------+-------+------+---------+-------+ 7 rows in set (0.03 sec)
可以看到，tb_dept1表中增加了一个名称为column3的字段，其位置在指定的name字段后面，添加字段成功。
4.3.5 删除字段
删除字段是将数据表中的某个字段从表中移除，语法格式如下：
ALTER TABLE <表名> DROP <字段名>;
“字段名”是指需要从表中删除的字段的名称。
【例4.21】删除数据表tb_dept1中的column2字段。
执行删除字段之前，先使用DESC语句查看tb_dept1表的结构，结果如下：
mysql> DESC tb_dept1; +-----------+--------------+-------+------+---------+-------+ | Field | Type | Null | Key | Default | Extr | +-----------+--------------+-------+------+---------+-------+ | column2 | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | column3 | int(11) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | managerId | int(10) | YES | | NULL | | | column1 | varchar(12) | NO | | NULL | | +-----------+--------------+-------+------+---------+-------+ 6 rows in set (0.03 sec)
删除column2字段，使用的SQL语句如下：
ALTER TABLE tb_dept1 DROP column2;
再次使用DESC语句查看表tb_dept1，结果如下：
mysql> DESC tb_dept1; +------------+-------------+-------+------+---------+-------+ | Field | Type | Null | Key | Default | Extr | +------------+-------------+-------+------+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | column3 | int(11) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | managerId | int(10) | YES | | NULL | | | column1 | varchar(12) | NO | | NULL | | +------------+-------------+-------+------+---------+-------+ 6 rows in set (0.03 sec)
可以看到，tb_dept1表中已经没有了名称为column2的字段，删除字段成功。
4.3.6 修改字段的排列位置
对于一个数据表来说，在创建的时候，字段在表中的排列顺序就已经确定了。但表的结构并不是完全不可以改变的，可以通过ALTER TABLE语句来改变表中字段的相对位置。语法格式如下：
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
“字段1”指要修改位置的字段，“数据类型”指“字段1”的数据类型，FIRST为可选参数，指将“字段1”修改为表的第一个字段，“AFTER <字段2>”指将“字段1”插入“字段2”后面。
1．修改字段为表的第一个字段
【例4.22】将数据表tb_dept1中的column1字段修改为表的第一个字段，使用的SQL语句如下：
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
使用DESC语句查看表tb_dept1，发现字段column1已经被移至表的第一列，结果如下：
mysql> DESC tb_dept1; +------------+--------------+--------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+--------+------+---------+-------+ | column1 | varchar(12) | NO | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | column3 | int(11) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | managerId | int(10) | YES | | NULL | | +------------+--------------+--------+------+---------+-------+ 6 rows in set (0.03 sec)
2．修改字段到表的指定列之后
【例4.23】将数据表tb_dept1中的column1字段插入location字段的后面，使用的SQL语句如下：
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;
使用DESC语句查看表tb_dept1，结果如下：
mysql> DESC tb_dept1; +------------+--------------+-------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+-------+------+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | column3 | int(11) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | | column1 | varchar(12) | NO | | NULL | | | managerId | int(10) | YES | | NULL | | +------------+--------------+-------+------+---------+-------+ 6 rows in set (0.03 sec)
可以看到，tb_dept1表中的字段column1已经被移至location字段之后。
4.3.7 更改表的存储引擎
通过前面章节的学习，知道存储引擎是MySQL中的数据存储在文件或者内存中时采用的不同技术实现。用户可以根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎。MySQL中的主要存储引擎有MyISAM、InnoDB、MEMORY(HEAP)、BDB、FEDERATED等。可以使用SHOW ENGINES语句查看系统支持的存储引擎。表4-3列出了5.5.13版本的MySQL所支持的存储引擎。
表4-3 MySQL支持的存储引擎
更改表的存储引擎的语法格式如下：
ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
【例4.24】将数据表tb_deptment3的存储引擎修改为MyISAM。
在修改存储引擎之前，先使用SHOW CREATE TABLE语句查看表tb_deptment3当前的存储引擎，结果如下。
mysql> SHOW CREATE TABLE tb_deptment3 \G
*************************** 1. row ***************************
Table: tb_deptment3
Create Table: CREATE TABLE 'tb_deptment3' (
'id' int(11) NOT NULL,
'name' varchar(22) DEFAULT NULL,
'location' varchar(50) DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'STH' ('name')
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
可以看到，表tb_deptment3当前的存储引擎为ENGINE=InnoDB，接下来修改存储引擎类型，输入如下SQL语句并执行：
mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;
使用SHOW CREATE TABLE语句再次查看表tb_deptment3的存储引擎，发现表tb_dept的存储引擎变成了MyISAM，结果如下：
mysql> SHOW CREATE TABLE tb_deptment3 \G
*************************** 1. row ***************************
Table: tb_deptment3
Create Table: CREATE TABLE 'tb_deptment3' (
'id' int(11) NOT NULL,
'name' varchar(22) DEFAULT NULL,
'location' varchar(50) DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'STH' ('name')
) ENGINE=MyISAM DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
4.3.8 删除表的外键约束
对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。MySQL中删除外键的语法格式如下：
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
“外键约束名”指在定义表时CONSTRAINT关键字后面的参数，详细内容可参考4.1.3节的内容。
【例4.25】删除数据表tb_emp9中的外键约束。
首先创建表tb_emp9，创建外键deptId关联tb_dept1表的主键id，使用的SQL语句如下：
CREATE TABLE tb_emp9
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);
使用SHOW CREATE TABLE语句查看表tb_emp9的结构，结果如下：
mysql> SHOW CREATE TABLE tb_emp9 \G
*************************** 1. row ***************************
Table: tb_emp9
Create Table: CREATE TABLE 'tb_emp9' (
'id' int(11) NOT NULL,
'name' varchar(25) DEFAULT NULL,
'deptId' int(11) DEFAULT NULL,
'salary' float DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'fk_emp_dept' ('deptId'),
CONSTRAINT 'fk_emp_dept' FOREIGN KEY ('deptId') REFERENCES 'tb_dept1' ('id')
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
可以看到，已经成功添加了表的外键，下面删除外键约束，使用的SQL语句如下：
ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;
执行完毕之后，将删除表tb_emp的外键约束，使用SHOW CREATE TABLE语句再次查看表tb_emp9的结构，结果如下：
mysql> SHOW CREATE TABLE tb_emp9 \G
*************************** 1. row ***************************
Table: tb_emp9
Create Table: CREATE TABLE 'tb_emp9' (
'id' int(11) NOT NULL,
'name' varchar(25) DEFAULT NULL,
'deptId' int(11) DEFAULT NULL,
'salary' float DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'fk_emp_dept' ('deptId')
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
可以看到，tb_emp9中已经不存在FOREIGN KEY，原有的名称为fk_emp_dept的外键约束删除成功。