|
|
-- 创建名为`secondhandtransactions`的数据库
|
|
|
CREATE DATABASE `secondhandtransactions`;
|
|
|
|
|
|
-- 使用`secondhandtransactions`数据库
|
|
|
USE `secondhandtransactions`;
|
|
|
|
|
|
-- 创建`role`表，用于定义系统中的角色
|
|
|
CREATE TABLE role
|
|
|
(
|
|
|
role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID(主键)',
|
|
|
role_name VARCHAR(18) NOT NULL COMMENT '角色名称',
|
|
|
remark VARCHAR(128) NULL COMMENT '备注',
|
|
|
status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态(0:禁用，1:启用)',
|
|
|
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
|
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
|
|
);
|
|
|
|
|
|
-- 插入角色数据
|
|
|
INSERT INTO role (role_name, remark, status)
|
|
|
VALUES ('管理员', '拥有平台所有管理权限，负责审核、管理用户和商品', 1),
|
|
|
('普通用户', '可以浏览商品、发布商品、进行交易', 1),
|
|
|
('配送员', '负责配送商品，完成交易，管理自己的订单', 1),
|
|
|
('商家', '商家可以发布商品，管理自己的商品和订单', 1);
|
|
|
|
|
|
-- 创建`users`表，用于定义系统中的用户
|
|
|
CREATE TABLE users
|
|
|
(
|
|
|
user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID(主键)',
|
|
|
head_pic_url VARCHAR(128) NULL COMMENT '头像',
|
|
|
nick_name VARCHAR(18) NULL COMMENT '昵称',
|
|
|
username VARCHAR(18) NOT NULL COMMENT '用户名',
|
|
|
mobile VARCHAR(15) NOT NULL COMMENT '手机号',
|
|
|
password VARCHAR(255) NOT NULL COMMENT '加密后的密码',
|
|
|
sex CHAR(1) NOT NULL COMMENT '性别(0：女，1：男) ',
|
|
|
email VARCHAR(255) NOT NULL comment '邮箱',
|
|
|
address VARCHAR(70) NOT NULL COMMENT '地址',
|
|
|
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
|
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
|
|
|
|
CONSTRAINT nick_name UNIQUE (nick_name), -- 确保昵称唯一性
|
|
|
CONSTRAINT username UNIQUE (username) -- 确保用户名唯一性
|
|
|
);
|
|
|
|
|
|
-- 创建`deliveries`表，用于定义配送员信息
|
|
|
CREATE TABLE deliveries
|
|
|
(
|
|
|
delivery_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '配送ID',
|
|
|
order_id INT NOT NULL COMMENT '订单ID（外键引用 orders）',
|
|
|
delivery_person_id INT NOT NULL COMMENT '配送员ID（外键引用 users）',
|
|
|
delivery_status ENUM ('pending', 'in_progress', 'delivered') NOT NULL DEFAULT 'pending' COMMENT '配送状态',
|
|
|
delivered_at TIMESTAMP NULL COMMENT '配送完成时间',
|
|
|
CONSTRAINT order_id_fk_delivery FOREIGN KEY (order_id) REFERENCES orders (order_id), -- 外键引用orders表
|
|
|
CONSTRAINT delivery_person_id_fk_delivery FOREIGN KEY (delivery_person_id) REFERENCES users (user_id) -- 外键引用users表
|
|
|
);
|
|
|
|
|
|
-- 创建`user_roles`关联表,用于用户和角色之间的多对多关系
|
|
|
CREATE TABLE user_roles
|
|
|
(
|
|
|
user_id INT NOT NULL COMMENT '用户ID(外键引用 user 表)',
|
|
|
role_id INT NOT NULL COMMENT '角色ID(外键引用 role 表)',
|
|
|
PRIMARY KEY (user_id, role_id),
|
|
|
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (user_id), -- 外键引用users表
|
|
|
CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES role (role_id) -- 外键引用role表
|
|
|
);
|
|
|
|
|
|
INSERT INTO users (nick_name, mobile, username, password, sex, email, address)
|
|
|
VALUES ('user001', '13800000001', 'user001', 'password123', '1', 'user001@example.com', '中区1'),
|
|
|
('user002', '13800000002', 'user002', 'password456', '1', 'user002@example.com', '中区1'),
|
|
|
('admin', '13800000003', 'admin', 'password789', '1', 'admin@example.com', '中区1'),
|
|
|
('deliveries', '13800000004', 'deliveries', 'password147', '1', 'deliveries@example.com', '中区1');
|
|
|
|
|
|
# -- 将用户与普通用户角色关联
|
|
|
# INSERT INTO user_roles (user_id, role_id)
|
|
|
# VALUES (LAST_INSERT_ID(), 2);
|
|
|
#
|
|
|
# -- 将用户与商家角色关联
|
|
|
# INSERT INTO user_roles (user_id, role_id)
|
|
|
# VALUES (LAST_INSERT_ID(), 3);
|
|
|
|
|
|
-- 创建`orders`表，用于存储订单信息
|
|
|
CREATE TABLE orders
|
|
|
(
|
|
|
order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID(主键)',
|
|
|
buyer_id INT NOT NULL COMMENT '买家用户ID',
|
|
|
item_id INT NOT NULL COMMENT '商品ID',
|
|
|
seller_id INT NOT NULL COMMENT '卖家用户ID',
|
|
|
address VARCHAR(255) NOT NULL COMMENT '收货地址',
|
|
|
total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',
|
|
|
status ENUM ('pending', 'paid', 'shipped', 'completed', 'cancelled') NOT NULL DEFAULT 'pending'
|
|
|
COMMENT '订单状态：pending-订单已创建，等待支付；paid-订单已支付，等待发货；shipped-订单已发货，等待收货；completed-订单已完成；cancelled-订单已取消',
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间',
|
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '订单更新时间',
|
|
|
|
|
|
-- 外键约束
|
|
|
CONSTRAINT buyer_id_fk_orders FOREIGN KEY (buyer_id) REFERENCES users (user_id), -- 外键引用users表（买家）
|
|
|
CONSTRAINT seller_id_fk_orders FOREIGN KEY (seller_id) REFERENCES users (user_id), -- 外键引用users表（卖家）
|
|
|
CONSTRAINT item_id_fk_orders FOREIGN KEY (item_id) REFERENCES items (item_id) -- 外键引用items表
|
|
|
);
|
|
|
|
|
|
-- 创建`items`表，用于定义商品信息
|
|
|
CREATE TABLE items
|
|
|
(
|
|
|
item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID(主键)',
|
|
|
user_id INT NOT NULL COMMENT '发布商品的用户ID（外键引用 users）',
|
|
|
item_name VARCHAR(255) NOT NULL COMMENT '商品名称',
|
|
|
description VARCHAR(500) NULL COMMENT '商品描述',
|
|
|
category_id INT NOT NULL COMMENT '商品类别ID（外键引用 categories 表）',
|
|
|
price DECIMAL(10, 2) CHECK (price >= 0) NOT NULL COMMENT '商品价格',
|
|
|
status ENUM ('available', 'sold', 'removed') NOT NULL DEFAULT 'available'
|
|
|
COMMENT '商品状态：available（可售）、sold（已售出）、removed（已下架）',
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '商品创建时间',
|
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '商品更新时间',
|
|
|
|
|
|
-- 外键约束
|
|
|
CONSTRAINT user_id_fk_items FOREIGN KEY (user_id) REFERENCES users (user_id), -- 外键引用users表
|
|
|
CONSTRAINT category_id_fk_items FOREIGN KEY (category_id) REFERENCES item_categories (category_id) -- 外键引用item_categories表
|
|
|
);
|
|
|
|
|
|
-- 创建`categories`表，用于定义商品类别
|
|
|
CREATE TABLE item_categories
|
|
|
(
|
|
|
category_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '类别ID',
|
|
|
category_name VARCHAR(50) NOT NULL COMMENT '类别名称'
|
|
|
);
|
|
|
|
|
|
-- 创建`item_images`表，用于存储商品的图片信息
|
|
|
CREATE TABLE item_images
|
|
|
(
|
|
|
image_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '图片ID(主键)',
|
|
|
item_id INT NOT NULL COMMENT '商品ID',
|
|
|
image_path VARCHAR(255) NULL COMMENT '图片路径',
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '图片创建时间',
|
|
|
-- 外键约束
|
|
|
CONSTRAINT item_id_fk_image FOREIGN KEY (item_id) REFERENCES items (item_id) -- 外键引用items表
|
|
|
);
|
|
|
|
|
|
-- 创建`item_comments`表，用于存储商品的评论信息
|
|
|
CREATE TABLE item_comments
|
|
|
(
|
|
|
comment_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID(主键)',
|
|
|
user_id INT NOT NULL COMMENT '评论用户ID',
|
|
|
item_id INT NOT NULL COMMENT '被评论的商品ID',
|
|
|
content VARCHAR(1000) NOT NULL COMMENT '评论内容',
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评论创建时间',
|
|
|
|
|
|
-- 外键约束
|
|
|
CONSTRAINT user_id_fk_comment FOREIGN KEY (user_id) REFERENCES users (user_id), -- 外键引用users表
|
|
|
CONSTRAINT item_id_fk_comment FOREIGN KEY (item_id) REFERENCES items (item_id) -- 外键引用items表
|
|
|
);
|
|
|
|
|
|
-- 创建`item_likes`表，用于存储商品的点赞信息
|
|
|
CREATE TABLE item_likes
|
|
|
(
|
|
|
like_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '点赞ID(主键)',
|
|
|
user_id INT NOT NULL COMMENT '点赞用户ID',
|
|
|
item_id INT NOT NULL COMMENT '被点赞的商品ID',
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '点赞创建时间',
|
|
|
|
|
|
-- 外键约束
|
|
|
CONSTRAINT user_id_fk_like FOREIGN KEY (user_id) REFERENCES users (user_id), -- 外键引用users表
|
|
|
CONSTRAINT item_id_fk_like FOREIGN KEY (item_id) REFERENCES items (item_id) -- 外键引用items表
|
|
|
);
|
|
|
|
|
|
-- 创建`item_favorites`表，用于用户收藏的商品信息
|
|
|
CREATE TABLE item_favorites
|
|
|
(
|
|
|
favorite_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '收藏ID(主键)',
|
|
|
user_id INT NOT NULL COMMENT '用户ID（外键引用 users）',
|
|
|
item_id INT NOT NULL COMMENT '商品ID（外键引用 items）',
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '收藏时间',
|
|
|
|
|
|
-- 外键约束
|
|
|
CONSTRAINT user_id_fk_favorite FOREIGN KEY (user_id) REFERENCES users (user_id), -- 外键引用users表
|
|
|
CONSTRAINT item_id_fk_favorite FOREIGN KEY (item_id) REFERENCES items (item_id) -- 外键引用items表
|
|
|
);
|
|
|
|
|
|
-- 创建`item_offers`表，用于存储物品下架的信息
|
|
|
CREATE TABLE item_offers
|
|
|
(
|
|
|
offer_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '物品下架ID（主键）',
|
|
|
user_id INT NOT NULL COMMENT '用户ID（外键引用 users）',
|
|
|
item_id INT NOT NULL COMMENT '商品ID（外键引用 items）',
|
|
|
reason VARCHAR(500) NOT NULL COMMENT '下架理由',
|
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下架时间',
|
|
|
|
|
|
-- 外键约束
|
|
|
CONSTRAINT user_id_fk_offer FOREIGN KEY (user_id) REFERENCES users (user_id), -- 外键引用users表
|
|
|
CONSTRAINT item_id_fk_offer FOREIGN KEY (item_id) REFERENCES items (item_id) -- 外键引用items表
|
|
|
);
|
