SQL Views（视图）

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（视图插入操作的解释）

1. We cannot insert into Synergy --- it is a virtual view.
2. 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.（可以使用触发器将视图投影成三个数对，对应三张表）
3. 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;