江南大学物联网工程学院数据库课程实验二作业2实验报告

--创建客户表并且添加数据
create table customers(
cnum varchar(5) primary key, --将客户编号设置为主键，使其具有完整性
cname varchar(5) not null, --非空约束（下同）
lname varchar(5) not null,
cadres varchar(30) not null,
cpos_code varchar(10) not null,
lphone varchar(12) not null
);
insert into customers
--values('0001','叶民','Yeman','桃园公寓','214000','17851313918')
--values('0002','Lily','Lily','gym','214026','15251883908')
values('0003','Tom','Tom','KFC','214142','15253343
900');
--创建存储过程
create proc get_cus_info (@cpos varchar(6)) --有一个 in 输入的局部变量
as
begin
--可以直接获取满足条件的结果，concat 单
行函数为拼接字符串功能，like 模糊查询
select * from customers where cpos_code like 
(concat('%',@cpos))
end
--执行存储过程
--将'124'作为 in 参数输入，即获取邮编尾号是'142'的客户信息
exec get_cus_info '142'

--创建学生表并添加测试数据
create table s(
snum varchar(4) primary key, --设置学号为主键，使其具有完整性
sname varchar(5) not null, --非空约束
age int,
dept varchar(15)
);
insert into s
--values('001','张三',22,'物联网工程学院');
--values('002','王四',21,'计算机学院');
--values('003','李五',19,'生命工程学院');
values('004','赵六',19,'医学院');
--创建课程表并添加测试数据
create table c(
cnum varchar(4) primary key, --设置课程号为主键，使其具有完整性
cname varchar(10) not null, --非空约束（下同）
teacher varchar(5) not null
);
insert into c
--values('C1','Java','杨均')
--values('C2','数据库','马振')
--values('C3','计算方法','江楠')
values('C4','数据结构','陈帆')
--创建选课表并添加测试数据
create table sc(
snum varchar(4),
cnum varchar(4),
score int,
foreign key (snum) references s(snum), --设置外键约束，该表中的 snum 需要在 s 表中存在
foreign key (cnum) references c(cnum), --设置外键约束，该表中的 cnum 需要在 c 表中存在
unique (snum,cnum) --设置（snum，cnum）组合唯一，即该表中不能存在 snum 和 cnum 都相同的数据
);
insert into sc
--values('001','C1',98)
--values('002','C1',89)
--values('003','C1',93)
--values('001','C2',92)
--values('002','C2',98)
--values('003','C2',96)
--values('004','C3',96)
--创建触发器
create trigger check_legalsc
on sc --基于 sc 选课表
after insert --在添加后触发
as
declare @curc_count varchar(4) 
--获取添加的课程编号
select @curc_count = cnum from inserted 
declare @curs_snum varchar(4)
--获取添加的学生学号
select @curs_snum = snum from inserted 
--检查学号是否存在 s 表
if @curs_snum in (select snum from s) 
begin
--检查课程编号是否存在 c 课程表且未被选满（这里设置满为 2）
if 2 >= (select COUNT(*) from sc where 
cnum = @curc_count) and @curc_count in (select 
cnum from c)
begin
select '恭喜你，选课成功！' as '结果'
end
else
begin
--回滚﹐避免加入
rollback transaction 
select '对不起，该课程人数已满！' as '结果'
end
end
else 
begin
rollback transaction --回滚﹐避免加入
select '你好，你没有选改课的权限！' 
as '结果'
end
--测试触发器
--①成功情况
insert into sc(snum,cnum)
values('001','C3')
--②失败情况 1：课程已满
insert into sc(snum,cnum)
values('004','C1')
--③失败情况 2：学生学号不在 s 表中
insert into sc(snum,cnum)
values('005','C4')