MySQL 创建表与数据约束

建立数据库之后，下一步就是构建它的结构以便存储具体的信息。这种结构由表（Tables）来定义。表将数据组织成行和列，这与电子表格（如 Excel）非常相似。

要正确地设计表，你需要了解如何定义列、分配数据类型，以及如何实施约束（Constraints）来确保数据的完整性和关联性。

1. 定义表结构与数据类型

创建表的过程包括：指定表的名称，然后为每一列定义一个唯一的名称以及合适的数据类型。正如我们在上一章“MySQL 数据类型”中所讨论的，数据类型决定了列可以存储哪种值（例如：数字、文本、日期）以及它需要多少存储空间。选择正确的数据类型对于提升存储效率和数据准确性至关重要。

1.1 CREATE TABLE 基础语法

创建表的基本 SQL 语法如下：

CREATE TABLE table_name (
    column1_name DATATYPE [CONSTRAINTS],
    column2_name DATATYPE [CONSTRAINTS],
    column3_name DATATYPE [CONSTRAINTS],
    -- ... 更多列
    [TABLE_CONSTRAINTS]
);

• table_name：你为新表指定的名称。
• column_name：表内每一列的名称。
• DATATYPE：该列的具体数据类型（例如 INT, VARCHAR(255), DATE）。
• CONSTRAINTS（列级约束）：应用于单列的规则，用于限制该列可以存储的数据类型。
• TABLE_CONSTRAINTS（表级约束）：应用于整个表的规则，通常涉及多个列。

1.2 示例：创建一个 Customers（客户）表

假设你要为一家在线书店建立数据库。一个最基础的表就是 Customers，用于存储客户信息。

CREATE TABLE Customers (
    customer_id INT,                  -- 每位客户的唯一标识符
    first_name VARCHAR(50),           -- 客户的名字
    last_name VARCHAR(50),            -- 客户的姓氏
    email VARCHAR(100),               -- 客户的电子邮箱地址
    registration_date DATE            -- 客户注册的日期
);

目前的这个 Customers 表允许使用各种数据类型：INT 用于数字 ID，VARCHAR 用于像姓名和邮箱这样长度可变的文本字符串，而 DATE 用于注册日期。

2. 主键 (Primary Key)：确保唯一标识

主键（Primary Key）是一个特殊的列（或一组列），它能够唯一地标识表中的每一行（即每一条记录）。它的存在是为了保证表中的每一条记录都能被清晰无误地找出来。一个表只能有一个主键。

主键的核心特征：

• 唯一性 (Uniqueness)：主键列中的每个值都必须是不一样的。任意两行不能拥有相同的主键值。
• 非空 (Non-NULL)：主键列不能包含 NULL（空）值。每一条记录都必须拥有一个具体的主键值。

2.1 列级主键

你可以使用 PRIMARY KEY 约束来将某一列指定为主键。如果它紧跟在列的定义之后，我们称之为列级约束。

CREATE TABLE Products (
    product_id INT PRIMARY KEY,      -- product_id 被设为主键
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    stock_quantity INT
);

在这个 Products（产品）表中，product_id 声明为主键。这意味着每个产品都必须有一个唯一的 product_id，且不能为 NULL。

2.2 表级主键

当主键由多个列组成（即复合主键），或者单纯为了代码更清晰时，通常会在表的最后定义主键，这被称为表级约束。

CREATE TABLE OrderItems (
    order_id INT,
    product_id INT,
    quantity INT,
    price_per_item DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id) -- 复合主键
);

在这里，order_id 和 product_id 的组合唯一标识了订单中的每一项。单独的 order_id 可能会出现多次（同一个订单包含多个不同产品），单独的 product_id 也可能出现多次（不同订单购买了同一个产品）。但是，对于每一行数据来说，(order_id, product_id) 这个组合必须是唯一的。

2.3 主键的自增属性 (AUTO_INCREMENT)

对于数字类型的主键，我们通常会使用 AUTO_INCREMENT。这个属性会在每次向表中插入新行时，自动生成一个唯一且连续递增的数字。这极大简化了数据录入并保证了绝对的唯一性。注意：AUTO_INCREMENT 列必须是主键或 UNIQUE（唯一）索引的一部分。

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT, -- 为新员工自动递增生成 ID
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

当你向 Employees（员工）表插入数据时，通常可以在 INSERT 语句中省略 employee_id，MySQL 会自动为你分配下一个可用的数字。

3. 其他核心数据约束

约束是强制应用在数据列表上的规则。它们确保了数据的准确性和可靠性。除了主键之外，其他常见的约束还包括：NOT NULL、UNIQUE、DEFAULT 和 CHECK。

3.1 非空约束 (NOT NULL)

NOT NULL 约束确保一列不能存储 NULL 值。这意味着在插入新行或更新现有行时，必须为该列提供一个具体的值。

