DDL语句-- 用户表 CREATE TABLE user ( user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL UNIQUE COMMENT 用户名, password VARCHAR(100) NOT NULL COMMENT 密码, real_name VARCHAR(50) COMMENT 真实姓名, phone VARCHAR(20) NOT NULL UNIQUE COMMENT 手机号, email VARCHAR(100) COMMENT 邮箱, role ENUM(student, teacher, admin) DEFAULT student COMMENT 用户角色, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 物品分类表 CREATE TABLE category ( category_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 分类ID, category_name VARCHAR(50) NOT NULL UNIQUE COMMENT 分类名称, description VARCHAR(200) COMMENT 分类描述 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 物品表 CREATE TABLE item ( item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 物品ID, item_name VARCHAR(100) NOT NULL COMMENT 物品名称, category_id INT NOT NULL COMMENT 分类ID, seller_id INT NOT NULL COMMENT 卖家ID, price DECIMAL(10,2) NOT NULL COMMENT 物品价格, status ENUM(on_sale, sold, removed) DEFAULT on_sale COMMENT 物品状态, description TEXT COMMENT 物品描述, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 发布时间, FOREIGN KEY (category_id) REFERENCES category(category_id), FOREIGN KEY (seller_id) REFERENCES user(user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 订单表 CREATE TABLE order ( order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID, buyer_id INT NOT NULL COMMENT 买家ID, item_id INT NOT NULL COMMENT 物品ID, order_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status ENUM(pending, completed, cancelled) DEFAULT pending COMMENT 订单状态, FOREIGN KEY (buyer_id) REFERENCES user(user_id), FOREIGN KEY (item_id) REFERENCES item(item_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 评价表 CREATE TABLE comment ( comment_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 评价ID, order_id INT NOT NULL COMMENT 订单ID, rating INT CHECK (rating BETWEEN 1 AND 5) COMMENT 评分, content TEXT COMMENT 评价内容, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 评价时间, FOREIGN KEY (order_id) REFERENCES order(order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;DML语句-- 用户表数据 INSERT INTO user (username, password, real_name, phone, email, role) VALUES (student01, 123456, 张三, 13800138001, zhangsanexample.com, student), (student02, 123456, 李四, 13800138002, lisiexample.com, student), (student03, 123456, 王五, 13800138003, wangwuexample.com, student), (student04, 123456, 赵六, 13800138004, zhaoliuexample.com, student), (student05, 123456, 孙七, 13800138005, sunqiexample.com, student), (teacher01, 123456, 周老师, 13900139001, zhouschool.com, teacher), (teacher02, 123456, 吴老师, 13900139002, wuschool.com, teacher), (admin01, 123456, 管理员, 13700137001, adminschool.com, admin), (student06, 123456, 郑八, 13800138006, zhengbaexample.com, student), (student07, 123456, 钱九, 13800138007, qianjiuexample.com, student); -- 物品分类表数据 INSERT INTO category (category_name, description) VALUES (电子产品, 手机、电脑、平板等数码产品), (书籍资料, 教材、辅导书、课外读物等), (生活用品, 水杯、收纳箱、床上用品等), (运动器材, 篮球、羽毛球拍、健身器材等), (美妆护肤, 护肤品、化妆品、美妆工具等), (服装服饰, 上衣、裤子、鞋子等), (学习用品, 文具、笔记本、计算器等), (乐器, 吉他、尤克里里、电子琴等), (宠物用品, 猫粮、狗粮、宠物玩具等), (其他, 未分类物品); -- 物品表数据 INSERT INTO item (item_name, category_id, seller_id, price, status, description) VALUES (iPhone 12, 1, 1, 2500.00, on_sale, 9成新功能正常无拆修), (高等数学教材, 2, 2, 30.00, sold, 几乎全新无笔记), (保温杯, 3, 3, 20.00, on_sale, 304不锈钢容量500ml), (篮球, 4, 4, 50.00, on_sale, 斯伯丁篮球使用半年), (爽肤水, 5, 5, 80.00, sold, 余量80%正品保证), (牛仔裤, 6, 6, 40.00, on_sale, L码版型宽松), (笔记本, 7, 7, 10.00, on_sale, 10本装空白内页), (吉他, 8, 8, 800.00, on_sale, 民谣吉他送琴包), (猫罐头, 9, 9, 15.00, sold, 12罐未开封), (台灯, 10, 10, 35.00, on_sale, 可调节亮度USB供电); -- 订单表数据 INSERT INTO order (buyer_id, item_id, amount, status) VALUES (2, 1, 2500.00, pending), (3, 2, 30.00, completed), (4, 5, 80.00, completed), (5, 9, 15.00, completed), (6, 3, 20.00, pending), (7, 4, 50.00, pending), (8, 6, 40.00, pending), (9, 7, 10.00, completed), (10, 8, 800.00, pending), (1, 10, 35.00, completed); -- 评价表数据 INSERT INTO comment (order_id, rating, content) VALUES (2, 5, 教材很新卖家发货快), (3, 4, 爽肤水是正品好用), (4, 5, 猫罐头日期新鲜猫咪爱吃), (8, 4, 笔记本质量不错), (10, 5, 台灯亮度合适性价比高), (1, 4, 手机外观完好功能正常), (5, 5, 保温杯保温效果好), (6, 4, 篮球弹性不错), (7, 5, 牛仔裤版型好看), (9, 4, 吉他音色不错);