MYSQL实训--------------餐饮点餐系统

阿扬、、、

已于 2024-06-28 13:16:47 修改

阅读量937

点赞数 7

文章标签： mysql 数据库

于 2024-06-27 22:24:54 首次发布

本文链接：https://blog.csdn.net/2301_80888367/article/details/140015555

版权

1、E-R图

2、数据模型图

3、DDL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    email VARCHAR(100) UNIQUE COMMENT '邮箱地址',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    phone VARCHAR(20) COMMENT '电话号码'
) COMMENT='用户表';
 
CREATE TABLE user_wallet (
    wallet_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '餐厅ID',
    name VARCHAR(100) NOT NULL COMMENT '餐厅名称',
    address VARCHAR(255) NOT NULL COMMENT '餐厅地址',
    opening_hours VARCHAR(50) COMMENT '营业时间',
    contact_number VARCHAR(20) COMMENT '联系电话'
) COMMENT='餐厅表';
 
CREATE TABLE dish_categories (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '菜品分类ID',
    name VARCHAR(50) NOT NULL COMMENT '分类名称',
    restaurant_id INT NOT NULL COMMENT '所属餐厅ID',
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) COMMENT='菜品分类表';
 
CREATE TABLE dishes (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '菜品ID',
    name VARCHAR(100) NOT NULL COMMENT '菜品名称',
    description TEXT COMMENT '菜品描述',
    price DECIMAL(10, 2) NOT NULL COMMENT '菜品价格',
    category_id INT NOT NULL COMMENT '所属分类ID',
    restaurant_id INT NOT NULL COMMENT '所属餐厅ID',
    FOREIGN KEY (category_id) REFERENCES dish_categories(id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) COMMENT='菜品表';
 
CREATE TABLE carts (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '购物车ID',
    user_id INT NOT NULL COMMENT '用户ID',
    restaurant_id INT NOT NULL COMMENT '餐厅ID',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) COMMENT='购物车表';
 
CREATE TABLE cart_items (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '购物车项ID',
    cart_id INT NOT NULL COMMENT '购物车ID',
    dish_id INT NOT NULL COMMENT '菜品ID',
    quantity INT NOT NULL COMMENT '数量',
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (dish_id) REFERENCES dishes(id) ON DELETE CASCADE
) COMMENT='购物车项表';
 
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    user_id INT NOT NULL COMMENT '用户ID',
    restaurant_id INT NOT NULL COMMENT '餐厅ID',
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单日期',
    total_price DECIMAL(10, 2) NOT NULL  COMMENT '订单总价',
    status ENUM('待支付', '已支付', '已取消', '已完成') NOT NULL DEFAULT '待支付' COMMENT '订单状态',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) COMMENT='订单表';
 
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单项ID',
    order_id INT NOT NULL COMMENT '订单ID',
    dish_id INT NOT NULL COMMENT '菜品ID',
    quantity INT NOT NULL COMMENT '数量',
    price_per_item DECIMAL(10, 2) NOT NULL COMMENT '单价',
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (dish_id) REFERENCES dishes(id) ON DELETE CASCADE
) COMMENT='订单项表';

4、DML

-- 插入用户数据
INSERT INTO users (username, password, email, gender, phone) VALUES
('小鱼儿', 'xiaoyu_pass', 'xiaoyu@example.com', '男', '1234567890'),
('花无缺', 'huawu_pass', 'huawu@example.com', '男', '0987654321'),
('小香', 'xiaoxiang_pass', 'xiaoxiang@example.com', '男', '15603102023'),
('小玖', 'xiaojiu_pass', 'xiaojiu@example.com', '女', '15530063071'),
('苏樱', 'suying_pass', 'suying@example.com', '女', '5555555555');
 
