周师宿舍图书管理

create database 图书管理

go

use 图书管理

-- 图书管理系统题库

-- 本题库所有题目都基于该处定义数据库：图书管理。该数据库中包括三个数据表，每个表的逻辑结构如下说明：

-- ①读者信息：

-- 借书证号 char（8） 非空，姓名 nvarchar（10）唯一，

-- 性别 nchar（1），出生日期 datetime，专业名称 nvarchar（20）。

-- ②图书信息：

-- 图书编号：char（8）非空，图书名称 nvarchar（20） 唯一，

-- 作者 nvarchar（10），出版社 nvarchar（20），

-- ISBN char（13），价格 decimal（5，2），

-- 复本数 tinyint，库存量 tinyint。

-- ③借阅信息：

-- 借书证号 参照“学生信息”表“借书证号”字段取值，

-- 图书编号 参照“图书信息”表“图书编号”字段取值，

-- 借书时间 datetime，

-- 还书时间 datetime。

-- 一、数据库表的创建

--1．请用SQL数据定义语句定义读者信息表。

create table 读者信息

(

借书证号 char(8) not null,

姓名 nvarchar(10) unique,

性别 nchar(1),

出生日期 datetime,

专业名称 nvarchar(20)

)

--2．请用SQL数据定义语句定义图书信息表。

create table 图书信息

(

图书编号 char(8) not null,

图书名称 nvarchar(20) unique,

作者 nvarchar(10),

出版社 nvarchar(20),

ISBN char(13),

价格 decimal(5,2),

复本数 tinyint,

库存量 tinyint

)

--3．请用SQL数据定义语句定义信阅信息表。

create table 借阅信息

(

借书证号 char(8) not null,

图书编号 char(8) not null,

借书时间 datetime,

还书时间 datetime

)

--二、表约束的创建

--1．为读者信息表声明主码：将借书证号字段设置为读者信息表的主键。

alter table 读者信息

add constraint pk_借书证号 primary key (借书证号)

--2．为借阅信息表声明主码：将（借书证号，图书编号）联合字段设置为借阅信息表的主键。

alter table 借阅信息

add constraint pk_借书证号_图书编号 primary key (借书证号,图书编号)

--3．为图书信息表声明主码：将图书编号字段设置为图书信息表的主键。

alter table 图书信息

add constraint pk_图书编号 primary key (图书编号)

--4．为借阅信息表声明外码：借书证号 参照“读者信息”表“借书证号”字段。

alter table 借阅信息

add constraint fk_借书证号 foreign key (借书证号) references 读者信息(借书证号)

--5．为借阅信息表声明外码：图书编号 参照“图书信息”表“图书编号”字段。

alter table 借阅信息

add constraint fk_图书编号 foreign key (图书编号) references 图书信息(图书编号)

--6．为图书信息表设置约束：图书的库存量小于等于图书的复本数量。

alter table 图书信息

add constraint ck_库存量 check (库存量 <= 复本数)

--7．为借阅信息表设置约束：图书的借书时间小于等于还书时间。

alter table 借阅信息

add constraint ck_借书时间 check (借书时间 <= 还书时间)

--8．为图书信息表设置约束：图书的价格大于0。

alter table 图书信息

add constraint ck_图书价格 check (价格>0)

--9．为读者信息表设置约束: 性别只能为“男”和“女”。

alter table 读者信息

add constraint ck_性别 check (性别 in ('男','女'))

--10．为读者信息表设置约束:出生日期介于1980-9-1~1995-9-1。

alter table 读者信息

add constraint ck_出生日期 check (出生日期 between '1980-9-1' and '1995-9-1')

--11．为读者信息表设置约束：将性别字段的默认值设置为“女”。

alter table 读者信息

add constraint dk_性别 default '女' for 性别

--12．为图书信息表设置约束：将出版社字段的默认值设置为“清华大学出版社”。

alter table 图书信息

add constraint dk_出版社 default '清华大学出版社' for 出版社

--13. 为图书信息表设置约束：将库存量字段的默认值设置为0。

alter table 图书信息

add constraint dk_库存量 default 0 for 库存量

--14. 为图书信息表设置约束：将复本数字段的默认值设置为0。

alter table 图书信息

add constraint dk_复本数 default 0 for 复本数

--三、数据查询和视图的创建

-- 为方便数据查询，现将本试题涉及的3个表的基本数据列示如下：

--表1：读者信息表

-- 借书证号 姓名 性别 出生日期 专业名称

-- XS000001 王琳琳 女 1990-03-14 政法系

