-- 创建数据库指定字符集避免中文乱码 CREATE DATABASE IF NOT EXISTS campus_second_hand DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE campus_second_hand; -- 1. 用户表核心基础表 CREATE TABLE IF NOT EXISTS users ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户唯一ID, student_id VARCHAR(20) UNIQUE NOT NULL COMMENT 学号/工号唯一标识, username VARCHAR(50) NOT NULL COMMENT 用户昵称, phone VARCHAR(11) NOT NULL COMMENT 手机号必填, email VARCHAR(100) COMMENT 邮箱选填, dormitory VARCHAR(50) COMMENT 宿舍号/居住地址, role ENUM(student, teacher, admin) DEFAULT student COMMENT 角色学生/教师/管理员, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ) COMMENT 校园二手交易-用户信息表; -- 2. 物品分类表字典表 CREATE TABLE IF NOT EXISTS categories ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 分类ID, name VARCHAR(50) NOT NULL COMMENT 分类名称如教材、电子产品, description VARCHAR(200) COMMENT 分类描述, sort INT DEFAULT 0 COMMENT 排序权重数值越大越靠前 ) COMMENT 校园二手交易-物品分类表; -- 3. 物品信息表核心业务表 CREATE TABLE IF NOT EXISTS items ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 物品ID, user_id INT NOT NULL COMMENT 发布者ID关联用户表, category_id INT NOT NULL COMMENT 分类ID关联分类表, title VARCHAR(100) NOT NULL COMMENT 物品标题, description TEXT COMMENT 物品详情描述, price DECIMAL(10,2) NOT NULL COMMENT 售价保留2位小数, status ENUM(on_sale, sold, off_shelf) DEFAULT on_sale COMMENT 状态在售/已售/下架, publish_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 发布时间, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, -- 外键约束 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT ) COMMENT 校园二手交易-物品信息表; -- 4. 订单表交易核心表 CREATE TABLE IF NOT EXISTS orders ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) UNIQUE NOT NULL COMMENT 订单编号自定义唯一标识, buyer_id INT NOT NULL COMMENT 买家ID关联用户表, seller_id INT NOT NULL COMMENT 卖家ID关联用户表, item_id INT NOT NULL COMMENT 交易物品ID关联物品表, price DECIMAL(10,2) NOT NULL COMMENT 实际交易价格, status ENUM(pending, completed, cancelled) DEFAULT pending COMMENT 状态待交易/已完成/已取消, transaction_time DATETIME COMMENT 交易完成时间仅completed状态有值, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 订单创建时间, -- 外键约束 FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE ) COMMENT 校园二手交易-订单表; -- 5. 评价表交易后评价 CREATE TABLE IF NOT EXISTS comments ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 评价ID, order_id INT NOT NULL COMMENT 关联订单ID, evaluator_id INT NOT NULL COMMENT 评价人ID买家/卖家, evaluated_id INT NOT NULL COMMENT 被评价人ID卖家/买家, score TINYINT NOT NULL COMMENT 评分1-5分, content TEXT COMMENT 评价内容, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 评价时间, -- 外键约束 FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (evaluator_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (evaluated_id) REFERENCES users(id) ON DELETE CASCADE ) COMMENT 校园二手交易-评价表;USE campus_second_hand; -- 1. 插入用户表12条 INSERT INTO users (student_id, username, phone, email, dormitory, role) VALUES (2023001, 张三, 13800138001, zhangsanschool.edu.cn, 1号楼301, student), (2023002, 李四, 13800138002, lisischool.edu.cn, 1号楼302, student), (2023003, 王五, 13800138003, wangwuschool.edu.cn, 2号楼401, student), (2023004, 赵六, 13800138004, zhaoliuschool.edu.cn, 2号楼402, student), (2023005, 钱七, 13800138005, qianqischool.edu.cn, 3号楼501, student), (2023006, 孙八, 13800138006, sunbaschool.edu.cn, 3号楼502, student), (2023007, 周九, 13800138007, zhoujiuschool.edu.cn, 4号楼601, student), (2023008, 吴十, 13800138008, wushischool.edu.cn, 4号楼602, student), (T001, 李老师, 13900139001, teacherlischool.edu.cn, 教师公寓5栋, teacher), (T002, 王老师, 13900139002, teacherwangschool.edu.cn, 教师公寓6栋, teacher), (A001, 系统管理员, 13700137001, adminschool.edu.cn, , admin), (2023009, 郑十一, 13800138009, zheng11school.edu.cn, 5号楼701, student); -- 2. 插入物品分类表10条 INSERT INTO categories (name, description, sort) VALUES (教材教辅, 大学各专业教材、考研/考公/考证资料, 1), (电子产品, 手机、电脑、平板、耳机、充电宝等数码产品, 2), (生活用品, 被子、枕头、收纳盒等宿舍日常用品, 3), (运动器材, 篮球、羽毛球拍、瑜伽垫等运动相关, 4), (美妆护肤, 口红、粉底液、面膜等全新/九成新, 5), (衣物鞋帽, 闲置衣服、鞋子、包包、帽子等, 6), (家具家电, 宿舍小桌子、风扇、洗衣机等, 7), (零食饮料, 临期零食、整箱饮料价格优惠, 8), (宠物用品, 猫粮、狗粮、宠物笼、玩具等, 9), (其他物品, 无法归类的闲置物品, 10); -- 3. 插入物品信息表15条 INSERT INTO items (user_id, category_id, title, description, price, status) VALUES (1, 1, 高等数学同济7版, 全新未使用考研必备送配套习题册, 35.00, on_sale), (2, 2, 小米13手机, 使用半年12256G无拆无修电池健康95%, 2800.00, on_sale), (3, 3, 纯棉四件套, 宿舍单人床尺寸只用过1个月几乎全新, 45.00, sold), (4, 4, 尤尼克斯羽毛球拍, 正品刚拉的线送3个羽毛球, 180.00, on_sale), (5, 5, 雅诗兰黛粉底液, 色号1W1只用过2次不适合自己肤质, 200.00, on_sale), (6, 6, Nike运动鞋, 42码九成新无开胶无破损, 220.00, sold), (7, 7, 折叠电脑桌, 可升降宿舍神器稳固不晃, 68.00, on_sale), (8, 8, 整箱可乐, 24罐还有3个月保质期便宜出, 30.00, on_sale), (9, 9, 猫砂10kg, 膨润土猫砂全新未拆封家里猫不用了, 25.00, on_sale), (10, 10, 台灯, 充电款三档调光续航8小时, 29.90, sold), (11, 1, 考研英语黄皮书, 2026版全新送真题解析视频, 88.00, on_sale), (12, 2, iPad 9代, 64Gwifi版电池健康98%送保护套, 1500.00, on_sale), (1, 3, 真空收纳袋, 10个装送电泵收纳被子超实用, 30.00, on_sale), (2, 6, 牛仔外套, M码宽松版型洗过2次, 59.00, on_sale), (3, 7, 小风扇, USB充电静音宿舍床头可用, 19.90, sold); -- 4. 插入订单表12条 INSERT INTO orders (order_no, buyer_id, seller_id, item_id, price, status, transaction_time) VALUES (ORD20260318001, 4, 3, 3, 45.00, completed, 2026-03-10 14:30:00), (ORD20260318002, 5, 6, 6, 220.00, completed, 2026-03-11 10:15:00), (ORD20260318003, 7, 10, 10, 29.90, completed, 2026-03-12 16:40:00), (ORD20260318004, 8, 3, 15, 19.90, completed, 2026-03-13 09:20:00), (ORD20260318005, 9, 1, 1, 35.00, pending, NULL), (ORD20260318006, 10, 2, 2, 2800.00, pending, NULL), (ORD20260318007, 11, 4, 4, 180.00, pending, NULL), (ORD20260318008, 12, 5, 5, 200.00, cancelled, NULL), (ORD20260318009, 1, 7, 7, 68.00, pending, NULL), (ORD20260318010, 2, 8, 8, 30.00, completed, 2026-03-14 11:50:00), (ORD20260318011, 3, 9, 9, 25.00, pending, NULL), (ORD20260318012, 6, 11, 11, 88.00, completed, 2026-03-15 17:10:00); -- 5. 插入评价表10条 INSERT INTO comments (order_id, evaluator_id, evaluated_id, score, content) VALUES (1, 4, 3, 5, 卖家人超好物品和描述一致送货上门), (2, 5, 6, 4, 鞋子很新就是尺码稍微有点偏差总体满意), (3, 7, 10, 5, 台灯质量很好续航超久推荐购买), (4, 8, 3, 5, 小风扇静音效果不错夏天宿舍必备), (10, 2, 8, 4, 可乐日期很新价格比超市便宜很多), (12, 6, 11, 5, 黄皮书是正版解析很详细谢谢卖家), (1, 3, 4, 5, 买家很爽快交易过程很顺利), (2, 6, 5, 5, 买家准时取货沟通愉快), (3, 10, 7, 4, 买家很有礼貌就是取货稍微晚了一点), (4, 3, 8, 5, 交易完成很快买家很靠谱);
SQL文件与ER截图
发布时间：2026/5/20 11:10:22
-- 创建数据库指定字符集避免中文乱码 CREATE DATABASE IF NOT EXISTS campus_second_hand DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE campus_second_hand; -- 1. 用户表核心基础表 CREATE TABLE IF NOT EXISTS users ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户唯一ID, student_id VARCHAR(20) UNIQUE NOT NULL COMMENT 学号/工号唯一标识, username VARCHAR(50) NOT NULL COMMENT 用户昵称, phone VARCHAR(11) NOT NULL COMMENT 手机号必填, email VARCHAR(100) COMMENT 邮箱选填, dormitory VARCHAR(50) COMMENT 宿舍号/居住地址, role ENUM(student, teacher, admin) DEFAULT student COMMENT 角色学生/教师/管理员, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ) COMMENT 校园二手交易-用户信息表; -- 2. 物品分类表字典表 CREATE TABLE IF NOT EXISTS categories ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 分类ID, name VARCHAR(50) NOT NULL COMMENT 分类名称如教材、电子产品, description VARCHAR(200) COMMENT 分类描述, sort INT DEFAULT 0 COMMENT 排序权重数值越大越靠前 ) COMMENT 校园二手交易-物品分类表; -- 3. 物品信息表核心业务表 CREATE TABLE IF NOT EXISTS items ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 物品ID, user_id INT NOT NULL COMMENT 发布者ID关联用户表, category_id INT NOT NULL COMMENT 分类ID关联分类表, title VARCHAR(100) NOT NULL COMMENT 物品标题, description TEXT COMMENT 物品详情描述, price DECIMAL(10,2) NOT NULL COMMENT 售价保留2位小数, status ENUM(on_sale, sold, off_shelf) DEFAULT on_sale COMMENT 状态在售/已售/下架, publish_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 发布时间, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, -- 外键约束 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT ) COMMENT 校园二手交易-物品信息表; -- 4. 订单表交易核心表 CREATE TABLE IF NOT EXISTS orders ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) UNIQUE NOT NULL COMMENT 订单编号自定义唯一标识, buyer_id INT NOT NULL COMMENT 买家ID关联用户表, seller_id INT NOT NULL COMMENT 卖家ID关联用户表, item_id INT NOT NULL COMMENT 交易物品ID关联物品表, price DECIMAL(10,2) NOT NULL COMMENT 实际交易价格, status ENUM(pending, completed, cancelled) DEFAULT pending COMMENT 状态待交易/已完成/已取消, transaction_time DATETIME COMMENT 交易完成时间仅completed状态有值, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 订单创建时间, -- 外键约束 FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE ) COMMENT 校园二手交易-订单表; -- 5. 评价表交易后评价 CREATE TABLE IF NOT EXISTS comments ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 评价ID, order_id INT NOT NULL COMMENT 关联订单ID, evaluator_id INT NOT NULL COMMENT 评价人ID买家/卖家, evaluated_id INT NOT NULL COMMENT 被评价人ID卖家/买家, score TINYINT NOT NULL COMMENT 评分1-5分, content TEXT COMMENT 评价内容, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 评价时间, -- 外键约束 FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (evaluator_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (evaluated_id) REFERENCES users(id) ON DELETE CASCADE ) COMMENT 校园二手交易-评价表;USE campus_second_hand; -- 1. 插入用户表12条 INSERT INTO users (student_id, username, phone, email, dormitory, role) VALUES (2023001, 张三, 13800138001, zhangsanschool.edu.cn, 1号楼301, student), (2023002, 李四, 13800138002, lisischool.edu.cn, 1号楼302, student), (2023003, 王五, 13800138003, wangwuschool.edu.cn, 2号楼401, student), (2023004, 赵六, 13800138004, zhaoliuschool.edu.cn, 2号楼402, student), (2023005, 钱七, 13800138005, qianqischool.edu.cn, 3号楼501, student), (2023006, 孙八, 13800138006, sunbaschool.edu.cn, 3号楼502, student), (2023007, 周九, 13800138007, zhoujiuschool.edu.cn, 4号楼601, student), (2023008, 吴十, 13800138008, wushischool.edu.cn, 4号楼602, student), (T001, 李老师, 13900139001, teacherlischool.edu.cn, 教师公寓5栋, teacher), (T002, 王老师, 13900139002, teacherwangschool.edu.cn, 教师公寓6栋, teacher), (A001, 系统管理员, 13700137001, adminschool.edu.cn, , admin), (2023009, 郑十一, 13800138009, zheng11school.edu.cn, 5号楼701, student); -- 2. 插入物品分类表10条 INSERT INTO categories (name, description, sort) VALUES (教材教辅, 大学各专业教材、考研/考公/考证资料, 1), (电子产品, 手机、电脑、平板、耳机、充电宝等数码产品, 2), (生活用品, 被子、枕头、收纳盒等宿舍日常用品, 3), (运动器材, 篮球、羽毛球拍、瑜伽垫等运动相关, 4), (美妆护肤, 口红、粉底液、面膜等全新/九成新, 5), (衣物鞋帽, 闲置衣服、鞋子、包包、帽子等, 6), (家具家电, 宿舍小桌子、风扇、洗衣机等, 7), (零食饮料, 临期零食、整箱饮料价格优惠, 8), (宠物用品, 猫粮、狗粮、宠物笼、玩具等, 9), (其他物品, 无法归类的闲置物品, 10); -- 3. 插入物品信息表15条 INSERT INTO items (user_id, category_id, title, description, price, status) VALUES (1, 1, 高等数学同济7版, 全新未使用考研必备送配套习题册, 35.00, on_sale), (2, 2, 小米13手机, 使用半年12256G无拆无修电池健康95%, 2800.00, on_sale), (3, 3, 纯棉四件套, 宿舍单人床尺寸只用过1个月几乎全新, 45.00, sold), (4, 4, 尤尼克斯羽毛球拍, 正品刚拉的线送3个羽毛球, 180.00, on_sale), (5, 5, 雅诗兰黛粉底液, 色号1W1只用过2次不适合自己肤质, 200.00, on_sale), (6, 6, Nike运动鞋, 42码九成新无开胶无破损, 220.00, sold), (7, 7, 折叠电脑桌, 可升降宿舍神器稳固不晃, 68.00, on_sale), (8, 8, 整箱可乐, 24罐还有3个月保质期便宜出, 30.00, on_sale), (9, 9, 猫砂10kg, 膨润土猫砂全新未拆封家里猫不用了, 25.00, on_sale), (10, 10, 台灯, 充电款三档调光续航8小时, 29.90, sold), (11, 1, 考研英语黄皮书, 2026版全新送真题解析视频, 88.00, on_sale), (12, 2, iPad 9代, 64Gwifi版电池健康98%送保护套, 1500.00, on_sale), (1, 3, 真空收纳袋, 10个装送电泵收纳被子超实用, 30.00, on_sale), (2, 6, 牛仔外套, M码宽松版型洗过2次, 59.00, on_sale), (3, 7, 小风扇, USB充电静音宿舍床头可用, 19.90, sold); -- 4. 插入订单表12条 INSERT INTO orders (order_no, buyer_id, seller_id, item_id, price, status, transaction_time) VALUES (ORD20260318001, 4, 3, 3, 45.00, completed, 2026-03-10 14:30:00), (ORD20260318002, 5, 6, 6, 220.00, completed, 2026-03-11 10:15:00), (ORD20260318003, 7, 10, 10, 29.90, completed, 2026-03-12 16:40:00), (ORD20260318004, 8, 3, 15, 19.90, completed, 2026-03-13 09:20:00), (ORD20260318005, 9, 1, 1, 35.00, pending, NULL), (ORD20260318006, 10, 2, 2, 2800.00, pending, NULL), (ORD20260318007, 11, 4, 4, 180.00, pending, NULL), (ORD20260318008, 12, 5, 5, 200.00, cancelled, NULL), (ORD20260318009, 1, 7, 7, 68.00, pending, NULL), (ORD20260318010, 2, 8, 8, 30.00, completed, 2026-03-14 11:50:00), (ORD20260318011, 3, 9, 9, 25.00, pending, NULL), (ORD20260318012, 6, 11, 11, 88.00, completed, 2026-03-15 17:10:00); -- 5. 插入评价表10条 INSERT INTO comments (order_id, evaluator_id, evaluated_id, score, content) VALUES (1, 4, 3, 5, 卖家人超好物品和描述一致送货上门), (2, 5, 6, 4, 鞋子很新就是尺码稍微有点偏差总体满意), (3, 7, 10, 5, 台灯质量很好续航超久推荐购买), (4, 8, 3, 5, 小风扇静音效果不错夏天宿舍必备), (10, 2, 8, 4, 可乐日期很新价格比超市便宜很多), (12, 6, 11, 5, 黄皮书是正版解析很详细谢谢卖家), (1, 3, 4, 5, 买家很爽快交易过程很顺利), (2, 6, 5, 5, 买家准时取货沟通愉快), (3, 10, 7, 4, 买家很有礼貌就是取货稍微晚了一点), (4, 3, 8, 5, 交易完成很快买家很靠谱);
