数据库实践二：高级sql语句
华东师范大学软件学院上机实践报告
| 课程名称：数据库应用 | 年级：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简单，就。。。嘻嘻
六、附录
目前数据库样子
