SQL实践—Index&View&Proc&Trigger

Index & View

1. 定义视图V_TCS（定义时不加with check option），存放全部计算机系老师的信息。

   create view V_TCS 
   as 
   select T.* from T join D on T.dno=D.dno where D.dn='计算机系';
   -- 视图查询语句：
   select * from V_TCS;

2. 定义视图V_sal，存放全体教师的教师号，姓名，教师酬金（工资+岗位津贴）和职称信息。

   go
   create view V_sal
   as 
   select tno 教师号,tn 教师姓名,Sal+comm 教师酬金,prof 职称信息 from T;
   go

3. 向V_TCS中插入一条计算机专业教师的新记录，并查询V_TCS视图的全部记录。

   insert into V_TCS values('t8','周杰','男','34','教授',1000,2000,'d1');

4. 向V_TCS中插入一条信息专业教师的新记录，并查询V_TCS视图的全部记录。

   insert into V_TCS values('t9','奕迅','男','40','讲师',1000,2000,'d3');

5. 修改V_TCS中的记录，使得编号为t22的教师的职称为副教授。

   update V_TCS set prof='副教授' where tno='t22';

6. 定义视图V_TCS_ck（定义时加with check option），存放全部计算机系老师的信息，对比该视图与V_TCS在进行数据更新时的差别。

   go
   create view V_TCS_ck
   as 
   select T.* from T join D on T.dno=D.dno where D.dn='计算机系'
   with check option;
   go

• 在插入时的对比

  -- V_TCS_ck：无法插入别的系的老师信息
  insert into V_TCS_ck values('t10','奕迅','男','40','讲师',1000,2000,'d3');
  
  -- V_TCS：可以插入其他系的老师信息，但是查询不出来其他系的老师信息
  insert into V_TCS values('t12','奕迅','男','40','讲师',1000,2000,'d3');

• 在更新时的对比

  -- V_TCS_ck：无法将老师信息更新为其他系
  update V_TCS_ck set dno='d3' where tno='t8';
  
  -- V_TCS：可以更新为其他系，但是查询视图时查不出该条信息
  update V_TCS set dno='d3' where tno='t8';

7. 在课程C表的课程名属性上按降序创建唯一索引I_cn。

   create unique index I_cn on C(cn DESC);

8. 删除C表上的索引I_cn。

   drop index C.I_cn;

9. 定义SC_C1视图，存放所有选修了C1课程的学生的学号和姓名。

   go
   create view SC_C1
   as 
   select S.sno,sn from S join SC on S.sno=SC.sno where SC.cno='c1';
   go

• 插入时的受限情况：

  -- 无法插入任何新的信息
  insert into SC_C1 values('s33','毕福剑');
  --（缺少原表不能为空的值，以向原表插入的信息格式插入会报错提供值得数目与表定义不匹配）
  --不能将值NULL插入列'sex'，表'sch_240.dbo.S'；列不允许有Null值。INSERT失败。
  
  insert into SC_C1 values('s34','毕福剑','男',23,'d1','1');
  --列名或所提供值的数目与表定义不匹配。

• 更新时的受限情况：

  update SC_C1 set sno='s9' where sno='s1';
  update SC_C1 set sn='毕福' where sno='s1';
  --（该视图来自选课表，其中sno是选课表参考学生表的外键，有外键约束，直接更新受限）
  UPDATE语句与REFERENCE约束"FK__SC__sno__4BAC3F29"冲突。该冲突发生于数据库"sch_240"，表"dbo.SC",column 'sno'。

• 删除时的受限情况：
  视图或函数'SC_C1'不可更新，因为修改会影响多个基表。

10. 定义一个C_score视图，存放各门课课程编号以及成绩的最大值。

    go
    create view C_score (课程号,课程最高分)
    as 
    select cno,MAX(score) from SC
    group by cno
    go

• 插入时的受限情况：
  对视图或函数'C_score'的更新或插入失败，因其包含派生域或常量域。
• 更新时的受限情况：
  因为视图或函数'C_score'包含聚合、DISTINCT或GROUP BY子句或者PIVOT或UNPIVOT运算符，所以无法进行更新。
