MySQL 中的 DDL 库操作详解

在 MySQL 中，数据定义语言（DDL，Data Definition Language）是指用于定义和修改数据库结构的 SQL 语句。DDL 操作包括创建、修改和删除数据库、表、索引等对象。本文将从零开始讲解 MySQL 中的 DDL 操作，帮助初学者理解和掌握相关的基本命令和用法。

1. 创建数据库

在 MySQL 中，创建数据库是最基础的操作之一。可以使用 CREATE DATABASE 语句来创建一个新数据库。数据库是存储数据表、视图、索引等对象的容器。

语法：

CREATE DATABASE [IF NOT EXISTS] database_name
    [DEFAULT CHARACTER SET charset_name]
    [DEFAULT COLLATE collation_name];

• IF NOT EXISTS：如果数据库已经存在，则不执行任何操作。
• database_name：指定要创建的数据库名称。
• DEFAULT CHARACTER SET：指定数据库的默认字符集。
• DEFAULT COLLATE：指定数据库的默认字符排序规则。

示例：

CREATE DATABASE IF NOT EXISTS my_database
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_general_ci;

这条语句会创建一个名为 my_database 的数据库，并设置默认字符集为 utf8mb4 和字符排序规则为 utf8mb4_general_ci。

2. 删除数据库

删除数据库是通过 DROP DATABASE 语句实现的。当删除数据库时，数据库中的所有数据将被丢失。

语法：

DROP DATABASE [IF EXISTS] database_name;

• IF EXISTS：如果数据库存在，则删除该数据库；如果数据库不存在，则不执行任何操作。

示例：

DROP DATABASE IF EXISTS my_database;

这条语句会删除 my_database 数据库，前提是它存在。

3. 创建数据表

在 MySQL 中，数据表是数据库中存储数据的基本单元。使用 CREATE TABLE 语句可以创建新的数据表。

语法：

CREATE TABLE table_name (
    column_name1 column_definition1,
    column_name2 column_definition2,
    ...
    [table_constraints]
);

• table_name：指定表的名称。
• column_name：列的名称。
• column_definition：列的定义，包括数据类型和约束。
• table_constraints：可选的表约束，如主键、外键等。

示例：

CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

在这个示例中，我们创建了一个名为 users 的数据表，包含了以下字段：

• user_id：自增的整数主键。
• username：用户名，不能为空。
• email：用户的电子邮件。
• created_at：记录创建时间，默认为当前时间。

4. 修改数据表结构

在 MySQL 中，使用 ALTER TABLE 语句可以修改已存在的表。通过 ALTER TABLE，可以增加、删除或修改列，添加约束，重命名表等。

语法：

ALTER TABLE table_name
    [ADD column_name column_definition]
    [DROP COLUMN column_name]
    [MODIFY column_name column_definition]
    [RENAME TO new_table_name]
    [ADD CONSTRAINT constraint_name];

示例：

• 添加列：

  ALTER TABLE users ADD age INT;
  

  这条语句会在 users 表中添加一个 age 列。

• 删除列：

  ALTER TABLE users DROP COLUMN age;
  

  这条语句会删除 users 表中的 age 列。

• 修改列：

  ALTER TABLE users MODIFY username VARCHAR(150) NOT NULL;
  

  这条语句会修改 username 列的长度为 150，并且设为非空。

• 重命名表：

  ALTER TABLE users RENAME TO user_accounts;
  

  这条语句会将 users 表重命名为 user_accounts。

5. 删除数据表

使用 DROP TABLE 语句可以删除已存在的表。注意，删除表会永久丢失表中的所有数据。

语法：

DROP TABLE [IF EXISTS] table_name;

示例：

DROP TABLE IF EXISTS users;

这条语句会删除 users 表，前提是表存在。

6. 索引操作

索引是数据库优化查询的一种方式。通过索引，可以提高数据表中数据检索的效率。MySQL 支持多种类型的索引，包括唯一索引、主键索引和全文索引等。

创建索引

使用 CREATE INDEX 语句创建索引：

CREATE INDEX index_name ON table_name (column_name);

示例：

CREATE INDEX idx_username ON users (username);

这条语句会在 users 表的 username 列上创建一个名为 idx_username 的索引。

删除索引

使用 DROP INDEX 语句删除索引：

DROP INDEX index_name ON table_name;

示例：

DROP INDEX idx_username ON users;

这条语句会删除 users 表中的 idx_username 索引。

7. 外键约束

外键约束用于建立表与表之间的关联。通过外键约束，可以确保数据的完整性和一致性。

语法：

ALTER TABLE table_name
    ADD CONSTRAINT fk_name
    FOREIGN KEY (column_name)
    REFERENCES parent_table (parent_column);

示例：

ALTER TABLE orders
    ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(user_id);

这条语句会在 orders 表中创建一个外键约束，确保 user_id 列中的值必须在 users 表的 user_id 列中存在。

8. 视图操作

视图是基于 SELECT 查询结果创建的虚拟表。在 MySQL 中，视图的创建和删除也属于 DDL 操作。

创建视图

使用 CREATE VIEW 语句创建视图：

CREATE VIEW view_name AS
    SELECT column1, column2
    FROM table_name
    WHERE condition;

示例：

CREATE VIEW active_users AS
    SELECT user_id, username
    FROM users
    WHERE status = 'active';

这条语句创建了一个名为 active_users 的视图，用于存储所有状态为 "active" 的用户。

删除视图

使用 DROP VIEW 语句删除视图：

DROP VIEW [IF EXISTS] view_name;

示例：

DROP VIEW IF EXISTS active_users;

总结

本文详细介绍了 MySQL 中常见的 DDL 操作，包括数据库和表的创建与删除，表结构的修改，索引的管理，外键约束的使用以及视图的操作。掌握这些基本操作，是构建和管理 MySQL 数据库的重要步骤。