数据库练习题（XXT实验二）

实验二：网络课程平台（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;