-- XS000002 江军 男 1989-04-21 计算机系

-- JS000001 沈俊逸 男 1991-01-03 计算机系

-- JS000002 赵妞 女 1990-05-12 计算机系

insert into 读者信息 (借书证号,姓名,性别,出生日期,专业名称)

values ('XS000001','王琳琳','女','1990-03-14','政法系')

insert into 读者信息 (借书证号,姓名,性别,出生日期,专业名称)

values ('XS000002','江军','男','1989-04-21','计算机系')

insert into 读者信息 (借书证号,姓名,性别,出生日期,专业名称)

values ('JS000001','沈俊逸','男','1991-01-03','计算机系')

insert into 读者信息 (借书证号,姓名,性别,出生日期,专业名称)

values ('JS000002','赵妞','女','1990-05-12','计算机系')

--表2：图书信息表

-- 图书编号 图书名称 作者 出版社 ISBN价格 复本数 库存量

-- A08001 C#程序设计 谭浩强 清华大学出版社 7-111-06359-72810 7

-- A08002 大型数据库 李坤仑 电子工业出版社 7-113-04908-73720 19

-- A08003 电子商务 郑强 清华大学出版社 7-115-07715-02015 14

insert into 图书信息 (图书编号,图书名称,作者,出版社,ISBN,价格,复本数,库存量)

values ('A08001','C#程序设计','谭浩强','清华大学出版社','7-111-06359-7',28,10,7)

insert into 图书信息 (图书编号,图书名称,作者,出版社,ISBN,价格,复本数,库存量)

values ('A08002','大型数据库','李坤仑','电子工业出版社','7-113-04908-7',37,20,19)

insert into 图书信息 (图书编号,图书名称,作者,出版社,ISBN,价格,复本数,库存量)

values ('A08003','电子商务','郑强','清华大学出版社','7-115-07715-0',20,15,14)

--表3：借阅信息表

-- 借书证号图书编号 借书时间 还书时间

-- XS000001A08001 2009-04-13

-- XS000001A08002 2009-04-13

-- XS000001A08003 2009-04-13

-- JS000001A08001 2009-05-01

-- JS000002A08001 2009-04-23

insert into 借阅信息 (借书证号,图书编号,借书时间)

values ('XS000001','A08001','2009-04-13')

insert into 借阅信息 (借书证号,图书编号,借书时间)

values ('XS000001','A08002','2009-04-13')

insert into 借阅信息 (借书证号,图书编号,借书时间)

values ('XS000001','A08003','2009-04-13')

insert into 借阅信息 (借书证号,图书编号,借书时间)

values ('JS000001','A08001','2009-05-01')

insert into 借阅信息 (借书证号,图书编号,借书时间)

values ('JS000002','A08001','2009-04-23')

--1.列示读者信息表中前3条读者的基本信息。

select top 3 * from 读者信息

--2.列示读者信息表中读者的借书证号、姓名、出生日期字段的基本信息。

select 借书证号,姓名,出生日期 from 读者信息

--3.列示读者信息表中读者的姓名和年龄字段的基本信息。

select 姓名,datediff("yyyy",出生日期,getdate()) as 年龄

from 读者信息

--4.列示读者信息表中“计算机系”的女读者的基本信息。

select * from 读者信息

where 专业名称 = '计算机系' and 性别 = '女'

--5.列示读者信息表中姓“赵”的读者信息。

select *

from 读者信息

where left(姓名,1)='赵'

select *

from 读者信息

where 姓名 like '赵%'

--6.列示姓名中带“军”字的读者的所有信息。

select *

from 读者信息

where charindex('军',姓名) != 0

--7.列示清华大学出版社出版的图书的基本信息。

select *

from 图书信息

where 出版社 = '清华大学出版社'

--8.列示所有图书的信息，且按“图书编号”降序排列。

select *

from 图书信息

order by 图书编号 desc

--9.列示所有读者的借阅信息，且按“借书时间”升序排列。

select * from 借阅信息

order by 借书时间

--10 列示出借书证号为“XS000001”的读者所借阅图书的图书编号、图书名称、借书时间、还书时间等信息。

-- 并按照库存量降序排列

select 图书信息.图书编号,图书名称,借书时间,还书时间

from 图书信息 inner join 借阅信息 on 图书信息.图书编号 = 借阅信息.图书编号

where 借书证号 = 'XS000001'

order by 库存量 desc

--11 列示出借阅了图书编号为‘A08001’的读者的借书证号、读者姓名、性别、出生日期，

-- 并且按照‘借书证号’降序排列。

