綜合應用 · Java96班隨堂筆記

# 綜合應用 > 結合之前所學知識，運用 SQL 語言完成一個實例應用的數據操作，重點是了解在應用程序中，如何構建數據查詢語句。 > 任何基于數據庫的應用程序類型，包括 B/S 架構、 C/S 架構、APP 應用，其實歸根結底都是用不同的界面或者形式完成對數據庫中數據的操作，所以，了解數據中應用的處理過程很重要。一個典型的應用程序，包含的功能有： * 基礎數據維護：一般通過初始化完成部分數據，后續再次維護； * 業務流程處理：一般通過具體的功能模塊完成； * 數據分析和報表：通過相關復雜的查詢完成，使用視圖，或者緩存統計報表。下面，我們從上一章節中的訂餐系統進行延伸，模擬系統的數據操作過程等。 ## 表結構以下為表的物理結構腳本 ```sql -- ---------------------------- -- Table structure for t_base_customer -- ---------------------------- DROP TABLE IF EXISTS `t_base_customer`; CREATE TABLE `t_base_customer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `sex` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, `tel` varchar(255) DEFAULT NULL, `wechat` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `birthday` date DEFAULT NULL, `profession` varchar(255) DEFAULT NULL, `image` varchar(255) DEFAULT NULL, `vip_code` varchar(255) DEFAULT NULL, `is_vip` int(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `dislike` varchar(255) DEFAULT NULL, `con_count` int(11) DEFAULT NULL, `con_able` varchar(255) DEFAULT NULL, `con_time` time DEFAULT NULL, `con_closest` datetime DEFAULT NULL, `con_sum` decimal(10,2) DEFAULT NULL, `con_style` varchar(255) DEFAULT NULL, `have_car` int(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='會員表'; -- ---------------------------- -- Table structure for t_base_employee -- ---------------------------- DROP TABLE IF EXISTS `t_base_employee`; CREATE TABLE `t_base_employee` ( `id` int(11) NOT NULL, `restaurant_id` int(11) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `position_id` int(11) DEFAULT NULL, `tel` varchar(255) DEFAULT NULL, `sex` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `id_number` varchar(255) DEFAULT NULL, `id_image` varchar(255) DEFAULT NULL, `entry_time` date DEFAULT NULL, `marry` varchar(255) DEFAULT NULL, `wechat` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_employee_restaurant` (`restaurant_id`), KEY `FK_fk_employee_type` (`position_id`), CONSTRAINT `FK_fk_employee_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `t_base_restaurant` (`id`), CONSTRAINT `FK_fk_employee_type` FOREIGN KEY (`position_id`) REFERENCES `t_base_position_type` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_base_menu -- ---------------------------- DROP TABLE IF EXISTS `t_base_menu`; CREATE TABLE `t_base_menu` ( `id` int(11) NOT NULL, `type_id` int(11) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `standard_price` decimal(10,2) DEFAULT NULL, `specification` varchar(255) DEFAULT NULL, `special` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `praise` varchar(255) DEFAULT NULL, `image` varchar(255) DEFAULT NULL, `restaurant_id` int(11) DEFAULT NULL, `window_id` int(11) DEFAULT NULL, `cost` decimal(10,2) DEFAULT NULL, `finish_time` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_menue_restaurant` (`restaurant_id`), KEY `FK_fk_menue_type` (`type_id`), KEY `FK_fk_menue_window` (`window_id`), CONSTRAINT `FK_fk_menue_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `t_base_restaurant` (`id`), CONSTRAINT `FK_fk_menue_type` FOREIGN KEY (`type_id`) REFERENCES `t_base_menu_type` (`id`), CONSTRAINT `FK_fk_menue_window` FOREIGN KEY (`window_id`) REFERENCES `t_base_window` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_base_menu_stand -- ---------------------------- DROP TABLE IF EXISTS `t_base_menu_stand`; CREATE TABLE `t_base_menu_stand` ( `id` int(11) NOT NULL AUTO_INCREMENT, `specification` varchar(255) DEFAULT NULL, `specification_price` decimal(10,2) DEFAULT NULL, `specification_cost` decimal(10,2) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `menu_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_STAND_MENU` (`menu_id`), CONSTRAINT `FK_STAND_MENU` FOREIGN KEY (`menu_id`) REFERENCES `t_base_menu` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_base_menu_type -- ---------------------------- DROP TABLE IF EXISTS `t_base_menu_type`; CREATE TABLE `t_base_menu_type` ( `id` int(11) NOT NULL, `code` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_base_position_type -- ---------------------------- DROP TABLE IF EXISTS `t_base_position_type`; CREATE TABLE `t_base_position_type` ( `id` int(11) NOT NULL, `code` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_base_restaurant -- ---------------------------- DROP TABLE IF EXISTS `t_base_restaurant`; CREATE TABLE `t_base_restaurant` ( `id` int(11) NOT NULL, `code` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `scale` varchar(255) DEFAULT NULL, `tel` varchar(255) DEFAULT NULL, `type` varchar(255) DEFAULT NULL, `manager_id` int(11) DEFAULT NULL, `m_tel` varchar(255) DEFAULT NULL, `legal_per` varchar(255) DEFAULT NULL, `license_code` varchar(255) DEFAULT NULL, `open_time` time DEFAULT NULL, `avg_customer` int(11) DEFAULT NULL, `official_account` varchar(255) DEFAULT NULL, `avg_day_turnover` decimal(10,2) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `table_turnover_rate` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_restaurant_employee` (`manager_id`), CONSTRAINT `FK_fk_restaurant_employee` FOREIGN KEY (`manager_id`) REFERENCES `t_base_employee` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_base_table -- ---------------------------- DROP TABLE IF EXISTS `t_base_table`; CREATE TABLE `t_base_table` ( `id` int(11) NOT NULL, `restaurant_id` int(11) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `people_num` int(11) DEFAULT NULL, `position` varchar(255) DEFAULT NULL, `kind` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_table_restaurant` (`restaurant_id`), CONSTRAINT `FK_fk_table_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `t_base_restaurant` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_base_window -- ---------------------------- DROP TABLE IF EXISTS `t_base_window`; CREATE TABLE `t_base_window` ( `id` int(11) NOT NULL, `restaurant_id` int(11) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `machine_code` varchar(255) DEFAULT NULL, `manager_id` int(11) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_window_employee` (`manager_id`), CONSTRAINT `FK_fk_window_employee` FOREIGN KEY (`manager_id`) REFERENCES `t_base_employee` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_busi_order_detail -- ---------------------------- DROP TABLE IF EXISTS `t_busi_order_detail`; CREATE TABLE `t_busi_order_detail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_head_id` int(11) DEFAULT NULL, `menu_id` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `fode_code_id` int(11) DEFAULT NULL, `cost` decimal(10,2) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `amount` int(11) DEFAULT NULL, `specification` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_detail_fode_code` (`fode_code_id`), KEY `FK_fk_detail_head` (`order_head_id`), KEY `FK_fk_detail_menue` (`menu_id`), CONSTRAINT `FK_DETAIL_MENU` FOREIGN KEY (`menu_id`) REFERENCES `t_base_menu` (`id`), CONSTRAINT `FK_fk_detail_fode_code` FOREIGN KEY (`fode_code_id`) REFERENCES `t_base_menu_stand` (`id`), CONSTRAINT `FK_fk_detail_head` FOREIGN KEY (`order_head_id`) REFERENCES `t_busi_order_head` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_busi_order_head -- ---------------------------- DROP TABLE IF EXISTS `t_busi_order_head`; CREATE TABLE `t_busi_order_head` ( `id` int(11) NOT NULL AUTO_INCREMENT, `restaurant_id` int(11) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `member` varchar(255) DEFAULT NULL, `reservation_code` varchar(255) DEFAULT NULL, `reservation_tel` varchar(255) DEFAULT NULL, `reservation_name` varchar(255) DEFAULT NULL, `reservation_num` int(11) DEFAULT NULL, `reservation_comment` varchar(255) DEFAULT NULL, `table_id` int(11) DEFAULT NULL, `amount` int(11) DEFAULT NULL, `enter_time` datetime DEFAULT NULL, `confirm_time` datetime DEFAULT NULL, `check_out_time` datetime DEFAULT NULL, `employee_id` int(11) DEFAULT NULL, `customer_id` int(11) DEFAULT NULL, `total` decimal(10,2) DEFAULT NULL, `discount` decimal(10,2) DEFAULT NULL, `actual_amount` decimal(10,2) DEFAULT NULL, `pay_type` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `state` int(11) DEFAULT NULL, `cashier_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_order_customer` (`customer_id`) USING BTREE, KEY `FK_fk_order_employee` (`employee_id`) USING BTREE, KEY `FK_fk_order_restaurant` (`restaurant_id`) USING BTREE, KEY `FK_fk_order_table` (`table_id`) USING BTREE, CONSTRAINT `FK_fk_order_customer` FOREIGN KEY (`customer_id`) REFERENCES `t_base_customer` (`id`), CONSTRAINT `FK_fk_order_employee` FOREIGN KEY (`employee_id`) REFERENCES `t_base_employee` (`id`), CONSTRAINT `FK_fk_order_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `t_base_restaurant` (`id`), CONSTRAINT `FK_fk_order_table` FOREIGN KEY (`table_id`) REFERENCES `t_base_table` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_busi_window_detail -- ---------------------------- DROP TABLE IF EXISTS `t_busi_window_detail`; CREATE TABLE `t_busi_window_detail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `p_id` int(11) DEFAULT NULL, `order_detail_id` int(11) DEFAULT NULL, `finish_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_windowdetail_detail` (`order_detail_id`), KEY `FK_fk_windowdetail_windoworder` (`p_id`), CONSTRAINT `FK_P_ID` FOREIGN KEY (`p_id`) REFERENCES `t_busi_window_order_head` (`id`), CONSTRAINT `FK_fk_windowdetail_detail` FOREIGN KEY (`order_detail_id`) REFERENCES `t_busi_order_detail` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_busi_window_order_head -- ---------------------------- DROP TABLE IF EXISTS `t_busi_window_order_head`; CREATE TABLE `t_busi_window_order_head` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_head_id` int(11) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `window_id` int(11) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `amount` int(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_fk_windoworder_order_` (`order_head_id`), CONSTRAINT `FK_fk_windoworder_order_` FOREIGN KEY (`order_head_id`) REFERENCES `t_busi_order_head` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8; ``` ## 數據處理 **1、注冊會員** > 輸入項：曹敏、女、1380000000，作為一個完整的記錄，還需要默認值 ```sql INSERT INTO t_base_customer(name, sex, tel, vip_code, is_vip) VALUES('曹敏', 1, '138000000', CONCAT('V',unix_timestamp(now())) ,1); ``` **2、會員信息完善** > 輸入項：用戶ID、微信賬號、地址、生日 ```sql UPDATE t_base_customer SET wechat='123456', address='南通', birthday='1995-10-10' WHERE id=4; ``` **3、客戶點餐** > 輸入項：餐廳、服務員、餐桌、時間、會員、就餐人數 ```sql INSERT INTO t_busi_order_head(code, member, restaurant_id, employee_id, table_id, enter_time, customer_id, amount, state) VALUES(CONCAT('PO',unix_timestamp(now())),'是', 1, 4, 1, now(), 4, 8, 1); ``` **4、確認訂單菜品** 4.1、插入訂單明細數據 > 輸入性：關聯訂單、規格ID、數量、非價格屬性規格、備注 ```sql INSERT INTO t_busi_order_detail(order_head_id, menu_id, name, fode_code_id, cost, price, amount, specification, comment) SELECT 5, m.id, m.name, s.id, s.specification_price, s.specification_cost, 1, '微辣', '少油' FROM t_base_menu_stand s LEFT JOIN t_base_menu m ON s.menu_id = m.id WHERE s.id = 1; INSERT INTO t_busi_order_detail(order_head_id, menu_id, name, fode_code_id, cost, price, amount, specification, comment) SELECT 5, m.id, m.name, s.id, s.specification_price, s.specification_cost, 1, '', '' FROM t_base_menu_stand s LEFT JOIN t_base_menu m ON s.menu_id = m.id WHERE s.id = 3; INSERT INTO t_busi_order_detail(order_head_id, menu_id, name, fode_code_id, cost, price, amount, specification, comment) SELECT 5, m.id, m.name, s.id, s.specification_price, s.specification_cost, 1, '中辣', '不要香菜' FROM t_base_menu_stand s LEFT JOIN t_base_menu m ON s.menu_id = m.id WHERE s.id = 6; INSERT INTO t_busi_order_detail(order_head_id, menu_id, name, fode_code_id, cost, price, amount, specification, comment) SELECT 5, m.id, m.name, s.id, s.specification_price, s.specification_cost, 1, '中辣', '不要香菜' FROM t_base_menu_stand s LEFT JOIN t_base_menu m ON s.menu_id = m.id WHERE s.id = 7; ``` 4.2、確認訂單：修改訂單頭 > 輸入項：訂單編號，默認操作：確認時間、修改訂單狀態 ```sql UPDATE t_busi_order_head SET confirm_time = now(), state = 2 WHERE id = 5; ``` 4.3、分配檔口訂單 > 輸入項：訂單編號,創建檔口訂單頭表 ```sql INSERT INTO t_busi_window_order_head(amount, order_head_id, code, window_id, create_time) SELECT count(*), 5, CONCAT('D',unix_timestamp(now())),m.window_id , now() FROM t_busi_order_detail d LEFT JOIN t_busi_order_head h ON d.order_head_id = h.id LEFT JOIN t_base_menu m ON d.menu_id = m.id WHERE h.id = 5 GROUP BY m.window_id; ``` 4.4、檔口訂單明細 > 輸入項：檔口訂單，檔口ID，訂單ID ```sql INSERT INTO t_busi_window_detail(p_id, order_detail_id, finish_time) SELECT 9, d.id, DATE_ADD(h.confirm_time,INTERVAL m.finish_time*d.amount MINUTE) FROM t_busi_order_detail d LEFT JOIN t_busi_order_head h ON d.order_head_id = h.id LEFT JOIN t_base_menu m ON d.menu_id = m.id WHERE m.window_id = 1 AND h.id = 5; INSERT INTO t_busi_window_detail(p_id, order_detail_id, finish_time) SELECT 10, d.id, DATE_ADD(h.confirm_time,INTERVAL m.finish_time*d.amount MINUTE) FROM t_busi_order_detail d LEFT JOIN t_busi_order_head h ON d.order_head_id = h.id LEFT JOIN t_base_menu m ON d.menu_id = m.id WHERE m.window_id = 3 AND h.id = 5; INSERT INTO t_busi_window_detail(p_id, order_detail_id, finish_time) SELECT 11, d.id, DATE_ADD(h.confirm_time,INTERVAL m.finish_time*d.amount MINUTE) FROM t_busi_order_detail d LEFT JOIN t_busi_order_head h ON d.order_head_id = h.id LEFT JOIN t_base_menu m ON d.menu_id = m.id WHERE m.window_id = 4 AND h.id = 5; ``` **5、埋單** 5.1、修改訂單數據 > 輸入項：關聯訂單、收銀員、優惠金額、支付方式 ```sql UPDATE t_busi_order_head h INNER JOIN (SELECT SUM(d.price * d.amount) as psum, d.order_head_id as order_id FROM t_busi_order_detail d GROUP BY d.order_head_id) AS t ON h.id = t.order_id SET h.cashier_id = 2, h.state = 3, h.total = t.psum, h.discount = 10, h.actual_amount = total - discount, h.check_out_time = now(), pay_type = '現金' WHERE h.id = 5; ``` 5.2、修改會員消費數據：增加消費數量、修改最近消費時間、增加消費總額，注意判斷字段是否為空 > 輸入項：關聯訂單 ```sql UPDATE t_base_customer c INNER JOIN t_busi_order_head h ON h.customer_id = c.id AND h.id = 5 SET c.con_closest = h.confirm_time, c.con_count = IFNULL(c.con_count,0) + 1, c.con_sum = IFNULL(c.con_sum,0) + h.actual_amount ``` **6、統計分析** 各自整理。