Oh there are many differences you will need to consider
Views for selection:
Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
Views can model complex joins easily.
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
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.
Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.
Views for insertion/updates:
You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view
Drawbacks
You lose information about relations (primary keys, foreign keys)
It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you
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
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.
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.
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.
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.
Views can represent a subset of the data contained in a table.
Views can limit the degree of exposure of 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 tables into 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 complexity of 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.
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)
您应该设计表而不考虑视图。 除了保存连接和条件之外，视图确实具有性能优势：SQL Server 可以在视图中计算并保存其执行计划，因此比“即时”SQL 语句更快。 View 还可以简化您在现场级别的用户访问方面的工作。
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.
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)
发布评论
评论（8）
哦，您需要考虑许多差异
用于选择的视图：
插入/更新视图：
缺点
Oh there are many differences you will need to consider
Views for selection:
Views for insertion/updates:
Drawbacks
视图可以：
并且您不应该设计表来匹配视图。您的基本模型应该关注数据的高效存储和检索。视图在某种程度上是一种工具，它允许您抽象复杂性，从而减轻高效、规范化模型所产生的复杂性。
另外，问“使用视图相对于表有什么优点？”并不是一个很好的比较。你不能没有表，但你可以没有视图。它们各自存在的原因截然不同。表是具体的模型，视图是抽象的视图。
Views can:
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.
当您需要确保每次都遵循复杂的逻辑时，视图是可以接受的。例如，我们有一个视图可以创建所有财务报告所需的原始数据。通过让所有报告都使用此视图，每个人都可以使用相同的数据集，而不是一个报告使用一组连接，而另一个报告忘记使用给出不同结果的一组连接。
当您想要将用户限制为特定的数据子集时，视图是可以接受的。例如，如果您不删除记录，而仅将当前记录标记为活动记录，将旧版本标记为非活动记录，则您希望使用一个视图来仅选择活动记录。这可以防止人们忘记在查询中放入 where 子句并得到错误的结果。
视图可用于确保用户只能访问一组记录 - 例如，特定客户端的表视图和表上没有安全权限可能意味着该客户端的用户只能看到数据对于那个客户。
重构数据库时视图非常有用。
当您使用视图调用视图时，视图是不可接受的，这可能会导致糟糕的性能（至少在 SQL Server 中）。我们几乎失去了一个价值数百万美元的客户，因为有人选择以这种方式抽象数据库，并且性能非常糟糕并且经常超时。我们也必须支付修复费用，而不是客户，因为性能问题完全是我们的错。当视图调用视图时，它们必须完全生成底层视图。我看到过这样的情况：视图调用视图，视图调用视图，生成了数百万条记录，以便查看用户最终需要的三个记录。我记得其中一个视图花了 8 分钟来对记录进行简单的计数（*）。视图调用视图是一个非常糟糕的主意。
使用视图来更新记录通常是一个坏主意，因为通常您只能更新同一个表中的字段（同样这是 SQL Server，其他数据库可能会有所不同）。如果是这种情况，无论如何直接更新表更有意义，以便您知道哪些字段可用。
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.
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.
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.
根据Wikipedia，
视图比表具有许多优势：
视图可以限制基础表对外部世界的暴露程度：给定用户可能有权查询视图，但拒绝访问基表的其余部分。
视图可以将多个表连接并简化为一个虚拟表。
视图可以充当聚合表，其中数据库引擎聚合数据（总和、平均值等）并将计算结果作为数据的一部分呈现。
视图可以隐藏数据的复杂性。例如，视图可以显示为 Sales2000 或 Sales2001，透明地对实际基础表进行分区。
视图需要很少的空间来存储；数据库只包含视图的定义，而不是它所呈现的所有数据的副本。
视图可以提供额外的安全性，具体取决于所使用的 SQL 引擎。
According to Wikipedia,
Views can provide many advantages over tables:
Views can limit the degree of exposure of 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 tables into 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 complexity of 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.
当您需要从多个表中进行选择或仅获取表的子集时，视图会很方便。
您应该以这样的方式设计表，使您的数据库良好标准化（重复最少）。这可能会使查询有些困难。
视图具有一定的分离性，允许您以不同于存储方式的方式查看表中的数据。
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 view the data in the tables differently than they are stored.
常见的做法是隐藏视图中的联接，以向用户呈现更加非规范化的数据模型。其他用途涉及安全性（例如通过隐藏某些列和/或行）或性能（在物化视图的情况下）
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)
您应该设计表而不考虑视图。
除了保存连接和条件之外，视图确实具有性能优势：SQL Server 可以在视图中计算并保存其执行计划，因此比“即时”SQL 语句更快。
View 还可以简化您在现场级别的用户访问方面的工作。
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.
首先，顾名思义，视图是不可变的。那是因为视图只不过是根据数据库中存储的查询创建的虚拟表。
因此，视图具有一些特征：
，因此存在大量视图比表更适合的用例，只需考虑仅在网站上显示活跃用户即可。视图会更好，因为您只对数据库中实际存在的数据子集（活动和非活动用户）进行操作，
请查看此 文章
希望这有帮助..
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:
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..