代码编织梦想

查看本章节

查看作业目录


需求说明:

使用子查询统计“国内长线游”线路数、线路最高价格和线路最低价格

使用子查询获得指定客户(如“魏国兰”)订购线路的相关信息,要求显示订单名和订单日期

说明:客户姓名“魏国兰”没有重名

实现思路:

需求说明(1)的解决思路:

  • 单行子查询:从线路类型表获取类型名为“国内长线游”的类型编号
  • 主查询:使用聚合函数从线路表获取线路统计信息,将子查询所获取的类型编号作为主查询的条件比较值

需求说明(2)的解决思路:

  • 单行子查询:从客户表获取姓名为“魏国兰”的客户编号
  • 主查询:将子查询所获取的客户编号作为主查询的条件比较值,从订单表获取满足条件的订单编号和订单日期

实现代码:

使用子查询统计“国内长线游”线路数、线路最高价格和线路最低价格

SELECT COUNT(lineID) 线路数,MAX(price) 线路最高价(元),MIN(price) 线路最低价(元)
FROM line WHERE lineTypeID=(SELECT lineTypeID FROM linetype WHERE typeName='国内长线游');

使用子查询获得指定客户(如“魏国兰”)订购线路的相关信息,要求显示订单名和订单日期

SELECT ordersID 订单编号 ,ordersDate 订单日期 FROM orders WHERE customerID=
(SELECT customerID FROM customer WHERE NAME='魏国兰');

数据库:

/*
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 ;

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接: https://blog.csdn.net/weixin_44893902/article/details/111097337

php基础开发(集成环境搭建)-爱代码爱编程

php基础开发(集成环境搭建) 1、下载软件 2、安装软件并配置环境 3、验证   1、下载软件 下载地址 链接:https://pan.baidu.com/s/14rAsOm1g2WUUVaQ7E7VOuQ  提取码:wamp  或者搜索wamp自行下载进行安装。 2、安装软件并配置 安装: 选中软件,双击安装默认下一步即

附录三:PHP与Mysql之间的纠缠(超详细)-爱代码爱编程

文章目录 第一章 PHP操作mysql数据库index.html代码connect.php代码如下:list.php代码如下:第二章 PHP 会话管理和控制一、php 会话控制 之 PHP中的Cookie二、php 会话控制 之 PHP中的session1.开启session2.添加session数据3.读取session数据4.销毁session

jsp、servlet、jdbc实现留言板(可记住密码)-爱代码爱编程

文章目录 一、目标二、JDBC使用说明三、代码总结 使用工具:mysql、IDEA 一、目标 对留言板进行优化: 登陆页面输入用户名、密码,提交给某个servlet,该servlet可以检索数据库,验证用户名和密码是否合法,给出验证结果二、JDBC使用说明 JDBC基本功能 建立连接、发送SQL语句,处理数据库操作结果 mysql

2020-12-13-爱代码爱编程

文章目录 今日内容数据库的基本概念MySQL数据库软件SQLDDL:操作数据库、表DML:增删改表中数据DQL:查询表中的记录 今日内容 数据库的基本概念 MySQL数据库软件 安装卸载配置 SQL 数据库的基本概念 数据库的英文单词: DataBase 简称 : DB 什么数据库? 用于存储和管理数据的仓库。 数据库的

数据库设计规范与设计工具-爱代码爱编程

数据库的设计要遵守三范式来设计,这样的设计的好处是可以消除数据冗余,缺点是增加了sql语句的难度。因此,我们在设计表结构的时候不一定要完全遵守三范式,如果遇到复杂问题,可以尝试着打破三范式找到解决问题或优化性能的方法。 一、三范式 第一范式 表中的字段要保持原子性,即字段不可再分 eg 学生信息表中的联系方式有两种——邮箱和电话

使用子查询统计每个客户预订线路的数量,要求按照预订线路数升序显示客户姓名和预订线路数-爱代码爱编程

查看本章节 查看作业目录 需求说明: 使用子查询统计每个客户预订线路的数量,要求按照预订线路数升序显示客户姓名和预订线路数在国外旅游淡季时节,所有“出境游”线路的价格下调 10%实现思路:  需求说明(1)的解决思路 在订单客户表中,根据出行客户编号分组获取每一个客户的预订线路数,查询结果作为 from 子句的虚拟表 A连接客户表和虚拟表 A,连

JavaEE之Mybatis关系映射-爱代码爱编程

JavaEE之Mybatis关系映射 创建封装工具类一对一(旅客与护照)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试一对多(部门与职员)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试多对多(学生与科目)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper

MySQL高级查询与编程笔记 • 【第3章 子查询】-爱代码爱编程

全部章节   >>>> 本章目录 3.1 子查询定义和单行子查询 3.1.1 子查询定义 3.1.2 单行子查询应用 3.1.4 实践练习 3.2 多行子查询应用 3.2.1 in 比较符 3.2.3 any|some 关键字子查询 3.2.4 实践练习 3.3 子查询特殊应用 3.3.1 from 子句中的子

SQL优化(二)-爱代码爱编程

order by排序方式 一般分两种,在索引中排序(索引里面数据有序),在内存中排序(内存不够的话会产生临时文件辅助排序)。其中走索引的排序会快很多。 索引排序 既然我们知道排序走索引会快很多,那我们排序时应该尽量让排序走索引。那什么情况下排序会走索引呢?我们知道查询排序语句一般由这几个部分构成:select +where+order by+limi

数据库设计规范与设计工具-爱代码爱编程

数据库的设计要遵守三范式来设计,这样的设计的好处是可以消除数据冗余,缺点是增加了sql语句的难度。因此,我们在设计表结构的时候不一定要完全遵守三范式,如果遇到复杂问题,可以尝试着打破三范式找到解决问题或优化性能的方法。 一、三范式 第一范式 表中的字段要保持原子性,即字段不可再分 eg 学生信息表中的联系方式有两种——邮箱和电话

使用子查询获取,使用 all 关键字获取比所有“国内短线游”价格高的线路信息,按照线路类型、线路价格升序显示线路编号、线路名和价格-爱代码爱编程

查看本章节 查看作业目录 需求说明: 使用子查询获取“国内短线游”及“国内长线游”的线路信息,按照线路类型、线路价格升序显示线路编号、线路名和价格 使用 all 关键字获取比所有“国内短线游”价格高的线路信息,按照线路类型、线路价格升序显示线路编号、线路名和价格 实现思路: 需求说明(1)的解决思路 多行子查询:从线路类型表获取类型名为“国内

使用子查询统计每个客户预订线路的数量,要求按照预订线路数升序显示客户姓名和预订线路数-爱代码爱编程

查看本章节 查看作业目录 需求说明: 使用子查询统计每个客户预订线路的数量,要求按照预订线路数升序显示客户姓名和预订线路数在国外旅游淡季时节,所有“出境游”线路的价格下调 10%实现思路:  需求说明(1)的解决思路 在订单客户表中,根据出行客户编号分组获取每一个客户的预订线路数,查询结果作为 from 子句的虚拟表 A连接客户表和虚拟表 A,连