第11章_数据库MySQL

授课语音

表结构修改、字段添加与删除操作

在数据库设计和维护过程中，随着业务的变化，我们需要对现有的表结构进行修改，以适应新的需求。这包括添加新的字段、删除不再需要的字段、修改字段的数据类型或约束等。MySQL提供了一系列的SQL语句来完成这些操作，确保数据表能够灵活适应变化，同时不影响现有数据的完整性。

1. 表结构修改的基本概念

表结构修改是指对数据库中已有表的结构进行更改，包括字段的增删改等操作。通常，数据库表结构修改的操作是不可逆的，因此在进行修改时要特别小心，特别是在生产环境中进行操作时，必须提前备份数据。

在MySQL中，表结构修改通常使用ALTER TABLE语句。它可以执行多种不同的操作，如添加字段、删除字段、修改字段类型、重命名字段、修改表的约束等。

2. 字段添加操作

2.1 添加新的字段

当需要向表中添加新的字段时，可以使用ALTER TABLE语句中的ADD COLUMN子句。

代码示例：添加字段

-- 向`users`表中添加一个名为`email`的字段，类型为`VARCHAR(255)`
ALTER TABLE users
ADD COLUMN email VARCHAR(255);

中文注释：

ALTER TABLE users：指定修改的表是users表。
ADD COLUMN email VARCHAR(255)：表示向users表中添加一个名为email的字段，字段类型为VARCHAR，最大长度为255个字符。

2.2 添加带默认值的字段

如果我们想要给新字段添加默认值，可以在ADD COLUMN语句中指定DEFAULT值。

代码示例：添加带默认值的字段

-- 向`users`表中添加一个名为`status`的字段，类型为`TINYINT`，并设定默认值为1
ALTER TABLE users
ADD COLUMN status TINYINT DEFAULT 1;

中文注释：

status字段被添加到users表中，类型为TINYINT，并且默认值为1。

3. 字段删除操作

3.1 删除字段

删除字段时，使用ALTER TABLE语句中的DROP COLUMN子句。删除字段会导致该字段的数据永久丢失，因此执行此操作前必须谨慎。

代码示例：删除字段

-- 删除`users`表中的`email`字段
ALTER TABLE users
DROP COLUMN email;

中文注释：

DROP COLUMN email表示删除表中的email字段。

4. 字段修改操作

4.1 修改字段的数据类型

当我们需要更改字段的数据类型时，可以使用MODIFY COLUMN子句。此操作常用于调整字段大小或数据类型，例如将VARCHAR(255)调整为TEXT类型。

代码示例：修改字段数据类型

-- 修改`users`表中`email`字段的类型，将`VARCHAR(255)`更改为`TEXT`
ALTER TABLE users
MODIFY COLUMN email TEXT;

中文注释：

MODIFY COLUMN email TEXT表示修改users表中的email字段的数据类型为TEXT，它的最大长度不再受限制。

4.2 修改字段名称

如果需要重命名字段，可以使用CHANGE COLUMN语句。CHANGE COLUMN语句不仅需要指定新字段的名称，还需要指定字段的类型。

代码示例：修改字段名称

-- 修改`users`表中`email`字段的名称为`user_email`
ALTER TABLE users
CHANGE COLUMN email user_email VARCHAR(255);

中文注释：

CHANGE COLUMN email user_email VARCHAR(255)表示将email字段重命名为user_email，并保持其数据类型为VARCHAR(255)。

4.3 修改字段的默认值

在修改字段的默认值时，我们可以使用ALTER COLUMN语句。

代码示例：修改字段默认值

-- 修改`users`表中`status`字段的默认值为0
ALTER TABLE users
ALTER COLUMN status SET DEFAULT 0;

中文注释：

ALTER COLUMN status SET DEFAULT 0表示将status字段的默认值修改为0。

5. 实际应用：表结构修改示例

在实际开发中，我们常常需要根据业务需求对表结构进行灵活调整。以下是一个常见的商城系统中，针对products表的表结构修改示例。

5.1 添加字段：增加商品描述

为了扩展商品信息，我们决定为products表添加一个新的字段description，用来存储商品的详细描述。

ALTER TABLE products
ADD COLUMN description TEXT;

中文注释：

description字段是TEXT类型，用于存储商品的详细描述。

5.2 删除字段：删除冗余字段

假设products表中的color_code字段已经不再使用，我们需要将其删除：

ALTER TABLE products
DROP COLUMN color_code;

中文注释：

删除color_code字段，因为它已不再使用。

5.3 修改字段：修改价格字段的数据类型

在数据库设计初期，我们给price字段设置了DECIMAL(5,2)类型，但是为了存储更高精度的价格数据，我们决定将其修改为DECIMAL(10,2)。

ALTER TABLE products
MODIFY COLUMN price DECIMAL(10,2);

中文注释：

price字段的数据类型由DECIMAL(5,2)修改为DECIMAL(10,2)，以支持更高的价格精度。

6. 总结

字段添加：使用ALTER TABLE ... ADD COLUMN语句可以向现有表中添加新的字段，可以指定字段类型、默认值等。
字段删除：使用ALTER TABLE ... DROP COLUMN语句删除不再需要的字段，注意数据丢失的风险。
字段修改：可以修改字段的类型、名称、默认值等，常用的操作包括MODIFY COLUMN、CHANGE COLUMN和ALTER COLUMN。
谨慎操作：表结构修改是不可逆的操作，因此在执行这些操作时，务必提前备份数据库，避免数据丢失。