alter table classes
add constraint c1
foreign key (zno) references majors(zno)
alter table majors
add constraint c2
foreign key (xno) references sdepts(xno)
alter table students
add constraint c3
foreign key (bno) references classes(bno)
alter table students
add constraint c4
foreign key (qno) references rooms(qno)
alter table students
add constraint c5
unique (tel)
alter table students
add constraint c6
default '男' for ssex
create view v_stu(学号,姓名,班级,专业,系部,寝室,性别)
as
select sno,sname,bname,zname,xname,qname,ssex
from students,classes,majors,rooms,sdepts
where students.bno=classes.bno
and students.qno=rooms.qno
and majors.xno=sdepts.xno
and classes.zno=majors.zno
create view v_tea(学号,姓名,班级,专业,系部,公寓,性别,年龄,政治面貌,籍贯,父亲姓名,母亲姓名,家庭联系电话)
as
select sno,sname,bname,zname,xname,qname,ssex,sage,zhengzhi,jiguan,fname,mname,tel
from students,classes,majors,rooms,sdepts
where students.bno=classes.bno
and students.qno=rooms.qno
and majors.xno=sdepts.xno
and classes.zno=majors.zno
create trigger t1
on students
for update
as
if update(sno)
begin
print'禁止修改学生的学号'
rollback
end
create trigger t2
on students
for update,insert
as
if(((select ssex from inserted)='男')and(((select qno from inserted )='q02')or((select qno from inserted )='q04'))or((select ssex from inserted)='女')and(((select qno from inserted )='q01')or((select qno from inserted )='q03')))
begin
print'宿舍分配出错,请重新分配!'
rollback
end
--insert into students
--values
--('n0017','张建','jd1002',2009,'q02',19,'男','张晨','黄帆','15830463617','浙江','共青团员')
CREATE PROC s1_info @sn char(10)
AS
SELECT sname,bname,zname
FROM students,majors,classes
WHERE students.bno=classes.bno
and classes.zno=majors.zno
and students.sno=@sn
Go
--EXEC s1_info 'n0001'
评论4