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

field

typenull

key

default

extraid

Char(9)

no

pri

Nullname

Varchar(10

no)nullcreatetableoperator(idchar(9)notnullprimarykey,namevarchar(10)notnull);insertintooperatorvalues(100001230,'aa'),(100001231,'bb'),(100001232,'cc'),(100001233,'dd'),(100001234,'ee'),(100001235,'ff');fieldtitletypenull

key

default

extravarchar(20

no)nullnullnulldate_of_publi

datecationyesauthorvarchar(15

no)ISBNchar(13)

no

pri

nullnumber_of_c

intopiesnonullnullpositionvarchar(30

yes)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','一楼>>文学类');fieldidtypenull

key

default

extravarchar(9)

no

pri

nullnamevarchar(10

no)nullsexchar(2)

yesnullnullphone_num

char(11)

yesberdepartment

varchar(11

yes)nullcreatetablestudents_information(idchar(9)notnullprimarykey,namevarchar(10)notnull,sexchar(2),phone_numberchar(11),departmentvarchar(10));insertintostudents_informationvalues财经财经财经财经计算机科学与运用计算机科学与运用计算机科学与运用计算机科学与运用土木工程');fieldtitletypenull

key

default

extravarchar(20)

yesnullISBNchar(13)

yesnullbgnamebg_idvarchar(10)

yeschar(9)

yesnullnulloperator_name

varchar(10)

yesnullnullnulloperator_idbtimechar(9)

yesvarchar(20)

yesgtimevarchar(20)

yesnullnullis_giveboolyescreatetableprocess(titlevarchar(20),ISBNchar(13),bgnamevarchar(10),bg_id

char(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');c