【正文】
rom student(1) 創(chuàng)建規(guī)則用于限制性別項只能輸入“男”或“女” create table student(Ssex char(2) CONSTRAINT C4 CHECK(Ssex IN(‘男’,‘女’)),CONSTRAINT studentKey PBIMARY(Sno))。(2) 建立觸發(fā)器創(chuàng)建觸發(fā)器,當(dāng)新增、修改或刪除學(xué)生時自動修改相應(yīng)班級的學(xué)生人數(shù)CREATE trigger dele_number on studentfor deleteas begin declare @sno intset @sno=(select sno from deleted)If(select * from student where =@sno)Update class set = where classname in(select classname from class where =@sno)endcreate trigger insert_number on studentfor insertasbegindeclare @sno intset @sno=(select sno from inserted)Update class set =+1 where classname in(select classname from class where =@sno)end創(chuàng)建存儲過程,統(tǒng)計各院系、專業(yè)學(xué)生的男女人數(shù)create procedure number@classname charas BeginReturn(select count(sno) 男生人數(shù) from student where classname=@classname and ssex=’男’)Return(select count(sno) 女生人數(shù) from student where classname=@classname and ssex=’女’)end10