mylibary数据库创建脚本.sql

图书馆管理系统,用java编写,有原码,有检索,查询,修改,删除等功能

create database MyLibary
GO
--drop database MyLibary
use MyLibary
GO
drop table borrowed
drop table history
drop table manager
drop table book
drop table reader



--创建书表
create table book
(
	b_id varchar(32) not null  ,--索取号，相同的书索取号相同，条码号唯一
	b_code varchar(10) not null primary key,--条码
	b_name varchar(100)not null ,--书名
	b_publish varchar(32) not null ,--出版社
	b_author varchar(50) not null,--作者
	b_pagecount int not null ,--书的页数
	b_price varchar(10) not null,--书的价格
	b_out int not null--保存书是否已经借出,0表示在馆，1表示借出
)
Go
--创建管理员表
create table manager
(
	m_id varchar(16) not null primary key ,--管理员登陆id
	m_name varchar(32) not null ,--管理员名称
	m_password varchar(16) not null --管理员登陆密码
)
GO
--创建读者表
create table reader
(
	r_id varchar(8) not null primary key ,--借阅证号
	r_name varchar(20) not null ,--读者姓名
	r_password varchar(32) not null,--读者密码
	inyear char(4) , --入学年份--参考inyear(y_id)
	college varchar(32) ,--学院--参考collage()
	degree varchar(12), --学位等级
	borrownum int  --已经借阅书数
)
GO
--创建已经借出的书的信息
create table borrowed
(
	r_id varchar(8) ,--读者证号
	b_code varchar(10) primary key,--索取号-->查找书目信息
	t_out varchar(20) not null ,--借出时间
	t_back varchar(20) not null ,--应该还回时间
	t_reborrownum int not null --续借次数
)	
go
create table history
(
	id int identity(1,1) not null primary key ,
	r_id varchar(8) not null ,
	b_code varchar(10) not null ,
	h_out varchar(20) not null ,
	h_back varchar(20) 
)

GO
--建立borrowed表的约束
alter table borrowed
	add constraint FK_borrowed_reader foreign key (r_id) references reader(r_id)
go
alter table borrowed
	add constraint FK_borrowed__book foreign key (b_code) references book(b_code)
go
alter table borrowed
	add constraint DF_borrowed_reb default(0)for t_reborrownum
go
--reader表中借书数目添加default约束
alter table reader
	add constraint DF_reader_borrownum default(0)for borrownum
go
--添加book表的约束
alter table book
	add constraint CK_book_out check(b_out in(0,1))
go
alter table book
	add constraint DF_book_out default(0)for b_out
go

--为history表添加约束
alter table history
	add constraint FK_history_rid foreign key (r_id) references reader(r_id)
go
alter table history
	add constraint FK_history_bcode foreign key (b_code) references book(b_code)
go
insert into manager values('admin','落叶','123456')
go
insert into book values('TP311.138160','JDC0875909','SQL Server 编程篇','电子工业出版社','本书编委会',413,39.00,default)
insert into book values('TCP312JA314','03176635','Hibernate 3和java Persistence API程序开发 从入门到精通','清华大学出版社','葛京',393,49.00,default)
insert into book values('TC393.922.285','JDC08334','Flash MX 2004基础与实例培训教程','中国电力出版社','庄辉',355,35.00,default)
insert into book values('TC393.920.285','JDC32541','中文版AutoCAD 2005基础与实例培训教程','中国电力出版社','雏树群',355,35.00,default)
insert into book values('TC393.920.285','JDC08241','Authorware 7.0基础与实例培训教程','中国电力出版社','裴红一',355,35.00,default)
insert into book values('TC393.920.285','JDC08390','Dreamweaver MX 2004基础与实例培训教程','中国电力出版社','赵育良、许兆林',355,35.00,default)
insert into book values('TC393.920.285','JDC08351','asd MAX 7.0基础与实例培训教程','中国电力出版社','崔燕菊',355,35.00,default)
select * from book
Go
insert into reader values('05080116','王娟','wj1129','2005','管理学院','本科',default)
select * from reader
GO
--drop trigger borrowed_insert
--drop trigger borrowed_delete
--borrowed表插入触发器
create trigger borrowed_insert 
on borrowed for insert
	as
begin
	--声明变量，以便插入history记录
	declare @rid varchar(8),@bcode varchar(10),@tout varchar(20)
	--更新读者的借书数目
	update reader 
	set borrownum=(select borrownum from reader 
		where r_id=(select r_id from inserted))+1
	--更新书的在馆或借出状态
	update book set b_out=1 where b_code=(select b_code from inserted)
	--插入history记录
	select @rid=r_id,@bcode=b_code,@tout=t_out from inserted
	insert into history values(@rid,@bcode,@tout,'未还')
end
GO
--borrowed删除触发器
create trigger borrowed_delete
on borrowed for delete
	as 
begin
	declare @hback varchar(20)
	select @hback= CONVERT(VARCHAR(10),GETDATE(),120)  
	--更新读者借书记录
	update reader set borrownum=(select borrownum from reader 
		where r_id=(select r_id from deleted))-1
	--更新书的在馆借出状态
	update book set b_out=0 where b_code=(select b_code from deleted)
	--更新history中的还书日期
	update history set h_back=@hback where b_code=(select b_code from deleted) and h_back='未还'
end
GO
	
/*
insert into inyear values('2001')
insert into inyear values('2002')
insert into inyear values('2003')
insert into inyear values('2004')
insert into inyear values('2005')
insert into inyear values('2006')
insert into inyear values('2007')
insert into inyear values('2008')
select * from inyear

insert into degree values('专科')
insert into degree values('本科')
insert into degree values('研究生')
insert into degree values('博士')
select * from degree

insert into collage values('建筑学院')
insert into collage values('管理学院')
insert into collage values('土木学院')
insert into collage values('人文学院')
insert into collage values('艺术学院')
insert into collage values('材料学院')
insert into collage values('信控学院')
insert into collage values('环工学院')
select * from collage
*/