1、1,用數(shù)據(jù)庫創(chuàng)建圖書管理系統(tǒng),王偉洪,2,-libraryDB數(shù)據(jù)庫的創(chuàng)建 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表的創(chuàng)建 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表的創(chuàng)建 crea

3、te table BookInfo ( TypeId int identity(1,1) primary key, TypeName varchar(50), Remark varchar(100) ),5,-BookInfo表的創(chuàng)建 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表的創(chuàng)建(略),看了那么久,是不是覺得很枯燥?,下面動手試試看吧!,7,-BackInfo表的創(chuàng)建 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,-數(shù)據(jù)庫不區(qū)分大小寫 以下書寫方式為個人習(xí)慣 -數(shù)據(jù)庫的數(shù)據(jù)要自己編輯 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, -借書時間默認(rèn)為當(dāng)前時間 shouldTime datetime, -應(yīng)還時間 mortgageMoney money -借書押金 as -判斷圖書是否存在 if not exists (select 1 from BookInfo where BookId =bookId) begin print 借閱失敗,不存在該圖書. return end,9,-判斷圖書的當(dāng)前數(shù)量,是否大于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,-判斷借書時間是否小于應(yīng)還書的時間 if (DATEDIFF(DD,borrowTime ,shouldTime )1) begin print 借閱失敗,借書時間應(yīng)小于還書時間 return end -修改圖書存量 update BookInfo set NowNum =NowNum -1 where BookId =bookId if (IDENTITY =0) begin print 借閱失敗,修改圖書數(shù)量失敗 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 -修改圖書的借閱次數(shù) update BookInfo set ByCount=ByCount+1 where BookId=bookId if (rowcount=0) begin print 借閱失敗,修改圖書的借閱次數(shù)失敗 return end -修改讀者的借閱次數(shù) update ReaderInfo set ReaderCount=ReaderCount+1 where ReaderId=readerId if (rowcount=0) begin print 借閱失敗,修改讀者的借閱次數(shù)失敗 return end,12,print 借閱成功 GO -執(zhí)行,-表BookInfo數(shù)據(jù)(部分),-表