数据库上机实验四（数据完整性与存储过程、触发器）

0本次数据库主要以数据完整性与存储过程为例，实验教材为李春葆的《数据库原理与技术》，本次实验内容为
1）完成P245第8题、第9题。
2）完成P395上机实验题5各题目、上机实验题6的（1）和（2）小题。
3）完成P396上机实验题7各题目。
4）完成P396 上机实验题8 （触发器内容从第18个开始）

上次实验关于索引与视图的过于简单就不再发博客了，如有需要可以评论留言。
当然对于数据完整性中，还是比较重要的，但是在一般工程设计中不介意设置数据的外键（阿里公司特意强调过），原因也是很明显，外键约束对于后期修改很麻烦，所以能不用还是不介意用。

下面将对实验进行说明：

1. 给学号增加非空约束(一开始要建表不要忘记）

create database test
use test
create  table table10(
    学号 int,
    姓名 nvarchar(10),
    专业 nvarchar(20),
    分数 int
)

-- 245-1
alter  table table10
    alter column 学号 int not null

2. 将学号设置为主键

-- 245-2
alter table table10
add primary key (学号)

3.在分数列上设置1-100的取值范围

-- 245-3
alter table  table10
add check(分数>0 and 分数<101)

4.在专业列上设置默认值“计算机科学与技术”

-- 245-4
alter table table10
add default '计算机科学与技术' for 专业

5.给出将其“借书人学号”列引用table10中学号列的外键关系的ALTER TABLE 语句

-- 245-question9
create table table11(
    图书编号 int,
    书名 nvarchar(30),
    借书人学号 int
)

alter table table11
add foreign key (借书人学号) references table10 (学号)

书后实验五-七

6.将student表中的性别列设置为只能取“男”或“女”值

-- test5 -1
use Library2128
alter table student2128
add constraint  性别  check (性别 in ('男','女'))

7.将student表中的性别列的默认值设置为男

--test5-2
alter  table student2128
add default '男' for 性别

8.修改student表，将其班号作为depart班号的外键

--test5-3
alter table student2128
add foreign key (班号) references depart2128(班号)

9.将borrow表中的学号和图书编号定义为主键

--test5-4, 注意此处删除主键的方法，这里的键按照自己的来写
alter table borrow2128
drop  CONSTRAINT PK__borrow21__7B9FD30EF71AFC24

alter table borrow2128
add primary key(学号,图书编号)

10.删除前面创建的约束

-- test5-5
alter table student2128
drop constraint 性别

alter  table student2128
drop constraint  FK__student2128__班号__49C3F6B7

11.建立一个事务向depart表插入2个记录并回滚事务，最后查看表情况

-- Test 6-1
begin transaction
    insert into depart2128 values ('2001','数学系')
    insert into depart2128 values ('2003','物理系')
    --insert into depart2128 values ('2004','化学系')
rollback
select  * from depart2128

12.建立一个事务向depart表插入3个记录，通过设置回滚点回到该回滚点上，最后查看情况。

-- Test 6-2
begin transaction
    insert into depart2128 values ('2001','数学系')
    insert into depart2128 values ('2003','物理系')
    save transaction Mysavp
    insert into depart2128 values ('2004','化学系')
rollback transaction  Mysavp
select  * from depart2128

13.在Library数据库中创建一个标量值函数Sum(n),求1+2+…+n之和,并用相关的数据进行测试

-- Test 7-1
use Library2128
create function  getSum(@n int)
returns  int
as
begin
    return @n*(@n+1)/2
end

--测试数据，测试n=100,getSum(100)
print dbo.getSum(100)

14.在Library数据库中创建一个内联表值函数nbook,返回指定系的学号、姓名、班级、所借图书名和借书日期

-- Test7-2
--drop function nbook
create function  nbook(@ch nvarchar(10))
returns table
as
  return
    (select s.学号,s.班号,bo.图书名,br.借书日期
    from depart2128 d,student2128 s,borrow2128 br,book2128 bo
    where d.系名=@ch and d.班号=s.班号 and s.学号=br.学号 and bo.图书编号=br.图书编号)

--测试数据
select * from dbo.nbook('电子工程系')

15.在Library数据库中创建一个多语句表值函数pbook,返回系名和该系所有学 生所借图书的平均价格，并用相关数据进行测试

-- Test7-3
create  function pbook(@ch nvarchar(10))
returns @st table
    (
        系名 nvarchar(10),
        平均价格 float
    )
as
begin
    insert @st
    select d.系名,avg(bo.定价*1.0)
    from student2128 s,depart2128 d,borrow2128 br,book2128 bo
    where d.系名=@ch and s.班号=d.班号 and s.学号=br.学号 and br.图书编号=bo.图书编号
    group by  d.系名
return
end
--测试数据
select * from  dbo.pbook('计算机系')

16.设计一个存储过程，查询每种图书品种的数目，并用相关数据进行测试

-- Test7-4
create procedure  getBookNum(@ch nvarchar(10))
as
select 图书名,count(*) as '数目'
from book2128
where 图书名=@ch
group by 图书名

--测试数据
exec getBookNum 'C程序设计'

17.设计一个存储过程，采用模糊查询方式查找借阅指定书名的学生，输出学号、 姓名、班号和书名，并用相关数据进行测试

create  procedure  checkStud(@ch nvarchar(10))
as
select s.学号,s.姓名,s.班号,bo.图书名
from student2128 s,book2128 bo,borrow2128 br
where s.学号=br.学号 and br.图书编号=bo.图书编号 and bo.图书名=@ch

--测试数据
exec checkStud 'C程序设计'

实验八

18.在borrow表中建一个触发器，完成：如果借阅的图书是”网络工程“，那么把该书记录保存在borrow1表中。

--Test 8-1
use Library2128
create  table borrow1(
    学号 nvarchar(10),
    图书编号 nvarchar(10) primary key (图书编号,学号),
    借书日期 datetime
)
go
create  trigger  tribor on borrow2128 for insert,update
as
    insert  borrow1  --插入数据到borrow1表中
    select  ins.*
    from inserted ins,book2128 b
    where ins.图书编号=b.图书编号 and b.图书名='网络工程'
go --测试数据，此时请在borrow1表中查看记录
insert into borrow2128 values ('19','20090','2022-4-27')
go --查看数据
select * from borrow1

19.在borrow表建一个触发器，完成：当删除borrow表中任何记录时，将记录保存在borrow1中

-- Test 8-2
create  trigger  tribordrop on borrow2128 for delete
as
    insert  borrow1  --插入数据到borrow1表中
    select *
    from deleted
go --测试数据，此时请在borrow1表中查看记录
delete  borrow2128 where 学号 =4
go
select * from borrow1

20.删除前面的触发器

-- Test 8-3
drop trigger tribor
drop trigger tribordrop