12.1 SQL Server视图

 

SQL Server视图(Views)

 

目录

• SQL Server视图(Views)
  • 简介
  • 视图的优点
    • 安全
    • 简单
    • 一致性
  • 创建视图
    • 示例
      • 创建一个简单视图
    • 重新定义视图
    • 使用聚合函数创建视图
  • 删除视图
    • 简介
    • 示例
      • 删除一个视图
      • 删除多个视图
  • 重命名视图
    • 简介
    • SQL Server Management Studio方式
    • T-SQL方式
  • 列出所有视图
    • 简介
    • 创建存储过程来显示SQL Server数据库中的视图
  • 获取一个视图的详细信息
    • 使用sql.sql_module获取一个视图的详细信息
    • 使用sp_helptext存储过程获取一个视图的详细信息
    • 使用OBJECT_DEFINITION()函数获取一个视图的详细信息

 

简介

当使用SELECT语句查询一个或多个表中的数据时，会得到一个结果集。

比如，返回products和brands表中所有产品的产品名称、品牌和标价：

	SELECT
	product_name,
	brand_name,
	list_price
	FROM
	production.products p
	INNER JOIN production.brands b
	ON b.brand_id = p.brand_id;

下次，如果想获得相同的结果集，可以将此查询保存到文本文件中，打开它，然后再次执行。

SQL Server提供了一种通过视图将此查询保存在数据库目录中的更好方法。

视图是存储在数据库目录中的命名查询，可以在很多地方引用它。

因此，可以使用CREATE VIEW语句将上述查询存储为视图，如下所示：

	CREATE VIEW sales.product_info
	AS
	SELECT
	product_name,
	brand_name,
	list_price
	FROM
	production.products p
	INNER JOIN production.brands b
	ON b.brand_id = p.brand_id;

后面就可以在SELECT语句中像下面的表一样引用视图：

SELECT * FROM sales.product_info;

执行此查询时，SQL Server实际上将执行以下查询：

	SELECT
	*
	FROM (
	SELECT
	product_name,
	brand_name,
	list_price
	FROM
	production.products p
	INNER JOIN production.brands b
	ON b.brand_id = p.brand_id;
	);

根据定义，视图不存储数据，索引视图除外。

一个视图可以由多个表中使用联接的列组成，也可以仅由单个表的部分列组成。这使得视图对于抽象或隐藏复杂查询非常有用。
这样就可以不暴露具体的表，还可以隐藏部分字段，有一定的安全效果。

下图显示了一个包含多个表中的列的视图：

视图的优点

一般来说，视图具有以下优点：

安全

可以限制用户直接访问表，而是通过视图访问数据子集。

例如，可以允许用户通过视图访问客户姓名、电话、电子邮件，但限制他们访问银行帐户和其他敏感信息。

简单

一个关系数据库可能有许多具有复杂关系的表，例如，一对多和多对多，这让导航变得困难，出现一些复杂的连接查询。

但是，您可以使用一组视图，通过连接和条件简化复杂的查询。

说白了就是将一堆复杂的SQL封装到一个视图中。

一致性

有时，需要在每个查询中编写复杂的公式或逻辑。

为了保持一致，可以在视图中隐藏复杂的查询逻辑和计算。

一旦定义了视图，就可以从视图中引用逻辑，而不是在每个单独的查询中都写一遍逻辑。

创建视图

语法：

	CREATE [OR ALTER] VIEW schema_name.view_name [(column_list)]
	AS
	select_statement;

• 首先，在CREATE VIEW关键字之后指定视图的名称。schema_name是视图所属架构的名称。
• 其次，指定一个SELECT语句（select_statement），该语句在AS关键字之后定义视图。SELECT语句可以引用一个或多个表。

如果未显式指定视图的列列表(column_list)，SQL Server将使用从SELECT语句派生的列列表。

如果想重新定义视图，例如，向其中添加更多列或从中删除一些列，您可以在CREATE VIEW关键字之后使用OR ALTER关键字。

示例

