使用 DML语句，对 “锦图网” 数据进行操作，连接查询（内连接，左外连接，右外连接，全连接）

- 日理万妓 2022-12-28 00:59 51阅读 0赞

## [查看本章节][Link 1] ##

## [查看作业目录][Link 2] ##

--------------------

## 需求说明： ##

对 “锦图网” 数据进行操作：

*  统计每一种线路类型的线路数量、最高线路价格、最低线路价格和平均线路价格，要求按照线路数量和平均线路价格升序显示。
 *  统计每种类型线路的订购数量和平均价格，要求按线路数量和平均线路价格升序显示
 *  查询指定客户（如“郝琼琼”）所预订的所有线路信息，要求显示下单客户姓名、出行客户姓名、订单号、线路名、下单日期和出行日期

## 实现思路： ##

1.  依据“统计每一种线路类型”可知，需按照线路类型分组
2.  需连接的表为线路类型表、线路表和订单线路明细表。由“统计每一个类型的线路订购”，可知需按照线路类型分组
3.  客户不仅可以预订自己的旅游线路，还可以为其他客户预订。所以不仅需要在订单表中查询“郝琼琼”作为下单人所预订的线路，还要包括此人替其他客户所预订的线路。需要连接的表为客户表、订单表、订单线路表、订单客户表和线路表

## 实现代码： ##

统计每一种线路类型的线路数量、最高线路价格、最低线路价格和平均线路价格，要求按照线路数量和平均线路价格升序显示。

SELECT typeName 线路类型名,COUNT(lineID) 线路数量,ROUND(MAX(price),2) 最高线路价格（元）,
    ROUND(MIN(price),2) 最低线路价格（元）,ROUND(AVG(price),2) 平均线路价格（元）
    FROM line l,linetype lp WHERE l.lineTypeID=lp.lineTypeID GROUP BY typeName
    ORDER BY 线路数量,AVG(price)

统计每种类型线路的订购数量和平均价格，要求按线路数量和平均线路价格升序显示

SELECT typeName 线路类型名,COUNT(old.lineID) 线路订购数量,ROUND(AVG(price),2) 平均线路订购价格（元）
    FROM line l,linetype lp,ol_detail old WHERE
    l.lineTypeID=lp.lineTypeID AND l.lineID=old.lineID GROUP BY typeName
    ORDER BY 线路订购数量,AVG(price)

查询指定客户（如“郝琼琼”）所预订的所有线路信息，要求显示下单客户姓名、出行客户姓名、订单号、线路名、下单日期和出行日期

SELECT c1.name 下单客户姓名,c2.name 出行客户姓名,o.ordersID 订单号,
    lineName 线路名,ordersDate 下单日期,travelDate 出行日期 FROM customer c1,customer c2,
    orders o,ol_detail old,oc_detail ocd,line l WHERE c1.customerID=o.customerID AND
    c1.name='李四' AND o.ordersID=old.ordersID AND old.lineID=l.lineID AND o.ordersID=
    ocd.ordersID AND ocd.travelCustomerID=c2.customerID ORDER BY 出行日期

> ### “锦图网” 完整数据库： ###

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 ;

[Link 1]: https://blog.csdn.net/weixin_44893902/article/details/110906842
[Link 2]: https://blog.csdn.net/weixin_44893902/article/details/110881499