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

mysql图书馆管理系统的数据库 数据库library 操作员表operator书库books学生信息表students information操作过程表process 操作员表operator 操作员表operator createtableoperator idchar 9 notnullprimarykey namevarchar 10 notnull insertintooperatorvalues 100001230 aa 100001231 bb 100001232 cc 100001233 dd 100001234 ee 100001235 ff 书库books 书库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 奥斯特洛夫斯基 9787530125403 25 二楼 成功 激励 水浒传 1998 11 15 施耐庵 9787530112454 10 一楼 文学类 小时代 2009 11 15 郭敬明 9782345612454 1 一楼 文学类 学生信息表students information 学生信息表students information createtablestudents information idchar 9 notnullprimarykey namevarchar 10 notnull sexchar 2 phone numberchar 11 departmentvarchar 10 insertintostudents informationvalues 100000001 aa MM财经 100000002 bb GG财经 100000003 cc MM财经 100000004 dd MM财经 100010001 ab GG计算机科学与运用 100010002 bc MM计算机科学与运用 100010003 cd GG计算机科学与运用 100010004 de MM计算机科学与运用 100020001 ef GG土木工程 操作过程表process 操作过程表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号 借 还书人 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 97