数据库实训sqlserver

一、数据库设计题

1、以“library”为名称创建一个数据库。该数据库中包含一个主数据文件tsdata.mdf，存放路径为“d:\data\”；一个事务日志文件tslog.ldf，存放路径为“d:\data\”。其他设置自定。

2、在上题创建好的数据库中，按如下要求创建三张表。

表1 书籍表：用来存储书籍的基本信息

字段名称

数据类型

长度

是否为空

说明

序号

int

非空

初始值和增量均为1

图书编号

char

10

非空

主键

书名

varchar

50

非空

作者

varchar

20

非空

价格

Money

空

出版社

varchar

50

非空

出版日期

smalldatetime

空

库存量

int

非空

=0

表2读者表：用来存储读者的基本信息

字段名称

数据类型

长度

是否为空

约束

借书证号

char

10

非空

主键

姓名

varchar

20

非空

性别

char

2

非空

默认值为“男”

单位

varchar

50

空

联系电话

char

11

空

表3 借阅表：存储读者借阅的信息

字段名称

数据类型

长度

是否为空

约束

图书编号

char

10

非空

外键，参照书籍表

借书证号

char

10

非空

外键，参照读者表

借书日期

smalldatetime

非空

还书日期

smalldatetime

空

归还否

char

2

非空

3、在“library”数据库中插入以下记录。

（1）在书籍表中插入以下数据：

图书编号

书名

作者

价格

出版社

出版日期

库存量

J1

计算机基础

刘大石

29

机械工业出版社

2014/2/1

5

J2

数据库应用教程

李刚

32

电子工业出版社

2014/9/1

8

（2）在读者表中插入以下数据：

借书证号

姓名

性别

单位

联系电话

10001

柯思扬

男

信息系

13837482123

10002

孙一明

男

管理系

13978621278

（3）在借阅表中插入以下数据：

图书编号

借书证号

借书日期

还书日期

归还否

J1

10001

2015/6/3

2015/12/3

否

J2

10001

2015/6/3

2015/12/3

否

4、为读者表创建一个“姓名”列的非聚集索引文件。

5、创建“读者借阅信息”视图，包括借书证号、姓名、书名、还书日期等信息。

二、查询设计题（每小题5分，共25分）

1、在library数据库中查询“孙一明”的相关信息。

请粘贴T-SQL查询语句：

select*from读者表where姓名='孙一明';

2、查询信息系或电子系的读者信息。

请粘贴T-SQL查询语句：

select*from读者表where单位='信息系'or单位='电子系';

3、查找书名以“计算机”打头的所有图书和作者。

请粘贴T-SQL查询语句：

select*from书籍表where书名like'计算机%';

4、查找姓名为“柯思扬”借阅书本的书名。

请粘贴T-SQL查询语句：

select书名from书籍表where图书编号in

(select图书编号from借阅表where借书证号=

(select借书证号from读者表where姓名='柯思扬'))

5、查询借书证号为“10001”所借书本的本数，显示借书证号和借书本数，并按借书证号升序排序。（4分）

请粘贴T-SQL查询语句：

select b.借书证号,COUNT(*)借书本数from借阅表b,书籍表a

wherea.图书编号=b.图书编号andb.借书证号='10001'groupbyb.借书证号orderbyb.借书证号;

三、填空题（每空2分，共10分）

1、读者还书存储过程：ReturnBook的创建，若读者没有借阅此书，则显示‘对不起，你没有借阅此书，故而无法进行此次还书操作，请核实！’信息。

use Library

go

create procedure ReturnBook

@no char(10),@bid char(10)

as

if not exists(select * from 借阅表where 借书证号=@no and 图书编号=@bid___________________________________________________)

begin

   print'对不起，你没有借阅此书，故而无法进行此次还书操作，请核实！'

end

2、在借阅表中创建一个触发器：tri_Book，若要借的书已无库存，则无法进行借书操作，即无法在‘借阅表’中插入记录。

create trigger_ tri_Book ________

