/*
 Navicat MySQL Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : invigilatorsystem

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 05/01/2021 13:19:33
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for classroom
-- ----------------------------
DROP TABLE IF EXISTS `classroom`;
CREATE TABLE `classroom`  (
  `classroom_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '考场id',
  `school_id` int(11) NULL DEFAULT NULL COMMENT '考场所在学校id',
  `position` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考场地点',
  PRIMARY KEY (`classroom_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of classroom
-- ----------------------------

-- ----------------------------
-- Table structure for command_classroom
-- ----------------------------
DROP TABLE IF EXISTS `command_classroom`;
CREATE TABLE `command_classroom`  (
  `school_id` int(5) UNSIGNED NULL DEFAULT NULL COMMENT '学校id',
  `require_room_num` int(3) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT '学校需要提供的考场数量',
  `exam_id` int(2) UNSIGNED NULL DEFAULT NULL COMMENT '考试id',
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '要求考场id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of command_classroom
-- ----------------------------
INSERT INTO `command_classroom` VALUES (1, 003, 1, 1);
INSERT INTO `command_classroom` VALUES (2, 003, 1, 2);

-- ----------------------------
-- Table structure for command_teacher
-- ----------------------------
DROP TABLE IF EXISTS `command_teacher`;
CREATE TABLE `command_teacher`  (
  `school_id` int(11) NULL DEFAULT NULL COMMENT '学校id',
  `require_male` int(11) NULL DEFAULT NULL COMMENT '需要的男老师数量',
  `require_female` int(11) NULL DEFAULT NULL COMMENT '需要的女老师数量',
  `exam_id` int(11) NULL DEFAULT NULL COMMENT '考试id',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of command_teacher
-- ----------------------------

-- ----------------------------
-- Table structure for exam
-- ----------------------------
DROP TABLE IF EXISTS `exam`;
CREATE TABLE `exam`  (
  `exam_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '考试id',
  `exam_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试名称',
  PRIMARY KEY (`exam_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of exam
-- ----------------------------

-- ----------------------------
-- Table structure for exam_role
-- ----------------------------
DROP TABLE IF EXISTS `exam_role`;
CREATE TABLE `exam_role`  (
  `role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `exam_role_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考式类型名字(文科\\理科)',
  `exam_id` int(11) NULL DEFAULT NULL COMMENT '考试id',
  PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of exam_role
-- ----------------------------

-- ----------------------------
-- Table structure for exam_subject
-- ----------------------------
DROP TABLE IF EXISTS `exam_subject`;
CREATE TABLE `exam_subject`  (
  `exam_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '考试id',
  `subject_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试科目',
  `subject_start_date` datetime(0) NULL DEFAULT NULL COMMENT '考试开始时间',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `subject_end_date` datetime(0) NULL DEFAULT NULL COMMENT '考试截止时间',
  `role_id` int(11) NULL DEFAULT NULL COMMENT '考试类型名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of exam_subject
-- ----------------------------

-- ----------------------------
-- Table structure for invigilator_arrange
-- ----------------------------
DROP TABLE IF EXISTS `invigilator_arrange`;
CREATE TABLE `invigilator_arrange`  (
  `group_id` int(11) NOT NULL COMMENT '小组id',
  `classroom_num` int(11) NOT NULL COMMENT '所在考场序号',
  `school_id` int(11) NULL DEFAULT NULL COMMENT '学校id',
  `id` int(11) NOT NULL COMMENT 'id',
  `subject_name` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '考试科目',
  `exam_id` int(11) NULL DEFAULT NULL COMMENT '考试id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of invigilator_arrange
-- ----------------------------

-- ----------------------------
-- Table structure for invigilator_classroom
-- ----------------------------
DROP TABLE IF EXISTS `invigilator_classroom`;
CREATE TABLE `invigilator_classroom`  (
  `classroom_num` int(11) NULL DEFAULT NULL COMMENT '正式考场序号',
  `classroom_id` int(11) NOT NULL COMMENT '考场id',
  `school_id` int(11) NULL DEFAULT NULL COMMENT '学校id',
  `subject_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试类型',
  `exam_id` int(11) NOT NULL COMMENT '考试id',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `role_id` int(11) NULL DEFAULT NULL COMMENT '考生分类名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of invigilator_classroom
-- ----------------------------
INSERT INTO `invigilator_classroom` VALUES (NULL, 1, 1, NULL, 1, 1, NULL);

-- ----------------------------
-- Table structure for invigilator_group
-- ----------------------------
DROP TABLE IF EXISTS `invigilator_group`;
CREATE TABLE `invigilator_group`  (
  `group_id` int(11) NOT NULL COMMENT '小组id',
  `member1` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组长工号',
  `member2` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组员1工号',
  `member3` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组员2工号',
  `school_id` int(11) NULL DEFAULT NULL COMMENT '监考学校id(考点)',
  `exam_id` int(11) NOT NULL COMMENT '考试id',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 55 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of invigilator_group
-- ----------------------------
INSERT INTO `invigilator_group` VALUES (1, '1', '11', '15', 1, 1, 49);
INSERT INTO `invigilator_group` VALUES (2, '3', '14', '12', 1, 1, 50);
INSERT INTO `invigilator_group` VALUES (3, '4', '10', '16', 1, 1, 51);
INSERT INTO `invigilator_group` VALUES (4, '6', '5', '17', 2, 1, 52);
INSERT INTO `invigilator_group` VALUES (5, '7', '2', '18', 2, 1, 53);
INSERT INTO `invigilator_group` VALUES (6, '8', '13', '9', 2, 1, 54);

-- ----------------------------
-- Table structure for invigilator_teacher
-- ----------------------------
DROP TABLE IF EXISTS `invigilator_teacher`;
CREATE TABLE `invigilator_teacher`  (
  `workcard` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '工号',
  `school_id` int(5) NULL DEFAULT NULL COMMENT '学校id',
  `seniority` enum('是','否') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否主监考资格',
  `sex` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `group_id` int(11) NULL DEFAULT NULL COMMENT '小组id',
  `exam_id` int(2) NOT NULL COMMENT '考试id',
  `note` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注(是否有违纪行为)',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of invigilator_teacher
-- ----------------------------
INSERT INTO `invigilator_teacher` VALUES ('1', 1, '是', '男', 1, 1, NULL, 1);
INSERT INTO `invigilator_teacher` VALUES ('2', 1, '否', '女', 5, 1, NULL, 2);
INSERT INTO `invigilator_teacher` VALUES ('3', 1, '是', '男', 2, 1, NULL, 3);
INSERT INTO `invigilator_teacher` VALUES ('4', 1, '是', '女', 3, 1, NULL, 4);
INSERT INTO `invigilator_teacher` VALUES ('5', 1, '否', '男', 4, 1, NULL, 5);
INSERT INTO `invigilator_teacher` VALUES ('6', 2, '是', '女', 4, 1, NULL, 6);
INSERT INTO `invigilator_teacher` VALUES ('7', 2, '是', '男', 5, 1, NULL, 7);
INSERT INTO `invigilator_teacher` VALUES ('8', 2, '是', '女', 6, 1, NULL, 8);
INSERT INTO `invigilator_teacher` VALUES ('9', 1, '是', '女', 6, 1, NULL, 9);
INSERT INTO `invigilator_teacher` VALUES ('10', 2, '否', '男', 3, 1, NULL, 10);
INSERT INTO `invigilator_teacher` VALUES ('11', 2, '否', '女', 1, 1, NULL, 11);
INSERT INTO `invigilator_teacher` VALUES ('12', 2, '否', '男', 2, 1, NULL, 12);
INSERT INTO `invigilator_teacher` VALUES ('13', 3, '否', '男', 6, 1, NULL, 13);
INSERT INTO `invigilator_teacher` VALUES ('14', 3, '否', '女', 2, 1, NULL, 14);
INSERT INTO `invigilator_teacher` VALUES ('15', 3, '是', '女', 1, 1, NULL, 15);
INSERT INTO `invigilator_teacher` VALUES ('16', 3, '否', '女', 3, 1, NULL, 16);
INSERT INTO `invigilator_teacher` VALUES ('17', 3, '是', '男', 4, 1, NULL, 17);
INSERT INTO `invigilator_teacher` VALUES ('18', 3, '否', '男', 5, 1, NULL, 18);

-- ----------------------------
-- Table structure for school
-- ----------------------------
DROP TABLE IF EXISTS `school`;
CREATE TABLE `school`  (
  `school_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学校id',
  `school_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校名称',
  `school_classroom_num` int(11) NULL DEFAULT NULL COMMENT '考场数量',
  `school_type` enum('考点','非考点') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否考点',
  PRIMARY KEY (`school_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of school
-- ----------------------------
INSERT INTO `school` VALUES (1, 'dfa', 12, '考点');
INSERT INTO `school` VALUES (2, 'ad', NULL, '考点');
INSERT INTO `school` VALUES (3, '22', NULL, '考点');
INSERT INTO `school` VALUES (4, 'afad', NULL, '非考点');
INSERT INTO `school` VALUES (6, '444', NULL, '考点');
INSERT INTO `school` VALUES (7, '厦门一中', NULL, '考点');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `sex` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `photo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '照片',
  `examine_state` enum('未通过','通过','待审核') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审批状态',
  `card` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  `teach_grade` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '授课年级',
  `violation` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '处罚',
  `seniority` enum('主考','非主考') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主考资格',
  `state` enum('在岗','病休') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态',
  `school_id` int(11) NULL DEFAULT NULL COMMENT '工作单位（学校）id',
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系电话',
  `middle_num` int(2) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT '中考监考次数',
  `high_num` int(2) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT '高考监考次数',
  `workcard` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工号',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (NULL, NULL, NULL, NULL, '通过', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '12345678', 1);

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `school_id` int(11) NULL DEFAULT NULL COMMENT '学校id',
  `role` enum('老师','校管理员','招生办审批员','教育部') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('123456', '123456', NULL, NULL, 4);
INSERT INTO `user` VALUES ('12345678', '123456', NULL, '老师', 5);

SET FOREIGN_KEY_CHECKS = 1;