修改表是指修改数据库中已存在的表的定义。比重新定义表简单,不需要重新加载数据,也不会影响正在进行的服务。MySQL 中通过ALTER TABLE语句来修改表。修改表包括修改表名、修改字段数据类型、修改字段名、增加字段、删除字段、修改字段的排列位置、更改默认存储引擎和删除表的外键约束等。
先查看example0表,之后的操作都针对这张表,我们可以观察其中变化。
mysql> DESC example0;
也可以使用语句: mysql> SHOW CREATE TABLE example0 G
1、修改表名
表名可以在一个数据库中唯一的确定一张表。数据库系统通过表名来区分不同的表。
语法形式: ALTER TABLE 旧表名 RENAME [TO] 新表名;
mysql> ALTER TABLE example0 RENAME user; 将example0表改名为user。
2、修改字段的数据类型
字段的数据类型包括整数型、浮点数型、字符串型、二进制类型、日期和时间类型等。数据类型决定了数据的存储格式、约束条件和有效范围。
基本语法: ALTER TABLE 表名 MODIFY 属性名 数据类型;
其中,表名 参数指所要修改的表的名称;属性名 参数指需要修改的字段的名称; 数据类型 参数指修改后的新数据类型。
mysql> ALTER TABLE user MODIFY name VARCHAR(30); 将user表中name字段的数据类型变为VARCHAR(30)。
3、修改字段名
字段名可以在一张表中唯一的确定一个字段。数据库系统通过字段名来区分表中的不同字段。
基本语法: ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
其中,旧属性名 参数指修改前的字段名;新属性名 参数指修改后的字段名;新数据类型 参数修改后的数据类型,如不需要修改,则将新数据类型设置与原来一样。
mysql> ALTER TABLE user CHANGE id stu_id INT(2); 将user表中id字段名改为stu_id,数据类型改为INT(2)。
如果只修改字段的数据类型,可以把旧属性名和新属性名都写成旧属性名。
4、增加字段
在创建表时,表中的字段就已经定义完成。如果增加新的字段,可以通过 ALTER TABLE 语句进行增加。
基本语法: ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
其中, 属性名1 参数是指需要增加的字段的名称; 数据类型 参数指新增加字段的数据类型; 完整性约束条件 是可选参数,用来设置新增字段的完整性约束条件; FIRST 是可选参数,作用是将新增字段设置为表的第一个字段; ALTER 属性名2 参数也是可选参数,作用是将新增字段添加到 属性名2 所指的字段后。如果没有 FIRST 和 AFTER 属性名2 参数指定新增字段的位置,新增字段默认为表的最后一个字段。
mysql> ALTER TABLE user ADD num INT(8) PRIMARY KEY FIRST; 在user表的第一个位置增加字段
mysql> ALTER TABLE user ADD address VARCHAR(30) NOT NULL AFTER num; 在user表的num字段后增加address字段
对于一个表而言,其中字段排列顺序对表不会有什么影响。但对于创建人来说,将某种直接或间接关系的字段放一块,更好理解。
5、删除字段
删除字段是指删除已经定义好的表中的某个字段。
基本语法: ALTER TABLE 表名 DROP 属性名; 其中,属性名 参数指需要从表中删除的字段的名称。
mysql> ALTER TABLE user ADD phone VARCHAR(20) NOT NULL; 在user表中增加一个字段,默认为表的最后一个字段。
mysql> ALTER TABLE user DROP phone; 在user表中删除字段phone。
6、修改字段的排列位置
创建表的时候,字段在表中的排列位置就已经确定,如果要改变字段在表的排列位置,使用 ALTER TABLE 语句来处理。
基本语法: ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST | AFTER 属性名2;
其中,属性名1 参数指需要修改位置的字段的名称; 数据类型 参数指 属性名1 的数据类型;FIRST 参数指定位置为表的第一个位置; AFTER 属性名2 参数指定 属性名1 插入在 属性名2 之后。
mysql> ALTER TABLE user MODIFY name VARCHAR(30) FIRST; 在user表中将字段user修改到第一个位置
mysql> ALTER TABLE user MODIFY sex TINYINT(1) AFTER name; 在user表中将字段sex修改到name字段后面
7、更改表的存储引擎
MySQL存储引擎是指MySQL数据库中表的存储类型。MySQL存储引擎包括InnoDB、MyISAM、MEMORY等。不同的表类型有着不同的优缺点。在创建表时,存储引擎就已经设定好了,使用ALTER TABLE 语句可以更改表的存储引擎的类型。
基本语法: ALTER TABLE 表名 ENGINE=存储引擎名; 其中,存储引擎名 参数指设置新的存储引擎的名称。
mysql> ALTER TABLE user ENGINE=MyISAM; 将user表存储引擎改为MyISAM
注意:使用ALTER语句可以改变表的存储引擎,避免重新创建表,但如果表中数据很多,改变存储引擎可能会造成一些意料之外的影响,如果一个表中已经存在很多数据,最好不要轻易更改其存储引擎。
8、删除表的外键约束
外键是一个特殊的字段,其将某一表与其父表建立关联关系。在创建表的时候,外键约束就已经设定好了。由于特殊需要,与父表之间的关联关系需要去除时,要求删除外键约束,使用 ALTER TABLE 语句删除表的外键约束。
基本语法: ALTER TABLE 表名 DROP FOREIGN KEY 外键别名; 其中,外键别名 参数指创建表时设置的外键的代号
mysql> SHOW CREATE TABLE example3 G 查看example3表详细结构
*************************** 1. row ***************************
Table: example3
Create Table: CREATE TABLE `example3` (
`id` int(11) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_fk` (`stu_id`,`course_id`),
CONSTRAINT `c_fk` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
从查询结果中可以看出,example3表的外键别名为 c_fk 。
mysql> ALTER TABLE example3 DROP FOREIGN KEY c_fk; 删除example3表的外键(FOREIGN KEY)约束