SQL 視圖完整操作指南

視圖 是基於 SQL 查詢結果集的 虛擬表。它包含行與欄位，字段來源於一或多個真實資料表。視圖可封裝複雜邏輯 (函數、WHERE、JOIN)，簡化查詢並增強安全性。

CREATE VIEW - 建立視圖

語法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

特性：視圖總是顯示最新資料！每次查詢視圖時，資料庫引擎透過定義的 SQL 重建資料。

實戰範例：Northwind 範例資料庫

範例 1：當前產品清單 (未停產)

CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;

查詢：SELECT * FROM [Current Product List];

範例 2：高於平均價格的產品

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

範例 3：1997 年分類銷售統計 (巢狀視圖)

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;

加條件查詢飲料類銷售：SELECT * FROM [Category Sales For 1997] WHERE CategoryName = 'Beverages';

CREATE OR REPLACE VIEW - 更新視圖

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

範例：為當前產品清單新增 Category 欄位

CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;

DROP VIEW - 刪除視圖

DROP VIEW view_name;