SQL -- alter

database test; alter database test character set utf8; alter database test character set gbk;

show create table user1;
alter table user1 character set gbk;
alter table user1 character set utf8;

engine

alter table TABLE_NAME ENGINE=InnoDB;

add drop

alter table user1 add age int not null default 10;           # 添加到最后一列
alter table user1 add age1 int not null default 10 first;    # 添加到第一列
alter table user1 add age2 int not null default 10 after age;
alter table user1 add age3 int not null, add age4 int not null;

alter table user1 drop age;
alter table user1 drop age1, drop age2, drop age3, drop age4;

create table test1 (
id int,
username varchar(10) not null
);

create table test2 (
id2 int not null,
username2 varchar(10) not null
);

primary key:

alter table test2 add constraint pk_test2_id primary key(id2);
show columns from test2;
alter table test2 drop primary key;

alter table test2 add primary key (id2);
show columns from test2;
alter table test2 drop primary key;

foreign key

alter table test2 add foreign key (id2) references test1(id);      # 类型要相同

"Can‘t create table ‘test.#sql-158e_11‘ (errno: 150)"              # 类型不同 150

alter table test2 modify id2 int not null;
alter table test1 modify id int not null;
alter table test1 add foreign key (id) references test2(id2);

"Can‘t create table ‘test.#sql-158e_11‘ (errno: 150)"

alter table test2 add constraint pk_test2_id primary key(id2);     # 父表一定要是主键
alter table test1 add foreign key (id) references test2(id2);
show columns from test2;

删除 foreign key:

show keys from test1;
show index from test1;
show create table test1;
# ... test1_ibfk_1
alter table test2 drop foreign key test1_ibfk_1;

default

alter table test2 add age int not null ;
alter table test2 alter age set default 15;    # 添加 default 属性
alter table test2 alter age drop default;      # 删除 default 属性

alter table test2 add unique (username2);
show columns from test2;

modify change

alter table test2 modify id smallint not null FIRST;
alter table test2 modify id tinyint not null FIRST;   # 把 *大整数* 切换到 *小整数* 注意防止数据丢失

alter table test2 change pid p_id tinyint;
alter table test2 change p_id pid int;

rename 一般不能更改, 乱改数据可能出错

rename table test1 to test3;
alter table test3 rename to test1;

drop table test1, test2;

SQL -- alter

标签：