結構化查詢語言/檢視

通常，使用者和應用程式會以與現有表結構不同的形式請求資訊。為了滿足這些請求，SELECT 命令提供了很多可能性：投影、聯接、分組依據子句等等。如果總是相同的請求，尤其是在應用程式中，或者如果表結構有意地應該對應用程式級別隱藏，則可以定義檢視。此外，對檢視的訪問許可權可能與對錶的訪問許可權不同。

檢視看起來像一張表。它們具有特定資料型別的列，可以像檢索表列一樣檢索它們。但檢視只是定義，它們沒有自己的資料！它們的資料始終是表的資料，或者基於其他檢視。檢視是對儲存資料的不同視角，或者類似於預定義的 SELECT。

建立檢視

透過指定檢視的名稱、可選的列名，尤其是檢視所基於的 SELECT 命令來建立一個檢視。在該 SELECT 中，所有元素都像獨立的 SELECT 命令中一樣被允許。如果沒有指定列名，則使用 SELECT 的列名。

CREATE VIEW <view_name> [(column_name, ...)] AS
  SELECT ...  -- as usual
;

示例和解釋

示例 1：隱藏列

作為第一個示例，這裡有檢視 person_view_1，它包含表 person 中除 id 和 ssn 列之外的所有列。有權從該檢視讀取但沒有權從表 person 讀取的使用者沒有訪問 id 和 ssn 的許可權。

CREATE VIEW person_view_1 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person;

-- SELECTs on views have identical syntax as SELECTs on tables
SELECT *
FROM   person_view_1
ORDER BY lastname;

-- The column 'id' is not part of the view. Therefore it is not seen and cannot be used
-- anywhere in SELECTs to person_view_1.
-- This SELECT will generate an error message because of missing 'id' column:
SELECT *
FROM   person_view_1
WHERE  id = 5;

如上面的 'order by' 示例所示，可以在 SELECT 對檢視的任何部分（但不包括表中的所有列！）中使用檢視的所有列：在投影中、WHERE 子句中、ORDER BY 子句中、GROUP BY 子句中和 HAVING 子句中，在函式呼叫中等等。

-- SELECTs on views have identical syntax as SELECTs on tables
SELECT count(lastname), lastname
FROM   person_view_1
GROUP BY lastname
ORDER BY lastname;

示例 2：重新命名列

接下來，對列進行重新命名。表中的列名 lastname 將在檢視中變為 familyname。

-- first technique: list the desired column names within parenthesis after the view name
CREATE VIEW person_view_2a (firstname, familyname, date_of_birth, place_of_birth, weight) AS
  SELECT                    firstname, lastname,   date_of_birth, place_of_birth, weight
  FROM   person;

-- second technique: rename the column in the SELECT part
CREATE VIEW person_view_2b AS
  SELECT firstname, lastname AS familyname, date_of_birth, place_of_birth, weight
  FROM   person;
-- Hint: technique 1 overwrites technique 2

-- Access to person.lastname is possible via person_view_2a.familyname or person_view_2b.familyname.
-- The objects person.familyname, person_view_2a.lastname or person_view_2b.lastname does not exist!

示例 3：應用 WHERE 條件

檢視中不僅僅可以隱藏列。還可以隱藏完整的行，因為檢視定義可能包含 WHERE 子句。

-- Restrict access to few rows
CREATE VIEW person_view_3 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person
  WHERE  place_of_birth in ('San Francisco', 'Richland');

-- Verify result:
SELECT *
FROM   person_view_3;

此檢視僅包含出生在舊金山或里士滿的個人。所有其他人員都被隱藏。因此，以下 SELECT 不會檢索任何內容，儘管表中存在滿足條件的個人。

-- No hit
SELECT *
FROM   person_view_3
WHERE  place_of_birth = 'Dallas';

-- One hit
SELECT *
FROM   person
WHERE  place_of_birth = 'Dallas';

示例 4：使用函式

此示例使用 sum() 函式。

-- 
CREATE VIEW person_view_4 AS
  -- General hint: Please consider that not all columns are available in a SELECT containing a GROUP BY clause
  SELECT lastname, count(lastname) AS count_of_members
  FROM   person
  GROUP BY lastname
  HAVING count(*) > 1;

-- Verify result: 2 rows
SELECT *
FROM   person_view_4;

-- The computed column 'count_of_members' may be part of a WHERE condition. 
-- This SELECT results in 1 row 
SELECT *
FROM   person_view_4
WHERE  count_of_members > 2;

在此示例中，詳細的結構 'GROUP BY / HAVING' 對使用者和應用程式隱藏。

示例 5：聯接

檢視可以使用 JOIN 命令包含來自多個表的列。以下示例檢視包含個人的姓名及其可用的聯絡資訊。由於使用了 INNER JOIN，一些個人會多次出現，而另一些則根本不會出現。

-- Persons and contacts
CREATE VIEW person_view_5 AS
  SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
  FROM   person p
  JOIN   contact c ON p.id = c.person_id;

-- Verify result
SELECT *
FROM   person_view_5;

SELECT *
FROM   person_view_5
WHERE  lastname = 'Goldstein';

列 person.id 和 contact.person_id 在檢視的定義過程中被使用。但它們不是投影的一部分，因此無法用於 SELECT 對檢視進行查詢。

