​在AI工具的辅助下，创建一个校园管理系统——主营方向是二手物品交易。要求：给出DDL语句DML语句，对应的外键逻辑关系清晰，并上传ER图，每张表的数据不能低于10条。

#DDL

-- 创建数据库
CREATE DATABASE IF NOT EXISTS campus_secondhand DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE campus_secondhand;

-- 1. 用户表
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(32) NOT NULL COMMENT '密码',
    real_name VARCHAR(20) NOT NULL COMMENT '真实姓名',
    phone VARCHAR(11) NOT NULL UNIQUE COMMENT '手机号',
    student_id VARCHAR(20) UNIQUE COMMENT '学号',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) COMMENT '校园用户表';

-- 2. 商品分类表
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
    cate_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
    cate_name VARCHAR(30) NOT NULL UNIQUE COMMENT '分类名称',
    parent_id INT DEFAULT 0 COMMENT '父分类ID(0为顶级)',
    FOREIGN KEY (parent_id) REFERENCES categories(cate_id) ON DELETE SET NULL
) COMMENT '商品分类表';

-- 3. 商品表
DROP TABLE IF EXISTS products;
CREATE TABLE products (
    prod_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
    prod_name VARCHAR(100) NOT NULL COMMENT '商品名称',
    cate_id INT NOT NULL COMMENT '分类ID',
    seller_id INT NOT NULL COMMENT '卖家ID',
    price DECIMAL(10,2) NOT NULL COMMENT '售价',
    original_price DECIMAL(10,2) COMMENT '原价',
    prod_desc TEXT COMMENT '商品描述',
    prod_status TINYINT DEFAULT 1 COMMENT '状态 1=在售 2=已售出 3=下架',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
    FOREIGN KEY (cate_id) REFERENCES categories(cate_id) ON DELETE CASCADE,
    FOREIGN KEY (seller_id) REFERENCES users(user_id) ON DELETE CASCADE
) COMMENT '二手商品表';

-- 4. 订单表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    order_no VARCHAR(30) NOT NULL UNIQUE COMMENT '订单编号',
    buyer_id INT NOT NULL COMMENT '买家ID',
    seller_id INT NOT NULL COMMENT '卖家ID',
    total_price DECIMAL(10,2) NOT NULL COMMENT '订单总价',
    order_status TINYINT DEFAULT 1 COMMENT '1=待付款 2=已付款 3=已完成 4=已取消',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
    FOREIGN KEY (buyer_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (seller_id) REFERENCES users(user_id) ON DELETE CASCADE
) COMMENT '订单主表';

-- 5. 订单详情表
DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '详情ID',
    order_id INT NOT NULL COMMENT '订单ID',
    prod_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL(10,2) NOT NULL COMMENT '成交单价',
    quantity INT DEFAULT 1 COMMENT '购买数量',
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (prod_id) REFERENCES products(prod_id) ON DELETE CASCADE
) COMMENT '订单商品明细表';

