MySQL 创建表详解：从入门到精通

MySQL 是一个广泛使用的开源关系型数据库管理系统 (RDBMS)。创建表是 MySQL 中最基本也是最重要的操作之一。一张表是数据的逻辑容器，它定义了数据的结构，包括列（字段）、数据类型、约束等。本文将详细介绍如何在 MySQL 中创建表，包括基本语法、数据类型、约束条件、以及一些高级技巧，帮助你从入门到精通 MySQL 表的创建。

准备工作

在开始创建表之前，你需要确保已经安装并正确配置了 MySQL 服务器，并且拥有可以连接和操作数据库的账号。你可以通过以下步骤进行准备：

安装 MySQL: 根据你的操作系统，下载并安装 MySQL Community Server。官方网站提供各种平台的安装包：https://dev.mysql.com/downloads/
启动 MySQL 服务: 安装完成后，启动 MySQL 服务。启动方法取决于你的操作系统（例如，在 Windows 上，可以在服务管理器中启动；在 Linux 上，可以使用 `systemctl start mysqld` 命令）。
连接到 MySQL 服务器: 使用 MySQL 客户端工具（例如 `mysql` 命令行工具，或图形化界面工具如 MySQL Workbench、Dbeaver 等）连接到 MySQL 服务器。你需要提供服务器地址、端口、用户名和密码。

创建数据库 (如果需要)

在创建表之前，你需要选择一个数据库。如果还没有数据库，可以使用以下 SQL 命令创建一个新的数据库：

CREATE DATABASE IF NOT EXISTS my_database;

这条命令会创建一个名为 `my_database` 的数据库。 `IF NOT EXISTS` 子句用于避免数据库已经存在时报错。

创建数据库之后，需要选择使用该数据库：

USE my_database;

CREATE TABLE 语句的基本语法

创建表的 SQL 语句是 `CREATE TABLE`。它的基本语法如下：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
    table_constraints
);

其中：

table_name 是你想要创建的表的名称。
column1, column2, column3 等是表的列名。
datatype 是列的数据类型（例如，INT, VARCHAR, DATE）。
constraints 是列的约束条件（例如，NOT NULL, PRIMARY KEY, UNIQUE）。
table_constraints 是表的约束条件（例如，FOREIGN KEY）。

常用的数据类型

MySQL 提供了丰富的数据类型，用于存储不同类型的数据。以下是一些常用的数据类型：

整数类型:
- TINYINT: 非常小的整数，占用 1 字节。
- SMALLINT: 小整数，占用 2 字节。
- MEDIUMINT: 中等大小的整数，占用 3 字节。
- INT (或 INTEGER): 整数，占用 4 字节。
- BIGINT: 大整数，占用 8 字节。
浮点数类型:
- FLOAT: 单精度浮点数。
- DOUBLE (或 REAL): 双精度浮点数。
字符串类型:
- CHAR(length): 固定长度字符串，最大长度为 255 个字符。
- VARCHAR(length): 可变长度字符串，最大长度为 65535 个字符。推荐使用VARCHAR，因为可以节约存储空间，但是需要注意长度的设置，不能超过上限。
- TEXT: 长文本字符串，最大长度为 65535 个字符。
- MEDIUMTEXT: 中等长度文本字符串，最大长度为 16777215 个字符。
- LONGTEXT: 长文本字符串，最大长度为 4294967295 个字符。
日期和时间类型:
- DATE: 日期，格式为 YYYY-MM-DD。
- TIME: 时间，格式为 HH:MM:SS。
- DATETIME: 日期和时间，格式为 YYYY-MM-DD HH:MM:SS。
- TIMESTAMP: 时间戳，存储自 1970-01-01 00:00:00 UTC 以来的秒数。
- YEAR: 年份，格式为 YYYY。
布尔类型:
- BOOLEAN: 实际上存储为 TINYINT(1)，0 表示 false，非 0 表示 true。
ENUM 类型：
- ENUM('value1', 'value2', ...): 只能存储预定义值列表中的一个值。例如 `ENUM(‘male’, ‘female’, ‘other’)`
SET 类型：
- SET('value1', 'value2', ...): 可以存储预定义值列表中的一个或多个值。例如 `SET(‘reading’, ‘writing’, ‘coding’)`
JSON 类型：
- JSON: 存储 JSON 数据。

常用的约束条件

约束条件用于限制表中数据的有效性，确保数据的完整性和一致性。以下是一些常用的约束条件：

NOT NULL: 指定列不能为空。
PRIMARY KEY: 指定列为主键，用于唯一标识表中的每一行。一个表只能有一个主键。主键列的值必须唯一且不能为空。
UNIQUE: 指定列的值必须唯一。
DEFAULT: 指定列的默认值。如果在插入新行时没有为该列提供值，则使用默认值。
AUTO_INCREMENT: 用于整数类型，指定列的值自动递增。通常用于主键列。
FOREIGN KEY: 指定列为外键，用于建立表之间的关系。外键列的值必须引用另一个表的主键列的值。
CHECK: 指定列的值必须满足某个条件。MySQL 8.0.16 及更高版本支持 CHECK 约束。

创建表的示例

以下是一个创建 `users` 表的示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

这个示例创建了一个名为 `users` 的表，包含以下列：

id: 整数类型，自动递增，为主键。
username: 字符串类型，最大长度为 50 个字符，不能为空，且必须唯一。
email: 字符串类型，最大长度为 100 个字符，不能为空。
password: 字符串类型，最大长度为 255 个字符，不能为空。
created_at: 时间戳类型，默认值为当前时间。

