修改数据表

修改数据表通过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;