MS SQL 學習紀錄-6

2022-08-25 22:20:06

MS SQL 學習紀錄-6

子查詢 (續)
子查詢的進階應用
- 衍生資料表 Derived Table
資料表運算式
- 衍生資料表 Derived Table
- 檢視表 View

子查詢 (續)

比較運算子 (續)
Demonstration：如何在多重值子查詢使用比較運算子

範例 A：查詢產品類別編號為 1 的所有訂單，傳回訂單的所有欄位資料

子查詢
select *
from sales.orders
where orderid in
    (
        select orderid
        from sales.orderdetails
        where productid in
            (
                select productid
                from Production.Products
                where categoryid = 1
            )
    );

聯結
select o.*
from sales.orders o
    join sales.orderdetails od on od.orderid = o.orderid
    join Production.Products p on p.productid = od.productid
where p.categoryid = 1;

範例 B：查詢哪些客戶還未曾下訂單，傳回 CustID, CompanyName

子查詢 1
select CustID, CompanyName
from sales.Customers
where custid not in
    (
        select distinct custid
        from sales.orders
    );

聯結 1
select c.CustID, c.CompanyName
from sales.customers c
    left join sales.orders o on o.custid = c.custid
where o.custid is null;
go

子查詢 2 (改傳回客戶的所有欄位資料)
select *
from sales.Customers
where custid not in
    (
        select distinct custid
        from sales.orders
    );

聯結 2 (改傳回客戶的所有欄位資料)
select *
from sales.customers c
    left join sales.orders o on o.custid = c.custid
where o.custid is null;
go

子查詢 3 (改用存在測試運算子)

select CustID, CompanyName
from Sales.Customers c
where not exists 
    (
        select *
        from Sales.Orders o
        where c.custid=o.custid
    );

子查詢 4 (改用 COUNT(*) = 0)

select CustID, CompanyName
from Sales.Customers c
where 
    (
        select count(*)
        from Sales.Orders o
        where c.custid=o.custid
    ) = 0;

注意事項：
子查詢 3 和 子查詢 4 必須使用相互關聯子查詢的寫法才更有意義，後續課程會說明
若只在乎子查詢有或沒有資料，使用 EXISTS 存在測試運算子是不錯的選擇
使用 COUNT(*) = 0 也能達到類似的效果

範例 C：查詢薪資比員工編號 5 或 6 或 7 等 3 人還高的員工，傳回員工的所有欄位

子查詢

select *
from hr.Employees
where salary > any
    (
        select salary
        from hr.Employees
        where empid in (5, 6, 7)
    );

範例 D：查詢供應商名稱 Supplier STUAZ 在加入供應鏈之前的所有訂單資料，傳回訂單的所有欄位資料

子查詢 1

select *
from sales.orders
where orderid < all
    (
        select orderid
        from sales.OrderDetails
        where productid in
            (
                select productid
                from Production.Products
                where supplierid = 
                    (
                        select supplierid
                        from Production.Suppliers
                        where companyname = 'Supplier STUAZ'
                    )
            )
    );

子查詢 2

select *
from sales.orders
where orderid < all
    (
        select orderid
        from sales.OrderDetails
        where productid in
            (
                select productid
                from Production.Products
                where supplierid = 
                    (
                        select supplierid
                        from Production.Suppliers
                        where companyname = 'Supplier STUAZ'
                    )
            )
        order by orderid
        offset 0 rows
        fetch next 1 rows only
    );

子查詢 3 + 聯結

select *
from sales.orders
where orderid < all
    (
        select od.orderid
        from sales.OrderDetails od
            join Production.Products p on p.productid = od.productid
            join Production.Suppliers s on s.supplierid = p.supplierid
        where s.companyname = 'Supplier STUAZ'
        order by od.orderid
        offset 0 rows
        fetch next 1 rows only
    );

範例 E：若剛進貨以下商品 (須先執行以下 insert 指令)，查詢同商品但進貨成本較為低價的記錄，傳回產品的所有欄位資料

INSERT INTO Production.Products(productname, supplierid, categoryid, listprice, discontinued, InStock)
  VALUES(N'Product LUNZZ', 15, 2, 13.30, 0, 10);

子查詢

