mysql-修改数据表
2018-10-28 本文已影响0人
黄一倚
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;