在 MySQL 中创建数据库和表涉及多个步骤，并涵盖了不同的场景。以下是涵盖各种场景的全面指南，包括创建数据库、创建表、添加数据类型、设置外键、设置索引等。

1. 创建数据库

创建数据库是第一步。在 MySQL 中，你可以使用以下命令创建一个新的数据库：

CREATE DATABASE my_database;

如果数据库已经存在，且你不希望报错，可以使用 IF NOT EXISTS：

CREATE DATABASE IF NOT EXISTS my_database;

要使用该数据库，可以执行：

USE my_database;

2. 创建表

创建表时，必须定义表的列（字段）及其数据类型、约束等。

基本的表创建

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自动递增主键
    username VARCHAR(50) NOT NULL,      -- 用户名，最大长度50，不能为空
    email VARCHAR(100),                 -- 邮箱，最大长度100
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 默认当前时间
);

带有外键的表

创建表时，通常需要涉及外键以保持数据库的完整性。例如，orders 表可能会引用 users 表中的 id 字段。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,   -- 自动递增主键
    user_id INT,                               -- 外键字段，指向 users 表
    order_date DATE,
    amount DECIMAL(10, 2),
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id)  -- 外键约束
);

• fk_user_id 是外键的名称，它将 user_id 字段连接到 users 表的 id 字段。
• DECIMAL(10, 2) 表示一个数值，最多有 10 位数，其中 2 位是小数。

3. 添加多列约束

在创建表时，可以为列添加各种约束以确保数据的完整性和一致性。

NOT NULL

确保字段不能为空：

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,  -- 名称不能为空
    price DECIMAL(10, 2) NOT NULL        -- 价格不能为空
);

UNIQUE 约束

UNIQUE 约束确保一列的值在表中是唯一的。例如，邮箱必须唯一：

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE    -- 邮箱必须唯一
);

DEFAULT 值

为字段设置默认值：

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active'  -- 默认为 'active'
);

4. 创建带索引的表

索引用于加速查询。可以在创建表时直接为列添加索引，也可以在创建表后添加索引。

创建时添加索引

CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    author_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (author_id)  -- 为 author_id 列添加索引
);

创建表后添加索引

如果需要在表创建后添加索引，可以使用以下命令：

CREATE INDEX idx_author ON posts(author_id);

5. 修改表结构

当表已经创建，但需要修改表结构时，可以使用 ALTER TABLE 命令。

添加新列

ALTER TABLE users ADD COLUMN age INT;

修改列的数据类型

ALTER TABLE users MODIFY COLUMN age SMALLINT;

删除列

ALTER TABLE users DROP COLUMN age;

6. 处理复杂数据类型

ENUM 类型

ENUM 用于定义只能取特定值的字段：

CREATE TABLE membership (
    membership_id INT AUTO_INCREMENT PRIMARY KEY,
    membership_type ENUM('basic', 'premium', 'vip') DEFAULT 'basic'  -- 只能为指定值之一
);

SET 类型

SET 类型允许列同时存储多个值（这些值必须在预定义的集合中）：

CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY,
    preferences SET('email', 'sms', 'push_notification')  -- 可以包含多个选项
);

7. 使用外键约束

使用外键时，可以在父表删除记录时指定相关记录的处理方式。

ON DELETE CASCADE

当父表中的记录被删除时，子表中的相关记录也会被删除：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE  -- 级联删除
);

ON DELETE SET NULL

当父表中的记录被删除时，子表中的外键列会被设置为 NULL：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL  -- 父记录删除时设为 NULL
);

8. 表分区

在大型数据库中，可以通过分区来提高性能。MySQL 支持基于范围、列表或哈希的表分区。

范围分区

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2015),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

9. 插入数据

创建表后，你可以插入数据。

插入单条记录

INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

插入多条记录

INSERT INTO users (username, email) VALUES 
('alice', 'alice@example.com'),
('bob', 'bob@example.com');

10. 创建视图

视图是存储的查询，它可以简化复杂查询的使用。

CREATE VIEW user_orders AS
SELECT u.username, o.order_id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

11. 删除表和数据库

删除表

DROP TABLE IF EXISTS users;

删除数据库

DROP DATABASE IF EXISTS my_database;

总结

• 创建数据库和表时需要考虑数据类型、约束、索引和外键等。
• 使用 ALTER TABLE 修改表结构。
• 可以通过 CREATE INDEX 和分区等技术提升查询性能。
• MySQL 提供了丰富的数据类型、约束和功能来处理多样化的场景。