实验三和四作业

实验三：项目名称：网络课程平台（1）

• 实现的功能：

（一）学生用户

1. 课程作业
2. 课程实验
3. 课程考试

（二）教师用户：

1. 课程的章节管理
2. 课程的题库管理
3. 教师管理
4. 班级管理
5. 学生管理
6. 作业管理
7. 实验管理
8. 考试管理

• ER图

• 需要建立的表：

课程章节表：

chapter(chapterId,chapterName,questionCount)

课程题库表：

questionBank(questionId,questionContent,questionAnswer,type,difficulty,chapterId)

教师表：

teacher(teacherId,teacherNUM,teacherName,teacherPW)

班级表：

class(classId,className,classSize,teacherId)

学生表：

student(studentId,studentNUM,studentName,studentPW,classId)

作业规则表：

work_rule(work_ruleId,chapterId,type,difficulty,count)

实验规则表：

experiment_rule(experiment_ruleId,chapterId,type,difficulty,count)

试卷规则表：

exam_rule(experiment_ruleId,fromchapterId,tochapterId,type,difficulty,count)

课程作业总表：

work(workId,chapterId,studentId,fromTime,toTime,score)

课程作业明细表：

work_detail(work_detailId,workId,questionId,answer,grade)

课程实验总表：

experiment(experimentId,chapterId,studentId,fromTime,toTime,score)

课程实验明细表：

experiment_detail(experiment_detailId,experimentId,questionId,answer,grade)

课程试卷总表：

exam(examId,chapterId,studentId,fromTime,toTime,state,score)

课程试卷明细表：

exam_detail(exam_detailId,examId,questionId,answer,grade)

• 建立索引：

教师表和学生表的姓名索引

题库表的试题内容、试题答案、题型、难度索引

班级表的班级名称索引

作业明细表的答案索引

课程试卷总表的答案索引

• 建立视图：

班级学生视图（班级表和学生表）

教师学生视图（教师表和班级学生视图）

作业视图（作业总表和作业明细表）

实验视图（实验总表和实验明细表）

试卷视图（试卷总表和试卷明细表）

学生作业视图（学生表、章节表和作业表）

学生实验视图（学生表、章节表和实验表）

学生试卷视图（学生表和实验表）

教师作业视图（教师学生视图、章节表和作业表）

教师实验视图（教师学生视图、章节表和实验表）

教师试卷视图（教师学生视图和试卷表）

• 插入记录

（一）插入章节表

• MySQL概述
• 数据类型
• MySQL常用操作
• MySQL查询
• MySQL函数和存储过程
• MySQL高级特性
• 索引
• 综合案例

（二）插入题库表

【单选题】______表示一个新的事务处理块的开始

A、 START TRANSACTION

B、 BEGIN TRANSACTION

C、 BEGIN COMMIT

D、 START COMMIT

答案： A

所属章节：第一章

难度：容易

【单选题】______函数通常用来计算累计排名、移动平均数和报表聚合等。

A、 汇总

B、 分析

C、 分组

D、 单行

答案： A

所属章节：第一章

难度：容易

【单选题】______是实体属性。

A、 形状

B、 汽车

C、 盘子

D、 高铁

答案： A

所属章节：第一章

难度：容易

【单选题】______是一个单一的逻辑工作单元。

A、 记录

B、 数据库

C、 事务

D、 字段

答案： C

所属章节：第一章

难度：容易

【单选题】______子句用于查询列的唯一值。

A、 unique

B、 distinct

C、 order by

D、 group by

答案： B

所属章节：第一章

难度：容易

【单选题】MySql数据库中,下面______可以作为有效的列名。

A、 Column

B、 123_NUM

C、 NUM_#123

D、 #NUM123

答案： C

所属章节：第一章

难度：容易

【单选题】MySql数据库中,以下______命令可以删除整个表中的数据,并且无法回滚。

A、 drop

B、 delete

C、 truncate

D、 cascade

答案： C

所属章节：第一章

难度：容易

【单选题】MySQL中,预设的、拥有最高权限超级用户的用户名为______

A、 test

B、 Administrator

C、 DA

D、 root

答案： D

所属章节：第一章

难度：容易

【单选题】MySQL组织数据采用______

A、 层次模型

