SQL 何时使用视图而不是表？

When to use a View instead of a Table?

提问by bevacqua

When should a View actually be used over an actual Table? What gains should I expect this to produce?

什么时候应该在实际表上实际使用视图？我应该期望这会产生什么收益？

Overall, what are the advantages of using a view over a table? Shouldn't I design the table in the way the view should look like in the first place?

总的来说，在表上使用视图有什么好处？我不应该按照视图最初的样子设计表格吗？

采纳答案by Lukas Eder

Oh there are many differences you will need to consider

哦，您需要考虑许多差异

Views for selection:

供选择的视图：

1. Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
2. Views can model complex joins easily.
3. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT function or many other things
4. You can easily manage your GRANTS directly on views, rather than the actual tables. It's easier to manage if you know a certain user may only access a view.
5. Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.

1. 视图提供对表的抽象。您可以在视图中轻松添加/删除字段，而无需修改基础架构
2. 视图可以轻松地对复杂的连接进行建模。
3. 视图可以对您隐藏特定于数据库的内容。例如，如果您需要使用 Oracles SYS_CONTEXT 函数或许多其他东西进行一些检查
4. 您可以直接在视图而不是实际表上轻松管理您的 GRANTS。如果您知道某个用户只能访问一个视图，则管理起来会更容易。
5. 视图可以帮助您实现向后兼容性。您可以更改底层架构，但视图可以对某个客户端隐藏这些事实。

Views for insertion/updates:

插入/更新的视图：

1. You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view

1. 您可以通过在视图中直接使用 Oracle 的“WITH CHECK OPTION”子句等功能来处理视图的安全问题

Drawbacks

缺点

1. You lose information about relations (primary keys, foreign keys)
2. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you

1. 您丢失了有关关系的信息（主键、外键）
2. 您是否能够插入/更新视图并不明显，因为该视图对您隐藏了其底层连接

回答by Paul Sasik

Views can:

视图可以：

• Simplify a complex table structure
• Simplify your security model by allowing you to filter sensitive data and assign permissions in a simpler fashion
• Allow you to change the logic and behavior without changing the output structure (the output remains the same but the underlying SELECT could change significantly)
• Increase performance (Sql Server Indexed Views)
• Offer specific query optimization with the view that might be difficult to glean otherwise

• 简化复杂的表结构
• 通过允许您以更简单的方式过滤敏感数据和分配权限来简化您的安全模型
• 允许您在不更改输出结构的情况下更改逻辑和行为（输出保持不变，但底层 SELECT 可能发生显着变化）
• 提高性能（Sql Server 索引视图）
• 使用可能难以收集的视图提供特定的查询优化

And you should not design tables to match views. Your base model should concern itself with efficient storage and retrieval of the data. Views are partly a tool that mitigates the complexities that arise from an efficient, normalized model by allowing you to abstract that complexity.

而且你不应该设计表，以匹配的观点。您的基本模型应该关注数据的高效存储和检索。视图在一定程度上是一种工具，它通过允许您抽象复杂性来减轻由高效、规范化模型产生的复杂性。

Also, asking "what are the advantages of using a view over a table? " is not a great comparison. You can't go without tables, but you can do without views. They each exist for a very different reason. Tables are the concrete model and Views are an abstracted, well, View.

此外，询问“在表格上使用视图的优势是什么？”并不是一个很好的比较。你不能没有表格，但你可以没有视图。他们每个人的存在都有一个非常不同的原因。表是具体模型，视图是抽象的视图。

回答by HLGEM

Views are acceptable when you need to ensure that complex logic is followed every time. For instance, we have a view that creates the raw data needed for all financial reporting. By having all reports use this view, everyone is working from the same data set, rather than one report using one set of joins and another forgetting to use one which gives different results.

当您需要确保每次都遵循复杂的逻辑时，视图是可以接受的。例如，我们有一个视图可以创建所有财务报告所需的原始数据。通过让所有报告都使用此视图，每个人都在使用相同的数据集工作，而不是一个报告使用一组连接，而另一个则忘记使用会产生不同结果的连接。

Views are acceptable when you want to restrict users to a particular subset of data. For instance, if you do not delete records but only mark the current one as active and the older versions as inactive, you want a view to use to select only the active records. This prevents people from forgetting to put the where clause in the query and getting bad results.

当您希望将用户限制为特定的数据子集时，视图是可以接受的。例如，如果您不删除记录而仅将当前记录标记为活动记录，而将旧版本标记为非活动记录，则您希望使用一个视图来仅选择活动记录。这可以防止人们忘记在查询中放置 where 子句而得到不好的结果。

Views can be used to ensure that users only have access to a set of records - for instance, a view of the tables for a particular client and no security rights on the tables can mean that the users for that client can only ever see the data for that client.

视图可用于确保用户只能访问一组记录 - 例如，特定客户端的表视图并且表上没有安全权限可能意味着该客户端的用户只能看到数据对于那个客户。

Views are very helpful when refactoring databases.

视图在重构数据库时非常有用。