select *
from Production.Products
where listprice < any 
    (
        select listprice
        from Production.Products
        where productname = N'Product LUNZZ'
    ) and productname = N'Product LUNZZ';

聯結 1 (自我聯結 Self-Join)

select p2.*
from Production.Products p1 join  
    Production.Products p2 on p2.productname = p1.productname
where p2.listprice < p1.listprice
    and p1.productname = N'Product LUNZZ';

聯結 2 (自我聯結 Self-Join)

select p2.*
from Production.Products p1 join  
    Production.Products p2 on p2.productname = p1.productname
where p2.listprice < p1.listprice
    and p1.productname = N'Product LUNZZ'
    and p2.productname = N'Product LUNZZ';

聯結 3 (自我聯結 Self-Join)

select p2.*
from Production.Products p1 join  
    Production.Products p2 on p2.productname = p1.productname
where p2.listprice < p1.listprice;

Exercises：使用和實作多重值子查詢

查詢銷售人員 LastName 為 Funk 在 2007 年訂單量 SUM(qty * unitprice) 的前 3 筆訂單，並傳回 OrderID

ANSWER：
 select top 3 orderid
from sales.OrderDetails
where orderid IN
	(
		select orderid
		from sales.orders
		where empid =
			(
				select empid
				from hr.Employees
				where lastname = N'Funk'
			)
			and year(orderdate) = 2007
	)
group by orderid
order by SUM(qty * unitprice) desc

供應商 Supplier SVIYA，假設在 2007 年 5 月份該供應商的產品有瑕疵，現在的需求是：

傳回訂單所有欄位資料
曾經有問題的產品之訂單要持續追踪 (有瑕疵產品之後的訂單)
必須是已訂講該供應商產品的訂單
找出這些爆發瑕疵產品之後的近 4 個月內 (6月份到9月份) 的高風險訂單

NSWER：

select *
from sales.Orders
where orderid IN
	(
		select distinct orderid
		from sales.OrderDetails
		where productid IN
			(
				select productid
				from Production.Products
				where supplierid =
					(
						select supplierid
						from [Production].[Suppliers]
						where [companyname] = N'Supplier SVIYA'
					)
			)	
	)
	and orderdate >= '20070601' and orderdate < '20071001'

深入探討子查詢

依照設計邏輯分類，可分成以下兩種類型：

自包含子查詢 Self-Contained Subqueries (又稱為獨立子查詢) ：
- 好處是容易偵錯，因為能夠獨立檢查內部查詢的結果，且效能通常優於聯結
- 壞處是巢狀層級多，語法不能精簡
- 寫法上，外部查詢的欄位，不須出現在內部查詢，反之，內部查詢的欄位，不可出現在外部查詢
相互關聯子查詢 Correlated Subqueries：
- 好處是分解複雜的聯結查詢，使其易於瞭解與維謢
- 壞處是不容易偵錯，因為不能獨立檢視內部查詢的結果
- 大部份都能轉換成聯結 JOIN 的設計邏輯
- 寫法上，外部查詢的欄位，必須出現在內部查詢，反之，內部查詢的欄位，不可出現在外部查詢

注意事項：
巢狀層級不得超過 32 層，此限制仍將取決於可用的記憶體，以及查詢中其他運算式的複雜性

子查詢的執行次數

自包含子查詢 Self-Contained Subqueries
- 外部查詢和內部查詢可不用考慮資料表的參考完整性 (FK --> PK)
- 前次課程內容皆為此類型，在此不再贅述
- 內部查詢只執行一次，因為內部查詢的篩選條件大部份都是根據某一指定值 (常數值或已運算的固定值) 作為條件來搜尋資料，代表子查詢不會重複執行
- 換言之，外部查詢每選取一筆記錄，內部查詢只根據第一次的結果為依據
相互關聯子查詢 Correlated Subqueries
- 外部查詢和內部查詢大部份須考慮資料表的參考完整性 (FK --> PK)
- 內部查詢執行不止一次，因為內部查詢的篩選條件都是根據外部查詢傳入的欄位值 (變動值) 作為條件來搜尋資料，所以內部查詢執行次數取決於外部查詢所選取的資料列，代表子查詢可以重複執行，直到滿足條件為止
- 換言之，外部查詢各輪選取一筆記錄，內部查詢就會重複執行一次(輪)，例如：外部查詢和內部查詢符合條件的筆數分別為 5 和 3，則全部被選取的有 15 筆
- 外部查詢必須 Pass 資料行來源值到內部查詢比對，所以寫法上，外部查詢的欄位，必須出現在內部查詢
- 「聯結 JOIN」的另一選擇