select 读者信息.借书证号,姓名,性别,出生日期

from 读者信息 inner join 借阅信息 on 读者信息.借书证号 = 借阅信息.借书证号

where 图书编号 = 'A08001'

order by 借书证号 desc

--12.列示所有的图书信息，并按库存量降序排列。

select * from 图书信息

order by 库存量 desc

--13.统计并列示每个读者的借书证号、姓名以及借阅书籍的数量。

select 读者信息.借书证号,姓名,count(*) as 图书借阅数量

from 读者信息 inner join 借阅信息 on 读者信息.借书证号 = 借阅信息.借书证号

group by 读者信息.借书证号,姓名

--14.统计并列示每个专业的总人数及专业名称。

select 专业名称,count(*) as 总人数

from 读者信息

group by 专业名称

--15.统计并列示每本图书的图书编号、图书名称以及借阅了该图书的人数。

select 图书信息.图书编号,图书名称,count(*) as 借阅人数

from 图书信息 inner join 借阅信息 on 图书信息.图书编号 = 借阅信息.图书编号

group by 图书信息.图书编号,图书名称

--16.列示读者信息表中不在1990年出生的读者的借书证号，姓名，性别，出生日期。

select 借书证号,姓名,性别,出生日期

from 读者信息

where 出生日期 not between '1990-1-1' and '1990-12-31'

--17.列示图书信息表中价格在20-30之间且库存量不在10-20之间的图书书名，作者，出版社,库存量.

select 图书名称,作者,出版社,库存量

from 图书信息

where 价格 between 20 and 30 and 库存量 not between 10 and 20

--18.列示图书信息表中价格最高的前两本书的图书书名，出版社，价格。

select top 2 图书名称,出版社,价格 from 图书信息 order by 价格 desc

--19.列示图书信息表中“清华大学出版社”的图书名称、价格、库存量，按价格进行降序排列，价格相等的按库存量升序排列。

select 图书名称,价格,库存量 from 图书信息 where 出版社='清华大学出版社' order by 价格 desc,库存量 asc

--20.查询图书信息表中“清华大学出版社”图书的平均价格，将标题改为“平均价格”。

select avg(价格) as '平均价格' from 图书信息 where 出版社='清华大学出版社'

--21.列示图书信息表中图书总册数和库存总册数。

select sum(复本数) as '图书总册数',sum(库存量) as '库存总册数' from 图书信息

--22.列示图书信息表中图书价格最高的图书名称和价格，和图书价格最低的图书名称和价格。

select 图书名称,价格 from 图书信息 where 价格=(select max(价格) from 图书信息)

union

select 图书名称,价格 from 图书信息 where 价格=(select min(价格) from 图书信息)

--23.统计读者信息表中读者的总数。

select count(借书证号) from 读者信息

--24.统计借阅信息表中借阅了图书的学生数。

select count(distinct 借书证号) as '借书的人数' from 借阅信息

--25.统计借阅信息表中被借阅图书的图书编号和借阅该图书的人数。

select 图书编号,count(借书证号) from 借阅信息 group by 图书编号

--26.列示图书信息表中每个出版社图书的平均价格，总复本数，并按总复本数升序显示平均价格小于50的出版社、平均价格、总复本数。

select 出版社 ,avg(价格) as '平均价格',sum(复本数) as '总复本数'

from 图书信息

group by 出版社

having avg(价格)<50

--27.列示出读者信息表中与“江军”在同一个专业的读者的借书证号，姓名，性别，出生日期。

select 借书证号,姓名,性别,出生日期

from 读者信息

where 专业名称 in(select 专业名称 from 读者信息 where 姓名='江军')

--28.列示出年龄大于所有学生平均年龄的读者信息。

select * from 读者信息 where datediff("yyyy",出生日期,getdate())

>(select avg(datediff("yyyy",出生日期,getdate())) from 读者信息)

--29.创建图书借阅信息的视图，其视图名为：TSQuery

-- 视图功能：列示借书证号、姓名、图书编号、图书名称、借书时间的信息。

create view TSQuery

as

SELECT dbo.读者信息.借书证号, dbo.读者信息.姓名, dbo.图书信息.图书编号,

dbo.图书信息.图书名称, dbo.借阅信息.借书时间

FROM dbo.读者信息 INNER JOIN

dbo.借阅信息 ON dbo.读者信息.借书证号 = dbo.借阅信息.借书证号 INNER JOIN

dbo.图书信息 ON dbo.借阅信息.图书编号 = dbo.图书信息.图书编号

