在 MySQL 中使用创建表

在 MySQL 中，CREATE TABLE 语句用于创建一个新表，表是数据库的基本存储单位，用于组织和管理数据。创建表的过程涉及定义表的名称、列、数据类型、约束条件等。本文将详细介绍如何在 MySQL 中使用 CREATE TABLE 语句，包括其基本语法、常用数据类型、约束条件、创建表的选项以及常见的操作示例。

1. 基本语法

在 MySQL 中，CREATE TABLE 的基本语法如下：

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

• table_name：指定表的名称。
• column1, column2, …：列的名称和数据类型。
• datatype：列的数据类型，例如 INT, VARCHAR, DATE 等。
• constraints：列的约束条件，例如 NOT NULL, UNIQUE, DEFAULT 等。
• table_constraints：表级别的约束条件，例如主键（PRIMARY KEY）、外键（FOREIGN KEY）等。

2. 数据类型

在 MySQL 中，有多种数据类型可供选择，以适应不同的需求：

• 整型：

  • TINYINT：小整数，范围从 -128 到 127。
  • SMALLINT：小整数，范围从 -32,768 到 32,767。
  • MEDIUMINT：中等大小的整数，范围从 -8,388,608 到 8,388,607。
  • INT：标准整数，范围从 -2,147,483,648 到 2,147,483,647。
  • BIGINT：大整数，范围从 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
  • SERIAL：自动递增的整数类型，适用于主键。

• 浮点型：

  • FLOAT：单精度浮点数。
  • DOUBLE：双精度浮点数。
  • DECIMAL(M, D)：精确的浮点数类型，其中 M 是总位数，D 是小数位数。

• 字符型：

  • CHAR(N)：定长字符型，N 指定长度。
  • VARCHAR(N)：变长字符型，N 指定最大长度。
  • TEXT：可变长度字符型，用于存储大文本。

• 日期和时间型：

  • DATE：日期类型，格式为 YYYY-MM-DD。
  • DATETIME：日期和时间类型，格式为 YYYY-MM-DD HH:MM:SS。
  • TIMESTAMP：时间戳类型，通常用于记录数据的创建或修改时间。
  • TIME：时间类型，格式为 HH:MM:SS。
  • YEAR：年份类型，格式为 YYYY。

• 布尔型：

  • BOOLEAN：布尔值类型，存储 TRUE 或 FALSE。

3. 约束条件

约束条件用于确保数据的完整性和一致性，常见的约束条件包括：

• PRIMARY KEY：主键约束，唯一标识表中的每一行数据，并且不能为 NULL。
• FOREIGN KEY：外键约束，用于建立表之间的关联，并确保引用完整性。
• UNIQUE：唯一约束，确保列中的值唯一。
• NOT NULL：非空约束，确保列中的值不能为空。
• CHECK：检查约束，确保列中的值符合指定的条件。
• DEFAULT：默认值约束，为列指定默认值。

4. 创建表的选项

在创建表时，可以指定一些选项来调整表的行为：

• 自动递增：用于自增列，例如主键列：

  CREATE TABLE users (
      user_id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(50) NOT NULL,
      email VARCHAR(100)
  );
  

• 表的引擎：指定存储引擎，例如 InnoDB 或 MyISAM：

  CREATE TABLE orders (
      order_id INT AUTO_INCREMENT PRIMARY KEY,
      product_name VARCHAR(100),
      quantity INT,
      order_date DATE,
      ENGINE=InnoDB
  );
  

• 字符集：指定表的字符集和排序规则：

  CREATE TABLE comments (
      comment_id INT AUTO_INCREMENT PRIMARY KEY,
      content TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  );
  

5. 示例

5.1 创建简单表

创建一个包含用户信息的简单表：

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

在这个示例中：

• emp_id 是自增的主键。
• first_name 和 last_name 是非空的字符串列。
• hire_date 列的默认值为当前日期。
• salary 列必须大于零。

5.2 创建带有外键约束的表

创建一个订单表，并与员工表建立外键关系：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON DELETE CASCADE
);

在这个示例中：

• order_id 是自增的主键。
• emp_id 是外键，引用 employees 表的 emp_id 列。
• order_date 列的默认值为当前时间。
• total_amount 列不能为空。

5.3 创建复杂约束的表

创建一个包含复杂约束的表：

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE,
    price DECIMAL(8, 2) NOT NULL CHECK (price > 0),
    stock INT DEFAULT 0 CHECK (stock >= 0)
);

在这个示例中：

• product_id 是自增的主键。
• product_name 列值唯一且不能为空。
• price 列不能为空且必须大于零。
• stock 列有默认值零，且必须大于或等于零。

6. 常见问题及解决方法

6.1 错误信息“表已经存在”

如果尝试创建一个已存在的表，可能会遇到以下错误：

ERROR 1050 (42S01): Table 'table_name' already exists

解决方法是使用 IF NOT EXISTS 选项来避免错误：

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype [constraints],
    ...
);

6.2 数据类型不匹配

在插入数据时，如果数据类型不匹配，可能会遇到错误。确保插入的数据类型与表的定义匹配。

6.3 约束条件的违反

在插入或更新数据时，如果违反了约束条件，例如 NOT NULL 或 CHECK，可能会遇到错误。确保数据符合所有约束条件。

7. 总结

在 MySQL 中，CREATE TABLE 语句是定义和创建表的基本工具。通过了解基本语法、数据类型、约束条件以及表的选项，可以有效地设计和管理数据库表。掌握这些知识后，您可以创建结构复杂的表，组织和存储数据，并确保数据的完整性和一致性。通过实践和应用这些知识，可以更好地利用 MySQL 的功能，实现高效的数据管理。