数据库编程技术实验指导书.doc

《数据库编程技术实验指导书.doc》由会员分享，可在线阅读，更多相关《数据库编程技术实验指导书.doc（28页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库编程技术实验指导书计算机专业教研室海口经济学院2009年9月目 录实验一图书管理系统表的设计1实验二图书管理系统存储过程设计9实验三图书管理系统触发器的设计13实验四事务控制与并发处理19实验一图书管理系统表的设计【实验目的】表的创建，主外键的设置，数据库四大基本操作。【实验内容】（1） 表的设计。（2） 四大基本操作【实验步骤】一、数据库的启动图1.1数据库启动页面二、数据库启动默认界面图1.2数据库启动页面三、表的可视化创建：1. 创建BookManager数据库。图1.3创建BookManager数据库图1.4 BookManager数据库创建成功界面2. 创建ManagerUse

2、r表，并设置UserID为主键。设置主键UserID标识规范，标识增量为1，标识种子为1。图1.5创建ManagerUser表图1.6 ManagerUser表3. 设置UserName字段为索引。图1.7 UserName字段设为索引图1.8 ManagerUser表打开界面4. 填入数据：图1.9 ManagerUser中填入数据5. 同样创建其他的表格，表格名称与各表字段定义如下：1）表名：PublishCompany字段：填充数据：2）表名：Book字段： 填充数据：3）表名：Reader字段：填充数据：4）表名：BorrowBook字段：填充数据：四、表的sql语句操作： 1Mana