on 借阅表__

for insert

as

declare @btotal varchar(10),@bborrowed varchar(10)

select @bborrowed=图书编号 from inserted

select @btotal=库存量 from 书籍表 where 图书编号=@bborrowed

if(@btotao=0________)

begin

rollback transaction

print '借阅失败！'

print'对不起，此书已经没有库存，无法进行本次借书操作！'

end

go

四、程序题（共15****分）

1、读者还书存储过程：ReturnBook_1的创建，1.成功还书时将归还否字段的‘否’改成‘是’，还书日期为当前时间，3.显示“成功地向图书馆归还！”。

createprocedureReturnBook_1

@novarchar(10),@bidvarchar(30)

as

ifexists(select* from借阅表where借书证号=@noand图书编号=@bidand归还否='否')

begin

update借阅表set归还否='是',借书日期=GETDATE()

where借书证号=@noand图书编号=@bid

select'成功地向图书馆归还!'

end

go

2、用借书证号和图书编号为“10001”和“j1” 来验证存储过程。

execReturnBook_1'10001','J1'

select*from借阅表;

-- 5、查询借书证号为“10001”所借书本的本数，
-- 显示借书证号和借书本数，并按借书证号升序排序。

select b.借书证号,COUNT(*) 借书本数 from 借阅表 b,书籍表 a
where a.图书编号=b.图书编号 and b.借书证号='10001' group by b.借书证号 order by b.借书证号;

select * from 书籍表
select * from 读者表
select * from 借阅表

-- 1、读者还书存储过程：ReturnBook_1的创建，1.成功还书时将归还否字段的‘否’改成‘是’，还书日期为当前时间，3.显示“成功地向图书馆归还！”。
create procedure xxx
@no varchar(10),@bid varchar(30)
as
if exists(select * from 借阅表 where 借书证号=@no and 图书编号=@bid and 归还否='否')
begin
update 借阅表 set 归还否='是',还书日期=GETDATE()
select '成功地向图书馆还书!'
end
go

-- 读者还书
exec xxx '10001','J2'
select* from 借阅表

-- 1、读者借书触发器
create trigger borrowbok2
on 借阅表
for insert
as
declare @total varchar(10),@borrow varchar(10)
select @borrow=图书编号 from 借阅表
select @total=库存量 from 书籍表 where 图书编号=@borrow
if(@total>0)
begin
update 书籍表 set 库存量=库存量-1
print'借书成功'
end
go

insert into 借阅表(图书编号,借书证号,借书日期,还书日期,归还否)
values('J1',10002,2022-12-12,2022-12-13,'否')

select* from 书籍表
select * from 借阅表
-- 在借阅表中创建一个触发器：tri_Book，若要借的书已无库存，则无法进行借书操作，即无法在‘借阅表’中插入记录
create trigger tri_Book
on 借阅表
for insert
as
declare @btotal varchar(10),@bborrowed varchar(10)
select @bborrowed=图书编号 from 借阅表
select @btotal=库存量 from 书籍表 where 图书编号=@bborrowed
if(@btotal=0)
begin
rollback transaction
print '借阅失败！'
print'对不起，此书已经没有库存，无法进行本次借书操作！'
end
go

insert into 借阅表(图书编号,借书证号,借书日期,还书日期,归还否)
values('J3',10001,2022-12-12,2022-12-13,'否')

use Library
go
create procedure ReturnBook
@no char(10),@bid char(10)
as
if not exists(select * from 借阅表 where 借书证号=@no and 图书编号=@bid)
begin
print'对不起，你没有借阅此书，故而无法进行此次还书操作，请核实！'
end

exec ReturnBook '10002','J1'
select* from 借阅表

create procedure ReturnBook_11
@no char(10),@bid char(10)
as
if not exists(select * from 借阅表 where @no=借书证号 and @bid=图书编号)
begin
print'你没有借到这本书，无法归还'
end

exec ReturnBook_11 '10001','J3'
select * from 借阅表