视图有什么用？
我只是想大致了解 RDBMS 中视图的用途。 也就是说，我知道什么是视图以及如何创建视图。 我也知道我过去用它们做什么。
但我想确保我彻底了解视图的用途和视图不应该用途。 更具体地说：
- 视图有什么用？
- 是否存在某些情况下，当您不应该使用视图时却很想使用视图？
- 为什么要使用视图来代替表值函数之类的东西，反之亦然？
- 是否存在某些视图可能有用但乍一看并不明显的情况？
（郑重声明，其中一些问题是故意幼稚的。这在一定程度上是概念检查。）
I'm just trying to get a general idea of what views are used for in RDBMSes. That is to say, I know what a view is and how to make one. I also know what I've used them for in the past.
But I want to make sure I have a thorough understanding of what a view is useful for and what a view shouldn't be useful for. More specifically:
- What is a view useful for?
- Are there any situations in which it is tempting to use a view when you shouldn't use one?
- Why would you use a view in lieu of something like a table-valued function or vice versa?
- Are there any circumstances that a view might be useful that aren't apparent at first glance?
(And for the record, some of these questions are intentionally naive. This is partly a concept check.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（14）
在某种程度上，视图就像一个界面。 您可以根据需要更改底层表结构，但视图提供了一种不必更改代码的方法。
视图是为报告编写者提供简单内容的好方法。 如果您的业务用户想要从 Crystal Reports 之类的工具访问数据，您可以在他们的帐户中为他们提供一些简化数据的视图，甚至可以为他们将数据非规范化。
In a way, a view is like an interface. You can change the underlying table structure all you want, but the view gives a way for the code to not have to change.
Views are a nice way of providing something simple to report writers. If your business users want to access the data from something like Crystal Reports, you can give them some views in their account that simplify the data -- maybe even denormalize it for them.
1) 视图有什么用？
2 ) 是否存在某些情况下，当您不应该使用视图时却很想使用视图？
3) 为什么要使用视图来代替表值函数之类的东西，反之亦然？
4）是否有任何情况下，视图可能有用但乍一看并不明显？
1) What is a view useful for?
2) Are there any situations in which it is tempting to use a view when you shouldn't use one?
3) Why would you use a view in lieu of something like a table-valued function or vice versa?
4) Are there any circumstances that a view might be useful that aren't apparent at first glance?
视图可用于提供安全性（即：用户可以访问仅访问表中某些列的视图），视图可以为更新、插入等提供额外的安全性。视图还提供了一种为列名别名的方法（如sp's），但视图更多的是与实际表的隔离。
Views can be used to provide security (ie: users can have access to views that only access certain columns in a table), views can provide additional security for updates, inserts, etc. Views also provide a way to alias column names (as do sp's) but views are more of an isolation from the actual table.
从某种意义上说，视图非规范化了。 有时需要非规范化才能以更有意义的方式提供数据。 这就是许多应用程序通过其对象中的域建模所做的事情。 它们有助于以更符合企业观点的方式呈现数据。
In a sense views denormalize. Denormalization is sometimes necessary to provide data in a more meaningful manner. This is what a lot of applications do anyway by way of domain modeling in their objects. They help present the data in a way that more closely matches a business' perspective.
除了其他人所说的之外，视图对于从应用程序中删除更复杂的 SQL 查询也很有用。
举个例子，而不是在应用程序中执行以下操作：
您可以将其抽象为视图：
并在应用程序代码中，只需：
此外，如果数据结构发生变化，您无需更改应用程序代码、重新编译和重新部署。 您只需更改数据库中的视图即可。
In addition to what the others have stated, views can also be useful for removing more complecated SQL queries from the application.
As an example, instead of in an application doing:
You could abstract that to a view:
and in the app code, simply have:
Also if the data structures ever change, you won't have to change the app code, recompile, and redeploy. you would just change the view in the db.
视图隐藏了数据库的复杂性。 它们的作用有很多，并且在很多情况下都很有用，但是如果您的用户被允许编写自己的查询和报告，您可以使用它们作为保障措施，以确保他们不会提交设计不良的内容带有令人讨厌的笛卡尔连接的查询会导致数据库服务器瘫痪。
Views hide the database complexity. They are great for a lot of reasons and are useful in a lot of situations, but if you have users that are allowed to write their own queries and reports, you can use them as a safeguard to make sure they don't submit badly designed queries with nasty cartesian joins that take down your database server.
OP 询问是否存在使用视图的情况，但这是不合适的。
您不想使用视图来代替复杂的连接。 也就是说，不要让您将问题分解为更小部分的过程编程习惯导致您使用连接在一起的多个视图而不是一个较大的连接。 这样做会降低数据库引擎的效率，因为它本质上是在执行多个单独的查询，而不是一个较大的查询。
例如，假设您必须将表 A、B、C 和 D 连接在一起。 您可能想要从表 A 和表 A 中创建视图。 B 和 C & 外面的景色 D、然后将两个视图连接在一起。 最好只在一个查询中连接 A、B、C 和 D。
The OP asked if there were situations where it might be tempting to use a view, but it's not appropriate.
What you don't want to use a view for is a substitute for complex joins. That is, don't let your procedural programming habit of breaking a problem down into smaller pieces lead you toward using several views joined together instead of one larger join. Doing so will kill the database engine's efficiency since it's essentially doing several separate queries rather than one larger one.
For example, let's say you have to join tables A, B, C, and D together. You may be tempted to make a view out of tables A & B and a view out of C & D, then join the two views together. It's much better to just join A, B, C, and D in one query.
视图可以集中或合并数据。 我所在的地方，我们在几个不同的链接服务器上有许多不同的数据库。 每个数据库保存不同应用程序的数据。 其中一些数据库保存与许多不同应用程序相关的信息。 在这种情况下，我们要做的是在该应用程序的数据库中创建一个视图，该视图仅从真正存储数据的数据库中提取数据，以便我们编写的查询看起来不像是在跨不同的数据库。
Views can centralize or consolidate data. Where I'm at we have a number of different databases on a couple different linked servers. Each database holds data for a different application. A couple of those databases hold information that are relavent to a number of different applications. What we'll do in those circumstances is create a view in that application's database that just pulls data from the database where the data is really stored, so that the queries we write don't look like they're going across different databases.
到目前为止的反应是正确的——视图有利于提供安全性、非规范化（尽管如果做错了会带来很多痛苦）、数据模型抽象等。
此外，视图通常用于实现业务逻辑（一个失效的业务逻辑）。 user 是过去 40 天内未登录的用户，诸如此类）。
The responses so far are correct -- views are good for providing security, denormalization (although there is much pain down that road if done wrong), data model abstraction, etc.
In addition, views are commonly used to implement business logic (a lapsed user is a user who has not logged in in the last 40 days, that sort of thing).
视图在 SQL 脚本中保存了大量重复的复杂 JOIN 语句。 您可以将一些复杂的 JOIN 封装在某个视图中，并在需要时在 SELECT 语句中调用它。 有时，这比在每个查询中编写连接语句更方便、直接且更容易。
Views save a lot of repeated complex JOIN statements in your SQL scripts. You can just encapsulate some complex JOIN in some view and call it in your SELECT statement whenever needed. This would sometimes be handy, straight forward and easier than writing out the join statements in every query.
视图只是一个存储的、命名的 SELECT 语句。 将视图想象成库函数。
A view is simply a stored, named SELECT statement. Think of views like library functions.
我想强调一下使用视图进行报告。 通常，规范化数据库表以提高性能（尤其是编辑和插入数据（OLTP 使用））与反规范化以减少报告和分析查询的表连接数量（OLAP 使用）之间存在冲突。 当然，OLTP 通常会获胜，因为数据输入必须具有最佳性能。 然后，为了获得最佳报告性能，创建视图可以帮助满足两类用户（数据输入者和报告查看者）的需求。
I wanted to highlight the use of views for reporting. Often, there is a conflict between normalizing the database tables to speed up performance, especially for editing and inserting data (OLTP uses), and denormalizing to reduce the number of table joins for queries for reporting and analysis (OLAP uses). Of necessity, OLTP usually wins, because data entry must have optimal performance. Creating views, then, for optimal reporting performance, can help to satisfy both classes of users (data entry and report viewers).
我记得一个很长的 SELECT 涉及多个 UNION。 每个 UNION 都包含一个价格表的联接，该价格表是由 SELECT 动态创建的，而 SELECT 本身相当长且难以理解。 我认为制定价格表是一个好主意。 它会将整个 SELECT 时间缩短大约一半。
我不知道数据库是否会评估视图一次，或者每次调用一次。 有人知道吗？ 如果是前者，使用视图会提高性能。
I remember a very long SELECT which involved several UNIONs. Each UNION included a join to a price table which was created on the fly by a SELECT that was itself fairly long and hard to understand. I think it would have been a good idea to have a view that to create the price table. It would have shortened the overall SELECT by about half.
I don't know if the DB would evaluate the view once, or once each time in was invoked. Anyone know? If the former, using a view would improved performance.
任何时候你需要 [my_interface] != [user_interface]。
示例：
表 A：
视图：
这是一种可以向客户隐藏 id 并同时将信息重命名为更详细名称的方法。
该视图将使用基础索引作为主键 id，因此您不会看到性能损失，只是更好地抽象了选择查询。
Anytime you need [my_interface] != [user_interface].
Example:
TABLE A:
VIEW for TABLE A:
this is a way you might hide the id from the customer and rename the info to a more verbose name both at once.
The view will use underlying index for primary key id, so you won't see a performance loss, just better abstraction of the select query.