You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
802 lines
37 KiB
802 lines
37 KiB
-- 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
|