图书馆

use master

if exists(select * from sysdatabases where name='library')

drop database library

create database Library

go

use Library

go

/*-----借书卡表--------*/

if exists(select *from sysobjects where name='card')

drop table card

create table card

(

 cNo int identity(1000,1), --卡号

 Name char(20) not null,   --姓名

 class char(20)            --班级

)

alter table card add constraint PK_CNO primary key(cNO) --主键

--select * from card

insert into card values ('jane','s')

insert into card values ('whisht','y')

insert into card values ('yehoo','z')

 

 

/*---------图书表---------*/

if exists (select *from sysobjects where name='books')

drop table books

create table books

(

 Bno int identity(1,1),    --书号

 Bname char(20) not null,  --书名

 Author char(20) not null, --作者

 Price money not null,     --单价

 Quantity int not null     --库存册数

)

alter table books add constraint PK_Bno primary key(Bno)       --主键

alter table books add constraint CK_Quantity check(Quantity>0) --库存册数必须大于

go

insert into books values('追忆似水年华','马塞尔.普鲁斯特',38,12)

insert into books values ('老人与海','海明威',50,20)

insert into books values ('雷雨','曹禺',45,8)

insert into books values ('复活','列夫.托尔斯泰',45,13)

insert into books values('堂吉诃德','塞万提斯',80,30)

insert into books values ('鲁滨逊漂流记','笛福',60,3)

insert into books values('生命中不能承受之轻','托马斯',50,1)

insert into books values('汤姆叔叔的小屋','斯陀夫人',75,10)

insert Books values('计算方法','Math',40,20)

insert Books values('计算方法习题集','Math',40,20)

insert into books values('小屋的主人','爱尔历',30,3)

insert into books values('组合数学','math',42,10)

 

select * from books

 

/*--------借出记录表--------*/

if exists(select* from sysobjects where name='borrow')

drop table borrow

create table borrow

(

 Cno int ,                --借书卡号

 Bno int,                 --书号

 Rdate datetime not null, --还书日期

)

alter table borrow add constraint FK_Cno foreign key(CNO) references card(cNO)

alter table borrow add constraint FK_Bno foreign key (BNo) references books(BNO)

 

insert borrow values(1000,2,'2008/9/12')

insert borrow values(1000,1,'2008/9/20')

insert borrow values(1001,12,'2008/3/5')

insert borrow values(1002,1,'2008/9/6')

insert borrow values(1001,8,'2008/9/10')

insert borrow values(1002,3,'2008/5/8')

insert borrow values(1000,9,'2008/9/12')

insert borrow values(1000,1,'2008/9/12')

insert borrow values (1002,9,'2008-5-06')

insert borrow values(1000,8,'2008-8-15')

insert borrow values(1000,1,'2008-7-26')

insert borrow values(1002,10,'2008-8-24')

insert borrow values(1001,9,'2008-8-8')

insert borrow values (1002,12,'2008-8-8')

--select cno ,bno from borrow where cno=1000

select * from borrow

 

 

 

--找出借书超过本书的读者，输出借书卡号及所借图书册数-------(group by ..having的用法)

select 卡号=cno,所借册数=count(*)from borrow group by cno having count(*)>5

 

--查询借阅了'老人与海'一书的读者，输出姓名及班级-------(三表查询的用法)

select 姓名=Name,班级=Class from card join borrow on card.CNo=borrow.CNo join Books on borrow.BNo=Books.BNo where BName='老人与海'

 

--查询过期未还图书，输出借阅者（卡号）、书号及还书日期----(日期用法)

select 卡号=cno,书号=bno,还书日期=Rdate from borrow where Rdate<getdate()

 

--查询书名包括"小屋"关键词的图书，输出书号、书名、作者------(模糊查询的用法)

select 书号=bno,书名=Bname,作者=Author from books where bname like '%小屋%'

 

--查询现有图书中价格最高的图书，输出书名及作者-----(聚合函数max的用法)

select 书名=bname,作者=author from books group by bname,author having max(price)=(select max(price)from books)

 

 

--选做题:查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

 

select 学号=cno from borrow inner join Books

on borrow.bno=Books.bno

where borrow.cno in(select cno from card where bname='计算方法')

and cno not in(select cno from borrow inner join Books on borrow.bno=Books.bno where cno in(select cno from card where bname='计算方法习题集'))

order by cno desc

 

 

select c.cno as 卡号from card as c

inner join borrow as bor on(c.cno=bor.cno)

inner join books as b on(bor.bno=b.bno)

where b.bname like '计算方法%'

group by c.cno

having count(c.cno)=1

order by c.cno desc

--@方法

select cno from borrow

where bno in(select bno from books where bname='计算方法')

or bno in(select bno from books where bname='计算方法集')

group by cno

having count(cno)<2

order by cno desc

 

 

 

 

--将"s"班同学所借图书的还期都延长一周

update borrow set Rdate=dateadd(wk,1,Rdate) where cno in (select cno from card where class='s')

--update borrow set Rdate=Rdate-7 (select Rdate  from borrow join card on borrow.cno=card.cno where class='s')--每操作一次加天

 

-- 从BOOKS表中删除当前无人借阅的图书记录

delete from  books where  bno not in(select bno from borrow where books.bno=borrow.bno )

 

delete from books where bno not in(select bno from borrow)

--如果经常按书名查询图书信息，请建立合适的索引

create nonclustered index IX_books_bno on books(bno) with fillfactor=30

--select * from books where bno=1

 

--查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。

select 卡号=cno from borrow where bno in(select bno from books where bname='计算方法' or  bname='组合数学') group by Cno having count(*)=2  order by CNo asc

 

--假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。

 

 

15．对CARD表做如下修改：

    a. 将NAME最大列宽增加到个字符（假定原为个字符）。

--alter table bb alter column score decimal(5,1)--更改已有字段的数据类型

  alter table card alter column name char(10)

 

    b. 为该表增加列NAME（系名），可变长，最大个字符。

  alter table card add h varchar(20) null ;--新增一列'hopy'

select * from card

 alter table card drop column h

11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。

 

12．建立一个视图，显示"z"班学生的借书信息（只要求显示姓名和书名）。

if exists (select * from sysobjects where name='view_card_books')

   drop view view_card_books

go

create view view_card_books as select 姓名=name,书名=bname from card join borrow on card.cno=borrow.cno join books on borrow.bno=books.bno where class='z'

go

select * from view_card_books

 

 

 

 

 

 

 

 

 

 

 

 

 

 

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。要求实现如下个处理：