SQL 为什么要在数据库中创建视图？

Why do you create a View in a database?

提问by MedicineMan

When and Why does some one decide that they need to create a View in their database? Why not just run a normal stored procedure or select?

何时以及为什么有人决定他们需要在他们的数据库中创建一个视图？为什么不直接运行一个普通的存储过程或选择？

回答by Dave Carlile

A view provides several benefits.

视图提供了几个好处。

1. Views can hide complexity

1. 视图可以隐藏复杂性

If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table.

如果您的查询需要连接多个表，或者具有复杂的逻辑或计算，您可以将所有逻辑编码到一个视图中，然后从视图中进行选择，就像选择表一样。

2. Views can be used as a security mechanism

2. 视图可以作为一种安全机制

A view can select certain columns and/or rows from a table (or tables), and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

视图可以从一个（或多个）表中选择某些列和/或行，并在视图上而不是基础表上设置权限。这允许仅显示用户需要查看的数据。

3. Views can simplify supporting legacy code

3. 视图可以简化支持遗留代码

If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure.

如果您需要重构会破坏大量代码的表，您可以用同名视图替换该表。该视图提供与原始表完全相同的架构，而实际架构已更改。这可以防止引用该表的遗留代码被破坏，允许您在闲暇时更改遗留代码。

These are just some of the many examples of how views can be useful.

这些只是视图如何有用的众多示例中的一部分。

回答by Graeme Perrow

Among other things, it can be used for security. If you have a "customer" table, you might want to give all of your sales people access to the name, address, zipcode, etc. fields, but not credit_card_number. You can create a view that only includes the columns they need access to and then grant them access on the view.

除其他外，它还可用于安全性。如果您有一个“客户”表，您可能希望让所有销售人员都可以访问姓名、地址、邮政编码等字段，但不能访问 credit_card_number。您可以创建一个仅包含他们需要访问的列的视图，然后授予他们对该视图的访问权限。

回答by Andrew Hare

A view is an encapsulation of a query. Queries that are turned into views tend to be complicated and as such saving them as a view for reuse can be advantageous.

视图是查询的封装。转换为视图的查询往往很复杂，因此将它们保存为视图以供重用可能是有利的。

回答by cmsjr

I usually create views to de-normalize and/or aggregate data frequently used for reporting purposes.

我通常创建视图来反规范化和/或聚合经常用于报告目的的数据。

EDIT

编辑

By way of elaboration, if I were to have a database in which some of the entities were person, company, role, owner type, order, order detail, address and phone, where the person table stored both employees and contacts and the address and phone tables stored phone numbers for both persons and companies, and the development team were tasked with generating reports (or making reporting data accessible to non-developers) such as sales by employee, or sales by customer, or sales by region, sales by month, customers by state, etc I would create a set of views that de-normalized the relationships between the database entities so that a more integrated view (no pun intended) of the real world entities was available. Some of the benefits could include:

作为详细说明，如果我有一个数据库，其中一些实体是个人、公司、角色、所有者类型、订单、订单详细信息、地址和电话，其中个人表存储员工和联系人以及地址和电话表存储个人和公司的电话号码，开发团队的任务是生成报告（或使非开发人员可以访问报告数据），例如员工销售额、客户销售额、地区销售额、月销售额，按州划分的客户等我将创建一组视图，这些视图对数据库实体之间的关系进行非规范化处理，以便提供真实世界实体的更加集成的视图（没有双关语）。其中一些好处可能包括：

1. Reducing redundancy in writing queries
2. Establishing a standard for relating entities
3. Providing opportunities to evaluate and maximize performance for complex calculations and joins (e.g. indexing on Schemabound views in MSSQL)
4. Making data more accessible and intuitive to team members and non-developers.

1. 减少编写查询的冗余
2. 建立关联实体的标准
3. 提供机会来评估和最大化复杂计算和连接的性能（例如在 MSSQL 中为 Schemabound 视图建立索引）
4. 使数据对团队成员和非开发人员更易于访问和直观。

回答by HLGEM

Several reasons: If you have complicated joins, it is sometimes best to have a view so that any access will always have the joins correct and the developers don;t have to remember all the tables they might need. Typically this might be for a financial application where it would be extremely important that all financial reports are based on the same set of data.