-- 插入钱包数据
INSERT INTO `user_wallet` VALUES ('1', '1', '422.00', '2024-06-08 22:07:42');
INSERT INTO `user_wallet` VALUES ('2', '2', '600.00', '2024-06-04 22:07:42');
INSERT INTO `user_wallet` VALUES ('3', '3', '150.00', '2024-06-03 22:07:42');
INSERT INTO `user_wallet` VALUES ('4', '4', '489.00', '2024-06-07 22:07:42');
INSERT INTO `user_wallet` VALUES ('5', '5', '319.00', '2024-06-06 22:07:42');
 
-- 插入餐厅数据
INSERT INTO restaurants (name, address, opening_hours, contact_number) VALUES
('江湖酒楼', '京城大街1号', '09:00-22:00', '12345678'),
('美味轩', '长安路88号', '10:00-21:30', '87654321'),
('蟹堡王', '海洋大街3号', '8:00-23:30', '987456123'),
('美味坊', '京津路666号', '12:00-4:00', '963852741'),
('清风阁', '西湖路123号', '11:00-23:00', '98765432');
 
 
-- 插入菜品分类数据
INSERT INTO dish_categories (name, restaurant_id) VALUES
('川菜', 1),
('粤菜', 2),
('湘菜', 3),
('鲁菜', 1),
('苏菜', 2),
('浙菜', 3);
 
 
-- 插入菜品数据
INSERT INTO dishes (name, description, price, category_id, restaurant_id) VALUES
('水煮鱼', '麻辣鲜香，回味无穷', 58.00, 1, 1),
('宫保鸡丁', '色泽红亮，口感鲜美', 48.00, 1, 1),
('麻婆豆腐', '麻辣可口，下饭佳品', 38.00, 1, 1),
('白切鸡', '皮爽肉滑，鲜美无比', 68.00, 2, 2),
('清蒸鲈鱼', '鲜嫩可口，营养丰富', 78.00, 2, 2),
('菠萝咕噜肉', '酸甜可口，色泽诱人', 52.00, 2, 2),
('剁椒鱼头', '香辣可口，回味无穷', 62.00, 3, 3),
('辣椒炒肉', '香辣可口，下饭佳品', 42.00, 3, 3),
('红烧肉', '肥而不腻，入口即化', 55.00, 3, 3);
 
 
-- 插入购物车数据（假设小鱼儿在江湖酒楼和美味轩有购物车）
INSERT INTO carts (user_id, restaurant_id) VALUES
(1, 1), -- 小鱼儿在江湖酒楼
(1, 2); -- 小鱼儿在美味轩
 
 
-- 获取购物车id
SET @cartId1 = (SELECT id FROM carts WHERE user_id = 1 AND restaurant_id = 1);
SET @cartId2 = (SELECT id FROM carts WHERE user_id = 1 AND restaurant_id = 2);
 
 
-- 插入购物车项数据（假设小鱼儿在江湖酒楼选择了前两个菜品，在美味轩选择了后两个菜品）
INSERT INTO cart_items (cart_id, dish_id, quantity) VALUES
(@cartId1, 1, 2), -- 水煮鱼
(@cartId1, 2, 1), -- 宫保鸡丁
(@cartId2, 4, 1), -- 白切鸡
(@cartId2, 5, 3); -- 清蒸鲈鱼
 
 
-- 插入订单数据（假设小鱼儿在江湖酒楼下了一个订单）
INSERT INTO orders (user_id, restaurant_id,total_price) VALUES
(1, 1,50.00); -- 小鱼儿在江湖酒楼下订单
 
 
-- 获取订单id
SET @orderId = LAST_INSERT_ID();
 
 
-- 插入订单项数据（假设小鱼儿购买了购物车中的水煮鱼和宫保鸡丁）
INSERT INTO order_items (order_id, dish_id, quantity, price_per_item) VALUES
(@orderId, 1, 2, 58.00), -- 菜品：水煮鱼
(@orderId, 2, 1, 48.00); -- 菜品：宫保鸡丁




-- 插入购物车数据（假设小香在江湖酒楼和清风阁有购物车）
INSERT INTO carts (user_id, restaurant_id) VALUES
(3, 1), -- 小香在江湖酒楼
(3, 3); -- 小香在清风阁

