ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
添加单列:
ALTER TABLE 表名 ADD [COLUNM] 列名 数据类型 [FIRST|AFTER 指定列];
例子：
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | s_id | int(11) | NO | PRI | NULL | | | sex | varchar(10) | NO | | NULL | | | age | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+
末尾插入address
ALTER TABLE student_details ADD address varchar(50);
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | s_id | int(11) | NO | PRI | NULL | | | sex | varchar(10) | NO | | NULL | | | age | int(11) | NO | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
头部插入name
ALTER TABLE student_details ADD name varchar(10) FIRST;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | s_id | int(11) | NO | PRI | NULL | | | sex | varchar(10) | NO | | NULL | | | age | int(11) | NO | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
sex后插入marry
ALTER TABLE student_details ADD marry varchar(10) AFTER sex;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | s_id | int(11) | NO | PRI | NULL | | | sex | varchar(10) | NO | | NULL | | | marry | varchar(10) | YES | | NULL | | | age | int(11) | NO | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
添加多列:
ALTER TABLE 表名 ADD [COLUMN] 列名1 数据类型 [FIRST|AFTER 指定列], ADD [COLUMN] 列名2 数据类型 [FIRST|AFTER 指定列];
例子：
age后插入father, mother
ALTER TABLE student_details ADD father varchar(10) AFTER age,ADD mother varchar(10) AFTER age;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | s_id | int(11) | NO | PRI | NULL | | | sex | varchar(10) | NO | | NULL | | | marry | varchar(10) | YES | | NULL | | | age | int(11) | NO | | NULL | | | mother | varchar(10) | YES | | NULL | | | father | varchar(10) | YES | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
删除数据表中的列：
ALTER TABLE 表名 DROP [COLUMN] 列名 ;
例子：
删除father,mother
ALTER TABLE student_details DROP father, DROP mother;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | s_id | int(11) | NO | PRI | NULL | | | sex | varchar(10) | NO | | NULL | | | marry | varchar(10) | YES | | NULL | | | age | int(11) | NO | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
修改列名和数据类型:
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
例子：
marry 改名 married
ALTER TABLE student_details CHANGE marry married varchar(12);
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | s_id | int(11) | NO | PRI | NULL | | | sex | varchar(10) | NO | | NULL | | | married | varchar(12) | YES | | NULL | | | age | int(11) | NO | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
修改数据类型不改列名：
ALTER TABLE 表名 MODIFY 列名 新数据类型;
例子：
sex 修改数据类型 INT
ALTER TABLE student_details MODIFY sex INT;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | s_id | int(11) | NO | PRI | NULL | | | sex | int(11) | YES | | NULL | | | married | varchar(12) | YES | | NULL | | | age | int(11) | NO | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
修改表名：
ALTER TABLE 表名 RENAME TO 新表名;
例子：
+-----------------+ | Tables_in_mydb | +-----------------+ | book | | course | | course_select | | department | | student | | student_details | | tb1 | +-----------------+
ALTER TABLE book RENAME TO music;
+-----------------+ | Tables_in_mydb | +-----------------+ | course | | course_select | | department | | music | | student | | student_details | | tb1 | +-----------------+