tushuguangli.sql

图书管理系统源代码！！很好！希望对大家有用·！

--QQ:475717989
--(1)主要的数据表
--书库基本信息表，借书信息表，还书信息表，员工信息表，学生信息表，挂失信息记录表。
--(2)主要功能模块
--1)	图书基本情况的录入，修改、查询，删除基本操作。
--2)	员工基本情况的录入、修改、查询，删除基本操作。
--3)	学生基本情况的录入、修改、查询，删除基本操作。
--4)	借书功能，借书基本情况的查询
--5)	还书功能，还书基本情况的查询
--6)	对超期欠费的情况能自动给出提示信息。
--7)	书籍挂失（挂失存储过程，）
--8)	交纳罚金（删除borrow的记录，然后再在lost中记录）
--9)	用户权限管理
--10)	具有数据备份和数据恢复功能。

use master
go

if exists (select * from dbo.sysdatabases where name = 'library') 
drop database library
GO
create database library
go
use library
go

--以下为建表过程

--书库表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[booklib]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[booklib]
GO

CREATE TABLE booklib(
	b_id varchar(10)NOT NULL primary key,
	b_name varchar (15)NOT NULL ,
	kind varchar (10)NULL ,
	press varchar (20)NULL ,
	author varchar (10) NULL,
	price int null,
	left_num int null,
	total int null
)
GO

create unique index b_idorder on booklib(b_id)
GO
--触发器创建命令一次只能用一个（一次只能创建一个触发器）
create trigger new_b_record
on booklib
for insert 
as print'插入了一个图书信息！'
go
create trigger upd_b_record
on booklib
for update 
as print'更新了一个图书信息！'
go
create trigger del_b_record
on booklib
for delete 
as print'删除了一个图书信息！'
go

--为书库插入数据
insert into booklib values('gs1','高等数学','大学教材','高等教育出版社','陈惠南',50,20,20)
insert into booklib values('ls1','离散数学','大学教材','高等教育出版社','蔡威',60,20,20)
insert into booklib values('xd1','线性代数','大学教材','高等教育出版社','崔沭阳',30,20,20)
insert into booklib values('wl1','大学物理','大学教材','高等教育出版社','郭单',45,20,20)
insert into booklib values('hx1','大学化学','大学教材','高等教育出版社','陈惠',80,30,30)
insert into booklib values('gy1','高级英语','大学教材','高等教育出版社','陈南',60,20,20)

--为书库插入数据，存储过程
create PROCEDURE new_book
(@b_id varchar(10),@b_name varchar(15),@kind varchar(10),@press varchar(20),@auther varchar(10),@price int,@left_num int)
as 
if @b_id is null  or @b_name is null
begin
	print'图书的id或书名不得为空！'
	return -1
end

if exists(select * from booklib where b_id=@b_id)
begin
	print'新图书的id不得和已有图书重复！'
	return -2
end
insert into booklib values(@b_id,@b_name,@kind,@press,@auther,@price,@left_num,@left_num)
go

execute new_book  'wy1','大学文艺','大学教材','人民教育出版社','丁东',90,30
execute new_book  'ys1','大学艺术','大学教材','人民教育出版社','宁夏',46,20
execute new_book  'sx1','思想修养','大学教材','人民教育出版社','萧威',71,40
--drop proc new_book


--为书库创建查询视图
create view book_view as
select b_id 
from booklib
go

create view book_view2 as
select *
from booklib
go
--为书库修改数据，存储过程
create PROCEDURE change_book
(@b_id varchar(10),@b_name varchar(15),@kind varchar(10),@press varchar(20),@auther varchar(10),@price int,@left_num int,@total int)
as 
if @b_id is null  or @b_name is null
begin
	print'图书的id或书名不得为空！'
	return -1
end
update booklib
set b_name=@b_name,kind=@kind,press=@press,author=@auther,price=@price,left_num=@left_num,total=@total
where b_id=@b_id
go

--exec change_book 'gs1','电子商务','课外读物','高教','陈楠',50,30,30

--为书库查询数据，存储过程
create PROCEDURE search_book
(@id varchar(10))
as 
if @id is null 
begin
	print'图书的id不得为空！'
	return -1
end

if not exists(select * from book_view where b_id=@id)
begin
	print'无该条记录！'
	return -2
end
select * from book_view where b_id=@id
go

--execute search_book 'gy1' 
--drop proc search_book

--为书库删除数据，存储过程
create PROCEDURE del_book
(@id varchar(10))
as 
if @id is null 
begin
	print'图书的id不得为空！'
	return -1
end

if not exists(select * from booklib where b_id=@id)
begin
	print'无该条记录！'
	return -2
end
delete from booklib where b_id=@id
go

--execute del_book '2'


--员工档案表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ygrecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ygrecord]
GO

CREATE TABLE ygrecord(
	yhid varchar(10)NOT NULL primary key,--yh=用户
	ygname varchar (10)NOT NULL,
	bor_num int default 6,--员工当前能借的书数
	ygdept_name varchar (10)NULL
)
GO

create unique index ygorder on ygrecord(yhid)
GO

create trigger new_ygrecord
on ygrecord
for insert 
as print'插入了一个员工信息！'
go
create trigger upd_ygrecord
on ygrecord
for update 
as print'更新了一个员工信息！'
go
create trigger del_ygrecord
on ygrecord
for delete 
as print'删除了一个员工信息！'
go

