数据库基本语言操作实验

对以上数据表，完成以下操作：

1. 查询每本图书的所有信息；
2. 查询每个读者的读者编号、姓名和班级；
3. 查询每条借阅记录的借阅天数（函数 DATEDIFF 获取两个日期的差）；
4. 查询被借阅过的图书的图书编号；
5. 查询图书编号为“10006”的书名和作者；
6. 查询库存数在 5 到 10 本之间的图书的图书编号和书名；
7. 查询计算机系或电子系姓张的读者信息；
8. 查询书名包括“英语”的图书信息；

1. 统计男读者、女读者的人数；
2. 统计各类图书的类别编号、平均定价以及库存总数；
3. 统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；
4. 查询有库存的各类别图书的类别编号、类别名称和借阅数量；
5. 查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；
6. 查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）
7. 查询现有图书中价格最高的图书，输出书名、作者、定价；
8. 查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部；
9. 统计借阅了 2 本以上图书的读者信息；
10. 查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
11. 查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
12. 查询计算机系中比其他系所有读者借书数量都多的读者的信息；

1. 在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；
2. 定义一个表 tb_bknew，包含图书编号、书名和类别名称字段，要求将类别编号为“3” 的图书的图书编号、书名和类别名称插入到 tb_bknew 表中；
3. 将类别编号为“3”的所有图书的库存数增加 5；
4. 将“C++程序设计”这本书的归还日期增加一个月（函数DATEADD）。
5. 删除姓名为“张三”的读者的信息；
6. 删除 tb_bknew 表中的所有数据；
7. 创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息 ，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息；
8. 创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；
9. 查询借阅次数大于 2 的图书的图书编号、书名、库存数和借阅次数；
10. 删除“图示借阅信息_VIEW”视图。

use master
go
create database db_Library
on primary
(
name=db_library_data,
filename="D:\xx\db_library_data.mdf",
size=10MB,
maxsize=200MB,
filegrowth=10%
)
log on
(
name=db_library_log,
filename="D:\xx\db_library_data.ldf",
size=5MB,
maxsize=200MB,
filegrowth=2MB
)
go
use db_library
go
create schema L_C
go
create table L_C.tb_course
(
课程编号 int,
课程名 varchar(20),
先修课 varchar(20),
学分 int,
)
go
create table dbo.tb_booktype
(
类别编号 int,
类别名称 varchar(20),
)
go
 create table dbo.tb_book
(
图书编号 int,
类别编号 varchar(20),
书名 varchar(20),
作者 varchar(20),
出版社 varchar(20),
定价 float,
库存数 int,
)
go 
create table dbo.tb_reader
(
读者编号 varchar(20),
姓名 varchar(20),
性别 varchar(20),
学号 int,
班级 varchar(20),
系部 varchar(20),
)
go 
create table dbo.tb_borrow
(
图书编号 varchar(20),
读者编号 varchar(20),
借阅日期 date,
归还日期 date,
)
go
alter table dbo.tb_reader drop column 系部
alter table  dbo.tb_reader  add 系部 varchar(20)
alter table dbo.tb_book alter column 定价 real
drop table L_C.tb_course
go
create clustered index IX_S_QUANTITY on dbo.tb_book(库存数 desc)
create unique index IX_S_NAME on dbo.tb_reader(姓名 asc)
drop index dbo.tb_book.IX_S_QUANTITY
drop index dbo.tb_reader.IX_S_NAME
go
insert into dbo.tb_booktype values(1,'数学')
insert into dbo.tb_booktype values(2,'英语')
insert into dbo.tb_booktype values(3,'计算机')
insert into dbo.tb_booktype values(4,'文学')
insert into dbo.tb_booktype values(5,'艺术')
insert into dbo.tb_booktype values(6,'电子信息')
insert into dbo.tb_booktype values(7,'建筑')
insert into dbo.tb_booktype values(8,'化学')
insert into dbo.tb_booktype values(9,'物理')
go
insert into dbo.tb_book values(10001,3,'数据库管理','王珊','高等教育出版社',35.50,10)
insert into dbo.tb_book values(10002,3,'软件测试','贺平','机械工业出版社',24.60,5)
insert into dbo.tb_book values(10003,3,'c++程序设计','谭浩强','清华大学出版社',30.00,8)
insert into dbo.tb_book values(10004,4,'红楼梦','曹雪芹','人民文学出版社',70.00,5)
insert into dbo.tb_book values(10005,4,'西游记','罗贯中','人民文学出版社',60.00,8)
insert into dbo.tb_book values(10006,4,'红与黑','司汤达','人民文学出版社',50.00,5)
insert into dbo.tb_book values(10007,1,'高等数学','李翼','清华大学出版社',28.00,4)
insert into dbo.tb_book values(10008,8,'有机化学','张翔','高等教育出版社',29.00,5)
insert into dbo.tb_book values(10009,2,'大学英语','王琳','高等教育出版社',25.00,10)
insert into dbo.tb_book values(10010,2,'英语教程','王琳','高等教育出版社',25.00,5)
go
insert into dbo.tb_reader values('R10001','张小航','男',1351101,13511,'计算机系')
insert into dbo.tb_reader values('R10002','王文广','女',1351102,13511,'计算机系')
insert into dbo.tb_reader values('R10003','李强','女',1351103,13511,'计算机系')
insert into dbo.tb_reader values('R10004','李彦宏','男',1351101,13512,'计算机系')
insert into dbo.tb_reader values('R10005','张丽霞','女',1351102,13512,'计算机系')
insert into dbo.tb_reader values('R10006','王强','男',1351104,12211,'电子系')
insert into dbo.tb_reader values('R10007','张宝田','男',1351104,12212,'电子系')
insert into dbo.tb_reader values('R10008','宋文霞','女',1351104,12611,'建工系')
insert into dbo.tb_reader values('R10009','刘芳菲','女',1351104,13811,'外语系')
insert into dbo.tb_reader values('R10010','常江宁','男',1351104,13812,'外语系')
go 
insert into dbo.tb_borrow values(10002,'R10003','2014-09-20','2014-10-20')
insert into dbo.tb_borrow values(10003,'R10003','2014-09-20','2014-10-20')
insert into dbo.tb_borrow values(10004,'R10003','2014-09-30','2014-10-30')
insert into dbo.tb_borrow values(10009,'R10003','2014-09-30','2014-10-30')
insert into dbo.tb_borrow values(10009,'R10007','2014-05-20','2014-06-20')
insert into dbo.tb_borrow values(10010,'R10007','2014-05-20','2014-06-20')
insert into dbo.tb_borrow values(10009,'R10009','2014-05-30','2014-06-30')
insert into dbo.tb_borrow values(10010,'R10009','2014-05-22','2014-06-22')
insert into dbo.tb_borrow values(10002,'R10009','2014-05-22','2014-06-22')
insert into dbo.tb_borrow values(10003,'R10009','2014-05-30','2014-06-30')
go

