mysql-修改数据表

mysql> alter table user add age smallint not null default 10;

如果不加 first | after 则被添加到最后一列

指定添加在某一列后面

mysql> alter table user add phone varchar(20) after password;

mysql> alter table user add (address varchar(20),birthday varchar(20));

多列只能添加在最后

删除单列

mysql> alter table user drop birthday;

删除多列

mysql> alter table user drop address,drop age;

在删除的同时进行添加操作

mysql> alter table user drop phone,add address varchar(20);

可以同时进行多个操作

添加主键约束

mysql> alter table user add primary key(uid);

mysql> alter table user drop primary key;

mysql> alter table user add unique(username);

mysql> show indexes from user;  查看唯一约束的名字

mysql> alter table user drop index username;

mysql> alter table user add foreign key(vip) references vip(vid);

mysql> show create table user;  查看外键的名称

mysql> alter table user drop foreign key user_ibfk_1;

添加默认约束

mysql> alter table user alter address set default '上海';

删除默认约束

mysql> alter table user alter address drop default;

修改列位置

mysql> alter table user modify username varchar(20) first;

修改列定义

mysql> alter table user modify uid tinyint;

修改列名称和列定义

mysql> alter table user change uid user_id smallint;

mysql> alter table user rename db_user;

mysql> rename table db_user to user;