? ? ? ?項目簡介：餐飲點餐系統(tǒng)是一款為餐廳和顧客提供便捷點餐服務的在線平臺。通過該系統(tǒng)，餐廳能夠展示其菜單，顧客可以瀏覽菜品，并將其加入購物車或直接下單。系統(tǒng)還提供了訂單管理功能，方便餐廳跟蹤和處理顧客的訂單。

1. 建表DDL

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`),KEY `user_id` (`user_id`),KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COMMENT='購物車表';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 '數(shù)量',PRIMARY KEY (`id`),KEY `cart_id` (`cart_id`),KEY `dish_id` (`dish_id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='購物車項表';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 COMMENT '所屬分類ID',`restaurant_id` int(11) NOT NULL COMMENT '所屬餐廳ID',PRIMARY KEY (`id`),KEY `category_id` (`category_id`),KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='菜品表';
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`),KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='菜品分類表';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) DEFAULT '0.00' COMMENT '訂單總價',`status` enum('待支付','已支付','已取消','已完成') DEFAULT '待支付' COMMENT '訂單狀態(tài)',PRIMARY KEY (`id`),KEY `user_id` (`user_id`),KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COMMENT='訂單表';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 '數(shù)量',`price_per_item` decimal(10,2) NOT NULL COMMENT '單價',PRIMARY KEY (`id`),KEY `order_id` (`order_id`),KEY `dish_id` (`dish_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='訂單項表';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 '營業(yè)時間',`contact_number` varchar(20) DEFAULT NULL COMMENT '聯(lián)系電話',PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='餐廳表';CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶ID',`username` varchar(50) NOT NULL COMMENT '用戶名',`password` varchar(255) NOT NULL COMMENT '密碼',`email` varchar(100) DEFAULT NULL COMMENT '郵箱地址',`gender` enum('男','女') NOT NULL COMMENT '性別',`phone` varchar(20) DEFAULT NULL COMMENT '電話號碼',PRIMARY KEY (`id`),UNIQUE KEY `username` (`username`),UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COMMENT='用戶表';

2. 插入數(shù)據(jù)（DML）

