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

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

一、目的

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

二、内容与设计思想

所涉及到的库表结构

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

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

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

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

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

三、使用环境

Windows XP Professional -> mysql

SQL Server 2005 ->DataGrip

四、实验过程

写出各条sql语句

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

#1.输入课程序号，输出课程名称
select cou_name
from courses
where cou_id='SOFT0031131105';

#2.输入学生学号，输出该学生的选课的数量
SELECT count(cou_id)
FROM stu_cou
WHERE stu_id = '10152510217';

#3.增加一门课程信息，输入是课程代码、课程名称
INSERT INTO courses
VALUES('PESE0031131411', '书法（隶书）')

#4.输出恰巧选择三门课程的学生名单
SELECT students.stu_name
FROM students, stu_cou
WHERE students.stu_id = stu_cou.stu_id
GROUP BY stu_cou.stu_id
HAVING count(stu_cou.cou_id) = 3;

#5.输出选修人数最多的课程的代码和名称
SELECT cou_id,cou_name
FROM courses NATURAL JOIN stu_cou
GROUP BY cou_id
HAVING 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_cou
AFTER INSERT ON courses
FOR EACH ROW
BEGIN
 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简单，就。。。嘻嘻

六、附录

目前数据库样子

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

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

一、目的

二、内容与设计思想

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

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

三、使用环境

四、实验过程

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

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

五、总结

六、附录

更多相关文章

随机推荐