Mysql修改数据表操作ALTER用法汇总

Mysql列的增删改，各种约束（主键约束、唯一约束、外键约束、默认约束）的增删以及表名的修改汇总。

列的添加删除与修改

添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

tbl_name：需要添加列的表名

col_name：需要添加的列的名称

column_definition：需要添加的列的数据类型

FIRST：需要添加的列的数据类型

AFTER col_name：当加入该关键字的时候，表示该列添加在col_name列的后面(当不加关键字的时候，默认该列添加在最后)

ALTER TABLE users ADD age TINYINT UNSIGNED NOT NULL DEFAULT 0;

添加多列

ALTER TABLE tblname ADD [COLUMN] (colname column_definition, …)

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

删除多个列

ALTER TABLE tbl_name DROP [COLUMN] col_name1, DROP [COLUMN] col_name2

删除一个列同时添加一个列

ALTER TABLE tb1_name DROP [COLUMN] col_name1, ADD [COLUMN] col_name2 column_definition [FIRST | AFTER col_name]

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

示例：

ALTER TABLE users MODIFY id TINYINT UNSIGNED NOT NULL FIRST;
（修改了id列的一些属性，同时将id列放在了第一位）

修改列名称（该方法同样可以用于修改列定义）

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

示例：

ALTER TABLE users CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
（将pid的名称修改为p_id）

约束添加删除：

添加主键约束：

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)

示例：

ALTER TABLE users ADD CONSTRAINT PK_users_id PRIMARY KEY (id);

PK_users_id：主键约束自取名称

删除主键约束

ALTER TABLE users DROP PRIMARY KEY;

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,…)

示例：

ALTER TABLE users UNIQUE (username);

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

示例：

若要删除唯一约束，需要先取到唯一约束的约束名称，通过以下语句查看
SHOW INDEXES FROM users\G;
找到Key_name项，就是我们的唯一约束的约束名称
ALTER TABLE users DROP INDEX username;

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) reference_definition

示例：

ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provinces (id);

users：用户表

provinces：省份表

本语句将用户表中的pid设置外键对应到省份表中的id，从而达到关联用户表和省份表的效果。

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

示例：

若要删除外键约束，则需要先找到外键约束的名称：
SHOW CREATE TABLE users;
找到`CONSTRAINT 'users_ibfk_1' FOREIGN KEY(pid)……`部分，其中的'users_ibfk_1'就是系统为我们创建的外键约束名称
ALTER TABLE users DROP FOREIGN KEY users_ibfk_1;
由于外键约束会自动帮助我们创建该列的索引，如果我们不需要该列的索引，则可以删除
ALTER TABLE users DROP INDEX pid;

pid：该外键列的列名

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

示例：

ALTER TABLE users ALTER age SET DEFAULT 15;

修改表

修改数据表名称（方法一）

ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name

修改数据表名称（方法二）

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2]…

代码音符

Mysql修改数据表操作ALTER用法汇总

登录