当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。
1）修改表名：表名可以在数据库中唯一标识一个table
命令格式：ALTER TABLE 旧名 RENAME 新名;
mysql> alter table course rename courses;
Query OK, rows affected (0.00 sec)
2）修改属性的数据类型
命令格式：ALTER TABLE 表名 MODIFY 属性名 数据类型 [完整性约束条件]
mysql> describe num;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| name | int() | YES | | NULL | |
+--------+---------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql> alter table num modify name char(20); //改变属性的数据类型
Query OK, rows affected (0.05 sec)
Records: Duplicates: Warnings: mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| name | char() | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)
mysql> alter table num modify sex char() not null default 'male'; //改变属性的完整性约束条件
Query OK, rows affected (0.08 sec)
Records: Duplicates: Warnings: mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| name | char() | NO | | male | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)
3）修改属性的默认值
命令格式1：ALTER TABLE 表名 MODIFY 属性名 数据类型 DEFAULT value;
命令格式2：ALTER TABLE 表名 ALTER 属性名 SET DEFAULT value；
mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| sex | char() | NO | | male | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql> alter table num modify sex char() default 'female'; //修改属性的默认值
Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings: mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| sex | char() | YES | | female | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)
注意：上述示例说明，在对属性做更改使，如果不指定NOT NULL参数，命令会按照“允许为空”的操作进行设置；
而且，若不指定DEFAULT参数，MySQL会自动设置该字段默认为 NULL，即便执行命令之前，其有非NULL默认值。
4）修改字段名
命令格式：ALTER TABLE 表名 CHANGE 属性名 新属性名 新数据类型
mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| name | char() | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql> alter table num change name sex char(); //修改属性名称
Query OK, rows affected (0.04 sec)
Records: Duplicates: Warnings: mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| sex | char() | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)
5）增加字段
命令格式：alter table 表名 ADD 属性名1 [完整性约束条件] [FIRST | AFTER 属性名2]
其中，FIRST参数表示将新加的属性设置为该表的第一个字段；AFTER 属性名2表示将新加的字段置于属性名2（已存在）之后。
mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| sex | char() | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql> alter table num add name char() NOT NULL FIRST; //新增一个属性
Query OK, rows affected (0.05 sec)
Records: Duplicates: Warnings: mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| name | char() | NO | | NULL | |
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| sex | char() | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)
6）删除字段
命令格式：ALTER TABLE 表名 DROP 属性名;
mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| name | char() | NO | | NULL | |
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| sex | char() | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql> alter table num drop name; //删除属性name
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings: mysql> describe num;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| num_id | int() | NO | PRI | NULL | auto_increment |
| stu_id | int() | NO | PRI | NULL | |
| sex | char() | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)