使用 SQL 视图的充分理由是什么？

What is a good reason to use SQL views?

提问by Luke101

I am reading through the SQL Server 2008 bible and I am covering the views section. But the author really doesn't explain the purposeof views. What is a good use for views? Should I use them in my website and what are the benefits of them?

我正在阅读 SQL Server 2008 圣经，我正在介绍视图部分。但是作者确实没有解释视图的目的。视图有什么用？我应该在我的网站中使用它们吗？它们有什么好处？

采纳答案by DVK

Another use that none of the previous answers seem to have mentioned is easier deployment of table structure changes.

以前的答案似乎都没有提到的另一个用途是更容易部署表结构更改。

Say, you wish to retire a table (T_OLD) containing data for active users, and instead use a new table with similar data (named T_NEW) but one that has data for both active and inactive users, with one extra column active.

假设，您希望停用T_OLD包含活动用户数据的表 ( )，而是使用具有相似数据（名为T_NEW）的新表，但该表同时包含活动用户和非活动用户的数据，并有一个额外的列active。

If your system(s) have gazillion queries that do SELECT whatever FROM T_OLD WHERE whatever, you have two choices for the roll-out:

如果您的系统有无数的查询，那么SELECT whatever FROM T_OLD WHERE whatever您有两种部署选择：

1) Cold Turkey- Change the DB, and at the same time, change, test and release numerous pieces of code which contained said query. VERY hard to do (or even coordinate), very risky. Bad.

1) Cold Turkey- 更改数据库，同时更改、测试和发布包含所述查询的大量代码。很难做到（甚至协调），风险很大。坏的。

2) Gradual- change the DB by creating the T_NEWtable, dropping the T_OLDtable and instead creating a VIEWcalled T_OLDthat mimics the T_OLDtable 100% (e.g the view query is SELECT all_fields_except_active FROM T_NEW WHERE active=1).

2）逐步-通过创建改变DBT_NEW表，滴下T_OLD表和而不是创建VIEW称为T_OLD模仿的T_OLD表100％（例如，视图查询SELECT all_fields_except_active FROM T_NEW WHERE active=1）。

That would allow you to avoid releasing ANY code that currently selects from T_OLD, and do the changes to migrate code from T_OLDto T_NEWat leisure.

这将允许您避免发布当前从 中选择的任何代码T_OLD，并在闲暇时进行更改以将代码从 迁移T_OLD到T_NEW。

This is a simple example, there are others a lot more involved.

这是一个简单的例子，还有很多其他的例子。

P.S. On the other hand, you probably should have had a stored procedure APIinstead of direct queries from T_OLD, but that's not always the case.

PS 另一方面，您可能应该使用存储过程 API而不是来自 的直接查询T_OLD，但情况并非总是如此。

回答by David

(Copied from the first tutorial that came up in a Google search (link now dead), but it has all of the benefits I would have typed manually myself.)

（复制自 Google 搜索中出现的第一个教程（链接现已失效），但它具有我自己手动输入的所有好处。）

Views have the following benefits:

• Security - Views can be made accessible to users while the underlying tables are not directly accessible. This allows the DBA to give users only the data they need, while protecting other data in the same table.
• Simplicity - Views can be used to hide and reuse complex queries.
• Column Name Simplication or Clarification - Views can be used to provide aliases on column names to make them more memorable and/or meaningful.
• Stepping Stone - Views can provide a stepping stone in a "multi-level" query. For example, you could create a view of a query that counted the number of sales each salesperson had made. You could then query that view to group the sales people by the number of sales they had made.

视图有以下好处：

• 安全性 - 用户可以访问视图，而不能直接访问基础表。这允许 DBA 仅向用户提供他们需要的数据，同时保护同一表中的其他数据。
• 简单性 - 视图可用于隐藏和重用复杂的查询。
• 列名称简化或说明 - 视图可用于为列名称提供别名，使它们更容易记住和/或更有意义。
• 垫脚石 - 视图可以在“多级”查询中提供垫脚石。例如，您可以创建一个查询视图，计算每个销售人员的销售额。然后，您可以查询该视图以按销售人员的销售额对他们进行分组。

回答by user210748

Some reasons from Wikipedia:

维基百科的一些原因：

Views can provide advantages over tables:

视图可以提供优于表的优势：

1. Views can represent a subset of the data contained in a table
2. Views can join and simplify multiple tables into a single virtual table
3. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc) and presents the calculated results as part of the data
4. Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
5. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents
6. Depending on the SQL engine used, views can provide extra security
7. Views can limit the degree of exposureof a table or tables to the outer world

1. 视图可以表示表中包含的数据的子集
2. 视图可以将多个表连接并简化为单个虚拟表
3. 视图可以充当聚合表，其中数据库引擎聚合数据（总和、平均值等）并将计算结果作为数据的一部分呈现
4. 视图可以隐藏数据的复杂性；例如，视图可以显示为 Sales2000 或 Sales2001，透明地对实际基础表进行分区
5. 视图占用很少的空间来存储；数据库只包含视图的定义，而不是它呈现的所有数据的副本
6. 根据所使用的 SQL 引擎，视图可以提供额外的安全性
7. 视图可以限制一个或多个表暴露于外部世界的程度

回答by Adriaan Stander

VIEWS can be used as reusable sections of SELECT/CODE, that can be included in other selects/queries to be joined on, and use various different filters, without having to recreate the entire SELECT every time.