-- 获取购物车id
SET @cartId1 = (SELECT id FROM carts WHERE user_id = 3 AND restaurant_id = 1);
SET @cartId2 = (SELECT id FROM carts WHERE user_id = 3 AND restaurant_id = 3);


-- 插入购物车项数据（假设小香在江湖酒楼选择了前两个菜品，在清风阁选择了后两个菜品）
INSERT INTO cart_items (cart_id, dish_id, quantity) VALUES
(@cartId1, 3, 2), -- 麻婆豆腐
(@cartId1, 2, 1), -- 宫保鸡丁
(@cartId2, 8, 1), -- 辣椒炒肉
(@cartId2, 9, 3); -- 红烧肉

-- 插入订单数据（假设小香在清风阁下了一个订单）
INSERT INTO orders (user_id, restaurant_id,total_price) VALUES
(3, 3,100.00); -- 小香在清风阁下订单

-- 获取订单id
SET @orderId1 = LAST_INSERT_ID();

-- 插入订单项数据（假设小香购买了购物车中的辣椒炒肉和红烧肉）
INSERT INTO order_items (order_id, dish_id, quantity, price_per_item) VALUES
(@orderId1, 8, 1, 42.00), -- 菜品：辣椒炒肉
(@orderId1, 9, 3, 55.00); -- 菜品：红烧肉

-- 插入购物车数据（假设小玖在蟹堡王和美味坊有购物车）
INSERT INTO carts (user_id, restaurant_id) VALUES
(4, 4), -- 小玖在蟹堡王
(4, 5); -- 小玖在美味坊

-- 获取购物车id
SET @cartId3 = (SELECT id FROM carts WHERE user_id = 4 AND restaurant_id = 4);
SET @cartId4 = (SELECT id FROM carts WHERE user_id = 4 AND restaurant_id = 5);


-- 插入购物车项数据（假设小玖在美味坊选择了前两个菜品，在蟹堡王选择了后两个菜品）
INSERT INTO cart_items (cart_id, dish_id, quantity) VALUES
(@cartId3,4,2), -- 白切鸡
(@cartId3,5,1), -- 清蒸鲈鱼
(@cartId4,	7,1), -- 剁椒鱼头
(@cartId4,9,3); -- 红烧肉

-- 插入订单数据（假设小玖在蟹堡王下了一个订单）
INSERT INTO orders (user_id, restaurant_id,total_price) VALUE
(4,4,150.00);-- 小玖在蟹堡王下订单

-- 获取订单id
SET @orderId2= LAST_INSERT_ID();

-- 插入订单项数据（假设小玖购买了购物车中的清蒸鲈鱼和剁椒鱼头）
INSERT INTO order_items (order_id, dish_id, quantity, price_per_item) VALUES
(@orderId2, 5,1,78.00),-- 菜品：清蒸鲈鱼
(@orderId2 ,7,1,62.00); -- 菜品：剁椒鱼头

-- 插入购物车数据（假设花无缺在美味坊和美味轩有购物车）
INSERT INTO carts (user_id, restaurant_id) VALUES
(2, 4), -- 花无缺在美味坊
(2,2 ); -- 花无缺在美味轩

-- 获取购物车id
SET @cartId3 = (SELECT id FROM carts WHERE user_id = 2 AND restaurant_id = 4);
SET @cartId4 = (SELECT id FROM carts WHERE user_id = 2 AND restaurant_id = 2);

-- 插入购物车项数据（假设花无缺在美味坊选择了前两个菜品，在美味轩选择了后两个菜品）
INSERT INTO cart_items (cart_id, dish_id, quantity) VALUES
(@cartId3,3,2), -- 麻婆豆腐
(@cartId3,5,1), -- 清蒸鲈鱼
(@cartId4,	7,1), -- 剁椒鱼头
(@cartId4,6,3); -- 菠萝咕噜肉

