AI 助理
备案控制台
开发者社区 数据库 文章 正文

使用 DML语句，对 “锦图网” 数据进行操作，聚合函数练习

2022-05-12 134
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 使用 DML语句，对 “锦图网” 数据进行操作，聚合函数练习

需求说明：


  • 根据客户 ID 统计订单数、订单总金额、最高订单金额、最低订单金额和每份订单平均金额，并按订单总金额升序显示
  • 根据客户统计订单总订购人次数＞ 5 的统计信息

根据以上要求对 “锦图网” 数据进行操作：

客户表（customer）

-- ----------------------------
-- 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');



线路类型表（lineType）


-- ----------------------------
-- 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', '出境游');



线路表（line）


-- ----------------------------
-- 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', '大巴', '无', '无');


订单表（orders）


-- ----------------------------
-- 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');


订单客户明细表（oc_detail）


-- ----------------------------
-- 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');


订单线路明细表（ol_detail）


-- ----------------------------
-- 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');


实现代码：


根据客户 ID 统计订单数、订单总金额、最高订单金额、最低订单金额和每份订单平均金额，并按订单总金额升序显示


select customerID 客户 ID, count(ordersID) 订单数 , sum(amount) 订单总金额 ,max(amount) 最高订单金额 ,min(amount) 最低订单金额 , avg(amount) 每份订单平均金额 from orders group by customerID order by 订单总金额


根据客户统计订单总订购人次数＞ 5 的统计信息


select customerID 客户 ID, sum(man_times) 总订购人次数 from orders group by customerID having sum(man_times)>5

 


“锦图网” 完整数据库：


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 ;
文章标签：
SQL
数据库
明金同学
目录
相关文章
修己xj
|
7月前
|
SQL 监控 关系型数据库
避免锁表：为Update语句中的Where条件添加索引字段
在一个灰度环境中，某业务创建数据时出现异常延迟，原本以为是第三方接口问题，但日志显示接口响应正常。进一步排查发现，工单表的SQL插入操作因另一个业务的无索引UPDATE操作阻塞。具体问题在于UPDATE语句的where子句涉及字段缺失索引，导致锁表并影响并发性能。通过复现问题并为相关字段添加索引，解决了阻塞问题。重要的是，在编写UPDATE语句时要注意Where条件字段的索引，以优化查询并减少锁表影响。
修己xj
139 6
避免锁表：为Update语句中的Where条件添加索引字段
Codelinghu
|
2月前
|
数据库
SQL_语句常见的操作创建，使用，删除表和表级约束
SQL_语句常见的操作创建，使用，删除表和表级约束
Codelinghu
37 0
多多!
|
6月前
|
SQL 数据库
零基础学习数据库SQL语句之操作表中数据的DML语句
零基础学习数据库SQL语句之操作表中数据的DML语句
多多!
63 0
零基础学习数据库SQL语句之操作表中数据的DML语句
智商低情商凑
|
6月前
|
SQL 数据库
DQL基本查询操作
DQL基本查询操作
智商低情商凑
32 2
红目香薰
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
红目香薰
66 1
多多!
|
6月前
|
SQL 前端开发 关系型数据库
零基础学习数据库SQL语句之查询表中数据的DQL语句
零基础学习数据库SQL语句之查询表中数据的DQL语句
多多!
41 0
魏红斌
|
7月前
|
SQL 关系型数据库 MySQL
SQL脚本利用带关联子查询Update语句更新数据
【5月更文挑战第5天】
魏红斌
135 0
季风泯灭的季节
|
7月前
|
SQL Oracle 关系型数据库
DML语句
DML语句
季风泯灭的季节
57 0
一身仙气
|
SQL 数据库
SQL中的DQL、DML、DDL和DCL是怎么区分和定义的
SQL中的DQL、DML、DDL和DCL是怎么区分和定义的
一身仙气
387 1
红目香薰
|
SQL 存储 运维
SQL语句分类DDL、DML、DQL、DCL语句
SQL语句分类DDL、DML、DQL、DCL语句
红目香薰
155 0

热门文章

最新文章

  • 1
    使用cProfile等工具来提高python的执行速度
  • 2
    四大触点，教你从“用户视角”构建数据分析体系
  • 3
    Spring-boot+Dubbo应用启停源码分析
  • 4
    docker阿里云镜像加速
  • 5
    《Stata统计分析与应用（第2版）》一3.4 图形的保存、合并及修改
  • 6
    语言设计中的鸭子类型风格
  • 7
    ActionBar的使用
  • 8
    [20150529]ORA-16664.txt
  • 9
    25个优秀的国外单页网站设计作品欣赏
  • 10
    人际风格的四大分类
  • 1
    【SpringFramework】Spring初体验
    75
  • 2
    「Mac畅玩鸿蒙与硬件46」UI互动应用篇23 - 自定义天气预报组件
    65
  • 3
    CDP与Selenium相结合——玩转网页端自动化数据采集/爬取程序
    63
  • 4
    解决Redis缓存数据类型丢失问题
    110
  • 5
    Flink 2.0 存算分离状态存储 — ForSt DB 
    114
  • 6
    RDS通用云盘核心能力
    65
  • 7
    体验云数据库RDS通用云盘核心能力
    34
  • 8
    产品动态丨阿里云弹性计算产品月刊（2024年11月）
    35
  • 9
    确保 Active Directory 安全性的方法
    31
  • 10
    【实践】快速学会使用云消息队列RabbitMQ版
    30

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
  • SQL进阶之约束、索引
  • SQL进阶之子句、关键字和操作符
    • 下一篇
    DataWorks