INSERT INTO `carts` VALUES ('1', '1', '1');
INSERT INTO `carts` VALUES ('2', '1', '2');
INSERT INTO `carts` VALUES ('3', '2', '4');
INSERT INTO `carts` VALUES ('4', '3', '3');
INSERT INTO `carts` VALUES ('5', '3', '1');
INSERT INTO `carts` VALUES ('6', '4', '1');
INSERT INTO `carts` VALUES ('7', '8', '2');
INSERT INTO `carts` VALUES ('8', '5', '1');
INSERT INTO `carts` VALUES ('9', '3', '1');
INSERT INTO `carts` VALUES ('10', '1', '1');
INSERT INTO `carts` VALUES ('11', '9', '1');
INSERT INTO `carts` VALUES ('12', '10', '8');
INSERT INTO `carts` VALUES ('13', '8', '6');
INSERT INTO `carts` VALUES ('14', '16', '4');
INSERT INTO `carts` VALUES ('15', '4', '6');
INSERT INTO `carts` VALUES ('16', '11', '5');
INSERT INTO `carts` VALUES ('17', '12', '8');
INSERT INTO `carts` VALUES ('18', '9', '1');INSERT INTO `cart_items` VALUES ('1', '1', '1', '2');
INSERT INTO `cart_items` VALUES ('2', '1', '2', '1');
INSERT INTO `cart_items` VALUES ('3', '2', '4', '1');
INSERT INTO `cart_items` VALUES ('4', '2', '5', '3');
INSERT INTO `cart_items` VALUES ('5', '2', '1', '7');
INSERT INTO `cart_items` VALUES ('6', '1', '7', '2');
INSERT INTO `cart_items` VALUES ('7', '3', '3', '1');
INSERT INTO `cart_items` VALUES ('8', '5', '6', '1');
INSERT INTO `cart_items` VALUES ('9', '6', '2', '7');
INSERT INTO `cart_items` VALUES ('10', '6', '2', '6');
INSERT INTO `cart_items` VALUES ('11', '4', '7', '3');
INSERT INTO `cart_items` VALUES ('12', '8', '1', '3');
INSERT INTO `cart_items` VALUES ('13', '5', '3', '9');
INSERT INTO `cart_items` VALUES ('14', '9', '3', '1');
INSERT INTO `cart_items` VALUES ('15', '8', '1', '0');
INSERT INTO `cart_items` VALUES ('16', '2', '4', '6');INSERT INTO `dishes` VALUES ('1', '水煮魚', '麻辣鮮香，回味無窮', '58.00', '1', '1');
INSERT INTO `dishes` VALUES ('2', '宮保雞丁', '色澤紅亮，口感鮮美', '48.00', '1', '1');
INSERT INTO `dishes` VALUES ('3', '麻婆豆腐', '麻辣可口，下飯佳品', '38.00', '1', '1');
INSERT INTO `dishes` VALUES ('4', '白切雞', '皮爽肉滑，鮮美無比', '68.00', '2', '2');
INSERT INTO `dishes` VALUES ('5', '清蒸鱸魚', '鮮嫩可口，營養(yǎng)豐富', '78.00', '2', '2');
INSERT INTO `dishes` VALUES ('6', '菠蘿咕嚕肉', '酸甜可口，色澤誘人', '52.00', '2', '2');
INSERT INTO `dishes` VALUES ('7', '剁椒魚頭', '香辣可口，回味無窮', '62.00', '3', '3');
INSERT INTO `dishes` VALUES ('8', '辣椒炒肉', '香辣可口，下飯佳品', '42.00', '3', '3');
INSERT INTO `dishes` VALUES ('9', '紅燒肉', '肥而不膩，入口即化', '55.00', '3', '3');
INSERT INTO `dishes` VALUES ('10', '扣碗小酥肉', '口感鮮嫩，色澤誘人', '56.00', '6', '4');
INSERT INTO `dishes` VALUES ('11', '開水白菜', '香味醇厚，湯味濃厚', '199.00', '4', '3');
INSERT INTO `dishes` VALUES ('12', '夫妻肺片', '色澤美觀，質嫩鮮美', '89.00', '5', '9');
INSERT INTO `dishes` VALUES ('13', '雞豆花', '吃雞不見雞，吃肉不見肉', '88.00', '3', '7');
INSERT INTO `dishes` VALUES ('14', '九轉大腸', '色澤紅潤，質地軟嫩', '199.00', '7', '4');
INSERT INTO `dishes` VALUES ('15', '爆炒腰花', '鮮嫩，潤滑不膩', '189.00', '6', '4');
INSERT INTO `dishes` VALUES ('16', '白切雞', '形狀美觀，原汁原味', '189.00', '2', '2');
INSERT INTO `dishes` VALUES ('17', '紅燒乳鴿', '皮脆，肉滑，骨嫩，多汁', '88.00', '1', '1');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');
INSERT INTO `dish_categories` VALUES ('7', '閩菜', '5');
INSERT INTO `dish_categories` VALUES ('8', '徽菜', '8');
INSERT INTO `dish_categories` VALUES ('9', '湘菜', '7');INSERT INTO `orders` VALUES ('1', '1', '1', '2024-06-27 13:59:06', '99.00', '待支付');
INSERT INTO `orders` VALUES ('5', '6', '4', '2024-06-27 17:47:18', '328.00', '待支付');
INSERT INTO `orders` VALUES ('4', '5', '2', '2024-06-27 17:44:19', '1100.00', '待支付');
INSERT INTO `orders` VALUES ('6', '7', '5', '2024-06-28 10:05:38', '43.00', '待支付');
INSERT INTO `orders` VALUES ('7', '8', '1', '2024-06-28 10:12:14', '159.00', '已支付');
INSERT INTO `orders` VALUES ('8', '9', '8', '2024-06-28 10:13:58', '190.00', '待支付');
INSERT INTO `orders` VALUES ('9', '10', '1', '2024-06-28 10:14:51', '126.00', '待支付');
INSERT INTO `orders` VALUES ('10', '11', '3', '2024-06-28 10:15:50', '112.00', '待支付');
INSERT INTO `orders` VALUES ('11', '12', '6', '2024-06-28 10:16:46', '442.00', '待支付');
INSERT INTO `orders` VALUES ('12', '13', '2', '2024-06-28 10:17:45', '45.00', '待支付');
INSERT INTO `orders` VALUES ('13', '14', '3', '2024-06-28 10:19:02', '123.00', '已支付');
INSERT INTO `orders` VALUES ('14', '15', '4', '2024-06-28 10:19:46', '232.00', '已支付');
INSERT INTO `orders` VALUES ('15', '16', '7', '2024-06-28 10:20:43', '565.00', '待支付');
INSERT INTO `orders` VALUES ('16', '17', '2', '2024-06-28 10:21:23', '123.00', '已支付');
INSERT INTO `orders` VALUES ('17', '18', '3', '2024-06-28 10:22:20', '563.00', '待支付');
INSERT INTO `orders` VALUES ('18', '19', '1', '2024-06-28 10:23:36', '213.00', '已取消');
INSERT INTO `orders` VALUES ('19', '20', '2', '2024-06-28 10:24:41', '123.00', '待支付');
INSERT INTO `orders` VALUES ('20', '21', '4', '2024-06-28 10:25:05', '13.00', '待支付');
INSERT INTO `orders` VALUES ('21', '22', '7', '2024-06-28 10:26:09', '123.00', '待支付');
INSERT INTO `orders` VALUES ('22', '23', '4', '2024-06-28 10:30:40', '112.00', '待支付');
INSERT INTO `orders` VALUES ('23', '11', '8', '2024-06-28 10:30:42', '762.00', '待支付');
INSERT INTO `orders` VALUES ('24', '12', '5', '2024-06-28 10:30:54', '199.00', '待支付');
INSERT INTO `orders` VALUES ('25', '5', '6', '2024-06-28 10:30:55', '192.00', '待支付');
INSERT INTO `orders` VALUES ('26', '2', '4', '2024-06-28 10:30:56', '172.00', '待支付');
INSERT INTO `orders` VALUES ('27', '1', '2', '2024-06-28 10:30:57', '234.00', '待支付');
INSERT INTO `orders` VALUES ('28', '7', '5', '2024-06-28 10:30:58', '423.00', '待支付');
INSERT INTO `orders` VALUES ('29', '13', '10', '2024-06-28 10:31:00', '2312.00', '待支付');
INSERT INTO `orders` VALUES ('30', '14', '3', '2024-06-28 10:31:01', '123.00', '已取消');
INSERT INTO `orders` VALUES ('31', '4', '3', '2024-06-28 10:31:03', '123.00', '待支付');
INSERT INTO `orders` VALUES ('32', '6', '6', '2024-06-28 10:31:04', '1534.00', '待支付');
INSERT INTO `orders` VALUES ('33', '8', '17', '2024-06-28 10:31:06', '2314.00', '待支付');
INSERT INTO `orders` VALUES ('34', '3', '2', '2024-06-28 10:31:07', '213.00', '待支付');
INSERT INTO `orders` VALUES ('35', '7', '7', '2024-06-28 10:31:09', '872.00', '已取消');
INSERT INTO `orders` VALUES ('36', '9', '1', '2024-06-28 10:31:10', '69.00', '待支付');
INSERT INTO `orders` VALUES ('37', '10', '3', '2024-06-28 10:31:11', '199.00', '待支付');
INSERT INTO `orders` VALUES ('38', '13', '1', '2024-06-28 10:31:12', '32.00', '已支付');
INSERT INTO `orders` VALUES ('39', '11', '4', '2024-06-28 10:31:13', '234.00', '待支付');
INSERT INTO `orders` VALUES ('40', '14', '1', '2024-06-28 10:31:15', '44.00', '已取消');
INSERT INTO `orders` VALUES ('41', '16', '1', '2024-06-28 10:31:16', '32.00', '待支付');
INSERT INTO `orders` VALUES ('42', '13', '1', '2024-06-28 10:51:13', '34.00', '待支付');INSERT INTO `order_items` VALUES ('1', '1', '1', '2', '58.00');
INSERT INTO `order_items` VALUES ('2', '1', '2', '1', '48.00');
INSERT INTO `order_items` VALUES ('3', '2', '2', '1', '99.00');
INSERT INTO `order_items` VALUES ('4', '4', '2', '4', '199.00');
INSERT INTO `order_items` VALUES ('5', '3', '6', '2', '169.00');
INSERT INTO `order_items` VALUES ('6', '6', '1', '5', '1100.00');
INSERT INTO `order_items` VALUES ('7', '5', '3', '1', '99.00');
INSERT INTO `order_items` VALUES ('8', '2', '1', '6', '499.00');
INSERT INTO `order_items` VALUES ('9', '3', '1', '5', '1099.00');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', '湘味閣', '南京路338', '3：00-3：00', '32809000');
INSERT INTO `restaurants` VALUES ('5', '百香館', '長安街1號', '8：00-23：30', '87892747');
INSERT INTO `restaurants` VALUES ('6', '味里香', '信安大廈770號', '9：00-23：00', '37182173');
INSERT INTO `restaurants` VALUES ('7', '魯香樓', '城南花苑824號', '10：00-10：00', '73731389');
INSERT INTO `restaurants` VALUES ('8', '西湖谷味', '西湖路188號', '12：00-3：00', '17362172');
INSERT INTO `restaurants` VALUES ('9', '木葉谷', '長江大道990號', '13：00-23：00', '72838381');
INSERT INTO `restaurants` VALUES ('10', '煙火味道', '梧桐大道779號', '7：00-17：00', '82173261');
INSERT INTO `restaurants` VALUES ('11', '川上人家', '川南小街990號', '24：00', '89232781');
INSERT INTO `restaurants` VALUES ('12', '雅尋名苑', '尋南路889號', '17：00-4：00', '72182373');
INSERT INTO `restaurants` VALUES ('13', '一品魚鄉(xiāng)', '江南小鎮(zhèn)770號', '8：00-23：30', '81276232');INSERT INTO `users` VALUES ('1', '小魚兒', 'xiaoyu_pass', 'xiaoyu@example.com', '男', '15273663822');
INSERT INTO `users` VALUES ('2', '花無缺', 'huawu_pass', 'huawu@example.com', '男', '17532698837');
INSERT INTO `users` VALUES ('3', '蘇櫻', 'suying_pass', 'suying@example.com', '女', '16728366253');
INSERT INTO `users` VALUES ('4', '散兵', 'sanbing', 'sanbing@example.com', '男', '15738826639');
INSERT INTO `users` VALUES ('5', '管容祖', 'rongzu_pass', 'rongzu@example.com', '女', '15632782993');
INSERT INTO `users` VALUES ('6', '榮祖管', 'zuguan_pass', 'zurong@example.com', '男', '17644552789');
INSERT INTO `users` VALUES ('7', '張三', 'zhangsan_pass', 'zhangsan@example.com', '男', '17237368137');
INSERT INTO `users` VALUES ('8', '李三', 'lisi_pass', 'lisi@example.com', '男', '16273828372');
INSERT INTO `users` VALUES ('9', '李四', 'lisi_pass', 'lisi@examole.com', '女', '17261537183');
INSERT INTO `users` VALUES ('10', '劉五', 'liuwu_Pass', 'liuwu@example.com', '男', '15624416819');
INSERT INTO `users` VALUES ('11', '張先生', 'zhangxiansheng_pass', 'zhangxiansheng@example.com', '男', '18862715218');
INSERT INTO `users` VALUES ('12', '劉先生', 'liu_pass', 'liuxiansheng@example', '男', '19173762517');
INSERT INTO `users` VALUES ('13', '甄嬛', 'zhenhuan_pass', 'zhenhaun@examaple', '女', '18374625174');
INSERT INTO `users` VALUES ('14', '四郎', 'silang_pass', 'silang@example', '男', '18937253518');
INSERT INTO `users` VALUES ('15', '沈梅莊', 'shen_pass', 'meizhuang@example', '女', '19347264628');
INSERT INTO `users` VALUES ('16', '華妃', 'hua_pass', 'huafei@example', '女', '18427461731');
INSERT INTO `users` VALUES ('17', '皇后', 'huanghou_pass', 'huanghou@ashdascm.com', '女', '17637512653');
INSERT INTO `users` VALUES ('18', '浣碧', 'huanbi_pass', 'huanbi@example.com', '女', '15276837683');
INSERT INTO `users` VALUES ('19', '太后', 'taihou_pass', 'taihou@example.com', '女', '18631763837');
INSERT INTO `users` VALUES ('20', '齊妃', 'qifei_pass', 'qifei@example.com', '女', '17649279127');
INSERT INTO `users` VALUES ('21', '祺貴人', 'qiguiren_pass', 'qiguiren@example.com', '女', '13842947274');
INSERT INTO `users` VALUES ('22', '葉瀾依', 'yelanyi_pass', 'lanyi@example.com', '女', '14723727382');

