在以前大多部分图书馆都是由人工直接管理,其中每天的业务和操作流程非常繁琐复杂,纸质版的登记信息耗费了大量的人力物力。因此图书馆管理系统应运而生,该系统采用智能化设计,在业务流程的实现方面更加注重智能化、规范化、流程化,极大地提高图书馆的管理效率及服务质量。
其中管理员负责图书的信息录入和类型归纳,以及读者借阅图书和归还图书时的信息登记;图书信息和图书类型为管理员或读者们查找所需要的图书时提供便捷;图书借阅负责记录读者的借阅信息并对借阅信息做一个统筹处理;图书归还负责读者归还图书后的信息的记录;读者信息方便图书的借阅与归还,凭借读者信息可以直接在网上进行借阅和归还。这样的流程大大降低了图书馆管理员的工作量,也提高了图书馆的管理效率。
图书馆管理系统分为三部分,管理员部分负责管理员信息的增删改查,图书管理部分主要包括图书信息、类型、借阅和归还,而读者管理负责读者信息的修改、增加、查询和修改。
一个管理员可以整理多本图书信息,因此管理员和图书信息具有一对多的联系,用整理来表示管理员和图书信息的关系。
一个管理员可以对多种图书类型进行归类,因此管理员和图书类型具有一对多的联系,用归类来表示管理员和图书类型的关系。
一个管理员可以管理多个图书借阅信息,因此管理员和图书借阅信息具有一对多的联系,用管理来表示管理员和图书借阅信息的关系。
一个管理员可以管理多个图书归还信息,因此管理员和图书归还信息具有一对多的联系,用管理来表示管理员和图书归还信息的关系。
一个管理员可以管理多个读者信息,因此管理员和读者信息具有一对多的联系,用管理来表示管理员和读者信息的关系。
一本图书可由多个读者借阅,一个读者也可借阅多本图书,因此读者和图书借阅具有多对多的联系,用借阅来表示读者和图书借阅的关系。
一本图书可由多个读者归还,一个读者也可归还多本图书,因此读者和图书归还具有多对多的联系,用归还来表示读者和图书归还的关系。
图书类型(图书编号、书名、数量、类型、所在书架)
图书借阅(读者编号、图书编号、读者姓名、借书时间、归还时间、是否归还)
图书归还(读者编号、图书编号、读者姓名、归还时间)
读者(读者编号、姓名、性别、职业、出生日期、证件号码、电话、电子邮箱)
(1)manager(管理员信息表)
Id |
10 |
ID编号 |
||
60 |
书名 |
|||
60 |
||||
10 |
图书编号 |
|||
30 |
价格 |
|||
30 |
出版社 |
列名 |
约束 |
|||
Char(10) |
10 |
读者编号 |
||
Varchar(10) |
10 |
读者姓名 |
||
BookId |
Char(10) |
10 |
图书编号 |
|
Date |
|
Not null |
借书时间 |
|
BackTime |
Date |
|
Not null |
归还时间 |
Varchar(10) |
10 |
Not null |
是否归还 |
列名 |
数据类型 |
约束 |
||
Char(10) |
10 |
读者编号 |
||
ReaderName |
Varchar(10) |
10 |
Not null |
读者姓名 |
BookId |
Char(10) |
10 |
图书编号 |
|
BackTime |
Date |
|
Not null |
归还时间 |
列名 |
数据类型 |
约束 |
||
Char(10) |
10 |
读者编号 |
||
ReaderName |
Varchar(10) |
10 |
Not null |
姓名 |
Sex |
Varchar(10) |
10 |
Not null |
性别 |
Varchar(30) |
30 |
Not null |
职业 |
|
Date |
|
Not null |
出生日期 |
|
Identify |
Varchar(20) |
20 |
Not null |
证件号码 |
Tel |
Varchar(20) |
20 |
Not null |
电话 |
|
Varchar(20) |
20 |
Not null |
3.1 数据库
3.2 表
Id char(10) not null, Bookname varchar(60) not null, BookId char(10) not null primary key, constraint bookinfo_fk foreign key (Id) references manager(Id) ); |
||
booktype |
BookId char(10) not null primary key, Bookname varchar(60) not null, |
|
bookborrow |
ReaderId char(10) not null primary key, ReaderName varchar(10) not null, BookId char(10) not null, constraint bookborrow_fk foreign key (BookId) references bookinfo(BookId) ); |
|
bookback |
ReaderId char(10) not null primary key, ReaderName varchar(10) not null, BookId char(10) not null, constraint bookback_fk foreign key (BookId) references bookinfo(BookId) ); |
|
ReaderId char(10) not null primary key, ReaderName varchar(10) not null, Vocation varchar(30) not null, |
3.3 数据操纵
数据操纵类型 |
说明 |
|
select * from bookborrow,readerinfo where readerinfo.ReaderId = ‘R0001’ and bookborrow.ReaderId = readerinfo.ReaderId; |
查询读者编号为R0001的读者信息和图书借阅信息 |
|
insert into manager values (‘001’, ‘李**’, ‘l123’); insert into manager values (‘002’, ‘董**’, ‘d123’); insert into bookinfo values (‘001’, ‘MySql数据库’, ‘林某某’, ‘000001’, 49.8, ‘人民邮电出版社’); insert into bookinfo values (‘001’, ‘Java程序设计‘, ‘赵某某’, ‘000002’, 59.0, ‘机械工业出版社’); insert into booktype values (‘000001’, ‘MySql数据库’, 100, ‘编程书籍‘, ‘1号书架’); insert into booktype values (‘000002’, ‘Java程序设计‘, ’89’, ‘编程书籍‘, ‘1号书架’); insert into bookborrow values (‘R0001’, ‘青玉案’, ‘000001’, ‘2022-10-23’, ‘2022-12-10’, ‘是’); insert into bookborrow values (‘R0002’, ‘北顾亭’, ‘000001’, ‘2022-10-24’, ‘2022-12-10’, ‘否’); insert into bookback values (‘R0001’, ‘青玉案’, ‘000001’,’2022-12-10′); insert into bookback values (‘R0002’, ‘北顾亭’, ‘000001’,’2022-12-10′); insert into readerinfo values (‘R0001’, ‘青玉案’, ‘男’, ‘学生‘, ‘2001-04-12’, ‘411456’, ‘1582476’, ‘297@qq.com‘); insert into readerinfo values (‘R0002’, ‘北顾亭’, ‘男’, ‘工人’, ‘2001-11-23’, ‘411423’, ‘1352686’, ‘225@qq.com‘); |
||
修改数据 |
update bookinfo set Price = 59.8 where BookId in (select BookId from booktype where Bookname = “MySql数据库”); |
|
删除数据 |
delete from bookback where ReaderId = (select ReaderId from readerinfo where ReaderId = ‘R0001’); |
3.4 视图
视图名 |
说明 |
|
create view readerinfo_v (读者编号,姓名,性别,职业,出生日期,证件号码,电话,电子邮箱)as select ReaderId,ReaderName,Sex,Vocation,Birthday,Identify,Tel,Emailfrom readerinfo where Vocation = ‘学生‘; select * from readerinfo_v; |
创建视图readerinfo_v,字段名用中文表示,包含所有学生的读者编号、姓名、性别、职业、出生日期、证件号码、电话和电子邮箱 |
|
readerlist_v |
create view readerlist_v (ReaderId,ReaderName,Bookname,BorrowTime,BackTime)as select readerinfo.ReaderId,readerinfo.ReaderName,Bookname,BorrowTime,BackTimefrom readerinfo,bookinfo,bookborrow where readerinfo.ReaderId = bookborrow.ReaderId and bookborrow.BookId = bookinfo.BookId; |
3.5 索引
索引名 |
定义索引语句 |
说明 |
对管理员信息表的管理员名称创建普通索引 |
||
create index combine_index on readerinfo(ReaderId,ReaderName,Tel); show index from readerinfo; |
说明 |
||
delimiter // create procedure select_p(in Bid char(10)) select * from bookinfo,bookborrow where bookinfo.BookId = Bid and bookinfo.BookId = bookborrow.BookId; end// delimiter ; |
||
update_p |
delimiter // create procedure update_p(in Rid char(10),in Rn varchar(10)) update bookback set ReaderName = Rn where ReaderId = Rid; end// delimiter ; |
|
interval_p |
delimiter // create procedure interval_p(in Date1 date,out Date2 integer) set Date2 = datediff(curdate(),Date1); end// delimiter ; |
|
compare_p |
delimiter // create procedure compare_p(in b1 char(10),in b2 char(10),out v varchar(30)) begin declare quantity1,quantity2 integer; select quantity into quantity1 from booktype where BookId = b1; select quantity into quantity2 from booktype where BookId = b2; end if; end// call compare_p(‘000001′,’000002’,@v); select @v; |
|
cursor_p |
delimiter // declare infor_cursor cursor for select ReaderId,BorrowTime,BackTime,IfBack from bookborrow; declare continue handler for not found set @cur=0; set @cur=1; fetch infor_cursor into ReaderIds,BorrowTimes,BackTimes,IfBacks; select ReaderIds,BorrowTimes,BackTimes,IfBacks; fetch infor_cursor into ReaderIds,BorrowTimes,BackTimes,IfBacks; end// delimiter ; |
3.7 存储函数
存储函数名 |
说明 |
|
delimiter // returns char(10) select count(*) into sumc from bookinfo where Price >= 50; end // delimiter ; |
||
delimiter // create function total_fun(Bid char(10)) declare qy char(20); select Price into pri from bookinfo where BookId = Bid; select Quantity into qy from booktype where BookId = Bid; set totals = pri * qy; end // delimiter ; select total_fun(‘000001’); |
||
select_fun |
delimiter // create function select_fun(Rid char(10)) returns char(10) begin declare Rne char(10); select ReaderName into Rne from readerinfo where ReaderId = Rid; if Rne is null then set Rne = “未找到该读者!”; return Rne; return Rne; end if; end // delimiter ; select select_fun(‘R0001’); |
|
compare_fun |
delimiter // create function compare_fun(Id1 char(10),Id2 char(10)) returns char(20) DETERMINISTIC begin declare Rid1 char(10); declare Rid2 char(10); declare Bn varchar(60); select Price into pce1 from bookinfo where BookId = Rid1; select Price into pce2 from bookinfo where BookId = Rid2; set Bn = “第二本书较便宜!”; end if; return Bn; end// delimiter ; select compare_fun(‘000001′,’000002’); |
|
pric_fun |
delimiter // create function pric_fun(Bid char(10)) begin declare p decimal(10,2); select Price into p from bookinfo where BookId=Bid; return p; end // delimiter ; select pric_fun(‘000001’); |
3.8 触发器
触发器名 |
说明 |
|
delimiter // create trigger update_trig before update on readerinfo for each row begin update bookborrow set ReaderName=new.ReaderName; update bookback set ReaderName=new.ReaderName; end // delimiter ; update readerinfo set ReaderName = “望海潮” where ReaderId = ‘R0001’; select * from bookborrow where ReaderId = ‘R0001’; select * from bookback where ReaderId = ‘R0001’; |
||
delimiter // create trigger delete_trig after delete on readerinfo for each row begin delete from bookborrow where ReaderId=old.ReaderId; delete from bookback where ReaderId=old.ReaderId; end // delimiter ; delete from readerinfo where ReaderId=’R0002′; select * from bookborrow where ReaderId = ‘R0002’; select * from bookback where ReaderId = ‘R0002’; |
||
insert_trig |
delimiter // create trigger insert_trig before insert on bookinfo for each row begin if new.Price < 20 then signal sqlstate ‘HY000’ set message_text = ‘价格不能低于20!’; end if ; end // delimiter ; insert into bookinfo values (‘001’, ‘软件工程‘, ‘张某某’, ‘000003’, 19.8, ‘清华大学出版社’); |
|
drop_trig |
delimiter // create trigger drop_trig after delete on bookback for each row begin if old.ReaderId = ‘R0001’ then signal sqlstate ‘HY000’ set message_text = ‘删除失败,你不是管理员!’; end if ; end // delimiter ; delete from bookback where ReaderId = ‘R0001’; |
3.9 事件
事件名 |
定义事件语句 |
说明 |
set @@global.event_scheduler = true; delimiter // create event update_event on schedule every 2 year starts now() do begin update bookborrow set IfBack = “黑名单“ where datediff(curdate(),BorrowTime) > datediff(BackTime,BorrowTime) and IfBack = “否”; end// delimiter ; select * from bookborrow; |
||
set @@global.event_scheduler = true; create event clean_event on schedule every 15 second ends ‘2022-12-13 16:10:00’ select * from readerinfo; |
||
set @@global.event_scheduler = true; create table blacklist like bookborrow; delimiter // create event blacklist_event on schedule every 1 year starts now() do begin insert into blacklist select * from bookborrow where datediff(curdate(),BorrowTime) > datediff(BackTime,BorrowTime) and IfBack = “否”; end // delimiter ; select * from blacklist; |
||
delback_event |
set @@global.event_scheduler = true; delimiter // create event delback_event on schedule every 1 year starts now() do begin delete from bookback where ReaderId in (select ReaderId from bookborrow where IfBack = “黑名单“); end // delimiter ; select * from bookback; |
|
book_total |
set @@global.event_scheduler = true; create table book_total(counts int not null primary key); delimiter // create event counts_event on schedule every 1 year starts now() do begin insert into book_total select sum(Quantity) from booktype; end// delimiter ; select * from book_total; |
存储过程名 |
定义及调用存储过程语句 |
说明 |
delete_aff |
delimiter // create procedure delete_aff(in Rid char(10)) begin declare exit handler for sqlexception rollback; delete from bookborrow where bookborrow.ReaderId = Rid; delete from bookback where bookback.ReaderId = Rid; end // delimiter ; select * from bookborrow where ReaderId=’R0002′; select * from bookback where ReaderId=’R0002′; |
|
insert_aff |
delimiter // create procedure insert_aff(in Bid char(10),in Bne varchar(60),in Qty char(20),in Tp varchar(30),in Sf varchar(20)) begin declare exit handler for sqlexception rollback; insert into booktype values(Bid,Bne,Qty,Tp,Sf); end // delimiter ; call insert_aff(‘000003’,”C语言程序设计“,97,”编程书籍“,”3号书架”); select * from booktype where BookId=000003; |
|
delimiter // create procedure update_aff(in Rne varchar(10),in Rid char(10)) begin declare exit handler for sqlexception rollback; update readerinfo set ReaderName = Rne where ReaderId = Rid; update bookborrow set ReaderName = Rne where ReaderId = Rid; update bookback set ReaderName = Rne where ReaderId = Rid; end // delimiter ; call update_aff(“燕歌行”,’R0002′); select * from readerinfo where ReaderId=’R0002′; select * from bookborrow where ReaderId=’R0002′; select * from bookback where ReaderId=’R0002′; |
创建存储过程,在其中使用事务,当修改读者信息表中的读者姓名同时修改借阅表和归还表中的读者姓名 |
定义用户语句 |
说明 |
||
admin01 |
grant select on readerinfo to ‘admin01’@’localhost‘; grant update on bookinfo to ‘admin01’@’localhost‘; |
||
admin02 |
grant all on manager to ‘admin02’@’localhost’ identified by ‘123456’ with grant option; |
操作类型 |
对应操作的SQL语句 |
说明 |
mysql -u root -p library_lwl <D:library_lwl_backup.sql source D:library_lwl_backup.sql select * from bookback; |
||
create table bk_readerinfo like readerinfo; select * from bk_readerinfo; load data infile ‘D:/readerinfo.txt‘ into table bk_readerinfo select * from bk_readerinfo; |
4 项目总结及心得
项目刚开始是非常难的,虽然内容都是之前讲过的,但是如果只局限于学过的内容是提高不了自己的能力的,所以每道题我都会思考很多新的思路,通过询问老师,与同学交流,我真的学到了课堂上没有的内容。
虽然期间遇到了许许多多的问题与错误,但只要相信自己,一直坚持做下去,就一定可以学会学好。通过这次项目的学习,真真正正的让我学到了好多书上没有的内容,老师的耐心解答和同学们的互帮互助使我受益匪浅,而这次项目也会成为我积累的经验,使得我以后的工作可以顺利进行,并进一步提高自己的学习能力和思考能力。
原文地址:https://blog.csdn.net/qq_64314976/article/details/128627464
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_42570.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!