SQL Views（视图）
目录
Views
Declaring Views
Example: View Definition
Example: Accessing a View
Advantages of Views
Triggers on Views
Interpreting a View Insertion（视图插入操作的解释）
The Trigger
Views
A view is a relation defined in terms of stored tables (called base tables ) and other views.（视图是一种储存表和其他视图之间的一种关系）
Two kinds:
-
Virtual = not stored in the database; just a query for constructing the relation.
-
Materialized = actually constructed and stored.
可以将视图分为两种形式：虚拟视图（并没有真实存在，只是构造关系的查询语句）、物化视图
Declaring Views
Declare by:
CREATE [MATERIALIZED] VIEW
<name> AS <query>;Default is virtual.（缺省值是虚拟的）
Example: View Definition
CanDrink(drinker, beer) is a view “containing” the drinker-beer pairs such that the drinker frequents at least one bar that serves the beer:
CREATE VIEW CanDrink AS
SELECT drinker, beer
FROM Frequents, Sells
WHERE Frequents.bar = Sells.bar;Example: Accessing a View
- Query a view as if it were a base table.（视图的查询和普通的表没有什么区别）
- Also: a limited ability to modify views if it makes sense as a modification of one underlying base table.（能够有限的修改能力，如果对于视图的操作能够合理的映射到基表）
Example query:
SELECT beer FROM CanDrink
WHERE drinker = ’Sally’;Advantages of Views
-
Focus the Data for Users（为用户聚焦数据）
-
Focus on important or appropriate data only
-
Limit access to sensitive data（限制访问敏感数据）
-
-
Mask Database Complexity（屏蔽掉数据库的复杂性）
-
Hide complex database design
-
Simplify complex queries, including distributed queries to heterogeneous data
-
-
Simplify Management of User Permissions
-
Improve Performance
-
Organize Data for Export to Other Application
Triggers on Views
-
Generally, it is impossible to modify a virtual view, because it doesn’t exist.（通常情况下，修改视图是不可能的，因为视图并不存在）
-
But an INSTEAD OF trigger lets us interpret view modifications in a way that makes sense.（但是可以通过建立触发器，使得对于视图的操作能够映射到对应的基表中）
-
Example:
View Synergy has (drinker, beer, bar) triples such that the bar serves the beer, the drinker frequents the bar and likes the beer.
Interpreting a View Insertion（视图插入操作的解释）
-
We cannot insert into Synergy --- it is a virtual view.
-
But we can use an INSTEAD OF trigger to turn a (drinker, beer, bar) triple into three insertions of projected pairs, one for each of Likes, Sells, and Frequents.（可以使用触发器将视图投影成三个数对，对应三张表）
-
Sells.price will have to be NULL.（要注意的是Sells表中的price一定要置空）
The Trigger
CREATE TRIGGER ViewTrig
INSTEAD OF INSERT ON Synergy
REFERENCING NEW ROW AS n
FOR EACH ROW
BEGIN
INSERT INTO LIKES VALUES(n.drinker, n.beer);
INSERT INTO SELLS(bar, beer) VALUES(n.bar, n.beer);
INSERT INTO FREQUENTS VALUES(n.drinker, n.bar);
END;