SqlServer数据实验报告（三）

利用SQL Server Management Studio创建一个存储过程ProcNum，查询每个班级中学生的人数，按班级号升序排序。 create procedure ProcNum  as  select 班级号,count(*) as 人数 from 学生表 group by 班级号 order by 班级号 asc  execute ProcNum 利用Transact-SQL语句创建一个带有参数的存储过程ProcInsert，向score表插入一条选课记录，并查询该学生的姓名、选修的所有课程名称、平时成绩和期末成绩。   create procedure ProcInsert1 @xh char(10),@kch  nchar(4),@cj numeric (4,1)  as   begin insert into 成绩表 (学号,课程号,成绩) values (@xh,@kch,@cj) select  课程名称,成绩 from 成绩表 inner join 课程表 on 课程表.课程号=成绩表.课程号 where 学号=@xh end execute ProcInsert '3120200916','D003',55 利用Transact-SQL语句创建一个存储过程ProcAvg，查询指定班级指定课程的平均分。班级号和课程名称由输入参数指定，计算出平均分通过输出参数返回。若该存储过程已存在，则删除后重建。 4. 利用SQL Server Management Studio创建一个AFTER触发器trigsex，当插入或修改student表中性别字段sex时，检查数据是否只为“男”或“女”。 create trigger trigsex1 on temp1  after  Insert,update  as  begin  declare @sex char(2)  select @sex=性别 from inserted  if(@sex<>'男' and @sex<>'女')   begin   raiserror('性别只能是男或女',16,1)   rollback   end       end   insert into temp1(学号,姓名,专业名,入校成绩,性别)  values( '3120200920','小李','软件工程',444,'中') 5. 利用Transact-SQL语句创建一个AFTER触发器trigforeign，当向score表中插入或修改记录时，如果插入或修改的数据与student表中数据不匹配，即没有对应的序号存在，则将此记录删除。 create trigger trigforeign1 on temp2  after  Insert,update  as  begin  declare @xh char(10)  select @xh=学号 from inserted  if not exists(select * from temp1 where 学号=@xh)     begin      raiserror('学生表中没有此学号！',16,1)      rollback     end     end insert into temp2 (学号,课程号,成绩) values ('3120200922','D001',55) 6.利用Transact-SQL语句创建一个AFTER触发器trigclassname，当向class表中插入或修改数据时，如果出现班级名称重复则回滚事务。若该触发器已存在，则删除后重建。。 7建立一个后触发器，当向成绩表中插入或修改记录时，如果插入或修改的数据与学生表和课程表的中数据不匹配，此插入或修改无效. create trigger trigger1 on temp2  after  Insert,update  as  begin  declare @xh char(10),@kch nchar(4)  select @xh=学号 ,@kch=课程号 from inserted  if not exists(select * from temp1 where 学号=@xh)     begin      raiserror('学生表中没有此学号！',16,1)      rollback        if not exists(select * from temp3 where 课程号=@kch)       begin      raiserror('课程表中没有此课程号！',16,1)      rollback       end     end  end insert into temp2 (学号,课程号,成绩) values ('3120200922','D018',55) 8 假设学生表中有电话列，但不是主码或候选码（因为允许用户不留电话），但要求入库的用户的电话号不重复。要求对insert操作时使用后触发器实现控制。 create trigger trigger6 on temp1  after Insert,update  as  begin  declare @sjhm nchar(11)  select @sjhm= 手机号 from inserted  if exists(select *from temp1 where 手机号=@sjhm)      begin      raiserror('此手机号码已存在！',16,1)      rollback     end  end    insert into temp1(学号,姓名,专业名,入校成绩,手机号) values( '3120200923','小李','软件工程',444,'13539422174')    9 使用一般过程或触发器实现：在学生表中删除一个学生时，自动先删除成绩表中该生的一切选课数据  create trigger trigger4 on temp1  after delete  as  begin  declare @xh char(10)  select @xh=学号 from deleted  if exists(select *from temp2 where 学号=@xh)  delete from temp2 where 学号=@xh  end  delete from temp1 where 学号='3120200905'