视图

定义视图

1.建立视图

CREATE VIEW <视图名> [(<列名>[,<列名>])]

AS <子查询>

[WITH CHECK OPTION];         /*对视图进行UPDATE,INSERT,DELETE时保证行满足子查询中条件表达式*/

 

CREATE VIEW IS_S1

AS

SELECT Sno,Sname,Sage

FROM Student                          /*在一个或多个表上建立视图*/

WHERE Sdept = 'IS'

WITH CHECK OPTION;          /*对视图进行UPDATE,INSERT,DELETE时保证条件自动加上Sdept='IS'条件*/

 

CREATE VIEW IS_S2(Sno,Sname,Sage)

AS

SELECT Sno,Sname,Sage

FROM IS_S1                              /*在一个或多个已定义的试图上建立视图*/

WHERE Sage > 20;

 

CREATE VIEW S_G(Sno,Gavg)

AS

SELECT Sno,AVG(Grade)

FROM SC

GROUP BY Sno;                   /*分组视图*/

 

删除视图

DROP VIEW S_G;

 

查询视图

SELECT * FROM S_G WHERE Gavg >= 90;              /*在视图S_G中查询，视图S_G简化了求平均值这一过程*/

 

跟新视图

/* 跟新视图，与视图相关的表也会跟新（涉及是否可安全跟新表） */

UPDATE IS_S1 SET Sname = 'lulu' WHERE Sno = '2002849308';

INSERT INTO IS_S1 VALUES ('20023984','66',22);

DELETE FROM IS_S1 WHERE Sno = '20008472389';