对于已经创建好的表，尤其是已经有大量数据的表，如果需要对表做一些结构上的改变，我们可以先将表删除（drop），然后再按照新的表定义重建表。这样做没有问题，但是必然要做一些额外的工作，比如数据的重新加载。而且，如果有服务在访问表，也会对服务产生影响。因此，在大多数情况下，表结构的更改一般都使用alter table 语句，以下是一些常用的命令。
（1） 修改表类型，语法如下：
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
例如，修改表emp 的ename 字段定义，将varchar(10)改为varchar(20)：
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp modify ename varchar(20); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
（2） 增加表字段，语法如下：
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
例如，表emp 上新增加字段age，类型为int(3)：
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp add column age int(3); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age | int(3) | YES | | | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
（3）删除表字段，语法如下：
ALTER TABLE tablename DROP [COLUMN] col_name
例如，将字段age 删除掉：
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age | int(3) | YES | | | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table emp drop column age; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
（4）字段改名，语法如下：
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
例如，将age 改名为age1，同时修改字段类型为int(4)：
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age | int(3) | YES | | | | +----------+---------------+------+-----+---------+-------+ mysql> alter table emp change age age1 int(4) ; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp -> ; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age1 | int(4) | YES | | | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
注意：change 和modify 都可以修改表的定义，不同的是change 后面需要写两次列名，不方便。
但是change 的优点是可以修改列名称，modify 则不能。
（5）修改字段排列顺序。
前面介绍的的字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项first|after column_name，这个选项可以用来修改字段在表中的位置，默认ADD 增加的新字段是加在表的最后位置，而CHANGE/MODIFY 默认都不会改变字段的位置。例如，将新增的字段birth date 加在ename 之后：
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age | int(3) | YES | | | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table emp add birth date after ename; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | birth | date | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age | int(3) | YES | | | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
修改字段age，将它放在最前面：
mysql> alter table emp modify age int(3) first; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | age | int(3) | YES | | | | | ename | varchar(20) | YES | | | | | birth | date | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
注意：CHANGE/FIRST|AFTER COLUMN 这些关键字都属于MySQL 在标准SQL 上的扩展，在其他数据库上不一定适用。
（6）表改名，语法如下：
ALTER TABLE tablename RENAME [TO] new_tablename
例如，将表emp 改名为emp1，命令如下：
mysql> alter table emp rename emp1; Query OK, 0 rows affected (0.00 sec) mysql> desc emp; ERROR 1146 (42S02): Table 'sakila.emp' doesn't exist mysql> desc emp1; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | age | int(3) | YES | | | | | ename | varchar(20) | YES | | | | | birth | date | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)