mysql数据库——修改表结构

墨蓝 2023-03-02 10:10 1阅读 0赞

## 修改表结构 ##

#### 1 问题 ####

本案例要求熟悉MySQL库中表的字段修改，主要练习以下操作：  
• 添加字段  
• 修改字段名  
• 修改字段类型  
• 删除字段

#### 2 步骤 ####

实现此案例需要按照如下步骤进行。  
**步骤一：添加字段**  
在studb中创建tea6表

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

为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.84 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)

在tea6表的age列之后添加一个gender字段  
添加操作：

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

确认添加结果

**步骤二：修改字段名和字段类型**  
将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.00 sec)

**步骤三：删除字段**  
删除tea6表中名为sex的字段：

mysql> ALTER TABLE tea6 DROP sex;                             //删除操作
    Query OK, 0 rows affected (0.52 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)