欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql数据库——修改表结构

程序员文章站 2022-03-11 16:47:37
修改表结构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 -> );Q...

修改表结构

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

确认添加结果

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

本文地址:https://blog.csdn.net/weixin_47062057/article/details/107639337