--为员工表插入数据，存储过程
create PROCEDURE new_yg
(@id varchar(10),@yg_name varchar(10),@num int,@dept varchar(10))
as 
if @id is null  or @yg_name is null
begin
	print'员工的id或员工名不得为空！'
	return -1
end

if exists(select * from ygrecord where yhid=@id)
begin
	print'新员工的id不得和已有员工重复！'
	return -2
end
insert into ygrecord values(@id,@yg_name,@num,@dept)
go


execute new_yg  'jw1','高强',6,'教务处'
execute new_yg  'dw1','刘文',6,'党务出'
execute new_yg  'jc1','李伟',6,'计财处'
execute new_yg  'st1','朱军',6,'食堂'

--为员工表创建查询视图
create view yg_view as
select yhid 
from ygrecord
go
--为员工表查询数据，存储过程
create PROCEDURE search_yg
(@id varchar(10))
as 
if @id is null 
begin
	print'员工的id不得为空！'
	return -1
end

if not exists(select * from yg_view where yhid=@id)
begin
	print'无该条记录！'
	return -2
end
select * from ygrecord where yhid=@id
go


--execute search_yg 'jw1'
--drop proc search_yg

--为员工修改数据，存储过程
create PROCEDURE change_yg
(@id varchar(10),@yg_name varchar(10),@num int,@dept varchar(10))
as 
if @id is null
begin
	print'员工id不得为空！'
	return -1
end

if not exists(select * from ygrecord where yhid=@id)
begin
	print'该员工不存在！'
	return -2
end
update ygrecord
set yhid=@id,ygname=@yg_name,bor_num=@num,ygdept_name=@dept
where yhid=@id
go


--exec change_yg 'dw1','刘胜',5,'教务处'

--删除员工信息，存储过程
create PROCEDURE del_yg
(@id varchar(10))
as 
if @id is null
begin
	print'员工的id不得为空！'
	return -1
end

if not exists(select * from ygrecord where yhid=@id)
begin
	print'该员工不存在！'
	return -2
end
delete from ygrecord where yhid=@id
go


--execute del_yg 'jc1'




--学生表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sturecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sturecord]
GO

CREATE TABLE sturecord(
	yhid varchar(10)NOT NULL primary key ,
	stuname varchar (10)NOT NULL,
	bor_num int default 4,--学生当前能借的书数
	studept_name varchar (10)NULL
)
GO

create unique index stuorder on sturecord(yhid)
GO

create trigger new_sturecord
on sturecord
for insert 
as print'插入了一个学生信息！'
go
create trigger upd_sturecord
on sturecord
for update 
as print'更新了一个学生信息！'
go
create trigger del_sturecord
on sturecord
for delete 
as print'删除了一个学生信息！'
go
--为学生表插入数据，存储过程
create PROCEDURE new_stu
(@id varchar(10),@stu_name varchar(10),@num int,@dept varchar(10))
as 
if @id is null  or @stu_name is null
begin
	print'学生的id或学生名不得为空！'
	return -1
end

if exists(select * from sturecord where yhid=@id)
begin
	print'新学生的id不得和已有学生重复！'
	return -2
end
insert into sturecord values(@id,@stu_name,@num,@dept)
go

--execute new_stu  '05111711','李一号',4,'软件工程系'
--execute new_stu  '05111712','李寅',4,'软件工程系'
--execute new_stu  '05111713','刘辰',4,'软件工程系'
--execute new_stu  '05111714','刘晟',4,'软件工程系'
--execute new_stu  '05111715','卢萍',4,'软件工程系'


--为学生表表创建查询视图
create view stu_view as
select yhid 
from sturecord
go
--为学生表查询数据，存储过程
create PROCEDURE search_stu
(@id varchar(10))
as 
if @id is null 
begin
	print'学生的id不得为空！'
	return -1
end

if not exists(select * from stu_view where yhid=@id)
begin
	print'无该条记录！'
	return -2
end
select * from stu_view where yhid=@id
go

--execute search_stu '05111711'

--为学生表修改数据，存储过程
create PROCEDURE change_stu
(@id varchar(10),@stu_name varchar(10),@num int,@dept varchar(10))
as 
if @id is null
begin
	print'学生id不得为空！'
	return -1
end
if not exists(select * from sturecord where yhid=@id)
begin
	print'该学生不存在！'
	return -2
end

update sturecord
set yhid=@id,stuname=@stu_name,bor_num=@num,studept_name=@dept
where yhid=@id
go
exec change_stu '05111711','李亦昊',5,'软件工程'

--删除学生信息，存储过程
create PROCEDURE del_stu
(@id varchar(10))
as 
if @id is null
begin
	print'学生的id不得为空！'
	return -1
end

if not exists(select * from sturecord where yhid=@id)
begin
	print'该学生不存在！'
	return -2
end
delete from sturecord where yhid=@id
go
--execute del_stu '05111711'


--借书表--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[borrow]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[borrow]
GO

CREATE TABLE borrow(
	yhid varchar(10)NOT NULL,
	yh_name varchar (10)NOT NULL,
	b_id varchar(10)NOT NULL foreign key references booklib(b_id),
	b_name varchar (15)NOT NULL,
	bor_date datetime not null
)
GO

create trigger new_borrow
on borrow
for insert 
as print'借书成功！'
go
create trigger del_borrow
on borrow
for delete 
as print'删除借书信息！'
go
--为借书表创建查询视图
create view borrow_view as
select * 
from borrow
go
--借书表信息查询,存储过程
create procedure search_borrow
(@yh_id varchar(10))