3、gerUser表创建与基本操作：CREATE TABLE ManagerUser1(UserID int IDENTITY(1,1) NOT NULL,UserName varchar(20) NOT NULL,UserPassword varchar(10) NOT NULL,UserSort varchar(10) NULL, CONSTRAINT PK_ManagerUser1 PRIMARY KEY (UserID ASC)插入：insert into ManagerUser1 values(张三,123456,系统管理员)查询：select UserName,UserPassword

4、 from ManagerUser1select * from ManagerUser1更新：update ManagerUser1 set UserName = 张雷 where UserID = 1删除：delete ManagerUser1where UserID = 1删除表：drop table ManagerUser1五 设置主外键关系：1. 点击 数据关系图，右键 选择 创建数据关系图。选中Book表，BorrowBook表，ManagerUser表，PublishCompany表和Reader表。点击添加。图2.0 选中要建立数据关系他表图2.1 未建立主外键关系2. 设置五表

5、之间的数据关系图2.2 建立主外键关系后的数据关系图 【实验报告要求】报告请用专门的实验报告纸书写，字迹清晰，格式规范。报告中应写清姓名、学号、实验日期、实验题目、实验目的、实验内容及实验过程。依据具体的试验内容，写出详细的试验步骤。-8-实验二图书管理系统存储过程设计【实验目的】掌握存储过程的创建与执行，完成图书管理系统存储过程的创建。【实验内容及步骤】一 ManagerUser表的存储过程创建与执行SQL调用例程：过程或者函数,存储过程，用户自定义函数（三种类型）存储过程：是由SQL语句和流控制语句构成的语句串，输入参数，输出参数优点：1.允许组件式编程2.执行速度快3.降低网络负载4.可

6、以作为一种安全机制流控制语句：begin.end/if else/while break continue/declare/goto label/return/waitfor/print/*/1.依次点击：可编程性，存储过程工具栏。右键 新建存储过程。图2.1 默认的存储过程语法规则2. ManagerUser表插入操作存储过程的创建：CREATE PROCEDURE spManagerUserInsertpUserID INTOUTPUT,pUserNameVARCHAR(20),pUserPasswordVARCHAR(10),pUserSort VARCHAR(10)= nullASBE

7、GININSERT INTO ManagerUser(UserName,UserPassword,UserSort)VALUES (pUserName,pUserPassword,pUserSort)SET pUserId = IDENT_CURRENT(ManagerUser);END存储过程的执行：execute spManagerUserInsert 1,张小红,123456,系统管理员3. ManagerUser表查询操作存储过程的创建CREATE PROCEDURE spManagerUserSelectByIDpUserId INTASBEGINSELECT UserID,User

8、Name,UserPassword,UserSortFROM ManagerUserWITH (NOLOCK)WHERE UserID = pUserIdEND 存储过程的执行：execute spManagerUserSelectByID 34. ManagerUser表更新操作存储过程的创建：CREATE PROCEDURE spManagerUserUpdateByIDpUserId INT,pUserNameVARCHAR(20),pUserPasswordVARCHAR(10),pUserSort VARCHAR(10)= nullASBEGINUPDATE ManagerUserS

9、ET UserName= pUserName,UserPassword= pUserPassword,UserSort= pUserSortWHEREUserID= pUserIdEND存储过程的执行：execute spManagerUserUpdateByID 1,张三,434343434,系统操作员5. ManagerUser表删除操作存储过程的创建：CREATE PROCEDURE spManagerUserDeleteByIDpUserId INTASBEGINDELETE ManagerUserWHEREUserID= pUserIdEND存储过程的执行：exec spManage

10、rUserDeleteByID 46.控制流语句 if else/declare/print基本用法：declare result varchar(20)select result =UserSortfrom ManagerUserwhere UserID= 2if result = 系统操作员print 系统操作员select result as UserSort与存储过程结合使用：declare UserID int,UserName varchar(20),UserPassword varchar(10),UserSort varchar(10)set UserName=小红set Us

11、erPassword = 123456set UserSort=系统管理员execute spManagerUserInsert UserID output,UserName,UserPassword,UserSortselect UserID as 生成的用户ID7.简单介绍存储过程与数据访问层的关系。二 上机结果：设计好BookManager的五个表和相应的存储过程，每个表有四个存储过程。【实验报告要求】报告请用专门的实验报告纸书写，字迹清晰，格式规范。报告中应写清姓名、学号、实验日期、实验题目、实验目的、实验内容及实验过程。依据具体的试验内容，写出详细的试验步骤。26实验三图书管理系统触

12、发器的设计【实验目的】掌握触发器的创建与执行，完成图书管理系统触发器的创建。【实验内容及步骤】一、触发器创建的初始界面图1.1触发器创建默认语法二、触发器进行完整性控制实例create trigger Book_Inserton Bookfor insert asdeclare BookAmount int,BookRemain intselect BookAmount=BookAmount,BookRemain=BookRemainfrom insertedif(BookAmountBookRemain or BookAmount =0 or BookRemain =0)begin roll

13、back transactionraiserror(输入的书的数量和剩余数量有误！,16,1,BookAmount,BookRemain)endinsert into Book values (书名,作者,1,2009-9-11,66,专业课,20,33)三、创建ManagerUser_History表1、使用代码创建方式USE BookManagerGO/* 对象: Table dbo.ManagerUser_History 脚本日期: 09/10/2009 11:22:08 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PA

14、DDING ONGOCREATE TABLE dbo.ManagerUser_History(ID int IDENTITY(1,1) NOT NULL,UserID int NOT NULL,UserName varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,UserPassword varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,UserSort varchar(10) COLLATE Chinese_PRC_CI_AS NULL,TimeStamp datetime NULL,TypeOfChang

15、e varchar(10) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT PK_ManagerUser_History PRIMARY KEY CLUSTERED (ID ASC)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFF2 可以使用可视化方式，创建的表如下图1.2所示：图1.2 ManagerUser_History表四、ManagerUser触发器的创建：CREATE TRIGGER ManagerUser_Triggers ON ManagerUs

16、er FOR INSERT,DELETE,UPDATEASIF EXISTS(SELECT * FROM INSERTED)IF NOT EXISTS(SELECT * FROM DELETED)BEGINDECLARE UserIDAS intDECLARE UserName AS varchar(20)DECLARE UserPassword AS varchar(10)DECLARE UserSort AS varchar(10)SELECT UserID = UserID,UserName = UserName,UserPassword = UserPassword,UserSort

17、= UserSortFROM INSERTED INSERT INTO ManagerUser_History(UserID,UserName,UserPassword,UserSort,TimeStamp,TypeOfChange)VALUES(UserID,UserName,UserPassword,UserSort,GETDATE(),insert)ENDELSE BEGININSERT INTO ManagerUser_History(UserID,UserName,UserPassword,UserSort,TimeStamp,TypeOfChange)SELECT UserID,U

18、serName,UserPassword,UserSort,GETDATE(),updateFROM inserted ENDELSEBEGININSERT INTO ManagerUser_History(UserID,UserName,UserPassword,UserSort,TimeStamp,TypeOfChange)SELECT UserID,UserName,UserPassword,UserSort,GETDATE(),delete FROM deletedEND五、ManagerUser触发器的执行：exec spManagerUserInsert 1,触发器插入测试,密码,

19、系统管理员exec spManagerUserUpdateByID 1,触发器更新测试,密码,系统管理员exec spManagerUserDeleteByID 6select * from ManagerUserselect * from ManagerUser_History-drop trigger ManagerUser_Triggers(触发器的删除)图 1.3 触发器执行之后历史表的情况六 上机结果：创建图书管理系统五个表的触发器，共有五个触发器。【实验报告要求】报告请用专门的实验报告纸书写，字迹清晰，格式规范。报告中应写清姓名、学号、实验日期、实验题目、实验目的、实验内容及实验过

20、程。依据具体的试验内容，写出详细的试验步骤。实验四事务控制与并发处理【实验目的】掌握事务的特性与操作，掌握隔离级别的设置与使用。【实验步骤】一、事务的执行流程SET TRANSACTIONSTART TRANSACTIONSQL语句SQL语句执行失败COMMIT更新后的数据库事务执行前的数据库ROLLBACKSQL语句执行成功图1.1 SQL事务控制流程二 事务的特性：ACID特性：atomic,consestent,isolation,durableSQL中与事务有关的语句：Set Transaction:Start transaction：Set constraints:Savepoint

21、:Release savepoint:Rollback :Commit:三 事务的隔离级别：并发操作的问题：脏读：读取了别人正在更改的数据不可重复读：更改了别人正在读取的数据幻读：读取了别人正在插入，更新或者删除的数据事务的隔离级别：Read uncommitted: 未提交读取。Read committed：提交读取。Repeatable read：可重复读取。Serializable：可串行化。四、事务的使用实例1 事务操作代码-事务的开始，保存点，回滚，提交begin transactioninsert into ManagerUser values (用户1,12345,系统管理员)i

22、nsert into ManagerUser values (用户2,12345,系统管理员)save transaction save1delete from ManagerUser where UserName = 用户2insert into ManagerUser values (用户3,12345,系统管理员)insert into ManagerUser values (用户4,12345,系统管理员)save transaction save2delete from ManagerUser where UserName = 用户1delete from ManagerUser w

23、here UserName = 用户3rollback transaction save2update ManagerUser set UserSort =系统操作员delete from ManagerUser where UserName = 用户4commit transaction2 执行结果图1.2 事务操作执行结果五、并发操作与隔离级别的使用1 并发操作用户1的代码：-事务隔离级别的使用create table Table_test(A varchar(12),B varchar(12),C varchar(12)insert into Table_test values (a1,

24、b1,c1)insert into Table_test values (a2,b2,c2)insert into Table_test values (a3,b3,c3)select * from Table_test-用户1和用户2先后访问（0begin waitfor delay 00:00:10print 不重复读，即两次select语句结果不同select * from Table_testend-用户1和用户2先后访问Table_test表，事务隔离级别为read committed 后果：不可重复读-用户2的代码：set transaction isolation level r

25、ead committedupdate Table_test set C=cprint 更新后表中的数据select * from Table_test-用户1和用户2先后访问Table_test表，事务隔离级别为repeatable read 后果：幻读-用户2的代码：set transaction isolation level repeatable readinsert into Table_test values (a4,b4,c4)print 更新后表中的数据select * from Table_test六 上机结果：在图书管理系统的存储过程里加入事务操作，涉及五个表的所有存储过程。【实验报告要求】报告请用专门的实验报告纸书写，字迹清晰，格式规范。报告中应写清姓名、学号、实验日期、实验题目、实验目的、实验内容及实验过程。依据具体的试验内容，写出详细的试验步骤