/*
SQLyog Ultimate v10.00 Beta1
MySQL - 8.0.31 : Database - exam_system
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE liuru;

USE liuru;

/*Table structure for table `answer` */

DROP TABLE IF EXISTS `answer`;
CREATE TABLE `answer` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '答案表的主键',
  `all_option` longtext COMMENT '当前题目所有答案的信息',
  `images` longtext COMMENT '答案的图片路径',
  `analysis` longtext COMMENT '答案解析',
  `question_id` int NOT NULL COMMENT '对应题目的id',
  `true_option` varchar(25) DEFAULT NULL COMMENT '正确的选项对应的下标',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `answer` */

insert  into `answer`(`id`,`all_option`,`images`,`analysis`,`question_id`,`true_option`) values (1,'1,2','','1',5,'0'),(3,'语文,数学,英语,选修课',',',NULL,6,'0,1,2'),(10,'0,1','','111',11,'0'),(11,'11,16','','16',12,'1'),(12,'9,8','','9',13,'0'),(13,'4,3','','4',14,'0'),(14,'18,11','','18',15,'0'),(15,'1','','1',16,'0'),(16,'4,3','','4',17,'0'),(17,'1,2,3,4','',NULL,18,'0,1'),(18,'奇数,偶数','','奇数',19,'0'),(19,'奇数,偶数','','奇数',20,'0'),(20,'111,222,333','',NULL,21,'0,1,2'),(21,'选项1,选项2,选项3,选项4','','22',22,'1'),(22,'选项1,选项2,选项3,选项4','',NULL,23,'0,2'),(23,'题目23','','11',24,'0'),(24,'题目24','',NULL,25,'0'),(25,'啊,是,的','','是',27,'1'),(26,'阿斯顿,阿斯顿,打撒','','大三',28,'2'),(27,'123,321,231','','大四',29,'2'),(28,'json,666,123','','yes',30,'0'),(29,'123,123,123','','345',31,'1');

/*Table structure for table `exam` */

DROP TABLE IF EXISTS `exam`;