3. 簡單查詢

# 簡單查詢# 1. 查詢所有菜品名稱和價格：
SELECT name, price FROM dishes;
# 2. 查詢所有餐廳的名稱和地址：
SELECT name, address FROM restaurants;
# 3. 查詢所有用戶的用戶名和性別：
SELECT username, gender FROM users;

效果：

?

?

4. 多表聯(lián)合復雜查詢?

#多表聯(lián)合復雜查詢# 1. 查詢用戶名為“小魚兒”的用戶的購物車中所有菜品的信息：
SELECT dishes.name, dishes.price, cart_items.quantity
FROM users
JOIN carts ON users.id = carts.user_id
JOIN cart_items ON carts.id = cart_items.cart_id
JOIN dishes ON cart_items.dish_id = dishes.id
WHERE users.username = '小魚兒';
# 2. 查詢所有訂單中包含“水煮魚”的訂單信息，包括訂單號、用戶名、訂單日期和訂單總價：
SELECT orders.id, users.username, orders.order_date, orders.total_price
FROM orders
JOIN order_items ON orders.id = order_items.order_id
JOIN users ON orders.user_id = users.id
JOIN dishes ON order_items.dish_id = dishes.id
WHERE dishes.name = '水煮魚';
# 3. 查詢每個餐廳的菜品數(shù)量：
SELECT restaurants.name, COUNT(dishes.id) as dish_count
FROM restaurants
LEFT JOIN dishes ON restaurants.id = dishes.restaurant_id
GROUP BY restaurants.id;
# 4.查詢每個用戶在購物車中添加的菜品總數(shù)量：
SELECT users.username, SUM(cart_items.quantity) as total_quantity
FROM users
JOIN carts ON users.id = carts.user_id
JOIN cart_items ON carts.id = cart_items.cart_id
GROUP BY users.id;
# 5. 查詢每個餐廳的訂單總金額：
SELECT restaurants.name, SUM(orders.total_price) as total_revenue
FROM orders
JOIN restaurants ON orders.restaurant_id = restaurants.id
GROUP BY orders.restaurant_id;

