--select 查询出来的结果集可以通过as 起别名当做虚拟表来用 select*from (select*from score ,grade where score.degree between low and upp)as jibietable where rank='A' --创建视图(视图只能建立在实体表的基础上) create view shitu1 as select student.sname,student.sno,score.degree from student,score where student.sno=score.sno go create view shitu2 as select*from (select*from score ,grade where score.degree between low and upp)as jibietable where rank='A' go --修改视图 alter view shitu2 as select *from student go select *from shitu2 --删除视图 drop view shitu1
--触发器---特殊的存储过程。通过增删改数据库表来自动引发 alter trigger TR_Student_Insert on student--在哪个表上建立触发器 for insert--for是新增之后触发,for还可以写成after as begin update student set sname='杨波' where sname='金拓' end go insert into student values(304,'杨岳','男','1990-07-21','95033') select *from student
-- alter trigger TR_Student_Delete on student instead of delete as begin declare @sno int select @sno=sno From deleted delete from score where sno=@sno delete from student where sno=@sno end go delete from Student where Sno=304 select *from student insert into Score values(304,'3-105',98)
-- drop trigger TR_Student_insert create trigger TR_student_inserted on student instead of insert as begin declare @sno int,@sname varchar(20),@ssex varchar(20), @sbirthday datetime, @class varchar(20) select @sno=sno,@sname=sname,@ssex=ssex,@sbirthday=sbirthday,@class=class from inserted if @ssex='男' begin set @ssex='1' end insert into student values(@sno,@sname,@ssex,@sbirthday,@class) end go insert into student values(303,'金拓','男','1992-07-21','95033') select *from student
--事务 select *from huowu begin tran--事务的开始 --开始写流程语句
--语句写完之后 if @@ERROR>0 rollback tran--回滚事务 else commit tran--提交事务 goto tranrollback --去往目标地 select*from student select*from score begin tran delete from score where sno=107 delete from student where sno=109 if @@ERROR>0 begin rollback tran end else begin commit tran end