图书管理增删改查sql全语句
图书管理增删改查 sql全语句
⼤学平时作业实操。
知识点：建表 增删改查 触发器 存储过程
create database图书馆管理信息系统
on primary
(name='图书馆管理',
filename ='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2\MSSQL\DATA\图书馆管理.mdf',
size =5MB,
filegrowth =1024KB
)
log on
(name='图书馆管理_log',
filename ='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2\MSSQL\DATA\图书馆管理_log.ldf',
size =5MB,
filegrowth=1024KB
)
EXEC sp_changedbowner 'sa'
--图书现有库存表
CREATE table图书现有库存表
(图书ID int not null primary key,
书号varchar(50)not null,--索书号
书名 nvarchar(20)not null,
作者 nvarchar(100),
简介 nvarchar(max),
类别 nvarchar(20)default'未知',
价格 money default20,
出版社 nvarchar(20)default'某出版社',
出版⽇期 nvarchar(50),
⼊库时间datetime,
现有库存数量int default0,
库存总量int default0,
最⼩库存量int default1,
库存位置 nvarchar(20)default'图书馆东南⾓'
)
select*from图书现有库存表
update图书现有库存表set书号='TP311.561/78'where书号='TP311.561'
--读者信息表
create table读者信息表
(
读者ID int not null primary key,
证件类型 nvarchar(20)default'居民⾝份证'check(证件类型in('居民⾝份证','港澳台胞通⾏证','⼠官证','学⽣证','驾驶证','护照','其他'))，证件号码nchar(20),
姓名 nvarchar(20),
职业 nvarchar(10)default'学⽣'check(职业in('教师','学⽣','教⼯','其他')),
是否受限bit default0,
书本借阅数量tinyint default0check(书本借阅数量<=10and书本借阅数量>=0),
所属单位 nvarchar(100)default'安财',
地址 nvarchar(100)default'曹⼭路962号',
char(20)default'10086'
)
create table借书登记表
(
(
借书ID int not null,
图书ID int not null,
读者ID int not null,
借阅数量tinyint default1check(借阅数量=1),
借阅⽇期datetime default getdate()not null,
应还⽇期datetime default dateadd(d,45,getdate()),
是否归还bit default0,
是否续借bit default0,
primary key(借书ID),
foreign key(图书ID)references图书现有库存表(图书ID),
foreign key(读者ID)references读者信息表(读者ID),
)
create table还书登记表
(
还书ID int not null,
图书ID int not null,
读者ID int not null,
归还数量tinyint default1check(归还数量=1),
归还⽇期datetime default getdate()not null,
是否超期bit default0,
超期天数int default0,
primary key(还书ID),
foreign key(图书ID)references图书现有库存表(图书ID),
foreign key(读者ID)references读者信息表(读者ID),
)
--⼊库单表
create table⼊库单表
(
⼊库单ID int not null primary key,
⼊库⽇期 smalldatetime,
图书ID int,
⼊库书号varchar(20),
书名 nvarchar(20),
⼊库数量int check(⼊库数量>=1),
foreign key(图书ID)references图书现有库存表(图书ID),
);
go
--创建存储过程，按“书号”（参数）查询该图书的库存信息
create procedure书号查询
@书号varchar(50)
as
begin
if(exists(select*from图书现有库存表where书号= @书号))
begin
select*
from图书现有库存表
where书号= @书号
end
else begin
print'书库中不存在此书，请重新核对书号'
rollback transaction
end
end
go
execute书号查询'K0/30'
execute书号查询100
go
--创建存储过程，按“类别”（参数）查询该类图书的库存信息、借阅量。
create proc类别查询
@类别 nvarchar(20)
as
begin
if((exists(select*from图书现有库存表where类别= @类别)))
select库存总量-现有库存数量借阅量,*from图书现有库存表where类别= @类别else
begin
print'书库中不存在此类别，请重新核对类别'
rollback transaction
end
end
execute类别查询100
execute类别查询 K0
go
--创建存储过程，按“读者编号”（参数）查询读者个⼈信息
create procedure读者编号查询
@读者ID int
as
begin
if(exists(select*from读者信息表where读者ID = @读者ID))
begin
select*
from读者信息表
where读者ID = @读者ID
end
else begin
print'该表中不存在该读者，请检查'
rollback transaction
end
end
execute读者编号查询100--⽆此⼈
execute读者编号查询1001--有此⼈
go
-
-创建存储过程，按“读者编号”（参数）查询该读者借阅情况
create procedure借书查询
@读者ID varchar(50)
as
begin
if(exists(select*from借书登记表where读者ID = @读者ID))
begin
select*
from借书登记表
where读者ID = @读者ID
end
else if(exists(select*from读者信息表where读者ID = @读者ID))
begin
print'该读者没有借阅记录'
end
else
begin
print'⽆此读者，请检查输⼊'
end
end
execute借书查询1001
execute借书查询1002
execute借书查询100
drop trigger借书
go
create trigger借书on借书登记表
after insert
as
begin
declare@BORROW as int,@BOOKID as int
declare@READERID as int,@borrow_count as int
declare@BOOK_ALL as int
declare@READER_BOOKS as int
declare@MIN_BOOKS as int
declare@flag as int
select@BORROW=inserted.借书ID,
@BOOKID=inserted.图书ID,
@READERID=inserted.读者ID,
@BOOK_ALL=图书现有库存表.现有库存数量,
@MIN_BOOKS=图书现有库存表.最⼩库存量
from inserted join图书现有库存表on inserted.图书ID=图书现有库存表.图书ID
--该读者的阅读记录表
select@flag=0
select@READER_BOOKS=书本借阅数量from读者信息表where读者ID=@READERID
-
- 是否借过这本书还没还
select@borrow_count=0
select@borrow_count=sum(借阅数量)from借书登记表
where是否归还=0and@READERID=读者ID and@BOOKID=图书ID
if(exists(select*from读者信息表where@READERID=读者ID and是否受限=1))
begin
print'请交，解除限制'
select@flag=1
rollback transaction
end
if(@READER_BOOKS>=10)
begin
print'借阅图书超出10本，请及时归还！'
select@flag=1
rollback transaction
end
if(@borrow_count>=1)
begin
print'您已借阅该书，不可再借'
select@flag=1
rollback transaction
end
if(@BOOK_ALL<1)
begin
print'该书已⽆库存'
select@flag=1
rollback transaction
end
if(@borrow_count<=@BOOK_ALL and@flag=0)
begin
update图书现有库存表set现有库存数量=现有库存数量-1where图书现有库存表.图书ID=@BOOKID
update读者信息表set书本借阅数量=书本借阅数量+1where读者ID=@READERID
if(@MIN_BOOKS>=(select图书现有库存表.现有库存数量from图书现有库存表where图书现有库存表.图书ID=@BOOKID)) begin
print'当前此图书库存量告急！'
end
end
end
go
drop trigger还书
go
create trigger还书on还书登记表
after insert
as
begin
declare@BORROW as int,@BOOKID as int
declare@READERID as int,@borrow_count as int
declare@BOOK_ALL as int
declare@READER_BOOKS as int
declare@MIN_BOOKS as int
declare@num as int
declare@date as datetime
declare@day as datetime
declare@flag as int
select@BORROW=inserted.还书ID,
@BOOKID=inserted.图书ID,
@READERID=inserted.读者ID,
@BOOK_ALL=图书现有库存表.现有库存数量,
@MIN_BOOKS=图书现有库存表.最⼩库存量
from inserted join图书现有库存表on inserted.图书ID=图书现有库存表.图书ID
select@num=归还数量from inserted
select@BORROW=借书ID from借书登记表where图书ID=@BOOKID and读者ID=@READERID
select@date=应还⽇期from借书登记表where读者ID=@READERID and图书ID=@BOOKID and是否归还=0 select@flag=0
--判断读者id合法性
if(not exists(select*from读者信息表where@READERID=读者ID))
begin
print'⽆此读者，请检查后重试'
select@flag=1
rollback transaction
end
--判断图书id的合法性
基本的增删改查语句
if(not exists(select*from借书登记表where@BOOKID=图书ID and是否归还=0))
begin
print'⽆此书的借阅记录，请检查后重试'
select@flag=1
rollback transaction
end
if(@flag!=1)
begin
update图书现有库存表set现有库存数量=现有库存数量+@num where图书ID=@BOOKID
update借书登记表set是否归还=1where图书ID=@BOOKID and读者ID=@READERID
update读者信息表set书本借阅数量=书本借阅数量-1where读者ID=@READERID
--交，改受限
if(datediff(day,@date,GETDATE())>0)
begin
update还书登记表set是否超期=1where还书ID=(select还书ID from inserted)
update还书登记表set超期天数=datediff(day,@date,getdate())where还书ID=(select还书ID from inserted) update读者信息表set是否受限=1where读者ID=@READERID
print'还书超期！请交！'
end
print'操作完成！'
end

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。