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
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）
总结
虽然我个人也经常自嘲，十年之后要去成为外卖专员，但实际上依靠自身的努力，是能够减少三十五岁之后的焦虑的，毕竟好的架构师并不多。
架构师，是我们大部分技术人的职业目标，一名好的架构师来源于机遇(公司)、个人努力(吃得苦、肯钻研)、天分(真的热爱)的三者协作的结果，实践+机遇+努力才能助你成为优秀的架构师。
如果你也想成为一名好的架构师，那或许这份Java成长笔记你需要阅读阅读，希望能够对你的职业发展有所帮助。
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）
总结
虽然我个人也经常自嘲，十年之后要去成为外卖专员，但实际上依靠自身的努力，是能够减少三十五岁之后的焦虑的，毕竟好的架构师并不多。
架构师，是我们大部分技术人的职业目标，一名好的架构师来源于机遇(公司)、个人努力(吃得苦、肯钻研)、天分(真的热爱)的三者协作的结果，实践+机遇+努力才能助你成为优秀的架构师。
如果你也想成为一名好的架构师，那或许这份Java成长笔记你需要阅读阅读，希望能够对你的职业发展有所帮助。
[外链图片转存中…(img-5wo7Nk26-1713522084942)]
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
192
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
