添加单列
ALTER TABLE tbl_name ADD age TINYINT NOT NULL DEFAULT 10 ;
添加到某一列之后,比如在name列后,AFTER name
ALTER TABLE tbl_name ADD age TINYINT NOT NULL DEFAULT 10 AFTER name;
最前,使用first
ALTER TABLE tbl_name ADD age TINYINT NOT NULL DEFAULT 10 FIRST ;
添加多列 ,不能指定插入位置
ALTER TABLE tbl_name ADD(age TINYINT NOT NULL DEFAULT 10 ,name varchar(20)) ;
删除多列
ALTER TABLE tbl_name DROP age ,DROP name;
添加主键约束,name设置为主键
ALTER TABLE mytable ADD CONSTRAINT PRIMARY KEY(name);
添加唯一约束
ALTER TABLE mytable ADD CONSTRAINT UNIQUE KEY(age,class);
添加外键约束
ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provinces(id);
添加删除默认约束
ALTER TABLE users ALTER username SET DEFAULT "dd"
删除默认约束
ALTER TABLE users ALTER username DROP DEFAULT ;
删除主键约束,如果设置了auto_increament,则无法删除
ALTER TABLE mytable DROP PRIMARY KEY;
删除唯一约束
ALTER TABLE mytable DROP INDEX name;
删除外键约束
表信息如下:外键名为 users2_ibfk_1,将其删除即可
| users2 | CREATE TABLE `users2` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
`pid` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
改变列的位置
修改列属性
ALTER TABLE mytable MODIFY name varchar(28) ;