以下是一个创建 `posts` 表的示例，该表与 `users` 表有外键关系：

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

这个示例创建了一个名为 `posts` 的表，包含以下列：

id: 整数类型，自动递增，为主键。
user_id: 整数类型，不能为空，且为外键，引用 `users` 表的 `id` 列。
title: 字符串类型，最大长度为 200 个字符，不能为空。
content: 文本类型。
created_at: 时间戳类型，默认值为当前时间。

更复杂的例子：订单表

让我们创建一个更复杂的例子，一个 `orders` 订单表，包含与订单相关的信息，以及与客户和产品的关系：

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(20),
    address VARCHAR(255)
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    shipping_address VARCHAR(255),
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

这个例子包含四个表：

customers: 存储客户信息。
products: 存储产品信息。
orders: 存储订单信息，包括客户 ID、订单日期、总金额、运输地址和订单状态。
order_items: 存储订单中的每个项目，包括订单 ID、产品 ID、数量和单价。

注意以下几点：

使用 DECIMAL(10, 2) 来存储货币值，其中 10 是总位数，2 是小数位数。
使用 ENUM 类型来存储订单状态，限制可能的值。
使用 FOREIGN KEY 约束来建立表之间的关系。

ALTER TABLE 语句：修改表结构

创建表后，你可能需要修改表结构，例如添加、删除或修改列。可以使用 `ALTER TABLE` 语句来完成这些操作。

添加列

ALTER TABLE table_name
ADD COLUMN column_name datatype constraints;

例如，向 `users` 表添加一个 `age` 列：

ALTER TABLE users
ADD COLUMN age INT;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

例如，从 `users` 表删除 `age` 列：

ALTER TABLE users
DROP COLUMN age;

修改列的数据类型

ALTER TABLE table_name
MODIFY COLUMN column_name datatype constraints;

例如，将 `username` 列的数据类型从 `VARCHAR(50)` 修改为 `VARCHAR(100)`：

ALTER TABLE users
MODIFY COLUMN username VARCHAR(100) NOT NULL UNIQUE;

修改列的名称

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype constraints;

例如，将 `username` 列的名称修改为 `user_name`：

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

添加约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

例如，向 `posts` 表添加一个外键约束，引用 `users` 表的 `id` 列（如果之前没有添加）：

ALTER TABLE posts
ADD CONSTRAINT fk_posts_user_id
FOREIGN KEY (user_id) REFERENCES users(id);

删除约束

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

例如，删除 `posts` 表的 `fk_posts_user_id` 外键约束：

ALTER TABLE posts
DROP FOREIGN KEY fk_posts_user_id;

DROP TABLE 语句：删除表

如果你不再需要某个表，可以使用 `DROP TABLE` 语句将其删除。注意：删除表是一个不可逆的操作，所有表中的数据都将被永久删除。

DROP TABLE table_name;

例如，删除 `users` 表：

DROP TABLE users;

为了避免删除不存在的表时报错，可以使用 `IF EXISTS` 子句：

DROP TABLE IF EXISTS users;

创建表时需要注意的事项

选择合适的数据类型: 根据数据的类型和范围选择合适的数据类型，以提高存储效率和查询性能。
使用适当的约束条件: 使用适当的约束条件来确保数据的完整性和一致性。
考虑性能: 合理设计表结构，例如使用索引来提高查询性能。
命名规范: 使用清晰、一致的命名规范，提高代码的可读性和可维护性。
规范化: 遵循数据库规范化原则，减少数据冗余，提高数据一致性。例如，将重复出现的地址信息单独存放在一个 `address` 表中，避免在多个表中重复存储地址信息。
备份: 定期备份数据库，以防止数据丢失。
索引： 适当使用索引能极大提升查询效率。但索引也会占用存储空间，并且在插入、更新、删除数据时需要维护索引，因此索引不是越多越好，需要根据实际情况进行权衡。
字符集和排序规则： 在创建数据库和表的时候，应该设置合适的字符集和排序规则，以确保能够正确存储和检索不同语言的数据。常用的字符集包括 `utf8mb4` (支持更广泛的 Unicode 字符) 和 `latin1`。排序规则决定了字符串的比较方式，例如区分大小写或不区分大小写。
分区： 对于大型表，可以考虑使用分区来提高查询性能和管理效率。分区是将表数据分割成更小的、更易于管理的部分，可以根据日期、范围或其他条件进行分区。

总结

创建表是 MySQL 中最基本也是最重要的操作之一。通过本文的学习，你应该已经掌握了如何在 MySQL 中创建表，包括基本语法、数据类型、约束条件、以及一些高级技巧。记住，良好的表设计是数据库性能和可维护性的关键。希望本文能帮助你更好地使用 MySQL。

创建表只是数据库设计的开始，还需要不断学习和实践，才能成为一名优秀的数据库开发者。

How to Do

Get clear, simple answers to all your questions. We resolve your doubts.

MySQL 创建表详解：从入门到精通

MySQL 创建表详解：从入门到精通

准备工作

创建数据库 (如果需要)

CREATE TABLE 语句的基本语法

常用的数据类型

常用的约束条件

创建表的示例

更复杂的例子：订单表

ALTER TABLE 语句：修改表结构

添加列

删除列

修改列的数据类型

修改列的名称

添加约束

删除约束

DROP TABLE 语句：删除表

创建表时需要注意的事项

总结