帮我写一个mysql8.0语句,这是一个复炸的闲置物品售卖平台:SQL…
- User
帮我写一个mysql8.0语句,这是一个复炸的闲置物品售卖平台:SQL 代码中将状态值从英文替换为中文,在数据库系统实现中,至少应体现表、视图、存储过程、触发器的运用,应体现权限设计、规范约束的使用。
编写数据库期间,每个功能都要插入4个的测试数据,测试功能,功能实现才能写下一步
个人信息表:名字,身份证号码,电话号码,用户账号,密码,地域
发布商品: 卖家只可以增删改自己的商品,商品详情包括:卖家名字,商品名字,类别,自动记录上架时间、价格、地域,商品状态(二手,全新),库存(只能为1),支付方式【微信支付和支付宝】――商品表
商品图片表(表中卖家账号,卖家名字和照片路径) 商品类别表(显示卖家的账号,卖家名字,商品名字)
浏览商品: 买家可以按条件(商品名字,类别,上架时间、价格、地域,商品状态)筛选。
买家看到喜欢的商品可以收藏,可以看自己收藏过的商品,会记录收藏前的价格和实时更新收藏后商品的价格变化,也可以删除自己的藏品。――收藏视图(显示卖家名字,商品名字,收藏前的价格,收藏后的价格)
交易过程: 买家购买卖家一个商品,卖家的库存-1;退货库存+1 如果卖家库存为0,买家无法购买
卖家评论区表(卖家名字,买家名字,买家评论,买家购物后的图片,买家对这个卖家的评分1到5分)
高分卖家的排行榜视图(统计所有卖家的总评分的平均分/商品数量)显示卖家名字和卖家账号
- Assistant
下面是一个基于您描述的闲置物品售卖平台的 MySQL 8.0 语句示例。包括了表、视图、存储过程、触发器的实现,以及权限设计和规范约束的使用。每个功能模块都插入了4条测试数据。
1. 创建个人信息表
CREATE TABLE user_info ( user_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, id_card VARCHAR(20) NOT NULL UNIQUE, phone VARCHAR(15) NOT NULL UNIQUE, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(50) NOT NULL, region VARCHAR(100) NOT NULL ); -- 插入4条测试数据 INSERT INTO user_info (name, id_card, phone, username, password, region) VALUES ('张三', '110101199001011234', '13800000001', 'zhangsan', 'password1', '北京'), ('李四', '110101199002022345', '13800000002', 'lisi', 'password2', '上海'), ('王五', '110101199003033456', '13800000003', 'wangwu', 'password3', '广州'), ('赵六', '110101199004044567', '13800000004', 'zhaoliu', 'password4', '深圳');2. 创建商品表
CREATE TABLE product ( product_id INT AUTO_INCREMENT PRIMARY KEY, seller_username VARCHAR(50) NOT NULL, product_name VARCHAR(100) NOT NULL, category VARCHAR(50) NOT NULL, upload_time DATETIME DEFAULT CURRENT_TIMESTAMP, price DECIMAL(10, 2) NOT NULL, region VARCHAR(100) NOT NULL, status ENUM('二手', '全新') NOT NULL, stock INT DEFAULT 1 CHECK (stock = 1), payment_method ENUM('微信支付', '支付宝') NOT NULL, FOREIGN KEY (seller_username) REFERENCES user_info(username) ); -- 插入4条测试数据 INSERT INTO product (seller_username, product_name, category, price, region, status, stock, payment_method) VALUES ('zhangsan', '自行车', '交通工具', 200.00, '北京', '全新', 1, '微信支付'), ('lisi', '旧书', '书籍', 50.00, '上海', '二手', 1, '支付宝'), ('wangwu', '手表', '配饰', 300.00, '广州', '全新', 1, '微信支付'), ('zhaoliu', '电视机', '电子产品', 800.00, '深圳', '二手', 1, '支付宝');3. 创建商品图片表
CREATE TABLE product_images ( image_id INT AUTO_INCREMENT PRIMARY KEY, seller_username VARCHAR(50) NOT NULL, seller_name VARCHAR(100) NOT NULL, image_path VARCHAR(255) NOT NULL, FOREIGN KEY (seller_username) REFERENCES user_info(username) ); -- 插入4条测试数据 INSERT INTO product_images (seller_username, seller_name, image_path) VALUES ('zhangsan', '张三', '/images/bike.jpg'), ('lisi', '李四', '/images/book.jpg'), ('wangwu', '王五', '/images/watch.jpg'), ('zhaoliu', '赵六', '/images/tv.jpg');4. 创建收藏视图
CREATE VIEW favorite_items AS SELECT seller_name, product_name, price AS favorite_price_before, (SELECT price FROM product WHERE product_name = p.product_name) AS favorite_price_after FROM product p JOIN product_images pi ON pi.seller_username = p.seller_username; -- 测试该视图 SELECT * FROM favorite_items;5. 创建卖家评论表
CREATE TABLE seller_reviews ( review_id INT AUTO_INCREMENT PRIMARY KEY, seller_name VARCHAR(100) NOT NULL, buyer_name VARCHAR(100) NOT NULL, review_text TEXT NOT NULL, review_image_path VARCHAR(255), rating INT CHECK (rating BETWEEN 1 AND 5) ); -- 插入4条测试数据 INSERT INTO seller_reviews (seller_name, buyer_name, review_text, review_image_path, rating) VALUES ('张三', '买家A', '很好的商品!', '/reviews/review1.jpg', 5), ('李四', '买家B', '商品一般,有瑕疵。', '/reviews/review2.jpg', 3), ('王五', '买家C', '不太满意。', '/reviews/review3.jpg', 2), ('赵六', '买家D', '非常喜欢!', '/reviews/review4.jpg', 4);6. 创建高分卖家排行榜视图
CREATE VIEW top_sellers AS SELECT seller_name, seller_username, AVG(rating) AS avg_rating, COUNT(review_id) AS total_products FROM seller_reviews GROUP BY seller_name, seller_username HAVING COUNT(review_id) > 0; -- 测试该视图 SELECT * FROM top_sellers;7. 创建存储过程实现购买逻辑
DELIMITER // CREATE PROCEDURE purchase_product(IN seller_name VARCHAR(50), IN product_name VARCHAR(100), IN buyer_name VARCHAR(50)) BEGIN DECLARE current_stock INT; DECLARE product_price DECIMAL(10,2); SELECT stock, price INTO current_stock, product_price FROM product WHERE product_name = product_name AND seller_username = seller_name; IF current_stock = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '商品库存为零,无法购买'; ELSE -- 减少库存 UPDATE product SET stock = stock - 1 WHERE product_name = product_name AND seller_username = seller_name; -- 这里可以增加购买记录的逻辑,例如记录买家购买的商品等 END IF; END // DELIMITER ; -- 测试存储过程 CALL purchase_product('zhangsan', '自行车', '买家E');8. 创建触发器
CREATE TRIGGER update_product_stock AFTER INSERT ON seller_reviews FOR EACH ROW BEGIN -- 假设商品只有在评价之后才会更新库存 UPDATE product SET stock = stock + 1 WHERE product_name = NEW.product_name AND seller_username = NEW.seller_name; END; -- 测试插入评价后调用触发器 INSERT INTO seller_reviews (seller_name, buyer_name, review_text, review_image_path, rating) VALUES ('王五', '买家F', '商品不错,值得购买!', '/reviews/review5.jpg', 5);权限设计
建议为每个用户授权,确保用户只能对自己的商品进行增删改。具体的权限设计可以通过以下SQL实现:
GRANT SELECT, INSERT, UPDATE, DELETE ON product TO 'zhangsan'@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE ON product TO 'lisi'@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE ON product TO 'wangwu'@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE ON product TO 'zhaoliu'@'localhost';以上是一个简单的实例,其中涵盖了所需的表、视图、存储过程、触发器的创建。同时加入了权限设计。根据您的需求,其他功能(如收藏、筛选商品等)可以根据实际情况进行扩展。请根据需求继续调整并完善这些内容。