修改数据表通过ALTER TABLE语句实现,其语法为:
ALTER TABLE tbl_name [ALTER_specificatiON [, ALTER_specificatiON] ...]
ALTER_specificatiON:
ADD [COLUMN] col_name column_definitiON [FIRST | AFTER col_name ] ADD [COLUMN] (col_name column_definitiON,...) ADD [CONSTRAINT [symbol]] PRIMARY KEY (INDEX_col_name,...) ADD [CONSTRAINT [symbol]] UNIQUE KEY [INDEX_name] (INDEX_col_name,...) DROP [COLUMN] col_name ALTER [COLUMN] col_name {SET DEFAULT iteral | DROP DEFAULT} MODIFY [COLUMN] col_name olumn_definitiON [FIRST | AFTER col_name] CHANGE [COLUMN] old_col_name new_col_name column_definitiON [FIRST|AFTER col_name] RENAME [TO] new_tbl_name
-- 在最前面添加一列 ALTER TABLE t1 ADD id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST; -- 在c3列之后添加email列 ALTER TABLE t1 ADD email VARCHAR(60) NOT NULL AFTER c3;
-- 添加多列 ALTER TABLE t1 ADD ( photo VARCHAR(60) NOT NULL, vISited SMALLINT UNSIGNED NOT NULL DEFAULT 0, publIShed INT UNSIGNED NOT NULL );
-- 删除单列 ALTER TABLE t1 DROP photo; -- 删除多列 ALTER TABLE t1 DROP photo,DROP publIShed ;
-- 添加默认值 ALTER TABLE t1 ALTER sex SET DEFAULT 0; -- 删除默认值 ALTER TABLE t1 ALTER sex DROP DEFAULT;
-- 将photo列的数据类型更改为VARCHAR(80) ALTER TABLE t3 MODIFY password VARCHAR(32); -- 将email列移动到photo列之后 ALTER TABLE t3 MODIFY password VARCHAR(32) AFTER age;
-- 将password列重新命名为user_password ALTER TABLE t3 CHANGE password user_password VARCHAR(32);
-- 将数据表t1更名为users ALTER TABLE t1 RENAME TO users; -- 或者 RENAME TABLE t1 TO t2;