杭电数据库张红娟编习题参考答案

1、)/*完成表中约束的定义*/-创建course表create table course(cno char(1) primary key,cname varchar(20) not null,credit smallint check(credit=1 and credit1 and number14 and sage0 andgmark 100), primary key (sno,cno)/*针对成绩管理数据库中的表，完成以下操作：*/-(1)用户张勇对Student表和Course表有Select权力。Grant select on student to张勇Grant select on

2、course to张勇-(2)把对表Student的INSERT和Delete权限授予用户张三，并允许他再把此权限授予其 他用户。Grant insert,delete on student to张三with grant option-(3)把查询Course表和修改属性Credit的权限授给用户李四。Grant select,update(credit) on course to李四-(4)授予用户李勇敏对Student表的所有权力(读、插、删、改) ，并具有给其他用户授 权的权力。Grant all privilege on student to李勇敏with grant option-(

3、5)撤销(1)中对张勇所授予的所有权力。Revoke select on student to张勇Revoke select on course to张勇或：Revoke select on student from张勇Revoke select on course from张勇-(6)撤销(2)中对张三所授予的所有权力。revoke insert,delete on student to张三cascade或revoke insert,delete on student from张三cascade/*为成绩管理数据库中的Student表创建一触发器： 当向表中插入或删除记录时， 修改Class

4、表中相应班级的人数。*/-创建insert触发器，适用于student表的单行数据的添加create trigger stu_inserton studentafter insertasupdate classset number=number+1from class,insertedwhere =-创建delete触发器，适用于student表的单行数据的删除create trigger stu_deleteon studentafter deleteasupdate classset number=number-1from class,deletedwhere =-将insert和dele

5、te写入一个触发器内，适用于student表的单行数据的添加或删除create trigger tri_stuon studentafter insert,deleteasif update(sno)update classset number=number+1where clno = (select clno from inserted)else update classset number=number-1where clno = (select clno from deleted)-验证触发器，添加数据insert into studentvalues (2222,tom,男,20,00

6、311)-验证触发器，删除数据delete from studentwhere sno=2222-假设向student表添加或删除的多行数据都来自同一个班级create trigger tri_stu2on studentafter insert,deleteasif update(sno)update classset number=number+(select count(*) from inserted) where clno = (select clno frominserted)else update classset number=number-(select count(*) f

7、rom inserted) where clno = (select clno from deleted)-适用于student表的多行数据的添加或删除（最靠谱解决方案）create trigger tri_stu2on student after insert,delete as begindeclare sno char(7),clno char(5)if update(sno)begindeclare mycursor cursor for select sno,clno from inserted -声明游标open mycursor-打开游标fetch next from mycur

8、sor into sno,clno-获取数据while(fetch_status =0 )/* 0操作成功，-1 FETCH语句失败或此行不在结果集中，-2被提取的行不存在*/beginupdate classset number=number+1 where clno = clnofetch next from mycursor into sno,clno endclose mycursordeallocate mycursorendelsebegindeclare mycursor cursor for select sno,clno from deleted open mycursorf

9、etch next from mycursor into sno,clno while(fetch_status = 0)beginupdate class set number = number -1 where clno=clnofetch next from mycursor into sno,clno endclose mycursor deallocate mycursorend end -为class表再建一更新触发器：当更新班长学号时，检查新输入的学号是否为同一班级的学 生学号，若不是，给出适当的提示信息。create trigger stu_updateon classafte

10、r update asif update(monitor)if ( select monitor from inserted ) not in( select sno from studentwhere clno = (select clno from deleted ) ) beginprint there is not the new monitor in the classrollback transactionend-验证触发器执行update class set monitor = 2001104 where clno = 00312-创建商品表-关闭游标-释放游标create ta

11、ble product( pno char(6) primary key,pname varchar(20) not null,price decimal(7,2) )-创建仓库表create table warehouse( whno char(3) primary key,whname varchar(20) not null, whaddress varchar(20) )-创建库存商品表create table whproduct( whno char(3) references warehouse(whno) on delete no action on update cascade, pno char(6) referencesproduct(pno) on delete cascade on update cascade, number int )-设计触发器，当新增商品时，自动生成该商品在所有仓库的库存记录，库存数量为0create trigger tri_producton productafter insertasbegindeclare pno char(3)select pno=pno from insertedinsert into whproductselect whno,pno,0from warehouseend-设计触发器，当新增仓库