sql关于视图的sql
SQL views are another powerful database object we have at our disposal. In the previous two articles of this series, we’ve talked about user-defined functions and user-defined procedures and showed simple examples of how to use them. Today, we’ll do the same for the SQL views.
SQL视图是我们可以使用的另一个强大的数据库对象。 在本系列的前两篇文章中，我们讨论了用户定义的函数和用户定义的过程，并展示了如何使用它们的简单示例。 今天，我们将对SQL视图执行相同的操作。
该模型 (The Model)
The first thing we’ll do is to remind ourselves of the database model we’ll be using today (and we’re using throughout this series):
我们要做的第一件事是提醒自己我们今天将要使用的数据库模型（并且在整个系列中都会使用）：
We’ll use it to create queries over a single table, and that will be the country table.
我们将使用它在单个表上创建查询，该表将成为国家/地区表。
什么是数据库视图？ (What Are Database Views?)
We’ve talked about user-defined functions and stored procedures in the previous two articles. If you’re into programming, I guess you’ve met them or at least their counterparts in some programming languages because they are pretty common as a concept and widely used. That is not the situation with views.
在前两篇文章中，我们讨论了用户定义的函数和存储过程。 如果您正在编程，我想您已经遇到了他们，或者至少在某些编程语言中遇到了他们，因为它们在概念上非常普遍并且被广泛使用。 观点并非如此。
They are much more specific to databases. The main idea is to create a database object where we’ll “store” the result of the query. The word “store” is maybe not the best one. We’ll store this structure and the query it contains, and we’ll run this query when we reference this structure.
它们更特定于数据库。 主要思想是创建一个数据库对象，我们将在其中“存储”查询结果。 “商店”一词可能不是最好的。 我们将存储此结构及其包含的查询，并在引用此结构时运行此查询。
If you ask yourself why, there are a few good reasons, and we’ll talk about the advantages and disadvantages later.
如果您问自己为什么，有几个充分的理由，我们稍后将讨论其优缺点。
If you ask yourself, why we have a new database object to store just a single query, and why not to write that query or use a procedure, this could be a short answer to that question. It’s important to understand that when you have a database view, the query is stored in that view and you don’t need to write it from scratch. Also, when compared to procedures, views are generally simpler (you don’t pass parameters, you have only one select statement), and you can do some operations procedures that wouldn’t allow you to do it (insert, update, delete).
如果您问自己，为什么我们有一个新的数据库对象来仅存储一个查询，以及为什么不编写该查询或使用过程，这可能是对该问题的简短回答。 重要的是要了解，当您拥有数据库视图时，查询将存储在该视图中，并且您无需从头开始编写它。 另外，与过程相比，视图通常更简单（您不传递参数，只有一个select语句），并且可以执行一些操作过程，而这些操作不允许您执行（插入，更新，删除） 。
Let’s move to the examples now.
现在让我们转到示例。
SQL视图–简单示例 (SQL Views – Simple Example)
Let’s create a very simple view. Our view shall return all data from the country table. The code needed is:
让我们创建一个非常简单的视图。 我们的视图将返回国家表中的所有数据。 所需的代码是：
DROP VIEW IF EXISTS v_country_all;
GO
CREATE VIEW v_country_all AS
SELECT * FROM country;
Similarly to the creating procedures in the previous article, the first line contains the DROP statement (to delete a view if it exists) and after that goes the code that creates a view. After running these statements, the view is created and we can see that in the Object Explorer under Views:
与上一篇文章中的创建过程类似，第一行包含DROP语句（如果存在视图，则删除该视图），然后是创建视图的代码。 运行这些语句后，将创建视图，我们可以在“视图”下的“对象资源管理器”中看到该视图：
Now, we’ll use this view in the select statement. We’ll go as simple as it’s possible.
现在，我们将在select语句中使用此视图。 我们将尽可能地简化。
You can notice that the result is the same as it would be if we ran the query that is in the view. Also, in our select query, we’ve used the view as we would use any other regular database table.
您可以注意到，结果与运行视图中的查询的结果相同。 同样，在我们的选择查询中，我们使用了视图，就像使用其他任何常规数据库表一样。
SQL视图–插入，更新和删除 (SQL Views – Insert, Updates & Delete)
If we can select from the view, this leads to the next question. Can we use the view to insert new rows, update or delete existing? And the answer, in SQL Server, is – “yes”.
如果我们可以从视图中进行选择，这将导致下一个问题。 我们可以使用该视图插入新行，更新或删除现有行吗？ 在SQL Server中，答案是“是”。
So, let’s insert a new row using the view we’ve just created:
因此，让我们使用刚刚创建的视图插入新行 ：
INSERT INTO v_country_all (country_name, country_name_eng, country_code) VALUES ('Nova', 'New', 'NEW');
The operation completed successfully, and we’ll check if the change in the table is as expected.
该操作已成功完成，我们将检查表中的更改是否符合预期。
You can notice that we have 1 more line in our table, so the insert using view was performed successfully.
您可能会注意到我们的表中还有1行，因此成功执行了using using视图的插入。
The next thing we’ll try is to update the existing row using the view. We’ll update the row we’ve inserted last, using the following statement:
我们将尝试的下一件事是使用视图更新现有行 。 我们将使用以下语句更新最后插入的行：
UPDATE v_country_all SET
country_name = 'Nova1'
WHERE id = 8;
You can notice that we’ve updated value for only one column from the view. Once more, we’ll check what happened in the table, selecting from the view:
您会注意到，我们仅更新了视图中一列的值。 再一次，我们将检查表中发生了什么，从视图中进行选择：
We can notice that the value changes.
我们可以注意到值发生了变化。
The last thing we’ll do using the view is to delete an existing record. To do that, we’ll use the following statement:
我们将使用该视图做的最后一件事是删除现有记录 。 为此，我们将使用以下语句：
DELETE
FROM v_country_all
WHERE id = 8;
We’ll again check the contents of the table using the combination of select and view.
我们将再次使用select和view的组合检查表的内容。
You can notice that, as expected, the row was deleted.
您可以注意到，正如预期的那样，该行已被删除。
After performing insert, update, and delete, we can only conclude that SQL Server allows us to perform all operations when we’re using views. Of course, for these operations, your view should contain only one table.
执行插入，更新和删除后，我们只能得出结论，即在使用视图时，SQL Server允许我们执行所有操作。 当然，对于这些操作，您的视图应仅包含一个表。
SQL视图–优点和缺点 (SQL Views – Advantages & Disadvantages)
Like stored procedures, SQL views also have a number of advantages. I’ll try to list the most important ones here:
像存储过程一样，SQL视图也具有许多优点 。 我会在这里列出最重要的一些：
- Security – I’ll put security in the first place because, similarly to procedures, you can define who can use a view and how. That same user doesn’t have access to tables used in the view, but only to the view. This way, you can protect sensitive details stored in the table and expose only the ones you want the user to see 安全性 –我将安全性放在首位，因为与过程类似，您可以定义谁可以使用视图以及如何使用视图。 该用户无权访问视图中使用的表，而只能访问视图。 这样，您可以保护存储在表中的敏感详细信息，并仅公开您希望用户看到的详细信息
- Easy to use (for the end-user) – While you might know how to write cool and complex queries, most business users are not interested in that. They just want to get the data. Putting your complex query in the view and allowing business users to use the view, shall hide the complexity of the query and return only the columns they need. You’ll use views as a way how to store your complex code. Also, be aware that you should name your views consistently and logically, so anyone can understand what the view does, simply from its’ name 易于使用（对于最终用户） –尽管您可能知道如何编写出色而复杂的查询，但大多数企业用户对此并不感兴趣。 他们只想获取数据。 将复杂的查询放入视图中并允许业务用户使用该视图，将隐藏查询的复杂性并仅返回他们需要的列。 您将使用视图作为存储复杂代码的方式。 另外，请注意，您应该一致且逻辑地命名视图，以便任何人都可以从视图名称中了解视图的用途
- Following business rules & consistency of business logic – This is related to the previous bullet. If you have specific reports, business users need, you can create a SQL view for every single report. All who need a certain number can simply run this view. If something changes in the reporting requirements, you’ll simply change the view, and all who use it shall immediately feel the effect of that change 遵循业务规则和业务逻辑的一致性 –这与上一个项目符号有关。 如果您有业务用户需要的特定报告，则可以为每个报告创建一个SQL视图。 所有需要一定数量的人都可以简单地运行此视图。 如果报表要求发生了某些变化，您只需更改视图，所有使用该视图的人都将立即感受到该更改的效果
- Use them to make database changes – Imagine a situation where you want to remove the table, replace it with few tables, or simply changing a table name. In case you do that, there is a great chance you’ll mess up the code somewhere, where this table was used. If you want to prevent that, you could create a view with the same name as the old table had. While this is a fix, this could prove to spare a lot of time 使用它们进行数据库更改 –设想一种情况，您要删除该表，用几个表替换它，或者只是更改表名。 如果这样做，您很有可能将使用该表的地方弄乱代码。 如果要防止这种情况，可以创建一个与旧表同名的视图。 尽管这是一个修复程序，但这可能会节省大量时间
- Views don’t take space – Views are used to store your code, not complete tables. Each time you call a view, you’ll run the related query. Therefore, you don’t lose disk space on views 视图不占用空间 -视图用于存储代码，而不是完整的表。 每次调用视图时，都将运行相关查询。 因此，您不会丢失视图上的磁盘空间
It would be great that we have only advantages, but as it’s usually the case with the most things in life, views also come with some disadvantages:
我们只有优势是非常棒的，但是正如生活中大多数事物通常如此，视图也有一些劣势 ：
- Database changes & views – If you remove an attribute used in the view, the view won’t work. That is the same thing as if you’re trying to run a query using the name of the non-existing column. This is not a big deal if you’re using views only for reporting, because end users will pass the info that their report is not working as expected. In case you’re combining views with insert, update, or delete (some DBMSs allow that) operations, you’ll have a bigger issue 数据库更改和视图 –如果删除视图中使用的属性，则该视图将不起作用。 这与尝试使用不存在的列的名称运行查询是一样的。 如果您仅将视图用于报告，则这没什么大不了的，因为最终用户将传递信息，表明他们的报告无法正常工作。 如果您将视图与插入，更新或删除操作结合使用（某些DBMS允许这样做），则会遇到更大的问题
- Performance – This could theoretically be a problem because business/end users are usually not aware (and there is no reason why they should be) of what you did. If the query stored in the SQL view is complex and/or not-optimized, it will use a lot of resources and time, and this will lead to all possible issues long queries can cause. We’ll talk more about that later in the series. Still, a business user has no idea of that and could be confused or try to use your view multiple times, etc. 性能 –从理论上讲这可能是个问题，因为业务/最终用户通常不了解（并且没有理由这么做）您所做的事情。 如果存储在SQL视图中的查询是复杂的和/或未优化的，它将使用大量的资源和时间，这将导致长查询可能导致的所有可能的问题。 我们将在本系列的后面部分进一步讨论。 不过，业务用户对此一无所知，可能会感到困惑或尝试多次使用您的视图，等等。
那么，何时使用SQL视图？ (So, When to Use SQL Views?)
I’m personally not a big fan of views because I like to store my code in the stored procedures. Still, there are occasions where they can be more than useful. My recommendation would be to use them when creating reports containing a complex select query, grabbing data from multiple tables. If the DBMS you’re using allows that, you could use views for other commands (insert, update, delete) too, and build your system in the “ORM (object-relational mapping) style”.
我个人并不喜欢视图，因为我喜欢将代码存储在存储过程中。 不过，在某些情况下它们可能不仅仅有用。 我的建议是在创建包含复杂选择查询的报告时使用它们，并从多个表中获取数据。 如果您使用的DBMS允许，您也可以将视图用于其他命令（插入，更新，删除），并以“ ORM（对象关系映射）样式”构建系统。
目录 (Table of contents)
sql关于视图的sql
721
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
