mysql圖書館管理系統(tǒng)的數(shù)據(jù)庫(kù).ppt

mysql圖書館管理系統(tǒng)的數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù)library 操作員表operator書庫(kù)books學(xué)生信息表students information操作過(guò)程表process 操作員表operator 操作員表operator createtableoperator idchar 9 notnullprimarykey namevarchar 10 notnull insertintooperatorvalues 100001230 aa 100001231 bb 100001232 cc 100001233 dd 100001234 ee 100001235 ff 書庫(kù)books 書庫(kù)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 二樓 成功 激勵(lì) 鋼鐵是怎樣煉成的 1997 05 12 奧斯特洛夫斯基 9787530125403 25 二樓 成功 激勵(lì) 水滸傳 1998 11 15 施耐庵 9787530112454 10 一樓 文學(xué)類 小時(shí)代 2009 11 15 郭敬明 9782345612454 1 一樓 文學(xué)類 學(xué)生信息表students information 學(xué)生信息表students information createtablestudents information idchar 9 notnullprimarykey namevarchar 10 notnull sexchar 2 phone numberchar 11 departmentvarchar 10 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 MM計(jì)算機(jī)科學(xué)與運(yùn)用 100020001 ef GG土木工程 9 可編輯 操作過(guò)程表process 操作過(guò)程表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號(hào) 借 還書人 000000000 操作員姓名 000000000 借書時(shí)間 還書時(shí)間 1 借書存儲(chǔ)過(guò)程 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 借書存儲(chǔ)過(guò)程 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 檢測(cè)借書過(guò)程 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 創(chuàng)建還書過(guò)程 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 檢測(cè)還書過(guò)程 callzj give 9787530125403 100000001 100001231 select fromprocess select frombooks callzj give 9787530125403 100000001 100001231 callzj give 978753012