mysql alter 命令_MySQL_ALTER命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

1)修改表名：表名可以在数据库中唯一标识一个table

命令格式：ALTER TABLE 旧名 RENAME 新名;

mysql>alter table course rename courses;

Query OK,0 rows affected (0.00sec)

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 | |

+--------+---------+------+-----+---------+----------------+rowsin set (0.00sec)

mysql> alter table num modify name char(20); //改变属性的数据类型

Query OK,0 rows affected (0.05sec)

Records:0 Duplicates: 0 Warnings: 0mysql>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 | |

+--------+----------+------+-----+---------+----------------+rowsin set (0.00 sec)

mysql> alter table num modify sex char(10) not null default 'male'; //改变属性的完整性约束条件

Query OK,0 rows affected (0.08sec)

Records:0 Duplicates: 0 Warnings: 0mysql>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 属性名 数据类型 DEFAULTvalue;

命令格式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.00sec)

mysql> alter table num modify sex char(10) default 'female'; //修改属性的默认值

Query OK,0 rows affected (0.03sec)

Records:0 Duplicates: 0 Warnings: 0mysql>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 | |

+--------+----------+------+-----+---------+----------------+rowsin set (0.00sec)

mysql> alter table num change name sex char(10); //修改属性名称

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql>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 | |

+--------+----------+------+-----+---------+----------------+rowsin 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 | |

+--------+----------+------+-----+---------+----------------+rowsin set (0.00sec)

mysql> alter table num add name char(20) NOT NULL FIRST; //新增一个属性

Query OK,0 rows affected (0.05sec)

Records:0 Duplicates: 0 Warnings: 0mysql>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 | |

+--------+----------+------+-----+---------+----------------+rowsin 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 | |

+--------+----------+------+-----+---------+----------------+rowsin set (0.00sec)

mysql>alter table num drop name; //删除属性name

Query OK,0 rows affected (0.09sec)

Records:0 Duplicates: 0 Warnings: 0mysql>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 | |

+--------+----------+------+-----+---------+----------------+rowsin set (0.00 sec)