《mysql图书馆管理系统的数据库.ppt》由会员分享，可在线阅读，更多相关《mysql图书馆管理系统的数据库.ppt（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),(100001234,ee),(
2、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(没有任何借口,2008-11-01,杰伊.瑞芬博瑞,9787500683858,20,二楼 成功/激励),(钢铁是怎样炼
3、成的,1997-05-12,奥斯特洛夫斯基,9787530125403,25,二楼 成功/激励),(水浒传,1998-11-15,施耐庵,9787530112454,10,一楼文学类),(小时代,2009-11-15,郭敬明,9782345612454,1,一楼文学类);,学生信息表 students_information,学生信息表 students_information,create table students_information(id char(9)not null primary key,name varchar(10)not null,sex char(2),phone_n
4、umber char(11),department varchar(10);insert into students_information values 财经财经财经财经计算机科学与运用计算机科学与运用计算机科学与运用计算机科学与运用土木工程);,操作过程表 process,操作过程表 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(2
5、0),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 varch
6、ar(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 b
7、ooks 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 b
8、ooks 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,1
9、00000001,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_borrow(9782345612454,100020001,100001232);,创建还书过程,delimiter|create proce
10、dure 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 th
11、en 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;,检测还书过程,ca
12、ll 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,
