图书馆管理系统

--创建数据库和表
create database library;

use library
go
create table book(idforbook varchar(10) primary key not null,nameforbook varchar(20) not null,statusforbook smallint)

create table reader(idforreader varchar(10) primary key not null,nameforreader varchar(10) not null,totallendsforreader

smallint,statusforreader smallint)

create table lendrelation(
idforbook varchar(10),
idforreader varchar(10),
lenddate datetime DEFAULT getDate(),
primary key(idforbook,idforreader),
foreign key(idforbook)references book(idforbook),
foreign key(idforreader)references reader(idforreader))

create table manager(idformanager varchar(4),nameformanager varchar(10),passwordformanager varchar(20))

 

--****增加管理员
insert into manager
values('001','段华斌','123456')
--***定义管理员操作
--**登录 如果查询结果存在,前台允许登录,如果查询结果不存在,提示错误
select idformanager
from manager
where nameformanager='段华斌' AND passwordformanager='123456'
--**增加书籍
insert  into  book
values('012345','数据库系统教程',0)
--修改书籍名称
UPDATE book
set nameforbook='数据库原理'
where idforbook='012345'
--**增加书籍结束 增加用户
insert into reader
values('001','崔凯',0,1)
--**增加用户结束 借阅书籍
insert into lendrelation
values('012345','001',getdate(),60)
UPDATE book
SET statusforbook='1'
WHERE idforbook='012345'
UPDATE reader
SET totallendsforreader = totallendsforreader + 1
WHERE idforreader='001'
go

--**借阅书籍结束 归还书籍
delete from  lendrelation
     where idforbook='012345'
UPDATE book
SET statusforbook='0'
WHERE idforbook='012345'
UPDATE reader
SET totallendsforreader = totallendsforreader - 1
WHERE idforreader='001'
go
--**首期规划失误**
--**在借阅关系表中插入一个属性列,剩余天数(由院图书馆启发)
use library
alter  table  lendrelation 
       add  remaining  int
--**学生续借处理
UPDATE lendrelation
set remaining=60-datediff("d",lenddate,getdate())+30
where idforreader='001'
--**每天早上执行一次检查用户是否超期未还
use library
go
UPDATE lendrelation
SET remaining=60-datediff("d",lenddate,getdate())
UPDATE reader
SET statusforreader=0
WHERE idforreader in
(
select idforreader
from lendrelation
where remaining<0)
--**查找活动状态为冻结的读者
use library
go
select *
from reader
where statusforreader=0
--**前台可以设置一报警程序,不允许冻结读者借阅书刊,下面的程序为解冻程序
use library
go
UPDATE lendrelation
SET remaining=60-datediff("d",lenddate,getdate())
UPDATE reader
SET statusforreader=1
WHERE idforreader not in
(
select idforreader
from lendrelation
where remaining<0)
--**学生毕业,删除学生记录 并在删除之前检查譔学生是否已经还清书籍
--*查询书籍是否已经还完
select totallendsforreader
from reader
where idforreader='001'
--*如果查询结果不为0 提醒读者还有哪些书没有还
select book.nameforbook
from book
where idforbook in(
select idforbook
from lendrelation
where idforreader='001')
--*如果查询结果为0 删除用户
delete from  reader
     where idforreader='001'
--*调用***管理员操作中的**还书操作
--**删除读者操作结束
--***管理员操作结束
--****管理员人员调动 删除旧管理员
delete from  manager
     where idformanager='001'
--以下操作为非管理员也进行的操作
--**查询读者信息
select *
from reader
where nameforreader like '%崔凯%'
--**查询某读者借阅信息
select reader.nameforreader,book.nameforbook,lendrelation.lenddate,remaining=60-

datediff("d",lenddate,getdate())
from reader,book,lendrelation
where lendrelation.idforbook = '012345' and reader.idforreader in(
select idforreader
from lendrelation
where idforbook = '012345')
--**查询图书
select *
from book
where nameforbook like '%数据库%'