-- 6. 收藏表
DROP TABLE IF EXISTS favorites;
CREATE TABLE favorites (
    fav_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '收藏ID',
    user_id INT NOT NULL COMMENT '用户ID',
    prod_id INT NOT NULL COMMENT '商品ID',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '收藏时间',
    UNIQUE KEY uk_user_prod (user_id, prod_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (prod_id) REFERENCES products(prod_id) ON DELETE CASCADE
) COMMENT '用户收藏表';

#DML

-- 插入用户数据（12条）
INSERT INTO users (username, password, real_name, phone, student_id) VALUES
('zhangsan', '123456', '张三', '13800138001', '2024001'),
('lisi', '123456', '李四', '13800138002', '2024002'),
('wangwu', '123456', '王五', '13800138003', '2024003'),
('zhaoliu', '123456', '赵六', '13800138004', '2024004'),
('sunqi', '123456', '孙七', '13800138005', '2024005'),
('zhouba', '123456', '周八', '13800138006', '2024006'),
('wujiu', '123456', '吴九', '13800138007', '2024007'),
('zhengshi', '123456', '郑十', '13800138008', '2024008'),
('liuyi', '123456', '刘一', '13800138009', '2024009'),
('chener', '123456', '陈二', '13800138010', '2024010'),
('yangsan', '123456', '杨三', '13800138011', '2024011'),
('huangsi', '123456', '黄四', '13800138012', '2024012');

-- 插入分类数据（10条）
INSERT INTO categories (cate_name, parent_id) VALUES
('书籍教材', 0),
('数码产品', 0),
('生活用品', 0),
('运动器材', 0),
('美妆护肤', 0),
('计算机类', 1),
('手机配件', 2),
('床上用品', 3),
('篮球足球', 4),
('面膜洁面', 5);

-- 插入商品数据（15条）
INSERT INTO products (prod_name, cate_id, seller_id, price, original_price, prod_desc, prod_status) VALUES
('Java编程思想', 6, 1, 25.00, 89.00, '九成新，无笔记', 1),
('MySQL从入门到精通', 6, 2, 18.00, 59.00, '正版书籍，少量划线', 1),
('iPhone充电器', 7, 3, 30.00, 149.00, '原装正品，没用几次', 1),
('纯棉四件套', 8, 4, 50.00, 199.00, '干净无破损', 2),
('标准7号篮球', 9, 5, 40.00, 129.00, '弹性很好', 1),
('补水面膜', 10, 6, 20.00, 99.00, '剩余20片', 1),
('大学英语四级真题', 1, 7, 10.00, 32.80, '只做了一套', 1),
('无线鼠标', 2, 8, 15.00, 59.00, '反应灵敏', 3),
('瑜伽垫', 4, 9, 22.00, 69.00, '厚度8mm', 1),
('洗面奶', 5, 10, 12.00, 45.00, '全新未拆封', 1),
('高等数学教材', 1, 11, 8.00, 42.00, '学长自用', 1),
('电竞耳机', 2, 12, 45.00, 199.00, '音质清晰', 1),
('晾衣架', 3, 1, 15.00, 35.00, '不锈钢材质', 1),
('足球', 9, 2, 35.00, 99.00, '气足耐用', 2),
('护手霜', 5, 3, 8.00, 29.00, '余量一半', 1);

-- 插入订单数据（12条）
INSERT INTO orders (order_no, buyer_id, seller_id, total_price, order_status) VALUES
('ORDER2026001', 2, 1, 25.00, 3),
('ORDER2026002', 3, 2, 18.00, 3),
('ORDER2026003', 4, 3, 30.00, 2),
('ORDER2026004', 5, 4, 50.00, 3),
('ORDER2026005', 6, 5, 40.00, 2),
('ORDER2026006', 7, 6, 20.00, 1),
('ORDER2026007', 8, 7, 10.00, 3),
('ORDER2026008', 9, 8, 15.00, 4),
('ORDER2026009', 10, 9, 22.00, 2),
('ORDER2026010', 11, 10, 12.00, 1),
('ORDER2026011', 12, 11, 8.00, 3),
('ORDER2026012', 1, 12, 45.00, 2);

-- 插入订单详情（15条）
INSERT INTO order_items (order_id, prod_id, price, quantity) VALUES
(1, 1, 25.00, 1),
(2, 2, 18.00, 1),
(3, 3, 30.00, 1),
(4, 4, 50.00, 1),
(5, 5, 40.00, 1),
(6, 6, 20.00, 1),
(7, 7, 10.00, 1),
(8, 8, 15.00, 1),
(9, 9, 22.00, 1),
(10, 10, 12.00, 1),
(11, 11, 8.00, 1),
(12, 12, 45.00, 1),
(1, 13, 15.00, 1),
(2, 14, 35.00, 1),
(3, 15, 8.00, 1);

-- 插入收藏数据（12条）
INSERT INTO favorites (user_id, prod_id) VALUES
(1, 2), (1, 3), (2, 1), (2, 5), (3, 4), (3, 6),
(4, 7), (5, 8), (6, 9), (7, 10), (8, 11), (9, 12);

ER截图