校园二手物品交易的核心是 **“人 - 物 - 交易行为”** 的闭环需围绕交易全流程拆解表结构确保数据逻辑完整且符合业务场景1. 核心表及设计目的表格表名设计核心目的必要性用户表t_user存储交易主体买家 / 卖家的基础信息是所有交易行为的 “身份载体”核心物品分类表t_category对二手物品标准化分类方便检索如教材、电子产品减少信息混乱重要物品信息表t_goods存储待交易物品的核心信息关联 “卖家” 和 “分类”是交易的 “标的物”核心订单表t_order记录交易行为的核心流程下单 - 付款 - 收货 - 完成关联 “买家 / 卖家 / 物品”核心评价表t_evaluation交易完成后对双方 / 物品的反馈关联 “订单” 和 “评价人 / 被评价人”完善交易闭环重要2. 表间核心关系外键逻辑用 “谁依赖谁” 的逻辑理解外键本质是保证数据一致性避免无效关联用户表 ← 物品表物品表的seller_id卖家 ID依赖用户表的user_id不能存在 “无主物品”分类表 ← 物品表物品表的category_id分类 ID依赖分类表的category_id不能存在 “无分类物品”物品表 ← 订单表订单表的goods_id物品 ID依赖物品表的goods_id不能交易 “不存在的物品”用户表 ← 订单表订单表的buyer_id/seller_id依赖用户表的user_id不能存在 “无身份的交易”订单表 ← 评价表评价表的order_id依赖订单表的order_id不能评价 “不存在的订单”用户表 ← 评价表评价表的evaluator_id/evaluated_id依赖用户表的user_id不能有 “匿名评价人”。二、插入数据的核心注意事项插入数据的核心原则是 **“先主后从、数据合法、逻辑自洽”**具体需注意以下 4 点1. 插入顺序严格遵循 “主表→从表”外键依赖顺序外键约束会阻止 “从表引用主表不存在的数据”因此必须按以下顺序插入plaintext用户表 → 分类表 → 物品表 → 订单表 → 评价表❌ 错误示例先插订单表再插用户表 → 订单的buyer_id找不到对应用户直接报错✅ 正确示例先插用户ID1→ 再插物品卖家 ID1→ 再插订单买家 ID1、物品 ID1。2. 数据合法性匹配字段约束表格约束类型注意点示例唯一约束UNIQUEuser_no学号、category_name分类名不能重复不能同时插入 2 条 “user_no2023001” 的用户非空约束NOT NULLgoods_name物品名、price价格必须赋值不能为 NULL物品表插入时price不能写 NULL字段类型匹配数值型字段不能插字符串枚举 / 状态值需符合业务定义score评分只能插 1-5 的整数外键匹配从表外键值必须是主表主键已存在的值订单goods_id10需确保物品表有 ID103. 业务逻辑自洽符合二手交易场景物品状态与订单状态匹配已售出的物品goods_status2需对应有 “已完成” 的订单不能出现 “已售出但无订单”订单金额与物品价格匹配订单order_amount需等于物品表的price特殊折扣需额外字段记录避免金额混乱评价仅能关联 “已完成” 的订单评价表的order_id需对应订单表order_status4已完成不能评价 “待付款” 订单。4. 批量插入避免单条报错导致全量失败测试阶段用INSERT IGNORE忽略重复数据如多次执行插入语句时避免 “唯一键重复” 报错生产阶段用事务BEGIN/COMMIT包裹插入语句确保 “要么全成功要么全失败”避免部分数据插入导致逻辑混乱sqlBEGIN; -- 开启事务 INSERT INTO t_user (...) VALUES (...); INSERT INTO t_category (...) VALUES (...); -- 其他表插入语句 COMMIT; -- 提交事务所有语句都成功才生效三、扩展思考可选表若系统需更完善可新增以下表不影响核心逻辑但提升实用性物品图片表t_goods_img存储物品实拍图关联goods_id一个物品可多张图交易日志表t_trade_log记录订单状态变更如 “待付款→待发货”便于问题排查收藏表t_collection存储用户收藏的物品关联user_id和goods_id提升用户体验。总结/* Navicat Premium Data Transfer Source Server : 1 Source Server Type : MySQL Source Server Version : 50731 Source Host : localhost:3306 Source Schema : schooldb Target Server Type : MySQL Target Server Version : 50731 File Encoding : 65001 Date: 18/03/2026 15:03:04 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; -- ---------------------------- -- Table structure for categories -- ---------------------------- DROP TABLE IF EXISTS categories; CREATE TABLE categories ( category_id int(11) NOT NULL AUTO_INCREMENT COMMENT 分类ID, category_name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 分类名称, description varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 分类描述, PRIMARY KEY (category_id) USING BTREE ) ENGINE InnoDB AUTO_INCREMENT 11 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 物品分类表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of categories -- ---------------------------- INSERT INTO categories VALUES (1, 电子产品, 手机、电脑、平板、耳机等数码产品); INSERT INTO categories VALUES (2, 书籍教材, 专业课教材、考研资料、小说等); INSERT INTO categories VALUES (3, 生活用品, 衣架、收纳盒、水壶、床垫等); INSERT INTO categories VALUES (4, 体育用品, 篮球、羽毛球拍、瑜伽垫、跑步机等); INSERT INTO categories VALUES (5, 美妆护肤, 口红、粉底液、面膜、水乳等全新/九成新); INSERT INTO categories VALUES (6, 服饰鞋包, 衣服、鞋子、背包、行李箱等); INSERT INTO categories VALUES (7, 学习用品, 笔记本、钢笔、画板、计算器等); INSERT INTO categories VALUES (8, 家具家电, 衣柜、书桌、洗衣机、冰箱等); INSERT INTO categories VALUES (9, 零食饮料, 临期零食、饮料未开封); INSERT INTO categories VALUES (10, 其他, 无法归类的二手物品); -- ---------------------------- -- Table structure for items -- ---------------------------- DROP TABLE IF EXISTS items; CREATE TABLE items ( item_id int(11) NOT NULL AUTO_INCREMENT COMMENT 物品ID, item_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 物品名称, category_id int(11) NOT NULL COMMENT 分类ID, seller_id int(11) NOT NULL COMMENT 卖家ID, price decimal(10, 2) NOT NULL COMMENT 售价, original_price decimal(10, 2) NULL DEFAULT NULL COMMENT 原价, status enum(on_sale,sold,off_shelf) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT on_sale COMMENT 状态在售/已售/下架, description text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 物品描述, publish_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT 发布时间, PRIMARY KEY (item_id) USING BTREE, INDEX category_id(category_id) USING BTREE, INDEX seller_id(seller_id) USING BTREE, CONSTRAINT items_ibfk_1 FOREIGN KEY (category_id) REFERENCES categories (category_id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT items_ibfk_2 FOREIGN KEY (seller_id) REFERENCES users (user_id) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE InnoDB AUTO_INCREMENT 16 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 物品信息表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of items -- ---------------------------- INSERT INTO items VALUES (1, 小米14手机, 1, 1, 3500.00, 4299.00, sold, 99新使用3个月无磕碰电池健康98%, 2026-03-18 14:58:16); INSERT INTO items VALUES (2, 《数据结构》教材, 2, 2, 25.00, 59.80, sold, 全新未使用考研自用多余, 2026-03-18 14:58:16); INSERT INTO items VALUES (3, 宜家收纳盒, 3, 3, 15.00, 39.90, on_sale, 8成新无破损可自提, 2026-03-18 14:58:16); INSERT INTO items VALUES (4, 尤尼克斯羽毛球拍, 4, 4, 180.00, 320.00, sold, 9成新线刚换不久, 2026-03-18 14:58:16); INSERT INTO items VALUES (5, 迪奥999口红, 5, 5, 80.00, 350.00, sold, 仅试色正品保证, 2026-03-18 14:58:16); INSERT INTO items VALUES (6, 李宁运动服, 6, 6, 45.00, 199.00, on_sale, XL码几乎全新洗过一次, 2026-03-18 14:58:16); INSERT INTO items VALUES (7, 机械键盘, 1, 7, 120.00, 299.00, sold, 青轴使用1年按键无故障, 2026-03-18 14:58:16); INSERT INTO items VALUES (8, 考研英语真题, 2, 8, 18.00, 45.00, on_sale, 2025版带详细解析, 2026-03-18 14:58:16); INSERT INTO items VALUES (9, 美的电水壶, 3, 9, 30.00, 99.00, sold, 使用半年无维修加热快, 2026-03-18 14:58:16); INSERT INTO items VALUES (10, 篮球, 4, 1, 40.00, 129.00, on_sale, 斯伯丁篮球7成新弹性好, 2026-03-18 14:58:16); INSERT INTO items VALUES (11, MacBook Air, 1, 10, 5000.00, 7999.00, sold, 2023款8G256G无划痕, 2026-03-18 14:58:16); INSERT INTO items VALUES (12, 《Python编程》, 2, 11, 30.00, 89.00, on_sale, 教师自用有笔记标注, 2026-03-18 14:58:16); INSERT INTO items VALUES (13, 折叠书桌, 8, 12, 80.00, 199.00, sold, 实木材质可折叠节省空间, 2026-03-18 14:58:16); INSERT INTO items VALUES (14, 百草味零食大礼包, 9, 2, 40.00, 89.00, on_sale, 临期1个月未开封, 2026-03-18 14:58:16); INSERT INTO items VALUES (15, 充电宝20000mAh, 1, 3, 50.00, 129.00, sold, 罗马仕品牌续航强, 2026-03-18 14:58:16); -- ---------------------------- -- Table structure for transactions -- ---------------------------- DROP TABLE IF EXISTS transactions; CREATE TABLE transactions ( transaction_id int(11) NOT NULL AUTO_INCREMENT COMMENT 交易ID, item_id int(11) NOT NULL COMMENT 物品ID, buyer_id int(11) NOT NULL COMMENT 买家ID, seller_id int(11) NOT NULL COMMENT 卖家ID, transaction_price decimal(10, 2) NOT NULL COMMENT 成交价格, transaction_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT 交易时间, status enum(completed,cancelled) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT completed COMMENT 交易状态完成/取消, PRIMARY KEY (transaction_id) USING BTREE, INDEX item_id(item_id) USING BTREE, INDEX buyer_id(buyer_id) USING BTREE, INDEX seller_id(seller_id) USING BTREE, CONSTRAINT transactions_ibfk_1 FOREIGN KEY (item_id) REFERENCES items (item_id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT transactions_ibfk_2 FOREIGN KEY (buyer_id) REFERENCES users (user_id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT transactions_ibfk_3 FOREIGN KEY (seller_id) REFERENCES users (user_id) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE InnoDB AUTO_INCREMENT 13 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 交易记录表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of transactions -- ---------------------------- INSERT INTO transactions VALUES (1, 1, 2, 1, 3500.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (2, 2, 4, 2, 25.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (3, 4, 5, 4, 180.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (4, 5, 6, 5, 80.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (5, 7, 8, 7, 120.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (6, 9, 9, 9, 30.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (7, 11, 10, 10, 5000.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (8, 13, 11, 12, 80.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (9, 15, 7, 3, 50.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (10, 1, 3, 1, 3450.00, 2026-03-18 14:58:16, cancelled); INSERT INTO transactions VALUES (11, 4, 1, 4, 170.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (12, 7, 2, 7, 110.00, 2026-03-18 14:58:16, completed); -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS users; CREATE TABLE users ( user_id int(11) NOT NULL AUTO_INCREMENT COMMENT 用户ID, user_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 用户名, student_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 学号/工号, phone varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 手机号, email varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 邮箱, role enum(student,teacher) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 角色学生/教职工, create_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT 创建时间, PRIMARY KEY (user_id) USING BTREE, UNIQUE INDEX student_id(student_id) USING BTREE ) ENGINE InnoDB AUTO_INCREMENT 13 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 用户表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO users VALUES (1, 张三, 2023001, 13800138001, zhangsanschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (2, 李四, 2023002, 13800138002, lisischool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (3, 王五, 2023003, 13800138003, wangwuschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (4, 赵六, 2023004, 13800138004, zhaoliuschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (5, 钱七, 2023005, 13800138005, qianqischool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (6, 孙八, 2023006, 13800138006, sunbaschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (7, 周九, 2023007, 13800138007, zhoujiuschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (8, 吴十, 2023008, 13800138008, wushischool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (9, 郑十一, 2022001, 13800138009, zheng11school.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (10, 王老师, T001, 13800138010, teacher_wangschool.com, teacher, 2026-03-18 14:58:16); INSERT INTO users VALUES (11, 李老师, T002, 13800138011, teacher_lischool.com, teacher, 2026-03-18 14:58:16); INSERT INTO users VALUES (12, 张老师, T003, 13800138012, teacher_zhangschool.com, teacher, 2026-03-18 14:58:16); SET FOREIGN_KEY_CHECKS 1;
创建一个校园管理系统——主营方向是二手物品交易。
校园二手物品交易的核心是 **“人 - 物 - 交易行为”** 的闭环需围绕交易全流程拆解表结构确保数据逻辑完整且符合业务场景1. 核心表及设计目的表格表名设计核心目的必要性用户表t_user存储交易主体买家 / 卖家的基础信息是所有交易行为的 “身份载体”核心物品分类表t_category对二手物品标准化分类方便检索如教材、电子产品减少信息混乱重要物品信息表t_goods存储待交易物品的核心信息关联 “卖家” 和 “分类”是交易的 “标的物”核心订单表t_order记录交易行为的核心流程下单 - 付款 - 收货 - 完成关联 “买家 / 卖家 / 物品”核心评价表t_evaluation交易完成后对双方 / 物品的反馈关联 “订单” 和 “评价人 / 被评价人”完善交易闭环重要2. 表间核心关系外键逻辑用 “谁依赖谁” 的逻辑理解外键本质是保证数据一致性避免无效关联用户表 ← 物品表物品表的seller_id卖家 ID依赖用户表的user_id不能存在 “无主物品”分类表 ← 物品表物品表的category_id分类 ID依赖分类表的category_id不能存在 “无分类物品”物品表 ← 订单表订单表的goods_id物品 ID依赖物品表的goods_id不能交易 “不存在的物品”用户表 ← 订单表订单表的buyer_id/seller_id依赖用户表的user_id不能存在 “无身份的交易”订单表 ← 评价表评价表的order_id依赖订单表的order_id不能评价 “不存在的订单”用户表 ← 评价表评价表的evaluator_id/evaluated_id依赖用户表的user_id不能有 “匿名评价人”。二、插入数据的核心注意事项插入数据的核心原则是 **“先主后从、数据合法、逻辑自洽”**具体需注意以下 4 点1. 插入顺序严格遵循 “主表→从表”外键依赖顺序外键约束会阻止 “从表引用主表不存在的数据”因此必须按以下顺序插入plaintext用户表 → 分类表 → 物品表 → 订单表 → 评价表❌ 错误示例先插订单表再插用户表 → 订单的buyer_id找不到对应用户直接报错✅ 正确示例先插用户ID1→ 再插物品卖家 ID1→ 再插订单买家 ID1、物品 ID1。2. 数据合法性匹配字段约束表格约束类型注意点示例唯一约束UNIQUEuser_no学号、category_name分类名不能重复不能同时插入 2 条 “user_no2023001” 的用户非空约束NOT NULLgoods_name物品名、price价格必须赋值不能为 NULL物品表插入时price不能写 NULL字段类型匹配数值型字段不能插字符串枚举 / 状态值需符合业务定义score评分只能插 1-5 的整数外键匹配从表外键值必须是主表主键已存在的值订单goods_id10需确保物品表有 ID103. 业务逻辑自洽符合二手交易场景物品状态与订单状态匹配已售出的物品goods_status2需对应有 “已完成” 的订单不能出现 “已售出但无订单”订单金额与物品价格匹配订单order_amount需等于物品表的price特殊折扣需额外字段记录避免金额混乱评价仅能关联 “已完成” 的订单评价表的order_id需对应订单表order_status4已完成不能评价 “待付款” 订单。4. 批量插入避免单条报错导致全量失败测试阶段用INSERT IGNORE忽略重复数据如多次执行插入语句时避免 “唯一键重复” 报错生产阶段用事务BEGIN/COMMIT包裹插入语句确保 “要么全成功要么全失败”避免部分数据插入导致逻辑混乱sqlBEGIN; -- 开启事务 INSERT INTO t_user (...) VALUES (...); INSERT INTO t_category (...) VALUES (...); -- 其他表插入语句 COMMIT; -- 提交事务所有语句都成功才生效三、扩展思考可选表若系统需更完善可新增以下表不影响核心逻辑但提升实用性物品图片表t_goods_img存储物品实拍图关联goods_id一个物品可多张图交易日志表t_trade_log记录订单状态变更如 “待付款→待发货”便于问题排查收藏表t_collection存储用户收藏的物品关联user_id和goods_id提升用户体验。总结/* Navicat Premium Data Transfer Source Server : 1 Source Server Type : MySQL Source Server Version : 50731 Source Host : localhost:3306 Source Schema : schooldb Target Server Type : MySQL Target Server Version : 50731 File Encoding : 65001 Date: 18/03/2026 15:03:04 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; -- ---------------------------- -- Table structure for categories -- ---------------------------- DROP TABLE IF EXISTS categories; CREATE TABLE categories ( category_id int(11) NOT NULL AUTO_INCREMENT COMMENT 分类ID, category_name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 分类名称, description varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 分类描述, PRIMARY KEY (category_id) USING BTREE ) ENGINE InnoDB AUTO_INCREMENT 11 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 物品分类表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of categories -- ---------------------------- INSERT INTO categories VALUES (1, 电子产品, 手机、电脑、平板、耳机等数码产品); INSERT INTO categories VALUES (2, 书籍教材, 专业课教材、考研资料、小说等); INSERT INTO categories VALUES (3, 生活用品, 衣架、收纳盒、水壶、床垫等); INSERT INTO categories VALUES (4, 体育用品, 篮球、羽毛球拍、瑜伽垫、跑步机等); INSERT INTO categories VALUES (5, 美妆护肤, 口红、粉底液、面膜、水乳等全新/九成新); INSERT INTO categories VALUES (6, 服饰鞋包, 衣服、鞋子、背包、行李箱等); INSERT INTO categories VALUES (7, 学习用品, 笔记本、钢笔、画板、计算器等); INSERT INTO categories VALUES (8, 家具家电, 衣柜、书桌、洗衣机、冰箱等); INSERT INTO categories VALUES (9, 零食饮料, 临期零食、饮料未开封); INSERT INTO categories VALUES (10, 其他, 无法归类的二手物品); -- ---------------------------- -- Table structure for items -- ---------------------------- DROP TABLE IF EXISTS items; CREATE TABLE items ( item_id int(11) NOT NULL AUTO_INCREMENT COMMENT 物品ID, item_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 物品名称, category_id int(11) NOT NULL COMMENT 分类ID, seller_id int(11) NOT NULL COMMENT 卖家ID, price decimal(10, 2) NOT NULL COMMENT 售价, original_price decimal(10, 2) NULL DEFAULT NULL COMMENT 原价, status enum(on_sale,sold,off_shelf) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT on_sale COMMENT 状态在售/已售/下架, description text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 物品描述, publish_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT 发布时间, PRIMARY KEY (item_id) USING BTREE, INDEX category_id(category_id) USING BTREE, INDEX seller_id(seller_id) USING BTREE, CONSTRAINT items_ibfk_1 FOREIGN KEY (category_id) REFERENCES categories (category_id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT items_ibfk_2 FOREIGN KEY (seller_id) REFERENCES users (user_id) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE InnoDB AUTO_INCREMENT 16 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 物品信息表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of items -- ---------------------------- INSERT INTO items VALUES (1, 小米14手机, 1, 1, 3500.00, 4299.00, sold, 99新使用3个月无磕碰电池健康98%, 2026-03-18 14:58:16); INSERT INTO items VALUES (2, 《数据结构》教材, 2, 2, 25.00, 59.80, sold, 全新未使用考研自用多余, 2026-03-18 14:58:16); INSERT INTO items VALUES (3, 宜家收纳盒, 3, 3, 15.00, 39.90, on_sale, 8成新无破损可自提, 2026-03-18 14:58:16); INSERT INTO items VALUES (4, 尤尼克斯羽毛球拍, 4, 4, 180.00, 320.00, sold, 9成新线刚换不久, 2026-03-18 14:58:16); INSERT INTO items VALUES (5, 迪奥999口红, 5, 5, 80.00, 350.00, sold, 仅试色正品保证, 2026-03-18 14:58:16); INSERT INTO items VALUES (6, 李宁运动服, 6, 6, 45.00, 199.00, on_sale, XL码几乎全新洗过一次, 2026-03-18 14:58:16); INSERT INTO items VALUES (7, 机械键盘, 1, 7, 120.00, 299.00, sold, 青轴使用1年按键无故障, 2026-03-18 14:58:16); INSERT INTO items VALUES (8, 考研英语真题, 2, 8, 18.00, 45.00, on_sale, 2025版带详细解析, 2026-03-18 14:58:16); INSERT INTO items VALUES (9, 美的电水壶, 3, 9, 30.00, 99.00, sold, 使用半年无维修加热快, 2026-03-18 14:58:16); INSERT INTO items VALUES (10, 篮球, 4, 1, 40.00, 129.00, on_sale, 斯伯丁篮球7成新弹性好, 2026-03-18 14:58:16); INSERT INTO items VALUES (11, MacBook Air, 1, 10, 5000.00, 7999.00, sold, 2023款8G256G无划痕, 2026-03-18 14:58:16); INSERT INTO items VALUES (12, 《Python编程》, 2, 11, 30.00, 89.00, on_sale, 教师自用有笔记标注, 2026-03-18 14:58:16); INSERT INTO items VALUES (13, 折叠书桌, 8, 12, 80.00, 199.00, sold, 实木材质可折叠节省空间, 2026-03-18 14:58:16); INSERT INTO items VALUES (14, 百草味零食大礼包, 9, 2, 40.00, 89.00, on_sale, 临期1个月未开封, 2026-03-18 14:58:16); INSERT INTO items VALUES (15, 充电宝20000mAh, 1, 3, 50.00, 129.00, sold, 罗马仕品牌续航强, 2026-03-18 14:58:16); -- ---------------------------- -- Table structure for transactions -- ---------------------------- DROP TABLE IF EXISTS transactions; CREATE TABLE transactions ( transaction_id int(11) NOT NULL AUTO_INCREMENT COMMENT 交易ID, item_id int(11) NOT NULL COMMENT 物品ID, buyer_id int(11) NOT NULL COMMENT 买家ID, seller_id int(11) NOT NULL COMMENT 卖家ID, transaction_price decimal(10, 2) NOT NULL COMMENT 成交价格, transaction_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT 交易时间, status enum(completed,cancelled) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT completed COMMENT 交易状态完成/取消, PRIMARY KEY (transaction_id) USING BTREE, INDEX item_id(item_id) USING BTREE, INDEX buyer_id(buyer_id) USING BTREE, INDEX seller_id(seller_id) USING BTREE, CONSTRAINT transactions_ibfk_1 FOREIGN KEY (item_id) REFERENCES items (item_id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT transactions_ibfk_2 FOREIGN KEY (buyer_id) REFERENCES users (user_id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT transactions_ibfk_3 FOREIGN KEY (seller_id) REFERENCES users (user_id) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE InnoDB AUTO_INCREMENT 13 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 交易记录表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of transactions -- ---------------------------- INSERT INTO transactions VALUES (1, 1, 2, 1, 3500.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (2, 2, 4, 2, 25.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (3, 4, 5, 4, 180.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (4, 5, 6, 5, 80.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (5, 7, 8, 7, 120.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (6, 9, 9, 9, 30.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (7, 11, 10, 10, 5000.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (8, 13, 11, 12, 80.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (9, 15, 7, 3, 50.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (10, 1, 3, 1, 3450.00, 2026-03-18 14:58:16, cancelled); INSERT INTO transactions VALUES (11, 4, 1, 4, 170.00, 2026-03-18 14:58:16, completed); INSERT INTO transactions VALUES (12, 7, 2, 7, 110.00, 2026-03-18 14:58:16, completed); -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS users; CREATE TABLE users ( user_id int(11) NOT NULL AUTO_INCREMENT COMMENT 用户ID, user_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 用户名, student_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 学号/工号, phone varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 手机号, email varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 邮箱, role enum(student,teacher) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 角色学生/教职工, create_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT 创建时间, PRIMARY KEY (user_id) USING BTREE, UNIQUE INDEX student_id(student_id) USING BTREE ) ENGINE InnoDB AUTO_INCREMENT 13 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 用户表 ROW_FORMAT Dynamic; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO users VALUES (1, 张三, 2023001, 13800138001, zhangsanschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (2, 李四, 2023002, 13800138002, lisischool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (3, 王五, 2023003, 13800138003, wangwuschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (4, 赵六, 2023004, 13800138004, zhaoliuschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (5, 钱七, 2023005, 13800138005, qianqischool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (6, 孙八, 2023006, 13800138006, sunbaschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (7, 周九, 2023007, 13800138007, zhoujiuschool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (8, 吴十, 2023008, 13800138008, wushischool.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (9, 郑十一, 2022001, 13800138009, zheng11school.com, student, 2026-03-18 14:58:16); INSERT INTO users VALUES (10, 王老师, T001, 13800138010, teacher_wangschool.com, teacher, 2026-03-18 14:58:16); INSERT INTO users VALUES (11, 李老师, T002, 13800138011, teacher_lischool.com, teacher, 2026-03-18 14:58:16); INSERT INTO users VALUES (12, 张老师, T003, 13800138012, teacher_zhangschool.com, teacher, 2026-03-18 14:58:16); SET FOREIGN_KEY_CHECKS 1;