B、 网状模型

C、 关系模型

D、 数据模型

答案： C

所属章节：第一章

难度：容易

【单选题】SELECT语句的完整语法较复杂,但至少包括的部分是______

A、 仅SELECT

B、 SELECT,FROM

C、 SELECT,GROUP

D、 SELECT,INTO

答案： B

所属章节：第一章

难度：容易

【单选题】SQL 查询中去除重复数据的是______

A、 ORDER BY

B、 GROUP BY

C、 DISTINCT

D、 DESC

答案： C

所属章节：第一章

难度：容易

【单选题】SQL是一种______语言。

A、 函数型

B、 高级算法

C、 关系数据库

D、 人工智能

答案： C

所属章节：第一章

难度：容易

【单选题】SQL语句中______命令可以授予用户对象权限。

A、 REVOKE

B、 GRANT

C、 DENY

D、 CREATE

答案： B

所属章节：第一章

难度：容易

【单选题】SQL语句中的条件用以下哪一项来表达______

A、 THEN

B、 WHILE

C、 WHERE

D、 IF

答案： C

所属章节：第一章

难度：容易

【单选题】SQL语言的数据操纵语句包括SELECT、INSERT、UPDATE、DELETE等。其中最重要的,也是使用最频繁的语句是______。

A、 UPDATE

B、 SELECT

C、 DELETE

D、 INSERT

答案： B

所属章节：第一章

难度：容易

【填空题】SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体,其中,CREATE、DROP、ALTER语句是实现哪种功能______

答案：数据定义 

B、 数据控制

C、 数据定义

D、 数据查询

答案： C

所属章节：第一章

难度：容易

【填空题】数据操纵语言中典型的四种语句是Insert、select 、update和____。

答案： delete；

所属章节：第一章

难度：容易

【填空题】事务处理控制语言中的Commit命令表示提交事务,而回退事务则用____命令表示。

答案： ROLLBACK；

所属章节：第一章

难度：容易

【填空题】PL/SQL基本语句块中的声明部分使用____关键词。

答案： DECLARE；

所属章节：第一章

难度：容易

【填空题】查询数据表的内容,需要用到的sql命令为:____.(字母小写)

答案： select

所属章节：第一章

难度：容易

【填空题】在MySQL中,可以使用____(要求小写)语句来修改、更新一个表或多个表中的数据。

答案： update

所属章节：第一章

难度：容易

【填空题】SQL语言是______的语言,轻易学习 。

答案：非过程化

所属章节：第一章

难度：容易

【填空题】SQL语言中,删除一个视图的命令是______

答案：DROP

所属章节：第一章

难度：容易

【填空题】UNIQUE惟一索引的作用是______

答案：保证各行在该索引上的值都不得重复

【填空题】ORDER BY NAME DESC是指按照姓名______

答案：降序

所属章节：第一章

难度：容易

（三）插入教师表

帐号：1001

密码：1001

姓名：刘老师

（四）插入班级表

名称：软工2001-2003班

教师：刘老师

（五）插入学生表

学号 姓名 班级

191451081007 李东晁 软工2101-2103

211451080101 卜哲珩 软工2101-2103

211451080102 曹幸蕊 软工2101-2103

211451080103 常家乐 软工2101-2103

211451080104 陈杰宇 软工2101-2103

211451080105 陈俊坦 软工2101-2103

211451080106 陈梦阳 软工2101-2103

211451080107 海启阳 软工2101-2103

211451080108 韩奇超 软工2101-2103

211451080109 候占东 软工2101-2103

211451080110 胡子涵 软工2101-2103

211451080111 贾金其 软工2101-2103

211451080112 蒋飞宇 软工2101-2103

211451080113 李军伟 软工2101-2103

211451080114 李欣梦 软工2101-2103

211451080115 林富佳 软工2101-2103

211451080116 吕尚儒 软工2101-2103

211451080117 牛奔腾 软工2101-2103

211451080118 潘鑫 软工2101-2103

211451080119 孙相龙 软工2101-2103

211451080120 王方旭 软工2101-2103

211451080121 王思琦 软工2101-2103

211451080122 吴妍 软工2101-2103

211451080123 邢耿 软工2101-2103

211451080124 杨继坤 软工2101-2103

