22222

/*

SQLyog Ultimate v12.08 (64 bit)

MySQL - 8.0.30 : Database - 网络课程平台

*********************************************************************

*/

 

/*!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 /*!32312 IF NOT EXISTS*/`网络课程平台` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `网络课程平台`;

/*Table structure for table `chapter` */

DROP TABLE IF EXISTS `chapter`;

CREATE TABLE `chapter` (

  `chapterId` varchar(10) NOT NULL,

  `chapterName` varchar(10) DEFAULT NULL,

  `questionCount` int 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),('第八章','综合案例',NULL),('第六章','MySQL高级特性',NULL),('第四章','MySQL查询',NULL);

/*Table structure for table `class` */

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (

  `classId` int NOT NULL AUTO_INCREMENT,

  `className` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,

  `classSize` int DEFAULT NULL,

  `teacherId` int DEFAULT NULL,

  PRIMARY KEY (`classId`),

  UNIQUE KEY `className` (`className`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `class` */

insert  into `class`(`classId`,`className`,`classSize`,`teacherId`) values (1,'软工2101-2103班',NULL,1);

/*Table structure for table `exam` */

DROP TABLE IF EXISTS `exam`;

CREATE TABLE `exam` (

  `examId` int NOT NULL AUTO_INCREMENT,

  `chapterId` int DEFAULT NULL,

  `studentId` int DEFAULT NULL,

  `fromTime` date DEFAULT NULL,

  `toTime` date DEFAULT NULL,

  `state` varchar(10) DEFAULT NULL,

  `score` double DEFAULT NULL,

  PRIMARY KEY (`examId`)

) 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` int NOT NULL AUTO_INCREMENT,

  `examId` int DEFAULT NULL,

  `questionId` int DEFAULT NULL,

  `answer` varchar(150) DEFAULT NULL,

  `grade` double DEFAULT NULL,

  PRIMARY KEY (`exam_detailId`),

  UNIQUE KEY `answer` (`answer`)

) 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` int DEFAULT NULL,

  `fromchapterId` varchar(10) DEFAULT NULL,

  `tochapterId` varchar(10) DEFAULT NULL,

  `type` varchar(10) DEFAULT NULL,

  `difficulty` varchar(10) DEFAULT NULL,

  `count` int DEFAULT NULL

) 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 (NULL,'第一章','第一章','单选题','容易',6);

/*Table structure for table `experiment` */

DROP TABLE IF EXISTS `experiment`;

CREATE TABLE `experiment` (

  `experimentId` int NOT NULL AUTO_INCREMENT,

  `chapterId` int DEFAULT NULL,

  `studentId` int DEFAULT NULL,

  `fromTime` date DEFAULT NULL,

  `toTime` date DEFAULT NULL,

  `score` double DEFAULT NULL,

  PRIMARY KEY (`experimentId`)

) 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` int NOT NULL AUTO_INCREMENT,

  `experimentId` int DEFAULT NULL,

  `questionId` int DEFAULT NULL,

  `answer` varchar(150) DEFAULT NULL,

  `grade` double DEFAULT NULL,

  PRIMARY KEY (`experiment_detailId`)

) 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` int NOT NULL AUTO_INCREMENT,

  `chapterId` int DEFAULT NULL,

  `type` varchar(10) DEFAULT NULL,

  `difficulty` int DEFAULT NULL,

  `count` int DEFAULT NULL,

  PRIMARY KEY (`experiment_ruleId`)

) 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` int NOT NULL AUTO_INCREMENT,

  `questionContent` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,

  `questionAnswer` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,

  `type` varchar(10) DEFAULT NULL,

  `difficulty` int DEFAULT NULL,

  `chapterId` int DEFAULT NULL,

  PRIMARY KEY (`questionId`),

  UNIQUE KEY `questionContent` (`questionContent`,`questionAnswer`,`type`,`difficulty`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `questionbank` */

insert  into `questionbank`(`questionId`,`questionContent`,`questionAnswer`,`type`,`difficulty`,`chapterId`) values (1,'\n______表示一个新的事务处理块的开始 \nA、 START TRANSACTION \nB、 BEGIN TRANSACTION \nC、 BEGIN COMMIT \nD、 START COMMIT ','A','1',1,1),(2,'______函数通常用来计算累计排名、移动平均数和报表聚合等。 \nA、 汇总 \nB、 分析 \nC、 分组 \nD、 单行 ','A','1',1,1),(3,'______是实体属性。 \nA、 形状 \nB、 汽车 \nC、 盘子 \nD、 高铁 ','A','1',1,1),(4,'______是一个单一的逻辑工作单元。 \nA、 记录 \nB、 数据库 \nC、 事务 \nD、 字段 ','C','1',1,1),(5,'______子句用于查询列的唯一值。 \nA、 unique \nB、 distinct \nC、 order by \nD、 group by ','B','1',1,1),(6,'MySql数据库中,下面______可以作为有效的列名。 \nA、 Column \nB、 123_NUM \nC、 NUM_#123 \nD、 #NUM123 ','C','1',1,1),(7,'MySql数据库中,以下______命令可以删除整个表中的数据,并且无法回滚。 \nA、 drop \nB、 delete \nC、 truncate \nD、 cascade ','C','1',1,1),(8,'MySQL中,预设的、拥有最高权限超级用户的用户名为______ \nA、 test \nB、 Administrator \nC、 DA \nD、 root ','D','1',1,1),(9,'MySQL组织数据采用______ \nA、 层次模型 \nB、 网状模型 \nC、 关系模型 \nD、 数据模型 ','C','1',1,1),(10,'SELECT语句的完整语法较复杂,但至少包括的部分是______ \nA、 仅SELECT \nB、 SELECT,FROM \nC、 SELECT,GROUP \nD、 SELECT,INTO ','B','1',1,1),(11,'SQL 查询中去除重复数据的是______ \nA、 ORDER BY \nB、 GROUP BY \nC、 DISTINCT \nD、 DESC ','C','1',1,1),(12,'SQL是一种______语言。 \nA、 函数型 \nB、 高级算法 \nC、 关系数据库 \nD、 人工智能 ','C','1',1,1),(13,'SQL语句中______命令可以授予用户对象权限。 \nA、 REVOKE \nB、 GRANT \nC、 DENY \nD、 CREATE ','B','1',1,1),(14,'SQL语句中的条件用以下哪一项来表达______ \nA、 THEN \nB、 WHILE \nC、 WHERE \nD、 IF ','C','1',1,1),(15,'SQL语言的数据操纵语句包括SELECT、INSERT、UPDATE、DELETE等。其中最重要的,也是使用最频繁的语句是______。 \nA、 UPDATE \nB、 SELECT \nC、 DELETE \nD、 INSERT ','B','1',1,1),(16,'SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体,其中,CREATE、DROP、ALTER语句是实现哪种功能______ \n答案：数据定义 \nB、 数据控制 \nC、 数据定义 \nD、 数据查询 ','C','1',1,1),(17,'数据操纵语言中典型的四种语句是Insert、select 、update和____。 ','delete','2',1,1),(18,'事务处理控制语言中的Commit命令表示提交事务,而回退事务则用____命令表示。','rollback','2',1,1),(19,'PL/SQL基本语句块中的声明部分使用____关键词。 ','declare','2',1,1),(20,'查询数据表的内容,需要用到的sql命令为:____.(字母小写) ','select','2',1,1),(21,'在MySQL中,可以使用____(要求小写)语句来修改、更新一个表或多个表中的数据。 ','update','2',1,1),(22,'SQL语言是______的语言,轻易学习 。 ','非过程化','2',1,1),(23,'SQL语言中,删除一个视图的命令是______ ','drop','2',1,1),(24,'UNIQUE惟一索引的作用是______','保证各行在该索引上的值都不得重复','2',1,1),(25,'ORDER BY NAME DESC是指按照姓名______ ','降序','2',1,1);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

  `studentId` int NOT NULL AUTO_INCREMENT,

  `studentNUM` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,

  `studentName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,

  `studentPW` varchar(20) DEFAULT NULL,

  `classId` int DEFAULT NULL,

  PRIMARY KEY (`studentId`),

  UNIQUE KEY `studentName` (`studentName`)

) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `student` */

