【MySQL基础-3.2】MySQL DDL 语句详解：数据表操作篇

在 MySQL 中，数据表是存储数据的核心结构。DDL（Data Definition Language，数据定义语言）提供了丰富的语句来定义和管理数据表。本文将深入探讨 MySQL 中用于数据表操作的 DDL 语句，包括创建、修改、删除表等操作，并通过示例代码帮助读者更好地理解和应用。

---

1. 创建数据表

在 MySQL 中，使用 CREATE TABLE 语句可以创建一个新的数据表。以下是创建表的基本语法：

CREATE TABLE [IF NOT EXISTS] table_name (
    column1_name data_type [column_constraints],
    column2_name data_type [column_constraints],
    ...
    [table_constraints]
) [ENGINE=storage_engine] [DEFAULT CHARSET=charset_name];

参数说明：

• IF NOT EXISTS：可选参数，避免表已存在时抛出错误。
• table_name：要创建的表名。
• column_name：列名。
• data_type：列的数据类型（如 INT, VARCHAR, DATE 等）。
• column_constraints：列的约束（如 PRIMARY KEY, NOT NULL, UNIQUE 等）。
• table_constraints：表级约束（如 PRIMARY KEY, FOREIGN KEY 等）。
• ENGINE：可选参数，指定存储引擎（如 InnoDB, MyISAM）。
• DEFAULT CHARSET：可选参数，指定表的默认字符集。

示例：

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

2. 修改数据表

在 MySQL 中，使用 ALTER TABLE 语句可以修改表的结构。以下是常见的修改操作：

2.1 添加列

ALTER TABLE table_name
ADD COLUMN column_name data_type [column_constraints];

示例：

ALTER TABLE users
ADD COLUMN age INT;

-- 可以添加多列
ALTER TABLE users
ADD address VARCHAR(200) NOT NULL,
ADD home_tel CHAR(11) NOT NULL;

2.2 修改列

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type [column_constraints];

示例：

ALTER TABLE users
MODIFY COLUMN email VARCHAR(150) NOT NULL;

2.3 删除列

ALTER TABLE table_name
DROP COLUMN column_name;

示例：

ALTER TABLE users
DROP COLUMN age;

2.4 重命名列

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name data_type [column_constraints];

示例：

ALTER TABLE users
CHANGE COLUMN username user_name VARCHAR(50) NOT NULL;

2.5 添加约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

示例：

ALTER TABLE users
ADD CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES roles(id);

---

3. 删除数据表

在 MySQL 中，使用 DROP TABLE 语句可以删除一个数据表。以下是删除表的基本语法：

DROP TABLE [IF EXISTS] table_name;

参数说明：

• IF EXISTS：可选参数，避免表不存在时抛出错误。

示例：

DROP TABLE IF EXISTS users;

---

4. 重命名数据表

在 MySQL 中，使用 RENAME TABLE 语句可以重命名一个数据表。以下是重命名表的基本语法：

RENAME TABLE old_table_name TO new_table_name;

示例：

RENAME TABLE users TO app_users;

---

5. 查看表结构

在 MySQL 中，使用 DESCRIBE 或 SHOW COLUMNS 语句可以查看表的结构。以下是查看表结构的基本语法：

DESCRIBE table_name;
-- 或
SHOW COLUMNS FROM table_name;

示例：

DESCRIBE users;

---

6. 查看创建表的 SQL 语句

在 MySQL 中，使用 SHOW CREATE TABLE 语句可以查看创建表时的详细 SQL 语句。以下是查看创建表语句的基本语法：

SHOW CREATE TABLE table_name;

示例：

SHOW CREATE TABLE users;

---

7. 清空数据表

在 MySQL 中，使用 TRUNCATE TABLE 语句可以清空表中的所有数据，但保留表结构。以下是清空表的基本语法：

TRUNCATE TABLE table_name;

示例：

TRUNCATE TABLE users;

---

8. 复制数据表

在 MySQL 中，可以通过 CREATE TABLE ... AS SELECT 语句复制表结构和数据。以下是复制表的基本语法：

CREATE TABLE new_table_name AS
SELECT * FROM old_table_name;

示例：

CREATE TABLE users_backup AS
SELECT * FROM users;

---

9. 表操作的最佳实践

1. 备份数据：在执行 DROP 或 TRUNCATE 操作前，务必备份数据。
2. 使用事务：在修改表结构时，建议开启事务以确保操作的原子性。
3. 避免频繁修改表结构：频繁修改表结构可能影响数据库性能，建议在设计阶段充分考虑表结构。
4. 合理使用索引：在创建表时，为常用查询字段添加索引以提高查询效率。

---

10. 结论

MySQL 的 DDL 语句提供了强大的功能来管理数据表结构。通过本文的介绍，读者应该能够熟练掌握创建、修改、删除、查看和复制表的基本操作。在实际应用中，合理使用这些语句可以大大提高数据库管理的效率和安全性。