垦荒学园

简体中文

English

简体中文

English

切换主题

CREATE 建表 ​

CREATE 语句用于创建数据库对象，包括数据库、表、索引等。本章将详细介绍如何使用 CREATE 语句创建数据库和表。

创建数据库 ​

基本语法 ​

sql
CREATE DATABASE database_name;

示例 ​

sql
-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS mydb;

-- 指定字符集
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看数据库 ​

sql
-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE mydb;

-- 查看当前数据库
SELECT DATABASE();

创建表 ​

基本语法 ​

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

简单示例 ​

sql
-- 创建用户表
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

数据类型 ​

数值类型 ​

sql
CREATE TABLE numbers_example (
    tiny_int TINYINT,           -- 1字节，-128 到 127
    small_int SMALLINT,         -- 2字节，-32768 到 32767
    medium_int MEDIUMINT,       -- 3字节
    normal_int INT,             -- 4字节，-2147483648 到 2147483647
    big_int BIGINT,             -- 8字节
    decimal_num DECIMAL(10,2),  -- 精确小数，10位数字，2位小数
    float_num FLOAT,            -- 单精度浮点数
    double_num DOUBLE           -- 双精度浮点数
);

字符串类型 ​

sql
CREATE TABLE strings_example (
    fixed_char CHAR(10),        -- 固定长度字符串
    var_char VARCHAR(255),      -- 可变长度字符串
    tiny_text TINYTEXT,         -- 最大 255 字符
    normal_text TEXT,           -- 最大 65,535 字符
    medium_text MEDIUMTEXT,     -- 最大 16,777,215 字符
    long_text LONGTEXT          -- 最大 4,294,967,295 字符
);

日期时间类型 ​

sql
CREATE TABLE datetime_example (
    date_col DATE,              -- 日期：YYYY-MM-DD
    time_col TIME,              -- 时间：HH:MM:SS
    datetime_col DATETIME,      -- 日期时间：YYYY-MM-DD HH:MM:SS
    timestamp_col TIMESTAMP,    -- 时间戳
    year_col YEAR               -- 年份：YYYY
);

其他类型 ​

sql
CREATE TABLE other_types (
    bool_col BOOLEAN,           -- 布尔值（0 或 1）
    enum_col ENUM('small', 'medium', 'large'),  -- 枚举
    set_col SET('red', 'green', 'blue'),        -- 集合
    json_col JSON,              -- JSON 数据
    blob_col BLOB               -- 二进制数据
);

约束 ​

PRIMARY KEY（主键） ​

sql
-- 方式 1：在列定义时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 方式 2：在表定义末尾指定
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

-- 方式 3：复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

AUTO_INCREMENT（自动增长） ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

NOT NULL（非空） ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

UNIQUE（唯一） ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

DEFAULT（默认值） ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CHECK（检查约束） ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CHECK (age >= 18),
    email VARCHAR(100) CHECK (email LIKE '%@%')
);

FOREIGN KEY（外键） ​

sql
-- 创建父表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 创建子表（带外键）
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 带级联操作的外键
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

完整示例 ​

示例 1：用户表 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 18),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

示例 2：商品表 ​

sql
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INT DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

示例 3：订单表 ​

sql
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

示例 4：订单详情表 ​

sql
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

创建表的高级选项 ​

指定存储引擎 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

指定字符集 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

添加表注释 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY COMMENT '用户ID',
    name VARCHAR(50) COMMENT '用户名'
) COMMENT='用户信息表';

从查询结果创建表 ​

CREATE TABLE AS SELECT ​

sql
-- 复制表结构和数据
CREATE TABLE users_backup AS
SELECT * FROM users;

-- 只复制部分数据
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

-- 只复制表结构（不复制数据）
CREATE TABLE users_template AS
SELECT * FROM users WHERE 1=0;

创建临时表 ​

sql
-- 创建临时表
CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50)
);

-- 临时表在会话结束时自动删除

查看表结构 ​

sql
-- 查看表结构
DESC users;
DESCRIBE users;

-- 查看创建表的 SQL
SHOW CREATE TABLE users;

-- 查看所有表
SHOW TABLES;

实战示例：电商系统 ​

完整的数据库设计 ​

sql
-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS ecommerce CHARACTER SET utf8mb4;
USE ecommerce;

-- 2. 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_username (username)
) COMMENT='用户表';

-- 3. 用户地址表
CREATE TABLE user_addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    recipient_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    district VARCHAR(50),
    address VARCHAR(200) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) COMMENT='用户地址表';

-- 4. 商品分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id INT,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) COMMENT='商品分类表';

-- 5. 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    original_price DECIMAL(10,2),
    stock INT DEFAULT 0 CHECK (stock >= 0),
    sales_count INT DEFAULT 0,
    image_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT idx_name_desc (name, description)
) COMMENT='商品表';

-- 6. 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_fee DECIMAL(10,2) DEFAULT 0,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    payment_method VARCHAR(20),
    shipping_address_id INT,
    remark TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL,
    shipped_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_user_id (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) COMMENT='订单表';

-- 7. 订单详情表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    product_image VARCHAR(255),
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) COMMENT='订单详情表';

-- 8. 购物车表
CREATE TABLE cart_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_product (user_id, product_id),
    INDEX idx_user_id (user_id)
) COMMENT='购物车表';

常见错误 ​

错误 1：表已存在 ​

sql
-- 错误
CREATE TABLE users (...);  -- 如果表已存在会报错

-- 正确
CREATE TABLE IF NOT EXISTS users (...);

错误 2：数据类型不匹配 ​

sql
-- 错误：VARCHAR 没有指定长度
CREATE TABLE users (
    name VARCHAR
);

-- 正确
CREATE TABLE users (
    name VARCHAR(50)
);

错误 3：外键引用不存在的表 ​

sql
-- 错误：引用的表不存在
CREATE TABLE orders (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- users 表必须先存在
);

-- 正确：先创建父表
CREATE TABLE users (id INT PRIMARY KEY);
CREATE TABLE orders (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

最佳实践 ​

1. 使用有意义的表名和列名 ​

sql
-- 好
CREATE TABLE user_orders (...);

-- 不好
CREATE TABLE tbl1 (...);

2. 总是定义主键 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
    name VARCHAR(50)
);

3. 合理使用约束 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE,  -- 非空且唯一
    age INT CHECK (age >= 0),            -- 检查约束
    status VARCHAR(20) DEFAULT 'active'  -- 默认值
);

4. 为外键列创建索引 ​

sql
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id)  -- 为外键创建索引
);

5. 添加时间戳字段 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

小结 ​

本章介绍了 CREATE 语句的使用：

  • 创建数据库：CREATE DATABASE
  • 创建表：CREATE TABLE
  • 数据类型：数值、字符串、日期时间等
  • 约束：PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、DEFAULT、CHECK
  • 高级选项：存储引擎、字符集、注释
  • 从查询创建表：CREATE TABLE AS SELECT
  • 临时表：CREATE TEMPORARY TABLE

掌握 CREATE 语句是数据库设计的基础，合理的表结构设计对系统性能和可维护性至关重要。

下一步： 学习 INSERT 插入数据，向表中添加数据。