数据库实践二：高级sql语句

华东师范大学软件学院上机实践报告

课程名称：数据库应用 年级：15级 上机实践成绩： 指导教师：金澈清 姓名：陈伟文 上机实践名称：高级sql语句实践 学号：10152510217 上机实践日期：2017/3/29 上机实践编号：No.2 组号：1-217 上机实践时间：4学时

一、目的

1. 深入理解SQL语言，熟练使用一些高级技术。
2. 掌握存储过程的使用方法
3. 掌握触发器的使用方法

二、内容与设计思想

所涉及到的库表结构

总共三张表，即学生表，课程表，选课表。

内容一：创建如下的存储过程

1. 输入课程序号，输出课程名称
2. 输入学生学号，输出该学生的选课的数量
3. 增加一门课程信息，输入是课程代码、课程名称
4. 输出恰巧选择三门课程的学生名单
5. 输出选修人数最多的课程的代码和名称
6. 以游标技术，列举出所有学生的名单，包括学生姓名、选择的课程的数量。

内容二：创建如下的触发器

1. 加入一门新课程时，自动为所有学生选上该门课程
2. 更改一门课程的课程号时，自动修改相应的选课记录的课程号。
3. 删除一个学生时，自动删除该学生的选课记录
4. 删除一条选课记录的时候，如果对应的课程号没有人选了，则删除该课程号。
5. 增加一个学生记录时，察看“数据库”和“CPP”课程是否存在，如果存在的话则为该学生选择这两门课程。

三、使用环境

Windows XP Professional -> mysql

SQL Server 2005 ->DataGrip

四、实验过程

写出各条sql语句

内容一：创建如下的存储过程

#1.输入课程序号，输出课程名称select cou_namefrom courseswhere cou_id='SOFT0031131105';#2.输入学生学号，输出该学生的选课的数量SELECT count(cou_id)FROM stu_couWHERE stu_id = '10152510217';#3.增加一门课程信息，输入是课程代码、课程名称INSERT INTO coursesVALUES('PESE0031131411', '书法（隶书）')#4.输出恰巧选择三门课程的学生名单SELECT students.stu_nameFROM students, stu_couWHERE students.stu_id = stu_cou.stu_idGROUP BY stu_cou.stu_idHAVING count(stu_cou.cou_id) = 3;#5.输出选修人数最多的课程的代码和名称SELECT cou_id,cou_nameFROM courses NATURAL JOIN stu_couGROUP BY cou_idHAVING count(*)>=ALL(  SELECT count(*)  FROM courses NATURAL JOIN stu_cou  GROUP BY cou_id);#6.以游标技术，列举出所有学生的名单，包括学生姓名、选择的课程的数量。CREATE PROCEDURE get_stus()  BEGIN    DECLARE no_more_stu int DEFAULT 0;    DECLARE stud_id VARCHAR(20);    DECLARE my_cursor CURSOR FOR SELECT stu_id FROM students;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_stu = 1;    CREATE TEMPORARY TABLE info(      stu_name VARCHAR(20),      take_cnt INT    );    OPEN my_cursor;    FETCH my_cursor INTO stud_id;    REPEAT      INSERT INTO info      SELECT stu_name,count(cou_id)        FROM stu_cou NATURAL JOIN students        WHERE stu_id = stud_id;      FETCH my_cursor INTO stud_id;    UNTIL  no_more_stu = 1    END REPEAT ;    CLOSE my_cursor;    SELECT *FROM info;  END;CALL get_stus();DROP TABLE info;

内容二：创建如下的触发器

#触发器#7.加入一门新课程时，自动为所有学生选上该门课程DROP TRIGGER IF EXISTS auto_sel_cou;CREATE TRIGGER auto_sel_couAFTER INSERT ON coursesFOR EACH ROWBEGIN    INSERT INTO stu_cou    SELECT stu_id, NEW.cou_id, null    FROM students;END;INSERT INTO courses VALUES ('SOFT0031121000','线性代数');#8.更改一门课程的课程号时，自动修改相应的选课记录的课程号。DROP TRIGGER auto_qudate_cou_id;CREATE TRIGGER auto_qudate_cou_id AFTER UPDATE ON courses  FOR EACH ROW  BEGIN    UPDATE stu_cou      SET cou_id = NEW.cou_id    WHERE cou_id = OLD.cou_id;  END;INSERT INTO stu_cou VALUES ('10152510217','     SOFT0031131130',NULL );UPDATE courses  SET cou_id = 'SOFT0031131130'WHERE cou_id = '    SOFT0031131130';#9.删除一个学生时，自动删除该学生的选课记录CREATE TRIGGER auto_del_taken AFTER DELETE ON students  FOR EACH ROW  BEGIN    DELETE FROM stu_cou      WHERE stu_cou.stu_id = OLD.stu_id;  END;INSERT INTO students VALUES ('111','111',NULL );INSERT INTO stu_cou VALUES ('111','SOFT0031131130',NULL );DELETE FROM students WHERE stu_id = '111';#10.删除一条选课记录的时候，如果对应的课程号没有人选了，则删除该课程号。DROP TRIGGER auto_del_cou;CREATE TRIGGER auto_del_cou AFTER DELETE ON stu_cou  FOR EACH ROW  BEGIN    DELETE FROM courses    WHERE courses.cou_id = OLD.cou_id      AND 1 > (SELECT count(*)           FROM stu_cou           WHERE stu_cou.cou_id = OLD.cou_id);  END;INSERT INTO courses VALUES ('11','11');INSERT INTO stu_cou VALUES ('11','11',NULL );DELETE FROM stu_cou WHERE cou_id = '11';SELECT count(*) FROM stu_cou WHERE cou_id = '11';#11.增加一个学生记录时，察看“数据库”和“CPP”课程是否存在，如果存在的话则为该学生选择这两门课程。DROP TRIGGER auto_add_db_cpp  ;CREATE TRIGGER auto_add_db_cpp AFTER INSERT ON students  FOR EACH ROW  BEGIN    INSERT INTO stu_cou      SELECT NEW.stu_id, cou_id, NULL        FROM courses          WHERE cou_name = '数据库' or cou_name = 'CPP';  END;UPDATE courses SET cou_name = '数据库' WHERE cou_name = 'DataBase';INSERT INTO courses VALUES ('SOFT0031131010','CPP');INSERT INTO students VALUES ('11','11',NULL );DELETE FROM students WHERE stu_id = '11';DELETE FROM stu_cou WHERE cou_id = '11';

五、总结

对上机实践结果进行分析，问题回答，上机的心得体会及改进意见。

游标不会写，抄的同学的，感觉本来可以一个select解决的东西，而且就算以后真的会用到循环，肯定是用其他语言for循环（以前也是这么干的），很稳，mysql感觉稳啊

中途自己在另一台windows服务器装了sql server的数据库，经过测试发现比mysql的query速度慢了不少，再加上mysql简单，就。。。嘻嘻

六、附录

目前数据库样子