MySql中alter关键字的用法

来源：互联网发布：百度云可以域名注册么编辑：程序博客网时间：2024/05/21 08:23

alter用于修改数据库和表。

alter database用于修改数据库全局特性。

ALTER {DATABASE | SCHEMA} [db_name]    alter_specification [, alter_specification] ...alter_specification:    [DEFAULT] CHARACTER SET charset_name  | [DEFAULT] COLLATE collation_name

alter table用于修改表的属性。

ALTER [IGNORE] TABLE tbl_name    alter_specification [, alter_specification] ...alter_specification:    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]  | ADD [COLUMN] (column_definition,...)  | ADD INDEX [index_name] [index_type] (index_col_name,...)  | ADD [CONSTRAINT [symbol]]        PRIMARY KEY [index_type] (index_col_name,...)  | ADD [CONSTRAINT [symbol]]        UNIQUE [index_name] [index_type] (index_col_name,...)  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)  | ADD [CONSTRAINT [symbol]]        FOREIGN KEY [index_name] (index_col_name,...)        [reference_definition]  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}  | CHANGE [COLUMN] old_col_name column_definition        [FIRST|AFTER col_name]  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]  | DROP [COLUMN] col_name  | DROP PRIMARY KEY  | DROP INDEX index_name  | DROP FOREIGN KEY fk_symbol  | DISABLE KEYS  | ENABLE KEYS  | RENAME [TO] new_tbl_name  | ORDER BY col_name  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]  | DISCARD TABLESPACE  | IMPORT TABLESPACE  | table_options  | partition_options  | ADD PARTITION partition_definition  | DROP PARTITION partition_names  | COALESCE PARTITION number  | REORGANIZE PARTITION partition_names INTO (partition_definitions)  | ANALYZE PARTITION partition_names  | CHECK PARTITION partition_names  | OPTIMIZE PARTITION partition_names  | REBUILD PARTITION partition_names  | REPAIR PARTITION partition_names

例子：

1、给表emp增加一个外键约束。

alter table emp add constraint thisisname foreign key(dept) references dept(id);

2、给dept表增加一列。

alter table dept add column test varchar(100);

3、删除一个表的外键约束。

alter table emp drop foreign key thisisname;

4、把name列放置到id列的后面。

alter table emp modify name varchar(100) after id;

5、把name列放作为第一列。

alter table emp modify name varchar(100) first;