购买
下载掌阅APP，畅读海量书库
立即打开
畅读海量书库
扫码下载掌阅APP

2.5　修改数据表

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有：修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对修改表的操作进行讲解。

2.5.1　修改表名

MySQL通过ALTER TABLE语句来实现表名的修改，具体的语法规则如下： 

     ALTER TABLE <旧表名> RENAME [TO] <新表名>;

其中，TO为可选参数，使用与否均不影响语句执行结果。

【例2.16】将数据表tb_dept3改名为tb_deptment3。

执行修改表名操作之前，使用SHOW TABLES查看数据库中所有的表。 

     mysql> SHOW TABLES;
     +---------------------+
     | Tables_in_test_db   |
     +---------------------+
     | tb_dept1          |
     | tb_dept2          |
     | tb_dept3          |
     …省略部分内容

使用ALTER TABLE将表tb_dept3改名为tb_deptment3，SQL语句如下： 

     ALTER TABLE tb_dept3 RENAME tb_deptment3;

语句执行之后，检验表tb_dept3是否改名成功。使用SHOW TABLES查看数据库中的表，结果如下： 

     mysql> SHOW TABLES;
     +---------------------+
     | Tables_in_test_db   |
     +---------------------+
     | tb_dept           |
     | tb_dept2          |
     | tb_deptment3        |
     …省略部分内容

经过比较可以看到，数据表列表中已经有了名称为tb_deptment3的表。

提示： 读者可以在修改表名称时，使用DESC命令查看修改前后两个表的结构，修改表名并不修改表的结构，因此，修改名称后的表和修改名称前的表的结构必然是相同的。

2.5.2　修改字段的数据类型

修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。在MySQL中修改字段数据类型的语法规则如下： 

     ALTER TABLE <表名> MODIFY <字段名>  <数据类型>

其中，“表名”指要修改数据类型的字段所在表的名称，“字段名”指需要修改的字段，“数据类型”指修改后字段的新数据类型。

【例2.17】 将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。

执行修改表名操作之前，使用DESC查看tb_dept1表结构，结果如下： 

     mysql> DESC tb_dept1;
     +----------+---------------+---------+--------+-------------+-------+
     | Field    | Type         | Null    | Key    |Default     | Extra |
     +----------+---------------+---------+--------+-------------+-------+
     | id      | int          | NO     | PRI    | NULL       |      |
     | name     | varchar(22)   | YES     |        | NULL        |       |
     | location | varchar(50)   | YES     |        | NULL        |       |
     +----------+---------------+---------+--------+-------------+-------+

可以看到现在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          | NO     | PRI    | NULL       |      |
     | name     | varchar(30)   | YES    |        | NULL        |       |
     | location | varchar(50)   | YES    |        | NULL        |       |
     +----------+---------------+--------+--------+-------------+-------+

语句执行之后，检验会发现表tb_dept1表中name字段的数据类型已经修改成了VARCHAR(30)，修改成功。

2.5.3　修改字段名

MySQL中修改表字段名的语法规则如下： 

     ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

其中，“旧字段名”指修改前的字段名；“新字段名”指修改后的字段名；“新数据类型”指修改后的数据类型，如果不需要修改字段的数据类型，将新数据类型设置成与原来一样即可，但数据类型不能为空。

【例2.18】将数据表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          | NO     | PRI    | NULL    |      |
     | name     | varchar(30)   | YES     |        | NULL    |       |
     | loc      | varchar(50)   | YES     |        | NULL    |       |
     +----------+---------------+---------+--------+---------+-------+

【例2.19】将数据表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          | NO     | PRI    | NULL       |      |
     | name     | varchar(30)   | YES     |        | NULL        |       |
     | location | varchar(60)   | YES     |        | NULL        |       |
     +----------+---------------+---------+--------+-------------+-------+

提示： CHANGE也可以只修改数据类型，实现和MODIFY同样的效果，方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。

由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录，因此当数据库表中已经有数据时，不要轻易修改数据类型。

2.5.4　添加字段

随着业务需求的变化，可能需要在已经存在的表中添加新的字段。一个完整字段包括字段名、数据类型、完整性约束。添加字段的语法格式如下： 

     ALTER TABLE <表名> ADD <新字段名> <数据类型>
     [约束条件] [FIRST | AFTER 已存在字段名];