VIEWS 可以用作 SELECT/CODE 的可重用部分，可以包含在其他要加入的选择/查询中，并使用各种不同的过滤器，而不必每次都重新创建整个 SELECT。

This also places logic in a single location, so that you do not have to change it all over the code base.

这也将逻辑放置在一个位置，这样您就不必在整个代码库中更改它。

Have a look at

看一下

Choice Between Stored Procedures, Functions, Views, Triggers, Inline SQL

存储过程、函数、视图、触发器、内联 SQL 之间的选择

The main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins. It can also use pretty much any object in the db except for stored procedures. Views are most useful when you always need to join the same set of tables say an Order with an Order Detail to get summary calculation fields etc.

视图的主要优点是在大多数情况下它可以像表一样使用，但与表不同的是，它可以封装非常复杂的计算和常用的连接。除了存储过程之外，它还可以使用数据库中的几乎任何对象。当您总是需要加入同一组表时，视图最有用，例如带有订单详细信息的订单以获取汇总计算字段等。

回答by dkretz

A view is an abstraction layer, and it does what any good abstraction layer does, including encapsulating the database schema and protecting you from the consequences of changing internal implementation details.

视图是一个抽象层，它可以完成任何好的抽象层所做的工作，包括封装数据库模式并保护您免受更改内部实现细节的后果。

It's an interface.

这是一个接口。

回答by Nefsu

Here is one very common use of using views to constrain an entity by some criteria.

这是使用视图通过某些标准约束实体的一种非常常见的用法。

Table: USERS contains all users

表：USERS 包含所有用户

View: ACTIVE_USERS contains all users excluding those who are suspended, banned, waiting to be activated and not meeting any criteria you may choose to define in the future as part of the active requirements. This makes it unnecessary to delete any rows from your USERS table should you choose not to because ACTIVE_USERS can always hide the unwanted rows.

视图：ACTIVE_USERS 包含所有用户，不包括被暂停、禁止、等待激活以及不符合您将来可能选择定义为活动要求一部分的任何标准的用户。如果您选择不删除，则无需从您的 USERS 表中删除任何行，因为 ACTIVE_USERS 始终可以隐藏不需要的行。

This way, you can use the table in your user management pages but the rest of the application can use ACTIVE_USERS as they may be the only users that should be able to execute processes and access/modify data.

这样，您可以在用户管理页面中使用该表，但应用程序的其余部分可以使用 ACTIVE_USERS，因为他们可能是唯一应该能够执行进程和访问/修改数据的用户。

回答by TLiebe

Views can allow you to combine data from several different tables and format it (combine fields, give more meaningful field names, etc.) so that it's easier for end users. They are an abstraction of the database model. They can also be used to give users access to the data in the table without giving them direct access to the table itself.

视图可以让您组合来自多个不同表的数据并对其进行格式化（组合字段、提供更有意义的字段名称等），以便最终用户更容易使用。它们是数据库模型的抽象。它们还可用于让用户访问表中的数据，而无需直接访问表本身。

回答by Ali Adravi

Here are some of many reasons of using view rather than table directly

以下是使用视图而不是直接使用表格的众多原因中的一些

• Simplicity - Views can be used to hide complex queries.
• Security - View can hide some important information from end user by creating view on some selected columns
• Security - Secure table to change the structure of it by using VIEW.
• Redundancy - Reduce redundant code in every procedures/query by using a common view.
• Calculation - All the calculations can be done once in view query.
• Meaningful Name - Table may have name for id like tbl_org_emp_id which can alias like [Employee No] or some meaningful name.

• 简单性 - 视图可用于隐藏复杂的查询。
• 安全性 - 通过在某些选定的列上创建视图，视图可以对最终用户隐藏一些重要信息
• 安全性 - 使用 VIEW 保护表以更改其结构。
• 冗余 - 通过使用通用视图减少每个过程/查询中的冗余代码。
• 计算 - 所有计算都可以在视图查询中完成一次。
• 有意义的名称 - 表可能有像 tbl_org_emp_id 这样的 id 名称，它可以别名为 [Employee No] 或一些有意义的名称。

From imexploring.com

来自imexploring.com

回答by slugster

A small list of common reasons/uses:

常见原因/用途的小清单：

• use them to change the format or 'look' of data (i.e. you might join a first and last name together)

  perform calculations or other lookups on data

  denormalize data (extract data from several tables in to one spot)

• 使用它们来更改数据的格式或“外观”（即您可以将名字和姓氏连接在一起）

  对数据执行计算或其他查找

  非规范化数据（将多个表中的数据提取到一个位置）

回答by Dan

The Views are evil! Avoid them if possible and use only for the reason mentioned by DVK - temporary data migration.

观点是邪恶的！如果可能，请避免使用它们，并且仅出于 DVK 提到的原因使用 - 临时数据迁移。

You should understand that in a database with a 100 tables it is hard to remember the purpose of every table. Now, if you add here another 300 views it will become a complete mess. Than the 'View lovers' tend to use nested views and then use the nested views in stored procedures. I personnaly work now with a database where there are Views nested in depth 4 times! So to understand a simpliest logic of a stored procedure I have to go through all the views first.

您应该明白，在拥有 100 个表的数据库中，很难记住每个表的用途。现在，如果您在这里再添加 300 个视图，它将变得一团糟。比“视图爱好者”更倾向于使用嵌套视图，然后在存储过程中使用嵌套视图。我个人现在使用一个数据库，其中有 4 次深度嵌套的视图！因此，要了解存储过程的最简单逻辑，我必须首先浏览所有视图。