MySQL修改数据表

======================================================
修改数据表
==========
修改列
ALTER TABLE table_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
*[FIRST|AFTER col_name]:FIRST表示把插入列至于所有列前面，AFTER col_name表示把插入列放在指定列后

ALTER TABLE users ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

添加多列
ALTER TABLE table_name ADD [COLUMN] (col_name column_definition,......)
列删除
ALTER TABLE table_name DROP column_name;

修改约束
添加，删除主键约束
ALTER TABLE table_name ADD [CONSTRAINT[symbol]]PRIMARY KEY [index_name] [index_type] (index_col_name)

ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY(id);

ALTER TABLE table_name DROP PRIMARY KEY
添加，删除添加唯一约束
ALTER TABLE table_name ADD [CONSTRAINT[symbol]] UNIQUE [INSERT|KEY] [index_name] [index_type] (index_col_name,....)

 ALTER TABLE user2 ADD UNIQUE(username);

ALTER TABLE table_name DORP {INDEX|KEY} index_name
 添加，删除外键约束
AlTER TABLE table_name ADD [CONSTRAINT[symbol]]FOREIGN KEY [index_name] (index_col_name,....) reference_definition

 ALTER TABLE user2 ADD FOREIGN KEY(pid) REFERENCES province(id);

ALTER TABLE table_name DORP FOREIGN KEY fy_symbol
添加、删除默认约束
ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal|DORP DEFAULT}

修改列定义
ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

ALTER TABLE user2 MODIFY id BIGINT UNSIGNED NOT NULL FIRST;

修改列名称（也可修改列定义）
ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

ALTER TABLE user2 CHANGE pid p_id BIGINT UNSIGNED NOT NULL FIRST;

修改表名称
(1)ALTER TABLE table_name RENAME [TO|AS] new_table_name

ALTER TABLE user2 RENAME user1;

(2)RENAME TABLE table_name TO new_table_name[table_name TO new_table_name.....]（可为多张表更名）