下面使用示例数据库中的orders（订单）、order_items（订单项）和products（产品）表进行演示。

创建一个简单视图

以下语句基于orders(订单)、order_items（订单项）和products（产品）表创建名为daily_sales（每日销售）的视图：

	CREATE VIEW sales.daily_sales
	AS
	SELECT
	year(order_date) AS y,
	month(order_date) AS m,
	day(order_date) AS d,
	p.product_id,
	product_name,
	quantity * i.list_price AS sales
	FROM
	sales.orders AS o
	INNER JOIN sales.order_items AS i
	ON o.order_id = i.order_id
	INNER JOIN production.products AS p
	ON p.product_id = i.product_id;

创建daily_sales视图后，可以使用简单的SELECT语句查询底层表的数据：

	SELECT
	*
	FROM
	sales.daily_sales
	ORDER BY
	y, m, d, product_name;

重新定义视图

将客户名称列添加到sales.daily_sales视图中，可以使用CREATE OR ALTER VIEW，如下所示：

	CREATE OR ALTER VIEW sales.daily_sales (
	year,
	month,
	day,
	customer_name,
	product_id,
	product_name
	sales
	)
	AS
	SELECT
	year(order_date),
	month(order_date),
	day(order_date),
	concat(
	first_name,
	' ',
	last_name
	),
	p.product_id,
	product_name,
	quantity * i.list_price
	FROM
	sales.orders AS o
	INNER JOIN
	sales.order_items AS i
	ON o.order_id = i.order_id
	INNER JOIN
	production.products AS p
	ON p.product_id = i.product_id
	INNER JOIN sales.customers AS c
	ON c.customer_id = o.customer_id;

在本例中，明确指定了视图的列列表。

使用sales.daily_sales视图查询：

	SELECT
	*
	FROM
	sales.daily_sales
	ORDER BY
	y,
	m,
	d,
	customer_name;

使用聚合函数创建视图

	CREATE VIEW sales.staff_sales (
	first_name,
	last_name,
	year,
	amount
	)
	AS
	SELECT
	first_name,
	last_name,
	YEAR(order_date),
	SUM(list_price * quantity) amount
	FROM
	sales.order_items i
	INNER JOIN sales.orders o
	ON i.order_id = o.order_id
	INNER JOIN sales.staffs s
	ON s.staff_id = o.staff_id
	GROUP BY
	first_name,
	last_name,
	YEAR(order_date);

以下语句创建了一个名为staff_sales的视图，使用SUM()聚合函数按员工和年份分组汇总销售额:

	CREATE VIEW sales.staff_sales (
	first_name,
	last_name,
	year,
	amount
	)
	AS
	SELECT
	first_name,
	last_name,
	YEAR(order_date),
	SUM(list_price * quantity) amount
	FROM
	sales.order_items i
	INNER JOIN sales.orders o
	ON i.order_id = o.order_id
	INNER JOIN sales.staffs s
	ON s.staff_id = o.staff_id
	GROUP BY
	first_name,
	last_name,
	YEAR(order_date);

以下语句返回视图的内容：

	SELECT
	*
	FROM
	sales.staff_sales
	ORDER BY
	first_name,
	last_name,
	year;

删除视图

简介

语法：

DROP VIEW [IF EXISTS] schema_name.view_name;

此语法中，您可以在drop view关键字之后指定要删除的视图的名称。如果视图属于架构，则还必须显式指定视图所属架构的名称。

如果尝试删除不存在的视图，SQL Server会报错。IF EXISTS子句可防止在删除不存在的视图时发生错误.

删除多个视图语法：】

	DROP VIEW [IF EXISTS]
	schema_name.view_name1,
	schema_name.view_name2,
	...;

请注意，当您删除视图时，SQL Server会删除该视图的所有权限。

示例

删除一个视图

删除视图sales.daily_sales:

DROP VIEW IF EXISTS sales.daily_sales;

删除多个视图

同时删除视图sales.staff_sales 和 sales.product_catalog：

	DROP VIEW IF EXISTS
	sales.staff_sales,
	sales.product_catalogs;

