数据库实验报告

数据库实验报告

班级：软工1501班

学号： 1508010130

姓名：吴志远

2017年6月

使用SQL Server开发服务器端应用程序

一、实验类别

综合型实验

二、实验目的

熟练掌握后台服务器端应用程序的开发。

三、实验环境

SQL Server系列的数据库管理系统

四、实验内容

在学生-课程数据库中，编写存储过程或触发器，完成以下功能：

1．将一个新学生元组（通过实参得到）插入student表中；

2．将一个新课程元组（通过实参得到）插入course表中；

3．输入某个学生的某门课程成绩（通过实参得到）；

4．逐条（使用游标）浏览某个系（通过实参得到）的学生记录；

5．统计任意一门课程的成绩分布情况，即按照各分数段统计人数；

6．统计每个学生（通过实参得到）的平均成绩及排名；

7．将学生选课成绩从百分制改为等级制（即A、B、C、D、E）显示。

8．创建一个触发器，删除学生表中记录的同时删除选修表中相应的选课记录。

五、实验过程

1. 创建数据库stu-course ，包含3张表，分别为：course表、sc表、student表；创建数据库study，包含6张表，分别为：Choice表，Class表，Course表，Student表，Teacher表，Teaching 表；创建后的结果如下图1和图2所示。

图1 stu-course数据库

图2 study数据库

2.创建存储过程Insert_Students完成功能：将一个新学生元组（通过实参得到）插入student 表中，具体代码及运行结果如下图3和图4所示：

创建存储过程：

Create Procedure Insert_Students

@sno char(10),

@sname nchar(10),

@ssex char(2),

@sage smallint,

@sdept nchar(10)

as

insert student (sno,sname,ssex,sage,sdept)

values(@sno,@sname,@ssex,@sage,@sdept)

执行存储过程：

exec Insert_Students '1508010130','吴志远','男',20,'CS'

运行结果：

图3 执行存储过程Insert_Students

图4 执行存储过程Insert_Students之后的student表

3. 创建存储过程Insert_Courses完成功能：将一个新课程元组（通过实参得到）插入course 表中，具体代码及运行结果如下图5和图6所示。

创建存储过程：

CREATE Procedure Insert_Courses

@cno char(1),

@cname nchar(16),

@cpno char(1),

@ccredit smallint

as

insert course(cno,cname,cpno,ccredit)

values(@cno,@cname,@cpno,@ccredit)

执行存储过程：

exec Insert_Courses '8','大学物理','5',2

运行结果：

图5 执行存储过程Insert_Courses

图6 执行存储过程Insert_Courses之后的course表

4.创建存储过程Input_Scores完成功能：输入某个学生的某门课程成绩（通过实参得到），具体代码及运行结果如下图7、图8和图9所示。

创建存储过程：

create procedure Insert_Scores

@sno char(10),

@cno char(1),

@grade smallint

as

update sc

set grade = @grade

where sno=@sno and cno = @cno

执行存储过程：

exec Insert_Scores '201215121','1',100

运行结果：

图7 执行存储过程Insert_Scores

图8 修改之前的sc表图9 修改之后的sc表

5.创建存储过程Dept_Searchs完成功能：逐条（使用游标）浏览某个系（通过实参得到）的学生记录，具体代码及运行结果如下图10和图11所示:

创建存储过程：

create procedure Dept_Searchs /*创建触发器(create trigger)*/

@sdept nchar(10) /*形参*/

as

declare /*定义变量*/

@sno char(10),

@sname nchar(10),

@ssex char(2),

@sage smallint

declare Dept_Cursor cursor for /*申明游标*/

select sno,sname,ssex,sage

from student

where sdept = @sdept

open Dept_Cursor

fetch next from Dept_Cursor

into @sno,@sname,@ssex,@sage /*将游标获得的内容放入临时变量*/ if @sdept='CS'

print 'CS系学生信息：'

