数据库.docx
《数据库.docx》由会员分享，可在线阅读，更多相关《数据库.docx（27页珍藏版）》请在冰豆网上搜索。
数据库
实验一：
数据定义及更新语句练习
一、实验目的：
熟练掌握用SQL语句实现数据库和基本表的创建、数据的更新。
二、预习要求：
实验前复习讲授过的有关创建数据库、基本表的SQL语句，预习SQLServer2000环境，特别是资源管理器和查询分析器的相关用法。
三、实验内容：
（一）用SQL语句建立如下mySPJ数据库，包括S，P，J，和SPJ四个基本表（教材第二章习题5中的四个表），要求实现关系的三类完整性。
S（SNO,SNAME,STATUS,CITY）;
P（PNO,PNAME,COLOR,WEIGHT）;
J（JNO,JNAME,CITY）;
SPJ（SNO,PNO,JNO,QTY）
供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成。
零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成。
工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成。
供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。
（二）分别使用插入、删除、修改的方式更新基本表中的数据。
四、实验方法和步骤：
（一）使用MicrosoftSQLServer企业管理器和查询分析器建立数据库mySPJ：
1.打开“开始－>程序－>MicrosoftSQLServer－>企业管理器”；
2.在企业管理器左边的树标签中依次打开“MicrosoftSQLServer－>SQLServer组－>（local）（WindowsNT）－>数据库”，（local）（WindowsNT）前的红色标记转化为绿色标记表明NT服务已启动；
3.从企业管理器的“工具”菜单中选择“SQL查询分析器”，打开查询分析器后，在其窗口书写创建数据库mySPJ的SQL语句，点击执行按钮（或F5键）执行该SQL语句；
4.在企业管理器左边的树标签中查看数据库是否建立成功。
（二）在数据库mySPJ中建立S，P，J，和SPJ四个基本表：
按照实验内容给出的基本表结构在查询分析器窗口中书写SQL语句分别建立各表，并设置主键和外键约束；
（三）更新表数据：
1.利用Insert语句将习题中给出的示例记录插入各表。
2.利用Update更新表中的记录：
①将p表中的所有红色零件的重量增加5。
②将spj表中所有天津供应商的QTY属性值减少10。
用子查询。
3.利用Delete语句删除p表中的所有红色零件的记录。
4.SQL语句执行后返回基本表查看更新后的结果，如果与期望不符，分析原因并记录在实验报告中。
五、思考题：
（一）在为各表设定关键字时弹出的页面中有关键字和索引选项，试通过给基本表设定这两个项目并说明二者的区别。
（二）在基本表中输入数据时，注意数据与字段的数据类型和长度以及能否为空的属性是否一致，保存时如有错误分析错误原因，及时改正并将错误报告写在实验报告中。
六、实验报告要求：
（一）实验目的：
熟练掌握用SQL语句实现数据库和基本表的创建、数据的更新。
（二）实验内容：
需要创建的数据库及其四个基本表：
零件表、供应商表、工程表、供应关系表的表结构、创建过程。
（三）完成情况（附上设计的SQL语句）。
（四）实验结果：
如果是查询语句，将查询结果记录列出来，其它可以不写。
（五）问题及解决：
首先写出执行语句不成功的时候系统报告的错误信息。
然后分析错误原因，并给出解决办法。
（六）回答思考题提出的问题，
（七）实验总结：
心得体会，建议等。
实验二：
简单查询和连接查询
一、实验目的：
熟练掌握用SQL语句实现的简单查询和多个数据表连接查询。
二、预习要求：
实验前复习讲授过的有关简单查询与多表查询的知识，编写相应的SQL语句。
三、实验内容：
（一）完成下面的简单查询：
①查询所有“天津”的供应商明细；
②查询所有“红色”的14公斤以上的零件。
③查询工程名称中含有“厂”字的工程明细。
（二）完成下面的连接查询：
①等值连接：
求s表和j表的相同城市的等值连接。
=
②自然连接：
查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。
③笛卡尔积：
求s和p表的笛卡尔积
④左连接：
求j表和spj表的左连接。
⑤右连接：
求spj表和j表的右连接。
四、实验方法和步骤：
参照实验一中给出的使用MicrosoftSQLServer企业管理器和SQL查询分析器的方法，将实验内容中所要求的查询项目依次用SQL语句实现，并记录下执行结果。
五、思考题：
查看实验内容2中左连接和右连接的执行结果是否一致，为什么？
六、实验报告要求：
（一）实验目的：
熟练掌握用SQL语句实现数据库和基本表的创建、数据的更新。
（二）实验内容：
需要创建的数据库及其四个基本表：
零件表、供应商表、工程表、供应关系表的表结构、创建过程。
（三）完成情况（附上设计的SQL语句）。
（四）实验结果：
如果是查询语句，将查询结果记录列出来，其它可以不写。
（五）问题及解决：
首先写出执行语句不成功的时候系统报告的错误信息。
然后分析错误原因，并给出解决办法。
（六）回答思考题提出的问题，
实验总结：
心得体会，建议等。
实验三：
分组查询和嵌套查询
一、实验目的：
熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。
二、预习要求：
实验前复习讲授过的有关分组查询和嵌套查询的知识，编写相应的SQL语句。
三、实验内容：
（一）分组查询：
1.求各种颜色零件的平均重量。
2.求北京供应商和天津供应商的总个数。
3.求各供应商供应的零件总数。
4.求各供应商供应给各工程的零件总数。
5.求使用了100个以上P1零件的工程名称。
6.求各工程使用的各城市供应的零件总数。
（二）嵌套查询：
1.in连接谓词查询：
①查询没有使用天津供应商供应的红色零件的工程名称。
②查询供应了1000个以上零件的供应商名称。
（having）
2.比较运算符：
求重量大于所有零件平均重量的零件名称。
3.Exists连接谓词：
①查询供应J1的所有的零件都是红色的供应商名称。
②至少用了供应商S1所供应的全部零件的工程号JNO。
四、实验方法和步骤：
参照实验一中给出的使用MicrosoftSQLServer企业管理器和SQL查询分析器的方法，将实验内容中所要求的查询项目依次用SQL语句实现，并记录下执行结果。
五、思考题：
（一）嵌套查询中的in连接谓词查询，关键字in可以直接用any代替么？
什么情况下in和any可以互相代替？
（二）嵌套查询中的内查询为外查询返回的内容是什么？
是表达式？
视图？
还是物理数据集合？
六、实验报告要求：
（一）实验目的：
熟练掌握用SQL语句实现数据库和基本表的创建、数据的更新。
（二）实验内容：
需要创建的数据库及其四个基本表：
零件表、供应商表、工程表、供应关系表的表结构、创建过程。
（三）完成情况（附上设计的SQL语句）。
（四）实验结果：
如果是查询语句，将查询结果记录列出来，其它可以不写。
（五）问题及解决：
首先写出执行语句不成功的时候系统报告的错误信息。
然后分析错误原因，并给出解决办法。
（六）回答思考题提出的问题，
（七）实验总结：
心得体会，建议等。
实验四：
视图操作和数据控制
实验四：
视图操作和数据控制
一、实验目的：
根据自己掌握的有关视图操作和数据控制的知识，编写相应的SQL语句。
最终达到熟练掌握用SQL语句实现视图操作和数据控制。
二、实验内容：
（1）定义如下视图：
①查询北京的供应商的编号、名称和城市。
②查询S1供应商的所有供应明细。
③查询各工程名称使用的各种颜色零件的个数。
查询上面定义的视图。
（二）数据控制：
①使用GRANT把对S表查询的权利授予WangLi。
②使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。
③使用REVOKE把LiMing对P表插入、删除的权利回收。
三、完成情况及查询结果：
（一）定义如下视图：
①查询北京的供应商的编号、名称和城市。
createviews1
as
selectsno,sname,city
froms
wherecity='北京'
②查询S1供应商的所有供应明细。
createviews4
as
selects.sno,sname,s.city,p.pno,j.jno,qty
froms,p,j,spj
wheres.sno='s1'ands.sno=spj.snoandp.pno=spj.pnoandj.jno=spj.jno
③查询各工程名称使用的各种颜色零件的个数。
createviews3
as
selectjname,color,qty
fromp,j,spj
wherep.pno=spj.pnoandj.jno=spj.jno
查询上面定义的视图。
（二）数据控制：
①使用GRANT把对S表查询的权利授予WangLi。
grantselect
ons
towangle
②使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。
grantselect,insert,update,delete
onp
toliming
③使用REVOKE把LiMing对P表插入、删除的权利回收。
revokeinsert,delete
onp
toliming
四、问题及解决：
1.问题：
在做使用GRANT把对S表查询的权利授予WangLi的时候，程序写的是：
grantselect
ontables
towangle
就会出现错误。
解决方案：
正确的程序应该为：
grantselect
ons
towangle
2.在做把一部分权利赋给wangli,或者是liming时，如果直接写程序，他只会显示程序无错，但是在执行的时候没有完成操作。
原因：
在把权力赋给某个人的时候没把用户创建进数据库中。
解决方法：
先把用户创建进去，方法是找到安全性，右键单击会弹出菜单，选中新建登陆，把名称改为：
wangli，选择SQLServer验证，数据库类型为myspj，就是在一开始新建的数据库名称，然后再选择数据库访问，还选择myspj，即可，创建新用户完成。
五、思考题：
实验内容中的哪些视图可以用来更新记录？
答：
数据控制语句语句可以更新记录。
六、心得体会：
通过本次实验，我对数据库的使用方式有了一定程度的了解，对数据库中建立一个表有了深刻的印象，以前学的只是都是在书本上的，但通过这次的实战，我更加牢固地掌握了以前学过的只是。
我感觉实践出真知，作为一名计算机工作人员，应该多实践，多实验，从实践中积累经验，获得经验，让自己获得更大的进步，只有不断的学习和实践，我们的能力才能不断的上升。
实验五：
触发器和存储过程
实验五：
触发器和存储过程
一、实验目的：
理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。
二、预习要求：
存储过程和触发器的相关概念，事务的相关概念，编写相应的SQL语句。
三、实验完成情况：
（一）为S表的删除操作定义一个触发器，在删除一个供应商记录时，将这个供应商的所有供应情况从spj表中删除。
CREATETRIGGERtgs_deleteONs
FORDELETE
AS
delete
fromspj
wheresnoin
（selectsnofromdeleted）
delete
froms
wheresno='s1';
（二）有一个小型的图书管理数据库，包含的表为：
bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表
borrowcard（cardid，ownername）；--借书证表
borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表
写一个存储过程，实现借书操作，要求有事务处理。
（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。
（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。
CREATEPROCEDUREsp_borrow
@mybookidchar（10）,@mycardidchar（10）,@bnamechar（30）output,@cnamechar（20）output,@mystatechar
（1）output/*,@mydatedatetimeoutput*/
AS
if@mybookidisnullor@mycardidisnull
begin
print'error:
yourbookidorcardidisnull'
return
end
else
begin
if（selectstatefrombookstorewherebookid=@mybookid）='y'
begin
updatebookstoresetstate='n'wherebookid=@mybookid
insertintoborrowlogvalues（@mycardid,@mybookid,getdate（）,null）
end
else
begin
print'error:
bookisborrowed!
'
end
select@bname=s.bookname,@cname=c.ownername,
/*@mydate=l.borrowdate,*/@mystate=s.state
frombookstores,borrowlogl,borrowcardc
wheres.bookid=@mybookidandc.cardid=@mycardidands.bookid=l.bookidandc.cardid=l.cardid
end
GO
执行:
（只剩下借阅日期的与char类型转换没解决）
DECLARE@bidvarchar（10）,@cidvarchar（10）,@bnamechar（30）,@cnamechar（20）/*,@mydatedatetime*/,@mystatechar
（1）
SET@bid='2008112601'set@cid='200602001'
EXECUTEsp_borrow@bid,@cid,@bnameoutput,@cnameoutput,/*@mydateoutput,*/@mystateoutput
SELECT'借阅人：
'+@cnameas'借阅人','图书名称'+@bnameas'图书名称'/*,@mydateas'借阅日期'*/,'借阅状态'+@mystateas'借阅状态'
加了事务处理
CREATEPROCEDUREsp_borrow
@mybookidchar（10）,@mycardidchar（10）,@bnamechar（30）output,@cnamechar（20）output,@mystatechar
（1）output,@mydatedatetimeoutput
AS
if@mybookidisnullor@mycardidisnull
begin
print'error:
yourbookidorcardidisnull'
return
end
else
begin
if（selectstatefrombookstorewherebookid=@mybookid）='y'
begin
begintransaction
updatebookstoresetstate='n'wherebookid=@mybookid
IF（@@ERROR<>0）
BEGIN
/**//*----------自定义错误输出----------*/
RAISERROR（'Insertdataerror!
',16,1）
/**//*--------事务回滚--------*/
ROLLBACKTRANSACTION
END
insertintoborrowlogvalues（@mycardid,@mybookid,getdate（）,null）
IF（@@ERROR<>0）
BEGIN
/**//*----------自定义错误输出----------*/
RAISERROR（'Insertdataerror!
',16,1）
/**//*--------事务回滚--------*/
ROLLBACKTRANSACTION
END
commit
end
else
begin
print'error:
bookisborrowed!
'
end
select@bname=s.bookname,@cname=c.ownername,@mydate=l.borrowdate,@mystate=s.state
frombookstores,borrowlogl,borrowcardc
wheres.bookid=@mybookidandc.cardid=@mycardidands.bookid=l.bookidandc.cardid=l.cardid
end
GO
执行
DECLARE@bidvarchar（10）,@cidvarchar（10）,@bnamechar（30）,@cnamechar（20）,@mydatedatetime,@mystatechar
（1）
SET@bid='2008112602'set@cid='200601001'
EXECUTEsp_borrow@bid,@cid,@bnameoutput,@cnameoutput,@mydateoutput,@mystateoutput
SELECT'借阅人：
'+@cnameas'借阅人','图书名称'+@bnameas'图书名称',@mydateas'借阅日期','借阅状态'+@mystateas'借阅状态'
四、实验方法和步骤：
（一）在查询分析器中编写实现上述功能的触发器，参考CreateTrigger语法。
（二）参考CREATEPROCEDURE语句。
在事务执行过程中引入错误触发事件，可以考虑在增加借书记录时违反实体完整性或参照完整性，以此体会事务的错误保护机制。
五、实验结果：
六、问题及解决办法：
1、问题：
破坏了关系完整性
解决办法:
在关系图S表的属性里的对insert和update强制关系选中。
2.问题：
当建立触发器是多加了一个SNO控制，S2及其他不能删除。
解决办法：
触发器删除重新建立连接。
七、思考题:
如何通过系统的设置实现类似的功能，而不需触发器？
答：
删除关系表的级联
八、实验感想：
通过本次试验使我对触发器的操作有所了解，并通过实践对其有所掌握。
通过阅读有关书籍知道了触发器的一些优点：
灵活性，强大性吧，具有实施更为复杂的检查和操作功能；具有更精细的数据控制能力；且其用起来也不是很复杂。
升级会员 