MySQL周内训参照2、DDL与DML语句

根据ER图与数据库模型图设计DDL语句

CREATE TABLE user (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_wallet (
    wallet_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
CREATE TABLE user_wallet_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    transaction_type VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_time TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
 
CREATE TABLE product_type (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL,
    parent_id INT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE product (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    stock INT NOT NULL DEFAULT 0,
    type_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (type_id) REFERENCES product_type(type_id) ON DELETE RESTRICT
);
 
CREATE TABLE `order` (
    `order_id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `order_status` VARCHAR(50) NOT NULL DEFAULT '待支付',
    `order_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `payment_status` VARCHAR(50) NOT NULL DEFAULT '未支付',
    `payment_time` TIMESTAMP NULL,
    `total_price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT
);
 
CREATE TABLE order_info (
    order_info_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 `order`(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT
);

-- 创建用户表
CREATE TABLE user (
    -- 用户ID，主键，自增
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户名，非空且唯一
    username VARCHAR(50) NOT NULL UNIQUE,
    -- 密码，非空，假设密码使用哈希加密存储
    password VARCHAR(255) NOT NULL, 
    -- 邮箱，非空且唯一
    email VARCHAR(100) UNIQUE,
    -- 手机号，非空且唯一
    phone VARCHAR(20) UNIQUE,
    -- 创建时间，默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
 
-- 创建用户钱包表
CREATE TABLE user_wallet (
    -- 钱包ID，主键，自增
    wallet_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户ID，外键，关联user表的user_id
    user_id INT NOT NULL,
    -- 钱包余额，非空，默认值为0.00
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    -- 创建时间，默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 外键约束，当user表删除用户时，级联删除该用户的钱包记录
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
 
 
-- 创建用户钱包交易日志表
CREATE TABLE user_wallet_log (
    -- 日志ID，主键，自增
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户ID，外键，关联user表的user_id
    user_id INT NOT NULL,
    -- 交易类型，非空
    transaction_type VARCHAR(50) NOT NULL,
    -- 交易金额，非空
    amount DECIMAL(10, 2) NOT NULL,
    -- 交易时间，非空
    transaction_time TIMESTAMP NOT NULL,
    -- 外键约束，当user表删除用户时，级联删除该用户的所有交易日志记录
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
 
 
-- 创建商品类别表
CREATE TABLE product_type (
    -- 类别ID，主键，自增
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 类别名称，非空
    type_name VARCHAR(100) NOT NULL,
    -- 父类别ID，可为空
    parent_id INT NULL,
    -- 类别描述
    description TEXT,
    -- 创建时间，默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
 
-- 创建商品表
CREATE TABLE product (
    -- 商品ID，主键，自增
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 商品名称，非空
    product_name VARCHAR(255) NOT NULL,
    -- 商品价格，非空，默认值为0.00
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    -- 库存数量，非空，默认值为0
    stock INT NOT NULL DEFAULT 0,
    -- 类别ID，非空，外键，关联product_type表的type_id
    type_id INT NOT NULL,
    -- 创建时间，默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 外键约束，当删除商品类别时，若商品仍属于该类别，则不允许删除
    FOREIGN KEY (type_id) REFERENCES product_type(type_id) ON DELETE RESTRICT
);
 
 
 
-- 创建订单表
CREATE TABLE `order` (
    -- 订单ID，主键，自增
    `order_id` INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户ID，非空，关联用户表的user_id字段
    `user_id` INT NOT NULL,
    -- 订单状态，非空，默认值为'待支付'（表示订单等待支付的状态）
    `order_status` VARCHAR(50) NOT NULL DEFAULT '待支付',
    -- 下单时间，默认值为当前时间戳（记录订单创建的时间）
    `order_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 支付状态，非空，默认值为'未支付'（表示订单尚未完成支付的状态）
    `payment_status` VARCHAR(50) NOT NULL DEFAULT '未支付',
    -- 支付时间，可以为空（表示订单完成支付的时间，如果未支付则为NULL）
    `payment_time` TIMESTAMP NULL,
    -- 订单总价，非空，默认值为0.00（表示订单中所有商品的总价格）
    `total_price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    -- 外键约束，关联用户表的user_id字段，当用户被删除时，限制删除该用户创建的订单（防止数据不一致）
    FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT
);
 
 
-- 创建订单详情表
CREATE TABLE order_info (
    -- 订单详情ID，主键，自增
    order_info_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 订单ID，非空，关联订单表的order_id字段
    order_id INT NOT NULL,
    -- 商品ID，非空，关联商品表的product_id字段
    product_id INT NOT NULL,
    -- 购买数量，非空（表示订单中每个商品的数量）
    quantity INT NOT NULL,
    -- 单价，非空（表示订单中每个商品的单位价格）
    unit_price DECIMAL(10, 2) NOT NULL,
    -- 外键约束，关联订单表的order_id字段，当订单被删除时，级联删除该订单的所有详情记录（保持数据完整性）
    FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,
    -- 外键约束，关联商品表的product_id字段，当商品被删除时，限制删除该商品在订单中的记录（防止数据不一致）
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT
);