几个原因： 如果您有复杂的连接，有时最好有一个视图，这样任何访问都将始终正确连接，而开发人员不必记住他们可能需要的所有表。通常，这可能适用于财务应用程序，其中所有财务报告都基于相同的数据集非常重要。

If you have users you want to limit the records they can ever see, you can use a view, give them access only to the view not the underlying tables and then query the view

如果您有用户想要限制他们可以看到的记录，您可以使用视图，让他们只能访问视图而不是基础表，然后查询视图

Crystal reports seems to prefer to use views to stored procs, so people who do a lot of report writing tend to use a lot of views

Crystal 报表似乎更喜欢使用视图而不是存储过程，因此编写大量报表的人倾向于使用大量视图

Views are also very useful when refactoring databases. You can often hide the change so that the old code doesn't see it by creating a view. Read on refactoring databases to see how this work as it is a very powerful way to refactor.

视图在重构数据库时也非常有用。您通常可以通过创建视图来隐藏更改，以便旧代码看不到它。阅读重构数据库以了解它是如何工作的，因为它是一种非常强大的重构方式。

回答by devuxer

The one major advantage of a view over a stored procedure is that you can use a view just like you use a table. Namely, a view can be referred to directly in the FROMclause of a query. E.g., SELECT * FROM dbo.name_of_view.

与存储过程相比，视图的一个主要优点是您可以像使用表一样使用视图。即，可以在FROM查询的子句中直接引用视图。例如，SELECT * FROM dbo.name_of_view。

In just about every other way, stored procedures are more powerful. You can pass in parameters, including outparameters that allow you effectively to return several values at once, you can do SELECT, INSERT, UPDATE, and DELETEoperations, etc. etc.

几乎在所有其他方面，存储过程都更强大。您可以传递参数，包括out参数，让你有效地在一次返回多个值，你可以做SELECT，INSERT，UPDATE，和DELETE操作，等等，等等

If you want a View's ability to query from within the FROMclause, but you also want to be able to pass in parameters, there's a way to do that too. It's called a table-valued function.

如果您希望视图能够从FROM子句中进行查询，但您还希望能够传入参数，那么也有一种方法可以做到这一点。它被称为表值函数。

Here's a pretty useful article on the topic:

这是一篇关于该主题的非常有用的文章：

http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

EDIT:By the way, this sort of raises the question, what advantage does a view have over a table-valued function? I don't have a really good answer to that, but I will note that the T-SQL syntax for creating a view is simpler than for a table-valued function, and users of your database may be more familiar with views.

编辑：顺便说一句，这种问题提出了一个问题，与表值函数相比，视图有什么优势？我对此没有很好的答案，但我会注意到创建视图的 T-SQL 语法比创建表值函数更简单，并且您的数据库用户可能更熟悉视图。

回答by Joseph

It can function as a good "middle man" between your ORM and your tables.

它可以充当 ORM 和表之间的良好“中间人”。

Example:

例子：

We had a Person table that we needed to change the structure on it so the column SomeColumn was going to be moved to another table and would have a one to many relationship to.

我们有一个 Person 表，我们需要改变它的结构，所以列 SomeColumn 将被移动到另一个表，并与它有一对多的关系。

However, the majority of the system, with regards to the Person, still used the SomeColumn as a single thing, not many things. We used a view to bring all of the SomeColumns together and put it in the view, which worked out nicely.

然而，系统的大部分，关于 Person，仍然使用 SomeColumn 作为一个单一的东西，而不是很多东西。我们使用一个视图将所有 SomeColumns 组合在一起并将其放入视图中，效果很好。

This worked because the data layer had changed, but the business requirement hadn't fundamentally changed, so the business objects didn't need to change. If the business objects had to change I don't think this would have been a viable solution, but views definitely function as a good mid point.

这是有效的，因为数据层发生了变化，但业务需求并没有从根本上发生变化，因此业务对象不需要发生变化。如果业务对象必须改变，我认为这不是一个可行的解决方案，但视图绝对是一个很好的中间点。

回答by Shaharban T A

To Focus on Specific DataViews allow users to focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary data can be left out of the view. This also increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table. For more information about using views for security purposes, see Using Views as Security Mechanisms.

专注于特定数据视图允许用户专注于他们感兴趣的特定数据以及他们负责的特定任务。不需要的数据可以被排除在视图之外。这也增加了数据的安全性，因为用户只能看到视图中定义的数据，而看不到基础表中的数据。有关出于安全目的使用视图的更多信息，请参阅将视图用作安全机制。

