数据库.docx
- 文档编号:18598237
- 上传时间:2023-08-20
- 格式:DOCX
- 页数:18
- 大小:22.30KB
数据库.docx
《数据库.docx》由会员分享,可在线阅读,更多相关《数据库.docx(18页珍藏版)》请在冰点文库上搜索。
数据库
实验四:
索引和视图的创建及操作
一、实验目的
(1)掌握创建索引的命令
(2)掌握创建视图的命令
(3)掌握使用资源管理及T-SQL命令管理索引的方法
(4)掌握使用资源管理及T-SQL命令管理视图的方法
二、实验准备
(1)了解聚集索引和非聚集索引的概念
(2)了解创建索引的SQL语句
(3)了解使用资源管理器创建索引的步骤
(4)了解视图的概念及作用
3、实验内容
(1)视图的基本操作
(1)在stusystem数据库中以建立一个名为“教师授课情况”的视图,视图中包含教师的姓名、教授的班级名称及教授课程名称等信息。
CREATEViewVIEW_教师授课情况
AS
SELECTdbo.teacher.teaname,dbo.class.claname,dbo.subject.subname
FROMdbo.classINNERJOIN
dbo.cla_teaONdbo.class.claid=dbo.cla_tea.claidINNERJOIN
dbo.teacherONdbo.cla_tea.teaid=dbo.teacher.teaidINNERJOIN
dbo.cla_subONdbo.class.claid=dbo.cla_sub.claidINNERJOIN
dbo.subjectONdbo.cla_sub.subid=dbo.subject.subid
(2)使用“教师授课情况”视图查询为班级号为“3”授课的相关信息。
select*
fromView_教师授课情况
whereclaid='3'
(3)将“教师授课情况”视图改名为“教师授课信息”。
sp_rename更改视图的名称,但是我们建议您删除现有视图,然后使用新名称重新创建它。
EXECsp_rename'View_教师授课情况','教师授课信息'
(4)修改“教师授课情况”视图的内容,使得该视图能查询到“计算机科学与技术”班级的授课情况。
ALTERVIEWdbo.教师授课信息
as
SELECTdbo.teacher.teaname,dbo.class.claid,dbo.class.claname,dbo.subject.subname
FROMdbo.classINNERJOIN
dbo.cla_teaONdbo.class.claid=dbo.cla_tea.claidINNERJOIN
dbo.teacherONdbo.cla_tea.teaid=dbo.teacher.teaidINNERJOIN
dbo.cla_subONdbo.class.claid=dbo.cla_sub.claidINNERJOIN
dbo.subjectONdbo.cla_sub.subid=dbo.subject.subid
WHERE(dbo.class.claname='计算机科学与技术')
(5)用SQL语句删除“教师授课情况”视图。
DropVIEWdbo.教师授课情况
(2)索引的基本操作
(1)为stusystem数据库中的“学生”表创建基于“班级”列的非聚集索引xs_bj_index。
CREATENONCLUSTEREDindexxs_bj_indexonstudent(claid)
(2)为stusystem数据库中学生成绩表的成绩字段建立一个非聚集索引,名为xscj_index。
CREATENONCLUSTEREDindexxscj_indexondbo.achievement(score)
(3)使用sp_helpindex查看学生表上的索引信息。
Execsp_helpindexstudent
显示结果
index_nameindex_descriptionindex_keys
index_namenonclustered,uniquelocatedonPRIMARYstuname
PK__student__AEC8BBF70CBAE877clustered,unique,primarykeylocatedonPRIMARYstuid
xs_bj_indexnonclusteredlocatedonPRIMARYclaid
(4)验证ALTERINDEX语句,重新生成索引。
ALTERINDEXxscj_indexONdbo.achievementREBUILD
(5)用SQL语句删除索引xs_bj_index
DROPINDEXxs_bj_indexONstudent
四、讲解、演示及提供辅导材料
1.讲解实验要求。
2.通过广播系统演示并讲解实验内容。
3.动画演示操作并提供实验要求电子版及实验报告模板。
实验五:
T-SQL程序设计
一、实验目的
●掌握程序中的批处理、脚本和注释的基本概念和使用方法。
●掌握程序中的流程控制语句。
●掌握SQLServer三类用户自定义函数的创建方法。
二、实验准备
(1)了解T-SQL语言基础。
(2)了解T-SQL语言的基本控制语句流程。
(3)SQLServer常用系统函数的基本功能。
(4)了解SQLServer三类用户自定义函数的语法。
3、实验内容
(一)T-SQL基础
(1)查询student表,只要有年龄小于20岁的学生,就将年龄最小的那个学生删掉,如此循环下去,直到所有的学生的年龄都不小于20岁,或是学生的总人数小于20就退出循环。
(2)在stusystems表上如果存在出生时间在1988年以前的学生,给出这些学生的列表。
否则给出一条提示信息,说明没有满足条件的学生。
(3)在班级表中的班级名称后加‘计算机’(要求使用replace函数,用法:
replace(串1,串2,串3),其功能是将串1中的串2替换为串3)
(4)利用DATEDIFF函数查询学生的姓名及年龄。
(5)将学生成绩表(achievement)中添加一个新的字段“成绩等级”,并按照学生的成绩确定“成绩等级”的值。
即:
成绩大于等于90为优,成绩在80~89之间为良,成绩在70~79之间为中,成绩在60~79之间为及格,成绩小于60为不及格。
(6)显示所有学号为‘201410001’的学生信息,并且在显示之前,暂停1分钟。
waitfordelay'00:
01:
00'
select*fromstusystemswherestu_id='201410001'
(二)用户自定义函数
SQLServer三类用户自定义函数的创建:
(1)创建一个自定义函数class_rs(),根据班级代码返回该班级学生总人数。
(2)创建一个自定义函数teacher_info(),根据教师编号返回该教师所带班级的基本信息(包含教师姓名及所授课班级名等信息)
(3)创建多语句表值函数stu_teach_score,根据课程名,返回选修该课程的学生的成绩信息.
(4)熟悉SQLServer中对自定义函数进行修改及删除的操作。
四、讲解、演示及提供辅导材料
1.讲解实验要求。
2.通过广播系统演示并讲解实验内容。
3.动画演示操作并提供实验要求电子版及实验报告模板。
五、参考答案:
(一)
3.updateclass_bf
setclaname=REPLACE(claname,claname,claname+'jsj')
5.altertableachievement
adddjvarchar(10)
updateachievementsetdj=case
whenscorebetween90and100then'优'
whenscorebetween80and89then'良'
whenscorebetween70and79then'中'
whenscorebetween60and69then'及格'
else'不及格'
end
6.waitfordelay'00:
00:
05'
select*fromstusystemswherestu_id='201410001'
(二)SQLServer三类用户自定义函数的创建:
1创建一个自定义函数department(),根据系部代码返回该系部学生总人数
createfunctiondepartment(@axbdmchar
(2))
returnsint
as
begin
declare@acountint
select@acount=count(*)from学生where系部代码=@axbdm
return@acount
end
2创建一个自定义函数teacher_info(),根据教师编号返回该教师任课基本信息
Usestusystem
Go
createfunctionteacher_info(@jsbhint)
returnstable
as
return
(selectteacher.teaname,teacher.teaid,class.clanamefrom
teacher,class,cla_tea
whereteacher.teaid=cla_tea.teaidandcla_tea.claid=class.claid
andteacher.teaid=@jsbh)
3.创建多语句表值函数stu_teach_score,根据课程名,返回选修该课程的学生的成绩信息.
createfunctionstu_sub_score(@kcmvarchar(20))
returns@xkcjtable
(snoint,
snamevarchar(20),
cnamevarchar(20),
gradeint)
begin
insertinto@xkcj
selects.stuid,s.stuname,c.subname,sc.score
fromstudents,subjectc,achievementsc
wheres.stuid=sc.stuidandsc.subid=c.subid
andc.subname=@kcm
return
end
实验六:
存储过程的应用
一、实验目的
●掌握利用查询分析器或对象资源管理器创建存储过程;
●掌握存储过程的执行与维护;
●理解使用存储过程来维护数据完整性。
2、实验内容
本次实验所用的数据库主要包括的数据表为:
学生、课程和成绩,其创建脚本如下:
CREATETABLEstudentinfo
(学号char(10),姓名char(8),性别char
(2),年级int,出生日期datetime)
GO
CREATETABLEcourse
(课程号char(4),课程名varchar(20),学分int)
GO
CREATETABLEstumark
(考试号char(10),学号char(10),
笔试成绩decimal(18,1),机试成绩decimal(18,1))
GO
(1)一个能向创建学生表中插入一条记录的存储过程Insert_student,该过程需要5个参数,分别用来传递学号、姓名、性别、年级、出生日期5个值。
createprocinsert_student
@snochar(10),@snamechar(8),@sexchar
(2),@sgradeint,@sbirthdatetime
as
begin
insertintostudentinfovalues(@sno,@sname,@sex,@sgrade,@sbirth)
end
(2)写出执行存储过程Insert_student的SQL语句,向学生表中插入一个新同学,并提供相应的实参值(实参值自定)。
execinsert_student'100001','张三','男','1','1990/1/1'
(3)创建一个向课程表中插入一门新课程的存储过程Insert_course,该存储过程需要三个参数,分别用来传递课程号、课程名、学分,但允许参数“学分”的默认值为2,即当执行存储过程Insert_course时,未给参数“学分”提供实参值时,存储过程将按默认值2进行运算。
createprocinsert_course
@cnochar(4),@cnamevarchar(20),@creditint=2
as
begin
insertintocoursevalues(@cno,@cname,@credit)
end
(4)执行存储过程Insert_course,向课程表Course中插入一门新课程。
分两种情况写出相应的SQL命令:
①提供三个实参值执行存储过程Insert_course
execinsert_course'1001','db','4'
②只提供二个实参值执行存储过程Insert_course,即:
不提供与参数“学分”对应的实参值。
execinsert_course@cno='1002',@cname='os',@credit=default
execinsert_course'1003','net',default
(5)创建一个名为Query_stusystem的存储过程,该存储过程的功能是根据学号查询学生表中某一学生的姓名、年级、性别及出生日期。
createprocedurequery_stusystem@snochar(10)
as
begin
select姓名,年级,性别,出生日期fromstudentinfowhere学号=@sno
end
(6)执行存储过程Query_stusystem,查询学号为”20060201”的学生的学号、班级号、性别及出生日期。
写出完成此功能的SQL命令。
execquery_stusystem'20060201'
(7)修改存储过程Query_stusystem,增加自定义处理错误信息,用来提示指定的学生学号不存在。
ALTERprocedurequery_stusystem@snochar(10)
as
IF(selectcount(*)fromstudentinfowhere学号=@sno)=0
begin
PRINT'不存在该学生'
RETURN
end
ELSE
BEGIN
select姓名,年级,性别,出生日期fromstudentinfowhere学号=@sno
END
(8)请创建存储过程,查看本次考试平均分以及未通过考试的学员名单。
Createproccheck_score@test_numchar(10)
asbegin
select@test_numas'考试号',avg(笔试成绩)'笔试平均成绩',avg(机试成绩)'机试平均成绩'fromstumarkwhere考试号=@test_num
select姓名as'未通过考试名单'fromstudentinfo,stumark
wherestudentinfo.学号=stumark.学号and(笔试成绩<60or机试成绩<60)and考试号=@test_num
end
执行:
execcheck_score'0001'
(9)修改上例:
由于每次考试的难易程度不一样,每次笔试和机试的及格线可能随时变化(不再是60分),这导致考试的评判结果也相应变化。
分析:
上述存储过程添加2个输入参数@writtenPass(笔试及格线)和@labPass(机试及格线)
alterproccheck_score@test_numchar(10),@writtenpassdecimal(18,1),@labpassdecimal(18,1)
asbegin
select@test_numas'考试号',avg(笔试成绩)'笔试平均成绩',avg(机试成绩)'机试平均成绩'fromstumark
where考试号=@test_num
select姓名as'未通过考试名单'fromstudentinfo,stumark
wherestusystem.学号=stumark.学号
and(笔试成绩<@writtenpassor机试成绩<@labpass)
and考试号=@test_num
end
--执行:
execcheck_score'0001',75,75
(10)如何修改上例存储过程,根据每次统考指定的及格分数线,显示通过考试的学员名单并返回及格人数。
(提示:
用输出参数)
alterproccheck_score2@test_numchar(10),@writtenpassdecimal(18,1),
@labpassdecimal(18,1),@numintoutput
As
begin
select姓名as'通过考试名单'fromstusystem,stumark
wherestusystem.学号=stumark.学号
and笔试成绩>=@writtenpassand机试成绩>=@labpass
and考试号=@test_num
select@num=count(*)fromstumark
where笔试成绩>=@writtenpassand机试成绩>=@labpass
and考试号=@test_num
End
--执行:
declare@numint
execcheck_score2'0001',75,75,@numoutput
select@num'通过考试人数'
方法二:
在查询中使用COMPUTE子句
createproccheck_score2_1@test_numchar(10),@writtenpassdecimal(18,1),
@labpassdecimal(18,1)
asbegin
select姓名as'通过考试名单'fromstusystem,stumark
wherestusystem.学号=stumark.学号
and笔试成绩>=@writtenpassand机试成绩>=@labpass
and考试号=@test_numcomputecount(姓名)
end
(11)思考:
如何返回及格率?
(提示:
①用输出参数②存储过程中用查询赋值语句分别求出及格人数与总人数,再求出及格率。
)
alterproccheck_score3@test_numchar(10),@writtenpassdecimal(18,1),
@labpassdecimal(18,1),@rateintoutput
As
begin
declare@passnumreal,@sum_numreal
select@passnum=count(*)fromstumark
where笔试成绩>=@writtenpassand机试成绩>=@labpassand考试号=@test_num
select@sum_num=count(*)fromstumark
where考试号=@test_num
select@rate=(@passnum/@sum_num)*100
end
--执行:
(注意保存人数的两个变量的类型不能为int)
Declare@rateint
execcheck_score3'0001',60,60,@rateoutput
select@rate'考试及格率%'
实验七:
触发器的应用
1、实验目的
●掌握DML和DDL触发器的创建方法;
●掌握DML和DDL触发器的查看、修改、重命名和删除方法。
2、实验内容
(1)创建触发器
(1)创建一个当学生表中插入一个新同学信息时能自动列出全部同学学生信息的触发器Display_trigger;向学生表中插入一新同学信息,验证触发器Display_trigger是否被执行。
createtriggerdisplay_trigger
onstudent
afterinsert
as
begin
select*fromstudent
end
(2)创建一个触发器delete_trigger,当向学生表中删除同学信息时显示'学生表中以下信息进行了删除操作:
'并将删除的的学生信息显示出来。
验证触发器delete_trigger是否被执行。
createtriggerdelete_triggeronstudent
afterdelete
as
begin
select'学生表中以下信息进行了删除操作:
'
select*fromdeleted
end
(3)创建一个DML触发器ins_sub_score,当对成绩表添加一条数据时,首先判断一下学号和课程号在学生表和课程表是否存在,若不在,拒绝插入。
CREATETRIGGERins_course_scoreoncourse_scoreafterinsert
AS
BEGIN
IFEXISTS(SELECT*FROMinsertedaWHEREa.stu_idnotin(SELECTb.stu_idFROMstudentsb)ora.cour_idnotin(SELECTc.cour_idFROMcoursesc))
BEGIN
RAISERROR('违背了数据的一致性',16,1)
ROLLBACKTRANSACTION
END
ELSE
RAISERROR('插入成绩记录成功!
',16,10)
END
GO
(4)在stusystem数据库中创建删除班级触发器remofve_class,作用是:
如果要删除一个班级,则首先需要删除其他表中与该班级有关联的信息。
即:
删除cla_sub表中与该班有关的记录,也就是取消改班级与相关科目的关联关系;
删除cla_tea表中与该班有关的记录,也就是取消改班级与教师的关联关系;
删除student表中与该班有关的记录,也就是删除该班级的学生及achievement表中学生的成绩信息。
createtriggerremofve_classonclass
afterdelete
as
begin
deletecla_subwhereclaidin(selectclaidfromdeleted)
deletecla_tea
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库