CREATE TABLE Publishers (
    publisher_id INT PRIMARY KEY AUTO_INCREMENT,
    publisher_name VARCHAR(100) NOT NULL, -- 出版商名称不能为 NULL
    city VARCHAR(50)
);

在这个 Publishers（出版商）表中，publisher_name 必须始终有值。如果你尝试插入一行不带 publisher_name 的数据，或者将其更新为 NULL，系统将会报错。这对于确保关键信息的完整性非常重要。

3.2 唯一约束 (UNIQUE)

UNIQUE 约束确保该列中的所有值都是互不相同的。虽然主键也保证唯一性，但一个表可以有多个 UNIQUE 约束，却只能有一个主键。UNIQUE 列可以包含 NULL 值（且在 MySQL 中，多个 NULL 值通常被认为是彼此不冲突的）。

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL, -- 用户名必须唯一且不能为空
    email VARCHAR(100) UNIQUE,            -- 邮箱必须唯一，但允许为空 (NULL)
    password_hash VARCHAR(255) NOT NULL
);

在这里，username 必须是唯一的且不能为 NULL。email 如果填了，也必须是唯一的，但它允许留空（即 NULL）。这非常适合诸如邮箱地址或身份证号等字段，它们应该是唯一的，但在某些情况下用户可能没有提供。

3.3 默认值约束 (DEFAULT)

当在 INSERT（插入）操作中没有明确给出值时，DEFAULT 约束用于为该列提供一个默认值。

CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT (CURRENT_DATE()), -- 如果未指定，则默认为今天
    status VARCHAR(20) DEFAULT 'Pending'      -- 默认状态设为 'Pending' (待处理)
);

当有一个新订单添加到 Orders（订单）表时，如果没有提供 order_date，它将自动设置为当前日期。同样，status 也会默认设置为 'Pending'。这保证了数据一致性，并减少了常见值的重复手动录入。

3.4 检查约束 (CHECK)

CHECK 约束允许你定义一个条件，该列中的所有值都必须满足这个条件。如果插入或更新的值违反了该条件，操作将会失败。

CREATE TABLE ProductsInventory (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price >= 0), -- 价格不能是负数
    stock_quantity INT CHECK (stock_quantity >= 0) -- 库存数量不能是负数
);

在 ProductsInventory（产品库存）中，price 和 stock_quantity 必须大于或等于零。这个约束可以防止诸如负数价格或负数库存等无效数据被存入数据库。

4. 综合实战演示

让我们将这些概念运用到一个实际场景中：为一个大学的学生管理系统创建数据库表。

首先，确保你正在使用正确的数据库。如果你在之前的学习中创建了 university_db，请激活它：

USE university_db;

实战 1：Students (学生) 表

Students 表将存储每个学生的基本信息。

CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE()),
    major VARCHAR(50)
);

• student_id: INT PRIMARY KEY AUTO_INCREMENT 确保每个学生都有一个唯一且自动分配的 ID。
• first_name, last_name: VARCHAR(50) NOT NULL 确保必须提供姓名。
• email: VARCHAR(100) UNIQUE NOT NULL 确保每个学生邮箱唯一且不能留空。
• enrollment_date: DATE DEFAULT (CURRENT_DATE()) 如果未填写，自动设置为当前日期。
• major: VARCHAR(50) 允许选填专业（没有约束，默认允许 NULL）。

实战 2：Courses (课程) 表

Courses 表用于存储开设的每门课程的详细信息。

CREATE TABLE Courses (
    course_id VARCHAR(10) PRIMARY KEY, -- 课程 ID 是字符串，例如 'CS101', 'MA201'
    course_title VARCHAR(100) NOT NULL UNIQUE,
    credits INT NOT NULL CHECK (credits > 0 AND credits <= 6), -- 学分必须在 1 到 6 之间
    department VARCHAR(50) NOT NULL
);

• course_id: VARCHAR(10) PRIMARY KEY 使用自定义的字符串作为主键。
• course_title: VARCHAR(100) NOT NULL UNIQUE 确保课程名称必填且唯一。
• credits: INT NOT NULL CHECK (credits > 0 AND credits <= 6) 强制学分必须是 1 到 6 之间的正整数。
• department: VARCHAR(50) NOT NULL 确保必须指明所属院系。

实战 3：Instructors (讲师) 表

Instructors 表存储大学教职员工的信息。

CREATE TABLE Instructors (
    instructor_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT (CURRENT_DATE()),
    salary DECIMAL(10, 2) CHECK (salary >= 30000.00) -- 最低薪资为 30,000
);

• instructor_id: INT PRIMARY KEY AUTO_INCREMENT 提供唯一的自动 ID。
• email: VARCHAR(100) UNIQUE 邮箱必须唯一，但允许为空。
• salary: DECIMAL(10, 2) CHECK (salary >= 30000.00) 强制执行最低薪资标准。

通过这些例子，你可以看到如何组合不同的约束来建立坚固的表结构，从而如实反映现实世界的业务规则。