MySQL库中表的字段修改

• 添加字段
• 修改字段名
• 修改字段类型
• 删除字段

步骤一：添加字段

在studb中创建tea5表

mysql> CREATE TABLE studb.tea5(id int (4) PRIMARY KEY,name varchar(4) NOT NULL,age innt(2) NOT NULL);Query OK, 0 rows affected (0.01 sec)

为tea5表添加一个address字段

mysql> ALTER TABLE tea5 ADD address varchar(48);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0

在tea5表的age列之后添加一个gender字段

添加操作：

mysql> ALTER TABLE tea5 ADD gender enum(\'boy\',\'girl\') AFTER age;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0

确认添加结果：

mysql> DESC tea5;+---------+--------------------+------+-----+---------+-------+| Field   | Type               | Null | Key | Default | Extra |+---------+--------------------+------+-----+---------+-------+| id      | int(4)             | NO   | PRI | NULL    |       || name    | varchar(4)         | NO   |     | NULL    |       || age     | int(2)             | NO   |     | NULL    |       || gender  | enum(\'boy\',\'girl\') | YES  |     | NULL    |       || address | varchar(48)        | YES  |     | NULL    |       |+---------+--------------------+------+-----+---------+-------+5 rows in set (0.00 sec)

步骤二：修改字段名和字段类型

将tea5表的gender字段改名为sex，并添加非空约束

修改操作：

mysql> ALTER TABLE tea5 CHANGE gender sex enum(\'boy\',\'girl\') NOT NULL;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0

确认修改结果：

mysql> DESC tea5;+---------+--------------------+------+-----+---------+-------+| Field   | Type               | Null | Key | Default | Extra |+---------+--------------------+------+-----+---------+-------+| id      | int(4)             | NO   | PRI | NULL    |       || name    | varchar(4)         | NO   |     | NULL    |       || age     | int(2)             | NO   |     | NULL    |       || sex     | enum(\'boy\',\'girl\') | NO   |     | NULL    |       || address | varchar(48)        | YES  |     | NULL    |       |+---------+--------------------+------+-----+---------+-------+5 rows in set (0.00 sec)

步骤三：删除字段

删除tea5表中名为sex的字段：

mysql> ALTER TABLE tea5 DROP sex;      //删除操作Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESC tea5;        //确认删除结果+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| id      | int(4)      | NO   | PRI | NULL    |       || name    | varchar(4)  | NO   |     | NULL    |       || age     | int(2)      | NO   |     | NULL    |       || address | varchar(48) | YES  |     | NULL    |       |+---------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)