《mysql图书馆管理系统的数据库》由会员分享，可在线阅读，更多相关《mysql图书馆管理系统的数据库(16页珍藏版)》请在人人文库网上搜索。
1、mysql 图书馆管理系统的数据库,数据库 library,操作员表 operator 书库 books 学生信息表 students_information 操作过程表 process,操作员表 operator,操作员表 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),(100。
2、001234,ee),(100001235,ff);,书库 books,书库 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 (没有任何借口,-11-01,杰伊.瑞芬博瑞,97875006。
3、83858,20,二楼 成功/激励),(钢铁是怎样炼成的,1997-05-12,奥斯特洛夫斯基,9787530125403,25,二楼 成功/激励),(水浒传,1998-11-15,施耐庵,9787530112454,10,一楼文学类),(小时代,-11-15,郭敬明,9782345612454,1,一楼文学类);,学生信息表 students_information,学生信息表 students_information,create table students_information ( id char(9) not null primary key, name varchar(1。
4、0) 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,。
5、MM,15208211200,计算机科学与运用),(100010003,cd,GG,15208211300,计算机科学与运用),(100010004,de,MM,15208211400,计算机科学与运用),(100020001,ef,GG,15208211120,土木工程);,操作过程表 process,操作过程表 process,create table process ( title varchar(20), ISBN char(13), bgname varchar(10), bg_id char(9), operator_name varchar(10), operator_id ch。
6、ar(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 va。
7、rchar(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);,借。
8、书存储过程,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_i。
9、d 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 * f。
10、rom process; select * from books; call zj_borrow(9787530125403,100000001,100001231); call zj_borrow(9787530125404,100000001,100001231); call zj_borrow(9787530125403,190000001,100001231); call zj_borrow(9787530125403,100000001,190001231); call zj_borrow(9782345612454,100010002,100001234); call zj_bor。
11、row(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 pro。
12、cess 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 num。
13、ber_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 (9787530125404,100000001,100001231); call zj_give (9787530125403,190000001,100001231); call zj_give (9787530125403,100000001,190001231); call zj_give (9787500683858,100000003,100001235);,Thank you。
