master

分支 (1)

管理

管理

master

RHHOA
/
gn_oa.sql

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50506
 Source Host           : localhost:3306
 Source Schema         : gn_oa

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

 Date: 27/03/2021 16:53:15
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for attendance
-- ----------------------------
DROP TABLE IF EXISTS `attendance`;
CREATE TABLE `attendance`  (
  `attendance_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '考勤表主键',
  `emp_id` int(11) NOT NULL COMMENT '员工ID,关联员工表的主键',
  `work_type_id` int(11) NOT NULL COMMENT '任务类型ID,关联任务类型主键',
  `start_time` date NOT NULL COMMENT '起始时间',
  `end_time` date NOT NULL COMMENT '结束时间',
  `days` int(11) NOT NULL DEFAULT 1 COMMENT '请假天数',
  `result_status` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务状态',
  PRIMARY KEY (`attendance_id`) USING BTREE,
  INDEX `emp_id`(`emp_id`) USING BTREE,
  INDEX `work_type_id`(`work_type_id`) USING BTREE,
  CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`emp_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `attendance_ibfk_2` FOREIGN KEY (`work_type_id`) REFERENCES `work_type` (`work_type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1002 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '考勤表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of attendance
-- ----------------------------
INSERT INTO `attendance` VALUES (1001, 1001, 1001, '2021-01-01', '2021-01-02', 1, '进行中');

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `class_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级主键',
  `class_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '班级名称',
  `subject_id` int(11) NOT NULL COMMENT '学科ID，关联学科表主键',
  `class_count` int(11) DEFAULT 0 COMMENT '班级人数',
  `class_life` int(11) DEFAULT 0 COMMENT '班级周期',
  `class_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '班级地址',
  `create_time` date NOT NULL COMMENT '班级创建时间',
  PRIMARY KEY (`class_id`) USING BTREE,
  INDEX `subject_id`(`subject_id`) USING BTREE,
  CONSTRAINT `class_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`subject_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1004 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '班级表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1001, '1班', 1001, 10, 11, '南京', '2021-01-01');
INSERT INTO `class` VALUES (1002, '2班', 1002, 11, 12, '北京', '2021-01-02');
INSERT INTO `class` VALUES (1003, '3班', 1003, 13, 14, '苏州', '2021-01-03');

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门主键',
  `dept_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名称',
  `create_time` date NOT NULL COMMENT '部门创建时间',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1005 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '部门表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1001, '行政部', '2021-01-07');
INSERT INTO `department` VALUES (1002, '技术部', '2021-01-15');
INSERT INTO `department` VALUES (1003, '财务部', '2021-01-14');
INSERT INTO `department` VALUES (1004, '销售部', '2021-01-01');

-- ----------------------------
-- Table structure for education
-- ----------------------------
DROP TABLE IF EXISTS `education`;
CREATE TABLE `education`  (
  `education_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学历主键',
  `education_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学历名称',
  PRIMARY KEY (`education_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1009 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学历表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of education
-- ----------------------------
INSERT INTO `education` VALUES (1001, '中专');
INSERT INTO `education` VALUES (1002, '大专');
INSERT INTO `education` VALUES (1003, '高中');
INSERT INTO `education` VALUES (1004, '初中');
INSERT INTO `education` VALUES (1005, '幼儿园');
INSERT INTO `education` VALUES (1006, '本科');
INSERT INTO `education` VALUES (1007, '研究生');
INSERT INTO `education` VALUES (1008, '博士');

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工主键',
  `emp_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工名',
  `dept_id` int(11) DEFAULT NULL COMMENT '部门ID，关联department表的主键',
  `gender` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别',
  `phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '手机号',
  `qq` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'QQ号',
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱',
  `hire_time` date DEFAULT NULL COMMENT '入职时间',
  `emp_password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '员工密码',
  `role_id` int(11) DEFAULT NULL COMMENT '角色ID关联角色表主键',
  `salt` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '盐值',
  PRIMARY KEY (`emp_id`) USING BTREE,
  INDEX `dept_id`(`dept_id`) USING BTREE,
  INDEX `role_id`(`role_id`) USING BTREE,
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1011 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1001, '张三', 1001, '男', '123', '123', '132@qq.com', '2021-01-01', '123', 1002, '123');
INSERT INTO `employee` VALUES (1002, '李四', 1002, '男', '456', '465', '456@qq.com', '2021-01-01', '2f09f01d85b3a0985c69b560cfbbad97', 1001, 'rh.163@.com');
INSERT INTO `employee` VALUES (1003, 'RHH', 1002, '男', '123', '123', 'rh.163@.com', '2021-01-27', '2f09f01d85b3a0985c69b560cfbbad97', 1002, 'rh.163@.com');
INSERT INTO `employee` VALUES (1004, '123', 1004, '未知', '123', '123', '123.163@.com', '2021-01-11', '2f09f01d85b3a0985c69b560cfbbad97', 1003, 'rh.163@.com');
INSERT INTO `employee` VALUES (1010, '饿汉', 1003, '中性', 'ehan', 'ehan', 'ehan@163.com', '2021-01-22', 'bc4e48c4bd5d26e9545ab243e5553157', 1001, 'ehan@163.com');

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色主键',
  `role_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称',
  `status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '状态: 有效/无效',
  PRIMARY KEY (`role_id`) USING BTREE,
  UNIQUE INDEX `role_name`(`role_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1004 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1001, 'admin', '有效');
INSERT INTO `role` VALUES (1002, 'teacher', '有效');
INSERT INTO `role` VALUES (1003, 'manager', '有效');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `student_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学员主键',
  `student_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学员名称',
  `class_id` int(11) NOT NULL COMMENT '班级ID关联班级表的主键',
  `stu_gender` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别 男:女',
  `stu_phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号',
  `stu_qq` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'QQ号',
  `stu_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱',
  `graduate_school` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '毕业院校',
  `education_id` int(11) NOT NULL COMMENT '学历ID,关联学历表主键',
  `create_time` date DEFAULT NULL COMMENT '出生日期',
  `in_time` date NOT NULL COMMENT '入学时间',
  `emp_id` int(11) NOT NULL COMMENT '教师ID关联员工表主键',
  `card_num` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号',
  PRIMARY KEY (`student_id`) USING BTREE,
  INDEX `education_id`(`education_id`) USING BTREE,
  INDEX `class_id`(`class_id`) USING BTREE,
  INDEX `emp_id`(`emp_id`) USING BTREE,
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`education_id`) REFERENCES `education` (`education_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `student_ibfk_2` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `student_ibfk_3` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`emp_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1002 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学员表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1001, 'rhh', 1002, '男', '123', '1234', '123@163.com', '扬州大学', 1006, '2021-01-07', '2021-01-16', 1001, '123121');

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`  (
  `subject_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学科主键',
  `subject_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学科名称',
  `subject_life` int(11) DEFAULT 0 COMMENT '学科周期',
  `create_time` date NOT NULL COMMENT '学科创建时间',
  `subject_type_id` int(11) NOT NULL COMMENT '学科类型ID，关联学科表主键',
  PRIMARY KEY (`subject_id`) USING BTREE,
  INDEX `subject_type_id`(`subject_type_id`) USING BTREE,
  CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`subject_type_id`) REFERENCES `subject_type` (`subject_type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1007 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学科表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES (1001, '摸鱼啊', 10, '2021-01-25', 1001);