重命名视图

简介

重命名视图之前，必须注意依赖于视图的所有对象可能会失败。其中包括存储过程、用户定义函数、触发器、查询、其他视图和客户端应用程序。

因此，重命名视图后，必须确保引用视图旧名称的所有对象都使用新名称。

SQL Server Management Studio方式

首先，在对象资源管理器中，展开数据库，选择包含要重命名的视图的数据库名称，然后展开Views(视图)文件夹:
然后，在要重命名的视图上右击，然后选择"Rename"(重命名)。
如图：

然后输入新名字：

T-SQL方式

可以使用系统中的sp_rename存储过程：

	EXEC sp_rename
	@objname = 'sales.product_catalog',
	@newname = 'product_list';

其中：
将视图原名字传递给@objname参数，将新视图的名称传递给@newname参数。注意，在@objname中必须指定视图的架构名。但是，在@newname参数中，一定不能指定架构名。

列出所有视图

简介

要列出SQL Server数据库中的所有视图，可以通过查询sys.views视图或者sys.objects目录视图。
下面是一个示例，通过sys.views视图：

	SELECT
	OBJECT_SCHEMA_NAME(v.object_id) schema_name,
	v.name
	FROM
	sys.views as v;

本示例中，通过OBJECT_SCHEMA_NAME()函数获取视图的架构名。

通过sys.objects视图返回视图列表：

	SELECT
	OBJECT_SCHEMA_NAME(o.object_id) schema_name,
	o.name
	FROM
	sys.objects as o
	WHERE
	o.type = 'V';

创建存储过程来显示SQL Server数据库中的视图

以下存储过程包装了上面的查询，根据输入架构名和视图名列出SQL Server数据库中的所有视图：

	CREATE PROC usp_list_views(
	@schema_name AS VARCHAR(MAX) = NULL,
	@view_name AS VARCHAR(MAX) = NULL
	)
	AS
	SELECT
	OBJECT_SCHEMA_NAME(v.object_id) schema_name,
	v.name view_name
	FROM
	sys.views as v
	WHERE
	(@schema_name IS NULL OR
	OBJECT_SCHEMA_NAME(v.object_id) LIKE '%' + @schema_name + '%') AND
	(@view_name IS NULL OR
	v.name LIKE '%' + @view_name + '%');

比如，您想知道包含sales的视图，可以调用存储过程usp_list_views：

EXEC usp_list_views @view_name = 'sales'

获取一个视图的详细信息

使用sql.sql_module获取一个视图的详细信息

要获取视图信息，可以使用系统目录sys.sql_module和OBJECT_ID()函数:

	SELECT
	definition,
	uses_ansi_nulls,
	uses_quoted_identifier,
	is_schema_bound
	FROM
	sys.sql_modules
	WHERE
	object_id = object_id('sales.daily_sales');

在此查询中，将视图的名称传递给WHERE子句中的OBJECT_ID()函数。OBJECT_ID()函数返回数据库对象的Id.
输出：

注意，默认输出格式是表格格式，如果要输出为文本格式，向上图一样，看到SELECT语句.

要将结果显示为文本，在查询编辑器中按Ctrl-T键盘快捷键或单击Results to Text按钮，如下图所示：

使用sp_helptext存储过程获取一个视图的详细信息

sp_helptext存储过程返回用户自定义对象（如视图）的定义。

要获取视图的信息，可以将视图名传递给sp_helptext存储过程。如，下面的语句返回sales.product_catalog目录的信息：

EXEC sp_helptext 'sales.product_catalog' ;

使用OBJECT_DEFINITION()函数获取一个视图的详细信息

获取视图信息的另一种方法是使用OBJECT_DEFINITION()和OBJECT_ID()函数，如下所示：

	SELECT
	OBJECT_DEFINITION(
	OBJECT_ID(
	'sales.staff_sales'
	)
	) view_info;

 

分类: SQL Server

标签: SQL Server , SQL Server高级 , 数据库