数据库实验六

-- 1.将stu数据库中student表的sno定义为主键；
alter table student
alter column sno varchar(20) not null
alter table student
add primary key (sno)
--2.将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;
alter table course
alter column cno varchar(10) not null
alter table course
add constraint cno_pk  primary key(cno)
--3.为表course中的字段cname添加唯一值约束；
alter table course
add constraint c1 unique(cname)
--4.将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;
alter table sc
alter column cno varchar(10) not null
alter table sc
alter column sno varchar(20) not null
alter table sc
add constraint sc_pk primary key(sno,cno)
--5.对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：

--1)删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；
--2)修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；
--3)修改course表cno字段值时，该字段在sc表中的对应值也应修改；
--4)删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；
--5)向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；
alter table sc
add constraint f1 foreign key (sno) references student(sno)
on delete cascade
on update no action
alter table sc
add constraint f2 foreign key (cno) references course(cno) 
on update cascade
on delete cascade
--6.定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；
alter table student
add constraint s1 check (sno like '[1-9]00______')
--7.定义stu数据库中student表中学生年龄值在16-25范围内；
alter table student
add constraint s2 check (sage between 16 and 25)
--8.定义stu数据库中student表中学生姓名长度在2-8之间；
alter table student
add constraint s3 check (len(sname) between 2 and 8)
--9.定义stu数据库中student表中学生性别列中只能输入“男”或“女”；
alter table student
add constraint s4 check (ssex in ('男','女'))
--10.定义stu数据库student表中学生年龄值默认值为20；
alter table student
add constraint s5 default(20) for sage
--11.修改student表学生的年龄值约束可以为15-30范围内；
alter table student
drop s2
alter table student
with nocheck
add constraint s2 check (sage between 15 and 30)
--12.删除上述唯一值约束、外键约束及check约束；
alter table course
drop c1
alter table sc
drop f1,f2
alter table student
drop s1,s2,s3,s4,s5
--13.设计触发器实现如果一个学生转专业了，那么输出一条信息显示该学生各门课程的平均分。
use stu
go
create trigger up_sdept
on student
for update
as
if update(sdept)
begin
declare @average int,@sno varchar(20)
select @sno=sno
from deleted
select @average=avg(grade)
from sc
where sc.sno=@sno
print @sno
end

--14.设计触发器实现如果成绩被修改了20分以上，则输出提示信息“修改成绩超过20分，请慎重”。
go
create trigger ck_grade on sc
for update
as
declare @grade int
select @grade=grade
from inserted
if @grade>20
begin
print '修改成绩超过20分，请慎重'
end

--15. 在student表中增加一列total,表示学生选课总门数，初始值为0。定义一个触发器，实现如下完整性约束：当向SC表插入选课记录时，自动更新student表对应学号的total值,考虑成批插入数据的情况。
alter table student
add total int default(0)
go
create trigger xuanke_check
on sc
for insert
as
declare @sno varchar(20)
select @sno = sno
from inserted
update student
set total=total+1
where sno=@sno

--16.设计一触发器，约束数据库系统课程的课容量为120。
go
create trigger full_course on course
instead of insert
as
declare @num int
select @num=count(*)
from course
if @num=120
begin
print '课容量已经达到最大了'
end
else
begin 
insert into course select * from inserted
end

--17．设有两个表：商品库存表（商品编号，商品名称，库存数量，库存单价，库存金额）；商品销售表（商品编号，商品名称，购货商号，销售数量，销售单价，销售金额）；设计一触发器实现如下业务规则：
create table kucun(
pno varchar(10),
pname nvarchar(20),
number int,
price int,
total int)
create table xiaoshou(
pno varchar(10),
pname nvarchar(20),
cno varchar(10),
counts int,
price int,
totalprice int)
--（1）保证在商品库存表中插入的数据，库存金额 = 库存数量 * 库存单价。
--（2）如果销售的商品不存在库存或者库存为零，则返回提示信息。否则自动减少商品库存表中对应商品的库存数量和库存金额。
--建表并验证触发器的执行。
go
create trigger ck_total
on kucun
instead of insert
as
declare @price int,@number int,@total int,@pno varchar(10),@pname nvarchar(20)
select @price = price from inserted
select @number = number from inserted
select @total=@price*@number
select @pno = pno from inserted
select @pname = pname from inserted
insert into kucun
values(@pno,@pname,@number,@price,@total)

go
create trigger tr_sa
on xiaoshou
for insert
as
if ((select number from kucun where kucun.pno=(select pno from inserted))=0)
begin
    print '库存不足'
    rollback transaction
end
else
begin
update kucun
set number=number-(select counts from inserted)
where kucun.pno=(select pno from inserted)
update kucun
set total=number*price
where kucun.pno=(select pno from inserted)
end

--18．建立教师表（教工编号，姓名，专业，职称，工资）和
--工资变化表（教工编号，原工资，新工资），设计触发器实现教授的工资不得低于4000元，如果低于4000元则自动改为4000元。
create table teacher(
tno varchar(10),
tname nvarchar(10),
tdept nvarchar(20),
zhicheng nvarchar(20),
gongzi int)
create table gongzi(
tno varchar(10),
old_gz int,
new_gz int)
go
create trigger gz_ck
on teacher
for update,insert
as
declare @gz int
declare @tno varchar(10)
select @gz=gongzi from inserted
if @gz<4000
begin
select @tno = tno from inserted
update teacher
set gongzi=4000
where tno=@tno
end
--19.    使用第18题的两个表设计触发器实现如果教工的工资发生变化则向工资变化表插入一条记录，包含教工编号，原工资，新工资。
go 
create trigger gzbh
on teacher
for update
as
if update(gongzi)
begin
declare @old_gz int,@new_gz int,@tno varchar(10)
select @old_gz=gongzi from deleted
select @new_gz = gongzi from inserted
select @tno = tno from inserted
insert into gongzi 
values(@tno,@old_gz,@new_gz)
end