本案例要求熟悉MySQL库中表的字段修改,主要练习以下操作:
添加字段
修改字段名
修改字段类型
删除字段
实现此案例需要按照如下步骤进行。
步骤一:添加字段
1)为tea6表添加一个address字段
添加前:
mysql> DESC tea6;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加address字段:
mysql> ALTER TABLE tea6 ADD address varchar(48);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加后(默认作为最后一个字段):
mysql> DESC tea6;
+---------+-------------+------+-----+---------+-------+
| 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)
2)在tea6表的age列之后添加一个gender字段
添加操作:
mysql> ALTER TABLE tea6 ADD gender enum('boy','girl') AFTER age;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
确认添加结果:
mysql> DESC tea6;
+---------+--------------------+------+-----+---------+-------+
| 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)
步骤二:修改字段名和字段类型
将tea6表的gender字段改名为sex,并添加非空约束
修改操作:
mysql> ALTER TABLE tea6 CHANGE gender
-> sex enum('boy','girl') NOT NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
确认修改结果:
mysql> DESC tea6;
+---------+--------------------+------+-----+---------+-------+
| 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.01 sec)
步骤三:删除字段
删除tea6表中名为sex的字段:
mysql> ALTER TABLE tea6 DROP sex; //删除操作
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea6; //确认删除结果
+---------+-------------+------+-----+---------+-------+
| 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)