mysql图书馆管理系统的数据库

mysql图书馆管理系统的数据库数据库library操作员表operator书库books学生信息表students_information操作过程表process操作员表operatorfieldtypenullkeydefaultextraidChar(9)nopriNullnameVarchar(10)nonull操作员表operatorcreatetableoperator(idchar(9)notnullprimarykey,namevarchar(10)notnull);insertintooperatorvalues(100001230,'aa'),(100001231,'bb'),(100001232,'cc'),(100001233,'dd'),(100001234,'ee'),(100001235,'ff');书库booksfieldtypenullkeydefaultextratitlevarchar(20)nonulldate_of_publicationdateyesnullauthorvarchar(15)nonullISBNchar(13)noprinullnumber_of_copiesintnonullpositionvarchar(30)yesnull书库bookscreatetablebooks(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','奥斯特洛夫斯基','9787530125403','25','二楼成功/激励'),('水浒传','1998-11-15','施耐庵','9787530112454','10','一楼文学类'),('小时代','2009-11-15','郭敬明','9782345612454','1','一楼文学类');学生信息表students_informationfieldtypenullkeydefaultextraidvarchar(9)noprinullnamevarchar(10)nonullsexchar(2)yesnullphone_numberchar(11)yesnulldepartmentvarchar(11)yesnull学生信息表students_informationcreatetablestudents_information(idchar(9)notnullprimarykey,namevarchar(10)notnull,sexchar(2),phone_numberchar(11),departmentvarchar(10));insertintostudents_informationvalues('100000001','aa','MM','15208211000','财经'),('100000002','bb','GG','15208211001','财经'),('100000003','cc','MM','15208211002','财经'),('100000004','dd','MM','15208211003','财经'),('100010001','ab','GG','15208211100','计算机科学与运用'),('100010002','bc','MM','15208211200','计算机科学与运用'),('100010003','cd','GG','15208211300','计算机科学与运用'),('100010004','de','MM','15208211400','计算机科学与运用'),('100020001','ef','GG','15208211120','土木工程');操作过程表processfieldtypenullkeydefaultextratitlevarchar(20)yesnullISBNchar(13)yesnullbgnamevarchar(10)yesnullbg_idchar(9)yesnulloperator_namevarchar(10)yesnulloperator_idchar(9)yesnullbtimevarchar(20)yesnullgtimevarchar(20)yesnullis_giveboolyesnull操作过程表processcreatetableprocess(titlevarchar(20),ISBNchar(13),bgnamevarchar(10),bg_idchar(9),operator_namevarchar(10),operator_idchar(9),btimevarchar(20),gtimevarchar(20),is_givebool);insertintoprocessvalues('书名','isbn号','借/还书人','000000000','操作员姓名','000000000','借书时间','还书时间',1);借书存储过程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=(selecttitlefrombookswhereISBN=book_id);setborrowname=(selectnamefromstudents_informationwhereid=b_id);setop_name=(selectnamefromoperatorwhereid=o_id);借书存储过程if(selectnumber_of_copiesfrombookswhereISBN=book_id)=numandbook_idin(selectISBNfrombooks)andb_idin(selectidfromstudents_information)ando_idin(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);endif;end|delimiter;检测借书过程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','190001231');callzj_borrow('9782345612454','100010002','100001234');callzj_borrow('9782345612454','100020001','100001232');创建还书过程delimiter|createprocedurezj_give(inb_idchar(13),ing_idchar(9),ino_idchar(9))begindeclarestimevarchar(20);declaregstimevarchar(20);setgstime=now();setstime=(selectmax(btime)fromprocesswherebg_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;检测还书过程callzj_give('9787530125403','100000001','100001231');select*fromprocess;select*frombooks;callzj_give('9787530125403','100000001','100001231');callzj_give('9787530125404','100000001','100001231');callzj_give('9787530125403','190000001','100001231');callzj_give('9787530125403','100000001','190001231');callzj_give('9787500683858','100000003','100001235');Thankyou