SQL指南－ CREATE VIEW 语句

A view is a virtual table based on the result-set of a SELECT statement.

---

什么是视图?

在SQL里，视图是一个基于SELECT查询的result-set的虚拟数据表。

视图包含列和行，就象是一个真正的表格。视图里的字段来自于数据库里一个或者多个真正的表。你可以给视图添加SQL函数，WHERE和JOIN语句，如同来自于一个表格般地呈现数据。

注意： 视图内的函数，WHERE或JOIN将不会影响数据库设计和结构。

CREATE VIEW view_name AS
            SELECT column_name(s)
            FROM table_name
            WHERE condition

注意： 数据库并不存储视图数据！数据引擎得以休息，使用视图查询语句，用户每次查询的只是视图。

---

 

使用视图

A view could be used from inside a query, a stored procedure, or from inside another view. By adding functions, joins, etc., to a view, it allows you to present exactly the data you want to the user.

The sample database Northwind has some views installed by default. The view "Current Product List" lists all active products (products that are not discontinued) from the Products table. The view is created with the following SQL:

CREATE VIEW [Current Product List] AS
            SELECT ProductID,ProductName
            FROM Products
            WHERE Discontinued=No

随后我们可以查询上面的视图

SELECT * FROM [Current Product List]

Another view from the Northwind sample database selects every product in the Products table that has a unit price that is higher than the average unit price:

CREATE VIEW [Products Above Average Price] AS
            SELECT ProductName,UnitPrice
            FROM Products
            WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

随后我们可以查询上面的视图

SELECT * FROM [Products Above Average Price]

另一个示例视图来自于Northwind 数据库计算1977年每个种类的销售合计。注意这个视图查询它的数据来自于另一个称作 "Product Sales for 1997"的视图。

CREATE VIEW [Category Sales For 1997] AS
            SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
            FROM [Product Sales for 1997]
            GROUP BY CategoryName

随后我们可以查询上面的视图

SELECT * FROM [Category Sales For 1997]

我们也可给视图添加一个条件。现在我们想看看"Beverages"的销售合计。

SELECT * FROM [Category Sales For 1997]
            WHERE CategoryName='Beverages'