图书馆管理系统.txt

实现基本的图书馆管理系统

create  database library2

drop database library2

create	table Users
(
	ID		numeric(10) 	primary key identity,
	UserName	varchar(10)	unique,
	Pwd		varchar(10)	not null,
	UserType	varchar(10)	not null
)

insert into Users(UserName,Pwd,UserType) values('smith','123','系统管理员')

select * from Users

drop table Users

create table Book_Info
(
	ID		numeric(10) 	primary key identity,
	BookID		varchar(20)	unique,
	BookName	varchar(20)	not null,
	Writer		varchar(20)	not null,
	Press		varchar(20)	not null,
	Price		numeric(4,2)	not null,
	InlibraryTime	datetime	not null,
	Total_Amount	numeric	(2)	not null,
	Now_Amount	numeric	(2)	not null
)


insert into Book_Info(BookID,BookName,Writer,Press,Price,InlibraryTime,Total_Amount,Now_Amount) values ('1001','围城','钱钟书','人民文学出版社',21.0,'1/1/2008',5,5)
insert into Book_Info(BookID,BookName,Writer,Press,Price,InlibraryTime,Total_Amount,Now_Amount) values ('1002','SQL Server','软件工作室','清华大学出版社',34.5,'1/2/2008',6,6)
insert into Book_Info(BookID,BookName,Writer,Press,Price,InlibraryTime,Total_Amount,Now_Amount) values ('1003','VC++','求是科技','人民邮电出版社',32.9,'1/3/2008',10,10)
insert into Book_Info(BookID,BookName,Writer,Press,Price,InlibraryTime,Total_Amount,Now_Amount) values ('1004','通信原理','樊昌信','国防工业出版社',46.0,'1/4/2008',8,8)
insert into Book_Info(BookID,BookName,Writer,Press,Price,InlibraryTime,Total_Amount,Now_Amount) values ('1005','飘','简爱','人民文学出版社',38.0,'1/5/2008',4,4)
insert into Book_Info(BookID,BookName,Writer,Press,Price,InlibraryTime,Total_Amount,Now_Amount) values ('1006','电磁场与微波','毕岗','浙江大学出版社',23.0,'1/6/2008',5,5)

select * from Book_Info

drop table Book_Info

create table Proof_Info
(
	ID		numeric(10)	primary key identity,
	BorrowID	varchar(20)	unique, 
	Stu_Name	varchar(20)	not null,
	Class		varchar(20)	not null,
	Sex		varchar(10)	not null,
	ID_Number	varchar(20)	not null,
	Tel_Number	varchar(20)	not null,
	Borrow_Amount	numeric(10)	not null
)

insert into Proof_Info (BorrowID,Stu_Name,Class,Sex,ID_Number,Tel_Number,Borrow_Amount) values('208060134','薛敏','光纤通信061','男','444444','13770563454',0)
insert into Proof_Info (BorrowID,Stu_Name,Class,Sex,ID_Number,Tel_Number,Borrow_Amount) values('208060135','袁海龙','光纤通信061','男','555555','13770563455',0)
insert into Proof_Info (BorrowID,Stu_Name,Class,Sex,ID_Number,Tel_Number,Borrow_Amount) values('208060136','袁祖华','光纤通信061','男','666666','13770563456',0)
insert into Proof_Info (BorrowID,Stu_Name,Class,Sex,ID_Number,Tel_Number,Borrow_Amount) values('208060137','张贵全','光纤通信061','男','777777','13770563457',0)
insert into Proof_Info (BorrowID,Stu_Name,Class,Sex,ID_Number,Tel_Number,Borrow_Amount) values('208060138','张筝','光纤通信061','男','888888','13770563458',0)

select * from Proof_Info

drop table Proof_Info

create table Borrow_Info
(	
	ID		numeric(10)	primary key identity,
	BorrowID	varchar(20)	not null,
	BookID		varchar(20)	not null,
	BorrowTime	datetime	not null,
	
)

insert into Borrow_Info(BorrowID,BookID,BorrowTime) values('208060134','1002','1/8/2008')
insert into Borrow_Info(BorrowID,BookID,BorrowTime) values('208060135','1001','2/8/2008')
insert into Borrow_Info(BorrowID,BookID,BorrowTime) values('208060135','1003','3/8/2008')
insert into Borrow_Info(BorrowID,BookID,BorrowTime) values('208060135','1004','4/8/2008')

select * from Borrow_Info

drop table Borrow_Info


select * from Book_Info where BookID in(select BookID from Borrow_Info where BorrowID in(select BorrowID from Proof_Info where BorrowID='208060134'))