数据库第三部分

1、实 验 报 告课程名称 数据库技术实践 实验项目 索引、存储过程和触发器、函数和游标 实验仪器 SQL Server 2008 系 别_计算机科学与技术系 _专 业_计算机科学与技术_ 班级/学号_xxxxxxxxxxxxxxxxxxxxxxxx学生姓名 _xxxxxxxxxxx 实验日期 _2xxxxxxxxxx成 绩 _ 指导教师 _ 梁琦 _ _一 内容说明1.索引 涉及索引类型，与不建立索引形成对比，可以提高执行效率2.存储过程存储过程是SQL语言在应用程序和数据库之间的主要编程接口，其存在形式有两种，一是在客户端存储代码，另一种是将SQL语言存储在数据库服务器端，由应用程序调用这些语

2、言。存储过程可以：接受输入参数并以输出参数的形式将多个数值返回、包含执行数据库的语句、将查询语句执行结果返回到客户端，其好处有，允许模块化程序设计、改善性能、减少网络流量、可作为安全机制使用。3.触发器是有一段由对数据的更改操作引发的自动执行的代码，通常用于保证业务规则和数据完整性，用户可以编程的方法来实现复杂的处理逻辑和业务规则，增强了完整性约束的功能。4.用户定义函数可以扩展数据操作的功能，期中包括，变量函数和表值函数，标量函数返回单个数据值，表值函数返回一个表，表值函数又分为内联表值函数和语句表值函数。5.游标游标提供对结果集中的每一行或一部分行进行单独的处理，这在select无法实现的

3、，其具有如下特点：允许定位结构集中的特定行、允许从结果集的当前位置检索一行或多行、支持对结果集中当前行的数据进行修改、为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。二. 索引本章上机练习均利用MySimpleDB数据库实现。写出实现创建满足如下要求的索引的SQL语句，并执行这些语句。1 请为下列查询设计一个最合适的索引，并查看建立索引前后该语句的执行计划，比较执行效率。SELECT FirstName,LastName, EmailAddress,Phone FROM Person.ContactWHERE Phone BETWEEN 300 AND 350CREATE

4、 NONCLUSTERED INDEX IX_Person_ContactON Person.Contact(Phone)INCLUDE (FirstName,LastName, EmailAddress);建立索引之前：建立索引后：2 Production.ProductReview 表包含的列有：ProductID（int）、ReviewerName（nvarchar(50) ）和 Comments（nvarchar (3850)）。假设经常执行下列形式的查询，请为该类查询创建合适的索引，以最大程度地提高查询效率。SELECT Comments,ReviewerNameFROM Produ

5、ction.ProductReview WHERE ProductID = 937 and ReviewerName like a-d%;create index pidx on Production.ProductReview(ProductID)include(Comments,ReviewerName) WHERE ProductID = 937建立索引前：建立索引后：3在Person.Address表上创建具有一个键列（PostalCode）和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的包含列索引。查看索引建立前后下列查询

6、语句的执行计划，观察索引对效率的提高情况。SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCodeFROM Person.AddressWHERE PostalCode BETWEEN N94000 and N95999;CREATE NONCLUSTERED INDEX IX_Address_PostalCodeON Person.Address(PostalCode)INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); 索引使该查询速度变快了建

