图书数据库脚本

use master
go
--创建练习题的数据库
if exists(select * from  sysdatabases  where  name='Library' )
  DROP database  Library
go
create database Library
on primary
(
name='Library_data',
filename='D:\DB\Library_data.mdf',
size=10mb,
maxsize=100mb,
filegrowth=10%
)
log on
(
name='Library_log',
filename='D:\DB\Library_log.idf',
size=2mb,
filegrowth=10
)
go

/*******************创建读者分类表********************/
use Library
go
if exists(select * from  sysobjects  where  name='Readertype' )
  DROP  TABLE  Readertype
go
create table Readertype
(
Typeid int identity(1,1) not null,
Typename varchar(20) not null,
Booksum smallint not null,
Bookday smallint not null
)
go
--创建读者类主键
alter table Readertype
add constraint PK_rtype primary key(Typeid)
go

/*******************创建图书分类表********************/
use Library
go
if exists(select * from  sysobjects  where  name='Booktype' )
  DROP TABLE  Booktype
go
create table Booktype
(
Typeid int identity(1,1) not null,
Typename varchar(20) not null
)
go
--创建图书类主键
alter table Booktype
add constraint PK_btype primary key(Typeid)
go

/*******************创建读者表********************/
use Library
go
if exists(select * from  sysobjects  where  name='Reader' )
  DROP TABLE Reader
go
create table Reader
(
Readerid char(13) not null,
Readername varchar(20) not null,
Typeid int,
Birhday date,
Sex char(2) not null,
Address varchar(40),
Postalcode char(6),
Tel varchar(15),
Enrolldate date not null,
State varchar(10),
Memo varchar(200)
)
go
--创建读者主键
alter table Reader
add constraint PK_Reader primary key(Readerid)
go
--创建唯一约束
alter table Reader
add constraint UQ_Readerid unique (Readerid)
go
--创建默认值
alter table Reader
add constraint DF_Sex
default('男') for Sex
go
--添加check约束
alter table Reader
add constraint ck_Sex
check(Sex='男' or Sex='女')
--创建默认值
alter table Reader
add constraint DF_Enrolldate
default(getdate()) for Enrolldate
go
--主外键
alter table Reader
add constraint FK_Typeid
foreign key(Typeid) references Readertype(Typeid)
go

 
/*******************创建图书表********************/
use Library
go
if exists(select * from  sysobjects  where  name='Book' )
  DROP TABLE Book
go
 create table Book
 (
 Bookid varchar(20) not null,
 Booktitle varchar(40) not null,
 ISBN char(21),
 Typeid int,
 Author varchar(30),
 Press char(30),
 Pubdate date,
 Price smallmoney,
 Regdate date,
 State varchar(10),
 Memo varchar(200)
 )
 go
 --创建图书主键
alter table Book
add constraint PK_Bookid primary key(Bookid)
go
--主外键
alter table Book
add constraint FK_Teypid
foreign key(Typeid) references Booktype(Typeid)
go

/*******************创建借阅记录********************/
use Library
go
if exists(select * from  sysobjects  where  name='Record' )
   DROP TABLE Record
go
create table Record
(
Recordid int identity(1,1) not null,
Readerid char(13) not null,
Bookid varchar(20) not null,
Outdate date not null,
Indate date,
State varchar(10) not null
)
go
--创建借阅记录主键
alter table Record
add constraint PK_Record primary key(Recordid)
go
--主外键
alter table Record
add constraint FK_Readerid
foreign key(Readerid) references Reader(Readerid)
go
--主外键
alter table Record
add constraint FK_Bookid
foreign key(Bookid) references Book(Bookid)
go
 
 
/****************添加数据********************/
----向读者分类表Readertype中添加记录
use Library
go
insert into Readertype values ('普通',10,60)
insert into Readertype values ('VIP',20,90)
go
select * from Readertype

----向图书分类表Booktype中添加记录
insert into Booktype values ('文学')
insert into Booktype values ('生活')
insert into Booktype values ('教育')
insert into Booktype values ('经济')
insert into Booktype values ('技术')
go
select * from Booktype

