数据库上机实验题(七、八、九)(共6页)

1、精选优质文档-倾情为你奉上上机实验题7相关知识：第14章。目的：通过本实验，掌握使用函数和存储过程的设计和使用方法。实验内容：编写满足如下各功能的程序：（1）在Library数据库中创建一个标量值函数Sum(n)，求1+2+n之和。并用相关数据进行测试。（2）在Library数据库中创建一个内联表值函数nbook，返回指定系的学号、姓名、班号、所借图书名和借书日期。并用相关数据进行测试。（3）在Library数据库中创建一个多语句表值函数pbook，返回系名和该系所有学生所借图书的平均价格。并用相关数据进行测试。（4）设计一个存储过程，查询每种图书品种的数目。并用相关数据进行测试。（5）设计一

2、个存储过程，采用模糊查询方式查找借阅指定书名的学生，输出学号、姓名、班号和书名。并用相关数据进行测试。设计过程：（1）对应的参考程序如下：USE LibraryGOIF EXISTS(SELECT * FROM sysobjects WHERE name='Sum' AND type='FN') -如果存在这样的函数则删除之 DROP FUNCTION SumGOCREATE FUNCTION Sum(n int) -输入参数RETURNS intASBEGIN DECLARE s int,i int SET s=0 SET i=1 WHILE i<=n

3、BEGIN SET s=s+i SET i=i+1 END RETURN(s)ENDGOPRINT '1+2+.+60='+CAST(dbo.Sum(60) AS char(10)GO（2）对应的参考程序如下：USE LibraryGOIF EXISTS(SELECT * FROM sysobjects -如果存在这样的函数则删除之 WHERE name='nbook' AND (type='IF' OR type ='TF') DROP FUNCTION nbookGOCREATE FUNCTION nbook(dname ch

4、ar(16)-建立函数nbook RETURNS TABLE-返回表，没有指定表结构，这是内联表值函数的特征ASRETURN ( SELECT s.学号,s.姓名,s.班号,b.图书名,bor.借书日期 FROM student s,depart sc,borrow bor,book b WHERE s.班号=sc.班号 AND s.学号=bor.学号 AND b.图书编号=bor.图书编号 AND sc.系名=dname)GOSELECT * FROM nbook('计算机系')ORDER BY 学号GO（3）对应的参考程序如下：USE LibraryGOIF EXISTS(

5、SELECT * FROM sysobjects -如果存在这样的函数则删除之 WHERE name='pbook' AND (type='IF' OR type ='TF') DROP FUNCTION pbookGOCREATE FUNCTION pbook() -建立函数pbook RETURNS st TABLE ( 系名 char(16), 平均价格 decimal(4,1) )AS BEGIN INSERT st-向st中插入满足条件的记录 SELECT sc.系名 AS '系名',AVG(b.定价) AS '

6、平均价格' FROM depart sc,student s,book b,borrow bor WHERE s.班号=sc.班号 AND s.学号=bor.学号 AND b.图书编号=bor.图书编号 GROUP BY sc.系名 RETURN ENDGOSELECT * FROM pbook()GO（4）对应的参考程序如下：USE LibraryGO-若存在存储过程proc1，则删除之IF EXISTS(SELECT * FROM sysobjects WHERE name='proc1' AND type='P') DROP PROCEDURE p

7、roc1GO-创建存储过程proc1CREATE PROCEDURE proc1AS SELECT 图书名 AS '书名',COUNT(*) AS '数目' FROM book GROUP BY 图书名GOEXEC proc1（5）对应的参考程序如下：USE LibraryGO-若存在存储过程proc2，则删除之IF EXISTS(SELECT * FROM sysobjects WHERE name='proc2' AND type='P') DROP PROCEDURE proc2GO-创建存储过程proc2CREATE PR

8、OCEDURE proc2(bn char(16)AS DECLARE pt char(16) SET pt='%'+RTRIM(bn)+'%' SELECT s.学号,s.姓名,s.班号,b.图书名 FROM student s,book b,borrow bor WHERE s.学号=bor.学号 AND b.图书编号=bor.图书编号 AND b.图书名 LIKE ptORDER BY s.班号GOEXEC proc2 '数'上机实验题8相关知识：第15章。目的：通过本实验，掌握触发器的设计和使用方法。实验内容：编写满足如下各功能的程序：（

9、1）在borrow上建立一个触发器，完成如下功能：如果读者借阅的书名是“网络工程”，就将该借书记录保存在borrow1表中（borrow1表结构同borrow相同）。（2）在borrow上建立一个触发器，完成如下功能：当删除borrow表中任何记录时，将该记录保存保存在borrow1表中（borrow1表结构同borrow相同）。（3）删除前面创建的触发器。设计过程：（1）对应的参考程序如下：USE LibraryGO-若不存在borrow1表，则通过复制创建表borrow1IF NOT EXISTS(SELECT name FROM sysobjects WHERE name='bo

10、rrow1' AND type='U')BEGIN SELECT * INTO borrow1 FROM borrow DELETE borrow1ENDGOCREATE TRIGGER trig1 ON borrowFOR INSERT,UPDATEASIF ROWCOUNT>0 INSERT borrow1 SELECT i.* FROM inserted i,book b WHERE i.图书编号=b.图书编号 AND b.图书名='网络工程'GOINSERT INTO borrow VALUES('11','2009

11、0','2014-4-15')GOSELECT * FROM borrow1GO说明：在本实验完成后，请将borrow表中新插入的记录删除，仍保持数据不变。（2）对应的参考程序如下：USE LibraryGOIF NOT EXISTS(SELECT name FROM sysobjects WHERE name='borrow1' AND type='U')BEGIN SELECT * INTO borrow1 FROM borrow DELETE borrow1ENDGOCREATE TRIGGER trig2 ON borrowFOR

12、 DELETEASIF ROWCOUNT>0 INSERT borrow1 SELECT * FROM deletedGO（3）对应的参考程序如下：USE LibraryGODROP TRIGGER trig1DROP TRIGGER trig2GO上机实验题9相关知识：第16章。目的：通过本实验，掌握使用T-SQL语句进行SQL Server安全管理的方法。实验内容：编写满足如下各功能的程序：（1）创建一个登录账号liblog，其密码为“”。（2）为liblog登录账号在Library数据库中创建一个数据库用户账号liblog。（3）将Library数据库中建表的权限授予libuser

13、数据库用户账号，然后收回该权限。（4）将Library数据库中表student上的INSERT、UPDATE和DELETE权限授予libuser数据库用户账号，然后收回该权限。（5）删除前面创建的登录账号liblog和数据库用户账号libuser。设计过程：（1）对应的参考程序如下：CREATE LOGIN liblog WITH PASSWORD=''GO（2）对应的参考程序如下：USE LibraryCREATE USER libuser FOR LOGIN liblogGO（3）对应的参考程序如下：USE LibraryGOGRANT CREATE TABLE TO libuserGOREVOKE CREATE TABLE FROM l