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

于 2024-05-12 16:17:21 发布
阅读量701 收藏 19
点赞数 28
分类专栏： 程序员 文章标签： java 面试 学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2401_84002594/article/details/138758506
版权

总结

面试前的“练手”还是很重要的，所以开始面试之前一定要准备好啊，不然也是耽搁面试官和自己的时间。

我自己是刷了不少面试题的，所以在面试过程中才能够做到心中有数，基本上会清楚面试过程中会问到哪些知识点，高频题又有哪些，所以刷题是面试前期准备过程中非常重要的一点。

面试题及解析总结

三年Java开发，刚从美团、京东、阿里面试归来，分享个人面经

大厂面试场景

三年Java开发，刚从美团、京东、阿里面试归来，分享个人面经

知识点总结

三年Java开发，刚从美团、京东、阿里面试归来，分享个人面经

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

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

最后

这份清华大牛整理的进大厂必备的redis视频、面试题和技术文档

祝大家早日进入大厂，拿到满意的薪资和职级~~~加油！！

感谢大家的支持！！

image.png

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

CREATE DEFINER=root@127.0.0.1 PROCEDURE proc_LineNumsRate(

_typeName varchar(20),

out numsRate decimal(10,3)

)

READS SQL DATA

最后

这份清华大牛整理的进大厂必备的redis视频、面试题和技术文档

祝大家早日进入大厂，拿到满意的薪资和职级~~~加油！！

感谢大家的支持！！

[外链图片转存中…(img-cbGzniFk-1715501818642)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

2401_84002594
关注
  • 28
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值