--30.创建图书借阅信息的视图，其视图名为：TJQuery

-- 视图功能：列示借书证号、图书编号、图书名称、出版社、价格的信息。

create view TJQuery

as

SELECT dbo.借阅信息.借书证号, dbo.借阅信息.图书编号, dbo.图书信息.图书名称,

dbo.图书信息.出版社, dbo.图书信息.价格

FROM dbo.借阅信息 INNER JOIN

dbo.图书信息 ON dbo.借阅信息.图书编号 = dbo.图书信息.图书编号

--四、数据更新

--1.为借阅信息表添加一条数据，其具体信息如下：

-- 借书证号：XS000002，图书编号：A08002，借书时间：系统当前时间。

insert into 借阅信息 (借书证号,图书编号,借书时间)

values ('XS000002','A08002',getdate())

--2.为读者信息表添加一条数据，其具体信息如下：

-- 借书证号：XS000003，姓名:李白，性别:男，出生日期:1989-1-1 专业名称：政法系

insert into 读者信息 (借书证号,姓名,性别,出生日期,专业名称)

values ('XS000003','李白','男','1989-1-1','政法系')

--3.将图书编号为A08003的图书的复本数量更新为30本。

update 图书信息

set 复本数 =30

where 图书编号 = 'A08003'

--4.将借阅了“大型数据库”图书的学生的还书时间改为当前系统时间。

update 借阅信息

set 还书时间='2009-6-1'

where 图书编号 = (select 图书编号 from 图书信息 where 图书名称='大型数据库')

--5.将“电子商务”书的复本数更新为20.

update 图书信息

set 复本数 = 20

where 图书名称 = '电子商务'

--6.删除学生信息表中男生的记录信息。

delete from 读者信息

where 性别 = '男'

--7.删除借阅信息表中还书时间晚于2009-5-1日的记录信息。

delete from 借阅信息

where 还书时间 >'2009-05-01'

--8.删除借阅信息表中借阅了“大型数据库”书的记录信息。

delete from 借阅信息

where 图书编号 = (select 图书编号 from 图书信息 where 图书名称 = '大型数据库')

--五、存储过程

--1．创建存储过程。

-- 功能：根据借书证号查询该证号所代表读者借阅的图书信息。

-- 存储过程名：ReaderBookQuery

-- 输入参数：借书证号

-- 返回查询结果集包括：图书编号、图书名称、ISBN、出版社、借书时间

create proc ReaderBookQuery

(

@借书证号 varchar(100)

)

as

select 图书信息.图书编号,图书名称,ISBN,出版社,借书时间

from 图书信息 inner join 借阅信息 on 图书信息.图书编号 = 借阅信息.图书编号

where 借书证号 = @借书证号

--2．创建存储过程。

-- 功能：根据借书证号查询该读者的基本信息。

-- 存储过程名：ReaderInfoQuery

-- 输入参数：借书证号

-- 返回查询结果集包括：姓名、性别、出生日期、专业名称

create proc ReaderInfoQuery

(

@借书证号 varchar(100)

)

as

select 姓名,性别,出生日期,专业名称 from 读者信息 where 借书证号 = @借书证号

--3．创建存储过程

-- 功能：根据图书编号，查看该编号代表图书的基本情况，

-- 存储过程名：BookInfoQuery

-- 输入参数：图书编号

-- 返回结果集包括：图书名称、作者、出版社、ISBN、价格、复本数、库存量。

create proc BookInfoQuery

(

@图书编号 varchar(100)

)

as

select 图书名称,作者,出版社,ISBN,价格,复本数,库存量 from 图书信息

where 图书编号 = @图书编号

--4．创建存储过程。

-- 功能：根据图书编号查询借阅该图书的读者信息。

-- 存储过程名：BookQuery

-- 输入参数：图书编号

-- 返回查询结果集包括：借书证号、姓名、性别、专业名称。

create proc BookQuery

(

@图书编号 varchar(100)

)

as

select 读者信息.借书证号,姓名,性别,专业名称

from 读者信息 inner join 借阅信息 on 读者信息.借书证号 = 借阅信息.借书证号

where 图书编号 = @图书编号

--5．创建存储过程。

-- 功能：根据图书出版社查询该出版社价格最高的图书名称。

-- 存储过程名：BookPubQuery

-- 输入参数：图书出版社

-- 返回查询结果集包括：图书名称。

create proc BookPubQuery

(

@pub nvarchar(20)

)

as

select 图书名称 from 图书信息

where 价格 =(select max(价格) from 图书信息 where 出版社=@pub)

