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

Click to edit Master title,Click to edit Master text,Second level,Third level,Fourth level,Fifth level,*,mysql,图书馆管理系统的数据库,数据库,library,操作员表,operator,书库,books,学生信息表,students_information,操作过程表,process,操作员表,operator,field,type,null,key,default,extra,id,Char(9),no,pri,Null,name,Varchar(10),no,null,操作员表,operator,create table operator,(,id char(9) not null primary key,name varchar(10) not null,);,insert into operator values (100001230,aa),(100001231,bb),(100001232,cc),(100001233,dd),(100001234,ee),(100001235,ff);,书库,books,field,type,null,key,default,extra,title,varchar(20),no,null,date_of_publication,date,yes,null,author,varchar(15),no,null,ISBN,char(13),no,pri,null,number_of_copies,int,no,null,position,varchar(30),yes,null,书库,books,create 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,奥斯特洛夫斯基,9787530125403,25,二楼,成功,/,激励,),(,水浒传,1998-11-15,施耐庵,9787530112454,10,一楼,文学类,),(,小时代,2009-11-15,郭敬明,9782345612454,1,一楼,文学类,);,学生信息表,students_information,field,type,null,key,default,extra,id,varchar(9),no,pri,null,name,varchar(10),no,null,sex,char(2),yes,null,phone_number,char(11),yes,null,department,varchar(11),yes,null,学生信息表,students_information,create 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,15208211000,财经,),(100000002,bb,GG,15208211001,财经,),(100000003,cc,MM,15208211002,财经,),(100000004,dd,MM,15208211003,财经,),(100010001,ab,GG,15208211100,计算机科学与运用,),(100010002,bc,MM,15208211200,计算机科学与运用,),(100010003,cd,GG,15208211300,计算机科学与运用,),(100010004,de,MM,15208211400,计算机科学与运用,),(100020001,ef,GG,15208211120,土木工程,);,操作过程表,process,field,type,null,key,default,extra,title,varchar(20),yes,null,ISBN,char(13),yes,null,bgname,varchar(10),yes,null,bg_id,char(9),yes,null,operator_name,varchar(10),yes,null,operator_id,char(9),yes,null,btime,varchar(20),yes,null,gtime,varchar(20),yes,null,is_give,bool,yes,null,操作过程表,process,create table process,(,title varchar(20),ISBN char(13),bgname,varchar(10),bg_id,char(9),operator_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,varchar(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 from 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 process 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(9787530125403,100000001,100001231);,select * from process;,select * from books;,call zj_borrow(9787530125403,100000001,100001231);,call zj_borrow(978753012540,4,100000001,100001231);,call zj_borrow(9787530125403,1,9,0000001,100001231);,call zj_borrow(9787530125403,100000001,1,9,0001231);,call zj_borrow(9782345612454,100010002,100001234);,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,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,=,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,zj_give,(9787530125403,100000001,100001231);,select * from process;,select * from books;,call,zj_give,(9787530125403,100000001,100001231);,call,zj_give,(978753012540,4,100000001,100001231);,call,zj_give,(9787530125403,1,9,0000001,100001231);,call,zj_give,(9787530125403,100000001,1,9,0001231);,call,zj_give,(9787500683858,100000003,100001235);,Thank you,