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图和类图