INSERT INTO `subject` VALUES (1002, 'H5', 22, '2020-12-24', 1001);
INSERT INTO `subject` VALUES (1003, 'python', 22, '2020-12-07', 1001);
INSERT INTO `subject` VALUES (1004, 'Java', 22, '2020-05-16', 1001);
INSERT INTO `subject` VALUES (1005, 'PHP', 22, '2020-12-15', 1001);
INSERT INTO `subject` VALUES (1006, '软件测试', 20, '2020-12-18', 1001);

-- ----------------------------
-- Table structure for subject_type
-- ----------------------------
DROP TABLE IF EXISTS `subject_type`;
CREATE TABLE `subject_type`  (
  `subject_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学科类型主键',
  `subject_type_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学科名称',
  PRIMARY KEY (`subject_type_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1006 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学科类型表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subject_type
-- ----------------------------
INSERT INTO `subject_type` VALUES (1001, '周末班');
INSERT INTO `subject_type` VALUES (1002, '精品班');
INSERT INTO `subject_type` VALUES (1003, '正式班');
INSERT INTO `subject_type` VALUES (1004, '好程序员');
INSERT INTO `subject_type` VALUES (1005, 'java进阶');

-- ----------------------------
-- Table structure for work_type
-- ----------------------------
DROP TABLE IF EXISTS `work_type`;
CREATE TABLE `work_type`  (
  `work_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '任务类型主键',
  `work_type_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务类型名称',
  PRIMARY KEY (`work_type_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1004 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '任务类型表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of work_type
-- ----------------------------
INSERT INTO `work_type` VALUES (1001, '婚假');
INSERT INTO `work_type` VALUES (1002, '病假');
INSERT INTO `work_type` VALUES (1003, '有事');

SET FOREIGN_KEY_CHECKS = 1;