注意事項：(相互關聯子查詢 和 聯結 的比較)
從設計邏輯的可讀性來說，在多重資料表 (至少 4 個以上的資料表) 的關聯條件和篩選條件的部份：
開發人員在解讀相互關聯子查詢的設計邏輯時，資料的流向會較為清楚，因為有模組化的感覺，有利於未來的修改作業
開發人員在解讀聯結的設計邏輯時，因為都寫在一起(擠成一團)，這種比較不利於未來的修改作業，且要花比較長的時間去釐清
從執行效能來說，兩者是不分軒輊的，但相互關聯子查詢還是有機會可以再超越聯結

相互關聯子查詢 Correlated Subqueries

基本語法：
SELECT ＜select-list＞
FROM 表1
WHERE 表1.欄名 =
    ( 
        SELECT ＜select-list＞  <-- 傳回單一值 Scalar Value
        FROM 表2
        WHERE 表2.欄名x = 表1.欄名x  <-- 可看成是聯結 JOIN 的關聯條件 (欄名對欄名)
    );

Demonstration：如何使用相互關聯子查詢 Correlated Subqueries

範例 A：查詢各員工所有最近一天的訂單，傳回 EmpID, OrderID, OrderDate

子查詢 1

select empid, orderid, orderdate
from Sales.Orders O1
where orderdate =
    (
        select max(orderdate)
        from Sales.Orders O2
        where O2.empid = O1.empid
    )
order by empid, orderdate;

子查詢 2

select empid, orderid, orderdate
from Sales.Orders O1
where orderdate =
    (
        select orderdate
        from Sales.Orders O2
        where O2.empid = O1.empid
        order by orderdate desc
        offset 0 rows
        fetch next 1 rows only
    )
order by empid, orderdate;

注意事項
來自外部查詢的 O1.empid，出現在內部查詢中
留意資料表別名並未全程標示
共 10 位員工，只有 9 位員工曾接訂單

範例 B：查詢 2007 年 5 月份的訂單明細筆數大於 4 的訂單，並傳回該客戶的 OrderID, CompanyName

子查詢 + 聯結

select	o.orderid, c.companyname
from sales.orders o
    join sales.Customers c on c.custid = o.custid
where o.orderdate between '20070501' and '20070531'
    and o.orderid in 
        (
            select od.orderid
            from sales.OrderDetails od 
            where od.orderid = o.orderid
            group by od.orderid
            having count(*) > 4
        );

聯結

select o.orderid, c.companyname
from sales.orders o
    join sales.OrderDetails od on od.orderid = o.orderid
    join sales.Customers c on c.custid = o.custid
where o.orderdate between '20070501' and '20070531'
group by o.orderid, c.companyname
having count(*) > 4;

Exercises：使用和實作相互關聯子查詢 Correlated Subqueries

查詢哪些訂單訂購商品的售出價格 (unitprice) 直接等於產品建議售價 (listprice) ，傳回該「訂單」的所有欄位資料

ANSWER：
select *
from sales.Orders
where orderid IN
	(
		select orderid
		from [Sales].[OrderDetails] od
		where productid IN
			(
				select productid
				from [Production].[Products] p
				where p.listprice = od.unitprice --< 非典型
			)
	)



select *
from sales.Orders
where orderid IN
	(
		select orderid
		from [Sales].[OrderDetails] od
		where unitprice =
			(
				select listprice
				from [Production].[Products] p
				where p.productid = od.productid --< 典型
			)
	)

查詢哪些訂單訂購商品的售出價格 (unitprice) 直接等於產品建議售價 (listprice)，傳回該「供應商」的所有欄位資料

ANSWER：
select *
from Production.Suppliers
where supplierid IN
	(
		select supplierid
		from Production.Products p
		where productid IN
			(
				select productid
				from sales.OrderDetails od
				where od.unitprice = p.listprice
			)
	)

