SQL Server视图(Views)
目录简介
当使用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;