【无标题】试卷的

试卷视图触发器巴

建立视图的代码：

 CREATE VIEW cs

 AS

 SELECT `class`.`classid`,`classname`,`classsize`,`teacherid`,`studentid`,`studentnum`,`studentname`,`studentpw`

  FROM `class`,`student`

  WHERE `class`.`classid`=`student`.`classid`;

  

CREATE VIEW cst

AS

SELECT `class`.`classid`,`classname`,`classsize`,

`studentid`,`studentnum`,`studentname`,`studentpw`,

`teacher`.`teacherId`,`teacherNUM`,`teacherName`,`teacherPW`

FROM `class`,`student`,`teacher`

WHERE class.`classid`=student.`classid` AND

class.`teacherid`=teacher.`teacherId`

CREATE VIEW wd

AS

SELECT `work`.`workid`,`work`.`chpaterid`,`work`.`fromtime`,

`work`.`score`,`work`.`studentid`,`work`.`totime`,

`work_detail`.`work_detailid`,`work_detail`.`answer`,

`work_detail`.`grade`,`work_detail`.`questionid`

FROM `work`,`work_detail`

WHERE `work`.`workid`=`work_detail`.`workid`;

CREATE VIEW ed

AS

SELECT `experiment`.`experimentid`,`experiment`.`chapterid`,

`experiment`.`studentid`,`experiment`.`fromtime`,`experiment`.`totime`,

`experiment`.`score`,`experiment_detail`.`experiment_detailid`,

`experiment_detail`.`questionid`,`experiment_detail`.`answer`,

`experiment_detail`.`grade`

FROM`experiment`,`experiment_detail`

WHERE `experiment`.`experimentid`=`experiment_detail`.`experimentid`;

CREATE VIEW exd

AS

SELECT `exam`.`examid`,`exam`.`studentid`,`exam`.`fromtime`,

`exam`.`totime`,`exam`.`state`,`exam`.`score`,`exam_detail`.`exam_detailid`,

`exam_detail`.`questionid`,`exam_detail`.`answer`,`exam_detail`.`grade`

FROM `exam`,`exam_detail`

WHERE `exam`.`examid`=`exam_detail`.`examid`;

CREATE VIEW csw

AS

SELECT `chapter`.`chapterid`,`chapterName`,`questioncount`,

`student`.`studentid`,`studentnum`,`studentname`,`studentpw`,`classid`,

`work`.`workid`,`fromtime`,`totime`,`score`

FROM `chapter`,`student`,`work`

WHERE `chapter`.`chapterid`=`work`.`chpaterid`

AND `work`.`studentid`=`student`.`studentid`;

CREATE VIEW ces

AS

SELECT `chapter`.`chapterid`,`chapterName`,`questioncount`,

`experiment`.`experimentid`,`fromtime`,`totime`,`score`,

`student`.`studentid`,`studentnum`,`studentname`,`studentpw`,

`classid`

FROM `chapter`,`experiment`,`student`

WHERE `chapter`.`chapterid`=`experiment`.`chapterid`

AND `experiment`.`studentid`=`student`.`studentid`;

CREATE VIEW es

AS

SELECT `experiment`.`experimentid`,`chapterid`,`fromtime`,`totime`,`score`,

`student`.`studentid`,`studentnum`,`studentname`,`studentpw`,`classid`

FROM `experiment`,`student`

WHERE `experiment`.`studentid`=`student`.`studentid`;

CREATE VIEW stcw

AS

SELECT `chapter`.`chapterid`,`chapterName`,`questioncount`,

`class`.`classid`,`classname`,`classsize`,`student`.`studentid`,

`studentnum`,`studentname`,`studentpw`,`teacher`.`teacherId`,

`teacherNUM`,`teacherName`,`teacherPW`,`work`.`workid`,`fromtime`,

`totime`,`score`

FROM `chapter`,`class`,`student`,`teacher`,`work`

WHERE `chapter`.`chapterid`=`work`.`chpaterid` AND

`work`.`studentid`=`student`.`studentid` AND

`student`.`classid`=`class`.`classid` AND

`class`.`teacherid`=`teacher`.`teacherId`;

CREATE VIEW stce

AS

SELECT `chapter`.`chapterid`,`chapterName`,`questioncount`,

`class`.`classid`,`classname`,`classsize`,`experiment`.`experimentid`,

`fromtime`,`totime`,`score`,`student`.`studentid`,`studentnum`,`studentname`,

`studentpw`,`teacher`.`teacherId`,`teacherNUM`,`teacherName`,`teacherPW`

FROM `chapter`,`class`,`experiment`,`student`,`teacher`

WHERE `chapter`.`chapterid`=`experiment`.`chapterid` AND