Views are not acceptable when you use views to call views which can result in horrible performance (at least in SQL Server). We almost lost a multimillion dollar client because someone chose to abstract the database that way and performance was horrendous and timeouts frequent. We had to pay for the fix too, not the client, as the performance issue was completely our fault. When views call views, they have to completely generate the underlying view. I have seen this where the view called a view which called a view and so many millions of records were generated in order to see the three the user ultimately needed. I remember one of these views took 8 minutes to do a simple count(*) of the records. Views calling views are an extremely poor idea.

当您使用视图调用视图时，视图是不可接受的，这可能会导致糟糕的性能（至少在 SQL Server 中）。我们几乎失去了一个价值数百万美元的客户，因为有人选择以这种方式抽象数据库，而且性能非常糟糕，而且经常超时。我们也必须为修复付费，而不是客户，因为性能问题完全是我们的错。当视图调用视图时，它们必须完全生成底层视图。我已经看到了这一点，其中视图调用了一个调用视图的视图，并且生成了数百万条记录，以便查看用户最终需要的三个记录。我记得其中一个视图花了 8 分钟来对记录进行简单的计数 (*)。视图调用视图是一个非常糟糕的主意。

Views are often a bad idea to use to update records as usually you can only update fields from the same table (again this is SQL Server, other databases may vary). If that's the case, it makes more sense to directly update the tables anyway so that you know which fields are available.

使用视图来更新记录通常是一个坏主意，因为通常您只能更新同一个表中的字段（同样这是 SQL Server，其他数据库可能会有所不同）。如果是这种情况，无论如何直接更新表更有意义，以便您知道哪些字段可用。

回答by Oded

Views are handy when you need to select from several tables, or just to get a subset of a table.

当您需要从多个表中进行选择，或者只是为了获取表的一个子集时，视图非常方便。

You should design your tables in such a way that your database is well normalized(minimum duplication). This can make querying somewhat difficult.

您应该以数据库良好规范化（最少重复）的方式设计您的表。这会使查询变得有些困难。

Views are a bit of separation, allowing you to viewthe data in the tables differently than they are stored.

视图有点分离，允许您以不同于存储的方式查看表中的数据。

回答by TToni

A common practice is to hide joins in a view to present the user a more denormalized data model. Other uses involve security (for example by hiding certain columns and/or rows) or performance (in case of materialized views)

一种常见的做法是在视图中隐藏连接，以向用户呈现更加非规范化的数据模型。其他用途涉及安全性（例如通过隐藏某些列和/或行）或性能（在物化视图的情况下）

回答by Patrick Honorez

You should design your table WITHOUT considering the views.
Apart from saving joins and conditions, Views do have a performance advantage: SQL Server may calculate and save its execution plan in the view, and therefore make it faster than "on the fly" SQL statements.
View may also ease your work regarding user access at field level.

您应该在不考虑视图的情况下设计您的表格。
除了保存连接和条件外，视图确实具有性能优势：SQL Server 可以在视图中计算并保存其执行计划，因此比“动态”SQL 语句更快。
View 还可以简化您在字段级别进行用户访问的工作。

回答by fasseg

First of all as the name suggests a view is immutable. thats because a view is nothing other than a virtual table created from a stored query in the DB. Because of this you have some characteristics of views:

首先，顾名思义，视图是不可变的。那是因为视图只不过是从数据库中存储的查询创建的虚拟表。因此，您具有视图的一些特征：

• you can show only a subset of the data
• you can join multiple tables into a single view
• you can aggregate data in a view (select count)
• view dont actually hold data, they dont need any tablespace since they are virtual aggregations of underlying tables

• 您只能显示数据的一个子集
• 您可以将多个表连接到一个视图中
• 您可以在视图中聚合数据（选择计数）
• 视图实际上不保存数据，它们不需要任何表空间，因为它们是基础表的虚拟聚合

so there are a gazillion of use cases for which views are better fitted than tables, just think about only displaying active users on a website. a view would be better because you operate only on a subset of the data which actually is in your DB (active and inactive users)

所以有无数的用例比表格更适合视图，想想只在网站上显示活跃用户。视图会更好，因为您仅对实际位于数据库中的数据子集（活动和非活动用户）进行操作

check out this article

看看这篇文章

hope this helped..

希望这有帮助..

回答by Loukan ElKadi

According to Wikipedia,

根据维基百科，

Views can provide many advantages over tables:

与表相比，视图可以提供许多优势：

• Views can represent a subset of the datacontained in a table.

• Views can limit the degree of exposureof the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

• Views can join and simplify multiple tablesinto a single virtual table.

• 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.

• Views can hide the complexityof data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.

• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.

• Views can provide extra security, depending on the SQL engine used.

• 视图可以表示表中包含的数据的子集。

• 视图可以限制底层表暴露于外部世界的程度：给定用户可能有权查询视图，但拒绝访问基表的其余部分。

• 视图可以将多个表连接并简化为单个虚拟表。

• 视图可以充当聚合表，其中数据库引擎聚合数据（总和、平均值等）并将计算结果作为数据的一部分呈现。

• 视图可以隐藏数据的复杂性。例如，视图可以显示为 Sales2000 或 Sales2001，透明地对实际基础表进行分区。

• 视图占用很少的空间来存储；数据库只包含视图的定义，而不是它所呈现的所有数据的副本。

• 视图可以提供额外的安全性，具体取决于所使用的 SQL 引擎。