餐饮点餐的简单MySQL集合

ER图

模型图（没有进行排序，混乱） 

 

 DDL和DML

/*
Navicat MySQL Data TransferSource Server         : Mylink
Source Server Version : 50726
Source Host           : localhost:3306
Source Database       : schooldbTarget Server Type    : MYSQL
Target Server Version : 50726
File Encoding         : 65001Date: 2024-06-28 00:49:34
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `carts`
-- ----------------------------
DROP TABLE IF EXISTS `carts`;
CREATE TABLE `carts` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '购物车ID',`user_id` int(11) NOT NULL COMMENT '用户ID',`restaurant_id` int(11) NOT NULL COMMENT '餐厅ID',PRIMARY KEY (`id`) USING BTREE,KEY `user_id` (`user_id`) USING BTREE,KEY `restaurant_id` (`restaurant_id`) USING BTREE,CONSTRAINT `carts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,CONSTRAINT `carts_ibfk_2` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='购物车表';-- ----------------------------
-- Records of carts
-- ----------------------------
INSERT INTO `carts` VALUES ('2', '1', '2');
INSERT INTO `carts` VALUES ('3', '3', '3');
INSERT INTO `carts` VALUES ('4', '5', '5');
INSERT INTO `carts` VALUES ('5', '4', '6');-- ----------------------------
-- Table structure for `cart_items`
-- ----------------------------
DROP TABLE IF EXISTS `cart_items`;
CREATE TABLE `cart_items` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '购物车项ID',`cart_id` int(11) NOT NULL COMMENT '购物车ID',`dish_id` int(11) NOT NULL COMMENT '菜品ID',`quantity` int(11) NOT NULL COMMENT '数量',PRIMARY KEY (`id`) USING BTREE,KEY `cart_id` (`cart_id`) USING BTREE,KEY `dish_id` (`dish_id`) USING BTREE,CONSTRAINT `cart_items_ibfk_1` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`id`) ON DELETE CASCADE,CONSTRAINT `cart_items_ibfk_2` FOREIGN KEY (`dish_id`) REFERENCES `dishes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='购物车项表';-- ----------------------------
-- Records of cart_items
-- ----------------------------
INSERT INTO `cart_items` VALUES ('3', '2', '4', '1');
INSERT INTO `cart_items` VALUES ('4', '2', '5', '3');
INSERT INTO `cart_items` VALUES ('5', '3', '2', '4');-- ----------------------------
-- Table structure for `dishes`
-- ----------------------------
DROP TABLE IF EXISTS `dishes`;
CREATE TABLE `dishes` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜品ID',`name` varchar(100) NOT NULL COMMENT '菜品名称',`description` text COMMENT '菜品描述',`price` decimal(10,2) NOT NULL COMMENT '菜品价格',`category_id` int(11) NOT NULL DEFAULT '1' COMMENT '所属分类ID',`restaurant_id` int(11) NOT NULL DEFAULT '1' COMMENT '所属餐厅ID',`stock_quantity` varchar(255) NOT NULL COMMENT '库存数量',PRIMARY KEY (`id`) USING BTREE,KEY `category_id` (`category_id`) USING BTREE,KEY `restaurant_id` (`restaurant_id`) USING BTREE,CONSTRAINT `dishes_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `dish_categories` (`id`) ON DELETE CASCADE,CONSTRAINT `dishes_ibfk_2` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='菜品表';-- ----------------------------
-- Records of dishes
-- ----------------------------
INSERT INTO `dishes` VALUES ('1', '水煮鱼', '麻辣鲜香，回味无穷', '102.75', '1', '1', '190');
INSERT INTO `dishes` VALUES ('2', '宫保鸡丁', '色泽红亮，口感鲜美', '48.00', '1', '1', '250');
INSERT INTO `dishes` VALUES ('3', '麻婆豆腐', '麻辣可口，下饭佳品', '38.00', '1', '1', '140');
INSERT INTO `dishes` VALUES ('4', '白切鸡', '皮爽肉滑，鲜美无比', '68.00', '2', '2', '300');
INSERT INTO `dishes` VALUES ('5', '清蒸鲈鱼', '鲜嫩可口，营养丰富', '78.00', '2', '2', '420');
INSERT INTO `dishes` VALUES ('6', '菠萝咕噜肉', '酸甜可口，色泽诱人', '52.00', '2', '2', '480');
INSERT INTO `dishes` VALUES ('7', '剁椒鱼头', '香辣可口，回味无穷', '62.00', '3', '3', '500');
INSERT INTO `dishes` VALUES ('8', '辣椒炒肉', '香辣可口，下饭佳品', '42.00', '3', '3', '350');
INSERT INTO `dishes` VALUES ('9', '红烧肉', '肥而不腻，入口即化', '55.00', '3', '3', '250');
INSERT INTO `dishes` VALUES ('16', '大鱼头', null, '55.00', '1', '1', '100');-- ----------------------------
-- Table structure for `dish_categories`
-- ----------------------------
DROP TABLE IF EXISTS `dish_categories`;
CREATE TABLE `dish_categories` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜品分类ID',`name` varchar(50) NOT NULL COMMENT '分类名称',`restaurant_id` int(11) NOT NULL COMMENT '所属餐厅ID',PRIMARY KEY (`id`) USING BTREE,KEY `restaurant_id` (`restaurant_id`) USING BTREE,CONSTRAINT `dish_categories_ibfk_1` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='菜品分类表';-- ----------------------------
-- Records of dish_categories
-- ----------------------------
INSERT INTO `dish_categories` VALUES ('1', '川菜', '1');
INSERT INTO `dish_categories` VALUES ('2', '粤菜', '2');
INSERT INTO `dish_categories` VALUES ('3', '湘菜', '3');
INSERT INTO `dish_categories` VALUES ('4', '鲁菜', '1');
INSERT INTO `dish_categories` VALUES ('5', '苏菜', '2');
INSERT INTO `dish_categories` VALUES ('6', '浙菜', '3');-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',`user_id` int(11) NOT NULL COMMENT '用户ID',`restaurant_id` int(11) DEFAULT NULL COMMENT '餐厅ID',`order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单日期',`total_price` decimal(10,2) NOT NULL DEFAULT '1.00' COMMENT '订单总价',`status` enum('待支付','已支付','已取消','已完成') NOT NULL DEFAULT '待支付' COMMENT '订单状态',PRIMARY KEY (`id`) USING BTREE,KEY `user_id` (`user_id`) USING BTREE,KEY `restaurant_id` (`restaurant_id`) USING BTREE,CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单表';-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('2', '3', '2', '2024-05-26 00:00:00', '95.00', '待支付');
INSERT INTO `orders` VALUES ('3', '2', '1', '2024-05-27 00:00:00', '75.08', '已完成');
INSERT INTO `orders` VALUES ('4', '5', '3', '2024-06-27 13:34:14', '100.00', '已支付');
INSERT INTO `orders` VALUES ('6', '4', '4', '2024-06-25 08:35:20', '74.00', '已完成');
INSERT INTO `orders` VALUES ('8', '1', '1', '2024-06-27 21:50:29', '116.00', '待支付');
INSERT INTO `orders` VALUES ('9', '1', '1', '2024-06-27 21:50:50', '116.00', '待支付');
INSERT INTO `orders` VALUES ('10', '1', '1', '2024-06-28 00:30:15', '205.50', '待支付');
INSERT INTO `orders` VALUES ('11', '1', '1', '2024-06-28 00:32:01', '205.50', '待支付');
INSERT INTO `orders` VALUES ('12', '2', null, '2024-06-28 00:37:55', '76.00', '待支付');
INSERT INTO `orders` VALUES ('13', '2', null, '2024-06-28 00:38:30', '76.00', '待支付');
INSERT INTO `orders` VALUES ('14', '2', null, '2024-06-28 00:39:34', '76.00', '待支付');
INSERT INTO `orders` VALUES ('15', '2', null, '2024-06-28 00:39:47', '76.00', '待支付');
INSERT INTO `orders` VALUES ('16', '2', null, '2024-06-28 00:47:01', '76.00', '待支付');-- ----------------------------
-- Table structure for `order_items`
-- ----------------------------
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单项ID',`order_id` int(11) NOT NULL COMMENT '订单ID',`dish_id` int(11) NOT NULL COMMENT '菜品ID',`quantity` int(11) NOT NULL COMMENT '数量',`price_per_item` decimal(10,2) NOT NULL COMMENT '单价',PRIMARY KEY (`id`) USING BTREE,KEY `order_id` (`order_id`) USING BTREE,KEY `dish_id` (`dish_id`) USING BTREE,CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`dish_id`) REFERENCES `dishes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单项表';-- ----------------------------
-- Records of order_items
-- ----------------------------
INSERT INTO `order_items` VALUES ('1', '2', '2', '2', '25.00');
INSERT INTO `order_items` VALUES ('3', '3', '6', '1', '35.00');
INSERT INTO `order_items` VALUES ('5', '4', '3', '5', '55.00');
INSERT INTO `order_items` VALUES ('6', '6', '1', '6', '43.00');
INSERT INTO `order_items` VALUES ('8', '8', '1', '2', '58.00');
INSERT INTO `order_items` VALUES ('9', '9', '1', '2', '58.00');
INSERT INTO `order_items` VALUES ('13', '10', '1', '2', '102.75');
INSERT INTO `order_items` VALUES ('14', '11', '1', '2', '102.75');
INSERT INTO `order_items` VALUES ('15', '12', '3', '2', '38.00');
INSERT INTO `order_items` VALUES ('16', '13', '3', '2', '38.00');
INSERT INTO `order_items` VALUES ('17', '14', '3', '2', '38.00');
INSERT INTO `order_items` VALUES ('18', '15', '3', '2', '38.00');
INSERT INTO `order_items` VALUES ('19', '16', '3', '2', '38.00');-- ----------------------------
-- Table structure for `restaurants`
-- ----------------------------
DROP TABLE IF EXISTS `restaurants`;
CREATE TABLE `restaurants` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '餐厅ID',`name` varchar(100) NOT NULL COMMENT '餐厅名称',`address` varchar(255) NOT NULL COMMENT '餐厅地址',`opening_hours` varchar(50) DEFAULT NULL COMMENT '营业时间',`contact_number` varchar(20) DEFAULT NULL COMMENT '联系电话',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='餐厅表';-- ----------------------------
-- Records of restaurants
-- ----------------------------
INSERT INTO `restaurants` VALUES ('1', '江湖酒楼', '京城大街1号', '09:00-22:00', '12345678');
INSERT INTO `restaurants` VALUES ('2', '美味轩', '长安路88号', '10:00-21:30', '87654321');
INSERT INTO `restaurants` VALUES ('3', '清风阁', '西湖路123号', '11:00-23:00', '98765432');
INSERT INTO `restaurants` VALUES ('4', '美食街', '西梅路45号', '14:00-18:00', '22651231');
INSERT INTO `restaurants` VALUES ('5', '饺子馆', '建设街23号', '8:00-11:30', '25641584');
INSERT INTO `restaurants` VALUES ('6', '河间驴肉火烧', '教育路47号', '13:00-19:00', '27512975');-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',`username` varchar(50) NOT NULL COMMENT '用户名',`gender` enum('男','女') NOT NULL COMMENT '性别',`phone` varchar(20) DEFAULT NULL COMMENT '电话号码',`balance` varchar(255) DEFAULT '钱包余额',PRIMARY KEY (`id`) USING BTREE,UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', '赵一', '男', '154786482', '600');
INSERT INTO `users` VALUES ('2', '钱二', '男', '154961482', '650');
INSERT INTO `users` VALUES ('3', '张三', '女', '121876879', '450');
INSERT INTO `users` VALUES ('4', '李四', '男', '187597326', '470');
INSERT INTO `users` VALUES ('5', '王五', '女', '145745468', '550');-- ----------------------------
-- Procedure structure for `create_order_infos`
-- ----------------------------
DROP PROCEDURE IF EXISTS `create_order_infos`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_order_infos`(IN in_user_id INT,IN in_dish_id INT,IN in_quantity INT
)
BEGIN-- 注释：声明变量用于存储相关信息DECLARE dish_price DECIMAL(10, 2);DECLARE total_price DECIMAL(10, 2);DECLARE order_id INT;DECLARE restaurant_id INT;-- 注释：获取菜品价格SELECT price INTO dish_price FROM dishes WHERE id = in_dish_id;-- 注释：计算订单总价SET total_price = dish_price * in_quantity;-- 注释：获取菜品所属餐厅 IDSELECT restaurant_id INTO restaurant_id FROM dishes WHERE id = in_dish_id;-- 注释：插入订单信息到订单表INSERT INTO orders (user_id, restaurant_id, order_date, total_price, status)VALUES (in_user_id, restaurant_id, NOW(), total_price, '待支付');-- 注释：获取生成的订单 IDSET order_id = LAST_INSERT_ID();-- 注释：插入订单详情信息到订单详情表INSERT INTO order_items (order_id, dish_id, quantity, price_per_item)VALUES (order_id, in_dish_id, in_quantity, dish_price);-- 注释：更新商品表的库存数量UPDATE dishes SET stock_quantity = stock_quantity - in_quantity WHERE id = in_dish_id;END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `trg_check_dish_price_update`;
DELIMITER ;;
CREATE TRIGGER `trg_check_dish_price_update` BEFORE UPDATE ON `dishes` FOR EACH ROW BEGINDECLARE original_price DECIMAL(10,2);DECLARE new_price DECIMAL(10,2);DECLARE diff DECIMAL(10,2);SET original_price = OLD.price;SET new_price = NEW.price;-- 计算价格差是否超过10%SET diff = ABS(new_price - original_price) / original_price;IF diff > 0.1 THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = '商品售价上下浮动超过10%的限制！';END IF;
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `trg_delete_order_items_before_order`;
DELIMITER ;;
CREATE TRIGGER `trg_delete_order_items_before_order` BEFORE DELETE ON `orders` FOR EACH ROW BEGINDELETE FROM order_items WHERE order_id = OLD.id;
END
;;
DELIMITER ;

 查询

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

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

 

​​2、根据商品名称进行模糊查询，模糊查询需要可以走索引，需要给出explain语句。使用explain测试给出的查询语句，需要显示走了索引查询。 

explain select * from dishes where restaurant_id like 'e%';

 

3、统计用户订单信息，查询所有用户的下单数量，并进行倒序排列。使用聚合函数查询处所有用户的订单数量，倒序排列结果。 

SELECT user_id,sum(restaurant_id) as '订单数量'
from `orders`
GROUP BY user_id
ORDER BY `订单数量` DESC;

 

1、查询用户的基本信息，钱包信息。正确显示用户信息，正确显示用户钱包信息，正确进行多表联合查询. 

select user_id,username,phone,order_date,total_price
FROMusers 
JOIN orders on user_id = user_id;

 

 2、查看订单中下单最多的产品对应的类别。正确使用聚合函数，正确使用子查询，正确显示结果.

SELECT `name`
from (
SELECT `name`,count(*) as id 
from dishes
GROUP BY `name`
)  as subquery
ORDER BY id DESC
LIMIT 1;

 

3、查询下单总金额最多的用户，并查询用户的全部信息与当前钱包余额。正确使用聚合函数，正确使用子查询，正确进行多表联合查询. 

 

SELECT u.*, balance
FROM users u
JOIN (SELECT o.user_id, SUM(o.total_price) AS total_spentFROM orders oGROUP BY o.user_idORDER BY total_spent DESCLIMIT 1) AS top_spender
ON u.id = top_spender.user_id
LEFT JOIN orders w ON u.id = w.user_id;

触发器和存储过程

1.触发器

DELIMITER $$
CREATE TRIGGER trg_check_price_change
BEFORE UPDATE ON dishes
FOR EACH ROW
BEGINDECLARE old_price DECIMAL(10,2);DECLARE new_price DECIMAL(10,2);DECLARE price_change DECIMAL(10,2);-- 获取新旧价格SET old_price = OLD.price;SET new_price = NEW.price;-- 计算价格变化百分比IF (new_price <> 0) THENSET price_change = (new_price - old_price) / old_price * 100;ELSE-- 如果新价格为0，则不允许更新SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '新价格不能为0。';END IF;-- 检查价格变动是否超过10%IF (price_change > 10 OR price_change < -10) THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '商品售价上下浮动不能超过10%。';END IF;
END;
$$
DELIMITER ;
DELIMITER //
CREATE TRIGGER trg_cascade_delete_order_items
AFTER DELETE ON orders
FOR EACH ROW
BEGINDELETE FROM order_items WHERE order_id = OLD.id;
END;
//
DELIMITER ;DELETE FROM `order` WHERE `order_id` = 刚才添加的id号;-- 插入测试数据：
insert into dishes(`name`,price,stock_quantity) VALUES ( '大鱼头',55.00,100);
-- 正常更新（未超10%）
UPDATE dishes set price =8.00 WHERE `name`='大鱼头';  -- 触发错误

 

2.存储过程

DELIMITER //CREATE PROCEDURE create_order_infos(IN in_user_id INT,IN in_dish_id INT,IN in_quantity INT
)
BEGIN-- 注释：声明变量用于存储相关信息DECLARE dish_price DECIMAL(10, 2);DECLARE total_price DECIMAL(10, 2);DECLARE order_id INT;DECLARE restaurant_id INT;-- 注释：获取菜品价格SELECT price INTO dish_price FROM dishes WHERE id = in_dish_id;-- 注释：计算订单总价SET total_price = dish_price * in_quantity;-- 注释：获取菜品所属餐厅 IDSELECT restaurant_id INTO restaurant_id FROM dishes WHERE id = in_dish_id;-- 注释：插入订单信息到订单表INSERT INTO orders (user_id, restaurant_id, order_date, total_price, status)VALUES (in_user_id, restaurant_id, NOW(), total_price, '待支付');-- 注释：获取生成的订单 IDSET order_id = LAST_INSERT_ID();-- 注释：插入订单详情信息到订单详情表INSERT INTO order_items (order_id, dish_id, quantity, price_per_item)VALUES (order_id, in_dish_id, in_quantity, dish_price);-- 注释：更新商品表的库存数量UPDATE dishes SET stock_quantity = stock_quantity - in_quantity WHERE id = in_dish_id;END //DELIMITER ;
CALL create_order_infos(2, 3, 2);

 

总结：

餐厅点餐系统的MySQL数据库数据管理与维护至关重要。这包括定期备份数据库，确保数据安全性；优化查询性能，提高系统响应速度；监控数据库运行状态，及时发现并解决潜在问题。同时，需要定期清理过期数据，保持数据库的整洁和高效。此外，还要定期更新和维护数据库表结构，以适应业务发展和变化。通过有效的数据管理与维护，可以确保餐厅点餐系统的稳定运行和数据的准确性。