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

1、mysql图书馆图书馆管理系管理系统统的的数数据据库库数数据据库库 library操作员表 operator书库 books学生信息表 students_information操作过程表 process操作操作员员表表 operatorfieldtypenullkeydefaultextraidChar(9)nopriNullnameVarchar(10) nonull操作操作员员表表 operatorcreate table operator(id char(9) not null primary key,name varchar(10) not null);insert into oper

2、ator values (100001230,aa),(100001231,bb),(100001232,cc),(100001233,dd),(100001234,ee),(100001235,ff);书库书库 booksfieldtypenullkeydefault extratitlevarchar(20) nonulldate_of_publicationdateyesnullauthorvarchar(15) nonullISBNchar(13)noprinullnumber_of_copiesintnonullpositionvarchar(30) yesnull书库书库 book

3、screate table books(title varchar(20) not null,date_of_publication date,author varchar(15) not null,ISBN char(13) not null primary key,number_of_copies int not null,position varchar(30);insert into books values (没有任何借口,2008-11-01,杰伊.瑞芬博瑞,9787500683858,20,二楼 成功/激励),(钢铁是怎样炼成的,1997-05-12,奥斯特洛夫斯基,978753

4、0125403,25,二楼 成功/激励),(水浒传,1998-11-15,施耐庵,9787530112454,10,一楼文学类),(小时代,2009-11-15,郭敬明,9782345612454,1,一楼文学类);学学生信息表生信息表 students_informationfieldtypenull keydefault extraidvarchar(9)noprinullnamevarchar(10) nonullsexchar(2)yesnullphone_numberchar(11)yesnulldepartment varchar(11) yesnull学学生信息表生信息表 stu

5、dents_informationcreate table students_information(id char(9) not null primary key,name varchar(10) not null,sex char(2),phone_number char(11),department varchar(10);insert into students_information values (100000001,aa,MM财经),(100000002,bb,GG财经),(100000003,cc,MM/p>

6、,财经),(100000004,dd,MM财经),(100010001,ab,GG计算机科学与运用),(100010002,bc,MM计算机科学与运用),(100010003,cd,GG计算机科学与运用),(100010004,de,MM计算机科学与运用),(100020001,ef,GG土木工程);操作操作过过程表程表 processfieldtypenull keydefault extratitlevarchar(20) yesnul

7、lISBNchar(13)yesnullbgnamevarchar(10) yesnullbg_idchar(9)yesnulloperator_namevarchar(10) yesnulloperator_idchar(9)yesnullbtimevarchar(20) yesnullgtimevarchar(20) yesnullis_giveboolyesnull操作操作过过程表程表 processcreate table process(title varchar(20),ISBN char(13),bgname varchar(10),bg_id char(9),operator_

8、name varchar(10),operator_id char(9),btime varchar(20),gtime varchar(20),is_give bool);insert into process values (书名,isbn号,借/还书人,000000000,操作员姓名,000000000,借书时间,还书时间,1);借书存储过程借书存储过程delimiter |create procedure zj_borrow(in book_id char(13),in b_id char(9),in o_id char(9)begin declare booktitle varcha

9、r(20); declare borrowname varchar(10); declare op_name varchar(10); declare bstime varchar(20); declare num int; set num=1; set bstime=now(); set booktitle=(select title from books where ISBN=book_id); set borrowname=(select name from students_information where id=b_id); set op_name=(select name fro

10、m operator where id=o_id);借书存储过程借书存储过程 if (select number_of_copies from books where ISBN=book_id)=num and book_id in (select ISBN from books) and b_id in (select id from students_information) and o_id in (select id from operator) and (select is_give from process where btime=(select max(btime) from p

11、rocess where bg_id=b_id)=1 or b_id not in (select bg_id from process) then update books set number_of_copies=number_of_copies-1 where ISBN=book_id; insert into process values (booktitle,book_id,borrowname,b_id,op_name,o_id,bstime,null,0); end if;end |delimiter ;检测借书过程检测借书过程call zj_borrow(97875301254

12、03,100000001,100001231);call zj_borrow(9787530125403,100000001,100001231);select select * * from process; from process;select select * * from books; from books;call zj_borrow(9787530125403,100000001,100001231);call zj_borrow(9787530125403,100000001,100001231);call zj_borrow(978753012540call zj_borro

13、w(9787530125404 4,100000001,100001231);,100000001,100001231);call zj_borrow(9787530125403,1call zj_borrow(9787530125403,19 90000001,100001231);0000001,100001231);call zj_borrow(9787530125403,100000001,1call zj_borrow(9787530125403,100000001,19 90001231);0001231);call zj_borrow(9782345612454,10001000

14、2,100001234);call zj_borrow(9782345612454,100010002,100001234);call zj_borrow(9782345612454,100020001,100001232);call zj_borrow(9782345612454,100020001,100001232);创建还书过程创建还书过程delimiter |create procedure zj_give(in b_id char(13),in g_id char(9),in o_id char(9)begin declare stime varchar(20); declare

15、gstime varchar(20); set gstime=now(); set stime=(select max(btime) from process where bg_id=g_id); if (select is_give from process where ISBN=b_id and bg_id=g_id and operator_id=o_id and btime=stime)=0 then update process set gtime=gstime where ISBN=b_id and bg_id=g_id and operator_id=o_id and btime

16、=stime; update process set is_give=1 where ISBN=b_id and bg_id=g_id and operator_id=o_id and btime=stime; update books set number_of_copies=number_of_copies+1 where ISBN=b_id; end if;end |delimiter ;检测还书过程检测还书过程call call zj_givezj_give (9787530125403,100000001,100001231); (9787530125403,100000001,10

17、0001231);select select * * from process; from process;select select * * from books; from books;call call zj_givezj_give (9787530125403,100000001,100001231); (9787530125403,100000001,100001231);call call zj_givezj_give (978753012540 (9787530125404 4,100000001,100001231);,100000001,100001231);call call zj_givezj_give (9787530125