提示：聯接操作的語法和語義在單獨的頁面中進行了解釋。

透過檢視進行寫入訪問

在某些情況下，但並非總是如此，應該可以透過訪問檢視來更改表中的資料（UPDATE、INSERT 或 DELETE 命令）。例如，假設要將 person_view_4 的 count_of_members 列更改為另一個值。DBMS 該怎麼做？該列受聚合函式的影響，該函式統計底層表中現有行的數量。是應該新增更多隨機值到新行中，還是應該刪除隨機行來滿足 count_of_members 的新值？當然不行！

另一方面，非常簡單的檢視，如 'CREATE VIEW person_0 AS SELECT * from person;'，它是一個原始表的 1:1 複製，DBMS 應該能夠處理。可更新檢視和不可更新檢視之間的界限在哪裡？SQL 標準沒有定義它。但具體的 SQL 實現會根據自己的規則提供有限的檢視寫入訪問許可權。有時這些規則非常固定，而其他情況下則包括靈活的技術，如 'INSTEAD OF' 觸發器，以便程式設計師有機會實現自己的規則。

以下是一些一般規則，它們可能包含在實現者定義哪些檢視在該意義上可更新的固定規則中

檢視定義基於一個且僅一個表。它包括此底層表的 PRIMARY KEY。
檢視定義不得使用任何聚合函式。
檢視定義不得有任何 DISTINCT 子句、GROUP BY 子句或 HAVING 子句。
檢視定義不得有任何 JOIN、SUBQUERY、SET 操作、EXISTS 或 NOT EXISTS 謂詞。
許多實現都有一個關鍵字，可以用來強制檢視只讀，即使從技術上講它不必如此。

如果可以對檢視使用 UPDATE、INSERT 或 DELETE 命令，則語法與表相同。

清理示例資料庫

DROP VIEW 語句用於刪除檢視定義。這樣做不會影響底層表的資料。

不要將 DROP 命令（定義）與 DELETE 命令（資料）混淆！

DROP VIEW person_view_1;
DROP VIEW person_view_2a;
DROP VIEW person_view_2b;
DROP VIEW person_view_3;
DROP VIEW person_view_4;
DROP VIEW person_view_5;

練習

建立一個名為 'hobby_view_1' 的檢視，其中包含 'hobby' 表的所有列，除了 'id' 列。
將列 'remark' 重新命名為 'explanation'。建立兩種不同的解決方案。

點選檢視解決方案

CREATE VIEW hobby_view_1a AS
  SELECT hobbyname, remark AS explanation
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_1a;

CREATE VIEW hobby_view_1b (hobbyname, explanation) AS
  SELECT hobbyname, remark
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_1b;

建立一個名為 'hobby_view_2' 的檢視，其標準與上一個示例相同。唯一的區別
是解釋列的長度限制為 30 個字元。提示：使用函式
substr(<列名>, 1, 30) 來確定前 30 個字元 - 這不是 SQL 標準的一部分，但在許多實現中都有效。

點選檢視解決方案

CREATE VIEW hobby_view_2 AS
  SELECT hobbyname, substr(remark, 1, 30) AS explanation
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_2;

建立一個名為 'contact_view_3' 的檢視，其中包含 'contact' 表的所有行，除了 'icq' 行。統計檢視行的數量，並將其與 'contact' 表中的行數進行比較。

點選檢視解決方案

CREATE VIEW contact_view_3 AS
  SELECT *
  FROM   contact
  WHERE  contact_type != 'icq';  -- an alternate operator with the same semantic as '!=' is '<>'

-- Verification
SELECT 'view',  count(*) FROM contact_view_3
  UNION
SELECT 'table', count(*) FROM contact;

建立一個名為 'contact_view_4' 的檢視，其中包含每個聯絡型別及其表示法以及出現次數。然後選擇出現次數超過一次的聯絡型別。

點選檢視解決方案

CREATE VIEW contact_view_4 AS
  SELECT contact_type, count(*) AS cnt
  FROM   contact
  GROUP BY contact_type;

-- Verification
SELECT *
FROM   contact_view_4;

-- Use columns of a view with the same syntax as a column of a table.
SELECT *
FROM   contact_view_4
WHERE  cnt > 1;

建立一個名為 'person_view_6' 的檢視，其中包含人員的姓和名，以及與該人員同名（姓氏）的人員數量。提示：解決方案使用相關子查詢。

點選檢視解決方案

CREATE VIEW person_view_6 AS
  SELECT firstname, lastname, (SELECT count(*) FROM person sq WHERE sq.lastname = p.lastname) AS cnt_family 
  FROM   person p;

-- Verification
SELECT *
FROM   person_view_6;

清理示例資料庫。

點選檢視解決方案

DROP VIEW hobby_view_1a;
DROP VIEW hobby_view_1b;
DROP VIEW hobby_view_2;
DROP VIEW contact_view_3;
DROP VIEW contact_view_4;
DROP VIEW person_view_6;

SELECT: 子查詢

結構化查詢語言
檢視

INSERT 1

建立檢視

示例和解釋

示例 1：隱藏列

示例 2：重新命名列

示例 3：應用 WHERE 條件

示例 4：使用函式

示例 5：聯接

更多提示

透過檢視進行寫入訪問

清理示例資料庫

練習