数据库理论与技术实验报告六

数据库理论与技术课程实验报告
学院：电子与信息工程学院专业：计算机科学与技术年级：计科
实验时间： 2012年4月26日
组长：学号：组_______
姓名：学号：组_______
姓名：学号：组_______
姓名：学号：组_______
指导教师签字：成绩：
实验六、视图、存储过程和触发器实验
一、实验目的和要求
1、实验目的：理解视图的概念和相关命令，并掌握视图相关的SQL语句；理解存储过程的概念和相关命令，并掌握存储过程相关的SQL语句；理解触发器的概念和相关命令，并掌握触发器相关的SQL语句
2、实验要求：掌握视图存储过程和触发器的使用
二、实验内容与步骤
1、利用数据库jxgl完成实现下列查询的视图。

（在SQL SERVER2005上附加数据库jxgl），并运行该视图。

安装好的SQL Server2005没有用户数据库，如果磁盘上有数据库文件，可以将其附加到数据库服务器中。

（1）创建视图，实现查询03物流1班学生的详细信息
（2）创建视图，实现查询“入学成绩”在350到400分之间的学生的姓名和班级（3）创建视图，实现查询students表中现有的班级
（4）创建视图，实现查询具有“教授”或“副教授”职称的教师的教师编号和姓名（5）创建视图，实现查询姓“陈”，且籍贯是“宁波”的学生的姓名，出生日期，入学成绩。

（6）创建视图，实现查询课程名称中包含“DB_”的课程的信息
（7）创建视图，实现查询教师上课情况表中还没有安排好上课教师的班级和对应的课程号
（8）创建视图，实现查询全体学生情况，查询结果按所在班级名升序排列，同一班级中的学生按出生日期降序排列
（9）创建存储过程，实现统计03物流1班学生“入学成绩”的平均分、最高分、最低分
（10）创建存储过程，实现统计各个班级的学生人数，按统计结果做降序排列（11）创建存储过程，实现统计各部门教师的人数，筛选出教师人数在指定人数（参数）以上的部门
（12）创建储存过程，实现查询平均分在指定分数（参数）以上的课程编号
2、将上述查询以存储过程实现，并在后面写出运行该存储过程的语句。

注意：在实验报告中说明查询的目的和对应的语句。

三、实验过程及数据记录
（1）创建视图，实现查询03物流1班学生的详细信息
create view v1 as
select *
from Students
where class='03物流1'
select * //查询视图v1
from v1
（2）创建视图，实现查询“入学成绩”在350到400分之间的学生的姓名和班级create view v2 as
select sname,class
from Students
where mgrade between 350 and 400
select * //查询视图v2
from v2
（3）创建视图，实现查询students表中现有的班级
create view v3 as
select distinct class //加上distinct能消除重复选项
from Students
select * //查询视图v3
from v3
（4）创建视图，实现查询具有“教授”或“副教授”职称的教师的教师编号和姓名create view v4 as
select tno,tname
from Teachers
where ps='副教授' or ps='教授' //写where ps='副教授' or '教授' 会出错
select * //查询视图v4
from v4
（5）创建视图，实现查询姓“陈”，且籍贯是“宁波”的学生的姓名，出生日期，入学成绩。

create view v5 as
select sname,bday,mgrade
from Students
where sname like '陈%' and bplace='宁波' //sname后要用like而不是=,%代表几个字符
select * //查询视图v5
from v5
（6）创建视图，实现查询课程名称中包含“DB_”的课程的信息
create view v6 as
select *
from Course
where cname like 'DB_%'
select * //查询视图v6
from v6
（7）创建视图，实现查询教师上课情况表中还没有安排好上课教师的班级和对应的课程号
create view v7 as
select *
from TC
where tno is NULL //tno = 'NULL' 错误，要用is以及不能用引号括起来
select * //查询视图v7
from v7
（8）创建视图，实现查询全体学生情况，查询结果按所在班级名升序排列，同一班级中的学生按出生日期降序排列
create view v8 as
select *
from students
order by class asc,bday desc
//写成order by class asc
order by bday desc 出错，order by在一个语句中不能写多个
select * //查询视图v8
from v8
（9）创建存储过程，实现统计03物流1班学生“入学成绩”的平均分、最高分、最低分
create procedure p9 as
select avg(students.mgrade) 平均分,
max(students.mgrade) 最高分,min(students.mgrade) 最低分
from Students
where class='03物流1'
exec p9 //查询储存过程v9
（10）创建存储过程，实现统计各个班级的学生人数，按统计结果做降序排列create procedure p10 as
select class, count(*) renshu //count(*)完成统计各个班级的学生人数
from Students
group by class //按class分组，否则无法按各个班级统计人数
order by renshu DESC //按统计结果做降序排列
exec p10 //查询储存过程v10
（11）创建存储过程，实现统计各部门教师的人数，筛选出教师人数在指定人数（参数）以上的部门
create procedure p11 as
select dept, count(*) renshu // count(*)完成统计各部门教师的人数
from Teachers
group by dept
having (count(*)>5) //having语句完成筛选出教师人数在指定人数5以上
exec p11 //查询储存过程v11
（12）创建储存过程，实现查询平均分在指定分数（参数）以上的课程编号create procedure p12 as
select o , avg(SC.grade) Grade
from Students,SC
where SC.sno=Students.sno
group by o
having (avg(SC.grade)>85) //having语句查询平均分在指定分数85以上
exec p12 //查询储存过程v12
四、实验结果分析
五、实验贡献情况（注意：每个组员必须分开书写，组长分配每个组员的贡献值）。