/*
Navicat MySQL Data Transfer

Source Server         : groundhot
Source Server Version : 50712
Source Host           : localhost:3306
Source Database       : sys_lab_assistant

Target Server Type    : MYSQL
Target Server Version : 50712
File Encoding         : 65001

Date: 2021-07-06 16:41:57
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `assistant_job`
-- ----------------------------
DROP TABLE IF EXISTS `assistant_job`;
CREATE TABLE `assistant_job` (
  `job_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '工作内容编号',
  `job_semester` varchar(50) NOT NULL COMMENT '工作学期',
  `job_week` varchar(50) NOT NULL COMMENT '工作周次',
  `job_time` date NOT NULL COMMENT '工作时间',
  `job_content` text NOT NULL COMMENT '工作内容',
  `job_assistant` int(10) NOT NULL COMMENT '助理编号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`job_id`),
  KEY `assistant` (`job_assistant`),
  CONSTRAINT `assistant` FOREIGN KEY (`job_assistant`) REFERENCES `user_assistant` (`a_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of assistant_job
-- ----------------------------
INSERT INTO `assistant_job` VALUES ('5', '上半学期', '12', '2021-07-06', 'aaaa', '5', '2021-07-06 16:01:00', '2021-07-06 16:01:00');

-- ----------------------------
-- Table structure for `assistant_repair`
-- ----------------------------
DROP TABLE IF EXISTS `assistant_repair`;
CREATE TABLE `assistant_repair` (
  `repair_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '报修编号',
  `equipment_name` varchar(255) NOT NULL COMMENT '设备名称',
  `equipment_no` varchar(50) NOT NULL COMMENT '设备编号',
  `error_content` text NOT NULL COMMENT '故障描述',
  `repair_time` date NOT NULL COMMENT '报修时间',
  `repair_assistant` int(10) NOT NULL COMMENT '报修人',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`repair_id`),
  KEY `repair_assistant` (`repair_assistant`),
  CONSTRAINT `repair_assistant` FOREIGN KEY (`repair_assistant`) REFERENCES `user_assistant` (`a_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of assistant_repair
-- ----------------------------

-- ----------------------------
-- Table structure for `assistant_subsidy`
-- ----------------------------
DROP TABLE IF EXISTS `assistant_subsidy`;
CREATE TABLE `assistant_subsidy` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '补助编号',
  `a_id` int(10) NOT NULL COMMENT '助理档案编号',
  `money` decimal(20,0) unsigned zerofill NOT NULL COMMENT '补助金额',
  `semester` varchar(50) NOT NULL COMMENT '学期',
  `year` varchar(20) NOT NULL COMMENT '年份',
  `month` varchar(20) NOT NULL COMMENT '月份',
  `day` varchar(20) NOT NULL COMMENT '日期',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of assistant_subsidy
-- ----------------------------

-- ----------------------------
-- Table structure for `department`
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `d_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `d_name` varchar(50) NOT NULL COMMENT '部门名称',
  `type_id` int(10) NOT NULL COMMENT '部门类型id',
  `pid` int(10) unsigned zerofill NOT NULL COMMENT '上一级部门编号',
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`d_id`),
  KEY `typeId` (`type_id`),
  CONSTRAINT `typeId` FOREIGN KEY (`type_id`) REFERENCES `department_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', '二级学院', '1', '0000000000', '2021-06-29 21:20:17', '2021-06-29 21:20:15');

-- ----------------------------
-- Table structure for `department_type`
-- ----------------------------
DROP TABLE IF EXISTS `department_type`;
CREATE TABLE `department_type` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(20) NOT NULL COMMENT '部门类型名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department_type
-- ----------------------------
INSERT INTO `department_type` VALUES ('1', '行政');

-- ----------------------------
-- Table structure for `interview_result`
-- ----------------------------
DROP TABLE IF EXISTS `interview_result`;
CREATE TABLE `interview_result` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `student_no` varchar(50) NOT NULL COMMENT '学号',
  `student_name` varchar(255) NOT NULL COMMENT '姓名',
  `interview_score` varchar(50) NOT NULL COMMENT '面试成绩',
  `interview_type` int(10) unsigned zerofill NOT NULL COMMENT '是否录用 0-未审核 1-录用 2-未录用',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of interview_result
-- ----------------------------
INSERT INTO `interview_result` VALUES ('2', '2018', 'a', '67', '0000000001', '2021-07-02 20:14:49', '2021-07-02 20:14:50');
INSERT INTO `interview_result` VALUES ('4', '2019', '张三', '68', '0000000001', '2021-07-06 11:04:06', '2021-07-06 11:04:07');

-- ----------------------------
-- Table structure for `lab`
-- ----------------------------
DROP TABLE IF EXISTS `lab`;
CREATE TABLE `lab` (
  `lab_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '实验室编号',
  `lab_name` varchar(255) NOT NULL COMMENT '实验室名称',
  `d_id` int(10) NOT NULL COMMENT '所属部门编号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`lab_id`,`lab_name`),
  KEY `did` (`d_id`),
  KEY `lab_id` (`lab_id`),
  CONSTRAINT `did` FOREIGN KEY (`d_id`) REFERENCES `department` (`d_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of lab
-- ----------------------------
INSERT INTO `lab` VALUES ('6', '实验室', '1', '2021-06-30 10:29:03', '2021-06-30 10:29:03');
INSERT INTO `lab` VALUES ('8', '软件实验室', '1', '2021-06-30 10:52:01', '2021-06-30 10:52:01');

-- ----------------------------
-- Table structure for `lab_technician`
-- ----------------------------
DROP TABLE IF EXISTS `lab_technician`;
CREATE TABLE `lab_technician` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `lab_id` int(10) NOT NULL COMMENT '实验室编号',
  `account` varchar(20) NOT NULL COMMENT '工号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `account` (`account`),
  KEY `labId` (`lab_id`),
  CONSTRAINT `account` FOREIGN KEY (`account`) REFERENCES `user_technician` (`account`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `labId` FOREIGN KEY (`lab_id`) REFERENCES `lab` (`lab_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of lab_technician
-- ----------------------------
INSERT INTO `lab_technician` VALUES ('1', '6', '1', '2021-06-30 10:29:03', '2021-06-30 10:29:03');
INSERT INTO `lab_technician` VALUES ('2', '8', '1', '2021-06-30 10:52:01', '2021-06-30 10:52:01');

-- ----------------------------
-- Table structure for `recruitment_plan`
-- ----------------------------
DROP TABLE IF EXISTS `recruitment_plan`;
CREATE TABLE `recruitment_plan` (
  `plan_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '招聘计划编号',
  `plan_name` varchar(255) NOT NULL COMMENT '招聘计划名称',
  `requirement` text NOT NULL COMMENT '岗位要求',
  `plan_num` int(20) unsigned zerofill NOT NULL COMMENT '招聘人数',
  `apply_lab` int(10) NOT NULL,
  `t_id` varchar(20) NOT NULL COMMENT '申请人',
  `start_time` date NOT NULL COMMENT '开始时间',
  `end_time` date NOT NULL COMMENT '截止时间',
  `apply_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '申请时间',
  `status` int(10) unsigned zerofill NOT NULL COMMENT '审核状态 0-未审核 1-通过 2-不通过',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`plan_id`),
  KEY `applyPeople` (`t_id`),
  KEY `labName` (`apply_lab`),
  CONSTRAINT `applyPeople` FOREIGN KEY (`t_id`) REFERENCES `user_technician` (`account`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `labName` FOREIGN KEY (`apply_lab`) REFERENCES `lab` (`lab_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of recruitment_plan
-- ----------------------------
INSERT INTO `recruitment_plan` VALUES ('1', '测试计划', '测试计划内容c', '00000000000000000012', '6', '1', '2021-07-02', '2021-07-02', '2021-07-02 13:20:46', '0000000001', '2021-07-02 10:48:31', '2021-07-02 13:20:46');
INSERT INTO `recruitment_plan` VALUES ('2', '课设答辩招聘', '陪同课设答辩', '00000000000000000002', '8', '1', '2021-07-04', '2021-07-04', '2021-07-04 09:40:14', '0000000001', '2021-07-04 09:38:52', '2021-07-04 09:40:14');
INSERT INTO `recruitment_plan` VALUES ('3', '招聘计划', '招聘答辩人与', '00000000000000000002', '8', '1', '2021-07-06', '2021-07-06', '2021-07-06 10:50:58', '0000000001', '2021-07-06 10:10:12', '2021-07-06 10:50:58');

-- ----------------------------
-- Table structure for `recruitment_post`
-- ----------------------------
DROP TABLE IF EXISTS `recruitment_post`;
CREATE TABLE `recruitment_post` (
  `post_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '岗位编号',
  `post_name` varchar(255) NOT NULL COMMENT '岗位名称',
  `post_content` text NOT NULL COMMENT '岗位内容',
  `plan_id` int(10) NOT NULL COMMENT '招聘计划编号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`post_id`),
  KEY `planId` (`plan_id`),
  KEY `post_name` (`post_name`),
  CONSTRAINT `planId` FOREIGN KEY (`plan_id`) REFERENCES `recruitment_plan` (`plan_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of recruitment_post
-- ----------------------------
INSERT INTO `recruitment_post` VALUES ('1', '测试计划', '测试计划内容c', '1', '2021-07-02 13:20:46', '2021-07-02 13:20:46');
INSERT INTO `recruitment_post` VALUES ('2', '课设答辩招聘', '陪同课设答辩', '2', '2021-07-04 09:40:14', '2021-07-04 09:40:14');
INSERT INTO `recruitment_post` VALUES ('3', '招聘计划', '招聘答辩人与', '3', '2021-07-06 10:50:58', '2021-07-06 10:50:58');

-- ----------------------------
-- Table structure for `student_apply`
-- ----------------------------
DROP TABLE IF EXISTS `student_apply`;
CREATE TABLE `student_apply` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '报名编号',
  `post_id` int(10) unsigned NOT NULL,
  `job_description` varchar(255) NOT NULL COMMENT '技能描述',
  `student_no` varchar(20) NOT NULL,
  `apply_name` varchar(255) NOT NULL COMMENT '申请人',
  `apply_time` date NOT NULL COMMENT '申请时间',
  `apply_type` int(10) unsigned zerofill NOT NULL COMMENT '报名状态 0-未审核 1-通过 2-不通过',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `applyname` (`apply_name`),
  KEY `postId` (`post_id`),
  KEY `applyno` (`student_no`),
  CONSTRAINT `applyname` FOREIGN KEY (`apply_name`) REFERENCES `user_student` (`student_name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `applyno` FOREIGN KEY (`student_no`) REFERENCES `user_student` (`student_no`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `postId` FOREIGN KEY (`post_id`) REFERENCES `recruitment_post` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student_apply
-- ----------------------------
INSERT INTO `student_apply` VALUES ('4', '1', '测试流程', '2018', 'a', '2021-07-02', '0000000001', '2021-07-02 20:12:27', '2021-07-02 20:12:28');
INSERT INTO `student_apply` VALUES ('6', '2', 'aaaa', '2019', '张三', '2021-07-06', '0000000001', '2021-07-06 11:03:51', '2021-07-06 11:03:51');

-- ----------------------------
-- Table structure for `user_admin`
-- ----------------------------
DROP TABLE IF EXISTS `user_admin`;
CREATE TABLE `user_admin` (
  `account` varchar(20) NOT NULL COMMENT '账号',
  `password` varchar(20) NOT NULL COMMENT '密码',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_admin
-- ----------------------------
INSERT INTO `user_admin` VALUES ('user', '123', '2021-06-30 08:35:05', '2021-06-30 08:35:06');

-- ----------------------------
-- Table structure for `user_assistant`
-- ----------------------------
DROP TABLE IF EXISTS `user_assistant`;
CREATE TABLE `user_assistant` (
  `a_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '档案编号',
  `student_no` varchar(50) NOT NULL COMMENT '学号',
  `a_password` varchar(50) NOT NULL COMMENT '密码',
  `a_name` varchar(255) NOT NULL COMMENT '姓名',
  `a_college` varchar(255) NOT NULL COMMENT '学院',
  `a_major` varchar(255) NOT NULL COMMENT '专业',
  `a_grade` varchar(50) NOT NULL COMMENT '年级',
  `a_mobile` varchar(20) NOT NULL COMMENT '电话',
  `a_email` varchar(50) NOT NULL COMMENT '邮箱',
  `img_url` varchar(255) NOT NULL COMMENT '个人相片',
  `lab_id` int(10) NOT NULL COMMENT '所属实验室id',
  `status` int(10) unsigned zerofill NOT NULL COMMENT '状态 0-在职 1-离职',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`a_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_assistant
-- ----------------------------
INSERT INTO `user_assistant` VALUES ('3', '2018', '123', 'a', 'a', 'a', 'a', 'a', 'a', 'http://localhost:9527/sysLab/img/8fce5281bbf840fda74d0a5f6bb86635.jpg', '6', '0000000000', '2021-07-02 20:14:50', '2021-07-02 20:14:50');
INSERT INTO `user_assistant` VALUES ('5', '2019', '123', '张三', '2019级', '大数据', '软件工程', '1231231212', '123123@qq.com', 'http://localhost:9527/sysLab/img/005e9e9a49dc45c0b3fd331bd2170997.jpg', '8', '0000000000', '2021-07-06 11:04:07', '2021-07-06 11:04:07');

-- ----------------------------
-- Table structure for `user_student`
-- ----------------------------
DROP TABLE IF EXISTS `user_student`;
CREATE TABLE `user_student` (
  `student_no` varchar(50) NOT NULL COMMENT '学号',
  `student_name` varchar(255) NOT NULL COMMENT '姓名',
  `student_pwd` varchar(50) NOT NULL COMMENT '密码',
  `student_major` varchar(255) NOT NULL COMMENT '专业',
  `student_college` varchar(255) NOT NULL COMMENT '学院',
  `student_grade` varchar(50) NOT NULL COMMENT '年级',
  `student_mobile` varchar(11) NOT NULL COMMENT '手机号',
  `student_email` varchar(20) NOT NULL COMMENT '邮箱',
  `img_url` varchar(255) NOT NULL COMMENT '相片路径',
  `status` int(10) unsigned zerofill NOT NULL COMMENT '状态 0-禁用 1-正常 2-待审核',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`student_no`),
  KEY `student_name` (`student_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_student
-- ----------------------------
INSERT INTO `user_student` VALUES ('2018', 'a', '123', 'a', 'a', 'a', 'a', 'a', 'http://localhost:9527/sysLab/img/8fce5281bbf840fda74d0a5f6bb86635.jpg', '0000000001', '2021-07-02 14:42:55', '2021-07-02 14:42:56');
INSERT INTO `user_student` VALUES ('2019', '张三', '123', '大数据', '2019级', '软件工程', '1231231212', '123123@qq.com', 'http://localhost:9527/sysLab/img/005e9e9a49dc45c0b3fd331bd2170997.jpg', '0000000001', '2021-07-04 09:21:43', '2021-07-04 09:21:43');
INSERT INTO `user_student` VALUES ('2020', '李四', '123', '大数据', '2020级', '软件工程', '1231231212', '123123@qq.com', 'http://localhost:9527/sysLab/img/66e0a3eeb35e4538b60effc0c611d77b.jpg', '0000000001', '2021-07-06 10:55:13', '2021-07-06 10:55:13');

-- ----------------------------
-- Table structure for `user_technician`
-- ----------------------------
DROP TABLE IF EXISTS `user_technician`;
CREATE TABLE `user_technician` (
  `account` varchar(20) NOT NULL DEFAULT '0' COMMENT '工号',
  `name` varchar(255) NOT NULL DEFAULT '0' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '0' COMMENT '密码',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_technician
-- ----------------------------
INSERT INTO `user_technician` VALUES ('1', '1', '1', '2021-06-30 10:21:48', '2021-06-30 10:21:51');