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

Posted by Cww97 on 2017-04-07

版权声明:本文为博主原创文章,未经博主允许不得转载。原文所在http://blog.csdn.net/cww97 https://blog.csdn.net/cww97/article/details/69526334
# 华东师范大学软件学院上机实践报告

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

一、目的

  1. 深入理解SQL语言,熟练使用一些高级技术。
  2. 掌握存储过程的使用方法
  3. 掌握触发器的使用方法
    ## 二、内容与设计思想

所涉及到的库表结构

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

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

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

  7. 加入一门新课程时,自动为所有学生选上该门课程
  8. 更改一门课程的课程号时,自动修改相应的选课记录的课程号。
  9. 删除一个学生时,自动删除该学生的选课记录
  10. 删除一条选课记录的时候,如果对应的课程号没有人选了,则删除该课程号。
  11. 增加一个学生记录时,察看“数据库”和“CPP”课程是否存在,如果存在的话则为该学生选择这两门课程。
    ## 三、使用环境

Windows XP Professional -> mysql

SQL Server 2005 ->DataGrip

四、实验过程

写出各条sql语句

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
#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;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
#触发器
#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简单,就。。。嘻嘻

六、附录

目前数据库样子

这里写图片描述
这里写图片描述