一.视图
定义:CREATE VIEW <视图名>[(<视图列表>)]
AS <子查询>
例如:CREATE VIEW SUB_S AS
SELECT Sno,Sname,SbirthDay
FROM Student WHERE Sdept =‘计算机’
删除视图: DROP VIEW <视图名>
如:DROP VIEW SUB_T
查询视图: SELECT Sname, SBirthDay FROM SUB_S
WHERE (YEAR(GETDATE())-YEAR(SBirthDay )) < 20
插入记录:INSERT INTO SUB_T
VALUES (‘T6’,’李丹’,’副教授’)
修改: UPDATE SUB_S SET Sex = ‘女’ WHERE Sname = ‘李丹’
删除:DELETE FROM SUB_S WHERE Sname = ‘李丹’
二.存储过程
定义:` CREATE PROCEDURE procedure_name
[@parameter data_type][OUTPUT]
[ ,...n ]
[WITH]{RECOMPILE|ENCRYPTION}
AS
SQL_statement
GO
[WITH]{RECOMPILE|ENCRYPTION}:其中,RECOMPILE表示每次执行此存储过程时都重新编译一次。ENCRYPTION表示所创建的存储过程的内容会被加密
例子:删除一个学生的记录
CREATE PROCEDURE usp_DeleteAStudent
@Sno char(5), @Sname varchar(8) OUTPUT
AS
IF EXISTS (SELECT * FROM SC WHERE Sno = @Sno)
DELETE FROM SC WHERE Sno = @Sno
SELECT @Sname = Sname FROM Student
WHERE Sno = @Sno
DELETE FROM Student WHERE Sno = @Sno
GO
调用:执行上例定义的存储过程,并删除学号为“95010”的相关信息
-- 测试和运行存储过程
DECLARE @Sname VARCHAR(8)
-- 调用存储过程
EXECUTE usp_DeleteAStudent ‘95010’, @Sanme
OUTPUT
-- 显示返回变量值
SELECT @Sname
删除存储过程:DROP PROCEDURE usp_DeleteAStudent;
GO
三:触发器
定义触发器的语法:
CREATE TRIGGER[OWNER.] trigger_name
ON [OWNER.] { TABLE | VIEW }
FOR { [ INSERT ] [ , ] [ UPDATE ] [ ,] [ DELETE] } AS
SQL_statement
例子1:保证学生-课程库中SC表的参照完整性,以维护其外码与参照表中的主码数据的一致性
例子2:若更改学生基本表Student的学号信息,则需要在选课基本表SC中也同时更改该学生的学号信息
例子3:若删除学生基本表Student的学号信息,则需要在选课基本表SC中也同时删除该学生所有的选课信息
修改触发器:
ALTER TRIGGER trigger_name
ON [OWNER.] { TABLE | VIEW }
FOR { [ INSERT ] [ , ] [ UPDATE ] [ ,] [ DELETE] }
AS SQL_statement
删除触发器:DROP TRIGGER trigger_name