网络课程平台net_sjk2

CREATE DATABASE /*!32312 IF NOT EXISTS*/`net_sjk` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `net_sjk`;

/*Table structure for table `chapter` */

DROP TABLE IF EXISTS `chapter`;

CREATE TABLE `chapter` (
  `chapterId` VARCHAR(20) NOT NULL,
  `chapterName` VARCHAR(20) DEFAULT NULL,
  `questionCount` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`chapterId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `chapter` */

INSERT  INTO `chapter`(`chapterId`,`chapterName`,`questionCount`) VALUES ('第一章','MySQL概述',NULL),('第七章','索引',NULL),('第三章','MySQL常用操作',NULL),('第二章','数据类型',NULL),('第五章','MySQL函数和存储过程',NULL),('第八章','综合案例',NULL),('第六章','MySQL高级特性',NULL),('第四章','MySQL查询',NULL);

/*Table structure for table `class` */

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (
  `classId` VARCHAR(20) NOT NULL,
  `className` VARCHAR(20) DEFAULT NULL,
  `classSize` VARCHAR(20) DEFAULT NULL,
  `teacherId` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`classId`,`teacherId`),
  KEY `teacher_1` (`teacherId`),
  KEY `class_name` (`className`),
  CONSTRAINT `teacher_1` FOREIGN KEY (`teacherId`) REFERENCES `teacher` (`teacherId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `class` */

INSERT  INTO `class`(`classId`,`className`,`classSize`,`teacherId`) VALUES ('2101-2103','软工2101-2103班','120','001');

/*Table structure for table `exam` */

DROP TABLE IF EXISTS `exam`;

CREATE TABLE `exam` (
  `examId` VARCHAR(20) NOT NULL,
  `chapterId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `studentId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `fromTime` VARCHAR(20) DEFAULT NULL,
  `toTime` VARCHAR(20) DEFAULT NULL,
  `state` VARCHAR(20) DEFAULT NULL,
  `score` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`examId`,`chapterId`,`studentId`),
  KEY `exam_chapter_1` (`chapterId`),
  KEY `exam_student_1` (`studentId`),
  CONSTRAINT `exam_chapter_1` FOREIGN KEY (`chapterId`) REFERENCES `chapter` (`chapterId`),
  CONSTRAINT `exam_student_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`studentId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `exam` */

/*Table structure for table `exam_detail` */

DROP TABLE IF EXISTS `exam_detail`;

CREATE TABLE `exam_detail` (
  `exam_detailId` VARCHAR(20) NOT NULL,
  `examId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `questionId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `answer` VARCHAR(20) DEFAULT NULL,
  `grade` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`exam_detailId`,`examId`,`questionId`),
  KEY `ed_exam_1` (`examId`),
  KEY `examd_question_1` (`questionId`),
  KEY `ed_answer` (`answer`),
  CONSTRAINT `ed_exam_1` FOREIGN KEY (`examId`) REFERENCES `exam` (`examId`),
  CONSTRAINT `examd_question_1` FOREIGN KEY (`questionId`) REFERENCES `questionbank` (`questionId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `exam_detail` */

/*Table structure for table `exam_rule` */

DROP TABLE IF EXISTS `exam_rule`;

CREATE TABLE `exam_rule` (
  `exam_ruleId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `fromchapterId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `tochapterId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `type` VARCHAR(20) DEFAULT NULL,
  `difficulty` VARCHAR(20) DEFAULT NULL,
  `count` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`exam_ruleId`,`fromchapterId`,`tochapterId`),
  KEY `er_chapter_1` (`fromchapterId`),
  KEY `er_chapter_2` (`tochapterId`),
  CONSTRAINT `er_chapter_1` FOREIGN KEY (`fromchapterId`) REFERENCES `chapter` (`chapterId`),
  CONSTRAINT `er_chapter_2` FOREIGN KEY (`tochapterId`) REFERENCES `chapter` (`chapterId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `exam_rule` */

INSERT  INTO `exam_rule`(`exam_ruleId`,`fromchapterId`,`tochapterId`,`type`,`difficulty`,`count`) VALUES ('001','第一章','第一章','单选题','容易','6'),('002','第一章','第一章','填空题','容易','4');

/*Table structure for table `experiment` */

DROP TABLE IF EXISTS `experiment`;

CREATE TABLE `experiment` (
  `experimentId` VARCHAR(20) NOT NULL,
  `chapterId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `studentId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `fromTime` VARCHAR(20) DEFAULT NULL,
  `toTime` VARCHAR(20) DEFAULT NULL,
  `score` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`experimentId`,`chapterId`,`studentId`),
  KEY `experiment_chapter_1` (`chapterId`),
  KEY `experiment_student_1` (`studentId`),
  CONSTRAINT `experiment_chapter_1` FOREIGN KEY (`chapterId`) REFERENCES `chapter` (`chapterId`),
  CONSTRAINT `experiment_student_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`studentId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `experiment` */

/*Table structure for table `experiment_detail` */

DROP TABLE IF EXISTS `experiment_detail`;

CREATE TABLE `experiment_detail` (
  `experiment_detailId` VARCHAR(20) NOT NULL,
  `experimentId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `questionId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `answer` VARCHAR(20) DEFAULT NULL,
  `grade` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`experiment_detailId`,`experimentId`,`questionId`),
  KEY `ed_experiment_1` (`experimentId`),
  KEY `ed_question_1` (`questionId`),
  CONSTRAINT `ed_experiment_1` FOREIGN KEY (`experimentId`) REFERENCES `experiment` (`experimentId`),
  CONSTRAINT `ed_question_1` FOREIGN KEY (`questionId`) REFERENCES `questionbank` (`questionId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `experiment_detail` */

/*Table structure for table `experiment_rule` */

DROP TABLE IF EXISTS `experiment_rule`;

CREATE TABLE `experiment_rule` (
  `experiment_ruleId` VARCHAR(20) NOT NULL,
  `chapterId` VARCHAR(20) NOT NULL,
  `type` VARCHAR(20) DEFAULT NULL,
  `difficulty` VARCHAR(20) DEFAULT NULL,
  `count` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`experiment_ruleId`,`chapterId`),
  KEY `er-chapter-1` (`chapterId`),
  CONSTRAINT `er-chapter-1` FOREIGN KEY (`chapterId`) REFERENCES `chapter` (`chapterId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `experiment_rule` */

/*Table structure for table `questionbank` */

DROP TABLE IF EXISTS `questionbank`;

CREATE TABLE `questionbank` (
  `questionId` VARCHAR(20) NOT NULL,
  `questionContent` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `questionAnswer` VARCHAR(20) DEFAULT NULL,
  `type` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `difficulty` VARCHAR(20) DEFAULT NULL,
  `chapterId` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`questionId`,`chapterId`),
  KEY `zhangjie_1` (`chapterId`),
  KEY `qb_c` (`questionContent`),
  KEY `qb_a` (`questionAnswer`),
  KEY `qb_type` (`type`),
  KEY `qb_d` (`difficulty`),
  CONSTRAINT `zhangjie_1` FOREIGN KEY (`chapterId`) REFERENCES `chapter` (`chapterId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `questionbank` */

INSERT  INTO `questionbank`(`questionId`,`questionContent`,`questionAnswer`,`type`,`difficulty`,`chapterId`) VALUES ('001','【单选题】______表示一个新的事务处理块的开始 ','答案： A','【单选题】','难度：容易','第一章'),('002','______函数通常用来计算累计排名、移动平均数和报表聚合等。 ','答案： A','【单选题】','难度：容易','第一章'),('003','______是实体属性。 ','答案： A','【单选题】','难度：容易','第一章'),('004','______是一个单一的逻辑工作单元。 ','答案： C','【单选题】','难度：容易','第一章'),('005','______子句用于查询列的唯一值。 ','答案： B','【单选题】','难度：容易','第一章'),('006','MySql数据库中,下面______可以作为有效的列名。 ','答案： C','【单选题】','难度：容易','第一章'),('007','MySql数据库中,以下______命令可以删除整个表中的数据,并且无法回滚。 ','答案： C','【单选题】','难度：容易','第一章'),('008','MySQL中,预设的、拥有最高权限超级用户的用户名为______ ','答案： D','【单选题】','难度：容易','第一章'),('009','MySQL组织数据采用______ ','答案： C','【单选题】','难度：容易','第一章'),('010','SELECT语句的完整语法较复杂,但至少包括的部分是______ ','答案： B','【单选题】','难度：容易','第一章'),('011','SQL 查询中去除重复数据的是______ ','答案： C','【单选题】','难度：容易','第一章'),('012','SQL是一种______语言。 ','答案： C','【单选题】','难度：容易','第一章'),('013','SQL语句中______命令可以授予用户对象权限。 ','答案： B','【单选题】','难度：容易','第一章'),('014','SQL语句中的条件用以下哪一项来表达______ ','答案： C','【单选题】','难度：容易','第一章'),('015','SQL语言的数据操纵语句包括SELECT、INSERT、UPDATE、DELETE等。其中最重要的,也是使用最频繁的语句是______。 ','答案： B','【单选题】','难度：容易','第一章'),('016','SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体,其中,CREATE、DROP、ALTER语句是实现哪种功能______ ','答案： C','【单选题】','难度：容易','第一章'),('017','数据操纵语言中典型的四种语句是Insert、select 、update和____。 ','答案： delete；','【填空题】','难度：容易','第一章'),('018','事务处理控制语言中的Commit命令表示提交事务,而回退事务则用____命令表示。','答案： ROLLBACK；','【填空题】','难度：容易','第一章'),('019','PL/SQL基本语句块中的声明部分使用____关键词。','答案： DECLARE；','【填空题】','难度：容易','第一章'),('020','查询数据表的内容,需要用到的sql命令为:____.(字母小写) ','答案： select','【填空题】','难度：容易','第一章'),('021','在MySQL中,可以使用____(要求小写)语句来修改、更新一个表或多个表中的数据。','答案： update','【填空题】','难度：容易','第一章'),('022','SQL语言是______的语言,轻易学习 。','答案：非过程化','【填空题】','难度：容易','第一章'),('023','SQL语言中,删除一个视图的命令是______ ','答案：DROP','【填空题】','难度：容易','第一章'),('024','UNIQUE惟一索引的作用是______','答案：保证各行在该索引上的值都不得重复','【填空题】','难度：容易','第一章'),('025','ORDER BY NAME DESC是指按照姓名______','答案：降序','【填空题】','难度：容易','第一章');

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `studentId` VARCHAR(20) NOT NULL,
  `studentNUM` VARCHAR(20) DEFAULT NULL,
  `studentName` VARCHAR(20) DEFAULT NULL,
  `studentPW` VARCHAR(20) DEFAULT NULL,
  `classId` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`studentId`,`classId`),
  KEY `class_1` (`classId`),
  KEY `student_name` (`studentName`),
  CONSTRAINT `class_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `student` */

INSERT  INTO `student`(`studentId`,`studentNUM`,`studentName`,`studentPW`,`classId`) VALUES ('001','191451081007','李东晁','123456','2101-2103'),('002','211451080101','卜哲珩','123456','2101-2103'),('003','211451080102','曹幸蕊','123456','2101-2103'),('004','211451080103','常家乐','123456','2101-2103'),('005','211451080104','陈杰宇','123456','2101-2103'),('006','211451080105','陈俊坦','123456','2101-2103'),('007','211451080106','陈梦阳','123456','2101-2103'),('008','211451080107','海启阳','123456','2101-2103'),('009','211451080108','韩奇超','123456','2101-2103'),('010','211451080109','候占东','123456','2101-2103'),('011','211451080110','胡子涵','123456','2101-2103'),('012','211451080111','贾金其','123456','2101-2103'),('013','211451080112','蒋飞宇','123456','2101-2103'),('014','211451080113','李军伟','123456','2101-2103'),('015','211451080114','李欣梦','123456','2101-2103'),('016','211451080115','林富佳','123456','2101-2103'),('017','211451080116','吕尚儒','123456','2101-2103'),('018','211451080117','牛奔腾','123456','2101-2103'),('019','211451080118','潘鑫','123456','2101-2103'),('020','211451080119','孙相龙','123456','2101-2103'),('021','211451080120','王方旭','123456','2101-2103'),('022','211451080121','王思琦','123456','2101-2103'),('023','211451080122','吴妍','123456','2101-2103'),('024','211451080123','邢耿','123456','2101-2103'),('025','211451080124','杨继坤','123456','2101-2103'),('026','211451080125','杨晶','123456','2101-2103'),('027','211451080126','杨舟','123456','2101-2103'),('028','211451080127','尹先澳','123456','2101-2103'),('029','211451080128','余家驹','123456','2101-2103'),('030','211451080129','张傲','123456','2101-2103'),('031','211451080130','张鼎','123456','2101-2103'),('032','211451080131','张钧航','123456','2101-2103'),('033','211451080132','张世凡','123456','2101-2103'),('034','211451080133','张天宇','123456','2101-2103'),('035','211451080134','张益恺','123456','2101-2103'),('036','211451080135','赵超群','123456','2101-2103'),('037','211451080136','闫家乐','123456','2101-2103'),('038','211451080137','晏澜','123456','2101-2103'),('039','201451081735','赵秀宇','123456','2101-2103'),('040','211451080201','蔡炎培','123456','2101-2103'),('041','211451080202','曹旭','123456','2101-2103'),('042','211451080203','曹永祥','123456','2101-2103'),('043','211451080204','陈佰飞','123456','2101-2103'),('044','211451080205','陈志昂','123456','2101-2103'),('045','211451080206','翟世聪','123456','2101-2103'),('046','211451080207','丁超强','123456','2101-2103'),('047','211451080208','丁路明','123456','2101-2103'),('048','211451080209','董畅','123456','2101-2103'),('049','211451080210','董思宇','123456','2101-2103'),('050','211451080211','杜坤璞','123456','2101-2103'),('051','211451080212','冯逸轩','123456','2101-2103'),('052','211451080213','郭龙浩','123456','2101-2103'),('053','211451080214','郭强','123456','2101-2103'),('054','211451080215','黄思晴','123456','2101-2103'),('055','211451080216','康国灿','123456','2101-2103'),('056','211451080217','康贺威','123456','2101-2103'),('057','211451080218','李健豪','123456','2101-2103'),('058','211451080219','梁鑫洋','123456','2101-2103'),('059','211451080220','刘民杰','123456','2101-2103'),('060','211451080221','刘迅','123456','2101-2103'),('061','211451080222','马敬雅','123456','2101-2103'),('062','211451080223','马旭','123456','2101-2103'),('063','211451080224','苗世朵','123456','2101-2103'),('064','211451080225','欧晨西','123456','2101-2103'),('065','211451080226','屈靖川','123456','2101-2103'),('066','211451080227','盛和友','123456','2101-2103'),('067','211451080228','王建军','123456','2101-2103'),('068','211451080229','王慕康','123456','2101-2103'),('069','211451080230','王一帆','123456','2101-2103'),('070','211451080231','王振赏','123456','2101-2103'),('071','211451080232','杨标','123456','2101-2103'),('072','211451080233','杨钍燃','123456','2101-2103'),('073','211451080234','张鹏','123456','2101-2103'),('074','211451080235','张泽同','123456','2101-2103'),('075','211451080236','祝仕威','123456','2101-2103'),('076','211451080237','左明哲','123456','2101-2103'),('077','211451080301','常廷凯','123456','2101-2103'),('078','211451080302','陈成豪','123456','2101-2103'),('079','211451080303','高维祯','123456','2101-2103'),('080','211451080304','郭佳佳','123456','2101-2103'),('081','211451080305','姜莱','123456','2101-2103'),('082','211451080306','冷宗儒','123456','2101-2103'),('083','211451080307','李春雨','123456','2101-2103'),('084','211451080308','李荣翔','123456','2101-2103'),('085','211451080309','李世隆','123456','2101-2103'),('086','211451080310','李允','123456','2101-2103'),('087','211451080311','刘杨','123456','2101-2103'),('088','211451080312','吕建宇','123456','2101-2103'),('089','211451080313','罗晓','123456','2101-2103'),('090','211451080314','马奕豪','123456','2101-2103'),('091','211451080316','邱田润','123456','2101-2103'),('092','211451080317','史朝旭','123456','2101-2103'),('093','211451080318','孙嘉译','123456','2101-2103'),('094','211451080319','孙佳鑫','123456','2101-2103'),('095','211451080320','田宏志','123456','2101-2103'),('096','211451080321','王祥林','123456','2101-2103'),('097','211451080322','王宇静','123456','2101-2103'),('098','211451080323','杨博文','123456','2101-2103'),('099','211451080324','杨晨','123456','2101-2103'),('100','211451080325','张宝加','123456','2101-2103'),('101','211451080326','张建党','123456','2101-2103'),('102','211451080327','张凯茜','123456','2101-2103'),('103','211451080328','张文辉','123456','2101-2103'),('104','211451080329','张文硕','123456','2101-2103'),('105','211451080330','张新疆','123456','2101-2103'),('106','211451080331','张宇','123456','2101-2103'),('107','211451080332','张钰伟','123456','2101-2103'),('108','211451080333','张钰哲','123456','2101-2103'),('109','211451080334','赵乾凯','123456','2101-2103'),('110','211451080335','周斐驰','123456','2101-2103'),('111','211451080336','左雨露','123456','2101-2103'),('112','211451080337','闫丰','123456','2101-2103');

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `teacherId` VARCHAR(20) NOT NULL,
  `teacherNUM` VARCHAR(20) DEFAULT NULL,
  `teacherName` VARCHAR(20) DEFAULT NULL,
  `teacherPW` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`teacherId`),
  KEY `teacher_name` (`teacherName`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `teacher` */

INSERT  INTO `teacher`(`teacherId`,`teacherNUM`,`teacherName`,`teacherPW`) VALUES ('001','1001','刘老师','1001');

/*Table structure for table `work` */

DROP TABLE IF EXISTS `work`;

CREATE TABLE `work` (
  `workId` VARCHAR(20) NOT NULL,
  `chapterId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `studentId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `fromTime` VARCHAR(20) DEFAULT NULL,
  `toTime` VARCHAR(20) DEFAULT NULL,
  `score` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`workId`,`chapterId`,`studentId`),
  KEY `work_chapter_1` (`chapterId`),
  KEY `work_student_1` (`studentId`),
  CONSTRAINT `work_chapter_1` FOREIGN KEY (`chapterId`) REFERENCES `chapter` (`chapterId`),
  CONSTRAINT `work_student_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`studentId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `work` */

/*Table structure for table `work_detail` */

DROP TABLE IF EXISTS `work_detail`;

CREATE TABLE `work_detail` (
  `work_detailId` VARCHAR(20) NOT NULL,
  `workId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `questionId` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `answer` VARCHAR(20) DEFAULT NULL,
  `grade` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`work_detailId`,`workId`,`questionId`),
  KEY `wd_question_1` (`questionId`),
  KEY `wd_work_1` (`workId`),
  KEY `wd_answer` (`answer`),
  CONSTRAINT `wd_question_1` FOREIGN KEY (`questionId`) REFERENCES `questionbank` (`questionId`),
  CONSTRAINT `wd_work_1` FOREIGN KEY (`workId`) REFERENCES `work` (`workId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `work_detail` */

/*Table structure for table `work_rule` */

DROP TABLE IF EXISTS `work_rule`;

CREATE TABLE `work_rule` (
  `work_ruleId` VARCHAR(20) NOT NULL,
  `chapterId` VARCHAR(20) NOT NULL,
  `type` VARCHAR(20) NOT NULL,
  `difficulty` VARCHAR(20) DEFAULT NULL,
  `count` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`work_ruleId`,`chapterId`),
  KEY `work-chapter-1` (`chapterId`),
  KEY `work-qb-1` (`type`),
  CONSTRAINT `work-chapter-1` FOREIGN KEY (`chapterId`) REFERENCES `chapter` (`chapterId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `work_rule` */

INSERT  INTO `work_rule`(`work_ruleId`,`chapterId`,`type`,`difficulty`,`count`) VALUES ('001','第一章','【单选题】','容易','10');

/* Trigger structure for table `questionbank` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `update_questionCount0` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `update_questionCount0` AFTER INSERT ON `questionbank` FOR EACH ROW BEGIN
    UPDATE `chapter` SET `chapter`.`questionCount` = `chapter`.`questionCount` + 1 WHERE chapterid = new.chapterid;
    END */$$

DELIMITER ;

/* Trigger structure for table `questionbank` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `update_questionCount1` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `update_questionCount1` BEFORE DELETE ON `questionbank` FOR EACH ROW BEGIN
    UPDATE `chapter` SET `chapter`.`questionCount` = `chapter`.`questionCount` - 1 WHERE chapterid = old.chapterid;
    END */$$

DELIMITER ;

/* Trigger structure for table `student` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `update_classsize0` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `update_classsize0` AFTER INSERT ON `student` FOR EACH ROW BEGIN
    UPDATE `class` SET `class`.`classsize` = `class`.`classsize` + 1 WHERE classid = new.classid;
    END */$$

DELIMITER ;

/* Trigger structure for table `student` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `update_classsize1` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `update_classsize1` BEFORE DELETE ON `student` FOR EACH ROW BEGIN
    UPDATE `class` SET `class`.`classsize` = `class`.`classsize` - 1 WHERE classid = old.classid;
    END */$$

DELIMITER ;

/* Function  structure for function  `checkpw` */

/*!50003 DROP FUNCTION IF EXISTS `checkpw` */;
DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `checkpw`(pw0 VARCHAR(20)) RETURNS int
BEGIN
    DECLARE flag INT;
    DECLARE ls_i INT;
    DECLARE ls_str VARCHAR(20);
    SET flag = 1;
    IF (LENGTH(pw0)<6 ) OR (LOCATE(pw0,'01234567890123456789876543210987654321')>0)  THEN 
        SET flag = 2;
    ELSE
        SET ls_i = 1;
        SET ls_str = '';
        WHILE (ls_i<=LENGTH(pw0)) DO
            IF(LOCATE(MID(pw0,ls_i,1),ls_str)<=0) THEN 
                SET ls_str = CONCAT(ls_str,MID(pw0,ls_i,1));
            END IF ;
            SET ls_i = ls_i + 1;
        END WHILE ;
        IF LENGTH(ls_str)<3 THEN 
            SET flag = 2;
        END IF;
    END IF ;
    RETURN flag;    
END */$$
DELIMITER ;

/* Procedure structure for procedure `insert_class_exam` */

/*!50003 DROP PROCEDURE IF EXISTS  `insert_class_exam` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_class_exam`(
    IN classid0 INT
    )
BEGIN
    DECLARE examid0 INT;
    DECLARE stuid0 INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT studentid FROM student WHERE classid=classid0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    FETCH cur INTO stuid0;
    WHILE(NOT done) DO
        CALL insert_stu_exam (stuid0);
        FETCH cur INTO stuid0;
    END WHILE;    
    CLOSE cur;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `insert_student` */

/*!50003 DROP PROCEDURE IF EXISTS  `insert_student` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_student`(
    IN num0 VARCHAR(20),
    IN name0 VARCHAR(20),
    IN pw0 VARCHAR(20),
    IN classid0 INT
        )
BEGIN
    DECLARE count1 INT;
    SELECT COUNT(*) INTO count1 FROM student WHERE studentnum=num0;
    IF count1 = 0 THEN 
        INSERT INTO student(studentnum,studentname,studentpw,classid)VALUES(num0,name0,pw0,classid0);
    END IF; 
    END */$$
DELIMITER ;

/* Procedure structure for procedure `insert_student1` */

/*!50003 DROP PROCEDURE IF EXISTS  `insert_student1` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_student1`(
    IN stustr VARCHAR(5000)
    )
BEGIN
    DECLARE str1 VARCHAR(200);
    DECLARE num0 VARCHAR(20);
    DECLARE name0 VARCHAR(20);
    DECLARE pw0 VARCHAR(20);
    DECLARE classid0 INT;
    DECLARE flag INT;
    WHILE stustr > '' DO
        SET str1 = MID(stustr,1,LOCATE(';',stustr)-1);
        SET num0 = MID(str1,1,LOCATE(',',str1)-1);
        SET str1 = MID(str1,LOCATE(',',str1)+1);
        SET name0 = MID(str1,1,LOCATE(',',str1)-1);
        SET str1 = MID(str1,LOCATE(',',str1)+1);
        SET pw0 = MID(str1,1,LOCATE(',',str1)-1);
        SET classid0 = CAST(MID(str1,LOCATE(',',str1)+1)  AS SIGNED); 
        CALL insert_struent(num0,name0,pw0,classid0);
    
        SET stustr = MID(stustr,LOCATE(';',stustr)+1);
    END WHILE;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `insert_stu_exam` */

/*!50003 DROP PROCEDURE IF EXISTS  `insert_stu_exam` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_stu_exam`(
    IN studentid0 INT
    )
BEGIN
    DECLARE examid0 INT;
    DECLARE fromc0 INT;
    DECLARE toc0 INT;
    DECLARE type0 INT;
    DECLARE diff0 INT;
    DECLARE c0 INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT `fromchapterid`,`tochapterid`,`type`,`difficulty`,`count` FROM exam_rule ORDER BY exam_ruleid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SELECT COUNT(*) INTO c0 FROM exam WHERE studentid=studentid0;
    IF c0=0 THEN 
        INSERT INTO exam(studentid,state,score)VALUES(studentid0,0,0);
        SELECT LAST_INSERT_ID() INTO examid0;
        OPEN cur;
        FETCH cur INTO fromc0,toc0,type0,diff0,c0;
        WHILE(NOT done) DO
            INSERT INTO exam_detail(examid,questionid,answer,grade)SELECT examid0 AS examid,questionid,'' AS answer,0 AS grade FROM questionbank WHERE chapterid>=fromc0 AND chapterid<=toc0 AND `type`=type0 AND difficulty=diff0 ORDER BY RAND() LIMIT c0;
            FETCH cur INTO fromc0,toc0,type0,diff0,c0;
        END WHILE;    
        CLOSE cur;
    END IF ;
END */$$
DELIMITER ;

/* Procedure structure for procedure `insert_teacher` */

/*!50003 DROP PROCEDURE IF EXISTS  `insert_teacher` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_teacher`(
    IN num0 VARCHAR(20),
    IN name0 VARCHAR(20),
    IN pw0 VARCHAR(20)
        )
BEGIN
    DECLARE count0 INT;
    SELECT COUNT(*) INTO count0 FROM teacher WHERE teachernum=num0;
    IF count0 = 0 THEN 
        INSERT INTO teacher(teachernum,teachername,teacherpw)VALUES(num0,name0,pw0);
    END IF; 
    END */$$
DELIMITER ;

/* Procedure structure for procedure `login` */

/*!50003 DROP PROCEDURE IF EXISTS  `login` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(
    IN num0 VARCHAR(20),
    IN pw0 VARCHAR(20)
        )
BEGIN
    DECLARE count0 INT;
    SELECT COUNT(*) INTO count0 FROM student WHERE studentnum=num0 AND studentpw=pw0;
    IF count0 = 1 THEN 
        SET count0 = checkpw(pw0);
    ELSE
        SET count0 = 0;
    END IF ;
     
    SELECT count0 AS count0;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `proc1` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc1` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(
    IN param1 INT,
    IN param2 VARCHAR(2000)
        )
BEGIN
    DECLARE num0 VARCHAR(20);
    DECLARE name0 VARCHAR(20);
    DECLARE pw0 VARCHAR(20);
    DECLARE classid0 INT;
    DECLARE count0 INT;
    CASE param1
        WHEN 1 THEN  -- 插入教师；
            SET num0 = MID(param2,1,LOCATE('|',param2)-1);
            SET param2 = MID(param2,LOCATE('|',param2)+1);
            SET name0 = MID(param2,1,LOCATE('|',param2)-1);
            SET pw0 = MID(param2,LOCATE('|',param2)+1);
            SELECT COUNT(*) INTO count0 FROM teacher WHERE teachernum = num0;
            IF count0 = 0 THEN
                INSERT INTO teacher(teachernum,teachername,teacherpw)VALUES(num0,name0,pw0);
            END IF ;
            
        WHEN 2 THEN  -- 插入学生
            SET num0 = MID(param2,1,LOCATE('|',param2)-1);
            SET param2 = MID(param2,LOCATE('|',param2)+1);
            SET name0 = MID(param2,1,LOCATE('|',param2)-1);
            SET param2 = MID(param2,LOCATE('|',param2)+1);
            SET pw0 = MID(param2,1,LOCATE('|',param2)-1);
            SET classid0 = CAST(MID(param2,LOCATE('|',param2)+1) AS SIGNED);
            SELECT COUNT(*) INTO count0 FROM student WHERE studentnum = num0;
            IF count0 = 0 THEN
                INSERT INTO student(studentnum,studentname,studentpw,classid)VALUES(num0,name0,pw0,classid0);            
            END IF; 
            
        WHEN 3 THEN  -- 学生帐号查询
            SELECT studentnum,studentname,studentpw,classid,classname FROM v_class_student WHERE studentnum=param2;
            
        WHEN 4 THEN  -- 按姓名查询
            SELECT studentnum,studentname,studentpw,classid,classname FROM v_class_student WHERE studentname LIKE CONCAT('%', param2, '%');
    END CASE;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `select_student` */

/*!50003 DROP PROCEDURE IF EXISTS  `select_student` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `select_student`(
    IN param1 VARCHAR(20)
        )
BEGIN
    SELECT studentnum,studentname,studentpw,classid FROM student WHERE studentnum=param1;
END */$$
DELIMITER ;

/* Procedure structure for procedure `select_student1` */

/*!50003 DROP PROCEDURE IF EXISTS  `select_student1` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `select_student1`(
    IN name0 VARCHAR(20)
        )
BEGIN
    SELECT studentnum,studentname,studentpw,classid,classname FROM v_class_student WHERE studentname LIKE CONCAT('%', name0, '%');
END */$$
DELIMITER ;

/*Table structure for table `class_student_view` */

DROP TABLE IF EXISTS `class_student_view`;

/*!50001 DROP VIEW IF EXISTS `class_student_view` */;
/*!50001 DROP TABLE IF EXISTS `class_student_view` */;

/*!50001 CREATE TABLE  `class_student_view`(
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) ,
 `className` varchar(20) ,
 `classSize` varchar(20) ,
 `teacherId` varchar(20) 
)*/;

/*Table structure for table `homework_view` */

DROP TABLE IF EXISTS `homework_view`;

/*!50001 DROP VIEW IF EXISTS `homework_view` */;
/*!50001 DROP TABLE IF EXISTS `homework_view` */;

/*!50001 CREATE TABLE  `homework_view`(
 `workId` varchar(20) ,
 `chapterId` varchar(20) ,
 `studentId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) ,
 `work_detailId` varchar(20) ,
 `questionId` varchar(20) ,
 `answer` varchar(20) ,
 `grade` varchar(20) 
)*/;

/*Table structure for table `teacher_student_view` */

DROP TABLE IF EXISTS `teacher_student_view`;

/*!50001 DROP VIEW IF EXISTS `teacher_student_view` */;
/*!50001 DROP TABLE IF EXISTS `teacher_student_view` */;

/*!50001 CREATE TABLE  `teacher_student_view`(
 `teacherId` varchar(20) ,
 `teacherNUM` varchar(20) ,
 `teacherName` varchar(20) ,
 `teacherPW` varchar(20) ,
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) 
)*/;

/*Table structure for table `experiment_view` */

DROP TABLE IF EXISTS `experiment_view`;

/*!50001 DROP VIEW IF EXISTS `experiment_view` */;
/*!50001 DROP TABLE IF EXISTS `experiment_view` */;

/*!50001 CREATE TABLE  `experiment_view`(
 `experimentId` varchar(20) ,
 `chapterId` varchar(20) ,
 `studentId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) ,
 `experiment_detailId` varchar(20) ,
 `questionId` varchar(20) ,
 `answer` varchar(20) ,
 `grade` varchar(20) 
)*/;

/*Table structure for table `exam_view` */

DROP TABLE IF EXISTS `exam_view`;

/*!50001 DROP VIEW IF EXISTS `exam_view` */;
/*!50001 DROP TABLE IF EXISTS `exam_view` */;

/*!50001 CREATE TABLE  `exam_view`(
 `examId` varchar(20) ,
 `chapterId` varchar(20) ,
 `studentId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `state` varchar(20) ,
 `score` varchar(20) ,
 `exam_detailId` varchar(20) ,
 `questionId` varchar(20) ,
 `answer` varchar(20) ,
 `grade` varchar(20) 
)*/;

/*Table structure for table `student_work_view` */

DROP TABLE IF EXISTS `student_work_view`;

/*!50001 DROP VIEW IF EXISTS `student_work_view` */;
/*!50001 DROP TABLE IF EXISTS `student_work_view` */;

/*!50001 CREATE TABLE  `student_work_view`(
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) ,
 `chapterName` varchar(20) ,
 `questionCount` varchar(20) ,
 `workId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) 
)*/;

/*Table structure for table `student_expriment_view` */

DROP TABLE IF EXISTS `student_expriment_view`;

/*!50001 DROP VIEW IF EXISTS `student_expriment_view` */;
/*!50001 DROP TABLE IF EXISTS `student_expriment_view` */;

/*!50001 CREATE TABLE  `student_expriment_view`(
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) ,
 `chapterName` varchar(20) ,
 `questionCount` varchar(20) ,
 `chapterId` varchar(20) ,
 `experimentId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) 
)*/;

/*Table structure for table `student_exam_view` */

DROP TABLE IF EXISTS `student_exam_view`;

/*!50001 DROP VIEW IF EXISTS `student_exam_view` */;
/*!50001 DROP TABLE IF EXISTS `student_exam_view` */;

/*!50001 CREATE TABLE  `student_exam_view`(
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) ,
 `examId` varchar(20) ,
 `chapterId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) ,
 `state` varchar(20) 
)*/;

/*Table structure for table `tands_chapter_work_view` */

DROP TABLE IF EXISTS `tands_chapter_work_view`;

/*!50001 DROP VIEW IF EXISTS `tands_chapter_work_view` */;
/*!50001 DROP TABLE IF EXISTS `tands_chapter_work_view` */;

/*!50001 CREATE TABLE  `tands_chapter_work_view`(
 `teacherId` varchar(20) ,
 `teacherNUM` varchar(20) ,
 `teacherName` varchar(20) ,
 `teacherPW` varchar(20) ,
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) ,
 `chapterName` varchar(20) ,
 `questionCount` varchar(20) ,
 `chapterId` varchar(20) ,
 `workId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) 
)*/;

/*Table structure for table `tands_chapter_experiment` */

DROP TABLE IF EXISTS `tands_chapter_experiment`;

/*!50001 DROP VIEW IF EXISTS `tands_chapter_experiment` */;
/*!50001 DROP TABLE IF EXISTS `tands_chapter_experiment` */;

/*!50001 CREATE TABLE  `tands_chapter_experiment`(
 `teacherId` varchar(20) ,
 `teacherNUM` varchar(20) ,
 `teacherName` varchar(20) ,
 `teacherPW` varchar(20) ,
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) ,
 `chapterName` varchar(20) ,
 `questionCount` varchar(20) ,
 `chapterId` varchar(20) ,
 `experimentId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) 
)*/;

/*Table structure for table `tands_exam` */

DROP TABLE IF EXISTS `tands_exam`;

/*!50001 DROP VIEW IF EXISTS `tands_exam` */;
/*!50001 DROP TABLE IF EXISTS `tands_exam` */;

/*!50001 CREATE TABLE  `tands_exam`(
 `teacherId` varchar(20) ,
 `teacherNUM` varchar(20) ,
 `teacherName` varchar(20) ,
 `teacherPW` varchar(20) ,
 `studentId` varchar(20) ,
 `studentNUM` varchar(20) ,
 `studentName` varchar(20) ,
 `studentPW` varchar(20) ,
 `classId` varchar(20) ,
 `chapterId` varchar(20) ,
 `examId` varchar(20) ,
 `fromTime` varchar(20) ,
 `toTime` varchar(20) ,
 `score` varchar(20) ,
 `state` varchar(20) 
)*/;