if @sdept='MA'

print 'MA系学生信息：'

if @sdept='IS'

print 'IS系学生信息：'

print '--------------------------'

while(@@fetch_status = 0)

begin

print '学号：'+@sno

print '姓名：'+@sname

print '性别：'+@ssex

print '年龄：'+cast(@sage as char)

print '--------------------------'

fetch next from Dept_Cursor /*推进游标*/

into @sno,@sname,@ssex,@sage

end

close Dept_Cursor

deallocate Dept_Cursor

执行存储过程：

exec Dept_Searchs 'CS'

运行结果：

图10 执行存储过程Dept_Searchs

图11 查询CS系学生信息

6.创建存储过程Count_Grades完成功能：统计任意一门课程的成绩分布情况，即按照各分数段统计人数，具体代码及运行结果如下图12、图13和图14所示。

创建存储过程：

create procedure Count_Grades

@cno nchar(1)

as

select count(case grade when 100 then 1 end) as [满分],

count(case when grade between 90 and 99 then 1 end) as[90-99分],

count(case when grade between 80 and 89 then 1 end) as[80-89分],

count(case when grade between 70 and 79 then 1 end) as[70-79分],

count(case when grade < 70 then 1 end) as[70分以下]

from sc

where cno = @cno

执行存储过程：

exec Count_Grades '1'

运行结果：

图12 执行存储过程Count_Grades

图13 sc表信息图14 统计情况

7.完成功能：统计每个学生（通过实参得到）的平均成绩及排名，具体代码及运行结果如下图15和图16所示。

SQL源代码:

select distinct sc.sno,avg_table.avg_grade,dense_RANK()

over(order by avg_table.avg_grade desc )

as [排名]

from sc,

(

select sno,A VG(grade)

as avg_grade from sc group by sno

)avg_table

where sc.sno = avg_table.sno

order by avg_table.avg_grade desc

查询结果：

图15 新建查询过程

图16 平均成绩及排名

8.完成功能：将学生选课成绩从百分制改为等级制（即A、B、C、D、E）显示，具体代码及运行结果如下图17和图18所示。

SQL源代码：

select sno[学号],cno[课程号],(case

when grade >= 80 AND grade < 90 then 'B'

when grade >= 70 AND grade < 80 then 'C'

when grade >= 60 AND grade < 70 then 'D'

when grade < 60 then 'E'

else 'A'

end) [等级]

from sc

查询结果：

图17 新建查询过程

图18 等级制成绩

9.完成功能：创建一个触发器，删除学生表中记录的同时删除选修表中相应的选课记录，具体代码及运行结果如下图19、图20、图21、图22和图23所示。

创建触发器源码如下：

GO

create trigger delete_stu

ON Student

with encryption

for delete

AS

delete from choice

where s_no in (select s_no from deleted)

将学号为991102的学生删除：

图19 创建触发器

图20 删除之前的Student表

图21 删除之后的Student表

图22 删除之前的Choice表图23 删除之后的Choice表

七、实验总结

在实训中我学到了许多新的知识。是一个让我把书本上的理论知识运用于实践中的好机会，原来，学的时候感叹学的内容太难懂，现在想来，有些其实并不难，关键在于理解。在这次实训中还锻炼了我其他方面的能力，提高了我的综合素质。

在编写SQL语句时，由于掌握的知识不足，有的语句还是太繁琐不够简洁，以及一些复杂的操作还要上网查阅资料，看书，请教同学等等，另外自主学习在实验中也是十分重要的，正是通过这些，我才能能够的顺利完成这次实验。同时，在今后的学习过程中还需加强训练，多动手实践，而不是只看书本的内容。希望在以后的学习过程中，数据库的运用对我有更多的帮助。

无论怎样，通过这次的实验我学到了很多，也为我的理论基础打下了坚实的铺垫，也能够为我以后的学习生涯带来巨大的帮助的!我很享受这个过程。