（1）查询每本图书的所有信息
select * from dbo.tb_book

（2）查询每个读者的读者编号、姓名和班级
select 读者编号,姓名,班级
from 
dbo.tb_reader

（3）查询每条借阅记录的借阅天数（函数 DATEDIFF 获取两个日期的差）；
select datediff(day,借阅日期,归还日期)as 借阅天数
from 
dbo.tb_borrow

（4）查询被借阅过的图书的图书编号；
select 图书编号 
from 
dbo.tb_borrow

（5）查询图书编号为“10006”的书名和作者；
select 书名,作者
from
dbo.tb_book
where 图书编号=10006

（6）查询库存数在 5 到 10 本之间的图书的图书编号和书名；
select 图书编号,书名
from dbo.tb_book
where 库存数 between 5 and 10

（7）查询计算机系或电子系姓张的读者信息；
select * 
from dbo.tb_reader
where 姓名 like '张%'

（8）查询书名包括“英语”的图书信息；
select *
from dbo.tb_book
where 书名 like '%英语%'

（9）统计男读者、女读者的人数；
select 性别,count(性别) as 人数
from dbo.tb_reader
group by 性别

10）统计各类图书的类别编号、平均定价以及库存总数；
select 类别编号,avg(定价)as 平均定价,sum(库存数)
from dbo.tb_book
group by 类别编号

（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；
select 图书编号,count(读者编号)as 借阅人数
from dbo.tb_borrow
group by 图书编号
order by 借阅人数 desc

（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；
select dbo.tb_book.类别编号,dbo.tb_booktype.类别名称,count(*)as 借阅数量
from dbo.tb_booktype,dbo.tb_borrow,dbo.tb_book
where dbo.tb_booktype.类别编号=dbo.tb_book.类别编号 and dbo.tb_book.图书编号=dbo.tb_borrow.图书编号
group by dbo.tb_book.类别编号,dbo.tb_booktype.类别名称

（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；
select 姓名,性别,系部
from dbo.tb_reader
where 读者编号 in(select 读者编号 from dbo.tb_borrow where 图书编号 in(select 图书编号 from dbo.tb_book where 书名='大学英语'))

（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）
select dbo.tb_reader.读者编号, 姓名,dbo.tb_borrow.图书编号,dbo.tb_borrow.借阅日期
from dbo.tb_reader left outer join dbo.tb_borrow on(dbo.tb_reader.读者编号=dbo.tb_borrow.读者编号)

（15）查询现有图书中价格最高的图书，输出书名、作者、定价；
select 书名,作者,定价
from dbo.tb_book
where 定价 >=all (select 定价 from dbo.tb_book)

