修改数据表
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
例子:
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name coulumn_definition,...)删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name [,DROP col_name,... ]
例子:
ALTER TABLE users1 DROP truename;
ALTER TABLE users1 DROP password,DROP age;添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)
例子:
CREATE TABLE users2(
`id` SMALLINT UNSIGNED,`username` VARCHAR(10) NOT NULL,
`pid` SMALLINT UNSIGNED,
`age` TINYINT UNSIGNED NOT NULL
);
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);
添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)
例子:
ALTER TABLE users2 ADD UNIQUE (username);
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_type] (index_col_name,...) reference_definition
例子:
ALTER TABLE users2 ADD CONSTRAINT FOREIGN KEY (pid) REFERENCES provinces (id);
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
例子:
ALTER TABLE users2 ALTER age SET DEFAULT 15;
ALTER TABLE users2 ALTER age DROP DEFAULT;删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
例子:
ALTER TABLE users2 DROP PRIMARY KEY;
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name
例子:
ALTER TABLE users2 DROP INDEX username;
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
例子:
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
例子:
ALTER TABLE users2 DROP INDEX pid;
修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
例子:
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL FIRST;
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition []FIRST | AFTER col_name]
例子:
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
方法1
ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
例子:
ALTER TABLE users2 RENAME users3;
方法2RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2,...]
例子:
RENAME TABLE users3 TO users2;
建议:以后实际项目中尽量不要对数据表和字段进行更名,因为会对其他使用了该数据表或字段的视图或存储过程无法工作。