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

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('沒(méi)有任何借口','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é)類');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財(cái)經(jīng)財(cái)經(jīng)財(cái)經(jīng)財(cái)經(jīng)計(jì)算機(jī)科學(xué)與運(yùn)用計(jì)算機(jī)科學(xué)與運(yùn)用計(jì)算機(jī)科學(xué)與運(yùn)用計(jì)算機(jī)科學(xué)與運(yùn)用土木工程');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('書(shū)名','isbn號(hào)','借/還書(shū)人','000000000','操作員姓名','000000000','借書(shū)時(shí)間','還書(shū)時(shí)間',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