新字段名为需要添加的字段的名称；“FIRST”为可选参数，其作用是将新添加的字段设置为表的第一个字段；“AFTER”为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。

提示： “FIRST”或“AFTER已存在字段名”用于指定新增字段在表中的位置，如果SQL语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后一列。

1．添加无完整性约束条件的字段

【例2.20】在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号），SQL语句如下： 

     ALTER TABLE tb_dept1 ADD managerId INT;

使用DESC查看表tb_dept1，会发现在表的最后添加了一个名为managerId的INT类型的字段，结果如下： 

     mysql> DESC tb_dept1;
     +-------------+---------------+----------+--------+-----------+-------+
     | Field      | Type         | Null     | Key    | Default   | Extra |
     +-------------+---------------+----------+--------+-----------+-------+
     | id         | int          | NO      | PRI    | NULL     |      |
     | name        | varchar(30)   | YES      |        | NULL      |       |
     | location    | varchar(60)   | YES      |        | NULL      |       |
     | managerId   | int          | YES     |       | NULL     |      |
     +-------------+---------------+----------+--------+-----------+-------+

2．添加有完整性约束条件的字段

【例2.21】在数据表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        | NO   | PRI  | NULL    |      |
     | name      | varchar(30) | YES  |      | NULL    |       |
     | location  | varchar(60) | YES  |      | NULL    |       |
     | managerId | int        | YES  |     | NULL    |      |
     | column1   | varchar(12) | NO   |      | NULL    |       |
     +-----------+-------------+------+------+---------+-------+

3．在表的第一列添加一个字段

【例2.22】在数据表tb_dept1中添加一个INT类型的字段column2，SQL语句如下： 

     ALTER TABLE tb_dept1 ADD column2 INT FIRST;

使用DESC查看表tb_dept1，会发现在表第一列添加了一个名为column2的INT类型字段，结果如下： 

     mysql> DESC tb_dept1;
     +-------------+---------------+----------+--------+-------------+--------+
     | Field      | Type         | Null     | Key    | Default     | Extra  |
     +-------------+---------------+----------+--------+-------------+--------+
     | column2     | int          | YES     |       | NULL       |       |
     | id         | int          | NO      | PRI    | NULL       |       |
     | name        | varchar(30)   | YES      |        | NULL        |        |
     | location    | varchar(60)   | YES      |        | NULL        |        |
     | managerId   | int          | YES     |       | NULL       |       |
     | column1     | varchar(12)   | NO       |        | NULL        |        |
     +-------------+---------------+----------+--------+-------------+--------+

4．在表的指定列之后添加一个字段

【例2.23】在数据表tb_dept1中name列后添加一个INT类型的字段column3，SQL语句如下： 

     ALTER TABLE tb_dept1 ADD column3 INT AFTER name;

使用DESC查看表tb_dept1，结果如下： 

     mysql> DESC tb_dept1;
     +------------+----------------+----------+--------+------------+--------+
     | Field     | Type          | Null     | Key    | Default    | Extra  |
     +------------+----------------+----------+--------+------------+--------+
     | column2    | int          | YES     |       | NULL      |       |
     | id        | int          | NO      | PRI    | NULL      |       |
     | name       | varchar(30)    | YES      |        | NULL       |        |
     | column3    | int          | YES     |       | NULL      |       |
     | location   | varchar(60)    | YES      |        | NULL       |        |
     | managerId  | int          | YES     |       | NULL      |       |
     | column1    | varchar(12)    | NO       |        | NULL       |        |
     +------------+----------------+----------+--------+------------+--------+

可以看到，tb_dept1表中增加了一个名称为column3的字段，其位置在指定的name字段后面，添加字段成功。

2.5.5　删除字段

删除字段是将数据表中的某个字段从表中移除，语法格式如下： 

     ALTER TABLE <表名> DROP <字段名>;

“字段名”指需要从表中删除的字段的名称。

【例2.24】删除数据表tb_dept1表中的column2字段。

首先，执行删除字段之前，使用DESC查看tb_dept1表结构，结果如下： 

     mysql> DESC tb_dept1;
     +-------------+--------------+----------+--------+-------------+--------+
     | Field      | Type        | Null     | Key    | Default     | Extr   |
     +-------------+--------------+----------+--------+-------------+--------+
     | column2     | int         | YES     |       | NULL       |       |
     | id         | int         | NO      | PRI    | NULL       |       |
     | name        | varchar(30)  | YES      |        | NULL        |        |
     | column3     | int         | YES     |       | NULL       |       |
     | location    | varchar(60)  | YES      |        | NULL        |        |
     | managerId   | int         | YES     |       | NULL       |       |
     | column1     | varchar(12)  | NO       |        | NULL        |        |
     +-------------+--------------+----------+--------+-------------+--------+

