sql server中的view有什么用？

what is the use of view in sql server?

提问by Surya sasidhar

Possible Duplicate:
What are views good for?

可能的重复：
视图有什么用？

hi, i have a doubt what is a view. where should we use and what is the use of view. is there any good reference for views. thank you

嗨，我怀疑什么是视图。我们应该在哪里使用以及视图的用途是什么。有什么好的意见参考。谢谢你

回答by Will Marcouiller

Please, have an eye out this View (database) Wikipedia article.

请注意这个视图（数据库）维基百科文章。

In short, a Viewcan be used with more than SQL Server, it is a SQL Standard.

总之，一个View可以与多个SQL Server一起使用的，它是一个SQL Standard。

A VIEWis generally used:

AVIEW一般用于：

1. To present the information data on a different point of view;
2. To simplify the access to the information of a high-level complexity of the database schema;
3. To prevent direct access to database tables (for security purposes)(less popular these days because of ORM tools);
4. Some "reality" is just simpler with a VIEW.

1. 呈现在不同的点的信息的数据视图;
2. 简化对高级复杂数据库模式信息的访问；
3. 防止直接访问数据库表（出于安全目的）（由于ORM 工具，这些天不太流行）；
4. 使用VIEW.

Here's an example when the database schema stores hierarchical data in multiple database tables.

下面是数据库架构在多个数据库表中存储分层数据的示例。

CREATE TABLE Vehicules (
    VId int IDENTITY(1, 1) PRIMARY KEY
    , VDescription nvarchar(20) NOT NULL
)

CREATE TABLE MotoredVehicules (
    MvId int IDENTITY(1, 1) PRIMARY KEY
    , MvVId int NOT NULL REFERENCES Vehicules (VId)
    , MvMake nvarchar(20) NOT NULL
)

CREATE TABLE MotorHP (
    MhpId int IDENTITY(1, 1) PRIMARY KEY
    , MhpMvId int NOT NULL REFERENCES MotoredVehicules (MvId)
    , MhpHP decimal(6, 2) NOT NULL
)

CREATE TABLE VehiculesWheels (
    VwId int IDENTITY(1, 1) PRIMARY KEY
    , VwVId int NOT NULL REFERENCES Vehicules (VId)
    , VwNumberOfWheels int NOT NULL
)

insert into Vehicules (VDescription) values (N'Bicycle')
GO
insert into Vehicules (VDescription) values (N'Motorcycle')
GO
insert into Vehicules (VDescription) values (N'Automobile')
GO
insert into Vehicules (VDescription) values (N'Yacht')
GO

-- Inserting the information about the vehicules that have a motor.
insert into MotoredVehicules (MvVId, MvMake) (
    select v.VId
            , N'Harley Davidson'
        from Vehicules as v
        where v.VDescription LIKE N'Motorcycle'
)
GO
insert into MotoredVehicules (MvVId, MvMake) (
    select v.VId
            , N'Sea-Ray'
        from Vehicules as v
        where v.VDescription LIKE N'Yacht'
)
GO
insert into MotoredVehicules (MvVId, MvMake) (
    select v.VId
            , N'Mercedes'
        from Vehicules as v
        where v.VDescription LIKE N'Automobile'
)
GO

-- Inserting motor HP for the motorized vehicules.
insert into MotorHP (MhpMvId, MhpHP) (
    select mv.MvId
            , 350
        from MotoredVehicules as mv
        where mv.MvMake LIKE N'Sea-Ray'
)
GO
insert into MotorHP (MhMvId, MhpHP) (
    select mv.MvId
            , 280
        from MotoredVehicules as mv
        where mv.MvMake LIKE N'Mercedes'
)
GO
insert into MotorHP (MhpMvId, MhpHP) (
    select mv.MvId
            , 930
        from MotoredVehicules as mv
        where mv.MvMake LIKE N'Harley Davidson'
)
GO