子查詢的進階應用

典型子查詢是在外部查詢的 WHERE 子句，事實上，子查詢可用在 SELECT、INSERT、UPDATE 或 DELETE 陳述式中，甚至任何可執行子查詢有效的陳述式中

從外部查詢 WHERE 以外的子句來看，子查詢還能應用的類型有：

取代運算式：子查詢置於 SELECT 任何可以使用運算式的位置，例如：
SELECT 子句，這是一個典型的取代運算式之運用，可以做到對比反差的報表，例如從訂單資料中，帶出訂單明細最高售價和最低售價的資料
ORDER BY 子句，可以做到有條件的排序，例如只排序目前月份有關的資料，或者是自動置頂排序客戶最喜好的商品，達到吸睛的效果
衍生資料表 Derived Table：子查詢置於 SELECT 的 FROM 子句的位置

注意事項：
子查詢傳回「單一值」適合用於「取代運算式」。
子查詢傳回「多重值」適合用於「衍生資料表」。

取代運算式
傳回「單一值」的子查詢，幾乎可以用在 T-SQL 任何允許運算式的子句中

位於 SELECT 的子查詢，可解決典型子查詢 (位於外部查詢 WHERE 子句) 的資料行無法顯示在外部查詢結果集的問題
- 適用於產生部份子集資料值的情境
- 一個取代運算式只能傳回一個欄位值，換言之，若是有兩個欄位值，就需要撰寫兩個取代運算式
若取代運算式傳回空值 NULL，除非來源資料本來就是空值 NULL，否則一定是相互關聯子查詢的篩選條件(關聯條件)不成立所導致

基本語法：

SELECT (子查詢)
...省略...
ORDER BY (子查詢);

注意事項
SELECT 子句有子查詢時，要注意 GROUP BY 的限制
ORDER BY 子句使用子查詢，可做出部份排序的效果，而不是整個資料表全部排序
子查詢若傳回空的結果集，會轉換成空值 NULL
ORDER BY 會對空值 NULL 排序在一起並置頂或置末端
搭配「相互關聯子查詢 (Correlated Subquery)」才會更有意義

Demonstration：如何應用子查詢在取代運算式中

範例 A：查詢所有訂單並找出訂購產品的最高售價，並傳回 OrderID, OrderDate, 商品最高售價

子查詢 (位於 SELECT 的子查詢)

select	O.OrderID,
        O.OrderDate,
        (
            select max(unitprice)
            from sales.OrderDetails OD
            where OD.orderid = O.orderid
        ) 產品最高售價
from sales.orders O;

注意事項：
位於 SELECT 的子查詢可解決位於 WHERE 子查詢的結果無法顯示在外部查詢的問題

聯結

select	O.OrderID,
        O.OrderDate,
        max(OD.unitprice) 產品最高售價
from sales.orders O
    join sales.OrderDetails OD on OD.orderid = O.orderid
group by O.OrderID, O.OrderDate;

範例 B：根據前面「相互關聯子查詢 Correlated Subquery」的範例，查詢 2007 年 5 月份的訂單明細筆數大於 4 的訂單，並傳回該客戶的 OrderID, CompanyName

子查詢 (位於 select 的子查詢，完全以子查詢設計，重要)
select	o.orderid,
        (
            select companyname
            from sales.customers c
            where c.custid = o.custid
        ) CompanyName
from sales.orders o
where o.orderdate between '20070501' and '20070531'
    and o.orderid in 
        (
            select od.orderid
            from sales.OrderDetails od 
            where od.orderid = o.orderid
            group by od.orderid
            having count(*) > 4
        );

範例 C：查詢員工資料表，但僅排序員工編號 4 到 7 號，傳回 Lastname 和 EmpID

子查詢 (位於 order by 的子查詢)

select e1.Lastname, e1.EmpID
from hr.Employees e1
order by (
            select e2.empid
            from hr.Employees e2
            where e2.empid = e1.empid 
                and e2.empid between 4 and 7
         );

注意事項：
那些子查詢範圍以外的記錄，因傳回 NULL 而排序在前面，這是 SQL Server 的特性
有條件的排序，可以再透過 COALESCE() 或 CASE…WHEN… 或 IIF() 解決