To Simplify Data ManipulationViews can simplify how users manipulate data. You can define frequently used joins, projections, UNION queries, and SELECT queries as views so that users do not have to specify all the conditions and qualifications each time an additional operation is performed on that data. For example, a complex query that is used for reporting purposes and performs subqueries, outer joins, and aggregation to retrieve data from a group of tables can be created as a view. The view simplifies access to the data because the underlying query does not have to be written or submitted each time the report is generated; the view is queried instead. For more information about manipulating data.

简化数据操作视图可以简化用户操作数据的方式。您可以将常用的连接、投影、UNION 查询和 SELECT 查询定义为视图，这样用户就不必在每次对该数据执行附加操作时指定所有条件和限定。例如，用于报告目的并执行子查询、外部联接和聚合以从一组表中检索数据的复杂查询可以创建为视图。视图简化了对数据的访问，因为不必在每次生成报告时编写或提交底层查询；而是查询视图。有关操作数据的更多信息。

You can also create inline user-defined functions that logically operate as parameterized views, or views that have parameters in WHERE-clause search conditions. For more information, see Inline User-defined Functions.

您还可以创建在逻辑上作为参数化视图或在 WHERE 子句搜索条件中具有参数的视图的内联用户定义函数。有关更多信息，请参阅内联用户定义函数。

To Customize DataViews allow different users to see data in different ways, even when they are using the same data concurrently. This is particularly advantageous when users with many different interests and skill levels share the same database. For example, a view can be created that retrieves only the data for the customers with whom an account manager deals. The view can determine which data to retrieve based on the login ID of the account manager who uses the view.

自定义数据视图允许不同的用户以不同的方式查看数据，即使他们同时使用相同的数据。当具有许多不同兴趣和技能水平的用户共享同一个数据库时，这尤其有利。例如，可以创建一个视图，该视图仅检索与客户经理打交道的客户的数据。该视图可以根据使用该视图的客户经理的登录 ID 来确定要检索哪些数据。

To Export and Import DataViews can be used to export data to other applications. For example, you may want to use the stores and sales tables in the pubs database to analyze sales data using Microsoft? Excel. To do this, you can create a view based on the stores and sales tables. You can then use the bcp utility to export the data defined by the view. Data can also be imported into certain views from data files using the bcp utility or BULK INSERT statement providing that rows can be inserted into the view using the INSERT statement. For more information about the restrictions for copying data into views, see INSERT. For more information about using the bcp utility and BULK INSERT statement to copy data to and from a view, see Copying To or From a View.

导出和导入数据视图可用于将数据导出到其他应用程序。例如，您可能想使用 pubs 数据库中的商店和销售表来使用 Microsoft 分析销售数据？优秀。为此，您可以创建基于商店和销售表的视图。然后可以使用 bcp 实用程序导出视图定义的数据。还可以使用 bcp 实用程序或 BULK INSERT 语句将数据从数据文件导入某些视图，前提是可以使用 INSERT 语句将行插入到视图中。有关将数据复制到视图的限制的更多信息，请参阅 INSERT。有关使用 bcp 实用程序和 BULK INSERT 语句将数据复制到视图和从视图复制数据的详细信息，请参阅复制到视图或从视图复制。

To Combine Partitioned DataThe Transact-SQL UNION set operator can be used within a view to combine the results of two or more queries from separate tables into a single result set. This appears to the user as a single table called a partitioned view. For example, if one table contains sales data for Washington, and another table contains sales data for California, a view could be created from the UNION of those tables. The view represents the sales data for both regions. To use partitioned views, you create several identical tables, specifying a constraint to determine the range of data that can be added to each table. The view is then created using these base tables. When the view is queried, SQL Server automatically determines which tables are affected by the query and references only those tables. For example, if a query specifies that only sales data for the state of Washington is required, SQL Server reads only the table containing the Washington sales data; no other tables are accessed.