-- Inserting the number of wheels for wheeled vehicules.
insert into VehiculesWheels (VwVId, VwNumberOfWheels) (
    select v.VId
            , 2
        from Vehicules as v
        where v.VDescription IN (N'Bicycle', N'Motorcycle')
)
GO
insert into VehiculesWheels (VwVId, VwNumberOfWheels) (
    select v.VId
            , 4
        from Vehicules as v
        where v.VDescription LIKE N'Automobile'
)
GO

This relational model is not very comprehensive by itself. We could have used one only table to insert them all with all of their specifications, and let NULLthe fields with no data. However, because of some hierarchy reasons, we have created tables for specifications about different type of Vehicules. Thus, when you want to have the information about vehicule in particular, it is not very practical, as you have to join the tables every now and then you have to retrieve the information. Here, it could become practical to have a Viewlike so:

这种关系模型本身并不是很全面。我们可以只使用一张表来插入它们的所有规范，并让没有数据的字段为NULL。但是，由于某些层次结构的原因，我们为不同类型的车辆的规范创建了表格。因此，当您特别想获得有关车辆的信息时，这不是很实用，因为您必须时不时地加入表格，您必须检索信息。在这里，拥有这样的视图可能变得实用：

CREATE VIEW WheeledMotoredVehiculesView AS
    select v.VId
            , mv.MvMake
            , hp.MhpHP
            , vw.NumberOfWheels
            , v.VDescription
        from Vehicules as v
            left join MotoredVehicules as mv on mv.MvVId = v.VId
            left join MotorHP as hp on hp.MhpMvId on mv.MvId
            left join VehiculesWheels as vw on vw.VwVId = v.VId
GO

CREATE VIEW MotoredVehiculesView AS
    select v.VId
            , mv.MvMake
            , hp.MhpHP
            , v.VDescription
        from Vehicules as v
            left join MotoredVehicules as mv on mv.MvId = v.Id
            left join MotorHP as hp on hp.MhpMvId = mv.MvId
GO

CREATE VIEW WheeledVehicules AS
    select v.VId
            , vw.NumberOfWheels
            , v.VDescription
        from Vehicules as v
            left join VehiculesWheels vw on vw.VwVId = v.VId
GO

Then, instead of having to write the selectcontained within each of both above-created views each time you need to access some information data about a given vehicule, you simply query against the appropriate viewitself:

然后，每次需要访问有关给定车辆的一些信息数据时，不必编写包含在上面创建的每个视图中的选择，而只需查询适当的视图本身：

select *
    from MotoredVehiculesView

Or whatever information you need.

或者您需要的任何信息。

Disclaimer:This code has not been tested and was written directly for example purposes only. It might not work as-is.

免责声明：此代码未经测试，直接编写仅用于示例目的。它可能无法按原样工作。

I hope this helps you better understand views, somehow.

我希望这能以某种方式帮助您更好地理解视图。

回答by Tom Hubbard

Views are essentially "Queries" that you can select from without touching the main tables.

视图本质上是“查询”，您可以在不触及主表的情况下从中进行选择。

Any select query that you normally do (multiple tables, functions, etc.) could be a view.

您通常执行的任何选择查询（多个表、函数等）都可以是视图。

There are two main advantages of views.

视图有两个主要优点。

The first is that they allow you to reuse common ways of looking at the data without having to write the same complex SQL over and over (ie, modularization).

首先是它们允许您重用查看数据的常用方法，而不必一遍又一遍地编写相同的复杂 SQL（即模块化）。

The second is that you can tighten security on the source tables for the particular user while allowing them to see the data through the views.

第二个是您可以为特定用户加强源表的安全性，同时允许他们通过视图查看数据。

回答by Galwegian

See Views in SQL Serverfor a good overview.

请参阅SQL Server中的视图以获得很好的概述。

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is not stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.

视图是由一个或多个表中的列组成的虚拟表。虽然它类似于表，但它并不存储在数据库中。它是一个存储为对象的查询。因此，视图是从一个或多个表派生其数据的对象。这些表被称为基表或基础表。