CREATE TABLE `exam` (
  `exam_id` int NOT NULL AUTO_INCREMENT,
  `exam_name` varchar(100) NOT NULL COMMENT '考试名称',
  `exam_desc` varchar(100) DEFAULT NULL COMMENT '考试描述',
  `type` int NOT NULL DEFAULT '1' COMMENT '1完全公开  2需要密码',
  `password` varchar(50) DEFAULT NULL COMMENT '需要密码考试的密码',
  `duration` int NOT NULL COMMENT '考试时长',
  `start_time` date DEFAULT NULL COMMENT '考试开始时间',
  `end_time` date DEFAULT NULL COMMENT '考试结束时间',
  `total_score` int NOT NULL COMMENT '考试总分',
  `pass_score` int NOT NULL COMMENT '考试通过线',
  `status` int NOT NULL DEFAULT '1' COMMENT '1有效 2无效',
  PRIMARY KEY (`exam_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `exam` */

insert  into `exam`(`exam_id`,`exam_name`,`exam_desc`,`type`,`password`,`duration`,`start_time`,`end_time`,`total_score`,`pass_score`,`status`) values (9,'考试1','考试1',1,NULL,120,NULL,NULL,105,60,1),(10,'考试2','考试2',1,NULL,90,NULL,NULL,100,60,1),(11,'考试3','考试3',2,'123',90,NULL,NULL,100,60,1),(12,'考试4','考试4',1,NULL,120,NULL,NULL,100,60,1),(13,'考试5','考试5',1,NULL,120,'2022-11-02','2022-11-02',100,60,1),(14,'考试6','考试6',1,NULL,120,'2022-11-02','2022-11-02',100,60,1);

/*Table structure for table `exam_question` */

DROP TABLE IF EXISTS `exam_question`;

CREATE TABLE `exam_question` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question_ids` varchar(100) NOT NULL COMMENT '考试的题目id列表',
  `exam_id` int NOT NULL COMMENT '考试的id',
  `scores` varchar(100) NOT NULL COMMENT '每一题的分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `exam_question` */

insert  into `exam_question`(`id`,`question_ids`,`exam_id`,`scores`) values (5,'3,11,19,12,14,15,8,6,13,18,27,28,29,30,31',9,'10,10,10,10,10,10,10,10,10,10,1,1,1,1,1'),(6,'18,6,3,8',10,'20,20,30,30'),(7,'3,8,26,12',11,'30,30,30,10'),(8,'6,12,15,13,14,19,8,18,20,11',12,'10,10,10,10,10,10,10,10,10,10'),(9,'19,18,15,3,8',13,'10,20,10,30,30'),(10,'11,12,13,14,15,6,18,19,20,3',14,'10,10,10,10,10,10,10,5,5,20');

/*Table structure for table `exam_record` */

DROP TABLE IF EXISTS `exam_record`;

CREATE TABLE `exam_record` (
  `record_id` int NOT NULL AUTO_INCREMENT COMMENT '考试记录的id',
  `user_id` int NOT NULL COMMENT '考试用户的id',
  `user_answers` longtext NOT NULL COMMENT '用户的答案列表',
  `credit_img_url` longtext COMMENT '考试诚信截图',
  `exam_id` int NOT NULL COMMENT '考试的id',
  `logic_score` int DEFAULT NULL COMMENT '考试的逻辑得分(除简答)',
  `exam_time` datetime NOT NULL COMMENT '考试时间',
  `question_ids` varchar(150) NOT NULL COMMENT '考试的题目信息',
  `total_score` int DEFAULT NULL COMMENT '考试总分数 (逻辑+简答)',
  `error_question_ids` varchar(50) DEFAULT NULL COMMENT '用户考试的错题',
  PRIMARY KEY (`record_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `exam_record` */

insert  into `exam_record`(`record_id`,`user_id`,`user_answers`,`credit_img_url`,`exam_id`,`logic_score`,`exam_time`,`question_ids`,`total_score`,`error_question_ids`) values (1,2,'0-0-1-1-1-1,2-1,2,3-0-aaaaa-bbbb','',9,20,'2022-11-02 22:21:38','11,12,15,14,13,6,18,19,3,8',40,'12,15,14,13,6,18'),(2,2,'1-解析1-解析3-解析25','',11,10,'2022-11-02 19:52:06','12,3,8,26',100,NULL),(3,8,'0-0-0-0-0-0,1,2-0,2-0-解析1-解析3','',9,60,'2022-11-02 20:25:59','14,15,11,12,13,6,18,19,3,8',75,'12,18'),(4,8,'0-0-0-0-0,1,2-0,3-0-0-解析3','',12,60,'2022-11-02 20:27:59','12,14,11,15,6,18,20,19,8',70,'12,18'),(5,2,'1-0-0','',12,10,'2022-11-02 22:45:44','11,6,19',NULL,'11,6'),(6,2,'0-0-0-0-0-0-0-0-aaaa','',12,60,'2022-11-02 22:47:43','15,13,14,11,6,18,19,20,8',70,'6,18'),(7,1,' - - - - - - - - -','https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/b2e37examTakePhoto.png,https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/b76f7examTakePhoto.png',9,0,'2023-03-01 20:14:08','15,11,14,12,13,6,18,19,3,8',NULL,'15,11,14,12,13,6,18,19'),(8,1,' - - - - - - - - -','https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/ec95eexamTakePhoto.png',9,0,'2023-03-01 20:14:11','11,12,15,14,13,6,18,19,3,8',NULL,'11,12,15,14,13,6,18,19'),(9,4,' - - - - - - - - -','https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/d6e3aexamTakePhoto.png',9,0,'2023-03-01 20:15:46','13,11,12,15,14,6,18,19,3,8',NULL,'13,11,12,15,14,6,18,19'),(10,4,' - - - - - - - - -','https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/0291aexamTakePhoto.png,https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/c06ccexamTakePhoto.png',12,0,'2023-03-01 20:29:18','12,13,15,14,11,6,18,19,20,8',NULL,'12,13,15,14,11,6,18,19,20'),(11,4,' - - - - - - - - -','https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/5fcfaexamTakePhoto.png',12,0,'2023-03-01 20:29:32','12,15,13,14,11,6,18,19,20,8',NULL,'12,15,13,14,11,6,18,19,20');

/*Table structure for table `question` */

DROP TABLE IF EXISTS `question`;

CREATE TABLE `question` (
  `id` int NOT NULL AUTO_INCREMENT,
  `qu_content` longtext NOT NULL COMMENT '问题内容',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `create_person` varchar(50) NOT NULL COMMENT '创建人',
  `qu_type` int NOT NULL COMMENT '问题类型 1单选 2多选 3判断 4简答',
  `level` int NOT NULL DEFAULT '1' COMMENT '题目难度 1简单 2中等 3困难',
  `image` longtext COMMENT '图片',
  `qu_bank_id` varchar(40) NOT NULL COMMENT '所属题库id',
  `qu_bank_name` varchar(255) NOT NULL COMMENT '所属题库名称',
  `analysis` varchar(255) DEFAULT NULL COMMENT '解析',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `question` */

insert  into `question`(`id`,`qu_content`,`create_time`,`create_person`,`qu_type`,`level`,`image`,`qu_bank_id`,`qu_bank_name`,`analysis`) values (3,'题目1','2022-11-02 21:17:19','admin',4,2,NULL,'1,2,5','程序设计,数据结构,算法分析','解析1'),(6,'题目2','2022-11-02 10:11:28','admin',2,1,NULL,'1,5','程序设计,算法分析','解析2'),(8,'题目3','2022-11-02 21:17:30','admin',4,3,NULL,'1,5','程序设计,算法分析','解析3'),(11,'题目4','2022-11-02 21:12:52','admin',1,1,NULL,'5,2','算法分析,数据结构','解析4'),(12,'题目5','2022-11-02 15:32:44','admin',1,3,NULL,'1,5','程序设计,算法分析','解析5'),(13,'题目6','2022-11-02 15:13:38','admin',1,1,NULL,'1,5','程序设计,算法分析','解析6'),(14,'题目7','2022-11-02 16:17:09','admin',1,1,'','1,5','程序设计,算法分析','解析7'),(15,'题目8','2022-11-02 15:53:14','admin',1,1,'','1,5','程序设计,算法分析','解析8'),(18,'题目9','2022-11-02 10:09:13','admin',2,2,NULL,'1','程序设计','解析9'),(19,'题目10','2022-11-02 14:54:09','admin',3,1,NULL,'1','程序设计','解析10'),(20,'题目11','2022-11-02 20:26:49','admin',3,1,NULL,'1','程序设计','解析11'),(21,'题目12','2022-11-02 14:42:56','admin',2,1,NULL,'1,2','程序设计,数据结构','解析12'),(22,'题目21','2022-11-02 21:48:38','admin',1,1,NULL,'2','数据结构','解析21'),(23,'题目22','2022-11-02 21:50:18','tom',2,1,NULL,'1,5','程序设计,算法分析','解析22'),(24,'题目23','2022-11-02 21:52:32','tom',3,1,NULL,'2','数据结构','解析23'),(25,'题目24','2022-11-02 21:54:06','tom',2,1,NULL,'5','算法分析','解析24'),(26,'题目25','2022-11-02 19:45:33','admin',4,2,NULL,'2,5,1','数据结构,算法分析,程序设计','解析25'),(27,'findtime','2023-03-01 19:51:07','admin',1,1,'https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/213b9微信图片_20230206174802.png','1,2,5','程序设计,数据结构,算法分析','a'),(28,'阿松大','2023-03-01 19:51:42','admin',1,1,'https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/213b9微信图片_20230206174802.png','1,2,5','程序设计,数据结构,算法分析',''),(29,'食不食油饼啊','2023-03-01 19:52:23','admin',1,1,'https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/213b9微信图片_20230206174802.png','1,2,5','程序设计,数据结构,算法分析',''),(30,'额贼','2023-03-01 19:54:45','admin',1,1,'https://zzuoss.oss-cn-shanghai.aliyuncs.com/2023-03-01/c8008微信图片_20220613210732.png','1,2,5','程序设计,数据结构,算法分析',''),(31,'是不是第十二','2023-03-01 21:57:15','admin',1,1,NULL,'1,2,5','程序设计,数据结构,算法分析','是不是第十二个');

/*Table structure for table `question_bank` */

DROP TABLE IF EXISTS `question_bank`;

CREATE TABLE `question_bank` (
  `bank_id` int NOT NULL AUTO_INCREMENT,
  `bank_name` varchar(100) NOT NULL,
  PRIMARY KEY (`bank_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `question_bank` */

insert  into `question_bank`(`bank_id`,`bank_name`) values (1,'程序设计'),(2,'数据结构'),(5,'算法分析');

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_id` int NOT NULL DEFAULT '1' COMMENT '1(学生) 2(教师) 3(管理员)',
  `username` varchar(100) NOT NULL,
  `true_name` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `salt` varchar(30) NOT NULL,
  `status` int NOT NULL DEFAULT '1' COMMENT '用户是否被禁用 1正常 2禁用',
  `create_date` datetime NOT NULL COMMENT '用户创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `user` */

insert  into `user`(`id`,`role_id`,`username`,`true_name`,`password`,`salt`,`status`,`create_date`) values (1,3,'admin','管理员','9499273223c7aca5949e3055eaa57f6f','c667d6',1,'2022-11-02 15:05:15'),(2,1,'zhangsan','张三','9499273223c7aca5949e3055eaa57f6f','c667d6',1,'2022-11-02 10:46:25'),(3,2,'tom','汤姆','9499273223c7aca5949e3055eaa57f6f','c667d6',1,'2022-11-02 11:10:12'),(4,1,'zhaoliu','赵六','98d4fa4da534339cbed32b62d7b246c7','ccb14b',1,'2022-11-02 18:13:20'),(5,1,'mc','马冲','2a2176cb62f5a62d396dbb2bdeed294c','605d1c',1,'2022-11-02 15:51:51'),(6,1,'amao','阿毛','41b8d2eff6cd26fc425be6ab343de397','8e2880',2,'2022-11-02 15:52:30'),(8,1,'lisi','李四','6d0e86b7df3040a3de6b7dce73a7d818','8097b9',1,'2022-11-02 15:54:48'),(9,1,'wangwu','王五','4ba6f54cc49fac7afc907b0b5fbfd7ac','c6ce5f',1,'2022-11-02 15:55:04'),(10,1,'qiaoqi','乔七','62e9ce36958d0ee247a145d29caab889','9a9d8c',1,'2022-11-02 15:55:25'),(11,1,'pgl','陈八','f7dcc6093292b30c698ec73bca37a7a4','d6da4b',1,'2022-11-02 15:55:52'),(12,1,'wjh','孙九','c94de75b9de1b60e2c5261dfade215f2','33641a',1,'2022-11-02 09:54:55'),(13,3,'ll','丽丽','0b600b5d50de0f1207a2a045bc22debf','0824f0',1,'2022-11-02 10:02:09');

/*Table structure for table `user_role` */

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `id` int NOT NULL,
  `role_id` int NOT NULL DEFAULT '1' COMMENT '1学生 2教师 3超级管理员',
  `role_name` varchar(15) NOT NULL,
  `menu_info` longtext NOT NULL COMMENT '主页的菜单信息',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `user_role` */

insert  into `user_role`(`id`,`role_id`,`role_name`,`menu_info`) values (1,1,'学生','[{\"topMenuName\":\"系统介绍\",\"topIcon\":\"el-icon-odometer\",\"url\":\"/dashboard\"},{\"topMenuName\":\"在线考试\",\"topIcon\":\"el-icon-menu\",\"submenu\":[{\"name\":\"在线考试\",\"icon\":\"el-icon-s-promotion\",\"url\":\"/examOnline\"},{\"name\":\"我的成绩\",\"icon\":\"el-icon-trophy\",\"url\":\"/myGrade\"},{\"name\":\"我的题库\",\"icon\":\"el-icon-notebook-2\",\"url\":\"/myQuestionBank\"}]}]'),(2,2,'老师','[{\"topMenuName\":\"系统介绍\",\"topIcon\":\"el-icon-odometer\",\"url\":\"/dashboard\"},{\"topMenuName\":\"考试管理\",\"topIcon\":\"el-icon-bangzhu\",\"submenu\":[{\"name\":\"题库管理\",\"icon\":\"el-icon-aim\",\"url\":\"/questionBankMange\"},{\"name\":\"试题管理\",\"icon\":\"el-icon-news\",\"url\":\"/questionManage\"},{\"name\":\"考试管理\",\"icon\":\"el-icon-tickets\",\"url\":\"/examManage\"},{\"name\":\"阅卷管理\",\"icon\":\"el-icon-view\",\"url\":\"/markManage\"}]},{\"topMenuName\":\"考试统计\",\"topIcon\":\"el-icon-pie-chart\",\"submenu\":[{\"name\":\"统计总览\",\"icon\":\"el-icon-data-line\",\"url\":\"/staticOverview\"}]}]'),(3,3,'超级管理员','[{\"topMenuName\":\"系统介绍\",\"topIcon\":\"el-icon-odometer\",\"url\":\"/dashboard\"},{\"topMenuName\":\"考试管理\",\"topIcon\":\"el-icon-bangzhu\",\"submenu\":[{\"name\":\"题库管理\",\"icon\":\"el-icon-aim\",\"url\":\"/questionBankMange\"},{\"name\":\"试题管理\",\"icon\":\"el-icon-news\",\"url\":\"/questionManage\"},{\"name\":\"考试管理\",\"icon\":\"el-icon-tickets\",\"url\":\"/examManage\"},{\"name\":\"阅卷管理\",\"icon\":\"el-icon-view\",\"url\":\"/markManage\"}]},{\"topMenuName\":\"考试统计\",\"topIcon\":\"el-icon-pie-chart\",\"submenu\":[{\"name\":\"统计总览\",\"icon\":\"el-icon-data-line\",\"url\":\"/staticOverview\"}]},{\"topMenuName\":\"系统设置\",\"topIcon\":\"el-icon-setting\",\"submenu\":[{\"name\":\"角色管理\",\"icon\":\"el-icon-s-custom\",\"url\":\"/roleManage\"},{\"name\":\"用户管理\",\"icon\":\"el-icon-user-solid\",\"url\":\"/userManage\"}]}]');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;