211451080125 杨晶 软工2101-2103

211451080126 杨舟 软工2101-2103

211451080127 尹先澳 软工2101-2103

211451080128 余家驹 软工2101-2103

211451080129 张傲 软工2101-2103

211451080130 张鼎 软工2101-2103

211451080131 张钧航 软工2101-2103

211451080132 张世凡 软工2101-2103

211451080133 张天宇 软工2101-2103

211451080134 张益恺 软工2101-2103

211451080135 赵超群 软工2101-2103

211451080136 闫家乐 软工2101-2103

211451080137 晏澜 软工2101-2103

201451081735 赵秀宇 软工2101-2103

211451080201 蔡炎培 软工2101-2103

211451080202 曹旭 软工2101-2103

211451080203 曹永祥 软工2101-2103

211451080204 陈佰飞 软工2101-2103

211451080205 陈志昂 软工2101-2103

211451080206 翟世聪 软工2101-2103

211451080207 丁超强 软工2101-2103

211451080208 丁路明 软工2101-2103

211451080209 董畅 软工2101-2103

211451080210 董思宇 软工2101-2103

211451080211 杜坤璞 软工2101-2103

211451080212 冯逸轩 软工2101-2103

211451080213 郭龙浩 软工2101-2103

211451080214 郭强 软工2101-2103

211451080215 黄思晴 软工2101-2103

211451080216 康国灿 软工2101-2103

211451080217 康贺威 软工2101-2103

211451080218 李健豪 软工2101-2103

211451080219 梁鑫洋 软工2101-2103

211451080220 刘民杰 软工2101-2103

211451080221 刘迅 软工2101-2103

211451080222 马敬雅 软工2101-2103

211451080223 马旭 软工2101-2103

211451080224 苗世朵 软工2101-2103

211451080225 欧晨西 软工2101-2103

211451080226 屈靖川 软工2101-2103

211451080227 盛和友 软工2101-2103

211451080228 王建军 软工2101-2103

211451080229 王慕康 软工2101-2103

211451080230 王一帆 软工2101-2103

211451080231 王振赏 软工2101-2103

211451080232 杨标 软工2101-2103

211451080233 杨钍燃 软工2101-2103

211451080234 张鹏 软工2101-2103

211451080235 张泽同 软工2101-2103

211451080236 祝仕威 软工2101-2103

211451080237 左明哲 软工2101-2103

211451080301 常廷凯 软工2101-2103

211451080302 陈成豪 软工2101-2103

211451080303 高维祯 软工2101-2103

211451080304 郭佳佳 软工2101-2103

211451080305 姜莱 软工2101-2103

211451080306 冷宗儒 软工2101-2103

211451080307 李春雨 软工2101-2103

211451080308 李荣翔 软工2101-2103

211451080309 李世隆 软工2101-2103

211451080310 李允 软工2101-2103

211451080311 刘杨 软工2101-2103

211451080312 吕建宇 软工2101-2103

211451080313 罗晓 软工2101-2103

211451080314 马奕豪 软工2101-2103

211451080316 邱田润 软工2101-2103

211451080317 史朝旭 软工2101-2103

211451080318 孙嘉译 软工2101-2103

211451080319 孙佳鑫 软工2101-2103

211451080320 田宏志 软工2101-2103

211451080321 王祥林 软工2101-2103

211451080322 王宇静 软工2101-2103

211451080323 杨博文 软工2101-2103

211451080324 杨晨 软工2101-2103

211451080325 张宝加 软工2101-2103

211451080326 张建党 软工2101-2103

211451080327 张凯茜 软工2101-2103

211451080328 张文辉 软工2101-2103

211451080329 张文硕 软工2101-2103

211451080330 张新疆 软工2101-2103

211451080331 张宇 软工2101-2103

211451080332 张钰伟 软工2101-2103

211451080333 张钰哲 软工2101-2103

211451080334 赵乾凯 软工2101-2103

211451080335 周斐驰 软工2101-2103

211451080336 左雨露 软工2101-2103

211451080337 闫丰 软工2101-2103

（六）插入试卷规则表：

第一章 至 第一章 单选题 容易 6

第一章 至 第一章 填空题 容易 4

（七）插入作业规则表：

