dijiuzu/timetable.sql

/*
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 ;