select 书名,作者,定价
from dbo.tb_book
where 定价 =(select max(定价) from dbo.tb_book)

（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、
系部；
select 姓名,性别,系部
from dbo.tb_reader
where 读者编号 in (select 读者编号 from dbo.tb_borrow where 图书编号 in (select 图书编号 from dbo.tb_book where 书名='大学英语')) 
and 读者编号 not in(select 读者编号 from dbo.tb_borrow where 图书编号 in (select 图书编号 from dbo.tb_book where 书名='c++程序设计'))

（17）统计借阅了 2 本以上图书的读者信息；
select *from
dbo.tb_reader
where dbo.tb_reader.读者编号 in(select 读者编号 from dbo.tb_borrow group by 读者编号 having count(*)>2)

（18）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查
询完成）找到读者编号就能查询
select 
*from dbo.tb_reader
where 读者编号 in(select 读者编号 from dbo.tb_borrow where 图书编号 in(select 图书编号 from dbo.tb_book where 书名='大学英语'))
union 
select 
*from dbo.tb_reader
where 读者编号 in(select 读者编号 from dbo.tb_borrow where 图书编号 in(select 图书编号 from dbo.tb_book where 书名='c++程序设计'))

（19）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息；（用集合查
询完成）找到读者编号就能查询
select *from
dbo.tb_reader
where 读者编号 in(select 读者编号 from dbo.tb_borrow where 图书编号 in(select 图书编号 from dbo.tb_book where 书名='大学英语'))
intersect
select *from
dbo.tb_reader
where 读者编号 in(select 读者编号 from dbo.tb_borrow where 图书编号 in (select 图书编号 from dbo.tb_book where 书名='c++程序设计'))

（20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；
select *from
dbo.tb_reader 
where 系部='计算机系'and 读者编号 in(select 
dbo.tb_reader.读者编号 from dbo.tb_reader,dbo.tb_borrow where dbo.tb_reader.读者编号=dbo.tb_borrow.读者编号 
group by dbo.tb_reader.读者编号 having count(图书编号)>any(select count(图书编号)from
dbo.tb_reader,dbo.tb_borrow where dbo.tb_reader.读者编号=dbo.tb_borrow.读者编号 and 系部<>'计算机系'group by dbo.tb_reader.读者编号))

（21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；
insert into 
dbo.tb_reader(读者编号,姓名,系部)
values('R10011','张三','电子系')

（22）定义一个表 tb_bknew，包含图书编号、书名和类别名称字段，要求将类别编号为“3” 的图书的图书编号、书名和类别名称插入到 tb_bknew 表中；
create table tb_bnew
(
图书编号 int,
书名 varchar(20),
类别名称 varchar(20)
)
insert into tb_bnew
select dbo.tb_book.图书编号,dbo.tb_book.书名,dbo.tb_booktype.类别名称
from dbo.tb_booktype,dbo.tb_book
where dbo.tb_book.类别编号=3 and dbo.tb_book.类别编号=dbo.tb_booktype.类别编号

（23）将类别编号为“3”的所有图书的库存数增加 5； 
update dbo.tb_book
set 库存数=库存数+5
where 类别编号=3

（24）将“C++程序设计”这本书的归还日期增加一个月（函数 DATEADD）。
update dbo.tb_borrow
set 归还日期=DATEADD(month,1,归还日期)
where 图书编号 in(select 图书编号 from dbo.tb_book where 书名='c++程序设计')

（25）删除姓名为“张三”的读者的信息；
delete from
dbo.tb_reader
where 姓名='张三'

（26）删除 tb_bknew 表中的所有数据；
delete from tb_bnew

（27）创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，
包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算
机系的读者借阅信息；

go
create view 读者借阅信息_view(读者编号,姓名,系部,图书编号,书名,借阅日期)
as
select  dbo.tb_reader.读者编号,姓名,系部,dbo.tb_book.图书编号,dbo.tb_book.书名,借阅日期
from dbo.tb_reader,dbo.tb_borrow,dbo.tb_book
where dbo.tb_book.图书编号=dbo.tb_borrow.图书编号 and dbo.tb_reader.读者编号=dbo.tb_borrow.读者编号 and 系部='计算机系'
with check option
go

（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编
号、书名、库存数、借阅次数字段； 

go
create view 图书借阅信息view(图书编号,书名,库存数,借阅次数)
as
select dbo.tb_book.图书编号,书名,库存数,count(*)
from dbo.tb_book,dbo.tb_borrow
where dbo.tb_book.图书编号=dbo.tb_borrow.图书编号
group by dbo.tb_book.图书编号,书名,库存数
go

（29）查询借阅次数大于 2 的图书的图书编号、书名、库存数和借阅次数；
select *from
图书借阅信息view
where 借阅次数>2

（30）删除“图示借阅信息_VIEW”视图
drop view _view