• 单选题 容易 10

（八）备份数据库的结构和数据，导出SQL文件名为：net_sjk.sql，将net_sjk.sql上传至实验报告三。

实验四：网络课程平台（2）

一、编写存储过程1

（一）插入教师表

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `insert_teacher`$$

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 ;

（二）插入学生表

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `insert_student`$$

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 ;

（三）按帐号查询学生表

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `select_student`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `select_student`(

IN param1 VARCHAR(20)

        )

BEGIN

SELECT studentnum,studentname,studentpw,classid FROM student WHERE studentnum=param1;

END$$

DELIMITER ;

（四）按学生姓名查询学生表

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `select_student1`$$

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 ;

（五）批量插入学生表

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `insert_student1`$$

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 ;

（六）编写一存储过程（两上参数1功能号，2字符串）实现插入教师表、学生表、按帐号查询学生表、按学生姓名查询学生

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `proc1`$$

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 ;

二、编写存储过程2

（一）判断用户登陆状态

输入参数：学生帐号和密码

输出当前学生帐号的状态（登录成功，用户名或密码不正确，密码过于简单）

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `login`$$

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 ;

/*判断口令是否合法的函数*/

DELIMITER $$

USE `network_course`$$

DROP FUNCTION IF EXISTS `checkpw`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `checkpw`(pw0 VARCHAR(20)) RETURNS INT(11)

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 ;

（二）生成学生试卷

输入参数：学生帐号，按照规则生成学生试卷总表以及试卷明细表

USE `network_course`$$

DROP PROCEDURE IF EXISTS `insert_stu_exam`$$

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 ;

（三）生成班级试卷

输入参数：班级ID，按照规则生成班级所有学生的试卷总表以及试卷明细表

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `insert_class_exam`$$

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 ;

三、编写触发器

（一）章节试题数量触发器

当插入、删除试题时，修改章节表的试题数量

DELIMITER $$

USE `network_course`$$

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

CREATE

    /*!50017 DEFINER = 'root'@'localhost' */

    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 ;

DELIMITER $$

USE `network_course`$$

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

CREATE

    /*!50017 DEFINER = 'root'@'localhost' */

    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 ;

（二）班级学生人数触发器

当插入、删除学生时，修改班级表的学生人数

DELIMITER $$

USE `network_course`$$

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

CREATE

    /*!50017 DEFINER = 'root'@'localhost' */

    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 ;

DELIMITER $$

USE `network_course`$$

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

CREATE

    /*!50017 DEFINER = 'root'@'localhost' */

    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 ;

四、备份数据库的结构和数据，导出SQL文件名为：net_sjk2.sql，将net_sjk2.sql上传至实验报告四。

