---
# System prepended metadata

title: L11 檢視表 VIEW
tags: [SQL]

---

# L11 檢視表 VIEW
###### tags: `SQL`
## 基本語法
針對物件：
新增 **CREATE**
修改 **ALTER**
刪除 **DROP**

## 檢視表概述
可由使用者從資料表抓資料後自訂檢視表

可在資料庫->**檢視**->看到檢視表

![](https://i.imgur.com/YOwfrVq.png =50%x)



優：
* 可限定資料表的使用範圍
  資料表可設權限，但資料表的各別欄位不能設權限
* **保護敏感性資料**
* 簡化複雜分散式的權限管理
* 可建立INDEX VIEW改善查詢效能
  針對有計算公式的查詢時，可建立Index View將結果儲存

## 新增檢視表
:::info
**CREATE VIEW** 檢視表名稱 (欄位)
**AS**
定義(SELECT...FROM...WHERE...)
:::


欄位可省略，除非有計算公式時，需有欄位名稱

ex.
```sql=
USE Northwind
GO
CREATE VIEW EmployeeView
AS 
SELECT LastName, Firstname
FROM Employees
GO
```

結果為
![](https://i.imgur.com/jScHqGc.png =35%x)

ex.有**計算公式**時，欄位名稱不可省
```sql=
USE Northwind
GO
CREATE VIEW dbo.OrderSubtotals (OrderID, Subtotal)
AS
SELECT OD.OrderID,
SUM(CONVERT(money,(OD.UnitPrice
    *Quantity*(1-  Discount)/100))*100)
FROM [Order Details] OD
GROUP BY OD.OrderID
GO
```

==**CONVERT**(DATATYPE,轉換變數/表示式,STYLE)==

ex.建立連結Join資料表後的檢視表
```sql=
USE Northwind
GO
CREATE VIEW ShipStatusView
AS
SELECT OrderID, ShippedDate, ContactName
FROM Customers c INNER JOIN Orders o
ON c.CustomerID = O.CustomerID
WHERE RequiredDate < ShippedDate
GO
```

ex.可從檢視表中建立檢視表
```sql=
USE Northwind
GO
CREATE VIEW dbo.TotalPurchaseView
AS
SELECT CompanyName, Subtotal=Sum(CONVERT(money,
     (UnitPrice*Quantity*(1-Discount)/100))*100) 
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
INNER JOIN [Order Details] od
ON o.OrderID = od.OrderID
GROUP BY CompanyName
GO

CREATE VIEW dbo.TopSalesView
AS
SELECT *
FROM dbo.TotalPurchaseView
WHERE Subtotal > 50000
GO

USE Northwind
SELECT *
FROM dbo.TopSalesView
WHERE CompanyName = 'Ernst Handel'
GO
```

## 修改檢視表
:::info
**ALTER VIEW** 檢視表名稱 (欄位)
**AS**
定義(SELECT...FROM...WHERE...)
:::

ex.
```sql=
USE Northwind
GO
ALTER VIEW dbo.EmployeeView
AS 
SELECT LastName, FirstName, Extension
FROM Employees
GO
```

![](https://i.imgur.com/pVHwNHD.png =60%x)

也可使用UI：
檢視表名稱->編寫檢視表的指令碼為->ALTER至->新增查詢編輯器視窗

![](https://i.imgur.com/UCHVyh6.png =65%x)



## 加密 WITH ENCRYPTION
檢視表加密 **WITH ENCRYPTION**
只能看到內容，但無法看到運算公式(定義)
🤦‍♀️檢視表加密後連使用者本人都無法查看!
加密時須**先儲存**檔案，解密時需用到同一連線！
查看時出現ERROR：存取權限不足而無法擷取，文字已加密

:::info
CREATE(ALTER) VIEW 檢視表名稱
**WITH ENCRYPTION**
AS
定義
:::

ex.加密
```sql=
ALTER VIEW dbo.[Order Subtotals]
WITH ENCRYPTION
AS
SELECT OrderID,
Sum(CONVERT(money,
    (UnitPrice*Quantity*(1-Discount)/100))*100)
AS Subtotal
FROM [Order Details]
GROUP BY OrderID
GO
```

結果：檢視表圖案會改變
![](https://i.imgur.com/wvdOHsY.png =60%x)

ex.解密
```sql=
ALTER VIEW dbo.[Order Subtotals]
--WITH ENCRYPTION
AS
SELECT OrderID,
Sum(CONVERT(money,
   (UnitPrice*Quantity*(1-Discount)/100))*100)
AS Subtotal
FROM [Order Details]
GROUP BY OrderID
GO
```

## 檢視表相依
檢視表名稱 右鍵-> 檢視相依性

![](https://i.imgur.com/82aryiT.png =40%x)

出現-> 可看到VIEW參考的物件和參考此VIEW的物件
![](https://i.imgur.com/00Ro0Zc.png =80%x)

也可使用語法
```sql=
exec sp_depends [OrderSubtotals]
```

*即使有相依檢視表，依舊能刪除資料表*，所以要小心!

## 檢視表 資料修改
預設：修改檢視表資料時會影響到資料表，危險！
所以希望透過檢視表做資料修改時會得到驗證 **WITH CKECK OPTION**
* 如果檢視表由不同資料表JOIN而成，不能同時修改
* 由計算公式所組成的欄位不能做修改
* 新增檢視表資料時，需檢查原先資料表欄位是否允許NULL值！
  若影響到不包括在VIEW中的欄位時仍可產生錯誤

:::info
CREATE(ALTER) VIEW 檢視表
AS
定義
**WITH CHECK OPTION**
:::