`experiment`.`studentid`=`student`.`studentid` AND

`student`.`classid`=`class`.`classid` AND

`class`.`teacherid`=`teacher`.`teacherId`;

CREATE VIEW cest

AS

SELECT `class`.`classid`,`classname`,`classsize`,`exam`.`examid`,

`fromtime`,`totime`,`state`,`score`,`student`.`studentid`,`studentnum`,

`studentname`,`studentpw`,`teacher`.`teacherId`,`teacherNUM`,`teacherName`,

`teacherPW`

FROM `class`,`exam`,`student`,`teacher`

WHERE `class`.`classid`=`student`.`classid` AND

`class`.`teacherid`=`teacher`.`teacherId` AND

`exam`.`studentid`=`student`.`studentid`;

• 编写触发器

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

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

插入时增加试题的触发器：

DELIMITER $$

CREATE

    TRIGGER `network_course`.`insert_from_questionbank_after_add_chapter`

    AFTER UPDATE

    ON `network_course`.`questionbank`

    FOR EACH ROW BEGIN

UPDATE chapter

SET `questioncount`=`questioncount`+1

WHERE `chapterid`=new.`chapterid`;

    END$$

DELIMITER ;

删除试题时，减少试题数量：

DELIMITER $$

CREATE

    TRIGGER `network_course`.`delete_from_questionbank_delete_from_chapter`

    AFTER DELETE

    ON `network_course`.`questionbank`

    FOR EACH ROW BEGIN

UPDATE `chapter`

SET `questioncount`=`questioncount`-1

WHERE `chapterid`=old.`chapterid`;

    END$$

DELIMITER ;

二）班级学生人数触发器

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

插入学生时，增加班级表人数;

DELIMITER $$

CREATE

    TRIGGER `network_course`.`insert_from_student_update_class`

    AFTER INSERT

    ON `network_course`.`student`

    FOR EACH ROW BEGIN

UPDATE `class`

SET `classsize`=`classsize`+1

WHERE `classid`=new.`classid`;

    END$$

DELIMITER ;

删除学生时，减少班级人数;

DELIMITER $$

CREATE

    TRIGGER `network_course`.`delete_from_student_update_class`

    AFTER DELETE

    ON `network_course`.`student`

    FOR EACH ROW BEGIN

UPDATE `class`

SET `classsize`=`classsize`-1

WHERE `classid`=old.`classid`;

    END$$

DELIMITER ;

• 编写存储过程或函数

• 写出插入一条学生数据的存储过程

DELIMITER $$

CREATE

    PROCEDURE `network_course`.`insert_one_student`(

IN snum VARCHAR(20),

IN sname VARCHAR(20),

IN spw VARCHAR(20),

IN cid INT

    )

    BEGIN

DECLARE count1 INT;

SELECT COUNT(*) INTO count1

FROM student

WHERE `studentnum`=snum;

IF count1=0 THEN

INSERT INTO student(`studentnum`,`studentname`,`studentpw`,`classid`)

VALUES(snum,sname,spw,cid);

END IF;

    END$$

DELIMITER ;

• 写出一次插入多条学生数据的存储过程，学生数据的格式为：

“学号1，姓名1，密码1，班级号1；学号2，姓名2，密码2，班级号2；”

例如：

"201451080102,张三,NULL,1;201451080103,李四,NULL,1;"

CAST( 字符串 AS SIGNED);  将字符串类型的数字转化为数字类型的数字

在MySQL中，MID()函数用于返回一个字符串中从指定位置开始的指定长度的子字符串。其语法为：

MID(str, start, length)
str为要从中提取子字符串的原始字符串。
start为要开始提取子字符串的位置（位置从1开始）。
length为要提取的子字符串的长度

在MySQL中，LOCATE()函数用于在一个字符串中查找子字符串，并返回子字符串第一次出现的位置。其语法为：

LOCATE(substring, string, start)
substring为要查找的子字符串。
string为要在其中查找子字符串的原始字符串。
start为可选参数，表示从原始字符串的某个位置开始查找。如果不指定start，则默认从字符串的第一个字符开始查找。

代码如下：

DELIMITER $$

CREATE

    PROCEDURE `network_course`.`insert_many_student`(

    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_one_student`(num0,name0,pw0,classid0);

SET stustr= MID(stustr,LOCATE(';',stustr)+1);

END WHILE ;

    END$$

DELIMITER ;

（三）生成学生试卷

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

DELIMITER $$

CREATE

    PROCEDURE `network_course`.`insert_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 $$

CREATE

    PROCEDURE `network_course`.`insert_exam0`(

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_exam (stuid0);

FETCH cur INTO stuid0;

END WHILE;

CLOSE cur;

    END$$

DELIMITER ;