1、1,mysql圖書館管理系統(tǒng)的數(shù)據(jù)庫,2,數(shù)據(jù)庫library,操作員表operator書庫books學(xué)生信息表students_information操作過程表process,3,操作員表operator,4,操作員表operator,createtableoperator(idchar(9)notnullprimarykey,namevarchar(10)notnull);insertintooperatorvalues(100001230,aa),(100001231,bb),(100001232,cc),(100001233,dd),(100001234,ee),(100001235,

2、ff);,5,書庫books,6,書庫books,createtablebooks(titlevarchar(20)notnull,date_of_publicationdate,authorvarchar(15)notnull,ISBNchar(13)notnullprimarykey,number_of_copiesintnotnull,positionvarchar(30);insertintobooksvalues(沒有任何借口,2008-11-01,杰伊.瑞芬博瑞,9787500683858,20,二樓成功/激勵),(鋼鐵是怎樣煉成的,1997-05-12,奧斯特洛夫斯基,97875

3、30125403,25,二樓成功/激勵),(水滸傳,1998-11-15,施耐庵,9787530112454,10,一樓文學(xué)類),(小時(shí)代,2009-11-15,郭敬明,9782345612454,1,一樓文學(xué)類);,7,學(xué)生信息表students_information,8,學(xué)生信息表students_information,createtablestudents_information(idchar(9)notnullprimarykey,namevarchar(10)notnull,sexchar(2),phone_numberchar(11),departmentvarchar(10)

4、;insertintostudents_informationvalues(100000001,aa,MM財(cái)經(jīng)),(100000002,bb,GG財(cái)經(jīng)),(100000003,cc,MM財(cái)經(jīng)),(100000004,dd,MM財(cái)經(jīng)),(100010001,ab,GG計(jì)算機(jī)科學(xué)與運(yùn)用),(100010002,bc,MM計(jì)算機(jī)科學(xué)與運(yùn)用),(100010003,cd,GG計(jì)算機(jī)科學(xué)與運(yùn)用),(100010004,de,

5、MM計(jì)算機(jī)科學(xué)與運(yùn)用),(100020001,ef,GG土木工程);,9,操作過程表process,10,操作過程表process,createtableprocess(titlevarchar(20),ISBNchar(13),bgnamevarchar(10),bg_idchar(9),operator_namevarchar(10),operator_idchar(9),btimevarchar(20),gtimevarchar(20),is_givebool);insertintoprocessvalues(書名,isbn號,借/還書

6、人,000000000,操作員姓名,000000000,借書時(shí)間,還書時(shí)間,1);,11,借書存儲過程,delimiter|createprocedurezj_borrow(inbook_idchar(13),inb_idchar(9),ino_idchar(9)begindeclarebooktitlevarchar(20);declareborrownamevarchar(10);declareop_namevarchar(10);declarebstimevarchar(20);declarenumint;setnum=1;setbstime=now();setbooktitle=(se

7、lecttitlefrombookswhereISBN=book_id);setborrowname=(selectnamefromstudents_informationwhereid=b_id);setop_name=(selectnamefromoperatorwhereid=o_id);,12,借書存儲過程,if(selectnumber_of_copiesfrombookswhereISBN=book_id)=numandbook_idin(selectISBNfrombooks)andb_idin(selectidfromstudents_information)ando_idin

8、(selectidfromoperator)and(selectis_givefromprocesswherebtime=(selectmax(btime)fromprocesswherebg_id=b_id)=1orb_idnotin(selectbg_idfromprocess)thenupdatebookssetnumber_of_copies=number_of_copies-1whereISBN=book_id;insertintoprocessvalues(booktitle,book_id,borrowname,b_id,op_name,o_id,bstime,null,0);e

9、ndif;end|delimiter;,13,檢測借書過程,callzj_borrow(9787530125403,100000001,100001231);select*fromprocess;select*frombooks;callzj_borrow(9787530125403,100000001,100001231);callzj_borrow(9787530125404,100000001,100001231);callzj_borrow(9787530125403,190000001,100001231);callzj_borrow(9787530125403,100000001,

10、190001231);callzj_borrow(9782345612454,100010002,100001234);callzj_borrow(9782345612454,100020001,100001232);,14,創(chuàng)建還書過程,delimiter|createprocedurezj_give(inb_idchar(13),ing_idchar(9),ino_idchar(9)begindeclarestimevarchar(20);declaregstimevarchar(20);setgstime=now();setstime=(selectmax(btime)fromproce

11、sswherebg_id=g_id);if(selectis_givefromprocesswhereISBN=b_idandbg_id=g_idandoperator_id=o_idandbtime=stime)=0thenupdateprocesssetgtime=gstimewhereISBN=b_idandbg_id=g_idandoperator_id=o_idandbtime=stime;updateprocesssetis_give=1whereISBN=b_idandbg_id=g_idandoperator_id=o_idandbtime=stime;updatebookssetnumber_of_copies=number_of_copies+1whereISBN=b_id;endif;end|delimiter;,15,檢測還書過程,callzj_give(9787530125403,100000001,100001231);select*fromprocess;select*frombooks;callzj_give(9787530125403,100000001,100001231);callzj_give(9787