修改表指的是修改数据库中已经存在的数据表的结构。MySQL 使用 ALTER TABLE 语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。
1.基本语法
ALTER TABLE <表名> [修改选项]
ADD COLUMN <列名> <类型>
MODIFY COLUMN <列名> <类型>
CHANGE COLUMN <旧列名> <新列名> <新列类型>
ENAME TO <新表名>
DROP COLUMN <列名>
ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
举例表:
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2.添加字段
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];
-- FIRST 或 AFTER 已存在的字段名”用于指定新增字段在表中的位置,如果 SQL 语句中没有这两个参数,则
-- 默认将新添加的字段设置为数据表的最后列。
【实例 1】使用 ALTER TABLE 修改表 tb_emp1 的结构,在表的第一列添加一个 int 类型的字段 col1,输入的 SQL 语句和运行结果如下所示。
ALTER TABLE tb_emp1 ADD COLUMN col1 INT FIRST;
--输出:
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
【实例 2】使用 ALTER TABLE 修改表 tb_emp1 的结构,在一列 name 后添加一个 int 类型的字段 col2,输入的 SQL 语句和运行结果如下所示。
ALTER TABLE tb_emp1 ADD COLUMN col2 INT AFTER name;
-- 输出:
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| col2 | int(11) | YES | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
-- 增加列为主键
alter table patient add column id BIGINT not null PRIMARY KEY auto_increment first
-- 将已有的列设置为主键
-- 将列名为id的字段设置为主键
alter table patient add primary key(id)
3.修改数据类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
-- 表名指要修改数据类型的字段所在表的名称,字段名指需要修改的字段,数据类型指修改后字段的新数据类型。
【实例 3】使用 ALTER TABLE 修改表 tb_emp1 的结构,将 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30),输入的 SQL 语句和运行结果如下所示。
ALTER TABLE tb_emp1 MODIFY name VARCHAR(30);
-- 输出:
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| col2 | int(11) | YES | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4.修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
-- TO 为可选参数,使用与否均不影响结果。
【实例 6】使用 ALTER TABLE 将数据表 tb_emp1 改名为 tb_emp2,输入的 SQL 语句和运行结果如下所示。
ALTER TABLE tb_emp1 RENAME TO tb_emp2;
+--------------------+
| Tables_in_test_db |
+--------------------+
| tb_emp2 |
+--------------------+
5.修改字段名称
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
--旧字段名:指修改前的字段名;新字段名:指修改后的字段名;
-- 新数据类型:指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样, 但数据类型不能为空。
【实例 5】使用 ALTER TABLE 修改表 tb_emp1 的结构,将 col1 字段名称改为 col3,同时将数据类型变为 CHAR(30),输入的 SQL 语句和运行结果如下所示。
ALTER TABLE tb_emp1 CHANGE col1 col3 CHAR(30);
-- 输出:
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col3 | char(30) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6.创建修改约束
create table table_name(
列名1 数据类型 (int) primary key auto_increment,
列名2 数据类型 not null,
列名3 数据类型 unique,
列名4 数据类型 default '值',
constraint 索引名 foreign key(外键列) references 主键表(主键列)
on delete cascade | on delete set null
)
1.主键约束
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key
2.非空约束
添加:alter table table_name modify 列名 数据类型 not null
删除:alter table table_name modify 列名 数据类型 null
3.唯一约束
添加:alter table table_name add unique 约束名(字段)
删除:alter table table_name drop key 约束名
4.自动增长
添加:alter table table_name modify 列名 int auto_increment
删除:alter table table_name modify 列名 int
5.外键约束
添加:alter table table_name add constraint 约束名 foreign key(外键列)
references 主键表(主键列)
6.默认值
添加:alter table table_name alter 列名 set default '值'
删除:alter table table_name alter 列名 drop default
7.删除字段
ALTER TABLE <表名> DROP <字段名>;
-- 其中,字段名指需要从表中删除的字段的名称。
【实例 4】使用 ALTER TABLE 修改表 tb_emp1 的结构,删除 col2 字段,输入的 SQL 语句和运行结果如下所示。
ALTER TABLE tb_emp1 DROP col2;
-- 输出:
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
8.默认值
Field Type Null Key Default Extra
------------- ----------- ------ ------ ------- --------
runoob_id int(10) YES (NULL)
runoob_title varchar(20) YES (NULL)
runoob_author varchar(20) YES (NULL)
1、修改字段默认值
alter table patient alter address set default 2
输出:
Field Type Null Key Default Extra
------------- ----------- ------ ------ ------- --------
runoob_id int(10) YES 3
runoob_title varchar(20) YES (NULL)
runoob_author varchar(20) YES (NULL)
2、删除字段默认值
alter table patient alter address drop default
输出:
Field Type Null Key Default Extra
------------- ----------- ------ ------ ------- --------
runoob_id int(10) YES (NULL)
runoob_title varchar(20) YES (NULL)
runoob_author varchar(20) YES (NULL)
3、增加新列,带默认值,在第一列
ALTER TABLE `runoob` ADD COLUMN backup1 VARCHAR(3) DEFAULT 6 FIRST
输出:
Field Type Null Key Default Extra
------------- ----------- ------ ------ ------- --------
backup1 varchar(3) YES 6
runoob_id int(10) YES (NULL)
runoob_title varchar(20) YES (NULL)
runoob_author varchar(20) YES (NULL)
4、查看表所有字段默认值
show COLUMNS from patient mysql
Field Type Null Key Default Extra
------------- ----------- ------ ------ ------- --------
backup1 varchar(3) YES 6
runoob_id int(10) YES 3
runoob_title varchar(20) YES (NULL)
runoob_author varchar(20) YES (NULL)