如何在SQL Server中创建视图

In this article, we will learn the basics of the view concept in SQL Server and then explore methods to create a view in SQL using T-SQL and SQL Server Management Studio.

在本文中，我们将学习SQL Server中视图概念的基础知识，然后探索使用T-SQL和SQL Server Management Studio在SQL中创建视图的方法。

定义 (Definition)

Most of the time, views can be defined as “virtual or logical” tables, but if we expand this basic definition we can understand the views more clearly. A view is a query that is stored in the database and returns the result set of the query in which it is defined. The query that defines the view can be composed of one or more tables. A view returns column or columns of the query in which it is referenced. However, we need to underline a significant point about the views, a simple view never stores data, merely it fetches the results of the query in which it is defined.

在大多数情况下，视图可以定义为“虚拟或逻辑”表，但是如果我们扩展此基本定义，我们可以更清楚地理解视图。 视图是存储在数据库中的查询，并返回定义查询的结果集。 定义视图的查询可以由一个或多个表组成。 视图返回引用它的查询的一个或多个列。 但是，我们需要在视图上强调一个重点，一个简单的视图永远不会存储数据，只是它会获取定义了它的查询的结果。

Query simplicity is the main advantage that comes first to our minds related to the views. We can encapsulate the complex query scripts into views so that we can use the views instead of the complex query codes. On the other hand, views can provide us to handle security issues. Each user can be granted authorization to see specific data through the views so we don’t have to struggle to give permissions to users for multiple-tables.

查询简单是与视图相关的首要优势。 我们可以将复杂的查询脚本封装到视图中，以便我们可以使用视图代替复杂的查询代码。 另一方面，视图可以为我们提供处理安全问题的方法。 可以授予每个用户通过视图查看特定数据的权限，因此我们不必费心为多个表授予用户权限。

如何在SQL中使用单个表创建视图 (How to create a view in SQL with a single table)

In this section, we will learn the syntax of the views. The following statement defines the syntax of a view:

在本节中，我们将学习视图的语法。 以下语句定义了视图的语法：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

At first, we need to specify the CREATE VIEW statement and then we have to give a name to the view. In the second step, we define the SELECT statement after the AS keyword. The following example will create a view that will be named as VProductSpecialList. VProductSpecialList view fetches data from the Product table and it only returns the ProductID, Name and ProductNumber columns of the Product table:

首先，我们需要指定CREATE VIEW语句，然后必须给视图命名。 在第二步中，我们在AS关键字之后定义SELECT语句。 下面的示例将创建一个名为VProductSpecialList的视图。 VProductSpecialList视图从产品表获取数据，它只是返回的产品ID， 产品表的名称和ProductNumber列：

CREATE VIEW VProductSpecialList
AS
select p.ProductID AS [ProductIdNumber] , 
p.Name AS [ProductName],
p.ProductNumber [ProductMainNumber] from [Production].[Product] p 
     
    WHERE ProductID >900

After the creation of the view, we can retrieve data using a simple SELECT statement. The following example shows how to fetch data from the VProductSpecialList view:

创建视图后，我们可以使用简单的SELECT语句检索数据。 以下示例显示如何从VProductSpecialList视图获取数据：

SELECT * FROM VProductSpecialList

As you can see above, the column names have been replaced with aliases which are defined in the query definition of the view. In addition, the data which is fetched by the view is filtered according to the criteria of the WHERE statement.

如您在上方所见，列名已替换为在视图的查询定义中定义的别名。 此外，根据WHERE语句的条件过滤由视图获取的数据。

For the different circumstances, we may need some particular columns of the view for this we can only use these column names in the SELECT statement:

对于不同的情况，为此我们可能需要视图的某些特定列，因此我们只能在SELECT语句中使用这些列名称：

SELECT ProductIdNumber,ProductName FROM VProductSpecialList

如何在SQL中使用多表创建视图 (How to create a view in SQL with multiple-tables)

In the previous example, we created a view for a single table but we can also create a view for joined multiple tables. In the following example, we will achieve this idea:

在上一个示例中，我们为单个表创建了一个视图，但是我们也可以为联接的多个表创建一个视图。 在以下示例中，我们将实现此想法：

