本文共 10230 字,大约阅读时间需要 34 分钟。
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 and and showed simple examples of how to use them. Today, we’ll do the same for the SQL views.
SQL视图是我们可以使用的另一个强大的数据库对象。 在本系列的前两篇文章中,我们讨论了和并展示了如何使用它们的简单示例。 今天,我们将对SQL视图执行相同的操作。
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.
我们将使用它在单个表上创建查询,该表将成为国家/地区表。
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.
现在让我们转到示例。
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;GOCREATE 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.
您可以注意到,结果与运行视图中的查询的结果相同。 同样,在我们的选择查询中,我们使用了视图,就像使用其他任何常规数据库表一样。
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_allWHERE 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允许我们执行所有操作。 当然,对于这些操作,您的视图应仅包含一个表。
Like stored procedures, SQL views also have a number of advantages. I’ll try to list the most important ones here:
像存储过程一样,SQL视图也具有许多优点 。 我会在这里列出最重要的一些:
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:
我们只有优势是非常棒的,但是正如生活中大多数事物通常如此,视图也有一些劣势 :
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(对象关系映射)样式”构建系统。
Learn SQL: SQL Views | |
学习SQL:SQL视图 | |
翻译自:
sql关于视图的sql
转载地址:http://xgjwd.baihongyu.com/