• 删除时的受限情况：
  因为视图或函数'C_score'包含聚合、DISTINCT或GROUP BY子句或者PIVOT或UNPIVOT运算符，所以无法进行更新。

Procedure & Trigger

触发器的工作原理
触发器建立在一个表或者视图之上，在数据发生更改时自动执行相关代码，以保证数据的完整性，比如在修改一个表的一个元组某个属性时，可能其他表某属性是该属性的外键，而修改后的内容是基本表没有的属性，这时外键表的属性没有随着基本表改变而产生错误，这时我们可以在基本表建立一个触发器，当进行修改属性值时，同时将外键表的值做相应的修改。

1. 创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。

   create proc JSXX_PROC
   as
   select tn,cn from TC join C on TC.cno=C.cno join T on TC.tno=T.tno
   -- 存储过程执行语句
   exec JSXX_PROC;

2. 创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。

   create procedure XM_PROC
   @sn char(20)
   as
   if exists (select * from S where sn=@sn)
   select S.sno,cn,score from SC join C on SC.cno=C.cno join S on S.sno=SC.sno where sn=@sn
   else
   print '无该姓名的学生'

3. 创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。如果没有该专业，则显示“无此专业”。

   create procedure XBNL_PROC 
   @dn char(20)='计算机系'
   as
   if exists(select * from D where dn=@dn) 
   select age,sum(case when sex='男' then 1 else 0 end) as 男生人数,sum(case when sex='女' then 1 else 0 end) as 女生人数 
   from S join D on s.dno=D.dno where dn=@dn group by age
   else
   print '无此专业'

4. 分别查看XBNL_PROC存储过程的一般信息。

   exec sp_helptext XBNL_PROC

5. 删除XM_PROC存储过程。

   drop proc XBNL_PROC

6. 创建触发器T_insertS，当向S表添加记录时，验证学生的班级号是否出现在B表中，如果不是则撤销S表添加记录的操作，如果学生的班号在班级表中则自动修改相应班级的人数字段值。

   create trigger T_insertS on S
   after insert
   as
   if exists(select * from inserted join B on B.bno=inserted.bno)
     begin
     update B set num=num+1 where bno in (select bno from inserted)
     print 'insert OK'
     end
   else
     rollback transaction

7. 禁用T_insertS触发器语句。

   disable trigger T_insertS on S

8. 启用T_insertS触发器语句。

   enable trigger T_insertS on S

9. 创建触发器T_updateS，当修改S表一位同学的班级字段值时，验证学生的班级号是否出现在B表中，如果不是则撤销对S表中该条记录的修改操作，如果修改后的班号在班级表中则自动修改相应班级的人数字段值。

   create trigger T_updateS on S
   after update
   as
   if exists(select * from inserted join B on B.bno=inserted.bno)
   begin
   update B set num=num+1 where bno in(select bno from inserted)
   update B set num=num-1 where bno in (select bno from deleted) 
   print 'update OK'
   end
   else
   rollback transaction

10. 请定义一个视图V_T，该视图体现了职称是副教授的老师的编号、姓名、性别和所教课程编号、课程名的信息。尝试对视图V_T进行数据插入，例如，插入编号为t111的男教师李晨副教授讲授编号为c11的“信息安全”课的信息，观察是否能成功。请自行编写一个该视图的触发器，使得定义触发器后，用户可以对视图V_T进行上述数据记录的插入。注意：所插入的教师和课程可能从未出现在教师表和课程表中。

    --视图创建：
    create view V_T
    as
    select T.tno,tn,sex,C.cno,cn from T join TC on TC.tno=T.tno join C on C.cno=TC.cno where prof='副教授'
    
    --触发器创建：
    create trigger Trigger_V_T
    on V_T instead of insert
    as
    if not exists(select *from C where cno in(select cno from inserted))
    insert C(cno,cn) select cno,cn from inserted
    if not exists(select *from T where tno in(select tno from inserted))
    insert T(tno,tn,sex) select tno,tn,sex from inserted
    update T set prof='副教授' where tno in(select tno from inserted)
    if not exists(select T.tno,C.cno from TC join T on T.tno=TC.tno join C on C.cno=TC.cno where TC.tno in (select tno from inserted))
    insert TC(tno,cno) select tno,cno from inserted