使用 IIF()
升序排序特定員工編號，其它的將其置頂
select Lastname, EmpID
from HR.Employees
order by iif(empid between 4 and 7, empid, 0);

注意事項：
若要將符合條件的置頂，只要將 0 改為大於現有員工編號的數值即可

升序排序主管編號並將 NULL 置末
select lastname, mgrid
from HR.Employees
order by iif(mgrid is null, 999, mgrid);

注意事項：
使用 999 是因為沒有一位主管編號如此大，所以只要大於現有主管編號的數值即可

Exercises：使用和實作取代運算式的子查詢
1.查詢供應商國籍來自 Japan 所有產品的訂單，以及訂單明細資料表中各單價(UnitPrice)小於產品資料表的各單價(ListPrice)的資料，並傳回訂單的 (非明細)：

OrderID
SubTotal ( 使用原始的計算資料行 SubTotal，或自行彙總 SUM(unitprice * QTY * (1 - Discount)) )
OrderDate

ANSWER：
select OrderID,
       sum(SubTotal) SubTotal,
	   (select orderdate 
	    from sales.orders o
		where o.orderid = od.orderid) OrderDate
from sales.OrderDetails od
where productid IN
	(
		select productid
		from Production.Products
		where supplierid IN
			(
				select supplierid
				from Production.Suppliers
				where country = N'Japan'
			)
	)
	and unitprice < 
		(
			select listprice
			from Production.Products p
			where p.productid = od.productid
		)
group by orderid

2.根據檢視表 Sales.EmpOrders 傳回的結果集，請統計出 2007 年各員工的最高和最低的銷售業績 (統計 val 欄位)，並傳回下列欄位：

原有的欄位不變 ( [empid], [ordermonth] ,[qty] ,[val] ,[numorders] )
最高業績
最低業績

ANSWER：
declare @y smallint = 2007

select *,
	(
		select max(val)
		from Sales.EmpOrders e2
		where e2.empid = e1.empid and year([ordermonth]) = @y 
	) 最高業績,
	(
		select min(val)
		from Sales.EmpOrders e2
		where e2.empid = e1.empid and year([ordermonth]) = @y 
	) 最低業績
from Sales.EmpOrders e1
where year([ordermonth]) = @y 
order by empid, ordermonth

衍生資料表 Derived Table

傳回「多重值」的子查詢，幾乎可以用在 T-SQL 任何允許 <Table_Source> 的子句中。

從外部查詢 WHERE 以外的子句來看，子查詢還能用在 SELECT 的 FROM 子句的位置，所以，「衍生資料表」只是從 SELECT 導出來的表格。

基本語法：
SELECT 選取清單
FROM (子查詢) as 資料表別名
WHERE ...省略...;

注意事項
只是將子查詢傳回的結果集加上短名，就變成虛擬資料表。
衍生資料表只存在於指令執行期間，不佔用資料庫空間，所以不能重複使用。

Demonstration：如何應用子查詢在衍生資料表中

範例 A：根據前面「相互關聯子查詢 Correlated Subquery」的範例，查詢 2007 年 5 月份的訂單明細筆數大於 4 的訂單，並傳回該客戶的 OrderID, CompanyName

聯結一個衍生資料表
select vt.orderid, c.companyname
from sales.Customers c
    join	(
                select o.orderid, o.custid
                from sales.orders o
                    join sales.OrderDetails od on od.orderid = o.orderid
                where o.orderdate between '20070501' and '20070531'
                group by o.orderid, o.custid
                having count(*) > 4
            ) vt on vt.custid = c.custid;

範例 B: 查詢各客戶之訂單最多的訂購數量，傳回 CustID, QTY，並根據 CustID 升序排序

不使用聯結 1

select	CustID,
        MAX(SubQTY) QTY
from (
         select o.CustID,
                (
                    select sum( od.qty )
                    from sales.OrderDetails od
                    where od.orderid = o.orderid
                ) SubQTY
         from sales.orders o
     ) vt
group by custid
order by custid;

聯結一個衍生資料表 2

select CustID,
       MAX(qty) QTY
from (
        SELECT O.custid, SUM(OD.qty) AS qty
        FROM Sales.Orders AS O 
            INNER JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid
        GROUP BY   O.custid
    ) vt
