MySQL(39)如何使用ALTER TABLE修改表结构？

ALTER TABLE 语句在 SQL 中用于修改表的结构。它可以用于添加、删除或修改表的列、约束和索引。下面详细介绍如何使用 ALTER TABLE 语句来执行不同的操作，并结合代码进行说明。

1. 添加列

使用 ALTER TABLE ... ADD 语句可以向现有表中添加新列。

语法：

ALTER TABLE table_name
ADD column_name column_type [column_constraint];

示例：

假设我们有一个名为 employees 的表，现在我们希望向表中添加一个 email 列：

ALTER TABLE employees
ADD email VARCHAR(100);

添加后的表结构：

emp_id | emp_name      | emp_position | dept_id | hire_date  | email
-------|---------------|--------------|---------|------------|---------
1      | John Doe      | Active       | 1       | 2023-10-01 | NULL
2      | Jane Smith    | Active       | 1       | 2023-09-01 | NULL
3      | Alice Johnson | Active       | 2       | 2023-08-01 | NULL
4      | Bob Brown     | Inactive     | NULL    | 2023-07-01 | NULL

2. 删除列

使用 ALTER TABLE ... DROP COLUMN 语句可以从表中删除列。

语法：

ALTER TABLE table_name
DROP COLUMN column_name;

示例：

现在我们希望删除刚添加的 email 列：

ALTER TABLE employees
DROP COLUMN email;

删除后的表结构：

emp_id | emp_name      | emp_position | dept_id | hire_date
-------|---------------|--------------|---------|-----------
1      | John Doe      | Active       | 1       | 2023-10-01
2      | Jane Smith    | Active       | 1       | 2023-09-01
3      | Alice Johnson | Active       | 2       | 2023-08-01
4      | Bob Brown     | Inactive     | NULL    | 2023-07-01

3. 修改列类型或名称

使用 ALTER TABLE ... MODIFY 或 ALTER TABLE ... CHANGE 语句可以修改列的数据类型或名称。

修改列类型

语法：

ALTER TABLE table_name
MODIFY column_name new_column_type;

示例：

假设我们希望将 hire_date 列的数据类型从 DATE 修改为 DATETIME：

ALTER TABLE employees
MODIFY hire_date DATETIME;

修改后的表结构：

emp_id | emp_name      | emp_position | dept_id | hire_date
-------|---------------|--------------|---------|-------------------
1      | John Doe      | Active       | 1       | 2023-10-01 00:00:00
2      | Jane Smith    | Active       | 1       | 2023-09-01 00:00:00
3      | Alice Johnson | Active       | 2       | 2023-08-01 00:00:00
4      | Bob Brown     | Inactive     | NULL    | 2023-07-01 00:00:00

修改列名称

语法：

ALTER TABLE table_name
CHANGE old_column_name new_column_name new_column_type;

示例：

假设我们希望将 emp_name 列的名称修改为 employee_name：

ALTER TABLE employees
CHANGE emp_name employee_name VARCHAR(100);

修改后的表结构：

emp_id | employee_name | emp_position | dept_id | hire_date
-------|---------------|--------------|---------|-------------------
1      | John Doe      | Active       | 1       | 2023-10-01 00:00:00
2      | Jane Smith    | Active       | 1       | 2023-09-01 00:00:00
3      | Alice Johnson | Active       | 2       | 2023-08-01 00:00:00
4      | Bob Brown     | Inactive     | NULL    | 2023-07-01 00:00:00

4. 添加和删除约束

ALTER TABLE 语句还可以用于添加和删除表的约束，例如主键、外键、唯一约束等。

添加主键

语法：

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

示例：

假设我们希望将 emp_id 列设置为主键：

ALTER TABLE employees
ADD PRIMARY KEY (emp_id);

添加外键

语法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
REFERENCES parent_table_name(parent_column_name);

示例：

假设我们希望将 employees 表的 dept_id 列设置为外键，指向 departments 表的 dept_id 列：

ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

删除约束

语法：

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

示例：

假设我们希望删除刚刚添加的外键约束：

ALTER TABLE employees
DROP CONSTRAINT fk_dept;

小结

通过 ALTER TABLE 语句，可以轻松地改变表的结构，包括添加、删除和修改列，以及添加和删除约束。上述示例展示了如何使用 ALTER TABLE 语句执行各种操作，以满足不同的数据库管理需求。以下是完整的示例代码，展示了上述操作：

完整示例代码

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建表 employees
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    dept_id INT,
    hire_date DATE
);

-- 创建表 departments
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- 插入示例数据到 departments 表
INSERT INTO departments (dept_name)
VALUES 
('Engineering'),
('Research'),
('Marketing');

-- 插入示例数据到 employees 表
INSERT INTO employees (emp_name, emp_position, dept_id, hire_date)
VALUES 
('John Doe', 'Manager', 1, '2023-10-01'),
('Jane Smith', 'Developer', 1, '2023-09-01'),
('Alice Johnson', 'Analyst', 2, '2023-08-01'),
('Bob Brown', 'Developer', NULL, '2023-07-01');

-- 添加列 email
ALTER TABLE employees
ADD email VARCHAR(100);

-- 删除列 email
ALTER TABLE employees
DROP COLUMN email;

-- 修改 hire_date 列类型为 DATETIME
ALTER TABLE employees
MODIFY hire_date DATETIME;

-- 修改 emp_name 列名称为 employee_name
ALTER TABLE employees
CHANGE emp_name employee_name VARCHAR(100);

-- 添加主键到 emp_id 列
ALTER TABLE employees
ADD PRIMARY KEY (emp_id);

-- 添加外键到 dept_id 列
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- 删除外键约束
ALTER TABLE employees
DROP CONSTRAINT fk_dept;

这些示例展示了如何使用 ALTER TABLE 语句来修改表结构，以满足不同的业务需求。