添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
示例:
另外,如果不写FIRST或AFTER col_name,则新加入的列将添加到所有列的最下面。
添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...);
删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name;
示例:
也可以在删除列的同时,又增加新列,这些语句只需用逗号分隔开即可。
添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...);
添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...);
添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition;
添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};
示例:
删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;
删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST | AFTER col_name];
修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];
数据表更名
方法一:
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
方法二:
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...;