MySQL_ALTER命令
当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。
1）修改表名：表名可以在数据库中唯一标识一个table
命令格式：ALTER TABLE 旧名 RENAME 新名;
mysql> alter table course rename courses; Query OK, 0 rows affected (0.00 sec)
2）修改属性的数据类型
命令格式：ALTER TABLE 表名 MODIFY 属性名 数据类型 [完整性约束条件]
mysql> describe num; +--------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+----------------+ | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | name | int(20) | YES | | NULL | | +--------+---------+------+-----+---------+----------------+ rows in set (0.00 sec) mysql> alter table num modify name char(20); //改变属性的数据类型 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe num; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | name | char(20) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ rows in set (0.00 sec)
mysql> alter table num modify sex char(10) not null default 'male'; //改变属性的完整性约束条件 Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe num; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | name | char(10) | NO | | male | | +--------+----------+------+-----+---------+----------------+ 3 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(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | sex | char(10) | NO | | male | | +--------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> alter table num modify sex char(10) default 'female'; //修改属性的默认值 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe num; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | sex | char(10) | YES | | female | | +--------+----------+------+-----+---------+----------------+ 3 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(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | name | char(20) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ rows in set (0.00 sec) mysql> alter table num change name sex char(10); //修改属性名称 Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe num; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | sex | char(10) | 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(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | sex | char(10) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ rows in set (0.00 sec) mysql> alter table num add name char(20) NOT NULL FIRST; //新增一个属性 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe num; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | name | char(20) | NO | | NULL | | | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | sex | char(10) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ rows in set (0.00 sec)
6）删除字段
命令格式：ALTER TABLE 表名 DROP 属性名;
mysql> describe num; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | name | char(20) | NO | | NULL | | | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | sex | char(10) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ rows in set (0.00 sec) mysql> alter table num drop name; //删除属性name Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe num; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | num_id | int(11) | NO | PRI | NULL | auto_increment | | stu_id | int(10) | NO | PRI | NULL | | | sex | char(10) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ rows in set (0.00 sec)