insert  into `student`(`studentId`,`studentNUM`,`studentName`,`studentPW`,`classId`) values (1,'191451081007','李东晁','191451081007',1),(2,'211451080101','卜哲珩','211451080101',1),(3,'211451080102','曹幸蕊','211451080102',1),(4,'211451080103','常家乐','211451080103',1),(5,'211451080104','陈杰宇','211451080104',1),(6,'211451080105','陈俊坦','211451080105',1),(7,'211451080106','陈梦阳','211451080106',1),(8,'211451080107','海启阳','211451080107',1),(9,'211451080108','韩奇超','211451080108',1),(10,'211451080109','候占东','211451080109',1),(11,'211451080110','胡子涵','211451080110',1),(12,'211451080111','贾金其','211451080111',1),(13,'211451080112','蒋飞宇','211451080112',1),(14,'211451080113','李军伟','211451080113',1),(15,'211451080114','李欣梦','211451080114',1),(16,'211451080115','林富佳','211451080115',1),(17,'211451080116','吕尚儒','211451080116',1),(18,'211451080117','牛奔腾','211451080117',1),(19,'211451080118','潘鑫','211451080118',1),(20,'211451080119','孙相龙','211451080119',1),(21,'211451080120','王方旭','211451080120',1),(22,'211451080121','王思琦','211451080121',1),(23,'211451080122','吴妍','211451080122',1),(24,'211451080123','邢耿','211451080123',1),(25,'211451080124','杨继坤','211451080124',1),(26,'211451080125','杨晶','211451080125',1),(27,'211451080126','杨舟','211451080126',1),(28,'211451080127','尹先澳','211451080127',1),(29,'211451080128','余家驹','211451080128',1),(30,'211451080129','张傲','211451080129',1),(31,'211451080130','张鼎','211451080130',1),(32,'211451080131','张钧航','211451080131',1),(33,'211451080132','张世凡','211451080132',1),(34,'211451080133','张天宇','211451080133',1),(35,'211451080134','张益恺','211451080134',1),(36,'211451080135','赵超群','211451080135',1),(37,'211451080136','闫家乐','211451080136',1),(38,'211451080137','晏澜','211451080137',1),(39,'201451081735','赵秀宇','201451081735',1),(40,'211451080201','蔡炎培','211451080201',1),(41,'211451080202','曹旭','211451080202',1),(42,'211451080203','曹永祥','211451080203',1),(43,'211451080204','陈佰飞','211451080204',1),(44,'211451080205','陈志昂','211451080205',1),(45,'211451080206','翟世聪','211451080206',1),(46,'211451080207','丁超强','211451080207',1),(47,'211451080208','丁路明','211451080208',1),(48,'211451080209','董畅','211451080209',1),(49,'211451080210','董思宇','211451080210',1),(50,'211451080211','杜坤璞','211451080211',1),(51,'211451080212','冯逸轩','211451080212',1),(52,'211451080213','郭龙浩','211451080213',1),(53,'211451080214','郭强','211451080214',1),(54,'211451080215','黄思晴','211451080215',1),(55,'211451080216','康国灿','211451080216',1),(56,'211451080217','康贺威','211451080217',1),(57,'211451080218','李健豪','211451080218',1),(58,'211451080219','梁鑫洋','211451080219',1),(59,'211451080220','刘民杰','211451080220',1),(60,'211451080221','刘迅','211451080221',1),(61,'211451080222','马敬雅','211451080222',1),(62,'211451080223','马旭','211451080223',1),(63,'211451080224','苗世朵','211451080224',1),(64,'211451080225','欧晨西','211451080225',1),(65,'211451080226','屈靖川','211451080226',1),(66,'211451080227','盛和友','211451080227',1),(67,'211451080228','王建军','211451080228',1),(68,'211451080229','王慕康','211451080229',1),(69,'211451080230','王一帆','211451080230',1),(70,'211451080231','王振赏','211451080231',1),(71,'211451080232','杨标','211451080232',1),(72,'211451080233','杨钍燃','211451080233',1),(73,'211451080234','张鹏','211451080234',1),(74,'211451080235','张泽同','211451080235',1),(75,'211451080236','祝仕威','211451080236',1),(76,'211451080237','左明哲','211451080237',1),(77,'211451080301','常廷凯','211451080301',1),(78,'211451080302','陈成豪','211451080302',1),(79,'211451080303','高维祯','211451080303',1),(80,'211451080304','郭佳佳','211451080304',1),(81,'211451080305','姜莱','211451080305',1),(82,'211451080306','冷宗儒','211451080306',1),(83,'211451080307','李春雨','211451080307',1),(84,'211451080308','李荣翔','211451080308',1),(85,'211451080309','李世隆','211451080309',1),(86,'211451080310','李允','211451080310',1),(87,'211451080311','刘杨','211451080311',1),(88,'211451080312','吕建宇','211451080312',1),(89,'211451080313','罗晓','211451080313',1),(90,'211451080314','马奕豪','211451080314',1),(91,'211451080316','邱田润','211451080316',1),(92,'211451080317','史朝旭','211451080317',1),(93,'211451080318','孙嘉译','211451080318',1),(94,'211451080319','孙佳鑫','211451080319',1),(95,'211451080320','田宏志','211451080320',1),(96,'211451080321','王祥林','211451080321',1),(97,'211451080322','王宇静','211451080322',1),(98,'211451080323','杨博文','211451080323',1),(99,'211451080324','杨晨','211451080324',1),(100,'211451080325','张宝加','211451080325',1),(101,'211451080326','张建党','211451080326',1),(102,'211451080327','张凯茜','211451080327',1),(103,'211451080328','张文辉','211451080328',1),(104,'211451080329','张文硕','211451080329',1),(105,'211451080330','张新疆','211451080330',1),(106,'211451080331','张宇','211451080331',1),(107,'211451080332','张钰伟','211451080332',1),(108,'211451080333','张钰哲','211451080333',1),(109,'211451080334','赵乾凯','211451080334',1),(110,'211451080335','周斐驰','211451080335',1),(111,'211451080336','左雨露','211451080336',1),(112,'211451080337','闫丰','211451080337',1);

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (

  `teacherId` int NOT NULL AUTO_INCREMENT,

  `teacherNUM` int DEFAULT NULL,

  `teacherName` varchar(10) DEFAULT NULL,

  `teacherPW` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`teacherId`),

  UNIQUE KEY `teacherName` (`teacherName`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `teacher` */

insert  into `teacher`(`teacherId`,`teacherNUM`,`teacherName`,`teacherPW`) values (1,1001,'刘老师','1001'),(2,100,'段老师','djx');

/*Table structure for table `work` */

DROP TABLE IF EXISTS `work`;

CREATE TABLE `work` (

  `workId` int NOT NULL AUTO_INCREMENT,

  `chapterId` int DEFAULT NULL,

  `studentId` int DEFAULT NULL,

  `fromTime` date DEFAULT NULL,

  `toTime` date DEFAULT NULL,

  `score` double DEFAULT NULL,

  PRIMARY KEY (`workId`)

) 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` int NOT NULL AUTO_INCREMENT,

  `workId` int DEFAULT NULL,

  `questionId` int DEFAULT NULL,

  `answer` varchar(150) DEFAULT NULL,

  `grade` double DEFAULT NULL,

  PRIMARY KEY (`work_detailId`),

  UNIQUE KEY `answer` (`answer`)

) 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` int NOT NULL AUTO_INCREMENT,

  `chapterId` varchar(10) DEFAULT NULL,

  `type` varchar(10) DEFAULT NULL,

  `difficulty` varchar(10) DEFAULT NULL,

  `count` int DEFAULT NULL,

  PRIMARY KEY (`work_ruleId`)

) ENGINE=InnoDB AUTO_INCREMENT=2 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 (1,'第一章','单选题','容易',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 ;

/* 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 `classstudent` */

DROP TABLE IF EXISTS `classstudent`;

/*!50001 DROP VIEW IF EXISTS `classstudent` */;

/*!50001 DROP TABLE IF EXISTS `classstudent` */;

/*!50001 CREATE TABLE  `classstudent`(

 `classId` int ,

 `className` varchar(20) ,

 `teacherId` int ,

 `studentId` int ,

 `studentName` varchar(20) ,

 `studentNUM` varchar(20) ,

 `studentPW` varchar(20)

)*/;

/*Table structure for table `examdetail` */

DROP TABLE IF EXISTS `examdetail`;

/*!50001 DROP VIEW IF EXISTS `examdetail` */;

/*!50001 DROP TABLE IF EXISTS `examdetail` */;

/*!50001 CREATE TABLE  `examdetail`(

 `examId` int ,

 `chapterId` int ,

 `studentId` int ,

 `fromTime` date ,

 `toTime` date ,

 `state` varchar(10) ,

 `score` double ,

 `exam_detailId` int ,

 `questionId` int ,

 `answer` varchar(150) ,

 `grade` double

)*/;

/*Table structure for table `experimentdetail` */

DROP TABLE IF EXISTS `experimentdetail`;

/*!50001 DROP VIEW IF EXISTS `experimentdetail` */;

/*!50001 DROP TABLE IF EXISTS `experimentdetail` */;

/*!50001 CREATE TABLE  `experimentdetail`(

 `experimentId` int ,

 `chapterId` int ,

 `studentId` int ,

 `fromTime` date ,

 `toTime` date ,

 `score` double ,

 `experiment_detailId` int ,

 `questionId` int ,

 `answer` varchar(150) ,

 `grade` double

)*/;

/*Table structure for table `studentchapterexperiment` */

DROP TABLE IF EXISTS `studentchapterexperiment`;

/*!50001 DROP VIEW IF EXISTS `studentchapterexperiment` */;

/*!50001 DROP TABLE IF EXISTS `studentchapterexperiment` */;

/*!50001 CREATE TABLE  `studentchapterexperiment`(

 `studentId` int ,

 `studentName` varchar(20) ,

 `chapterId` varchar(10) ,

 `chapterName` varchar(10) ,

 `experimentId` int ,

 `score` double

)*/;

/*Table structure for table `studentchapterwork` */

DROP TABLE IF EXISTS `studentchapterwork`;

/*!50001 DROP VIEW IF EXISTS `studentchapterwork` */;

/*!50001 DROP TABLE IF EXISTS `studentchapterwork` */;

/*!50001 CREATE TABLE  `studentchapterwork`(

 `studentId` int ,

 `studentName` varchar(20) ,

 `chapterId` varchar(10) ,

 `chapterName` varchar(10) ,

 `workId` int ,

 `score` double

)*/;

/*Table structure for table `studentexperiment` */

DROP TABLE IF EXISTS `studentexperiment`;

/*!50001 DROP VIEW IF EXISTS `studentexperiment` */;

/*!50001 DROP TABLE IF EXISTS `studentexperiment` */;

/*!50001 CREATE TABLE  `studentexperiment`(

 `studentId` int ,

 `studentName` varchar(20) ,

 `experimentId` int ,

 `score` double ,

 `fromTime` date ,

 `toTime` date

)*/;

/*Table structure for table `teacherclassstudent` */

DROP TABLE IF EXISTS `teacherclassstudent`;

/*!50001 DROP VIEW IF EXISTS `teacherclassstudent` */;

/*!50001 DROP TABLE IF EXISTS `teacherclassstudent` */;

/*!50001 CREATE TABLE  `teacherclassstudent`(

 `teacherId` int ,

 `teacherName` varchar(10) ,

 `classId` int ,

 `className` varchar(20) ,

 `studentName` varchar(20) ,

 `studentId` int

)*/;

/*Table structure for table `teacherstudentchapterexperiment` */

DROP TABLE IF EXISTS `teacherstudentchapterexperiment`;

/*!50001 DROP VIEW IF EXISTS `teacherstudentchapterexperiment` */;

/*!50001 DROP TABLE IF EXISTS `teacherstudentchapterexperiment` */;

/*!50001 CREATE TABLE  `teacherstudentchapterexperiment`(

 `experimentId` int ,

 `chapterId` varchar(10) ,

 `chapterName` varchar(10) ,

 `teacherId` int ,

 `teacherName` varchar(10)

)*/;

/*Table structure for table `teacherstudentchapterwork` */

DROP TABLE IF EXISTS `teacherstudentchapterwork`;

/*!50001 DROP VIEW IF EXISTS `teacherstudentchapterwork` */;

/*!50001 DROP TABLE IF EXISTS `teacherstudentchapterwork` */;

/*!50001 CREATE TABLE  `teacherstudentchapterwork`(

 `workId` int ,

 `fromTime` date ,

 `toTime` date ,

 `chapterId` varchar(10) ,

 `chapterName` varchar(10) ,

 `teacherId` int ,

 `teacherName` varchar(10)

)*/;

/*Table structure for table `teacherstudentexam` */

DROP TABLE IF EXISTS `teacherstudentexam`;

/*!50001 DROP VIEW IF EXISTS `teacherstudentexam` */;

/*!50001 DROP TABLE IF EXISTS `teacherstudentexam` */;

/*!50001 CREATE TABLE  `teacherstudentexam`(

 `teacherId` int ,

 `teacherName` varchar(10) ,

 `examId` int ,

 `state` varchar(10)

)*/;

/*Table structure for table `workdetail` */

DROP TABLE IF EXISTS `workdetail`;

/*!50001 DROP VIEW IF EXISTS `workdetail` */;

/*!50001 DROP TABLE IF EXISTS `workdetail` */;

/*!50001 CREATE TABLE  `workdetail`(

 `workId` int ,

 `chapterId` int ,

 `studentId` int ,

 `fromTime` date ,

 `toTime` date ,

 `score` double ,

 `work_detailId` int ,

 `questionId` int ,

 `answer` varchar(150) ,

 `grade` double

)*/;

/*View structure for view classstudent */

/*!50001 DROP TABLE IF EXISTS `classstudent` */;

/*!50001 DROP VIEW IF EXISTS `classstudent` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `classstudent` AS select `class`.`classId` AS `classId`,`class`.`className` AS `className`,`class`.`teacherId` AS `teacherId`,`student`.`studentId` AS `studentId`,`student`.`studentName` AS `studentName`,`student`.`studentNUM` AS `studentNUM`,`student`.`studentPW` AS `studentPW` from (`class` join `student`) where (`class`.`classId` = `student`.`classId`) */;

/*View structure for view examdetail */

/*!50001 DROP TABLE IF EXISTS `examdetail` */;

/*!50001 DROP VIEW IF EXISTS `examdetail` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `examdetail` AS select `exam`.`examId` AS `examId`,`exam`.`chapterId` AS `chapterId`,`exam`.`studentId` AS `studentId`,`exam`.`fromTime` AS `fromTime`,`exam`.`toTime` AS `toTime`,`exam`.`state` AS `state`,`exam`.`score` AS `score`,`exam_detail`.`exam_detailId` AS `exam_detailId`,`exam_detail`.`questionId` AS `questionId`,`exam_detail`.`answer` AS `answer`,`exam_detail`.`grade` AS `grade` from (`exam` join `exam_detail`) where (`exam`.`examId` = `exam_detail`.`examId`) */;

/*View structure for view experimentdetail */

/*!50001 DROP TABLE IF EXISTS `experimentdetail` */;

/*!50001 DROP VIEW IF EXISTS `experimentdetail` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `experimentdetail` AS select `experiment`.`experimentId` AS `experimentId`,`experiment`.`chapterId` AS `chapterId`,`experiment`.`studentId` AS `studentId`,`experiment`.`fromTime` AS `fromTime`,`experiment`.`toTime` AS `toTime`,`experiment`.`score` AS `score`,`experiment_detail`.`experiment_detailId` AS `experiment_detailId`,`experiment_detail`.`questionId` AS `questionId`,`experiment_detail`.`answer` AS `answer`,`experiment_detail`.`grade` AS `grade` from (`experiment` join `experiment_detail`) where (`experiment`.`experimentId` = `experiment_detail`.`experimentId`) */;

/*View structure for view studentchapterexperiment */

/*!50001 DROP TABLE IF EXISTS `studentchapterexperiment` */;

/*!50001 DROP VIEW IF EXISTS `studentchapterexperiment` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `studentchapterexperiment` AS select `student`.`studentId` AS `studentId`,`student`.`studentName` AS `studentName`,`chapter`.`chapterId` AS `chapterId`,`chapter`.`chapterName` AS `chapterName`,`experiment`.`experimentId` AS `experimentId`,`experiment`.`score` AS `score` from ((`student` join `chapter`) join `experiment`) where ((`student`.`studentId` = `experiment`.`studentId`) and (`experiment`.`chapterId` = `chapter`.`chapterId`)) */;

/*View structure for view studentchapterwork */

/*!50001 DROP TABLE IF EXISTS `studentchapterwork` */;

/*!50001 DROP VIEW IF EXISTS `studentchapterwork` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `studentchapterwork` AS select `student`.`studentId` AS `studentId`,`student`.`studentName` AS `studentName`,`chapter`.`chapterId` AS `chapterId`,`chapter`.`chapterName` AS `chapterName`,`work`.`workId` AS `workId`,`work`.`score` AS `score` from ((`chapter` join `student`) join `work`) where ((`student`.`studentId` = `work`.`studentId`) and (`work`.`chapterId` = `chapter`.`chapterId`)) */;

/*View structure for view studentexperiment */

/*!50001 DROP TABLE IF EXISTS `studentexperiment` */;

/*!50001 DROP VIEW IF EXISTS `studentexperiment` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `studentexperiment` AS select `student`.`studentId` AS `studentId`,`student`.`studentName` AS `studentName`,`experiment`.`experimentId` AS `experimentId`,`experiment`.`score` AS `score`,`experiment`.`fromTime` AS `fromTime`,`experiment`.`toTime` AS `toTime` from (`student` join `experiment`) where (`student`.`studentId` = `experiment`.`studentId`) */;

/*View structure for view teacherclassstudent */

/*!50001 DROP TABLE IF EXISTS `teacherclassstudent` */;

/*!50001 DROP VIEW IF EXISTS `teacherclassstudent` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `teacherclassstudent` AS select `teacher`.`teacherId` AS `teacherId`,`teacher`.`teacherName` AS `teacherName`,`classstudent`.`classId` AS `classId`,`classstudent`.`className` AS `className`,`classstudent`.`studentName` AS `studentName`,`classstudent`.`studentId` AS `studentId` from (`teacher` join `classstudent`) where (`teacher`.`teacherId` = `classstudent`.`teacherId`) */;

/*View structure for view teacherstudentchapterexperiment */

/*!50001 DROP TABLE IF EXISTS `teacherstudentchapterexperiment` */;

/*!50001 DROP VIEW IF EXISTS `teacherstudentchapterexperiment` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `teacherstudentchapterexperiment` AS select `experiment`.`experimentId` AS `experimentId`,`chapter`.`chapterId` AS `chapterId`,`chapter`.`chapterName` AS `chapterName`,`teacherclassstudent`.`teacherId` AS `teacherId`,`teacherclassstudent`.`teacherName` AS `teacherName` from ((`chapter` join `teacherclassstudent`) join `experiment`) where ((`chapter`.`chapterId` = `experiment`.`chapterId`) and (`experiment`.`studentId` = `teacherclassstudent`.`studentId`)) */;

/*View structure for view teacherstudentchapterwork */

/*!50001 DROP TABLE IF EXISTS `teacherstudentchapterwork` */;

/*!50001 DROP VIEW IF EXISTS `teacherstudentchapterwork` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `teacherstudentchapterwork` AS select `work`.`workId` AS `workId`,`work`.`fromTime` AS `fromTime`,`work`.`toTime` AS `toTime`,`chapter`.`chapterId` AS `chapterId`,`chapter`.`chapterName` AS `chapterName`,`teacherclassstudent`.`teacherId` AS `teacherId`,`teacherclassstudent`.`teacherName` AS `teacherName` from ((`chapter` join `teacherclassstudent`) join `work`) where ((`chapter`.`chapterId` = `work`.`chapterId`) and (`work`.`studentId` = `teacherclassstudent`.`studentId`)) */;

/*View structure for view teacherstudentexam */

/*!50001 DROP TABLE IF EXISTS `teacherstudentexam` */;

/*!50001 DROP VIEW IF EXISTS `teacherstudentexam` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `teacherstudentexam` AS select `teacherclassstudent`.`teacherId` AS `teacherId`,`teacherclassstudent`.`teacherName` AS `teacherName`,`exam`.`examId` AS `examId`,`exam`.`state` AS `state` from (`teacherclassstudent` join `exam`) where (`exam`.`studentId` = `teacherclassstudent`.`studentId`) */;

/*View structure for view workdetail */

/*!50001 DROP TABLE IF EXISTS `workdetail` */;

/*!50001 DROP VIEW IF EXISTS `workdetail` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `workdetail` AS select `work`.`workId` AS `workId`,`work`.`chapterId` AS `chapterId`,`work`.`studentId` AS `studentId`,`work`.`fromTime` AS `fromTime`,`work`.`toTime` AS `toTime`,`work`.`score` AS `score`,`work_detail`.`work_detailId` AS `work_detailId`,`work_detail`.`questionId` AS `questionId`,`work_detail`.`answer` AS `answer`,`work_detail`.`grade` AS `grade` from (`work` join `work_detail`) where (`work`.`workId` = `work_detail`.`workId`) */;

/*!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 */;