答案{、

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 8.0.28 : Database - net_sjk
*********************************************************************
*/

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

/*View structure for view class_student_view */

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

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

/*View structure for view homework_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `homework_view` 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`) */;

/*View structure for view teacher_student_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `teacher_student_view` AS select `teacher`.`teacherId` AS `teacherId`,`teacher`.`teacherNUM` AS `teacherNUM`,`teacher`.`teacherName` AS `teacherName`,`teacher`.`teacherPW` AS `teacherPW`,`student`.`studentId` AS `studentId`,`student`.`studentNUM` AS `studentNUM`,`student`.`studentName` AS `studentName`,`student`.`studentPW` AS `studentPW`,`student`.`classId` AS `classId` from (`teacher` join `student`) */;

/*View structure for view experiment_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `experiment_view` 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`) */;

/*View structure for view exam_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `exam_view` 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`) */;

/*View structure for view student_work_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `student_work_view` AS select `student`.`studentId` AS `studentId`,`student`.`studentNUM` AS `studentNUM`,`student`.`studentName` AS `studentName`,`student`.`studentPW` AS `studentPW`,`student`.`classId` AS `classId`,`chapter`.`chapterName` AS `chapterName`,`chapter`.`questionCount` AS `questionCount`,`work`.`workId` AS `workId`,`work`.`fromTime` AS `fromTime`,`work`.`toTime` AS `toTime`,`work`.`score` AS `score` from ((`student` join `chapter`) join `work`) */;

/*View structure for view student_expriment_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `student_expriment_view` AS select `student`.`studentId` AS `studentId`,`student`.`studentNUM` AS `studentNUM`,`student`.`studentName` AS `studentName`,`student`.`studentPW` AS `studentPW`,`student`.`classId` AS `classId`,`chapter`.`chapterName` AS `chapterName`,`chapter`.`questionCount` AS `questionCount`,`experiment`.`chapterId` AS `chapterId`,`experiment`.`experimentId` AS `experimentId`,`experiment`.`fromTime` AS `fromTime`,`experiment`.`toTime` AS `toTime`,`experiment`.`score` AS `score` from ((`student` join `chapter`) join `experiment`) */;

/*View structure for view student_exam_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `student_exam_view` AS select `student`.`studentId` AS `studentId`,`student`.`studentNUM` AS `studentNUM`,`student`.`studentName` AS `studentName`,`student`.`studentPW` AS `studentPW`,`student`.`classId` AS `classId`,`exam`.`examId` AS `examId`,`exam`.`chapterId` AS `chapterId`,`exam`.`fromTime` AS `fromTime`,`exam`.`toTime` AS `toTime`,`exam`.`score` AS `score`,`exam`.`state` AS `state` from (`student` join `exam`) */;

/*View structure for view tands_chapter_work_view */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tands_chapter_work_view` AS select `teacher_student_view`.`teacherId` AS `teacherId`,`teacher_student_view`.`teacherNUM` AS `teacherNUM`,`teacher_student_view`.`teacherName` AS `teacherName`,`teacher_student_view`.`teacherPW` AS `teacherPW`,`teacher_student_view`.`studentId` AS `studentId`,`teacher_student_view`.`studentNUM` AS `studentNUM`,`teacher_student_view`.`studentName` AS `studentName`,`teacher_student_view`.`studentPW` AS `studentPW`,`teacher_student_view`.`classId` AS `classId`,`chapter`.`chapterName` AS `chapterName`,`chapter`.`questionCount` AS `questionCount`,`work`.`chapterId` AS `chapterId`,`work`.`workId` AS `workId`,`work`.`fromTime` AS `fromTime`,`work`.`toTime` AS `toTime`,`work`.`score` AS `score` from ((`teacher_student_view` join `chapter`) join `work`) */;

/*View structure for view tands_chapter_experiment */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tands_chapter_experiment` AS select `teacher_student_view`.`teacherId` AS `teacherId`,`teacher_student_view`.`teacherNUM` AS `teacherNUM`,`teacher_student_view`.`teacherName` AS `teacherName`,`teacher_student_view`.`teacherPW` AS `teacherPW`,`teacher_student_view`.`studentId` AS `studentId`,`teacher_student_view`.`studentNUM` AS `studentNUM`,`teacher_student_view`.`studentName` AS `studentName`,`teacher_student_view`.`studentPW` AS `studentPW`,`teacher_student_view`.`classId` AS `classId`,`chapter`.`chapterName` AS `chapterName`,`chapter`.`questionCount` AS `questionCount`,`experiment`.`chapterId` AS `chapterId`,`experiment`.`experimentId` AS `experimentId`,`experiment`.`fromTime` AS `fromTime`,`experiment`.`toTime` AS `toTime`,`experiment`.`score` AS `score` from ((`teacher_student_view` join `chapter`) join `experiment`) */;

/*View structure for view tands_exam */

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

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tands_exam` AS select `teacher_student_view`.`teacherId` AS `teacherId`,`teacher_student_view`.`teacherNUM` AS `teacherNUM`,`teacher_student_view`.`teacherName` AS `teacherName`,`teacher_student_view`.`teacherPW` AS `teacherPW`,`teacher_student_view`.`studentId` AS `studentId`,`teacher_student_view`.`studentNUM` AS `studentNUM`,`teacher_student_view`.`studentName` AS `studentName`,`teacher_student_view`.`studentPW` AS `studentPW`,`teacher_student_view`.`classId` AS `classId`,`exam`.`chapterId` AS `chapterId`,`exam`.`examId` AS `examId`,`exam`.`fromTime` AS `fromTime`,`exam`.`toTime` AS `toTime`,`exam`.`score` AS `score`,`exam`.`state` AS `state` from (`teacher_student_view` join `exam`) */;

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