7、立索引前：建立索引后：4.设经常需要执行下列类型的查询，以统计2003年某一段时间各产品的销售总量。 SELECT ProductID, SUM(sod.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.OrderDate = 2003-08-02 AND soh.OrderDate 2003-08-31GROUP BY sod.ProductID为尽可能提高该类查询的执行效率，

8、请分别为Sales.SalesOrderHeader和Sales.SalesOrderDetail表建立合适的索引，并简单说明理由。查看索引建立前后上述查询语句的执行计划，观察索引对该查询的效率提高情况。索引使该查询速度变快了三存储过程和触发器如无特别说明，以下各题均利用第6章建立的Students数据库以及Student、Course和SC表实现。1 创建满足下述要求的存储过程，并查看存储过程的执行结果。（1） 查询每个学生的修课总学分，要求列出学生学号及总学分。CREATE PROCEDURE SELECT_STUDENTAS SELECT Sno,sum(Credit) as 总学分 F

9、ROM sc join course c on o = o Group by Sno EXEC SELECT_STUDENT（2） 查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”。执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。CREATE PROC informationdept CHAR(20)=计算机系ASSELECT sc$.sno,sname,sc$.cno,cname,credit FROM student$ s inner join sc$ ON s.sno=sc$.sno inner join course$ c

10、 ON o=sc$.cnoWHERE dept=deptEXEC information 信息管理系EXEC SELECT_STUDENT1 通信工程系,null（3） 查询指定系的男生人数，其中系为输入参数，人数为输出参数。CREATE PROCEDURE SELECT_STUDENT2t_Dept nvarchar(20),total_man int OUTPUTASSELECT total_man = COUNT(*) FROM studentWHERE Dept=t_Dept AND Sex=男PRINT total_man DECLARE RE_man int -声明变量EXEC S

11、ELECT_STUDENT2 计算机系,RE_man OUTPUT-PRINT RE_man（4） 查询指定学生（姓名）在指定学期的选课门数和考试平均成绩，要求姓名和学期为输入参数，选课门数和平均成绩用输出参数返回，平均成绩保留到小时点后2位。 create procedure ti_4 name char(10),semes int,countCno int output,avgG numeric(4,2) outputasselect countCno =COUNT(*),avgG =avg(Grade*1.00) from SC join Course c on SC.Cno=c.Cno

12、join Student s on s.Sno=SC.Snowhere Semester=semesand Sname=namegroup by SC.Cnodeclare c int ,a numeric(4,2)exec ti_4 李勇, 2,c output,a outputprint c print a（5） 查询指定学生（学号）的选课门数。如果指定学生不存在，则返回代码1；如果指定的学生没有选课，则返回代码2；如果指定学生有选课，则返回代码0，并用输出参数返回该学生的选课门数。create procedure ti_5sno char(7),Count int outputas if

13、 exists (select * from Student where Sno=sno) beginif exists (select * from sc where sno=sno)begin select Count=count(Cno)from Student left join sc on Student.Sno=sc.Snowhere student.Sno=snoreturn 0endelse return 2end else return 1（6） 删除指定学生（学号）的修课记录，如果指定的学生不存在，则显示提示信息“没有指定学生”；如果指定的学生没有选课，则显示提示信息“该学

14、生没有选课”。学号为输入参数。 create procedure ti_6 sno char(7)as if sno is not null and exists(select * from Student where Sno=sno)begin if exists(select * from SC where Sno=sno) begin delete from SC where Sno=sno end else print 该学生没有选课endelseprint没有指定学生（7） 修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。 create procedure ti_7cn

15、o char(6),sem intas update Course set Semester=sem where Cno=cno（8） 在Course表中插入一行数据，课程号、课程名、学分、开课学期均为输入参数。课程号为C100、课程名为操作系统、学分为4、开课学期为4，开课学期的默认值为3。如果学分大于10或者小于1，则不插入数据，并显示提示信息“学分为110间的整数”。 create procedure ti_8cno char(6),cname varchar(20),credit tinyint,sem tinyintas if credit between 1 and 10inser

16、t into Course values(cno,cname,credit,sem) else print 学分为110间的整数2 创建满足下述要求的DML触发器（前触发器、后触发器均可），并验证触发器执行情况。（1） 限制学生所在系的取值范围为计算机系，信息管理系，数学系，通信工程系 create trigger tri_depton Student after insert, updateas if exists (select * from inserted where Sdept not in(计算机系,信息管理系,数学系,通信工程系) rollbackgo（2） 限制每个学期开设的课

17、程总学分在2030范围内。create TRIGGER eve_total_creditON course AFTER INSERTASdeclare t int SELECT t=SUM(C.Credit)FROM Course c JOIN INSERTED I ON I.Semester = c.Semester IF (t30)BEGIN PRINT 本学期课程学分限制在-30之间！ROLLBACKEND插入正常的数据：插入超限的数据提醒：（3） 限制每个学生每学期选课门数不能超过6门（设只针对单行插入操作）。CREATE TRIGGER eve_total_cnoON course

18、AFTER INSERTASdeclare t int SELECT t=COUNT(*)FROM Course c JOIN INSERTED I ON I.Semester = c.Semester JOIN SC S ON S.cno=C.cnoIF (t6)BEGIN PRINT 本学期选课门数不能超过6门！ROLLBACKEND（4） 限制不能删除有人选的课程。create trigger tri_delCnoon course after deleteas if exists (select * from deleted d where d.Cno in(select Cno fr

19、om sc) )rollbackgo（5） 利用10.2.2例6建立的Teachers表和Depts表，编写实现如下要求的触发器：每当在Teachers表中修改了某个教师的职称时，自动维护Depts表中职称人数统计的一致性。（考虑同时修改多名教师职称的情况）（6） 利用10.2.2例6建立的Teachers表和Depts表，首先为Depts表增加一个记录部门教师人数的列，列名为：DeptCount，类型为整型。然后编写实现如下要求的触发器：每当在Teachers表中插入一行数据或者是删除一行数据时，自动维护Depts表中的相关信息。四函数和游标如无特别说明，以下各题均利用第6章建立的Stude

20、nt、Course和SC表实现。3 创建满足下述要求的用户自定义标量函数。（1） 查询指定学生已经得到的修课总学分（考试及格的课程才能拿到学分），学号为输入参数，总学分为函数返回结果。并写出利用此函数查询0811101学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句。CREATE FUNCTION DBO.FIND_XF(SNO CHAR(7)RETURNS INT ASBEGIN DECLARE X INT SELECT X=SUM(CREDIT) FROM STUDENT S JOIN SC ON S.SNO=SC.SNO JOIN COURSE C ON SC.C

21、NO=C.CNOwhere grade=60 and s.sno=SNORETURN XENDSELECT sname AS 姓名,cname AS 课程名, credit AS 课程学分, grade AS 考试成绩, dbo.find_xf(s.sno) as 总学分 from STUDENT S JOIN SC ON S.SNO=SC.SNO JOIN COURSE C ON SC.CNO=C.CNO where s.sno=0811101（2） 查询指定系在指定课程(课程号)的考试平均成绩。CREATE FUNCTION DBO.FIND_AVG(DEPT CHAR(20),CNO C

22、HAR(6)RETURNS numeric(4,2)AS BEGIN DECLARE AVG numeric(4,2)SELECT AVG=AVG(GRADE) FROM SC JOIN student s on sc.sno=s.sno join course c on o=owhere o=cno and dept=deptreturn AVG Endselect S.dept,So,dbo.FIND_AVG(S.dept,o) AS avg_GRADE from sc join student s on sc.sno = s.sno where dept = 信息管理系（3） 查询指定系

23、的男生中选课门数超过指定门数的学生人数。create FUNCTION DBO.FIND_man(DEPT CHAR(20),CNO int)returns int AS BEGINDECLARE cum int select cum=COUNT(*) from student s join sc on s.sno=sc.sno where dept=dept and sex=男 group by s.Sno having COUNT(*) cnoreturn cum Endselect sname,dept,cname, DBO.FIND_man(dept, 1) as 学生人数 from

24、student s join sc on s.sno=sc.sno join course c on o=o where dept=计算机系 4 创建满足下述要求的用户自定义内联表值函数。（1） 查询选课门数在指定范围内的学生的姓名、所在系和所选的课程名。CREATE function find_1(x int)returns table return (select sname,dept,cname from student s join sc on s.sno=sc.sno join course c on o=o where s.sno in ( select sno from SC g

25、roup by Sno having COUNT(*) x )-利用函数查询选课门数超过门的情况select * from find_1(3) （2） 查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生姓名、课程名和考试成绩。create function find_2(x char(20)returns table return (SELECT sname,dept,cname,grade FROM student AS s LEFT JOIN sc ON s.sno = sc.sno LEFT JOI

26、N course AS c ON o = o WHERE grade = 90 and s.dept = x ) select sname,cname,grade from find_2(计算机系) 5 创建满足下述要求的用户自定义多语句表值函数。（1） 查询指定系年龄最大的前2名学生的姓名和年龄，包括并列的情况。alter FUNCTION DBO.FIND_AGE (DEPT CHAR(20)RETURNS RET_FIND_AGE TABLE(SNAME CHAR(10),AGE INT )AS BEGIN INSERT INTO RET_FIND_AGESELECT TOP 2 WIT

27、H ties sname, YEAR(GETDATE()-YEAR(BirTHDAY) AGE FROM STUDENT WHERE DEPT = DEPTORDER BY AGE DESCRETURNENDSELECT SNAME,AGE from FIND_AGE(计算机系)（2） 查询指定学生（姓名）的考试情况，列出姓名、所在系、修的课程名和考试情况，其中考试情况列的取值为：如果成绩大于等于90，则为“优”；如果成绩在8089，则为“良好”；如果成绩在7079，则为“一般”；如果成绩在6069，则为“不太好”；如果成绩小于60，则为“很糟糕”。并写出利用此函数查询李勇的考试情况的SQL语

28、句。create function find_3(x nchar(5)returns table return (SELECT sname,dept,cname,grade, case when grade =90 then 优 when grade between 80 and 89 then 良 when grade between 70 and 79 then 一般 when grade between 60 and 69 then 不太好 when grade 60 then 很糟糕 END AS 考试情况FROM student AS s LEFT JOIN sc ON s.sno

29、= sc.sno LEFT JOIN course AS c ON o = o WHERE SNAME=x ) SELECT *FROM FIND_3(李勇)6 创建满足下述要求的游标。（1） 查询Java课程的考试情况，并按图11-18所示样式显示结果数据。declare cname varchar(20),cno char(8),sname nchar(5),sex nchar(1),dept nvarchar(20),grade smallint-（a)声明游标declare cur_cno cursor for select distinct cname from Coursewher

30、e cno in (select cno from sc where grade is not null and cname = VB)open cur_cno-(3)取数据 fetch next from cur_cno into cnamewhile FETCH_STATUS = 0begin print 课程名: + cname print 姓名 性别所在系 成绩 - -(2)对每一门课程，查询选了这门课程的学生(高等数学):用游标实现 -（a）声明游标 declare cur_s cursor for select sname,sex,dept,grade from student s

31、 join SC on s.Sno=SC.Sno join course c on o = o where cname =cname -（b）打开游标 open cur_s -取数据处理 fetch next from cur_s into sname,sex,dept,grade while FETCH_STATUS = 0 begin if grade is not null print sname + sex + + dept + +cast(grade as char(4) else print sname + sex + + dept + null fetch next from c

32、ur_s into sname,sex,dept,grade end close cur_s deallocate cur_s FETCH NEXT FROM cur_cno INTO cnameendclose cur_cnodeallocate cur_cno图11-18 游标（1）的显示样式图11-19 游标（2）的显示样式（2） 统计每个系的男生人数和女生人数，并按图11-19所示样式显示结果数据。DECLARE counts INT,dept NVARCHAR(14),sex CHAR(10)DECLARE c2 CURSOR FOR SELECT dept,sex,count(*)

33、 FROM student$GROUP BY sex,deptOPEN c2FETCH NEXT FROM c2 INTO dept,sex,countsPRINT 系名 性别 人数PRINT =while fetch_status=0beginPRINT dept+ +sex+cast(counts AS CHAR(4)FETCH NEXT FROM c2 INTO dept,sex,countsENDCLOSE c2DEALLOCATE c2（3） 列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名下列出本系学生的姓名和性别；再列出下一个系名，然后在此系名下再列出该系学生姓名和性

34、别；以此类推，直至列出全部系。要求按图11-20所示样式显示结果数据。DECLARE sname CHAR(10),dept CHAR(14)DECLARE c3 CURSOR FOR SELECT DISTINCT dept FROM student$OPEN c3FETCH NEXT FROM c3 into deptWHILE fetch_status=0BEGINPRINT deptDECLARE c4 CURSOR FOR SELECT sname,dept FROM student$WHERE dept=deptOPEN c4FETCH NEXT FROM c4 INTO snam

35、e,deptWHILE fetch_status=0BEGINPRINT sname+deptFETCH NEXT FROM c4 INTO sname,dept ENDCLOSE c4DEALLOCATE c4PRINT=FETCH NEXT FROM c3 INTO deptENDCLOSE c3DEALLOCATE c3图11-20 游标（3）的显示样式（4）针对11.2.3中例4建立的Job表和数据,用游标对Job表数据进行如下修改：将工作级别相同的工作只保留工作编号较小的一项工作，同时，将这些工作的工作描述拼接为一个工作描述，中间用逗号分隔。修改后的数据示意如表11-3所示。表11-

36、3 改造后的Job表数据JobidDescLevelJ01软件开发，软件测试10J02硬件开发，硬件测试12J04硬件维护8DECLARE cur_Job CURSOR FOR SELECT * FROM Job;DECLARE ojdesc VARCHAR(40)DECLARE jobno CHAR(4),jdesc VARCHAR(40),level TINYINTOPEN cur_JobFETCH NEXT FROM cur_Job INTO jobno,jdesc,levelWHILE FETCH_STATUS = 0BEGINSET ojdesc = jdescDECLARE inj

37、obno CHAR(4),injdesc VARCHAR(40)DECLARE incur_Job CURSOR FOR SELECT jobid,decs FROM Job WHERE lvl=level and jobid != jobnoOPEN incur_JobFETCH NEXT FROM incur_Job INTO injobno,injdescWHILE FETCH_STATUS = 0BEGINSET ojdesc = ojdesc + , + injdesc-print ojdescDELETE FROM Job WHERE Jobid = injobnoFETCH NEXT FROM incur_Job INTO injobno,injdescENDPRINT ojdescCLOSE incur_JobDEALLOCATE incur_JobUPDATE Job SET decs=