删除column2字段，SQL语句如下： 

     ALTER TABLE tb_dept1 DROP column2;

再次使用DESC查看表tb_dept1，结果如下： 

     mysql> DESC tb_dept1;
     +-------------+--------------+----------+--------+-------------+--------+
     | Field      | Type        | Null     | Key    | Default     | Extr   |
     +-------------+--------------+----------+--------+-------------+--------+
     | id         | int         | NO      | PRI    | NULL       |       |
     | name        | varchar(30)  | YES      |        | NULL        |        |
     | column3     | int         | YES     |       | NULL       |       |
     | location    | varchar(60)  | YES      |        | NULL        |        |
     | managerId   | int         | YES     |       | NULL       |       |
     | column1     | varchar(12)  | NO       |        | NULL        |        |
     +-------------+--------------+----------+--------+-------------+--------+

可以看到，tb_dept1表中已经不存在名称为column2的字段，说明删除字段成功。

2.5.6　修改字段的排列位置

对于一个数据表来说，在创建的时候，字段在表中的排列顺序就已经确定了，但表的结构也是可以改变的，可以通过ALTER TABLE来改变表中字段的相对位置。语法格式如下： 

     ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

其中，“字段1”指要修改位置的字段；“数据类型”指“字段1”的数据类型；“FIRST”为可选参数，指将“字段1”修改为表的第一个字段；“AFTER 字段2”指将“字段1”插入到“字段2”后面。

1．修改字段为表的第一个字段

【例2.25】将数据表tb_deptl中的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) | YES   |     | NULL    |       |
     | id       | int        | NO    | PRI | NULL    |      |
     | name      | varchar(30) | YES   |     | NULL    |       |
     | column3   | int        | YES   |     | NULL    |      |
     | location  | varchar(60) | YES   |     | NULL    |       |
     | managerId | int        | YES   |     | NULL    |      |
     +-----------+-------------+-------+-----+---------+-------+

2．修改字段到表的指定列之后

【例2.26】将数据表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        | NO   | PRI | NULL    |      |
     | name      | varchar(30) | YES  |     | NULL    |       |
     | column3   | int        | YES  |     | NULL    |      |
     | location  | varchar(60) | YES  |     | NULL    |       |
     | column1   | varchar(12) | YES  |     | NULL    |       |
     | managerId | int        | YES  |     | NULL    |      |
     +-----------+-------------+------+-----+---------+-------+

可以看到，tb_dept1表中的字段column1已经被移至location字段之后。

2.5.7　删除表的外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。一旦删除外键，就会解除主表和从表间的关联关系，MySQL中删除外键的语法格式如下： 

     ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

“外键约束名”指在定义表时CONSTRAINT关键字后面的参数，详细内容可参考2.3.3节。

【例2.27】删除数据表tb_emp9中的外键约束。

首先创建表tb_emp9，创建外键deptId关联tb_dept1表的主键id，SQL语句如下： 

     CREATE TABLE tb_emp9
     (
     id INT PRIMARY KEY,
     name VARCHAR(25),
     deptId INT,
     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 NOT NULL,
       `name` varchar(25) DEFAULT NULL,
       `deptId` INT 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
     1 row in set (0.00 sec)

可以看到，已经成功添加了表的外键，下面删除外键约束，SQL语句如下： 

     ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

执行完毕之后，将删除表tb_emp9的外键约束。使用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 NOT NULL,
       `name` varchar(25) DEFAULT NULL,
       `deptId` INT DEFAULT NULL,
       `salary` float DEFAULT NULL,
       PRIMARY KEY (`id`),
       KEY `fk_emp_dept` (`deptId`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
     1 row in set (0.00 sec)

可以看到，tb_emp9中已经不存在FOREIGN KEY，原有的名称为fk_emp_dept的外键约束删除成功。 ASewN0iv9gg720Qi+8Df5BizhsxQzy9R12nBRZXgUIB2JtXiWBGxiVHa+VBf7nur

点击中间区域
呼出菜单
上一章
目录
下一章
×