综合应用 · Java业余班讲义

# 综合应用 > 结合之前所学知识，运用 SQL 语言完成一个实例应用的数据操作，重点是了解在应用程序中，如何构建数据查询语句。 > 任何基于数据库的应用程序类型，包括 B/S 架构、 C/S 架构、APP 应用，其实归根结底都是用不同的界面或者形式完成对数据库中数据的操作，所以，了解数据中应用的处理过程很重要。一个典型的应用程序，包含的功能有： * 基础数据维护：一般通过初始化完成部分数据，后续再次维护； * 业务流程处理：一般通过具体的功能模块完成； * 数据分析和报表：通过相关复杂的查询完成，使用视图，或者缓存统计报表。下面，我们从上一章节中的订餐系统进行延伸，模拟系统的数据操作过程等。 ## 表结构及数据 ~~~sql /* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80018 Source Host : localhost:3306 Source Schema : order Target Server Type : MySQL Target Server Version : 80018 File Encoding : 65001 Date: 15/11/2019 20:45:28 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_employee -- ---------------------------- DROP TABLE IF EXISTS `t_employee`; CREATE TABLE `t_employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` int(1) NULL DEFAULT NULL, `id_card` varchar(23) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(2) NULL DEFAULT NULL, `come_time` date NULL DEFAULT NULL, `post_id` int(11) NULL DEFAULT NULL, `telephone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `address` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `restaurant_id` int(11) NULL DEFAULT NULL, `work_time` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `education` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `is_marry` int(1) NULL DEFAULT NULL COMMENT '0未婚，1已婚', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_employee -- ---------------------------- INSERT INTO `t_employee` VALUES (1, '赵柳', 1, '3206231991067128212', 30, '2017-01-13', 1, '12345678910', '南通', 1, 'AM9:00-PM10:30', '本科', 1); INSERT INTO `t_employee` VALUES (2, '勘误', 0, '3206231991067128212', 30, '2017-01-13', 2, '12345678910', '南通', 1, 'AM9:00-PM10:30', '高中', 1); INSERT INTO `t_employee` VALUES (3, '看晚会', 0, '3206231991067128212', 30, '2017-01-13', 2, '12345678910', '南通', 1, 'AM9:00-PM10:30', '高中', 1); INSERT INTO `t_employee` VALUES (4, '吴昕', 1, '3206231991067128212', 30, '2017-01-13', 3, '12345678910', '南通', 1, 'AM9:00-PM9:30', '大专', 1); -- ---------------------------- -- Table structure for t_kitchen -- ---------------------------- DROP TABLE IF EXISTS `t_kitchen`; CREATE TABLE `t_kitchen` ( `id` int(11) NOT NULL AUTO_INCREMENT, `restaurant_id` int(11) NULL DEFAULT NULL, `employee_id` int(11) NULL DEFAULT NULL, `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `marchine_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_kitchen -- ---------------------------- INSERT INTO `t_kitchen` VALUES (1, 1, 4, '红案', 'wuhbd287bnsdh8', '汤菜'); INSERT INTO `t_kitchen` VALUES (2, 1, 4, '白案', 'weub27846bdf7', '中式面点'); INSERT INTO `t_kitchen` VALUES (3, 1, 4, '红案', 'weub2347884b734b', '炒菜'); -- ---------------------------- -- Table structure for t_member -- ---------------------------- DROP TABLE IF EXISTS `t_member`; CREATE TABLE `t_member` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员名称', `sex` int(11) NULL DEFAULT NULL COMMENT '性别（0为女，1为男）', `birthday` date NULL DEFAULT NULL COMMENT '会员生日', `telephone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机', `aviod_food` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '忌口', `taste_like` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '口味喜好', `isCar` int(11) NULL DEFAULT NULL COMMENT '是否有车（0为无，1为有）', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_member -- ---------------------------- INSERT INTO `t_member` VALUES (1, '张三', 1, '1993-02-11', '14672398712', '酸', '甜', 1); INSERT INTO `t_member` VALUES (2, '李四', 1, '1993-02-11', '14672392112', '苦', '甜', 1); INSERT INTO `t_member` VALUES (3, '王五', 1, '1993-02-11', '14672392112', '苦', '甜', 1); -- ---------------------------- -- Table structure for t_member_analysis -- ---------------------------- DROP TABLE IF EXISTS `t_member_analysis`; CREATE TABLE `t_member_analysis` ( `id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NULL DEFAULT NULL COMMENT '会员表主键', `count_time` int(11) NULL DEFAULT NULL COMMENT '消费次数', `count_money` decimal(10, 6) NULL DEFAULT NULL COMMENT '消费金额', `wechat_money` decimal(10, 6) NULL DEFAULT NULL, `zfb_money` decimal(10, 6) NULL DEFAULT NULL, `count` decimal(10, 6) NULL DEFAULT NULL, `card_money` decimal(10, 6) NULL DEFAULT NULL, `flag` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_member_analysis -- ---------------------------- INSERT INTO `t_member_analysis` VALUES (1, 1, 3, 500.500000, 200.000000, 300.500000, 0.000000, 0.000000, '中'); INSERT INTO `t_member_analysis` VALUES (2, 2, 1, 200.000000, 0.000000, 200.000000, 0.000000, 0.000000, '低'); INSERT INTO `t_member_analysis` VALUES (3, 3, 3, 1900.000000, 900.000000, 1000.000000, 0.000000, 0.000000, '高'); -- ---------------------------- -- Table structure for t_menu -- ---------------------------- DROP TABLE IF EXISTS `t_menu`; CREATE TABLE `t_menu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `price` decimal(10, 2) NULL DEFAULT NULL, `restaurant_id` int(11) NULL DEFAULT NULL, `flavor` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `picture_url` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `kitchen_id` int(11) NULL DEFAULT NULL, `material` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cook_time` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ` evaluate` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cost_price` decimal(10, 2) NULL DEFAULT NULL, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_menu -- ---------------------------- INSERT INTO `t_menu` VALUES (1, '红烧肉', '本帮菜', 40.00, 1, '甜', 'https://www.weuih/ujhdn/jhe.jpg', 3, '猪肉', '10分钟', '优', 20.00, NULL); INSERT INTO `t_menu` VALUES (2, '宫保鸡丁', '川菜', 35.00, 1, '甜辣', 'https://www.weuih/ujhdn/jhe.jpg', 3, '鸡肉，胡萝卜，花生', '6分钟', '优', 13.00, NULL); -- ---------------------------- -- Table structure for t_menu_type -- ---------------------------- DROP TABLE IF EXISTS `t_menu_type`; CREATE TABLE `t_menu_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `menu_id` int(11) NULL DEFAULT NULL, `type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `type_price` decimal(10, 2) NULL DEFAULT NULL, `type_cost_price` decimal(10, 2) NULL DEFAULT NULL, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_menu_type -- ---------------------------- INSERT INTO `t_menu_type` VALUES (1, 1, '大份', 65.00, 30.00, NULL); INSERT INTO `t_menu_type` VALUES (2, 1, '中份', 40.00, 20.00, NULL); INSERT INTO `t_menu_type` VALUES (3, 1, '小份', 20.00, 10.00, NULL); INSERT INTO `t_menu_type` VALUES (4, 2, '大份', 45.00, 20.00, NULL); INSERT INTO `t_menu_type` VALUES (5, 2, '中份', 35.00, 13.00, NULL); INSERT INTO `t_menu_type` VALUES (6, 2, '小份', 20.00, 8.00, NULL); -- ---------------------------- -- Table structure for t_order -- ---------------------------- DROP TABLE IF EXISTS `t_order`; CREATE TABLE `t_order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `table_id` int(11) NULL DEFAULT NULL, `employee_id` int(11) NULL DEFAULT NULL, `restaurant_id` int(11) NULL DEFAULT NULL, `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `is_member` int(1) NULL DEFAULT NULL COMMENT '0不是 1是', `member_id` int(11) NULL DEFAULT NULL, `order_money` decimal(10, 2) NULL DEFAULT NULL, `discount_money` decimal(10, 2) NULL DEFAULT NULL, `pay_money` decimal(10, 2) NULL DEFAULT NULL, `pay_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `people_num` int(11) NULL DEFAULT NULL, `wait_time` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `pay_time` datetime(0) NULL DEFAULT NULL, `telephone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_order -- ---------------------------- INSERT INTO `t_order` VALUES (2, 'wund2873n', 1, 2, 1, '堂食', 0, NULL, 325.00, 5.00, 320.00, '支付宝', '2019-11-13 20:47:14', 4, '2019-11-15 20:31:47', '2019-11-15 20:43:36', NULL, NULL, NULL); INSERT INTO `t_order` VALUES (3, 'wund2873k', 2, 2, 1, '堂食', 0, NULL, 325.00, 5.00, 320.00, '支付宝', '2019-11-14 20:37:18', 2, '3', '2019-11-15 20:39:16', NULL, NULL, NULL); -- ---------------------------- -- Table structure for t_order_detail -- ---------------------------- DROP TABLE IF EXISTS `t_order_detail`; CREATE TABLE `t_order_detail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NULL DEFAULT NULL, `menu_id` int(11) NULL DEFAULT NULL, `menu_type_id` int(11) NULL DEFAULT NULL, `count` int(11) NULL DEFAULT 1, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_order_detail -- ---------------------------- INSERT INTO `t_order_detail` VALUES (1, 2, 1, 1, 1, '不要太辣'); INSERT INTO `t_order_detail` VALUES (2, 2, 2, 5, 2, NULL); -- ---------------------------- -- Table structure for t_order_kitchen -- ---------------------------- DROP TABLE IF EXISTS `t_order_kitchen`; CREATE TABLE `t_order_kitchen` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_detail_id` int(11) NULL DEFAULT NULL, `kitchen_id` int(11) NULL DEFAULT NULL, `come_time` datetime(0) NULL DEFAULT NULL, `out_time` datetime(0) NULL DEFAULT NULL, `employee_id` int(11) NULL DEFAULT NULL, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_order_kitchen -- ---------------------------- INSERT INTO `t_order_kitchen` VALUES (1, 1, 3, '2019-11-13 20:58:33', '2019-11-15 20:28:26', 4, '不放蒜'); INSERT INTO `t_order_kitchen` VALUES (2, 2, 3, '2019-11-13 20:59:16', '2019-11-15 20:28:34', 4, NULL); -- ---------------------------- -- Table structure for t_post -- ---------------------------- DROP TABLE IF EXISTS `t_post`; CREATE TABLE `t_post` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `restaurant_id` int(11) NULL DEFAULT NULL, `post_money` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_post -- ---------------------------- INSERT INTO `t_post` VALUES (1, '经理', 1, 10000.00); INSERT INTO `t_post` VALUES (2, '服务员', 1, 4000.00); INSERT INTO `t_post` VALUES (3, '厨师', 1, 8000.00); -- ---------------------------- -- Table structure for t_restaurant -- ---------------------------- DROP TABLE IF EXISTS `t_restaurant`; CREATE TABLE `t_restaurant` ( `id` int(11) NOT NULL AUTO_INCREMENT, `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phone1` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phone2` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `employee_id` int(11) NULL DEFAULT NULL, `create_time` date NULL DEFAULT NULL, `work_time` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `restaurant_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `scale` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '集团内部定义规模', `turnover` decimal(65, 30) NULL DEFAULT NULL COMMENT '月平均营业额', `turnover_rate` double(3, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_restaurant -- ---------------------------- INSERT INTO `t_restaurant` VALUES (1, '跃龙南路', '洞子火锅', '0513-844821223', '0513-66668888', 1, '2011-01-13', 'AM9:00-PM22:30', 'H12389IWY9347NS888', '中型', 300000.000000000000000000000000000000, 2.70); -- ---------------------------- -- Table structure for t_restaurant_table -- ---------------------------- DROP TABLE IF EXISTS `t_restaurant_table`; CREATE TABLE `t_restaurant_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `restaurant_id` int(11) NULL DEFAULT NULL, `table_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `table_size` int(3) NULL DEFAULT NULL, `employee_id` int(11) NULL DEFAULT NULL, `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_restaurant_table -- ---------------------------- INSERT INTO `t_restaurant_table` VALUES (1, 1, '01', 4, 2, '大堂', NULL); INSERT INTO `t_restaurant_table` VALUES (2, 1, '02', 12, 3, '包厢', NULL); SET FOREIGN_KEY_CHECKS = 1; ~~~ ## 业务逻辑 ~~~sql # 未上线之前基础数据完善 # 业务流程处理 # 第一点注册会员 insert into t_member(name,sex,birthday,telephone,aviod_food,taste_like,isCar) value("张三",1,"1993-02-11","14672398712","酸","甜",1) insert into t_member(name,sex,birthday,telephone,aviod_food,taste_like,isCar) value("李四",1,19930211,"14672392112","苦","甜",1) insert into t_member(name,sex,birthday,telephone,aviod_food,taste_like,isCar) value("王五",1,19930211,"14672392112","苦","甜",1) # 第二点修改会员分析表数据(假如只知道叫王五) update t_member_analysis set count_time=2,count_money=1400,wechat_money=400 where member_id=(select id from t_member where name="王五") -- 使用update 和连接（内，外）进行多表的更新操作 update t_member_analysis a left join t_member m on a.member_id=m.id set a.count_time=3,a.count_money=1900,a.wechat_money=900 where m.name="王五" # 第三点点菜 insert into t_order(order_code,table_id,employee_id,restaurant_id,type,is_member,member_id,order_money,discount_money,pay_money,pay_type,create_time,people_num,wait_time,pay_time,telephone,address,description) value("wund2873n",1,2,1,"堂食",0,null,NULL,NULL,NULL,null,now(),4,null,null,null,null,null) # 第四点定菜（顾客确定菜品） insert into t_order_detail value(1,2,1,1,1,null) insert into t_order_detail value(null,2,2,5,2,null) # 第五点档口分配 insert into t_order_kitchen value(null,1,3,now(),null,4,"不放蒜") insert into t_order_kitchen value(null,2,3,now(),null,4,null) # 第六点档口成菜 -- 查看菜品查看本档口的要制作的订单编号,菜品名称，菜品大小（菜品规格），菜品份数，菜品顾客备注 select o.order_code,m.name,mt.type,od.count,od.description from t_order as o left join t_order_detail as od on o.id=od.order_id left join t_menu as m on od.menu_id=m.id left join t_menu_type as mt on od.menu_type_id=mt.id left join t_kitchen as k on m.kitchen_id=k.id where k.id=3 -- 出菜 update t_order_kitchen set out_time=now() where order_detail_id=2 # 第七点全菜 update t_order set wait_time=now() where id=2; # 第八点买单 -- 注意对于null进行判断不能使用!= = <>（永远返回false）,需要使用 is not null 和 is null update t_order set order_money=325,discount_money=5,pay_money=320,pay_type="支付宝",pay_time=now() where id=2 and wait_time is not null ~~~