图书馆数据库.sql

一个图书管管理软件

create database BookSys
go
create table Book
(
     BookID char(4) primary key,
     BookName varchar(10),
     BookType varchar(10),
     BookPrice Money,
     BookState char(4),
     provider char(40),
     time datetime
)
go
insert into Book values ('B101','三国演义','古典名著',80,'在库','fdsf',2005-9-7)
insert into Book values ('B102','射雕英雄传','武侠小说',90,'在库','dfsd',2006-8-6)
insert into Book values ('B103','VB程序设计','计算机类',40,'在库','dfs',2007-8-4)
insert into Book values ('B104','西游记','古典名著',120,'在库','fsd',2000-8-2)
insert into Book values ('B105','神雕侠侣','武侠小说',100,'在库','fsd',1998-9-5)
insert into Book values ('B106','SQL Server','计算机类',60,'在库','sdf',2003-9-4)
go


create table Reader
(
     ReaderID char(4) primary key,
     ReaderName varchar(10),
     ReaderAge int,
     ReaderCity varchar(10)
)
go

insert into Reader values ('R101','李小龙',30,'旺角')
insert into Reader values ('R102','成龙',20,'九龙')
insert into Reader values ('R103','吕小龙',24,'尖沙嘴')
insert into Reader values ('R104','梁小龙',26,'旺角')
insert into Reader values ('R105','李连杰',27,'九龙')
insert into Reader values ('R106','洪金宝',28,'尖沙嘴')
go


create table BookTran
(
     autoID int identity(1,1) primary key,
     ReaderID char(4),
     BookID char(4),
     BorrowDate datetime,
     ReturnDate datetime
)
go
insert into BookTran values ('R101','B101','2005-8-8','2005-9-6')
insert into BookTran values ('R101','B102','2005-8-10','2005-10-1')
insert into BookTran values ('R103','B103','2004-10-1','2004-12-12')
insert into BookTran values ('R104','B104','2005-1-1','2005-1-12')
insert into BookTran values ('R105','B103','2005-6-6','2005-7-6')
insert into BookTran values ('R106','B106','2005-7-7','2005-8-9')
go

create table yh
(
     userid char(20) primary key,
     pwd char(20),
)
go
insert into yh values ('admin','admin')
insert into yh values ('jiangbj','jiangbj')
insert into yh values ('jbj','jbj')
go
select *from BOOK
select *from reader
select *from booktran
select *from yh