试卷视图触发器巴
建立视图的代码:
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 ;