需求说明：
对比显示每条线路的价格和该类型线路的平均价格
分别使用子查询和 exists 获取线路数量超过“出境游”线路数的线路类型信息，要求按照线路数升序显示线路类型编号和线路数
实现思路：
需求说明（1）的解决思路
- 在 from 子句中显示当前线路所属类型的平均价格
需求说明（2）的解决思路
单行子查询：从线路类型表获取线路名为“出境游”的线路类型编号
多行子查询：使用 count( 线路编号 ) 从线路表获取“出境游”的线路数，将从单行子查询中获取的线路类型编号作为比较条件
主查询：在线路表中，依据线路类型编号进行分组，使用 count( 线路编号 ) 计算出不同类型的线路数，并使用 having 子句对分组数据进行限制，将从多行子查询中获取的“出境游”线路数作为限制条件
实现代码：
对比显示每条线路的价格和该类型线路的平均价格
SELECT A.lineTypeID 类型,lineName 线路名,price 价格,该线路类型平均价格 FROM line A, (SELECT lineTypeID, AVG(price) 该线路类型平均价格 FROM line GROUP BY lineTypeID) B WHERE A.lineTypeID=B.lineTypeID;
分别使用子查询和 exists 获取线路数量超过“出境游”线路数的线路类型信息，要求按照线路数升序显示线路类型编号和线路数
SELECT lineTypeID 线路类型编号 ,COUNT(lineID) 线路数 FROM line GROUP BY lineTypeID HAVING COUNT(lineID)>(SELECT COUNT(lineID) FROM line WHERE lineTypeID= (SELECT lineTypeID FROM lineType WHERE typeName='出境游'));
数据库：
/* Navicat MySQL Data Transfer Source Server : mysql-1 Source Server Version : 50624 Source Host : localhost:3306 Source Database : journey Target Server Type : MYSQL Target Server Version : 50624 File Encoding : 65001 Date: 2019-01-26 11:19:38 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `customer` -- ---------------------------- DROP TABLE IF EXISTS `customer`; CREATE TABLE `customer` ( `customerID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `gender` varchar(50) DEFAULT NULL, `identityID` varchar(18) DEFAULT NULL, `tel` varchar(18) DEFAULT NULL, PRIMARY KEY (`customerID`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of customer -- ---------------------------- INSERT INTO `customer` VALUES ('1', '魏国兰', '女', '420103198309125344', '13923561234'); INSERT INTO `customer` VALUES ('2', '刘亚蒙', '男', '420105197610200916', '13867893421'); INSERT INTO `customer` VALUES ('3', '郝琼琼', '女', '420104198703125881', '15902712563'); INSERT INTO `customer` VALUES ('4', '雷亚波', '男', '420103199806195830', '13686035678'); INSERT INTO `customer` VALUES ('5', '李慧娟', '女', '420106199208113738', '13798235671'); -- ---------------------------- -- Table structure for `line` -- ---------------------------- DROP TABLE IF EXISTS `line`; CREATE TABLE `line` ( `lineID` int(11) NOT NULL AUTO_INCREMENT, `lineTypeID` int(11) DEFAULT NULL, `lineName` varchar(50) NOT NULL, `days` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `vehicle` char(10) DEFAULT NULL, `hotel` char(10) DEFAULT NULL, `hasMeal` char(2) DEFAULT NULL, PRIMARY KEY (`lineID`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of line -- ---------------------------- INSERT INTO `line` VALUES ('1', '1', '黄陂木兰天池', '1', '159.00', '大巴', '无', '无'); INSERT INTO `line` VALUES ('2', '1', '大别山天堂寨', '2', '429.00', '大巴', '二星级', '无'); INSERT INTO `line` VALUES ('5', '1', '恩施大峡谷', '4', '1089.00', '火车卧铺', '二星级', '无'); INSERT INTO `line` VALUES ('6', '1', '庐山', '2', '729.00', '大巴', '二星级', '含'); INSERT INTO `line` VALUES ('7', '1', '凤凰古城', '3', '959.00', '火车卧铺', '二星级', '含'); INSERT INTO `line` VALUES ('8', '1', '黄山', '3', '1099.00', '动车', '三星级', '含'); INSERT INTO `line` VALUES ('9', '2', '海南岛三亚', '5', '3868.00', '飞机', '三星级', '含'); INSERT INTO `line` VALUES ('10', '2', '青岛蓬莱', '4', '2680.00', '飞机', '三星级', '含'); INSERT INTO `line` VALUES ('12', '2', '桂林', '5', '1920.00', '火车卧铺', '二星级', '无'); INSERT INTO `line` VALUES ('13', '2', '华东五市', '6', '2856.00', '动车', '三星级', '含'); INSERT INTO `line` VALUES ('14', '2', '成都九寨沟', '7', '4500.00', '飞机', '三星级', '含'); INSERT INTO `line` VALUES ('15', '2', '西安', '4', '2180.00', '动车', '三星级', '无'); INSERT INTO `line` VALUES ('16', '3', '欧洲德法意瑞', '13', '12294.91', '飞机', '四星级', '含'); INSERT INTO `line` VALUES ('17', '3', '日本东京富士山', '6', '7119.09', '飞机', '三星级', '含'); INSERT INTO `line` VALUES ('18', '3', '新马泰', '8', '6058.80', '飞机', '三星级', '含'); INSERT INTO `line` VALUES ('19', '3', '美国夏威夷', '6', '11493.90', '飞机', '四星级', '无'); INSERT INTO `line` VALUES ('20', null, '梁子湖游', '1', '168.00', '大巴', '无', '无'); INSERT INTO `line` VALUES ('21', null, '洪湖游', '1', '128.00', '大巴', '无', '无'); -- ---------------------------- -- Table structure for `linetype` -- ---------------------------- DROP TABLE IF EXISTS `linetype`; CREATE TABLE `linetype` ( `lineTypeID` int(11) NOT NULL AUTO_INCREMENT, `typeName` varchar(50) DEFAULT NULL, PRIMARY KEY (`lineTypeID`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of linetype -- ---------------------------- INSERT INTO `linetype` VALUES ('1', '国内短线游'); INSERT INTO `linetype` VALUES ('2', '国内长线游'); INSERT INTO `linetype` VALUES ('3', '出境游'); -- ---------------------------- -- Table structure for `oc_detail` -- ---------------------------- DROP TABLE IF EXISTS `oc_detail`; CREATE TABLE `oc_detail` ( `travelCustomerID` int(11) NOT NULL, `ordersID` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of oc_detail -- ---------------------------- INSERT INTO `oc_detail` VALUES ('1', '1'); INSERT INTO `oc_detail` VALUES ('1', '2'); INSERT INTO `oc_detail` VALUES ('2', '3'); INSERT INTO `oc_detail` VALUES ('2', '5'); INSERT INTO `oc_detail` VALUES ('2', '7'); INSERT INTO `oc_detail` VALUES ('3', '3'); INSERT INTO `oc_detail` VALUES ('3', '4'); INSERT INTO `oc_detail` VALUES ('3', '7'); INSERT INTO `oc_detail` VALUES ('4', '3'); INSERT INTO `oc_detail` VALUES ('4', '5'); INSERT INTO `oc_detail` VALUES ('4', '8'); INSERT INTO `oc_detail` VALUES ('4', '9'); INSERT INTO `oc_detail` VALUES ('5', '1'); INSERT INTO `oc_detail` VALUES ('5', '6'); -- ---------------------------- -- Table structure for `ol_detail` -- ---------------------------- DROP TABLE IF EXISTS `ol_detail`; CREATE TABLE `ol_detail` ( `ordersID` int(11) NOT NULL, `lineID` int(11) NOT NULL, `travelDate` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of ol_detail -- ---------------------------- INSERT INTO `ol_detail` VALUES ('1', '2', '2018-10-27'); INSERT INTO `ol_detail` VALUES ('2', '5', '2018-01-20'); INSERT INTO `ol_detail` VALUES ('2', '7', '2018-02-01'); INSERT INTO `ol_detail` VALUES ('3', '1', '2018-06-26'); INSERT INTO `ol_detail` VALUES ('3', '6', '2018-07-05'); INSERT INTO `ol_detail` VALUES ('4', '13', '2018-08-29'); INSERT INTO `ol_detail` VALUES ('5', '1', '2018-10-16'); INSERT INTO `ol_detail` VALUES ('5', '14', '2018-10-21'); INSERT INTO `ol_detail` VALUES ('6', '18', '2018-07-10'); INSERT INTO `ol_detail` VALUES ('7', '15', '2018-10-19'); INSERT INTO `ol_detail` VALUES ('8', '19', '2018-11-27'); INSERT INTO `ol_detail` VALUES ('9', '7', '2018-12-28'); -- ---------------------------- -- Table structure for `orders` -- ---------------------------- DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` ( `ordersID` int(11) NOT NULL AUTO_INCREMENT, `customerID` int(11) DEFAULT NULL, `ordersDate` date DEFAULT NULL, `amount` decimal(8,2) DEFAULT NULL, `man_times` int(11) DEFAULT NULL, `discount` decimal(8,2) DEFAULT NULL, `effectiveAmount` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`ordersID`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of orders -- ---------------------------- INSERT INTO `orders` VALUES ('1', '1', '2018-10-20', '798.00', '2', '0.98', '782.04'); INSERT INTO `orders` VALUES ('2', '1', '2018-01-15', '1898.00', '2', '0.98', '1860.04'); INSERT INTO `orders` VALUES ('3', '2', '2018-06-18', '2574.00', '6', '0.96', '2471.04'); INSERT INTO `orders` VALUES ('4', '3', '2018-08-21', '2856.00', '1', '1.00', '2856.00'); INSERT INTO `orders` VALUES ('5', '4', '2018-10-10', '7698.00', '4', '0.96', '7390.08'); INSERT INTO `orders` VALUES ('6', '5', '2018-06-23', '6732.00', '1', '1.00', '6732.00'); INSERT INTO `orders` VALUES ('7', '3', '2018-10-11', '4360.00', '2', '0.98', '4272.80'); INSERT INTO `orders` VALUES ('8', '4', '2018-11-21', '12771.00', '1', '1.00', '12771.00'); INSERT INTO `orders` VALUES ('9', '4', '2013-12-20', '899.00', '1', '0.98', '881.02'); -- ---------------------------- -- View structure for `v_customer_orderline_detail` -- ---------------------------- DROP VIEW IF EXISTS `v_customer_orderline_detail`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER VIEW `v_customer_orderline_detail` AS select `c`.`name` AS `客户名`,`l`.`lineName` AS `线路名`,`l`.`days` AS `行程天数`,`l`.`price` AS `价格` from (((`customer` `c` join `oc_detail` `ocd`) join `ol_detail` `old`) join `line` `l`) where ((`c`.`customerID` = `ocd`.`travelCustomerID`) and (`ocd`.`ordersID` = `old`.`ordersID`) and (`old`.`lineID` = `l`.`lineID`)) ; -- ---------------------------- -- View structure for `v_customer_orders_detail` -- ---------------------------- DROP VIEW IF EXISTS `v_customer_orders_detail`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER VIEW `v_customer_orders_detail` AS select `c`.`name` AS `name`,`l`.`lineName` AS `lineName`,`l`.`days` AS `days`,`l`.`price` AS `price` from (((`customer` `c` join `oc_detail` `ocd`) join `ol_detail` `old`) join `line` `l`) where ((`c`.`customerID` = `ocd`.`travelCustomerID`) and (`ocd`.`ordersID` = `old`.`ordersID`) and (`old`.`lineID` = `l`.`lineID`)) ; -- ---------------------------- -- View structure for `v_nums_line` -- ---------------------------- DROP VIEW IF EXISTS `v_nums_line`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER VIEW `v_nums_line` AS select `l`.`lineName` AS `线路`,count(`old`.`lineID`) AS `预订数` from (`line` `l` join `ol_detail` `old`) where (`l`.`lineID` = `old`.`lineID`) group by `l`.`lineName` ; -- ---------------------------- -- Procedure structure for `proc_adjust_price` -- ---------------------------- DROP PROCEDURE IF EXISTS `proc_adjust_price`; DELIMITER ;; CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_adjust_price`( out oldPrice decimal, -- 原价 out newPrice decimal, -- 现价 out lineName_maxPrice varchar(50) -- 价格最高国内长线游线路名 ) MODIFIES SQL DATA begin declare lineID_maxPrice int; -- 价格最高国内长线游线路编号 select max(price) into oldPrice from line where lineTypeID= (select lineTypeID from linetype where typeName='国内长线游'); select lineID, lineName into lineID_maxPrice, lineName_maxPrice from line where price=oldPrice and lineTypeID=(select lineTypeID from linetype where typeName='国内长线游'); if oldPrice<3000 then set newPrice=oldPrice; elseif oldPrice>=3000 and oldPrice<4000 then set newPrice=oldPrice*0.95; elseif oldPrice>=4000 and oldPrice<5000 then set newPrice=oldPrice*0.93; else set newPrice=oldPrice*0.90; end if; if newPrice<>oldPrice then update line set price=newPrice where lineID=lineID_maxPrice; end if; end ;; DELIMITER ; -- ---------------------------- -- Procedure structure for `proc_deleteLineType` -- ---------------------------- DROP PROCEDURE IF EXISTS `proc_deleteLineType`; DELIMITER ;; CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_deleteLineType`( _typeName varchar(20) -- 线路类型名称 ) MODIFIES SQL DATA begin declare state varchar(20); declare _lineTypeID int; -- 线路类型编号 -- 定义错误处理 declare continue handler for sqlexception set state='error'; select lineTypeID into _lineTypeID from LineType where typeName=_typeName; -- 开启事务 start transaction; -- 将线路中所需要删除的线路类型的编号置为NULL update line set lineTypeID=NULL where lineTypeID=_lineTypeID; if(state='error') then select '线路信息修改失败'; rollback; else delete from LineType where typeName=_typeName; if(state='error') then select '线路类型删除失败'; rollback; else select '线路类型删除成功'; commit; end if; end if; end ;; DELIMITER ; -- ---------------------------- -- Procedure structure for `proc_LineDetail` -- ---------------------------- DROP PROCEDURE IF EXISTS `proc_LineDetail`; DELIMITER ;; CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_LineDetail`( _lineName varchar(20) ) READS SQL DATA begin select lineName 线路名, days 行程天数, price 价格, vehicle 交通工具, hotel 住宿标准 from line where lineName=_lineName; end ;; DELIMITER ; -- ---------------------------- -- Procedure structure for `proc_LineNumsRate` -- ---------------------------- DROP PROCEDURE IF EXISTS `proc_LineNumsRate`; DELIMITER ;; CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_LineNumsRate`( _typeName varchar(20), out numsRate decimal(10,3) ) READS SQL DATA begin declare totalNum int; -- 定义全部线路数 declare num int; -- 定义指定类型的线路数 select count(*) into totalNum from line; select count(*) into num from line L, linetype LT where L.lineTypeID=LT.lineTypeID and LT.typeName=_typeName; -- 生成指定类型的线路数与全部线路数之比，赋给输出参数numsRate set numsRate=num*1.0/totalNum; end ;; DELIMITER ; -- ---------------------------- -- Procedure structure for `proc_NumsGivenLineType` -- ---------------------------- DROP PROCEDURE IF EXISTS `proc_NumsGivenLineType`; DELIMITER ;; CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_NumsGivenLineType`( _typeName varchar(20), out count int -- 输出参数，用于输出指定线路类型的总预订数 ) READS SQL DATA begin select count(OLD.lineID) into count from ol_detail OLD, line L, lineType LT where OLD.lineID=L.lineID and L.lineTypeID=LT.lineTypeID and typeName=_typeName; end ;; DELIMITER ; -- ---------------------------- -- Procedure structure for `proc_PriceModify` -- ---------------------------- DROP PROCEDURE IF EXISTS `proc_PriceModify`; DELIMITER ;; CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `proc_PriceModify`() MODIFIES SQL DATA begin declare _lineID int; declare _days int; -- 线路旅程天数 declare reduce_money decimal(10,2); -- 减免的住宿费 declare state varchar(20); -- 错误状态 declare line_cursor1 cursor for select lineID, days from line where hotel='二星级'; declare continue handler for 1329 set state='error'; open line_cursor1; traverse_line:while true do fetch line_cursor1 into _lineID, _days; if(state='error') then leave traverse_line; end if; set reduce_money=(_days-1)*30; update line set price=price-reduce_money where lineID=_lineID; end while; close line_cursor1; end ;; DELIMITER ;