group by custid
order by custid;

完全使用聯結 (不使用衍生資料表) 3

SELECT o.CustID, 
       MAX(od.qty) QTY
FROM Sales.Orders o
    join Sales.OrderDetails od on od.orderid = o.orderid
GROUP BY custid
order by custid;

Exercises：使用和實作衍生資料表的子查詢
1.根據前面「典型子查詢 Self-Contained Subquery」的範例，查詢高於平均薪資的所有員工資料，請改以「衍生資料表」結合「聯結 JOIN」方式來設計 (將子查詢從 WHERE 移到 FROM)

ANSWER：
select *
from hr.Employees e
	join 
	(
		select avg(salary) avgSalary
		from hr.Employees
	) dt on e.salary > dt.avgSalary

2.根據前面「典型子查詢 Self-Contained Subquery」的範例，查詢哪些訂單，是在客戶名稱 Customer UMTLM 的最後一天訂單之後才產生的，傳回那些訂單的所有欄位資料，請改以「衍生資料表」結合「聯結 JOIN」方式來設計 (將子查詢從 WHERE 移到 FROM)

ANSWER：
select *
from sales.orders o
	join
	(
        select max(orderdate) maxOrderdate
        from sales.orders
        where custid = 
            (
                select custid
                from sales.Customers
                where companyname = 'Customer UMTLM'
            )
	) dt on o.orderdate > dt.maxOrderdate

資料表運算式

何謂「資料表運算式」 (Table Expression)：

它不是標準的「使用者自訂資料表」，本身也不儲存用戶資料，各位將會發現這些物件都是僅由 SELECT 定義出來的，所以傳回的結果集也是由 SELECT 所導出來的表格。
可內含運算式，它是計算公式的一部分，例如計算年薪基本公式 SALARY * 12，當更新基礎 (基底) 資料表時，運算式中的值都會進行評估，所以可視為「內含運算式的資料表」。
有些還支援輸入參數到資料表運算式中，以滿足不同運算需求。
資料表運算式皆為具名的資料表運算式 Named Table Expression，也就是說必須指定名稱給它。
資料表運算式分為：
衍生資料表 Derived Table
檢視表 View
資料表值函式(數) Table-Valued Function (TVF)
通用資料表運算式 Common Table Expression (CTE)

資料表運算式比較表：

資料表運算式	傳入值	儲存於資料庫	重複使用	SELECT	INSERT	UPDATE	DELETE
衍生資料表	變數，由外部 DECLARE	N	Y	N	N	N	N
檢視表	None	Y	Y	Y	Y	Y	Y
資料表值函式(數)	變數，本體結構的一部份	Y	Y	Y	Y	Y	Y
通用資料表運算式	變數，由外部 DECLARE	N	Y	Y	Y	Y	Y

注意事項：
僅允許來自一個基底資料表
不允許來自函數產生的資料行，例如：針對 SUM() 傳回的值之資料行執行 UPDATE 指令

衍生資料表 Derived Table

何謂「衍生資料表」(Derived Table)：

從典型子查詢演變而來，置於外部查詢 FROM 子句，並賦予資料表別名的一個具名的資料表運算式，提供外部查詢的資料表來源之結果集，也是一種虛擬資料表 (FROM 子句 + 子查詢 + 表別名)
作用範圍僅在所定義的查詢中，生命週期止於該查詢結束，所以它不儲存在於資料庫
可根據查詢需求而自訂資料來源

不同於典型子查詢的是：

所有資料行必須要有名稱 (別名) 且不可重複
置於外部查詢 FROM 子句

注意事項：
支援輸入參數值到資料表運算式，做法是利用外部已宣告的變數，並用於衍生資料表的運算式中
衍生資料表前面課程已討論過，就不再贅述

檢視表 View

資料表是根據資料模型設計流程所設計出來的物件，而檢視表是從使用者觀點來設計，例如一般員工看的資料和主管看的資料一定不相同，甚至同一個部門不同職位的一般員工之間，他們所看的資料也會不一樣，也因為檢視表不支援「輸入參數值」，某些情形下，必須指定不同常數值在各個檢視表中，所以檢視表的數量會比資料表數量多

