数据库实验六

宁波工程学院

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

学院： 电子与信息工程学院 专业： 计算机科学与技术 年级： 大二 实验时间：

组名： 组长： 组员姓名：

指导教师签字： 成绩：

实验六 视图、存储过程和触发器实验

一、实验目的

1、理解视图的概念和相关命令，并掌握视图相关的SQL语句

2、理解存储过程的概念和相关命令，并掌握存储过程相关的SQL语句 2、理解触发器的概念和相关命令，并掌握触发器相关的SQL语句

二、实验环境

CPU 2.0 GHz, 1.0 G Memory；Windows XP, SQL Server 2005。

三、实验内容及步骤

1、利用数据库jxgl完成实现下列查询的视图。（在SQL SERVER2005上附加数据库jxgl），并运行该视图。

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

操作：在“对象资源管理器”窗口中，选择数据库服务器，右击“数据库”在弹出菜单中单击“附加”命令，打开“附加数据库”对话框。

图6-1 选择附加操作窗口

单击该按钮打开“定位数据库文件”对话框，选择要附加的数据库文件

图6-2 “附加数据库”窗口

选择扩展名是.mdf的数据文件

图6-3 “定位数据库文件”对话框

点击“确定”后，回到“附加数据库”对话框。

点击“确定”完成数据库文件的附加

图6-4 “附加数据库”对话框

（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 --创建视图v1 as select * from Students

where class='03物流' Go

select * --查询视图v1 from v1

实验查询结果：

（2）创建视图，实现查询“入学成绩”在350到400分之间的学生的姓名和班级 create view v2(sname,class) --创建视图v2

as select sname,class from Students

where mgrade>=350 and mgrade<=400 Go

select * --查询视图v2 from v2

实验查询结果：

（3）创建视图，实现查询students表中现有的班级

create view v3(class) --创建视图v3

as select distinct class --加上distinct能消除重复选项 from students go

select * --查询视图v3 from v3

实验查询结果：

（4）创建视图，实现查询具有“教授”或“副教授”职称的教师的教师编号和姓名 create view v4(tno,tname) --创建视图v4

as select tno,tname from teachers

where ps='教授' or ps='副教授' --写where ps='教授' or '副教授'，会出错 Go

select * --查询视图v4 from v4

实验查询结果：

（5）创建视图，实现查询姓“陈”，且籍贯是“宁波”的学生的姓名，出生日期，入学成绩。

create view v5(sname,bday,mgrade) --创建视图v5

as select sname,bday,mgrade from Students

where sname like '陈%' and bplace='宁波' --sname后面要用like而不是=，

%代表多个字符 Go

select * --查询视图v5 from v5

实验查询结果：

（6）创建视图，实现查询课程名称中包含“DB_”的课程的信息

create view v6 --创建视图v5

as select *

from Course

where cname like 'DB_%' Go

select * --查询视图v6 from v6

实验查询结果：

（7）创建视图，实现查询教师上课情况表中还没有安排好上课教师的班级和对应的课程号

Create View v7(class,cno) --创建视图v5 AS select class,cno from TC

where tno is null --tno=null错误，要用is以及不能用引号括起来 go

select * --查询视图v7 from v7

实验查询结果：

（8）创建视图，实现查询全体学生情况，查询结果按所在班级名升序排列，同一班级中的学生按出生日期降序排列

Create View v8 --创建视图v8 AS select * from Students go

select * --查询视图v8，并实现查询结果按所在班级名升序排列，同一班级中的学生按出生日期降序排列

from v8

Order by class asc,bday desc

实验查询结果：

（9）创建存储过程，实现统计03物流1班学生“入学成绩”的平均分、最高分、最低分

create procedure students_grade --创建存储过程students_grade

as select avg(mgrade)as 平均分,max(mgrade) as 最高分,min(mgrade) as 最低分

from Students

where class='03物流' group by class Go

exec students_grade --执行存储过程students_grade

实验查询结果：

（10）创建存储过程，实现统计各个班级的学生人数，按统计结果做降序排列

create procedure students_num --创建存储过程students_num as select class,count(class)as 学生人数 --实现统计各个班级的学生人数 from Students group by class

order by 学生人数 desc --统计结果做降序排列

Go

exec students_num --执行存储过程students_grade

实验查询结果：

（11）创建存储过程，实现统计各部门教师的人数，筛选出教师人数在指定人数（参数）以上的部门

create procedure p11 --创建存储过程p11

as select dept as 部门,count(*) as 教师人数 --实现统计各部门教师的人数 from teachers group by dept

having count(*)>5 --筛选出教师人数在指定人数（参数）以上的部门 Go

exec p11 --执行存储过程p11

实验查询结果：

（12）创建储存过程，实现查询平均分在指定分数（参数）以上的课程编号

create procedure p12 @grade int --创建存储过程p12 as select cno as 课程编号 from sc group by cno

having avg(grade)>@grade Go

exec p12 @grade=90 --执行存储过程p12

实验查询结果：

五、实验结果分析

1. SQL Server的注释有两种：（1）--（两个减号）：用于注释单行；（2）/*.......*/：用于 注释多行（注意多行注释不能跨批）。所以实验中用“//”来注释是错误的。 2. 写“where ps='教授' or '副教授'”出错，应该写成：“where ps='教授' or ps='副教授'”；“tno=null”错误，要用is以及不能用引号括起来。

六、实验贡献情况（注意：每个组员必须分开书写，组长分配每个组

员的贡献值）

（10）创建存储过程，实现统计各个班级的学生人数，按统计结果做降序排列

create procedure students_num --创建存储过程students_num as select class,count(class)as 学生人数 --实现统计各个班级的学生人数 from Students group by class

order by 学生人数 desc --统计结果做降序排列

Go

exec students_num --执行存储过程students_grade

实验查询结果：

（11）创建存储过程，实现统计各部门教师的人数，筛选出教师人数在指定人数（参数）以上的部门

create procedure p11 --创建存储过程p11

as select dept as 部门,count(*) as 教师人数 --实现统计各部门教师的人数 from teachers group by dept

having count(*)>5 --筛选出教师人数在指定人数（参数）以上的部门 Go

exec p11 --执行存储过程p11

实验查询结果：

（12）创建储存过程，实现查询平均分在指定分数（参数）以上的课程编号

create procedure p12 @grade int --创建存储过程p12 as select cno as 课程编号 from sc group by cno

having avg(grade)>@grade Go

exec p12 @grade=90 --执行存储过程p12

实验查询结果：

五、实验结果分析

1. SQL Server的注释有两种：（1）--（两个减号）：用于注释单行；（2）/*.......*/：用于 注释多行（注意多行注释不能跨批）。所以实验中用“//”来注释是错误的。 2. 写“where ps='教授' or '副教授'”出错，应该写成：“where ps='教授' or ps='副教授'”；“tno=null”错误，要用is以及不能用引号括起来。

六、实验贡献情况（注意：每个组员必须分开书写，组长分配每个组

员的贡献值）

本文来源：https://www.bwwdw.com/article/eylf.html