【正文】 22:20:3139。, 39。43102300439。, 39。湘 L546SB39。, 39。480000039。)。INSERT INTO `rent` VALUES (39。140539。, 39。20220601 11:40:2339。, 39。20220701 06:31:2639。, 39。43102300139。, 39。湘 L520SB39。, 39。400000039。)。INSERT INTO `rent` VALUES (39。140639。, 39。20220503 22:06:2539。, 39。20220519 22:06:2939。, 39。43102300239。, 39。湘 L124SB39。, 39。410000039。)。 Table structure for `worker1` DROP TABLE IF EXISTS `worker1`。CREATE TABLE `worker1` (37 / 44 `WorkNo` varchar(10) NOT NULL DEFAULT 39。39。, `WorkPassword` varchar(10) DEFAULT NULL, `WorkName` varchar(10) DEFAULT NULL, `WorkSex` varchar(10) DEFAULT NULL, `WorkPhone` varchar(10) DEFAULT NULL, PRIMARY KEY (`WorkNo`)) ENGINE=InnoDB DEFAULT CHARSET=utf8。 Records of worker1 INSERT INTO `worker1` VALUES (39。10139。, 39。00000139。, 39。張三39。, 39。女39。, 39。15512339。)。INSERT INTO `worker1` VALUES (39。10239。, 39。00000239。, 39。李四39。, 39。男39。, 39。15513539。)。INSERT INTO `worker1` VALUES (39。10339。, 39。00000339。, 39。王五39。, 39。男39。, 39。15513839。)。 View structure for `v1` DROP VIEW IF EXISTS `v1`。CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `carclass`.`CarClassNo` AS `CarClassNo`,`carclass`.`CarClassCnt` AS `CarClassCnt`,`carclass`.`CarClassBrand` AS `CarClassBrand`,`rent`.`RecordCost` AS `RecordCost` from ((`carclass` join `car`) join `rent`) where ((`carclass`.`CarClassNo` = `car`.`CarClassNo`) and (`rent`.`CarNo` = `car`.`CarNo`)) group by `carclass`.`CarClassNo` 。 View structure for `v2` 38 / 44DROP VIEW IF EXISTS `v2`。CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `rent`.`RecordNo` AS `RecordNo`,`car`.`CarClassNo` AS `CarClassNo`,`carclass`.`CarClassBrand` AS `CarClassBrand` from ((`rent` join `car`) join `carclass`) where ((`rent`.`CarNo` = `car`.`CarNo`) and (`car`.`CarClassNo` = `carclass`.`CarClassNo`)) group by `car`.`CarClassNo` 。 View structure for `v3` DROP VIEW IF EXISTS `v3`。CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `customer`.`CusAge` AS `CusAge`,`carclass`.`CarClassBrand` AS `CarClassBrand`,count(`customer`.`CusAge`) AS `Count()` from (((`carclass` join `customer`) join `rent`) join `car`) where ((`customer`.`CusNo` = `rent`.`CusNo`) and (`rent`.`CarNo` = `car`.`CarNo`) and (`car`.`CarClassNo` = `carclass`.`CarClassNo`)) group by `carclass`.`CarClassBrand` 。 View structure for `v4` DROP VIEW IF EXISTS `v4`。CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `car`.`CarState` AS `CarState`,`car`.`CarNo` AS `CarNo`,`carclass`.`CarClassBrand` AS `CarClassBrand` from (`car` join `carclass`) where (`car`.`CarClassNo` = `carclass`.`CarClassNo`) 。 39 / 44 Procedure structure for `P1` DROP PROCEDURE IF EXISTS `P1`。DELIMITER 。CREATE DEFINER=`root`@`localhost` PROCEDURE `P1`()begin /*每輛車(chē)的狀態(tài)和庫(kù)存數(shù)*/select CarNo,CarState,CarClassBrand,CarClassCntfrom car,carclasswhere =。end。DELIMITER 。 Procedure structure for `P2` DROP PROCEDURE IF EXISTS `P2`。DELIMITER 。CREATE DEFINER=`root`@`localhost` PROCEDURE `P2`(newCusNo varchar(10))beginselect RecordNo,RecordRentDate,RecordReturnDate,RecordCost,CarNo from rentwhere CusNo=newCusNo。end。DELIMITER 。 Procedure structure for `P3` 40 / 44DROP PROCEDURE IF EXISTS `P3`。DELIMITER 。CREATE DEFINER=`root`@`localhost` PROCEDURE `P3`(newCarClassBrand varchar(10))beginselect CarClassCnt,CarStatefrom carclass,carwhere CarClassBrand=newCarClassBrand。end。DELIMITER 。 Procedure structure for `P4` DROP PROCEDURE IF EXISTS `P4`。DELIMITER 。CREATE DEFINER=`root`@`localhost` PROCEDURE `P4`(`CarClassBrand` varchar(10))BEGINSELECT CarClassCnt,CarStateFROM car,carclassWHERE = and =。END。DELIMITER 。DROP TRIGGER IF EXISTS `T1`。DELIMITER 。CREATE TRIGGER `T1` AFTER INSERT ON `car` FOR EACH ROW begindeclare c int。41 / 44set c=( select CarClassCnt from carclass where CarClassNo=)。 update carclass set CarClassCnt=c+1 where CarClassNo=。end。DELIMITER 。DROP TRIGGER IF EXISTS `T2`。DELIMITER 。CREATE TRIGGER `T2` AFTER DELETE ON `car` FOR EACH ROW begindeclare c int。set c=( select CarClassCnt from carclass where CarClassNo=)。 update carclass set CarClassCnt=c1 where CarClassNo=。end。DELIMITER 。DROP TRIGGER IF EXISTS `T3`。DELIMITER 。CREATE TRIGGER `T3` AFTER INSERT ON `rent` FOR EACH ROW update carclassset CarClassCnt=39。0139。where CarClassNo in(select CarClassNo from carwhere CarNo in 42 / 44(select CarNofrom rent where CarNo =))。DELIMITER 。DROP TRIGGER IF EXISTS `T5`。DELIMITER 。CREATE TRIGGER `T5` AFTER UPDATE ON `rent` FOR EACH ROW update carset CarState=CarState+1where CarNo in(select CarNofrom rent where CarNo =)。DELIMITER 。DROP TRIGGER IF EXISTS `T4`。DELIMITER 。CREATE TRIGGER `T4` BEFORE DELETE ON `rent` FOR EACH ROW update carclassset CarClassCnt=CarClassCnt+1where CarClassNo in(select CarClassNo from carwhere CarNo in (select CarNofrom rent where CarNo =))。DELIMITER 。43 / 44 連接數(shù)據(jù)庫(kù)源代碼package ggcc。import .*。public class Test {public static Connection conn=null。public static Connection dbConion() throws ClassNotFoundException, SQLException {if(conn == null) { ()。 String url=jdbc:String user=root。String password=123456。conn=(url,user,password)。}return conn。}public static void main(String[] args){try {()。 Statement stmt = ()。 String sql = select * from car。 ResultSet rs =(sql)。 while(()) { String CarNo=(1)。 String CarColor=(2)。 String CarState=(5)。 (CarNo:+CarNo)。 (CarColor:+CarColor)。 (CarState:+CarState)。 }} catch (ClassNotFoundException e) {()。} catch (SQLException e) {()。} 44 / 44}