create database tsgl_system;
use tsgl_system;
show variables like 'storage_engine';
create table readers(
rno char(10) primary key,
rname char(4) not null,
rsex enum('男','女'),
rtel char(11) not null unique,
rfre smallint,
rsum tinyint
); /*读者信息表*/
create table books(
bno char(10) primary key,
bdate date not null,
bauthor char(4),
bsum int, /*总量*/
bname varchar(15),
bsort varchar(6), /*类别*/
bfre smallint, /*借阅次数*/
bmony double
);/*图书信息表*/
create table admins(
ano char(10) primary key,
aname char(4) not null,
aage int,
atel char(10) not null unique
);/*管理员表*/
create table lend(
rno char(10) not null,
bno char(10) not null,
ldate datetime,
rdate datetime,
primary key (rno,bno),
foreign key(bno) references books(bno),
foreign key(rno) references readers(rno)
);/*借阅归还表*/
create table punish(
rno char(10) not null,
bno char(10) not null,
pdate datetime,
ndate timestamp default now(),
pmoney double,
primary key (rno,pdate),
foreign key(bno) references books(bno),
foreign key(rno) references readers(rno)
);/*逾期处罚表*/
insert into readers values /*插入6条读者记录*/
('2017070231','李丽','女','17336464751',0,0),
('2017070232','汪洋','男','17336464752',0,0),
('2017070233','刘晨','女','17336464753',0,0),
('2017070234','刘小晨','男','17336464754',0,0),
('2017070235','张放','男','17336464755',0,0),
('2017070236','乔云萍','女','17336464756',0,0);
insert into books values /*插入6类书*/
('2017122401','2017-12-24','刘伟',6,'高等数学','数学',0,20),
('2017122402','2017-12-24','张建',6,'计算机导论','计算机',0,30),
('2017122403','2017-12-24','王刚',6,'C程序设计','程序',0,40),
('2017122404','2017-12-24','李强',6,'数据结构','计算机',0,50),
('2017122405','2017-12-24','刘军',6,'数据库系统','程序',0,60),
('2017122406','2017-12-24','李明明',6,'模糊数学','数学',0,70);
insert into admins values
('1999070401','张三','19','15193913868'),
('1998070402','李四','20','15293913867'),
('1997070403','王五','21','15393913869');
select *
from admins;
select *
from books
where bauthor like '%刘%';
flush privileges;
create index book1 on books(bno); /*建立索引，图书表索引*/
create index admin1 on admins(ano); /*管理员表索引*/
create index reader1 on readers(rno); /*读者表索引*/
show index from books;
show index from admins;
show index from readers;
show index from lend;
show index from punish;
create view reder_1
as select *
from readers; /*建立读者表视图*/
create view admin_1
as select *
from admins; /*建立管理员视图*/
create view lend_1
as select *
from lend; /*建立借书表视图*/
create view punish_1
as select *
from punish; /*建立逾期视图*/
create view book_1
as select *
from books; /*建立图书视图*/
/*借书触发器及其存储过程*/
create trigger ts_sub after insert /*图书被借后总量减少*/
on lend for each row
begin
update books
set bsum = bsum - 1
where new.bno = bno;
update books
set bfre = bfre + 1
where new.bno = bno;
update readers
set readers.rfre = readers.rfre + 1
where new.rno = readers.rno;
update readers
set readers.rsum = readers.rsum + 1
where new.rno = readers.rno;
end;
#当借书时（insert），改书总量减少（bsum），阅览量增加（bfre），读者持有图书量增加（rsum），读者阅读量怎家（rfre）
drop trigger ts_sub;
create procedure jieshu(in r_bh varchar(10),in b_bh varchar(10))
begin
set @sum_b = (select bsum from books where bno=b_bh);
set @sum_r = (select rsum from readers where rno=r_bh);
if @sum_b > 0 && @sum_r < 3 then
insert into lend
values(r_bh,b_bh,now(),date_add(NOW(),interval 1 MONTH));
elseif @sum_b > 0 then /*借书存储过程*/
select '已超过借书上限，最多可借3本书！' 提示;
else
select '改书不存在！' 提示;
end if;
end;
#借书时先判断，改书是否还有库存，读者持有量是否小于3，如果持有量大于等于3则无法借书，如果书不存在也无法借书
drop procedure jieshu;
call jieshu('2017070231','2017122401');
call jieshu('2017070231','2017122402');
call jieshu('2017070231','2017122403');
call jieshu('2017070231','2017122404');
call jieshu('2017070232','2017122403');
call jieshu('2017070232','2017122404');
/*还书触发器及其存储过程*/
create trigger ts_add after delete
on lend for each row
begin
update books
set bsum = bsum + 1
where old.bno = bno;
update readers
set readers.rsum = readers.rsum - 1
where old.rno = readers.rno;
end;
#（对lend表delete操作）还书时，图书总量增加，读者图书持有量减少
drop trigger ts_add;
create procedure huanshu(in r_bh varchar(10),in b_bh varchar(10))
begin
set @hs_dt = (select rdate from lend where r_bh=rno and b_bh=bno);
set @ck_mn = datediff(now(),@hs_dt);
if datediff(now(),@hs_dt) > 0 then
insert into punish
values(r_bh,b_bh,@hs_dt,now(),5*@ck_mn);
delete
from lend
where r_bh=rno and b_bh=bno;
select @ck_mn*5 请先交处罚费（元）：,@ck_mn 拖欠日期（天）：;
elseif (select count(*) from lend where rno=r_bh and bno=b_bh) > 0 then
delete
from lend
where r_bh=rno and b_bh=bno;
select '还书成功！' 提示;
else
select '不存在该用户或该用户未借书！' 提示;
end if;
end;
#还书时，先判断是否超过规定借阅天数，如果超过则处罚对应金额：超期天数*5，并完成还书，
#否则判断一下是否存在有借阅记录，如果有则还书成功，如果没有则提示该用户未借书或不存在。
drop procedure huanshu;
call huanshu('2017070231','2017122401');
call huanshu('2017070231','2017122402');
call huanshu('2017070231','2017122403');
call huanshu('2017070231','2017122404');
call huanshu('2017070232','2017122403');
call huanshu('2017070232','2017122404');
#模糊查询，按书名查找书
create procedure bsearch_xm(in b_name varchar(4))
select bno 编号,bdate 入库日期,bauthor 作者,bsum 存量,bname 书名,bsort 类别,bfre 借阅次数,bmony 价格
from books
where bname like CONCAT('%',b_name,'%');
drop procedure bsearch_xm;
call bsearch_xm('');
#模糊查询，按读者姓名查找个人借阅情况信息
create procedure rsearch_xm(in r_name varchar(4))
select readers.rno 编号,readers.rname 姓名,readers.rsex 性别,readers.rtel 电话,
readers.rfre 借书总次数,readers.rsum 已借阅数,books.bname 已借阅书籍
from readers,books,lend
where readers.rno = lend.rno and books.bno = lend.bno and
rname like concat('%',r_name,'%');
drop procedure rsearch_xm;
call rsearch_xm('李');
call rsearch_xm('汪');
#特殊视图
create view 图书热度借阅榜（TOP⑤）
as
select bname 书名,bsum 库存,bfre 热度, bauthor 作者,bmony 价格
from books
order by bfre desc/*排序asc*/
limit 5
create view 借读明星榜（TOP⑤）
as
select rname 姓名,rsex 性别,rfre 借阅次数
from readers
order by rfre desc
limit 5
#创建用户
create user 'reader_0'@'localhost'
identified by '123';
insert into
mysql.user(user,host,authentication_string, ssl_cipher,x509_issuer,x509_subject)
values('admin_0','localhost',password('123'),'','','');
select * from mysql.user
grant show view,select /*授予读者查看图书权限*/
on books
to 'reader_0'@'localhost' identified by '123';
grant execute /*允许调用存储过程权限*/
on procedure bsearch_xm
to 'reader_0'@'localhost' identified by '123';
revoke all privileges,grant option
from 'reader_0'@'localhost';
grant show view,select,insert,update,delete #给图书管理员授权
on tsgl_system.*
to 'admin_0'@'loc
mysql图书管理系统.zip （19个子文件） 
201707024325-王旭东 
201707024325-王旭东.doc 452KB
ibdata1 18MB 图书管理.sql 9KB tsgl_system admin_1.frm 689B reder_1.frm 864B ts_add.TRN 36B ts_sub.TRN 36B books.frm 9KB @56fe@4e66@70ed@5ea6@501f@9605@699c@ff08top@2464@ff09.frm 995B lend.TRG 916B punish.frm 8KB punish_1.frm 777B readers.frm 9KB lend_1.frm 667B @501f@8bfb@660e@661f@699c@ff08top@2464@ff09.frm 821B book_1.frm 1014B lend.frm 8KB admins.frm 8KB db.opt 59B
评论5
最新资源