mysql圖書(shū)館管理系管理系統(tǒng)的的數(shù)數(shù)據(jù)據(jù)庫(kù)數(shù)數(shù)據(jù)據(jù)庫(kù) library操作員表 operator書(shū)庫(kù) books學(xué)生信息表 students_information操作過(guò)程表 process操作操作員表表 operatorfieldtypenullkeydefaultextraidChar(9)nopriNullnameVarchar(10)nonull操作操作員表表 operatorcreate table operator(id char(9) not null primary key,name varchar(10) not null);insert into operator values (100001230,'aa'),(100001231,'bb'),(100001232,'cc'),(100001233,'dd'),(100001234,'ee'),(100001235,'ff');書(shū)庫(kù) booksfieldtypenullkeydefault extratitlevarchar(20)nonulldate_of_publicationdateyesnullauthorvarchar(15)nonullISBNchar(13)noprinullnumber_of_copiesintnonullpositionvarchar(30)yesnull書(shū)庫(kù) bookscreate table books(title varchar(20) not null,date_of_publication date,author varchar(15) not null,ISBN char(13) not null primary key,number_of_copies int not null,position varchar(30));insert into books values ('沒(méi)有任何借口','2008-11-01','杰伊.瑞芬博瑞','9787500683858','20','二樓>> 成功/激勵(lì)'),('鋼鐵是怎樣煉成的','1997-05-12','奧斯特洛夫斯基','9787530125403','25','二樓>> 成功/激勵(lì)'),('水滸傳','1998-11-15','施耐庵','9787530112454','10','一樓>>文學(xué)類(lèi)'),('小時(shí)代','2009-11-15','郭敬明','9782345612454','1','一樓>>文學(xué)類(lèi)');學(xué)學(xué)生信息表生信息表 students_informationfieldtypenull keydefault extraidvarchar(9)noprinullnamevarchar(10)nonullsexchar(2)yesnullphone_numberchar(11)yesnulldepartment varchar(11)yesnull學(xué)學(xué)生信息表生信息表 students_informationcreate table students_information(id char(9) not null primary key,name varchar(10) not null,sex char(2),phone_number char(11),department varchar(10));insert into students_information values ('100000001','aa','MM','15208211000','財(cái)經(jīng)'),('100000002','bb','GG','15208211001','財(cái)經(jīng)'),('100000003','cc','MM','15208211002','財(cái)經(jīng)'),('100000004','dd','MM','15208211003','財(cái)經(jīng)'),('100010001','ab','GG','15208211100','計(jì)算機(jī)科學(xué)與運(yùn)用'),('100010002','bc','MM','15208211200','計(jì)算機(jī)科學(xué)與運(yùn)用'),('100010003','cd','GG','15208211300','計(jì)算機(jī)科學(xué)與運(yùn)用'),('100010004','de','MM','15208211400','計(jì)算機(jī)科學(xué)與運(yùn)用'),('100020001','ef','GG','15208211120','土木工程');操作操作過(guò)過(guò)程表程表 processfieldtypenull keydefault extratitlevarchar(20) yesnullISBNchar(13)yesnullbgnamevarchar(10) yesnullbg_idchar(9)yesnulloperator_namevarchar(10) yesnulloperator_idchar(9)yesnullbtimevarchar(20) yesnullgtimevarchar(20) yesnullis_giveboolyesnull操作操作過(guò)程表程表 processcreate table process(title varchar(20),ISBN char(13),bgname varchar(10),bg_id char(9),operator_name varchar(10),operator_id char(9),btime varchar(20),gtime varchar(20),is_give bool);insert into process values ('書(shū)名','isbn號(hào)','借/還書(shū)人','000000000','操作員姓名','000000000','借書(shū)時(shí)間','還書(shū)時(shí)間',1);借書(shū)存儲(chǔ)過(guò)程借書(shū)存儲(chǔ)過(guò)程delimiter |create procedure zj_borrow(in book_id char(13),in b_id char(9),in o_id char(9))begin declare booktitle varchar(20); declare borrowname varchar(10); declare op_name varchar(10); declare bstime varchar(20); declare num int; set num=1; set bstime=now(); set booktitle=(select title from books where ISBN=book_id); set borrowname=(select name from students_information where id=b_id); set op_name=(select name from operator where id=o_id);借書(shū)存儲(chǔ)過(guò)程借書(shū)存儲(chǔ)過(guò)程 if (select number_of_copies from books where ISBN=book_id)>=num and book_id in (select ISBN from books) and b_id in (select id from students_information) and o_id in (select id from operator) and ((select is_give from process where btime=(select max(btime) from process where bg_id=b_id))=1 or b_id not in (select bg_id from process)) then update books set number_of_copies=number_of_copies-1 where ISBN=book_id; insert into process values (booktitle,book_id,borrowname,b_id,op_name,o_id,bstime,null,0); end if;end |delimiter ;檢測(cè)借書(shū)過(guò)程檢測(cè)借書(shū)過(guò)程call zj_borrow('9787530125403','100000001','100001231');call zj_borrow('9787530125403','100000001','100001231');select * from process;select * from process;select * from books;select * from books;call zj_borrow('9787530125403','100000001','100001231');call zj_borrow('9787530125403','100000001','100001231');call zj_borrow('978753012540call zj_borrow('9787530125404 4','100000001','100001231');','100000001','100001231');call zj_borrow('9787530125403','1call zj_borrow('9787530125403','19 90000001','100001231');0000001','100001231');call zj_borrow('9787530125403','100000001','1call zj_borrow('9787530125403','100000001','19 90001231');0001231');call zj_borrow('9782345612454','100010002','100001234');call zj_borrow('9782345612454','100010002','100001234');call zj_borrow('9782345612454','100020001','100001232');call zj_borrow('9782345612454','100020001','100001232');創(chuàng)建還書(shū)過(guò)程創(chuàng)建還書(shū)過(guò)程delimiter |create procedure zj_give(in b_id char(13),in g_id char(9),in o_id char(9))begin declare stime varchar(20); declare gstime varchar(20); set gstime=now(); set stime=(select max(btime) from process where bg_id=g_id); if (select is_give from process where ISBN=b_id and bg_id=g_id and operator_id=o_id and btime=stime)=0 then update process set gtime=gstime where ISBN=b_id and bg_id=g_id and operator_id=o_id and btime=stime; update process set is_give=1 where ISBN=b_id and bg_id=g_id and operator_id=o_id and btime=stime; update books set number_of_copies=number_of_copies+1 where ISBN=b_id; end if;end |delimiter ;檢測(cè)還書(shū)過(guò)程檢測(cè)還書(shū)過(guò)程call call zj_givezj_give ('9787530125403','100000001','100001231'); ('9787530125403','100000001','100001231');select * from process;select * from process;select * from books;select * from books;call call zj_givezj_give ('9787530125403','100000001','100001231'); ('9787530125403','100000001','100001231');call call zj_givezj_give ('978753012540 ('9787530125404 4','100000001','100001231');','100000001','100001231');call call zj_givezj_give ('9787530125403','1 ('9787530125403','19 90000001','100001231');0000001','100001231');call call zj_givezj_give ('9787530125403','100000001','1 ('9787530125403','100000001','19 90001231');0001231');call call zj_givezj_give ('9787500683858','100000003','100001235'); ('9787500683858','100000003','100001235');Thank you 。