15.3修改数据表

9/17/2020 9:40:31 PM 人评论次浏览

15.3　修改数据表

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

15.3.1　修改表名

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

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

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

【例15.13】将数据表tb_dept3改名为tb_department3。

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

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

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

　　ALTER TABLE tb_dept3 RENAME tb_department3;

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

　　mysql> SHOW TABLES;
　　+---------------------+
　　| Tables_in_test    |
　　+---------------------+
　　| tb_department3  |
　　| tb_dept1         |
　　| tb_dept2        |
　　           省略部分内容

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

提　示

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

15.3.2　修改字段的数据类型

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

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

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

【例15.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)，修改成功。

15.3.3　修改字段名

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

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

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

【例15.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)

【例15.16】将数据表tb_dept1中的loc字段名称改为location，同时将数据类型改为VARCHAR(60)，SQL语句如下：

　　ALTER TABLE tb_dept1CHANGE 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语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变数据类型。

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

15.3.4　添加字段

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

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

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

提　示

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

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

【例15.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．添加有完整性约束条件的字段

【例15.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．在表的第一列添加一个字段

【例15.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．在表的指定列之后添加一个字段

【例15.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字段后面，添加字段成功。

15.3.5　删除字段

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

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

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

【例15.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的字段，删除字段成功。

15.3.6　修改字段的排列位置

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

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

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

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

【例15.22】将数据表tb_dept中的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．修改字段到表的指定列之后

【例15.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字段之后。

15.3.7　更改表的存储引擎

存储引擎是MySQL中的数据存储在文件或者内存中时采用的不同技术实现。可以根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎。MySQL中主要存储引擎有MyISAM、InnoDB、MEMORY（HEAP）、BDB、FEDERATED等。可以使用SHOW ENGINES语句查看系统所支持的存储引擎。表15-3列出了5.5.13版本的MySQL所支持的存储引擎。

表15-3　MySQL支持的存储引擎

引擎名	是否支持
FEDERATED	否
MRG_MYISAM	是
MyISAM	是
BLACKHOLE	是
CSV	是
MEMORY	是
ARCHIVE	是
InnoDB	是
PERFORMANCE_SCHEMA	是

更改表的存储引擎的语法格式如下：

　　ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

【例15.24】将数据表tb_department3的存储引擎修改为MyISAM。

在修改存储引擎之前，先使用SHOW CREATE TABLE查看表tb_department3当前的存储引擎，结果如下。

　　mysql>  SHOW CREATE TABLE tb_department3 \G;
　　*************************** 1. row ***************************
　　       Table: tb_department3
　　Create Table: CREATE TABLE 'tb_department3' (
　　  '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=utf8
　　1 row in set (0.00 sec)

可以看到，表tb_department3当前的存储引擎为ENGINE=InnoDB，接下来修改存储引擎类型，输入如下SQL语句并执行：

　　mysql> ALTER TABLE tb_department3 ENGINE=MyISAM;

使用SHOW CREATE TABLE再次查看表tb_department3的存储引擎，发现表tb_department3的存储引擎变成了MyISAM，结果如下：

　　mysql> SHOW CREATE TABLE tb_department3 \G;
　　*************************** 1. row ***************************
　　      Table: tb_department3
　　Create Table: CREATE TABLE 'tb_department3' (
　　  '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=utf8
　　1 row in set (0.00 sec)

15.3.8　删除表的外键约束

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

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

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

【例15.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=utf8
　　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(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=utf8
　　1 row in set (0.00 sec)

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

文章教程

15.3修改数据表