网站图片布局,wordpress手机显示侧边栏,wordpress用什么服务器系统,美妆销售网站开发的目的项目简介#xff1a;餐饮点餐系统是一款为餐厅和顾客提供便捷点餐服务的在线平台。通过该系统#xff0c;餐厅能够展示其菜单#xff0c;顾客可以浏览菜品#xff0c;并将其加入购物车或直接下单。系统还提供了订单管理功能#xff0c;方便餐厅跟踪和处理顾客的订单。 1. … 项目简介餐饮点餐系统是一款为餐厅和顾客提供便捷点餐服务的在线平台。通过该系统餐厅能够展示其菜单顾客可以浏览菜品并将其加入购物车或直接下单。系统还提供了订单管理功能方便餐厅跟踪和处理顾客的订单。 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)
) ENGINEMyISAM AUTO_INCREMENT19 DEFAULT CHARSETutf8 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 数量,PRIMARY KEY (id),KEY cart_id (cart_id),KEY dish_id (dish_id)
) ENGINEMyISAM AUTO_INCREMENT17 DEFAULT CHARSETutf8 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)
) ENGINEMyISAM AUTO_INCREMENT18 DEFAULT CHARSETutf8 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)
) ENGINEMyISAM AUTO_INCREMENT10 DEFAULT CHARSETutf8 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 订单状态,PRIMARY KEY (id),KEY user_id (user_id),KEY restaurant_id (restaurant_id)
) ENGINEMyISAM AUTO_INCREMENT43 DEFAULT CHARSETutf8 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 数量,price_per_item decimal(10,2) NOT NULL COMMENT 单价,PRIMARY KEY (id),KEY order_id (order_id),KEY dish_id (dish_id)
) ENGINEMyISAM AUTO_INCREMENT10 DEFAULT CHARSETutf8 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 营业时间,contact_number varchar(20) DEFAULT NULL COMMENT 联系电话,PRIMARY KEY (id)
) ENGINEMyISAM AUTO_INCREMENT14 DEFAULT CHARSETutf8 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)
) ENGINEMyISAM AUTO_INCREMENT23 DEFAULT CHARSETutf8 COMMENT用户表;
2. 插入数据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, 清蒸鲈鱼, 鲜嫩可口营养丰富, 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, 300-300, 32809000);
INSERT INTO restaurants VALUES (5, 百香馆, 长安街1号, 800-2330, 87892747);
INSERT INTO restaurants VALUES (6, 味里香, 信安大厦770号, 900-2300, 37182173);
INSERT INTO restaurants VALUES (7, 鲁香楼, 城南花苑824号, 1000-1000, 73731389);
INSERT INTO restaurants VALUES (8, 西湖谷味, 西湖路188号, 1200-300, 17362172);
INSERT INTO restaurants VALUES (9, 木叶谷, 长江大道990号, 1300-2300, 72838381);
INSERT INTO restaurants VALUES (10, 烟火味道, 梧桐大道779号, 700-1700, 82173261);
INSERT INTO restaurants VALUES (11, 川上人家, 川南小街990号, 2400, 89232781);
INSERT INTO restaurants VALUES (12, 雅寻名苑, 寻南路889号, 1700-400, 72182373);
INSERT INTO restaurants VALUES (13, 一品鱼乡, 江南小镇770号, 800-2330, 81276232);INSERT INTO users VALUES (1, 小鱼儿, xiaoyu_pass, xiaoyuexample.com, 男, 15273663822);
INSERT INTO users VALUES (2, 花无缺, huawu_pass, huawuexample.com, 男, 17532698837);
INSERT INTO users VALUES (3, 苏樱, suying_pass, suyingexample.com, 女, 16728366253);
INSERT INTO users VALUES (4, 散兵, sanbing, sanbingexample.com, 男, 15738826639);
INSERT INTO users VALUES (5, 管容祖, rongzu_pass, rongzuexample.com, 女, 15632782993);
INSERT INTO users VALUES (6, 荣祖管, zuguan_pass, zurongexample.com, 男, 17644552789);
INSERT INTO users VALUES (7, 张三, zhangsan_pass, zhangsanexample.com, 男, 17237368137);
INSERT INTO users VALUES (8, 李三, lisi_pass, lisiexample.com, 男, 16273828372);
INSERT INTO users VALUES (9, 李四, lisi_pass, lisiexamole.com, 女, 17261537183);
INSERT INTO users VALUES (10, 刘五, liuwu_Pass, liuwuexample.com, 男, 15624416819);
INSERT INTO users VALUES (11, 张先生, zhangxiansheng_pass, zhangxianshengexample.com, 男, 18862715218);
INSERT INTO users VALUES (12, 刘先生, liu_pass, liuxianshengexample, 男, 19173762517);
INSERT INTO users VALUES (13, 甄嬛, zhenhuan_pass, zhenhaunexamaple, 女, 18374625174);
INSERT INTO users VALUES (14, 四郎, silang_pass, silangexample, 男, 18937253518);
INSERT INTO users VALUES (15, 沈梅庄, shen_pass, meizhuangexample, 女, 19347264628);
INSERT INTO users VALUES (16, 华妃, hua_pass, huafeiexample, 女, 18427461731);
INSERT INTO users VALUES (17, 皇后, huanghou_pass, huanghouashdascm.com, 女, 17637512653);
INSERT INTO users VALUES (18, 浣碧, huanbi_pass, huanbiexample.com, 女, 15276837683);
INSERT INTO users VALUES (19, 太后, taihou_pass, taihouexample.com, 女, 18631763837);
INSERT INTO users VALUES (20, 齐妃, qifei_pass, qifeiexample.com, 女, 17649279127);
INSERT INTO users VALUES (21, 祺贵人, qiguiren_pass, qiguirenexample.com, 女, 13842947274);
INSERT INTO users VALUES (22, 叶澜依, yelanyi_pass, lanyiexample.com, 女, 14723727382);
3. 简单查询
# 简单查询# 1. 查询所有菜品名称和价格
SELECT name, price FROM dishes;
# 2. 查询所有餐厅的名称和地址
SELECT name, address FROM restaurants;
# 3. 查询所有用户的用户名和性别
SELECT username, gender FROM users;效果 4. 多表联合复杂查询
#多表联合复杂查询# 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. 查询每个餐厅的菜品数量
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.查询每个用户在购物车中添加的菜品总数量
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. 触发器 插入-修改-删除
create trigger after_inster_user -- 创建触发器的名称
after insert on users -- 用户表执行插入操作之后被出发的触发器
for each row -- 每行数据都要进行触发
beginINSERT INTO orders (user_id, total_price) -- 在orders表中插入一行数据VALUES (NEW.id, 0.00); -- 使用NEW.id作为新插入行的user_idtotal_price为0.00
end ;
$$
delimiter ; -- 结束$$的有效性更换成;insert into users values (0,散兵,sanbing,sanbing,男,99999999999)-- 删除触发器
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 ;-- 更新触发器
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. 创建存储过程
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;-- 创建新订单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 ); 本项目实现了餐饮点餐系统的基本功能包括菜品浏览、购物车管理、订单处理等达到了预期目标。在项目中我们遇到了数据库优化、并发控制等技术难题通过查阅资料和团队讨论成功找到解决方案。未来我们计划对餐饮点餐系统进行优化升级增加用户评价功能提高用户体验同时考虑移动端的适配问题。
