图书馆之数据库设计与实现-高级查询-存储过程 PPT课件

1、1,用数据库创建图书管理系统,王伟洪,2,-libraryDB数据库的创建 create database libraryDB on ( name=libraryDB_mdf, fileName=F:图书馆libraryDB_mdf.mdf, size=3mb, maxsize=100mb, filegrowth=1mb ) log on ( name=libraryDB_ldf, fileName=F:图书馆libraryDB_ldf.ldf, size=3mb, maxsize=100mb, filegrowth=1mb ),3,-ReaderInfo表的创建 create table R

2、eaderInfo ( ReaderId int identity(1,1) ReaderNo varchar(20) not null, ReaderName varchar(10) not null, Sex int default(0), BirthDate datetime, Phone varchar(50), NumberID varchar(30), Address varchar(200), RegisterDate datetime, ReaderCount int, Remark varchar(500), IsLoss int ),4,-BookType表的创建 crea

3、te table BookInfo ( TypeId int identity(1,1) primary key, TypeName varchar(50), Remark varchar(100) ),5,-BookInfo表的创建 create table BookInfo ( BookId int identity primary key, BookName varchar(50) not null, TypeId int not null, Author varchar(20), Press varchar(50), PressDate datetime, Price money, P

4、age int, NowNum int, BookNum int, AddDate datetime, ByCount int, IsOff int, Remark varchar(500) ),6,-BorrowInfo表的创建(略),看了那么久,是不是觉得很枯燥?,下面动手试试看吧!,7,-BackInfo表的创建 create table BackInfo ( BackId int identity(1,1) primary key , BookId int constraint FK_BookInfo_BackInfo_BookId foreign key references Boo

5、kInfo(BookId), ReaderId int constraint FK_ReaderInfo_BackInfo_ReaderId foreign key references ReaderInfo(ReaderId), BackMoney money, EndTime datetime, IsBack int ),8,-数据库不区分大小写 以下书写方式为个人习惯 -数据库的数据要自己编辑 use libraryDB go if exists (select * from sys.objects where name=proc_borrow) drop proc proc_borro

6、w -判断存储过程是否纯在 go create procedure proc_borrow bookId int, -图书编号 readerId int, -阅读者编号 borrowTime datetime, -借书时间默认为当前时间 shouldTime datetime, -应还时间 mortgageMoney money -借书押金 as -判断图书是否存在 if not exists (select 1 from BookInfo where BookId =bookId) begin print 借阅失败,不存在该图书. return end,9,-判断图书的当前数量,是否大于1本

7、 declare nowNum int select nowNum =nowNum from BookInfo where BookId =bookId if (nowNum 1) begin print 借阅失败,图书已全部借出 return end -判断阅读者是否存在 if not exists (select * from ReaderInfo where ReaderId =readerId ) begin print 借阅失败,不存在该阅读者 return end -判断押金是否大于1元 if (mortgageMoney1) begin print 借阅失败,押金小于1元 ret

8、urn end,10,-判断借书时间是否小于应还书的时间 if (DATEDIFF(DD,borrowTime ,shouldTime )1) begin print 借阅失败,借书时间应小于还书时间 return end -修改图书存量 update BookInfo set NowNum =NowNum -1 where BookId =bookId if (IDENTITY =0) begin print 借阅失败,修改图书数量失败 return end -修改借书记录 insert into BorrowInfo values (BookId,readerId,borrowTime,s

9、houldTime,mortgageMoney,0) if (IDENTITY=0) begin print 借阅失败,插入借书记录失败 return end,11,-修改借书记录 insert into BorrowInfo values (BookId,readerId,borrowTime,shouldTime,mortgageMoney,0) if (IDENTITY=0) begin print 借阅失败,插入借书记录失败 return end -修改图书的借阅次数 update BookInfo set ByCount=ByCount+1 where BookId=bookId if (rowcount=0) begin print 借阅失败,修改图书的借阅次数失败 return end -修改读者的借阅次数 update ReaderInfo set ReaderCount=ReaderCount+1 where ReaderId=readerId if (rowcount=0) begin print 借阅失败,修改读者的借阅次数失败 return end,12,print 借阅成功 GO -执行,-表BookInfo数据(部分),-表