效果：

?

?

?

?

5. 觸發(fā)器 插入-修改-刪除?

create trigger after_inster_user -- 創(chuàng)建觸發(fā)器的名稱
after insert on `users` -- 用戶表執(zhí)行插入操作之后被出發(fā)的觸發(fā)器
for each row -- 每行數(shù)據(jù)都要進行觸發(fā)
beginINSERT INTO orders (user_id, total_price)  -- 在orders表中插入一行數(shù)據(jù)VALUES (NEW.id, 0.00);  -- 使用NEW.id作為新插入行的user_id，total_price為0.00
end ;
$$
delimiter ; -- 結束$$的有效性更換成;insert into users values (0,'散兵','sanbing','sanbing','男','99999999999')-- 刪除觸發(fā)器
DROP TRIGGER IF EXISTS `after_delete_user`;
DELIMITER ;;
CREATE TRIGGER `after_delete_user` AFTER DELETE ON `users` FOR EACH ROW
BEGIN-- 刪除與該用戶相關的訂單DELETE FROM `orders` WHERE `user_id` = OLD.id;
END
;;
DELIMITER ;-- 更新觸發(fā)器
DROP TRIGGER IF EXISTS `after_update_user`;
DELIMITER ;;
CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW
BEGIN-- 更新與該用戶相關的訂單中的某些信息（例如，更新訂單中的用戶名）UPDATE `orders` SET `user_id` = NEW.id WHERE `user_id` = OLD.id;
END
;;
DELIMITER ;

