-- MySQL dump 10.13 Distrib 8.0.19, for Win64 (x86_64) -- -- Host: localhost Database: DrivingSchool -- ------------------------------------------------------ -- Server version 8.0.40 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `account` -- DROP TABLE IF EXISTS `account`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `account` ( `account_id` int NOT NULL AUTO_INCREMENT, `type` varchar(50) DEFAULT NULL, `status` varchar(50) DEFAULT NULL, `created_by` varchar(255) DEFAULT NULL, PRIMARY KEY (`account_id`), KEY `created_by` (`created_by`), CONSTRAINT `account_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `admin` (`admin_name`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `account` -- LOCK TABLES `account` WRITE; /*!40000 ALTER TABLE `account` DISABLE KEYS */; INSERT INTO `account` VALUES (1,'管理员','活跃','张伟'),(2,'学员','活跃','张伟'),(3,'教练','活跃','李娜'),(4,'学员','停用','李娜'),(5,'管理员','活跃','李娜'),(6,'学员','活跃','张伟'),(7,'教练','活跃','张伟'),(8,'学员','活跃','李娜'),(9,'教练','停用','张伟'),(10,'学员','活跃','李娜'); /*!40000 ALTER TABLE `account` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `admin` -- DROP TABLE IF EXISTS `admin`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `admin` ( `admin_name` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, PRIMARY KEY (`admin_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `admin` -- LOCK TABLES `admin` WRITE; /*!40000 ALTER TABLE `admin` DISABLE KEYS */; INSERT INTO `admin` VALUES ('张伟','password123'),('李娜','securepass'); /*!40000 ALTER TABLE `admin` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `appointment` -- DROP TABLE IF EXISTS `appointment`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `appointment` ( `appointment_id` int NOT NULL AUTO_INCREMENT, `student_id` varchar(255) NOT NULL, `coach_id` varchar(255) NOT NULL, `venue_id` int NOT NULL, `period` varchar(50) NOT NULL, `status` varchar(50) NOT NULL, `feedback_id` int DEFAULT NULL, PRIMARY KEY (`appointment_id`), KEY `feedback_id` (`feedback_id`), KEY `idx_appointment_student` (`student_id`), KEY `idx_appointment_coach` (`coach_id`), KEY `idx_appointment_venue` (`venue_id`), KEY `idx_appointment_period` (`period`), KEY `idx_appointment_status` (`status`), CONSTRAINT `appointment_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`), CONSTRAINT `appointment_ibfk_2` FOREIGN KEY (`coach_id`) REFERENCES `coach` (`coach_id`), CONSTRAINT `appointment_ibfk_3` FOREIGN KEY (`venue_id`) REFERENCES `venue` (`venue_id`), CONSTRAINT `appointment_ibfk_4` FOREIGN KEY (`feedback_id`) REFERENCES `feedback` (`feedback_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `appointment` -- LOCK TABLES `appointment` WRITE; /*!40000 ALTER TABLE `appointment` DISABLE KEYS */; INSERT INTO `appointment` VALUES (1,'S001','C001',1,'上午','已确认',NULL),(2,'S002','C002',2,'下午','待确认',NULL),(3,'S003','C003',3,'上午','已取消',NULL),(4,'S004','C004',4,'下午','已确认',NULL),(5,'S005','C005',5,'上午','待确认',NULL),(6,'S006','C006',6,'下午','已确认',NULL),(7,'S007','C007',7,'上午','已确认',NULL),(8,'S008','C008',8,'下午','待确认',NULL),(9,'S009','C009',9,'上午','已取消',NULL),(10,'S010','C010',10,'下午','已确认',NULL); /*!40000 ALTER TABLE `appointment` ENABLE KEYS */; UNLOCK TABLES; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_appointment_update` AFTER UPDATE ON `appointment` FOR EACH ROW BEGIN IF OLD.status <> NEW.status THEN INSERT INTO log (table_name, record_id, action, details) VALUES ('appointment', NEW.appointment_id, 'update', CONCAT('Status changed from ', OLD.status, ' to ', NEW.status)); END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Temporary view structure for view `appointment_details` -- DROP TABLE IF EXISTS `appointment_details`; /*!50001 DROP VIEW IF EXISTS `appointment_details`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `appointment_details` AS SELECT 1 AS `appointment_id`, 1 AS `student_name`, 1 AS `coach_name`, 1 AS `venue_name`, 1 AS `period`, 1 AS `status`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `booking_slot` -- DROP TABLE IF EXISTS `booking_slot`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `booking_slot` ( `slot_id` int NOT NULL AUTO_INCREMENT, `start_time` time NOT NULL, `end_time` time NOT NULL, `venue_id` int NOT NULL, PRIMARY KEY (`slot_id`), KEY `venue_id` (`venue_id`), CONSTRAINT `booking_slot_ibfk_1` FOREIGN KEY (`venue_id`) REFERENCES `venue` (`venue_id`), CONSTRAINT `booking_slot_chk_1` CHECK ((`end_time` > `start_time`)) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `booking_slot` -- LOCK TABLES `booking_slot` WRITE; /*!40000 ALTER TABLE `booking_slot` DISABLE KEYS */; INSERT INTO `booking_slot` VALUES (1,'09:00:00','10:00:00',1),(2,'10:00:00','11:00:00',1),(3,'11:00:00','12:00:00',1),(4,'14:00:00','15:00:00',2),(5,'15:00:00','16:00:00',2),(6,'16:00:00','17:00:00',2),(7,'09:00:00','10:00:00',3),(8,'10:00:00','11:00:00',3),(9,'11:00:00','12:00:00',3),(10,'14:00:00','15:00:00',3); /*!40000 ALTER TABLE `booking_slot` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `coach` -- DROP TABLE IF EXISTS `coach`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `coach` ( `coach_id` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `personal_info_id` int DEFAULT NULL, PRIMARY KEY (`coach_id`), KEY `personal_info_id` (`personal_info_id`), KEY `idx_coach_name` (`name`), CONSTRAINT `coach_ibfk_1` FOREIGN KEY (`personal_info_id`) REFERENCES `personal_info` (`info_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `coach` -- LOCK TABLES `coach` WRITE; /*!40000 ALTER TABLE `coach` DISABLE KEYS */; INSERT INTO `coach` VALUES ('C001','王五','coachpass1',3),('C002','赵六','coachpass2',4),('C003','陈翔宇','coachpass3',7),('C004','赵嘉琪','coachpass4',9),('C005','黄涛','coachpass5',NULL),('C006','钱芳','coachpass6',NULL),('C007','朱刚','coachpass7',NULL),('C008','孙莉','coachpass8',NULL),('C009','郑凯','coachpass9',NULL),('C010','林敏','coachpass10',NULL); /*!40000 ALTER TABLE `coach` ENABLE KEYS */; UNLOCK TABLES; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_coach_update` AFTER UPDATE ON `coach` FOR EACH ROW BEGIN IF OLD.name <> NEW.name OR OLD.password <> NEW.password THEN INSERT INTO log (table_name, record_id, action, details) VALUES ('coach', NEW.coach_id, 'update', CONCAT('Name or password updated')); END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Temporary view structure for view `coach_details` -- DROP TABLE IF EXISTS `coach_details`; /*!50001 DROP VIEW IF EXISTS `coach_details`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `coach_details` AS SELECT 1 AS `coach_id`, 1 AS `coach_name`, 1 AS `phone`, 1 AS `email`, 1 AS `status`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `feedback` -- DROP TABLE IF EXISTS `feedback`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `feedback` ( `feedback_id` int NOT NULL AUTO_INCREMENT, `content` text NOT NULL, `date` date NOT NULL, PRIMARY KEY (`feedback_id`), KEY `idx_feedback_date` (`date`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `feedback` -- LOCK TABLES `feedback` WRITE; /*!40000 ALTER TABLE `feedback` DISABLE KEYS */; INSERT INTO `feedback` VALUES (1,'教练非常耐心，教学很好！','2023-10-01'),(2,'场地设施齐全，体验不错。','2023-10-02'),(3,'课程安排合理，推荐这里学车。','2023-10-03'),(4,'教练态度好，进步很快。','2023-10-04'),(5,'学习氛围浓厚，值得信赖。','2023-10-05'),(6,'教练很专业，感谢指导。','2023-10-06'),(7,'场地干净整洁，感觉舒适。','2023-10-07'),(8,'课程内容丰富，收获颇丰。','2023-10-08'),(9,'教练很有经验，帮助很大。','2023-10-09'),(10,'场地条件优越，性价比高。','2023-10-10'); /*!40000 ALTER TABLE `feedback` ENABLE KEYS */; UNLOCK TABLES; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `before_feedback_delete` BEFORE DELETE ON `feedback` FOR EACH ROW BEGIN DECLARE expiry_date DATE; SET expiry_date = CURDATE() - INTERVAL 1 YEAR; -- 假设过期时间为一年前 IF OLD.date < expiry_date THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete expired feedback'; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Table structure for table `invoice` -- DROP TABLE IF EXISTS `invoice`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `invoice` ( `invoice_id` int NOT NULL AUTO_INCREMENT, `amount` decimal(10,2) NOT NULL, `date` date NOT NULL, `appointment_id` int DEFAULT NULL, PRIMARY KEY (`invoice_id`), KEY `appointment_id` (`appointment_id`), KEY `idx_invoice_amount` (`amount`), KEY `idx_invoice_date` (`date`), CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`appointment_id`) REFERENCES `appointment` (`appointment_id`), CONSTRAINT `invoice_chk_1` CHECK ((`amount` >= 0)) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `invoice` -- LOCK TABLES `invoice` WRITE; /*!40000 ALTER TABLE `invoice` DISABLE KEYS */; INSERT INTO `invoice` VALUES (1,1500.00,'2023-10-01',1),(2,1800.00,'2023-10-02',2),(3,2000.00,'2023-10-03',3),(4,1700.00,'2023-10-04',4),(5,1600.00,'2023-10-05',5),(6,1900.00,'2023-10-06',6),(7,1400.00,'2023-10-07',7),(8,1550.00,'2023-10-08',8),(9,1650.00,'2023-10-09',9),(10,1750.00,'2023-10-10',10); /*!40000 ALTER TABLE `invoice` ENABLE KEYS */; UNLOCK TABLES; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_invoice_insert` AFTER INSERT ON `invoice` FOR EACH ROW BEGIN UPDATE account SET status = '已支付' WHERE account_id = ( SELECT student_id FROM appointment WHERE appointment_id = NEW.appointment_id ); END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Temporary view structure for view `invoice_details` -- DROP TABLE IF EXISTS `invoice_details`; /*!50001 DROP VIEW IF EXISTS `invoice_details`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `invoice_details` AS SELECT 1 AS `invoice_id`, 1 AS `amount`, 1 AS `invoice_date`, 1 AS `student_name`, 1 AS `coach_name`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `log` -- DROP TABLE IF EXISTS `log`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `log` ( `log_id` int NOT NULL AUTO_INCREMENT, `table_name` varchar(50) DEFAULT NULL, `record_id` int DEFAULT NULL, `action` varchar(50) DEFAULT NULL, `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `details` text, PRIMARY KEY (`log_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `log` -- LOCK TABLES `log` WRITE; /*!40000 ALTER TABLE `log` DISABLE KEYS */; /*!40000 ALTER TABLE `log` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `personal_info` -- DROP TABLE IF EXISTS `personal_info`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `personal_info` ( `info_id` int NOT NULL AUTO_INCREMENT, `student_id` varchar(255) NOT NULL, `phone` varchar(20) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`info_id`), UNIQUE KEY `student_id` (`student_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `personal_info` -- LOCK TABLES `personal_info` WRITE; /*!40000 ALTER TABLE `personal_info` DISABLE KEYS */; INSERT INTO `personal_info` VALUES (1,'S001','13800138000','zhangsan@example.com'),(2,'S002','13900139000','lisi@example.com'),(3,'C001','13700137000','wangwu@example.com'),(4,'C002','13600136000','zhaoliu@example.com'),(5,'S003','13500135000','sunbaohua@example.com'),(6,'S004','13400134000','liwenbo@example.com'),(7,'C003','13300133000','chenxiangyu@example.com'),(8,'S005','13200132000','yangming@example.com'),(9,'C004','13100131000','zhaojiaqi@example.com'),(10,'S006','13000130000','wulifei@example.com'); /*!40000 ALTER TABLE `personal_info` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `student` -- DROP TABLE IF EXISTS `student`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `student` ( `student_id` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `personal_info_id` int DEFAULT NULL, PRIMARY KEY (`student_id`), KEY `personal_info_id` (`personal_info_id`), KEY `idx_student_name` (`name`), CONSTRAINT `student_ibfk_1` FOREIGN KEY (`personal_info_id`) REFERENCES `personal_info` (`info_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `student` -- LOCK TABLES `student` WRITE; /*!40000 ALTER TABLE `student` DISABLE KEYS */; INSERT INTO `student` VALUES ('S001','张三','studypass1',1),('S002','李四','studypass2',2),('S003','孙宝华','studypass3',5),('S004','李文博','studypass4',6),('S005','杨明','studypass5',8),('S006','吴雷飞','studypass6',10),('S007','刘强','studypass7',NULL),('S008','王丽','studypass8',NULL),('S009','陈杰','studypass9',NULL),('S010','周梅','studypass10',NULL); /*!40000 ALTER TABLE `student` ENABLE KEYS */; UNLOCK TABLES; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_student_insert` AFTER INSERT ON `student` FOR EACH ROW BEGIN INSERT INTO account (account_id, type, status, created_by) VALUES (NEW.student_id, '学员', '活跃', '张伟'); END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Temporary view structure for view `student_appointment_feedback` -- DROP TABLE IF EXISTS `student_appointment_feedback`; /*!50001 DROP VIEW IF EXISTS `student_appointment_feedback`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `student_appointment_feedback` AS SELECT 1 AS `student_name`, 1 AS `coach_name`, 1 AS `appointment_period`, 1 AS `appointment_status`, 1 AS `feedback_content`, 1 AS `feedback_date`*/; SET character_set_client = @saved_cs_client; -- -- Temporary view structure for view `student_details` -- DROP TABLE IF EXISTS `student_details`; /*!50001 DROP VIEW IF EXISTS `student_details`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `student_details` AS SELECT 1 AS `student_id`, 1 AS `student_name`, 1 AS `phone`, 1 AS `email`, 1 AS `status`*/; SET character_set_client = @saved_cs_client; -- -- Table structure for table `training_record` -- DROP TABLE IF EXISTS `training_record`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `training_record` ( `record_id` int NOT NULL AUTO_INCREMENT, `student_id` varchar(255) NOT NULL, `coach_id` varchar(255) NOT NULL, `feedback` text, `appointment_id` int DEFAULT NULL, PRIMARY KEY (`record_id`), KEY `student_id` (`student_id`), KEY `coach_id` (`coach_id`), KEY `appointment_id` (`appointment_id`), CONSTRAINT `training_record_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`), CONSTRAINT `training_record_ibfk_2` FOREIGN KEY (`coach_id`) REFERENCES `coach` (`coach_id`), CONSTRAINT `training_record_ibfk_3` FOREIGN KEY (`appointment_id`) REFERENCES `appointment` (`appointment_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `training_record` -- LOCK TABLES `training_record` WRITE; /*!40000 ALTER TABLE `training_record` DISABLE KEYS */; INSERT INTO `training_record` VALUES (1,'S001','C001','很好的一次训练！',1),(2,'S002','C002','还需要多加练习。',2),(3,'S003','C003','继续保持进步。',3),(4,'S004','C004','表现不错，加油！',4),(5,'S005','C005','需要改进的地方很多。',5),(6,'S006','C006','进步明显，继续努力。',6),(7,'S007','C007','基础还行，还需加强。',7),(8,'S008','C008','整体不错，细节需注意。',8),(9,'S009','C009','还有提升空间。',9),(10,'S010','C010','做得很好，保持下去。',10); /*!40000 ALTER TABLE `training_record` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `venue` -- DROP TABLE IF EXISTS `venue`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `venue` ( `venue_id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `capacity` int NOT NULL, PRIMARY KEY (`venue_id`), KEY `idx_venue_name` (`name`), CONSTRAINT `venue_chk_1` CHECK ((`capacity` > 0)) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `venue` -- LOCK TABLES `venue` WRITE; /*!40000 ALTER TABLE `venue` DISABLE KEYS */; INSERT INTO `venue` VALUES (1,'训练场A',50),(2,'训练场B',30),(3,'理论教室',40),(4,'模拟驾驶室',20),(5,'休息区',60),(6,'户外练习场',70),(7,'室内练习场',55),(8,'考试中心',25),(9,'培训办公室',10),(10,'接待厅',35); /*!40000 ALTER TABLE `venue` ENABLE KEYS */; UNLOCK TABLES; -- -- Dumping routines for database 'DrivingSchool' -- /*!50003 DROP PROCEDURE IF EXISTS `AddNewStudent` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `AddNewStudent`( IN p_student_id VARCHAR(255), IN p_name VARCHAR(255), IN p_password VARCHAR(255), IN p_phone VARCHAR(20), IN p_email VARCHAR(255) ) BEGIN DECLARE v_info_id INT; -- 插入个人基本信息 INSERT INTO personal_info (phone, email) VALUES (p_phone, p_email); SET v_info_id = LAST_INSERT_ID(); -- 插入学生信息 INSERT INTO student (student_id, name, password, personal_info_id) VALUES (p_student_id, p_name, p_password, v_info_id); -- 插入账户信息 INSERT INTO account (account_id, type, status, created_by) VALUES (p_student_id, '学员', '活跃', '张伟'); END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `CalculateCoachTotalIncome` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateCoachTotalIncome`( IN p_coach_id VARCHAR(255) ) BEGIN SELECT c.coach_id, c.name AS coach_name, SUM(i.amount) AS total_income FROM invoice i JOIN appointment ap ON i.appointment_id = ap.appointment_id JOIN coach c ON ap.coach_id = c.coach_id WHERE c.coach_id = p_coach_id GROUP BY c.coach_id, c.name; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `DeleteExpiredInvoices` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteExpiredInvoices`( IN p_expiry_date DATE ) BEGIN DELETE FROM invoice WHERE date < p_expiry_date; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `GenerateStudentFeedbackReport` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `GenerateStudentFeedbackReport`( IN p_student_id VARCHAR(255) ) BEGIN SELECT f.feedback_id, f.content AS feedback_content, f.date AS feedback_date FROM feedback f JOIN training_record tr ON f.feedback_id = tr.feedback_id WHERE tr.student_id = p_student_id; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `UpdateAppointmentStatus` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateAppointmentStatus`( IN p_appointment_id INT, IN p_new_status VARCHAR(50) ) BEGIN UPDATE appointment SET status = p_new_status WHERE appointment_id = p_appointment_id; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Final view structure for view `appointment_details` -- /*!50001 DROP VIEW IF EXISTS `appointment_details`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `appointment_details` AS select `ap`.`appointment_id` AS `appointment_id`,`st`.`name` AS `student_name`,`co`.`name` AS `coach_name`,`v`.`name` AS `venue_name`,`ap`.`period` AS `period`,`ap`.`status` AS `status` from (((`appointment` `ap` join `student` `st` on((`ap`.`student_id` = `st`.`student_id`))) join `coach` `co` on((`ap`.`coach_id` = `co`.`coach_id`))) join `venue` `v` on((`ap`.`venue_id` = `v`.`venue_id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `coach_details` -- /*!50001 DROP VIEW IF EXISTS `coach_details`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `coach_details` AS select `c`.`coach_id` AS `coach_id`,`c`.`name` AS `coach_name`,`pi`.`phone` AS `phone`,`pi`.`email` AS `email`,`a`.`status` AS `status` from ((`coach` `c` join `personal_info` `pi` on((`c`.`personal_info_id` = `pi`.`info_id`))) join `account` `a` on((`c`.`coach_id` = `a`.`account_id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `invoice_details` -- /*!50001 DROP VIEW IF EXISTS `invoice_details`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `invoice_details` AS select `i`.`invoice_id` AS `invoice_id`,`i`.`amount` AS `amount`,`i`.`date` AS `invoice_date`,`st`.`name` AS `student_name`,`co`.`name` AS `coach_name` from (((`invoice` `i` join `appointment` `ap` on((`i`.`appointment_id` = `ap`.`appointment_id`))) join `student` `st` on((`ap`.`student_id` = `st`.`student_id`))) join `coach` `co` on((`ap`.`coach_id` = `co`.`coach_id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `student_appointment_feedback` -- /*!50001 DROP VIEW IF EXISTS `student_appointment_feedback`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `student_appointment_feedback` AS select `st`.`name` AS `student_name`,`co`.`name` AS `coach_name`,`ap`.`period` AS `appointment_period`,`ap`.`status` AS `appointment_status`,`f`.`content` AS `feedback_content`,`f`.`date` AS `feedback_date` from (((`appointment` `ap` join `student` `st` on((`ap`.`student_id` = `st`.`student_id`))) join `coach` `co` on((`ap`.`coach_id` = `co`.`coach_id`))) left join `feedback` `f` on((`ap`.`feedback_id` = `f`.`feedback_id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `student_details` -- /*!50001 DROP VIEW IF EXISTS `student_details`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `student_details` AS select `s`.`student_id` AS `student_id`,`s`.`name` AS `student_name`,`pi`.`phone` AS `phone`,`pi`.`email` AS `email`,`a`.`status` AS `status` from ((`student` `s` join `personal_info` `pi` on((`s`.`personal_info_id` = `pi`.`info_id`))) join `account` `a` on((`s`.`student_id` = `a`.`account_id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-12-25 14:58:02