当前位置：首页 > news >正文

MySQL数据库———二手市场DDL,DML语句（课后练习

news 2026/8/23 16:54:39

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 '创建时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 物品分类表 CREATE TABLE `category` ( `category_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID', `category_name` VARCHAR(50) NOT NULL UNIQUE COMMENT '分类名称', `description` VARCHAR(200) COMMENT '分类描述' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 物品表 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 订单表 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 评价表 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DML语句：

-- 用户表数据 INSERT INTO `user` (`username`, `password`, `real_name`, `phone`, `email`, `role`) VALUES ('student01', '123456', '张三', '13800138001', 'zhangsan@example.com', 'student'), ('student02', '123456', '李四', '13800138002', 'lisi@example.com', 'student'), ('student03', '123456', '王五', '13800138003', 'wangwu@example.com', 'student'), ('student04', '123456', '赵六', '13800138004', 'zhaoliu@example.com', 'student'), ('student05', '123456', '孙七', '13800138005', 'sunqi@example.com', 'student'), ('teacher01', '123456', '周老师', '13900139001', 'zhou@school.com', 'teacher'), ('teacher02', '123456', '吴老师', '13900139002', 'wu@school.com', 'teacher'), ('admin01', '123456', '管理员', '13700137001', 'admin@school.com', 'admin'), ('student06', '123456', '郑八', '13800138006', 'zhengba@example.com', 'student'), ('student07', '123456', '钱九', '13800138007', 'qianjiu@example.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, '吉他音色不错');

查看全文

http://www.cnnetsun.cn/news/1428742.html