视图

定义:视图在数据库中只是一种逻辑存在。它们可作用表,但是数据视图可以包括连接,聚集和过滤器。
语法:CREATE VIEW <view name> AS <selete statement>
优点:对表进行筛选;节省硬盘控件;过滤内容
案例:CREATE VIEW Employees_vw  AS  SELECT EmployeeID,FirstName,MiddleInitial,LastName,Title,HireDate,TerminationDate,ManagerEmpID,Department  FROM  Employees;

使用视图过滤数据

案例:
CREATE VIEW CurrentEmployees_vw
AS
SELECT EmployeeID,FirstName,MiddleInitial,LastName,Title,HireDate,ManagerEmpID,Department FROM Employees WHERE TerminationDate IS NULL;

使用DATEADD和CAST函数

DATEADD:可以增加(如果使用负数,就是减少)需要处理的任意长的时间。只要告诉它想要在那个日期上,以及想要在它上面添加的时间单位(天,星期,年,分钟等)。

通过WITH CHECK OPTION限制插入到视图中的内容

案例:
CREATE VIEW PortlandAreaAddresses_vw
AS
SELECT
AddressID,
AddressLine1,
City,
StateProvinceID,
PostalCode,
ModifiedDate
 FROM Person.Address
WHERE 
PostalCode LIKE '970%'
OR PostalCode LIKE '971%'
OR PostalCode LIKE '972%'
OR PostalCode LIKE '986[6-9]%'
WITH CHECK OPTION;

删除视图

语法:DROP VIEW PortlandAreaAddresses_vw;

审查:显示现有的代码

查看视图创建代码:
1.sp_helptext
语法:EXEC sp_helptext YesterdaysOrders_vw;
2.sys.sql_modules 元数据函数
语法:SELECT * FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.YesterdaysOrders_vw');

保护代码:加密视图

定义:加密视图必须做的所有工作是使用WITH ENCRYPTION选项。
注意:1.WITH ENCRYPTION跟在视图的名称之后,单在AS关键字之前。
2.WITH ENCRYPTION不使用OPTION关键字。
案例:
ALTER VIEW CustomerOrders_vw
WITH ENCRYPTION
AS
SELECT 
sc.AccountNumber,
soh.SalesOrderID,
soh.OrderDate,
sod.ProductID,
pp.Name,
sod.OrderQty,
sod.UnitPrice,
sod.UnitPriceDiscount*sod.UnitPrice*sod.OrderQty AS TotalDiscount,
sod.LineTotal
 FROM 
Sales.Customer AS sc
INNER JOIN Sales.SalesOrderHeader AS soh
ON sc.CustomerID = soh.CustomerID
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS pp
ON sod.ProductID = pp.ProductID;

索引视图

案例:
ALTER VIEW CustomerOrders_vw
WITH SCHEMABINDING
AS
SELECT 
sc.AccountNumber,
soh.SalesOrderID,
soh.OrderDate,
sod.ProductID,
pp.Name,
sod.OrderQty,
sod.UnitPrice,
sod.UnitPriceDiscount*sod.UnitPrice*sod.OrderQty AS TotalDiscount,
sod.LineTotal
 FROM Sales.Customer AS sc
INNER JOIN Sales.SalesOrderHeader AS soh
ON sc.CustomerID = soh.CustomerID
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS pp
ON sod.ProductID = pp.ProductID;

SELECT * FROM CustomerOrders_vw;

给视图创建群集索引
CREATE UNIQUE CLUSTERED INDEX ivCustomerOrders
ON CustomerOrders_vw(AccountNumber,SalesOrderID,ProductID);

索引聚集视图

案例:

CREATE VIEW CustomerTotalOrdersByDay_vw
WITH SCHEMABINDING
AS
SELECT 
SUM(sod.OrderQty) TotalOrderQty,
AVG(sod.UnitPrice) AveragePrice,
AVG(sod.UnitPriceDiscount*sod.UnitPrice*sod.OrderQty)
AS AverageDiscount,
SUM(sod.LineTotal) TotalSale
 FROM Sales.Customer AS sc
INNER JOIN Sales.SalesOrderHeader AS soh
ON sc.CustomerID = soh.CustomerID
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID  = sod.SalesOrderID
GROUP BY soh.OrderDate;












本文转载:CSDN博客