/* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 50624 Source Host : localhost:3306 Source Database : timetable Target Server Type : MYSQL Target Server Version : 50624 File Encoding : 65001 Date: 2022-12-27 18:03:22 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for attendclass -- ---------------------------- DROP TABLE IF EXISTS `attendclass`; CREATE TABLE `attendclass` ( `attendclass_id` int(11) NOT NULL AUTO_INCREMENT, `attendclass_date` varchar(100) DEFAULT NULL, `attendclass_day` varchar(100) DEFAULT NULL, `attendclass_num` int(11) DEFAULT NULL, `attendclass_start` varchar(100) DEFAULT NULL, `attendclass_end` varchar(100) DEFAULT NULL, `teacher_id` int(11) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, `course_id` int(11) DEFAULT NULL, `classroom_id` int(11) DEFAULT NULL, PRIMARY KEY (`attendclass_id`), KEY `fk_atttendclasswithclass` (`class_id`), KEY `fk_atttendclasswithteacher` (`teacher_id`), KEY `fk_atttendclasswithcourse` (`course_id`), KEY `fk_atttendclasswithclassroom` (`classroom_id`), CONSTRAINT `fk_atttendclasswithclass` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_atttendclasswithclassroom` FOREIGN KEY (`classroom_id`) REFERENCES `classroom` (`classroom_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_atttendclasswithcourse` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_atttendclasswithteacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`teacher_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Records of attendclass -- ---------------------------- INSERT INTO `attendclass` VALUES ('1', '2022-12-19', '1', '1', '8:00', '9:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('2', '2022-12-19', '1', '2', '9:00', '10:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('3', '2022-12-19', '1', '3', '10:00', '11:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('4', '2022-12-19', '1', '4', '11:00', '12:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('5', '2022-12-19', '1', '5', '14:00', '15:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('6', '2022-12-19', '1', '6', '15:00', '16:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('7', '2022-12-20', '2', '1', '8:00', '9:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('8', '2022-12-20', '2', '2', '9:00', '10:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('9', '2022-12-20', '2', '3', '10:00', '11:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('10', '2022-12-20', '2', '4', '11:00', '12:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('11', '2022-12-20', '2', '5', '14:00', '15:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('12', '2022-12-20', '2', '6', '15:00', '16:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('13', '2022-12-21', '3', '1', '8:00', '9:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('14', '2022-12-21', '3', '2', '9:00', '10:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('15', '2022-12-21', '3', '3', '10:00', '11:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('16', '2022-12-21', '3', '4', '11:00', '12:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('17', '2022-12-21', '3', '5', '14:00', '15:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('18', '2022-12-21', '3', '6', '15:00', '16:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('19', '2022-12-22', '4', '1', '8:00', '9:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('20', '2022-12-22', '4', '2', '9:00', '10:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('21', '2022-12-22', '4', '3', '10:00', '11:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('22', '2022-12-22', '4', '4', '11:00', '12:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('23', '2022-12-22', '4', '5', '14:00', '15:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('24', '2022-12-22', '4', '6', '15:00', '16:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('25', '2022-12-23', '5', '1', '8:00', '9:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('26', '2022-12-23', '5', '2', '9:00', '10:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('27', '2022-12-23', '5', '3', '10:00', '11:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('28', '2022-12-23', '5', '4', '11:00', '12:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('29', '2022-12-23', '5', '5', '14:00', '15:00', '1', '1', '1', '1'); INSERT INTO `attendclass` VALUES ('30', '2022-12-23', '5', '6', '15:00', '16:00', '1', '1', '1', '1'); -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `class_id` int(11) NOT NULL AUTO_INCREMENT, `class_name` varchar(100) DEFAULT NULL, PRIMARY KEY (`class_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES ('1', '一班'); -- ---------------------------- -- Table structure for classroom -- ---------------------------- DROP TABLE IF EXISTS `classroom`; CREATE TABLE `classroom` ( `classroom_id` int(11) NOT NULL AUTO_INCREMENT, `classroom_name` varchar(100) DEFAULT NULL, PRIMARY KEY (`classroom_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Records of classroom -- ---------------------------- INSERT INTO `classroom` VALUES ('1', '第一教室'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `course_id` int(11) NOT NULL AUTO_INCREMENT, `course_name` varchar(100) DEFAULT NULL, PRIMARY KEY (`course_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('1', '高数'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `teacher_id` int(11) NOT NULL AUTO_INCREMENT, `teacher_name` varchar(100) DEFAULT NULL, PRIMARY KEY (`teacher_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '张老师'); -- ---------------------------- -- View structure for view_attendclass -- ---------------------------- DROP VIEW IF EXISTS `view_attendclass`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_attendclass` AS select a.* ,(select class_name from class where class_id = a.class_id) as class_name ,(select course_name from course where course_id = a.course_id) as course_name ,(select classroom_name from classroom where classroom_id = a.classroom_id) as classroom_name ,(select teacher_name from teacher where teacher_id = a.teacher_id) as teacher_name from attendclass as a ;