-- 插入订单数据（假设花无缺在美味轩下了一个订单）
INSERT INTO orders (user_id, restaurant_id,total_price) VALUE
(2,2,250.00);-- 花无缺在美味轩下订单

-- 获取订单id
SET @orderId3= LAST_INSERT_ID();

-- 插入订单项数据（假设花无缺购买了购物车中的清蒸鲈鱼和菠萝咕噜肉）
INSERT INTO order_items (order_id, dish_id, quantity, price_per_item) VALUES
(@orderId3, 5,1,78.00),-- 菜品：清蒸鲈鱼
(@orderId3 ,6,3,52.00); -- 菜品：菠萝咕噜肉

-- 插入购物车数据（假设苏樱在江湖酒楼和美味坊有购物车）
INSERT INTO carts (user_id, restaurant_id) VALUES
(5, 1), -- 苏樱在江湖酒楼
(5, 4); -- 苏樱在美味坊

-- 获取购物车id5
SET @cartId5 = (SELECT id FROM carts WHERE user_id = 5 AND restaurant_id = 1);
SET @cartId6 = (SELECT id FROM carts WHERE user_id =5  AND restaurant_id = 4);

-- 插入购物车项数据（假设苏樱在美味坊选择了前两个菜品，在江湖酒楼选择了后两个菜品）
INSERT INTO cart_items (cart_id, dish_id, quantity) VALUES
(@cartId5,1,2), -- 水煮鱼
(@cartId5,5,1), -- 清蒸鲈鱼
(@cartId6,	8,2), -- 辣椒炒肉
(@cartId6,6,3); -- 菠萝咕噜肉

-- 插入订单数据（假设苏樱在美味坊下了一个订单）
INSERT INTO orders (user_id, restaurant_id,total_price) VALUE
(5,4,350.00);-- 苏樱在美味坊下订单

-- 获取订单id
SET @orderId4= LAST_INSERT_ID();

-- 插入订单项数据（假设苏樱购买了购物车中的水煮鱼和辣椒炒肉）
INSERT INTO order_items (order_id, dish_id, quantity, price_per_item) VALUES
(@orderId4, 1,2,58.00),-- 菜品：水煮鱼
(@orderId4 ,8,2,42.00); -- 菜品：辣椒炒肉

5、简单查询

1、查询用户信息，仅显示用户的姓名与手机号，用中文显示列名。中文显示姓名列与手机号列

select username as '姓名',phone as '手机号' from users ;

2、模糊查询

create index idx_name on dishes(`name`);
explain select *from dishes where `name` like '香%';

3、统计用户订单信息，查询所有用户的下单数量，并进行倒序排列

select order_id,count(order_id)as '订单数量'
from order_items
group by order_id
order by `订单数量` desc;

6、多表联合查询

1、查询用户的基本信息，订单信息。

SELECT 
    u.id, -- 选择用户的用户ID
    u.username, -- 选择用户名
    u.email, -- 选择邮箱
    u.phone, -- 选择电话
    uw.wallet_id, -- 选择钱包ID
    uw.balance -- 选择钱包余额
FROM 
    users u -- 从用户表中选择数据
JOIN 
    user_wallet uw ON u.id = uw.user_id; -- 使用JOIN连接用户表和钱包表，连接条件是两个表中的user_id相同

2、查看订单中下单最多的菜品对应的类别。

select
	p.name as '下单最多的菜品种类'
from 
	dish_categories p
inner join 
	dishes d on p.id = d.category_id
inner join 
	order_items oi on d.id = oi.dish_id
where
	oi.order_id in (
	select 
	o.id
	from
	orders o
	where 
	o.user_id=(
	select 
	user_id
	from	
	orders
	group by
	user_id	
	order by
	count(*) desc
	limit 1
		)
)
group by
	p.name
order by
	sum(oi.quantity) desc
limit 1;

3、查询下单总金额最多的用户，并查询用户的全部信息

