​在AI工具的辅助下，创建一个校园管理系统——主营方向是二手物品交易.

-- 用户表 CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL, phone VARCHAR(20) NOT NULL, role ENUM('student', 'admin') DEFAULT 'student', create_time DATETIME DEFAULT CURRENT_TIMESTAMP ); -- 物品分类表 CREATE TABLE categories ( category_id INT AUTO_INCREMENT PRIMARY KEY, category_name VARCHAR(50) NOT NULL UNIQUE, description TEXT ); -- 二手物品表 CREATE TABLE items ( item_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, category_id INT NOT NULL, title VARCHAR(100) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, status ENUM('on_sale', 'sold', 'off_sale') DEFAULT 'on_sale', publish_time DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, FOREIGN KEY (category_id) REFERENCES categories(category_id) ); -- 订单表 CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, seller_id INT NOT NULL, buyer_id INT NOT NULL, item_id INT NOT NULL UNIQUE, order_status ENUM('pending', 'paid', 'completed', 'cancelled') DEFAULT 'pending', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, pay_time DATETIME NULL, FOREIGN KEY (seller_id) REFERENCES users(user_id), FOREIGN KEY (buyer_id) REFERENCES users(user_id), FOREIGN KEY (item_id) REFERENCES items(item_id) ); -- 收藏表 CREATE TABLE favorites ( favorite_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, item_id INT NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY (user_id, item_id), FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE ); INSERT INTO users (username, password, phone, role) VALUES ('stu001', '123456', '13800000001', 'student'), ('stu002', '123456', '13800000002', 'student'), ('stu003', '123456', '13800000003', 'student'), ('stu004', '123456', '13800000004', 'student'), ('stu005', '123456', '13800000005', 'student'), ('stu006', '123456', '13800000006', 'student'), ('stu007', '123456', '13800000007', 'student'), ('stu008', '123456', '13800000008', 'student'), ('stu009', '123456', '13800000009', 'student'), ('admin01', 'admin123', '13900000001', 'admin'), ('stu010', '123456', '13800000010', 'student'); INSERT INTO categories (category_name, description) VALUES ('书籍教材', '各类课本、参考书、考研资料'), ('数码产品', '手机、电脑、耳机等'), ('生活用品', '衣物、厨具、收纳用品'), ('体育器材', '篮球、羽毛球拍、瑜伽垫'), ('文具', '笔、本子、文件夹'), ('乐器', '吉他、尤克里里、口琴'), ('美妆护肤', '护肤品、化妆品'), ('游戏周边', '手办、卡牌、游戏设备'), ('交通工具', '自行车、滑板'), ('其他', '未分类物品'); INSERT INTO items (user_id, category_id, title, description, price, status) VALUES (1, 1, '《数据库系统概论》第五版', '九成新，无笔记，附习题答案', 25.00, 'on_sale'), (2, 2, '二手iPhone 12 64G', '电池健康85%，无维修，送充电器', 1200.00, 'on_sale'), (3, 3, '全新未拆封保温杯', '304不锈钢，500ml，颜色白色', 35.00, 'on_sale'), (4, 4, '斯伯丁篮球7号', '仅使用过2次，气足，送球针', 80.00, 'on_sale'), (5, 5, '百乐P500中性笔10支装', '黑色，全新未拆，适合考试', 20.00, 'on_sale'), (6, 6, '入门级民谣吉他', '41寸，原木色，带背包和调音器', 300.00, 'on_sale'), (7, 7, '雅诗兰黛小棕瓶精华', '余量约70%，专柜购入，保真', 350.00, 'on_sale'), (8, 8, '任天堂Switch游戏卡带《塞尔达》', '仅拆封，几乎全新，无划痕', 280.00, 'on_sale'), (9, 9, '美利达山地自行车', '26寸，变速正常，适合通勤', 500.00, 'on_sale'), (1, 10, '闲置小风扇', 'USB充电，三档风力，便携', 15.00, 'on_sale'), (10, 2, '二手AirPods Pro 2代', '正品，充电盒完好，音质正常', 600.00, 'on_sale'); INSERT INTO orders (seller_id, buyer_id, item_id, order_status, pay_time) VALUES (1, 2, 1, 'completed', '2026-03-10 14:30:00'), (2, 3, 2, 'paid', '2026-03-11 10:20:00'), (3, 4, 3, 'completed', '2026-03-12 16:45:00'), (4, 5, 4, 'pending', NULL), (5, 6, 5, 'completed', '2026-03-13 09:15:00'), (6, 7, 6, 'cancelled', NULL), (7, 8, 7, 'completed', '2026-03-14 19:00:00'), (8, 9, 8, 'paid', '2026-03-15 11:30:00'), (9, 10, 9, 'completed', '2026-03-16 15:20:00'), (1, 10, 10, 'pending', NULL), (10, 1, 11, 'completed', '2026-03-17 20:00:00'); INSERT INTO favorites (user_id, item_id) VALUES (2, 1), (3, 2), (4, 3), (5, 4), (6, 5), (7, 6), (8, 7), (9, 8), (10, 9), (1, 10), (10, 11);