This website requires JavaScript.

MSSQL:建立&修改视图

话说工作到现在,其实视图用的很少,不过一些特性还是不能忘的,记录如下:

一、建立视图

IF OBJECT_ID('Sales.OrderTotalsByYear', 'V') IS NOT NULL
    DROP VIEW Sales.OrderTotalsByYear;
GO
例一:
CREATE VIEW Sales.OrderTotalsByYear
WITH SCHEMABINDING ,  --在修改用于生成当前视图的表或视图时,一旦对当前视图产生影响(导致视图失效),则不允许修改。   
     ENCRYPTION  --加密,不能编辑(加密等级并不高)
AS
    SELECT  YEAR(O.orderdate) AS orderyear ,
            SUM(OD.qty) AS qty
    FROM    Sales.Orders AS O
            JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid
    GROUP BY YEAR(orderdate);
GO
例二:
CREATE VIEW dbo.viewTestIndexInfo
AS
    SELECT DISTINCT
            Employees.*
    FROM    Employees
            JOIN Sales ON Employees.EmployeeID = Sales.EmployeeID
    WHERE   Title = 'Sales Person'
WITH CHECK OPTION   --如过视图中有where语句,通过view来修改表格的时候有可能更新掉 where条件之外的行,该选项用来强制更改的内容必须匹配where条件
### 二、查看数据库内的视图
--To explore view metadata using T-SQL, you can query the sys.views catalog view:
USE TSQL2012;
GO
SELECT name, object_id, principal_id, schema_id, type 
FROM sys.views;

--You can also query the INFORMATION_SCHEMA.TABLES system view, but it is slightly more complex:
SELECT SCHEMA_NAME, TABLE_NAME, TABLE_TYPE 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'VIEW';
### 三、修改视图
ALTER VIEW Sales.OrderTotalsByYear
WITH SCHEMABINDING
AS
    SELECT  O.shipregion ,
            YEAR(O.orderdate) AS orderyear ,
            SUM(OD.qty) AS qty
    FROM    Sales.Orders AS O
            JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid
    GROUP BY YEAR(orderdate) ,
            O.shipregion;
GO
### 四、通过视图修改记录
--通过视图增加记录  如果视图带了CHECK OPTION 选项,那么插入的数据必须跟随WHERE条件
INSERT  vEmployees
        SELECT  3 ,
                'xxx' ,
                'xx'
0条评论
avatar