set @topUserId =(
	select	user_id
	from orders
	group by  user_id
	order by
	sum(total_price) desc
	limit 1
);
select 
	u.*,
	uw.balance as wallet_balance
	from users u
join	
	user_wallet uw on u.id = uw.user_id
where
	u.id = @topUserId ;

7、触发器---插入---修改---删除

1、用户表添加语句添加触发器，要求在添加用户信息时同时初始化用户钱包表数据，初始金额为0。

delimiter $$
create trigger after_user_insert
after insert on users
for each row
begin
	insert into user_wallet (user_id,balance) values(NEW.id,0.00);
end ;
$$
delimiter ;

测试语句

insert into users values(0,'小刘','xiaoliu_password','xiaoliu@emplain','男','7418529630');

2、菜品表修改语句添加触发器，要求在修改商品售价时不允许上下浮动超过10%。

delimiter $$
create trigger check_dish_price_chage
before update on dishes
for each row
begin
	declare original_price decimal(10,2);
	declare new_price decimal(10,2);
	declare price_difference decimal(10,2);

	set original_price = OLD.price;
	set new_price = NEW.price;

	set price_difference =(new_price - original_price) / original_price * 100;

	if new_price > original_price and price_difference > 10 then 
		signal sqlstate '45000'
		set message_text = '价格超出';
	end if ;
	if new_price < original_price and abs(price_difference) > 10 then
		signal sqlstate '45000'
		set message_text = '价格超出';
	end if ; 
end ;
$$
delimiter ;

测试语句

update dishes set price = 100.00 where `name` = '麻婆豆腐';

手动测试

3、订单表删除语句添加触发器，要求在删除订单信息时先删除订单详情表中的订单信息。

delimiter $$
create trigger before_delete_order
before delete on orders 
for each row  
begin 
		delete from order_items where order_id = OLD.id;
end;
$$
delimiter ;

测试语句

delete from  order_items where order_id =@orderId1;
select *  from order_items where order_id =@orderId1;

8、存储过程创建

1.计算某个用户的总订单金额

delimiter $$
create procedure orderAmount (in userID int)
begin
	declare total_amount decimal (10,2) default 0.00;
	
	select sum(oi.quantity * oi.price_per_item) into total_amount
	from order_items oi
	join orders o on oi.order_id = o.id
	where o.user_id = userID;

	select total_amount as total_order_amount;
end;
$$
delimiter ;

call orderAmount (1);

2.在数据库中创建一个新的订单，并更新相关的用户钱包余额和订单详情。

DELIMITER //

CREATE PROCEDURE UpdateUserWalletBalanceAfterPayment(IN p_orderId INT)
BEGIN
    -- 声明变量
    DECLARE v_userId INT;
    DECLARE v_totalPrice DECIMAL(10, 2);
    DECLARE v_walletBalance DECIMAL(10, 2);

    -- 从订单表中获取用户ID和订单总价
    SELECT user_id, total_price
    INTO v_userId, v_totalPrice
    FROM orders
    WHERE id = p_orderId;

    -- 从用户钱包表中获取当前余额
    SELECT balance
    INTO v_walletBalance
    FROM user_wallet
    WHERE user_id = v_userId;

    -- 检查余额是否足够支付
    IF v_walletBalance >= v_totalPrice THEN
        -- 更新用户钱包余额
        UPDATE user_wallet
        SET balance = v_walletBalance - v_totalPrice
        WHERE user_id = v_userId;

        -- 更新订单状态为已支付（可选，取决于您的业务逻辑）
        UPDATE orders
        SET status = '已支付'
        WHERE id = p_orderId;

        -- 输出成功信息（可选）
        SELECT '支付成功，余额已更新。';
    ELSE
        -- 余额不足，输出错误信息（可选）
        SELECT '余额不足，支付失败。';
    END IF;
END //

DELIMITER ;

CALL UpdateUserWalletBalanceAfterPayment(1); -- 假设订单ID为1的订单已被支付

买的起效果：

买不起效果：