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

1,mysql图书馆管理系统的数据库,2,数据库library,操作员表operator书库books学生信息表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,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,奥斯特洛夫斯基,9787530125403,25,二楼成功/激励),(水浒传,1998-11-15,施耐庵,9787530112454,10,一楼文学类),(小时代,2009-11-15,郭敬明,9782345612454,1,一楼文学类);,7,学生信息表students_information,8,学生信息表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土木工程);,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号,借/还书人,000000000,操作员姓名,000000000,借书时间,还书时间,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=(selecttitlefrombookswhereISBN=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(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;,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,190001231);callzj_borrow(9782345612454,100010002,100001234);callzj_borrow(9782345612454,100020001,100001232);,14,创建还书过程,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;,15,检测还书过程,callzj_give(9787530125403,100000001,100001231);select*fromprocess;select*frombooks;callzj_give(9787530125403,100000001,100001231);callzj_give(9787