MS SQL 學習紀錄-6

• 子查詢 (續)
• • 深入探討子查詢
  • 子查詢的執行次數
  • 相互關聯子查詢 Correlated Subqueries
• 子查詢的進階應用
• • 衍生資料表 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：使用和實作多重值子查詢

1. 查詢銷售人員 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

2. 供應商 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

1. 查詢哪些訂單訂購商品的售出價格 (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 --< 典型
			)
	)

2. 查詢哪些訂單訂購商品的售出價格 (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)

資料表運算式比較表：

注意事項：
僅允許來自一個基底資料表
不允許來自函數產生的資料行，例如：針對 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;