数据库第三部分.docx
- 文档编号:28581877
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:31
- 大小:472.17KB
数据库第三部分.docx
《数据库第三部分.docx》由会员分享,可在线阅读,更多相关《数据库第三部分.docx(31页珍藏版)》请在冰豆网上搜索。
数据库第三部分
实验报告
课程名称数据库技术实践
实验项目索引、存储过程和触发器、函数和游标
实验仪器SQLServer2008
系别____计算机科学与技术系_
专业____计算机科学与技术____
班级/学号___xxxxxxxxxxxxxxxxxxxxxxxx
学生XX_____xxxxxxxxxxx
实验日期___2xxxxxxxxxx
成绩_______________________
指导教师___梁琦______________
一.内容说明
1.索引
涉与索引类型,与不建立索引形成对比,可以提高执行效率
2.存储过程
存储过程是SQL语言在应用程序和数据库之间的主要编程接口,其存在形式有两种,一是在客户端存储代码,另一种是将SQL语言存储在数据库服务器端,由应用程序调用这些语言。
存储过程可以:
接受输入参数并以输出参数的形式将多个数值返回、包含执行数据库的语句、将查询语句执行结果返回到客户端,其好处有,允许模块化程序设计、改善性能、减少网络流量、可作为安全机制使用。
3.触发器
是有一段由对数据的更改操作引发的自动执行的代码,通常用于保证业务规则和数据完整性,用户可以编程的方法来实现复杂的处理逻辑和业务规则,增强了完整性约束的功能。
4.用户定义函数
可以扩展数据操作的功能,期中包括,变量函数和表值函数,标量函数返回单个数据值,表值函数返回一个表,表值函数又分为内联表值函数和语句表值函数。
5.游标
游标提供对结果集中的每一行或一部分行进行单独的处理,这在select无法实现的,其具有如下特点:
允许定位结构集中的特定行、允许从结果集的当前位置检索一行或多行、支持对结果集中当前行的数据进行修改、为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。
二.索引
本章上机练习均利用MySimpleDB数据库实现。
写出实现创建满足如下要求的索引的SQL语句,并执行这些语句。
1.请为下列查询设计一个最合适的索引,并查看建立索引前后该语句的执行计划,比较执行效率。
SELECTFirstName,LastName,EmailAddress,Phone
FROMPerson.Contact
WHEREPhoneBETWEEN'300'AND'350'
CREATENONCLUSTEREDINDEXIX_Person_Contact
ONPerson.Contact(Phone)
INCLUDE(FirstName,LastName,EmailAddress);
建立索引之前:
建立索引后:
2.Production.ProductReview表包含的列有:
ProductID(int)、ReviewerName(nvarchar(50))和ments(nvarchar(3850))。
假设经常执行下列形式的查询,请为该类查询创建合适的索引,以最大程度地提高查询效率。
SELECTments,ReviewerName
FROMProduction.ProductReview
WHEREProductID>=937andReviewerNamelike'[a-d]%';
createindexpidxonProduction.ProductReview(ProductID)
include(ments,ReviewerName)WHEREProductID>=937
建立索引前:
建立索引后:
3.在Person.Address表上创建具有一个键列(PostalCode)和四个非键列(AddressLine1、AddressLine2、City、StateProvinceID)的包含列索引。
查看索引建立前后下列查询语句的执行计划,观察索引对效率的提高情况。
SELECTAddressLine1,AddressLine2,City,StateProvinceID,PostalCode
FROMPerson.Address
WHEREPostalCodeBETWEENN'94000'andN'95999';
CREATENONCLUSTEREDINDEXIX_Address_PostalCode
ONPerson.Address(PostalCode)
INCLUDE(AddressLine1,AddressLine2,City,StateProvinceID);
索引使该查询速度变快了
建立索引前:
建立索引后:
4.设经常需要执行下列类型的查询,以统计2003年某一段时间各产品的销售总量。
SELECTProductID,SUM(sod.OrderQty)ASQtySold
FROMSales.SalesOrderHeadersoh
JOINSales.SalesOrderDetailsod
ONsoh.SalesOrderID=sod.SalesOrderID
WHEREsoh.OrderDate>='2003-08-02'
ANDsoh.OrderDate<'2003-08-31'
GROUPBYsod.ProductID
为尽可能提高该类查询的执行效率,请分别为Sales.SalesOrderHeader和Sales.SalesOrderDetail表建立合适的索引,并简单说明理由。
查看索引建立前后上述查询语句的执行计划,观察索引对该查询的效率提高情况。
索引使该查询速度变快了
三.存储过程和触发器
如无特别说明,以下各题均利用第6章建立的Students数据库以与Student、Course和SC表实现。
1.创建满足下述要求的存储过程,并查看存储过程的执行结果。
(1)查询每个学生的修课总学分,要求列出学生学号与总学分。
CREATEPROCEDURESELECT_STUDENT
AS
SELECTSno,sum(Credit)as总学分
FROMscjoincourseconc.o=sc.o
GroupbySno
EXECSELECT_STUDENT
(2)查询学生的学号、XX、修的课程号、课程名、课程学分,将学生所在系作为输入参数,默认值为“计算机系”。
执行此存储过程,并分别指定一些不同的输入参数值,查看执行结果。
CREATEPROCinformation
deptCHAR(20)='计算机系'
AS
SELECTsc$.sno,sname,sc$.o,ame,creditFROMstudent$sinnerjoinsc$ON
s.sno=sc$.snoinnerjoincourse$cONc.o=sc$.o
WHEREdept=dept
EXECinformation'信息管理系'
EXECSELECT_STUDENT1'通信工程系',null
(3)查询指定系的男生人数,其中系为输入参数,人数为输出参数。
CREATEPROCEDURESELECT_STUDENT2
t_Deptnvarchar(20),total_manintOUTPUT
AS
SELECTtotal_man=COUNT(*)FROMstudent
WHEREDept=t_DeptANDSex='男'
PRINTtotal_man
DECLARERE_manint--声明变量
EXECSELECT_STUDENT2'计算机系',RE_manOUTPUT
--PRINTRE_man
(4)查询指定学生(XX)在指定学期的选课门数和考试平均成绩,要求XX和学期为输入参数,选课门数和平均成绩用输出参数返回,平均成绩保留到小时点后2位。
createprocedureti_4
namechar(10),semesint,
countointoutput,
avgGnumeric(4,2)output
as
selectcounto=COUNT(*),avgG=avg(Grade*1.00)from
SCjoinCourseconSC.o=c.o
joinStudentsons.Sno=SC.Sno
whereSemester=semes
andSname=name
groupbySC.o
declarecint,anumeric(4,2)
execti_4"李勇",2,coutput,aoutput
printc
printa
(5)查询指定学生(学号)的选课门数。
如果指定学生不存在,则返回代码1;如果指定的学生没有选课,则返回代码2;如果指定学生有选课,则返回代码0,并用输出参数返回该学生的选课门数。
createprocedureti_5
snochar(7),
Countintoutput
as
ifexists(select*fromStudentwhereSno=sno)
begin
ifexists(select*fromscwheresno=sno)
begin
selectCount=count(o)
fromStudentleftjoinsconStudent.Sno=sc.Sno
wherestudent.Sno=sno
return0
end
else
return2
end
else
return1
(6)删除指定学生(学号)的修课记录,如果指定的学生不存在,则显示提示信息“没有指定学生”;如果指定的学生没有选课,则显示提示信息“该学生没有选课”。
学号为输入参数。
createprocedureti_6
snochar(7)
as
ifsnoisnotnullandexists(select*fromStudentwhereSno=sno)
begin
ifexists(select*fromSCwhereSno=sno)
begin
deletefromSCwhereSno=sno
end
else
print'该学生没有选课'
end
else
print'没有指定学生'
(7)修改指定课程的开课学期。
输入参数为:
课程号和修改后的开课学期。
createprocedureti_7
ochar(6),
semint
as
updateCourseset
Semester=sem
whereo=o
(8)在Course表中插入一行数据,课程号、课程名、学分、开课学期均为输入参数。
课程号为C100、课程名为操作系统、学分为4、开课学期为4,开课学期的默认值为3。
如果学分大于10或者小于1,则不插入数据,并显示提示信息“学分为1~10间的整数”。
createprocedureti_8
ochar(6),
amevarchar(20),
credittinyint,
semtinyint
as
ifcreditbetween1and10
insertintoCoursevalues(o,ame,credit,sem)
else
print'学分为1~10间的整数'
2.创建满足下述要求的DML触发器(前触发器、后触发器均可),并验证触发器执行情况。
(1)限制学生所在系的取值X围为{计算机系,信息管理系,数学系,通信工程系}
createtriggertri_dept
onStudentafterinsert,update
as
ifexists(select*frominserted
whereSdeptnotin('计算机系','信息管理系','数学系','通信工程系'))
rollback
go
(2)限制每个学期开设的课程总学分在20~30X围内。
createTRIGGEReve_total_credit
ONcourseAFTERINSERT
AS
declaretint
SELECTt=SUM(C.Credit)FROMCoursec
JOININSERTEDIONI.Semester=c.Semester
IF(t<20ort>30)
BEGIN
PRINT'本学期课程学分限制在--30之间!
'
ROLLBACK
END
插入正常的数据:
插入超限的数据提醒:
(3)限制每个学生每学期选课门数不能超过6门(设只针对单行插入操作)。
CREATETRIGGEReve_total_o
ONcourseAFTERINSERT
AS
declaretint
SELECTt=COUNT(*)FROMCoursec
JOININSERTEDIONI.Semester=c.Semester
JOINSCSONS.o=C.o
IF(t<0ort>6)
BEGIN
PRINT'本学期选课门数不能超过6门!
'
ROLLBACK
END
(4)限制不能删除有人选的课程。
createtriggertri_delo
oncourseafterdelete
as
ifexists(select*fromdeleteddwhered.oin(selectofromsc))
rollback
go
(5)利用10.2.2例6建立的Teachers表和Depts表,编写实现如下要求的触发器:
每当在Teachers表中修改了某个教师的职称时,自动维护Depts表中职称人数统计的一致性。
(考虑同时修改多名教师职称的情况)
(6)利用10.2.2例6建立的Teachers表和Depts表,首先为Depts表增加一个记录部门教师人数的列,列名为:
DeptCount,类型为整型。
然后编写实现如下要求的触发器:
每当在Teachers表中插入一行数据或者是删除一行数据时,自动维护Depts表中的相关信息。
四.函数和游标
如无特别说明,以下各题均利用第6章建立的Student、Course和SC表实现。
3.创建满足下述要求的用户自定义标量函数。
(1)查询指定学生已经得到的修课总学分(考试与格的课程才能拿到学分),学号为输入参数,总学分为函数返回结果。
并写出利用此函数查询0811101学生的XX、所修的课程名、课程学分、考试成绩以与拿到的总学分的SQL语句。
CREATEFUNCTIONDBO.FIND_XF(SNOCHAR(7))
RETURNSINT
AS
BEGIN
DECLAREXINT
SELECTX=SUM(CREDIT)FROMSTUDENTSJOINSC
ONS.SNO=SC.SNOJOINCOURSECONSC.O=C.O
wheregrade>=60ands.sno=SNO
RETURNX
END
SELECTsnameASXX,ameAS课程名,
creditAS课程学分,gradeAS考试成绩,
dbo.find_xf(s.sno)as总学分
fromSTUDENTSJOINSCON
S.SNO=SC.SNOJOINCOURSECONSC.O=C.O
wheres.sno=0811101
(2)查询指定系在指定课程(课程号)的考试平均成绩。
CREATEFUNCTIONDBO.FIND_AVG(DEPTCHAR(20),OCHAR(6))
RETURNSnumeric(4,2)
AS
BEGIN
DECLAREAVGnumeric(4,2)
SELECTAVG=AVG(GRADE)FROMSCJOIN
studentsonsc.sno=s.sno
joincourseconsc.o=c.o
wheresc.o=oanddept=dept
returnAVG
End
selectS.dept,Sc.o,
dbo.FIND_AVG(S.dept,sc.o)ASavg_GRADE
fromscjoinstudentsonsc.sno=s.sno
wheredept='信息管理系'
(3)查询指定系的男生中选课门数超过指定门数的学生人数。
createFUNCTIONDBO.FIND_man(DEPTCHAR(20),Oint)
returnsint
AS
BEGIN
DECLAREcumint
selectcum=COUNT(*)fromstudentsjoin
scons.sno=sc.sno
wheredept=deptandsex='男'
groupbys.SnohavingCOUNT(*)>o
returncum
End
selectsname,dept,ame,DBO.FIND_man(dept,1)as学生人数
fromstudentsjoinscons.sno=sc.sno
joincourseconc.o=sc.o
wheredept='计算机系'
4.创建满足下述要求的用户自定义内联表值函数。
(1)查询选课门数在指定X围内的学生的XX、所在系和所选的课程名。
CREATEfunctionfind_1(xint)
returnstable
return(selectsname,dept,ame
fromstudentsjoinscons.sno=sc.sno
joincourseconc.o=sc.o
wheres.snoin(
selectsnofromSCgroupbySnohavingCOUNT(*)>x))
--利用函数查询选课门数超过门的情况
select*fromfind_1(3)
(2)查询指定系的学生考试成绩大于等于90的学生的XX、所在系、课程名和考试成绩。
并写出利用此函数查询计算机系学生考试情况的SQL语句,只列出学生XX、课程名和考试成绩。
createfunctionfind_2(xchar(20))
returnstable
return(SELECTsname,dept,ame,gradeFROMstudentASs
LEFTJOINscONs.sno=sc.sno
LEFTJOINcourseAScONc.o=sc.o
WHEREgrade>=90ands.dept=x)
selectsname,ame,gradefromfind_2('计算机系')
5.创建满足下述要求的用户自定义多语句表值函数。
(1)查询指定系年龄最大的前2名学生的XX和年龄,包括并列的情况。
alterFUNCTIONDBO.FIND_AGE(DEPTCHAR(20))
RETURNSRET_FIND_AGETABLE(
SNAMECHAR(10),
AGEINT)
AS
BEGIN
INSERTINTORET_FIND_AGE
SELECTTOP2WITHtiessname,YEAR(GETDATE())-YEAR(BirTHDAY)AGE
FROMSTUDENTWHEREDEPT=DEPT
ORDERBYAGEDESC
RETURN
END
SELECTSNAME,AGEfromFIND_AGE('计算机系')
(2)查询指定学生(XX)的考试情况,列出XX、所在系、修的课程名和考试情况,其中考试情况列的取值为:
如果成绩大于等于90,则为“优”;如果成绩在80~89,则为“良好”;如果成绩在70~79,则为“一般”;如果成绩在60~69,则为“不太好”;如果成绩小于60,则为“很糟糕”。
并写出利用此函数查询李勇的考试情况的SQL语句。
createfunctionfind_3(xnchar(5))
returnstable
return(SELECTsname,dept,ame,grade,
case
whengrade>=90then'优'
whengradebetween80and89then'良'
whengradebetween70and79then'一般'
whengradebetween60and69then'不太好'
whengrade<60then'很糟糕'
ENDAS'考试情况'
FROMstudentASs
LEFTJOINscONs.sno=sc.sno
LEFTJOINcourseAScONc.o=sc.o
WHERESNAME=x)
SELECT*FROMFIND_3('李勇')
6.创建满足下述要求的游标。
(1)查询Java课程的考试情况,并按图11-18所示样式显示结果数据。
declareamevarchar(20),ochar(8),snamenchar(5),sexnchar
(1),
deptnvarchar(20),gradesmallint
--(a)声明游标
declarecur_ocursorfor
selectdistinctamefromCourse
whereoin(selectofromscwheregradeisnotnull
andame='VB')
opencur_o
--(3)取数据
fetchnextfromcur_ointoame
whileFETCH_STATUS=0
begin
print'课程名:
'+ame
print'XX性别所在系成绩'
-----------------------------
--
(2)对每一门课程,查询选了这门课程的学生(高等数学):
用游标实现
--(a)声明游标
declarecur_scursorfor
selectsname,sex,dept,gradefromstudentsjoinSCons.Sno=SC.Sno
joincourseconc.o=sc.o
whereame=ame
--(b)打开游标
opencur_s
--取数据处理
fetchnextfromcur_sintosname,sex,dept,grade
whileFETCH_STATUS=0
begin
ifgradeisnotnull
printsname+sex+''+dept+''+cast(gradeaschar(4))
else
printsname+sex+''+dept+'null'
fetchnextfromcur_sintosname,sex,dept,grade
end
closecur_s
deallocatecur_s
FETCHNEXTFROMcur_oINTOame
end
closecur_o
deallocatecur_o
(2)
统计每个系的男生人数和女生人数,并按图11-19所示样式显示结果数据。
DECLAREcountsINT,deptNVARCHAR(14),sexCHAR(10)
DECLAREc2CURSORFORSELECTdept,sex,count(*)FROMstudent$
GROUPBYsex,dept
OPENc2
FETCHNEXTFROMc2INTOdept,sex,counts
PRINT'系名性别人数'
PRINT'==============================='
whilefetch_status=0
begin
PRINTdept+''+sex+cast(countsASCHAR(4))
FETCHNEXTFROMc2INTOdept,sex,counts
END
CLOSEc2
DEALLOCA
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 第三 部分