6. 創(chuàng)建存儲過程

DELIMITER //CREATE PROCEDURE CreateOrder(IN p_user_id INT,IN p_restaurant_id INT,IN p_dish_id INT,IN p_quantity INT
)
BEGINDECLARE v_order_id INT;DECLARE v_price_per_item DECIMAL(10,2);-- 獲取菜品價格SELECT price INTO v_price_per_item FROM dishes WHERE id = p_dish_id;-- 創(chuàng)建新訂單INSERT INTO orders (id,user_id, restaurant_id,order_date, total_price,status)VALUES (22,6,1,'2024-06-27 17:47:18,880','已支付');-- 獲取新插入訂單的IDSET v_order_id = LAST_INSERT_ID();-- 插入訂單項INSERT INTO order_items (order_id, dish_id, quantity, price_per_item)VALUES (v_order_id, p_dish_id, p_quantity, v_price_per_item);-- 更新訂單總價UPDATE ordersSET total_price = (SELECT SUM(price_per_item * quantity) FROM order_items WHERE order_id = v_order_id)WHERE id = v_order_id;
END //DELIMITER ;-- CALL CreateOrder(1, 1,1 ,99 );

? ? ? ?本項目實現(xiàn)了餐飲點餐系統(tǒng)的基本功能，包括菜品瀏覽、購物車管理、訂單處理等，達到了預期目標。在項目中我們遇到了數(shù)據(jù)庫優(yōu)化、并發(fā)控制等技術難題，通過查閱資料和團隊討論，成功找到解決方案。未來我們計劃對餐飲點餐系統(tǒng)進行優(yōu)化升級，增加用戶評價功能，提高用戶體驗，同時考慮移動端的適配問題。