A view is a virtual table based on the result-set of a SELECT statement.
视图其实是建立在SELECT语句结果集合基础上的虚拟表

What is a View?
什么是视图

In SQL, a VIEW is a virtual table based on the result-set of a SELECT statement.
在SQL中，视图其实是建立在SELECT语句结果集合基础上的虚拟表

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from a single table.
视图里有行和栏目，和真的表差不多。在视图里的记录可以是来自一个或多个数据库表中。你可以在视图上加入SQL函数，WHERE和JOIN声明就好象这些数据是来自一张单独的表上的

Note: The database design and structure will NOT be affected by the functions, where, or join statements in a view.
注意：所设计好的数据库结构不会因为视图里的函数，where或是join声明而受到影响

语法

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

Note: The database does not store the view data! The database engine recreates the data, using the view's SELECT statement, every time a user queries a view.
注意：数据库不会保存视图数据！每当使用视图的SELECT声明一次就会让数据库引擎重建一次数据

Using Views
使用视图

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.
视图的使用可以来自一条查询，被存放的程序，或是来自另一个视图。通过给视图加上函数，joins等，可以让将准确的数据呈现给用户

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:
在样本数据库Northwind里就有一些默认就被安装好的视图。视图"Current Product List"列就是可以从Product表中列出所有活跃中的产品(没有停止的产品)。这个视图可以用以下SQL建立起来：

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

We can query the view above as follows:
我们可以这样查询上面建立好的视图：

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:
另一张视图里选择了所有超过unit平均价格的产品

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

We can query the view above as follows:
我们可以这样查询视图

SELECT * FROM [Products Above Average Price]

Another example view from the Northwind database calculates the total sale for each category in 1997. Note that this view select its data from another view called "Product Sales for 1997":
另一样本视图里放置了Northwind数据库里每个在1997分类下的总销售。注意一下这个视图里所选择的数据是来自名为"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

We can query the view above as follows:
我们可以这样查询视图：

SELECT * FROM [Category Sales For 1997]

We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":
我们还可以给查询加上条件。现在我们想看看"Beverages"类所销售的总量:

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

SQL Select Into SQL Server

评论 (0) All