檢視表 是虛擬資料表,本質是儲存的 SELECT 查詢。它不儲存實體資料(索引檢視表除外),卻能像資料表一樣被查詢、甚至更新。本文詳細解析檢視表的定義、用途、建立/修改/刪除語法與進階應用。
| 用途 | 說明 | 實例 |
|---|---|---|
| 資料安全/存取控制 | 僅暴露特定欄位/列,隱藏敏感欄位(如薪資、身分證號) | CREATE VIEW v_EmpPublic AS SELECT EmpId, Name, Dept FROM Employee; |
| 簡化複雜查詢 | 封裝多表 JOIN、子查詢、聚合邏輯,應用端只需 SELECT * FROM View | 多維報表基礎檢視表 |
| 邏輯資料獨立性 | 底層結構變更(如拆表、欄位重命名)時,只需修改檢視表,應用程式不受影響 | 重構時的緩衝層 |
| 資料彙總/格式化 | 預先計算衍生欄位、格式化輸出 | FullName = LastName + ' ' + FirstName |
CREATE VIEW [schema.]ViewName [(ColumnAlias1, ColumnAlias2, ...)]
[WITH ENCRYPTION | SCHEMABINDING | VIEW_METADATA]
AS
SELECT ColumnList
FROM TableSource
[WHERE Condition]
[GROUP BY ...]
[HAVING ...];
CREATE VIEW dbo.v_OfficeInfo
AS
SELECT
A.Name AS 樓盤名稱,
B.A_Name AS 城市,
C.A_Name AS 區域,
D.A_Name AS 街道
FROM dbo.Office_Info AS A
INNER JOIN dbo.Base_Area AS B ON A.CityId = B.A_ID
INNER JOIN dbo.Base_Area AS C ON A.AreaId = C.A_ID
INNER JOIN dbo.Base_Area AS D ON A.StreetId = D.A_ID;
使用:SELECT * FROM dbo.v_OfficeInfo;
WITH SCHEMABINDING:綁定底層結構,防止被引用的表/欄位被修改/刪除,**建立索引檢視表的前提**。WITH ENCRYPTION:加密定義文本(可被反編譯)。VIEW_METADATA:讓 ODBC/OLEDB 客戶端將檢視表視為可更新資料表。ALTER VIEW dbo.v_Student
AS
SELECT StudentId, Name, ClassName
FROM Students
WHERE IsActive = 1;
IF EXISTS (SELECT * FROM sys.views WHERE name = 'v_OfficeInfo')
DROP VIEW dbo.v_OfficeInfo;
符合以下條件的檢視表可直接 INSERT/UPDATE/DELETE:
WITH CHECK OPTION 的可更新檢視表)。DISTINCT、GROUP BY、HAVING、聚合函數、UNION、衍生欄位、常數欄位。TOP(除非搭配 WITH CHECK OPTION)。WITH CHECK OPTION:確保透過檢視表修改的資料,仍符合檢視表的
WHERE條件,否則拒絕操作。
-- 前提:WITH SCHEMABINDING
CREATE VIEW dbo.v_SalesSummary
WITH SCHEMABINDING
AS
SELECT Ctiy, YEAR(OrderDate) AS OrderYear, COUNT_BIG(*) AS Cnt
FROM dbo.SalesInfo
GROUP BY Ctiy, YEAR(OrderDate);
-- 建立唯一叢集索引 → 物質化
CREATE UNIQUE CLUSTERED INDEX IX_v_SalesSummary
ON dbo.v_SalesSummary (Ctiy, OrderYear);
-- 列出所有使用者檢視表
SELECT name, definition FROM sys.views;
-- 檢視定義文本
EXEC sp_helptext 'dbo.v_OfficeInfo';
檢視表是資料庫設計中「抽象與封裝」的利器,善用它能讓系統更安全、維護更輕鬆、查詢更高效。