/* 完成表中约束的定义 */ --创建course表
create table course (
cno char(1) primary key,
cname varchar(20) not null,
credit smallint check（credit>=1 and credit<=6） )
--创建class表 create table class (
clno char(5) primary key,
speciality varchar(20) not null,
inyear char(4) not null,
number integer check(number>1 and number<100), monitor char(7) )
--创建student表 create table student3 (
sno char(7) primary key, sname varchar(20) not null,
ssex char(2) not null default('男'), sage smallint check(sage>14 and sage<65),
clno char(5) not null references class(clno) on delete cascade on update cascade )
--为class表添加参照完整性 alter table class
add constraint fk_monitor foreign key (monitor) references student(sno) on delete no action
--创建grade表 create table grade (
sno char(7) not null references student(sno) on delete cascade on update cascade, cno char(1) not null references course(cno) on delete cascade on update cascade, gmark decimal(4,1) check(gmark>0 and gmark <100), primary key (sno,cno) )
/* 针对成绩管理数据库中的表，完成以下操作：*/ --（1）用户张勇对Student表和Course表有Select权力。 Grant select on student to 张勇 Grant select on 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
--（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表中相应班级的人数。*/
--创建insert触发器，适用于student表的单行数据的添加 create trigger stu_insert on student after insert as
update class
set number=number+1 from class,inserted
where class.clno = inserted.clno
--创建delete触发器，适用于student表的单行数据的删除 create trigger stu_delete on student after delete
as
update class
set number=number-1 from class,deleted
where class.clno = deleted.clno
--将insert和delete写入一个触发器内，适用于student表的单行数据的添加或删除 create trigger tri_stu on student after insert,delete as
if update(sno)
update class
set number=number+1
where clno = (select clno from inserted) else update class
set number=number-1
where clno = (select clno from deleted)
--验证触发器，添加数据 insert into student
values ('2222','tom','男',20,'00311') --验证触发器，删除数据 delete from student where sno='2222'
--假设向student表添加或删除的多行数据都来自同一个班级 create trigger tri_stu2 on student
after insert,delete as
if update(sno)
update class
set number=number+(select count(*) from inserted) where clno = (select clno from inserted) else update class
set number=number-(select count(*) from inserted) where clno = (select clno from deleted)