--6.创建存储过程

--功能：根据图书编号查询图书借书情况。

-- 如果复本数-库存量<=2，则显示“该图书库存不多，请继续进书”，返回1；

-- 如果2<复本数-库存量<=6，则显示“该图书库存良好”，返回2；

-- 如果复本数-库存量>6,则显示“该图书库存量充裕”,返回3.

--存储过程名:BookJYQuery

--输入参数：图书编号

create proc BOOKJYQuery

(

@图书编号 varchar(100)

)

as

if(select 复本数-库存量 from 图书信息 where 图书编号=@图书编号)<=2

begin

print '该图书库存不多，请继续进书'

return 1

end

else

if (select 复本数-库存量 from 图书信息 where 图书编号=@图书编号)<=6

begin

print '该图书库存良好'

return 2

end

else

begin

print '该图书库存量充裕'

return 3

end

--六、触发器的创建

--1．创建触发器

-- 功能：当向借阅信息表中添加借书信息时，自动将图书信息表中相应的库存量减少1。

-- 触发器名字：BOOKTrigger

create trigger BOOKTrigger

on 借阅信息

after insert

as

update 图书信息 set 库存量 = 库存量 - 1 where 图书编号 = (select 图书编号 from inserted)

--2．创建触发器

-- 功能：当从借阅信息表中删除借书信息时，自动将图书信息表中相应的库存量增加1。

-- 触发器名字：BookDeleteTrigger

create trigger BookDeleteTrigger

on 借阅信息

after delete

as

update 图书信息 set 库存量 = 库存量 + 1 where 图书编号 = (select 图书编号 from deleted)

--3．创建触发器

-- 功能：当向借阅信息表插入数据时，如果图书信息表中的库存量等于0，则提示信息“库存量为0,不能插入”。

-- 触发器名字：JYinsert

create trigger JYinsert

on 借阅信息

for insert

as

if (select 库存量 from 图书信息) = 0

begin

raiserror('库存量为0,不能插入',16,1)

rollback transaction

end

--4．创建触发器

-- 功能：当向读者信息表插入数据时，如果出现姓名重复的记录，则提示信息“姓名重复，不得插入”

-- 触发器名字：DZinsert

create trigger DZinsert

on 读者信息

after insert

as

if exists(select 姓名 from 读者信息 where 姓名=(select 姓名 from inserted))

begin

raiserror('姓名重复，不得插入',16,1)

rollback

end

--5．创建触发器

-- 功能：当向读者信息表删除数据一条记录时，借阅信息表中的学生记录也删除。

-- 触发器名字：DZdelete

create trigger DZdelete

on 读者信息

after delete

as

delete from 借阅信息 where 借书证号 in (select 借书证号 from deleted)

1、自助续借：

读者登录：打开图书馆网站，点击首页上的“馆藏检索”图标进入图书馆opac查询系统，在opac系统中点击“我的图书馆”，然后输入读者证号和密码，或者在opac系统首页直接输入读者证号和密码（注：读者证号即为校园卡号，也就是自己的学工号。但如果您使用的是图书馆办理的条形码借书证，则读者证号为其数字部分。系统默认设置的读者密码与读者证号相同，因此读者在首次成功登录后应尽快修改密码，修改方法参邮件通知服务中的“自助修改读者密码”。），点击登录后进入“图书馆读者登录”页面；

在“图书馆读者登录”页面，点击“借还查询”显示本人的借阅信息，需要续借哪本书，点击借阅信息列表中该书后的“续借” 。每本书只能续借一次，借期30天，凡超期的图书不能续借。

2、自助预约：

读者登录：方法同续借；

登录以后点击“馆藏检索”，在馆藏检索页面中按您的需求进行检索；

在查询的结果中选择您需要的图书，然后点击该种图书后的“预约”。

进行预约，其它情况则不能预约。

注：

预约图书后请经常进行预约查询 *** 作，关注预约图书是否已到，如果在opac系统中注册了邮箱，系统会自动发送预约到书通知（注册方法见邮件通知服务）；

预约图书到馆后保留5天，5天之后预约失效，请及时到文典阁一楼东大厅总服务台办理预约图书借阅手续；

只有分配地址在“文典阁中文社会科学图书借阅室”和“文典阁中文自然科学图书借阅室”的图书才能预约，取预约书也仅限在磬苑校区文典阁总服务台。

3、自助取消预约：

读者登录，方法同续借；

在“图书馆读者登录”页面点击“预约查询”，在查询结果中选择需要取消预约的图书，点击该图书后的“取消预约”。

---