sql server 上机实验 sql server实训

一、数据库设计题

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

where a.图书编号=b.图书编号 and b.借书证号='10001' group by b.借书证号 order by b.借书证号;

三、填空题（每空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.显示“成功地向图书馆归还！”。

create procedure ReturnBook_1

@no varchar(10),@bid varchar(30)

as

if exists(select *  from 借阅表 where 借书证号=@no and 图书编号=@bid and 归还否='否')

begin

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

where 借书证号=@no and 图书编号=@bid

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

end

go

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

exec ReturnBook_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 借阅表