合并分区数据Transact-SQL UNION 集合运算符可在视图中使用，以将来自不同表的两个或多个查询的结果合并为一个结果集。这在用户看来是一个称为分区视图的表。例如，如果一个表包含华盛顿的销售数据，而另一个表包含加利福尼亚的销售数据，则可以从这些表的 UNION 创建一个视图。该视图表示两个地区的销售数据。要使用分区视图，您需要创建几个相同的表，指定一个约束来确定可以添加到每个表的数据范围。然后使用这些基表创建视图。查询视图时，SQL Server 自动确定哪些表受查询影响并仅引用这些表。例如，如果查询指定仅需要华盛顿州的销售数据，则 SQL Server 仅读取包含华盛顿州销售数据的表；没有访问其他表。

Partitioned views can be based on data from multiple heterogeneous sources, such as remote servers, not just tables in the same database. For example, to combine data from different remote servers each of which stores data for a different region of your organization, you can create distributed queries that retrieve data from each data source, and then create a view based on those distributed queries. Any queries read only data from the tables on the remote servers that contains the data requested by the query; the other servers referenced by the distributed queries in the view are not accessed.

分区视图可以基于来自多个异构源的数据，例如远程服务器，而不仅仅是同一数据库中的表。例如，要合并来自不同远程服务器的数据，每个服务器都存储组织不同区域的数据，您可以创建从每个数据源检索数据的分布式查询，然后基于这些分布式查询创建视图。任何查询仅从包含查询请求的数据的远程服务器上的表中读取数据；视图中分布式查询引用的其他服务器不被访问。

When you partition data across multiple tables or multiple servers, queries accessing only a fraction of the data can run faster because there is less data to scan. If the tables are located on different servers, or on a computer with multiple processors, each table involved in the query can also be scanned in parallel, thereby improving query performance. Additionally, maintenance tasks, such as rebuilding indexes or backing up a table, can execute more quickly. By using a partitioned view, the data still appears as a single table and can be queried as such without having to reference the correct underlying table manually.

当您跨多个表或多个服务器对数据进行分区时，仅访问一小部分数据的查询可以运行得更快，因为要扫描的数据较少。如果表位于不同的服务器上，或者在多处理器的计算机上，也可以并行扫描查询中涉及的每个表，从而提高查询性能。此外，维护任务（例如重建索引或备份表）可以更快地执行。通过使用分区视图，数据仍然显示为单个表，并且可以这样查询，而无需手动引用正确的基础表。

Partitioned views are updatable if either of these conditions is met: An INSTEAD OF trigger is defined on the view with logic to support INSERT, UPDATE, and DELETE statements.

如果满足以下任一条件，则分区视图是可更新的： 在具有支持 INSERT、UPDATE 和 DELETE 语句的逻辑的视图上定义了 INSTEAD OF 触发器。

Both the view and the INSERT, UPDATE, and DELETE statements follow the rules defined for updatable partitioned views. For more information, see Creating a Partitioned View.

视图和 INSERT、UPDATE 和 DELETE 语句都遵循为可更新分区视图定义的规则。有关更多信息，请参阅创建分区视图。

https://technet.microsoft.com/en-us/library/aa214282(v=sql.80).aspx#sql:join

https://technet.microsoft.com/en-us/library/aa214282(v=sql.80).aspx#sql:join

回答by KM.

Here are two common reasons:

以下是两个常见的原因：

You can use it for security. Grant no permissions on the main table and create views that limits column or row access and grant permissions to users to see the view.

您可以将其用于安全性。不授予对主表的权限，并创建限制列或行访问的视图，并授予用户查看视图的权限。

You can use use it for convenience. Join together some tables that you use together all the time in the view. This can make queries consistent and easier.

为方便起见，您可以使用它。将您在视图中一直使用的一些表连接在一起。这可以使查询一致且更容易。

回答by RC.

There is more than one reason to do this. Sometimes makes common join queries easy as one can just query a table name instead of doing all the joins.

这样做的原因不止一个。有时使常见的连接查询变得简单，因为人们可以只查询一个表名而不是执行所有连接。

Another reason is to limit the data to different users. So for instance:

另一个原因是将数据限制为不同的用户。所以例如：

Table1: Colums - USER_ID;USERNAME;SSN

表 1：列 - USER_ID;USERNAME;SSN

Admin users can have privs on the actual table, but users that you don't want to have access to say the SSN, you create a view as

管理员用户可以在实际表上拥有权限，但是您不想访问 SSN 的用户，您可以创建一个视图作为

CREATE VIEW USERNAMES AS SELECT user_id, username FROM Table1;

Then give them privs to access the view and not the table.

然后给他们权限来访问视图而不是表。