CREATE VIEW VProductDetailList
AS
select p.ProductID AS [ProductIdNumber] , 
p.Name AS [ProductName],
p.ProductNumber [ProductMainNumber],
pm.Name as [ProductModelName]  from [Production].[Product] p 
    INNER JOIN [Production].[ProductModel] pm 
    ON p.[ProductModelID] = pm.[ProductModelID] 
    WHERE ProductID >900
    GO
    
SELECT * FROM VProductDetailList WHERE ProductModelName='LL Mountain Frame'

The above example demonstrated how to create a view from multiple tables and then we saw, how we fetch data from this view and we also filtered fetched data of the VProductDetailList view.

上面的示例演示了如何从多个表创建视图，然后我们看到了如何从该视图中获取数据，并且还过滤了VProductDetailList视图的获取数据。

如何通过SSMS在SQL中创建视图 (How to create a view in SQL via SSMS)

SQL Server Management Studio AKA SSMS is the most popular and powerful tool to manage, configure, administer and do other uncountable operations in SQL Server. So, we can create a view through SSMS.

SQL Server Management Studio AKA SSMS是用于管理，配置，管理和执行SQL Server中其他不可数操作的最流行，功能最强大的工具。 因此，我们可以通过SSMS创建视图。

We will launch SSMS and login the database with any user who granted to create a view. Expand the database in which we want to create a view. Then right-click on the Views folder and choose the New View option:

我们将启动SSMS并使用任何有权创建视图的用户登录数据库。 展开我们要在其中创建视图的数据库。 然后右键单击“ 视图”文件夹，然后选择“ 新建视图”选项：

The Add Table dialog appears on the screen. On this screen, we will find and then select the Product and ProductModel tables and click Add:

屏幕上将出现“ 添加表”对话框。 在此屏幕上，我们将找到然后选择Product和ProductModel表，然后单击Add ：

The relations between the tables are automatically detected and created by SSMS and at the same time, the view query will be generated in the query tab automatically:

由SSMS自动检测和创建表之间的关系，同时，将在查询选项卡中自动生成视图查询：

In this step, we will check in the necessary columns for the view. If we want to select all column names of the table we can check in the * (All Columns) option. We will check in ProductId, Name, ProductNumber columns in the Production table and Name column in ProductModel table. We can observe the query changing when we check in the names of the columns in tables:

在此步骤中，我们将检查视图的必要列。 如果要选择表的所有列名，可以在*（所有列）选项中进行检查。 我们将检入Production表中的ProductId ， Name ， ProductNumber列和ProductModel表的Name列。 当我们检查表中列的名称时，我们可以观察到查询的变化：

We will set aliases of the columns:

我们将设置列的别名：

We will set the filter criteria and it will be automatically added into the WHERE condition of the query:

我们将设置过滤条件，并将其自动添加到查询的WHERE条件中：

We can right-click in the table panel so that we can execute the view. The result data shows at the bottom of the screen:

我们可以在表格面板中单击鼠标右键，以便执行视图。 结果数据显示在屏幕底部：

Finally, we will press CTRL+S keys in order to save the view and give a name to the view and click OK:

最后，我们将按CTRL + S键以保存视图并为视图命名，然后单击OK ：

The created view can be found under the Views folder:

可以在“ 视图”文件夹下找到创建的视图：

如何在SQL中删除视图 (How to drop a view in SQL)

In order to delete a view in a database, we can use the DROP VIEW statement. However, the DROP VIEW statement may return an error if the view we want to delete do not exists in the database. To overcome this issue, we can use the IF EXISTS keyword with the DROP VIEW statement. The following script deletes the vProductSpecialList from the database:

为了删除数据库中的视图，我们可以使用DROP VIEW语句。 但是，如果我们要删除的视图在数据库中不存在，则DROP VIEW语句可能会返回错误。 为了克服这个问题，我们可以将IF EXISTS关键字与DROP VIEW语句一起使用。 以下脚本从数据库中删除vProductSpecialList ：

DROP VIEW IF EXISTS [VProductSpecialList]

结论 (Conclusion)

In this article, we explored the view notion in SQL and then we learned how to create a view in SQL with two different methods. In the first one, we used the T-SQL method and the second one we used SQL Server Management Studio.

在本文中，我们探讨了SQL中的视图概念，然后学习了如何使用两种不同的方法在SQL中创建视图。 在第一个中，我们使用T-SQL方法，第二个中，我们使用SQL Server Management Studio。

翻译自: https://www.sqlshack.com/how-to-create-a-view-in-sql-server/