--视图
--视图和临时表的区别:视图存储的是sql语句,占用存储空间小,而临时表存储的是结果集,占用的存储空间大,所以视图更加好
--创建视图存储一个结果
drop view v1
create view v1 as
select * from sc where cno=1
--查询视图中的数据
select * from v1
--创建视图存储两个结果
drop view v2
create view v2 as
select * from sc where cno=1
union
select * from sc where sno=1
--查询视图中的数据
select * from v2
--存储过程
--创建单一存储过程
drop procedure p1
create procedure p1 as
begin
select *from sc where cno=1
select * from sc where sno=1
end
--查询存储过程中的数据
p1
--创建带参数的存储过程
drop procedure p2
create procedure p2 @x int,@y int as
begin
select *from sc where cno=@x
select * from sc where sno=@y
end
--查询存储过程中的数据
--p2 1 只有一个参数@x=1
p2 1,2 --有两个参数 @x=1,@y=2
---------------------------------------------------------
----触发器:创建触发器之前先删除表中关联的外键
--触发器的功能:删除没有外键映射的两个表中同一个字段的数据 功能与外键功能一样
select * from dept
select * from emp
--在部门表里面创建修改触发器
drop trigger tri_dept_emp_update
go
create trigger tri_dept_emp_update on dept for update
as
begin
update emp set emp.deptno = i.deptno
from deleted d, inserted i
where emp.deptno = d.deptno
end
go
update dept set deptno=12 where deptno=10
select * from dept
select * from emp
--在部门表里面创建删除触发器
drop trigger tri_dept_emp_delete
create trigger tri_dept_emp_delete on dept for delete
as
begin
delete emp
from deleted d
where emp.deptno = d.deptno
end
--在课程表里面创建修改触发器
drop trigger tri_course_sc_update
create trigger tri_course_sc_update on course for update
as
begin
update sc set sc.cno=i.cno
from deleted d,inserted i
where sc.cno=d.cno
end
--在学生表里面创建修改触发器
drop trigger tri_student_sc_update
create trigger tri_student_sc_update on student for update
as
begin
update sc set sc.sno=h.sno
from deleted e,inserted h
where sc.sno=e.sno
end
--视图和临时表的区别:视图存储的是sql语句,占用存储空间小,而临时表存储的是结果集,占用的存储空间大,所以视图更加好
--创建视图存储一个结果
drop view v1
create view v1 as
select * from sc where cno=1
--查询视图中的数据
select * from v1
--创建视图存储两个结果
drop view v2
create view v2 as
select * from sc where cno=1
union
select * from sc where sno=1
--查询视图中的数据
select * from v2
--存储过程
--创建单一存储过程
drop procedure p1
create procedure p1 as
begin
select *from sc where cno=1
select * from sc where sno=1
end
--查询存储过程中的数据
p1
--创建带参数的存储过程
drop procedure p2
create procedure p2 @x int,@y int as
begin
select *from sc where cno=@x
select * from sc where sno=@y
end
--查询存储过程中的数据
--p2 1 只有一个参数@x=1
p2 1,2 --有两个参数 @x=1,@y=2
---------------------------------------------------------
----触发器:创建触发器之前先删除表中关联的外键
--触发器的功能:删除没有外键映射的两个表中同一个字段的数据 功能与外键功能一样
select * from dept
select * from emp
--在部门表里面创建修改触发器
drop trigger tri_dept_emp_update
go
create trigger tri_dept_emp_update on dept for update
as
begin
update emp set emp.deptno = i.deptno
from deleted d, inserted i
where emp.deptno = d.deptno
end
go
update dept set deptno=12 where deptno=10
select * from dept
select * from emp
--在部门表里面创建删除触发器
drop trigger tri_dept_emp_delete
create trigger tri_dept_emp_delete on dept for delete
as
begin
delete emp
from deleted d
where emp.deptno = d.deptno
end
--在课程表里面创建修改触发器
drop trigger tri_course_sc_update
create trigger tri_course_sc_update on course for update
as
begin
update sc set sc.cno=i.cno
from deleted d,inserted i
where sc.cno=d.cno
end
--在学生表里面创建修改触发器
drop trigger tri_student_sc_update
create trigger tri_student_sc_update on student for update
as
begin
update sc set sc.sno=h.sno
from deleted e,inserted h
where sc.sno=e.sno
end