因為檢視表不支援「輸入參數值」，所以 SELECT 查詢的篩選條件幾乎都是指定「常數」，例如：WHERE empid = 3
檢視表和資料表的相似之處：
- 檢視表的使用方式和真實資料表相同，所以檢視表又稱為虛擬資料表
- 和標準的使用者自訂資料表類似的是，檢視表是由一組具名資料行和資料列所組成
- 檢視表也允許 DML 作業
檢視表和資料表的不同之處：
- 資料表有綱要結構的定義，例如資料行名稱、資料類型、資料行屬性清單等，另外，本身也儲存資料(記錄)
- 檢視表沒有綱要結構的定義，它是由一個 SELECT 查詢語法的定義，所以本身不可能儲存任何資料(記錄)
檢視表中的 SELECT 查詢語法所參考的資料表支援：
- 一個以上的真實資料表，又稱為基底資料表 (Base Table)
- 另一個檢視表 (但底層最終還是基底資料表)
檢視表的好處：
- 可以隱藏敏感性的資料，例如：薪資
- 將實體資料表隱藏起來，用戶難以得知實際的資料結構，降低資料庫被攻擊的風險
- 隱藏 SELECT 查詢語法的設計邏輯
- 簡化查詢，將高度複雜的查詢包裝在檢視表中，外部程式只需要直接存取該檢視表即可取出需要的資料
- 若檢視表被設計為可更新的檢視表 (Updatable View)，就可以支援 DML 作業，但仍有些許限制，這個部份留待後續再深入介紹

注意事項：

若從網管角度來看，檢視表可視為「防火牆」一樣地只允許特定網路流量通過 ，這相當於檢視表隱藏敏感性欄位只顯示特定欄位，因此可以保護基底資料表

目前為止，所介紹的檢視表皆為使用者自訂的檢視表，只是在原始的 SELECT 查詢定義，套上 CREATE VIEW 的框架

注意事項 (檢視表和衍生資料表比較)

衍生資料表只是非具名的資料表運算式，相關限制和檢視表相同
就使用變數來說：
檢視表：不允許包含變數在查詢定義中
衍生資料表：允許包含變數在查詢定義中
就物件的生命週期來說：
檢視表：因為是具體的物件，也儲存在資料庫中，所以可重複使用
衍生資料表：因為是非具體的物件，也沒有儲存在資料庫中，所以不可重複使用

基本語法：

CREATE VIEW [結構描述名稱.] 檢視表名稱
[WITH 屬性]
AS
SELECT ...省略...
[WITH CHECK OPTION];

WITH 屬性支援：
[ ENCRYPTION ]：加密 select 定義
[ SCHEMABINDING ]：綁定「基底資料表」，以避免它被刪除，導致檢視表無法運作

WITH CHECK OPTION

強制規定對檢視表執行的所有資料修改陳述式，必須遵循 select_statement 所設定的準則
若沒有指定這個屬性，UPDATE、DELETE 仍會遵循 select_statement 所設定的篩選條件，但 INSERT 不受限制
若有指定這個屬性，INSERT 會遵循 select_statement 所設定的篩選條件
可確保已修改的資料在認可之後，仍可以透過檢視表見到資料

Demonstration：檢視表的建立和執行

範例 A：資料來源是單一資料表和多重資料表的檢視表

-- Step 1: 建立來自單一資料表的檢視表
-- Select and execute the following to create a simple view
CREATE VIEW HR.EmpPhoneList
AS
SELECT empid, lastname, firstname, phone
FROM HR.Employees;
GO

-- Select from the new view
SELECT empid, lastname, firstname, phone
FROM HR.EmpPhoneList;
GO

-- Step 2: 建立來自多重資料表的檢視表
-- Create a view using a multi-table join
CREATE VIEW Sales.OrdersByEmployeeYear
AS
    SELECT  emp.empid AS employee ,
            YEAR(ord.orderdate) AS orderyear ,
            SUM(od.qty * od.unitprice) AS totalsales
    FROM    HR.Employees AS emp
            JOIN Sales.Orders AS ord ON emp.empid = ord.empid
            JOIN Sales.OrderDetails AS od ON ord.orderid = od.orderid
    GROUP BY emp.empid ,
            YEAR(ord.orderdate)
