本次数据库主要以数据完整性与存储过程为例，实验教材为李春葆的《数据库原理与技术》，本次实验内容为
1）完成P245第8题、第9题。
2）完成P395上机实验题5各题目、上机实验题6的（1）和（2）小题。
3）完成P396上机实验题7各题目。
上次实验关于索引与视图的过于简单就不再发博客了，如有需要可以评论留言。
当然对于数据完整性中，还是比较重要的，但是在一般工程设计中不介意设置数据的外键（阿里公司特意强调过），原因也是很明显，外键约束对于后期修改很麻烦，所以能不用还是不介意用。
下面将对实验进行说明：
- 给学号增加非空约束(一开始要建表不要忘记）
create database test
use test
create table table10(
学号 int,
姓名 nvarchar(10),
专业 nvarchar(20),
分数 int
)
-- 245-1
alter table table10
alter column 学号 int not null
- 将学号设置为主键
-- 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程序设计'