----向读者表Reader中添加记录
insert into Reader values ('3872-3423-001','郭玉娇',1,'1988-3-4','女','江苏省南京市','210038','13802748383','2007-9-3','有效',null)
insert into Reader values ('3872-3423-002','张蓓蕾',1,'1988-2-25','女','湖北省武汉市','430042','13894749384','2007-9-3','有效',null)
insert into Reader values ('3872-3423-003','姜鑫锋',1,'1989-3-6','男','湖北省襄樊市','441054','13904030284','2007-9-3','有效',null)
insert into Reader values ('3872-3423-004','姜祝进',1,'1988-5-14','男','江苏省苏州市','215021',null,'2007-9-3','有效',null)
insert into Reader values ('3872-3423-005','李大春',1,'1988-2-7','男','江苏省常州市','213003',null,'2007-9-3','有效',null)
insert into Reader values ('3872-3423-006','陆杭轲',1,'1988-2-15','男','山东省济南市','250021','13905403050','2007-9-3','有效',null)
insert into Reader values ('3872-3423-007','许杰',2,'1989-4-15','男','浙江省杭州市','310020','13907040600','2008-9-1','有效',null)
insert into Reader values ('3872-3423-008','胡伟伟',2,'1989-2-5','男','浙江省杭州市','310020',null,'2008-9-1','有效',null)
insert into Reader values ('3872-3423-009','缪广林',1,'1989-2-27','男','安徽省黄山市','245900',null,'2008-9-10','有效',null)
insert into Reader values ('3872-3423-010','陈华明',1,'1989-8-12','男','吉林省长春市','130032','13865094948','2008-9-1','有效',null)
insert into Reader values ('3872-3423-011','宋金龙',1,'1989-10-4','男','湖南省长沙市','410013','13985839374','2008-9-1','有效',null)
insert into Reader values ('3872-3423-012','张赛峰',2,'1989-4-6','男','北京市','100081','13958493859','2008-9-1','有效',null)
insert into Reader values ('3872-3423-013','闻翠萍',1,'1988-5-12','女','江苏省南京市','210056',null,'2007-9-3','有效',null)
insert into Reader values ('3872-3423-014','李亮',1,'1988-6-26','男','江苏省连云港市','222062',null,'2007-9-3','有效',null)
insert into Reader values ('3872-3423-015','祁强',1,'1988-7-4','男','江苏省淮安市','223321','13892747201','2007-9-3','有效',null)
insert into Reader values ('3872-3423-016','黄晓琳',1,'1988-4-16','男','上海市','200061','13869305809','2007-9-3','有效',null)
insert into Reader values ('3872-3423-017','张芳',2,'1989-7-17','女','江苏省常州市','213003',null,'2007-9-3','有效',null)
insert into Reader values ('3872-3423-018','徐海东',1,'1988-1-7','男','安徽省合肥市','230022','138905039583','2007-9-3','有效',null)
insert into Reader values ('3872-3423-019','滕荣莉',2,'1988-4-7','女','安徽省马鞍山市','243061','13705020989','2008-9-1','有效',null)
insert into Reader values ('3872-3423-020','奚冬梅',1,'1989-3-8','女','浙江省杭州市','320032',null,'2008-9-2','有效',null)
insert into Reader values ('3872-3423-021','毕志成',1,'1989-7-25','男','江苏省无锡市','214107','13982620500','2008-9-1','无效',null)
insert into Reader values ('3872-3423-022','王甲寅',1,'1989-7-3','男','江苏省淮安市','223324','13984503988','2008-9-1','有效',null)
go
select * from Reader

----向图书表Book中添加记录
insert into Book values ('39845-23847-00193447','李开复自传','9787508616780',1,'李开复','中信出版社','2009-9-1','29.8','2009-10-1','借出',null)
insert into Book values ('90394-49345-83708295','好妈妈胜过好老师','9787506345040',3,'尹建莉','作家出版社','2009-1-4','19.6','2009-4-4','可借',null)
insert into Book values ('39305-84748-38547898','每天懂一点色彩心理学','9787561345467',2,'原田玲仁','陕西师范大学出版社','2009-6-16','32.0','2009-9-2','可借',null)
insert into Book values ('92654-82762-81673837','不抱怨的世界','9787561345948',4,'Will Bowen','陕西师范大学出版社','2009-4-6','24.0','2009-12-4','借出',null)
insert into Book values ('39587-95729-38347397','没有悲伤的城市','9787561347676',1,'Lrani Anosh','陕西师范大学出版社','2009-9-26','25.0','2009-12-4','借出',null)
insert into Book values ('87628-38473-43957397','杜拉拉升职记','97875613339121',1,'李可','陕西师范大学出版社','2008-1-5','26.0','2008-5-31','可借',null)
insert into Book values ('23879-48373-96725789','手到病除','9787214059413',2,'杨奕','江西人民出版社','2009-9-3','29.0','2009-12-4','可借',null)
insert into Book values ('95930-96629-57392589','丽江之恋','9787229008994',1,'点坑木','重庆出版社','2009-8-3','16.0','2009-12-7','借出',null)
insert into Book values ('02284-28571-28927481','老子十八讲','9787108033055',3,'王蒙','三联书店','2009-9-8','28.0','2009-12-25','可借',null)
insert into Book values ('38573-28475-92756258','代码揭秘','9787121093104',5,'左飞','电子工业出版社','2009-9-16','56.0','2009-10-5','借出',null)
insert into Book values ('82712-34859-27396826','程序员实用算法','9787111272960',5,'Andrew Binstock','机械工业出版社','2009-9-18','65.0','2009-12-6','可借',null)
go
select * from Book

----向借阅记录表Record中添加记录
insert into Record values ('3872-3423-001','39845-23847-00193447','2009-10-2',null,'借出')
insert into Record values ('3872-3423-002','92654-82762-81673837','2009-4-5','2009-5-5','已还')
insert into Record values ('3872-3423-003','92654-82762-81673837','2009-9-2','2009-11-2','已还')
insert into Record values ('3872-3423-004','92654-82762-81673837','2009-12-4',null,'借出')
insert into Record values ('3872-3423-005','39587-95729-38347397','2009-12-4','2009-12-4','已还')
insert into Record values ('3872-3423-006','87628-38473-43957397','2010-5-31','2010-7-31','已还')
insert into Record values ('3872-3423-007','39587-95729-38347397','2010-3-4','2010-12-4','已还')
insert into Record values ('3872-3423-008','95930-96629-57392589','2010-4-7',null,'借出')
insert into Record values ('3872-3423-009','39587-95729-38347397','2010-12-25',null,'借出')
insert into Record values ('3872-3423-010','38573-28475-92756258','2009-10-5',null,'借出')
insert into Record values ('3872-3423-011','82712-34859-27396826','2009-12-6','2010-3-6','已还')
go
select * from Record