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]…


作者:筱剑Beta

2019-12-20 16:07:05.0编辑

2019-08-02 11:59:05.0创作

所属分类:Mysql

mysql
sql
发布

街角有人祝福,巷口有人哭,这里却没人评论ε=(´ο`*)))