USER
sql
CREATE DATABASE IF NOT EXISTS campus_food_delivery_advanced;
USE campus_food_delivery_advanced;
2. 创建表
sql
-- 用户表，增加用户积分字段
CREATE TABLE Users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
user_type ENUM('student', 'restaurant', 'admin', 'delivery_driver') NOT NULL,
contact_info VARCHAR(100),
points INT DEFAULT 0
);
-- 餐厅表，增加评分和评论数
CREATE TABLE Restaurants (
restaurant_id INT AUTO_INCREMENT PRIMARY KEY,
restaurant_name VARCHAR(100) NOT NULL,
address VARCHAR(255) NOT NULL,
contact_number VARCHAR(20) NOT NULL,
user_id INT NOT NULL,
rating DECIMAL(3, 1) DEFAULT 0.0,
review_count INT DEFAULT 0,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
-- 菜品分类表
CREATE TABLE DishCategories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL
);
-- 菜品表，关联菜品分类
CREATE TABLE Dishes (
dish_id INT AUTO_INCREMENT PRIMARY KEY,
dish_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
description TEXT,
restaurant_id INT NOT NULL,
category_id INT,
FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id),
FOREIGN KEY (category_id) REFERENCES DishCategories(category_id)
);
-- 订单表，增加配送信息
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
restaurant_id INT NOT NULL,
order_time DATETIME DEFAULT CURRENT_TIMESTAMP,
status ENUM('pending', 'accepted', 'preparing', 'out_for_delivery', 'delivered', 'cancelled') DEFAULT 'pending',
delivery_address VARCHAR(255),
delivery_driver_id INT,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id),
FOREIGN KEY (delivery_driver_id) REFERENCES Users(user_id)
);
-- 订单详情表
CREATE TABLE OrderDetails (
order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
dish_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
FOREIGN KEY (dish_id) REFERENCES Dishes(dish_id)
);
-- 评论表
CREATE TABLE Reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
restaurant_id INT NOT NULL,
rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
review_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id)
);
-- 优惠券表
CREATE TABLE Coupons (
coupon_id INT AUTO_INCREMENT PRIMARY KEY,
coupon_code VARCHAR(20) NOT NULL UNIQUE,
discount DECIMAL(5, 2) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
is_active BOOLEAN DEFAULT TRUE
);
-- 用户优惠券关联表
CREATE TABLE UserCoupons (
user_coupon_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
coupon_id INT NOT NULL,
is_used BOOLEAN DEFAULT FALSE,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (coupon_id) REFERENCES Coupons(coupon_id)
);
3. 创建视图
sql
-- 创建一个视图，显示每个订单的详细信息，包括菜品分类
CREATE VIEW OrderDetailView AS
SELECT
o.order_id,
u.username AS customer_name,
r.restaurant_name,
o.order_time,
o.status,
GROUP_CONCAT(d.dish_name SEPARATOR ', ') AS dishes,
GROUP_CONCAT(dc.category_name SEPARATOR ', ') AS categories,
SUM(od.quantity * d.price) AS total_amount
FROM
Orders o
JOIN
Users u ON o.user_id = u.user_id
JOIN
Restaurants r ON o.restaurant_id = r.restaurant_id
JOIN
OrderDetails od ON o.order_id = od.order_id
JOIN
Dishes d ON od.dish_id = d.dish_id
LEFT JOIN
DishCategories dc ON d.category_id = dc.category_id
GROUP BY
o.order_id, u.username, r.restaurant_name, o.order_time, o.status;
-- 创建一个视图，显示每个餐厅的平均评分和评论数
CREATE VIEW RestaurantRatingView AS
SELECT
r.restaurant_id,
r.restaurant_name,
AVG(re.rating) AS average_rating,
COUNT(re.review_id) AS review_count
FROM
Restaurants r
LEFT JOIN
Reviews re ON r.restaurant_id = re.restaurant_id
GROUP BY
r.restaurant_id, r.restaurant_name;
4. 创建存储过程
sql
-- 创建一个存储过程，用于添加新订单，支持优惠券使用
DELIMITER //
CREATE PROCEDURE AddOrderWithCoupon(
IN p_user_id INT,
IN p_restaurant_id INT,
IN p_dish_ids VARCHAR(255),
IN p_quantities VARCHAR(255),
IN p_coupon_code VARCHAR(20)
)
BEGIN
DECLARE v_order_id INT;
DECLARE v_dish_id INT;
DECLARE v_quantity INT;
DECLARE v_pos INT;
DECLARE v_pos2 INT;
DECLARE v_coupon_id INT;
DECLARE v_discount DECIMAL(5, 2);
-- 检查优惠券是否有效
SELECT coupon_id, discount INTO v_coupon_id, v_discount
FROM Coupons
WHERE coupon_code = p_coupon_code AND is_active = TRUE AND start_date <= CURRENT_TIMESTAMP AND end_date >= CURRENT_TIMESTAMP;
-- 添加订单
INSERT INTO Orders (user_id, restaurant_id) VALUES (p_user_id, p_restaurant_id);
SET v_order_id = LAST_INSERT_ID();
-- 拆分并插入订单详情
SET v_pos = 1;
WHILE v_pos > 0 DO
SET v_pos2 = LOCATE(',', p_dish_ids, v_pos);
IF v_pos2 = 0 THEN
SET v_dish_id = CAST(SUBSTRING(p_dish_ids, v_pos) AS UNSIGNED);
ELSE
SET v_dish_id = CAST(SUBSTRING(p_dish_ids, v_pos, v_pos2 - v_pos) AS UNSIGNED);
END IF;
SET v_pos = LOCATE(',', p_quantities, v_pos);
IF v_pos = 0 THEN
SET v_quantity = CAST(SUBSTRING(p_quantities, v_pos) AS UNSIGNED);
ELSE
SET v_quantity = CAST(SUBSTRING(p_quantities, v_pos, v_pos2 - v_pos) AS UNSIGNED);
END IF;
INSERT INTO OrderDetails (order_id, dish_id, quantity) VALUES (v_order_id, v_dish_id, v_quantity);
SET v_pos = v_pos2 + 1;
END WHILE;
-- 如果优惠券有效，更新用户优惠券状态并计算折扣
IF v_coupon_id IS NOT NULL THEN
UPDATE UserCoupons
SET is_used = TRUE
WHERE user_id = p_user_id AND coupon_id = v_coupon_id;
-- 这里可以添加逻辑来更新订单总金额，考虑折扣
END IF;
END //
DELIMITER ;
-- 创建一个存储过程，用于更新餐厅评分
DELIMITER //
CREATE PROCEDURE UpdateRestaurantRating(IN p_restaurant_id INT)
BEGIN
DECLARE v_avg_rating DECIMAL(3, 1);
DECLARE v_review_count INT;
SELECT AVG(rating), COUNT(review_id) INTO v_avg_rating, v_review_count
FROM Reviews
WHERE restaurant_id = p_restaurant_id;
UPDATE Restaurants
SET rating = v_avg_rating, review_count = v_review_count
WHERE restaurant_id = p_restaurant_id;
END //
DELIMITER ;
5. 创建触发器
sql
-- 创建一个触发器，在用户下单后更新用户积分
DELIMITER //
CREATE TRIGGER AfterOrderInsert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
-- 假设每消费10元获得1积分
UPDATE Users
SET points = points + (SELECT SUM(od.quantity * d.price) / 10
FROM OrderDetails od
JOIN Dishes d ON od.dish_id = d.dish_id
WHERE od.order_id = NEW.order_id)
WHERE user_id = NEW.user_id;
END //
DELIMITER ;
-- 创建一个触发器，在插入评论后更新餐厅评分
DELIMITER //
CREATE TRIGGER AfterReviewInsert
AFTER INSERT ON Reviews
FOR EACH ROW
BEGIN
CALL UpdateRestaurantRating(NEW.restaurant_id);
END //
DELIMITER ;
6. 权限设计
sql
-- 创建一个普通用户，用于应用程序连接
CREATE USER 'campus_app_user'@'localhost' IDENTIFIED BY 'app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.Users TO 'campus_app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.Restaurants TO 'campus_app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.Dishes TO 'campus_app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.Orders TO 'campus_app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.OrderDetails TO 'campus_app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.Reviews TO 'campus_app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.Coupons TO 'campus_app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON campus_food_delivery_advanced.UserCoupons TO 'campus_app_user'@'localhost';
-- 创建一个管理员用户
CREATE USER 'campus_admin_user'@'localhost' IDENTIFIED BY 'admin_app_password';
GRANT ALL PRIVILEGES ON campus_food_delivery_advanced.* TO 'campus_admin_user'@'localhost';
FLUSH PRIVILEGES;
根据上面sql代码，帮我做一个ER图和类图