GO
-- Select from the view
SELECT employee, orderyear, totalsales
FROM Sales.OrdersByEmployeeYear
ORDER BY employee, orderyear;


-- Step 3: Clean up
DROP VIEW Sales.OrdersByEmployeeYear;
DROP VIEW HR.EmpPhoneList;

範例 B：使用檢視表支援的屬性

-- START：建立測試環境
use TSQL2;

-- 建立「基底資料表」 HR.Copy_Emp
drop table if exists HR.Copy_Emp;
SELECT empid, lastname, firstname, phone INTO HR.Copy_Emp
FROM HR.Employees;
GO

-- 查詢「基底資料表」 HR.Copy_Emp
SELECT * FROM HR.Copy_Emp;
GO
-- END：建立測試環境


-- START：建立加密的檢視表
-- 使用 ENCRYPTION 屬性建立加密的檢視表 HR.EmpPhoneList
CREATE OR ALTER VIEW HR.EmpPhoneList
WITH ENCRYPTION
AS
SELECT empid, lastname, firstname, phone
FROM HR.Copy_Emp;
GO

-- 操作：從「物件總管」觀察檢視表 HR.EmpPhoneList 加密後的狀態

-- END：建立加密的檢視表



-- START：綁定「基底資料表」
-- 使用 SCHEMABINDING 綁定「基底資料表」HR.Copy_Emp
CREATE OR ALTER VIEW HR.EmpPhoneList
WITH SCHEMABINDING
AS
SELECT empid, lastname, firstname, phone
FROM HR.Copy_Emp;
GO

-- 嘗試刪除「基底資料表」HR.Copy_Emp
DROP TABLE HR.Copy_Emp;
GO
-- 訊息：無法 DROP TABLE HR.Copy_Emp，因為物件 'EmpPhoneList' 正在參考它。

-- 解除綁定「基底資料表」
CREATE OR ALTER VIEW HR.EmpPhoneList
AS
SELECT empid, lastname, firstname, phone
FROM HR.Copy_Emp;
GO

-- 嘗試刪除「基底資料表」HR.Copy_Emp
DROP TABLE HR.Copy_Emp;
GO
-- 訊息：命令已成功完成

-- END：綁定「基底資料表」



-- START：強制遵循 select_statement 內所設定的準則 WITH CHECK OPTION
-- 再次建立「基底資料表」 HR.Copy_Emp
drop table if exists HR.Copy_Emp;
SELECT empid, lastname, firstname, phone INTO HR.Copy_Emp
FROM HR.Employees;
GO

-- 建立 select_statement 含準則的檢視表 HR.EmpPhoneList
CREATE OR ALTER VIEW HR.EmpPhoneList
AS
SELECT empid, lastname, firstname, phone
FROM HR.Copy_Emp
WHERE empid between 3 and 5
WITH CHECK OPTION;
GO

-- 查詢檢視表 HR.EmpPhoneList
SELECT *
FROM HR.EmpPhoneList;

-- 對檢視表 HR.EmpPhoneList 嘗試 INSERT
INSERT INTO HR.EmpPhoneList (lastname, firstname, phone) VALUES (N'大明', N'王', N'123-4567')
--訊息：嘗試插入或更新已經失敗，因為目標檢視指定了 WITH CHECK OPTION 或跨越指定了 WITH CHECK OPTION 的檢視，而該作業產生的一個或多個資料列在 CHECK OPTION 條件約束下並不合格。

-- 對檢視表 HR.EmpPhoneList 嘗試 UPDATE
UPDATE HR.EmpPhoneList
SET phone = N'333-5555'
WHERE empid = 8;
--訊息：(0 個資料列受到影響)

-- 對檢視表 HR.EmpPhoneList 嘗試 DELETE
DELETE HR.EmpPhoneList
WHERE empid = 8;
--訊息：(0 個資料列受到影響)

-- 操作：試著移除 WITH CHECK OPTION 再重試對檢視表 HR.EmpPhoneList 嘗試 INSERT、UPDATE、DELETE

-- END：強制遵循 select_statement 內所設定的準則



-- 復原變更，與本主題無關
DROP VIEW HR.EmpPhoneList;
DROP TABLE HR.Copy_Emp;

码农公寓