專欄作者新書出版：Android App程式開發剖析 第三版（適用Android 8 Oreo與Android Studio 3）

MySQL 超新手入門（10）子查詢 << 前情

1 View的應用

在使用MySQL資料庫的時候，你會使用各種不同的SQL敘述來執行查詢與維護的工作。資料庫在運作一段時間後，你會發覺不論是查詢與維護的敘述，都可能會出現一些類似、而且很常使用的SQL敘述：

以上列的查詢敘述來說，雖然它並不是很複雜，只是一個加入排序設定的一般查詢而已。可是如果常常會執行這樣的查詢，你每次都要輸入這個查詢敘述再執行它；就算你把這個查詢敘述儲存為文字檔保存起來，需要的時候再開啟檔案使用，這樣做的話是比較方便一些，不過還是很麻煩，而且比較沒有靈活性。

如果在資料庫的應用中，出現這種很常執行的查詢敘述時，你可以在MySQL資料庫中建立一種「View」元件，View元件用來保存一段你指定的查詢敘述：

建立好需要的View元件以後，除了有一些限制外，它使用起來就像是一個表格，所以當你需要執行這樣的查詢時，可以在查詢敘述的「FROM」子句指定一個View元件：

也有很多人稱「View」元件是一種「虛擬表格」，因為它不是一個真正儲存紀錄資料的表格，可是它又跟表格的用法類似。所以如果有需要的話，你也可以使用View元件回傳的紀錄資料，執行統計、分組與其它需要的處理：

View元件就像是一個表格，大部份使用表格可以完成的工作，也可以套用在View元件。所以把View元件和表格一起放在「FROM」子句中，執行需要的結合查詢也是可以的：

2 建立需要的View

不論是為了查詢或維護，如果你很常需要使用到同一個查詢敘述，你就可以考慮建立一個View元件把這個查詢敘述儲存起來。下列是建立View元件基本的語法：

如果你很常執行查詢「每個地區GNP最高的國家」資料，這樣的需求可以使用子查詢來完成，為了不想要每次重複輸入這個查詢敘述，你可以建立一個名稱「CountryMaxGNP」的View元件，這樣以後要執行這個查詢的時候就方便多了：

在上列建立View元件的範例中，只有「Name」與「GNP」兩個欄位，如果想要在已經建立好的「CountryMaxGNP」的View元件中，再加入新的「Code」欄位的話：

如果需要修改一個已經建立好的View元件，你就要加入「OR REPLACE」的設定，這樣才不會出現錯誤訊息：

如果想要查詢一個View元件中會傳回哪些欄位的資料，可以使用「DESCRIBE」或是比較簡短的「DESC」指令：

下列是MySQL關於View元件的規定與限制：

• 在同一個資料庫中，View的名稱不可以重複，也不可以跟表格名稱一樣
• View不可以跟Triggers建立聯結

儲存在View中的查詢敘述也有下列的規定：

• 查詢敘述中只能使用到已存在的表格或View
• 「FROM」子句中不可以使用子查詢
• 不可以使用「TEMPORARY」表格
• 不可以使用自行定義的變數、Procedure與Prepared statement參數

註：「TEMPORARY」表格在「表格與索引、建立表格、建立暫存表格」中討論。「Triggers」、定義變數、「Procedure」與「Prepared statement」在後面都會有章節詳細的討論。

結合查詢在關聯式資料庫中幾乎是必要的一種查詢，以下列查詢「國家與城市人口比例」的需求來說，就需要從「country」與「city」表格中查詢必要的欄位資料：

如果會經常執行這個結合查詢的話，你應該會很希望把它儲存為View元件：

你不會在一個表格中，為不同的兩個欄位取一樣的名稱；在使用查詢敘述提供View元件的欄位時，也要注意名稱重複的問題，雖然在單純的結合查詢回傳的資料中，有一樣的欄位名稱並不會造成錯誤。要解決這個錯誤有兩種方式，第一種是在查詢敘述的「SELECT」子句中，自己為名稱重複的欄位取不同的欄位別名：

另外一種方式可以在建立View元件的時候，另外指定View元件的欄位名稱：

這樣的作法不用修改查詢敘述，依照查詢敘述回傳的欄位順序，另外指定View元件使用的欄位名稱：

3 修改View

使用「ALTER VIEW」敘述，可以讓你修改一個已經建立好的View元件：

下列的範例使用「ALTER VIEW」敘述修改已經存在的「CountryMaxGNP」View元件：

上列範例執行的工作也可以使用「CREATE OR REPLACE VIEW」敘述來完成：

如果以修改View元件的工作來說，使用「ALTER VIEW」或「CREATE OR REPLACE VIEW」敘述的效果是完全一樣的。唯一的差異是要修改View元件如果不存在的話，「CREATE OR REPLACE VIEW」敘述會直接建立新的View元件：

4 刪除View

下列的語法可以刪除一個不需要的View元件：

如果「DROP VIEW」敘述指定的View元件不存在的話，執行敘述以後會產生錯誤訊息：

你可以在「DROP VIEW」敘述加入「IF EXISTS」，這樣就可以防止產生View元件不存在的錯誤訊息：

5 資料維護與View

View元件除了提供比較方便的查詢方式外，你也可以使用View元件來執行資料維護的工作。與View元件應用在查詢資料時提供的方便性一樣，不使用表格元件，而使用View元件來執行新增、修改或刪除的工作，也可以增加資料維護的方便性。

要使用View元件來執行新增、修改或刪除的工作，View元件所包含的查詢敘述必須符合下列的規則：

• 不可以包含計算或函式的欄位
• 只允許一對一的結合查詢
• View元件的「ALGORITHM」不可以設定為「TEMPTABLE」

如果符合上列規定的View元件，就會稱為「可修改的View元件、updattable views」。只有可修改的View元件，可以使用在「INSERT」、「UPDATE」或「DELETE」敘述中執行資料維護的工作。

註：View元件的「ALGORITHM」設定在這一章後面的「View的演算法」中討論。

5.1 使用View元件執行資料維護

下列是一個可以執行資料維戶的View元件，它的欄位沒有包含計算或函式，也沒有使用結合查詢：

如果要修改員工編號「7844」的佣金為600的話，你除了可以在「UPDATE」敘述中指定修改的表格名稱為「emp」外，也可以在「UPDATE」敘述中指定View元件「EmpDept30View」：

在執行上列的「UPDATE」敘述以後，不論是查詢View元件或表格，都可以確定資料已經修改了：

使用「INSERT」敘述新增紀錄時，也可以指定View元件「EmpDept30View」：

在執行上列的「INSERT」敘述以後，查詢View元件所得到的結果並沒有剛才新增的員工資料，查詢表格時才可以確定資料已經新增，這是因為新增紀錄的部門編號欄位資料為「NULL」的關係：

與「INSERT」和「UPDATE」敘述一樣，「DELETE」敘述也可以指定View元件的紀錄資料：

不過執行上列的刪除敘述後，千萬不要以為你已經刪除員工編號「9001」的員工紀錄了：

5.2 使用「WITH CHECK OPTION」

你可以使用View元件來執行資料維護的工作，可是在執行新增或修改的時候，又可能會造成一些有問題的資料。如果你不希望產生這類的問題，你可以為View元件加入「WITH CHECK OPTION」的設定：

加入「WITH CHECK OPTION」設定的View元件，在執行資料維護工作時，會先執行檢查的工作，規則是一定要符合「View元件中WHERE設定的條件」：

因為上列範例所新增的紀錄資料，「deptno」欄位會儲存「NULL」值，這樣就違反View元件中「WHERE deptno = 30」的條件設定了，所以在執行以後會產生錯誤訊息。下列的修改敘述就可以正確的執行：

View元件中的「WITH CHECK OPTION」設定，還有額外的「CASCADE」和「LOCAL」兩個控制檢查範圍的設定：

會有「CASCADE」和「LOCAL」這兩個設定的原因，是因為View元件的資料來源可以一個表格，也可以是一個View元件：

查詢「EmpDept20View」後，傳回的紀錄資料包含「deptno = 20」條件，與設定在「EmpSalaryView」的「salary >= 1500」條件：

檢查範圍設定為「LOCAL」的View元件，在執行資料維護的時候，只會檢查是否符合自己的條件設定：

如果執行資料維護的敘述違反「EmpSalaryView」的條件設定，還是可以正確的執行：

如果你希望所有的View元件在執行資料維護的時候，都不可以出現這類的問題，就應該把View元件的檢查範圍設定為「CASCADE」：

檢查範圍設定為「CASCADE」的View元件，在執行資料維護的時候，就不能違反所有VIew元件的條件設定：

6 View的演算法

View元件可以提供更方便的資料查詢與維護方式，在你建立View元件的時候，除了指定的查詢敘述要符合規定，還可以指定資料庫執行View元件時所使用的「演算法、algorithm」：

一般來說，你不需要特別指定View元件使用的演算法。如果在建立View元件的時候，沒有指定使用的演算法為「MERGE」或「TEMPTABLE」，MySQL會設定為「UNDEFINED」，這個設定表示MySQL會依照View元件中包含的敘述，自動選擇一個適合的演算法，可能是「MERGE」或「TEMPTABLE」。

下列是一個演算法設定為「MERGE」的View元件，在MySQL資料庫中的運作情形：

下列是一個演算法設定為「TEMPTABLE」的View元件，在MySQL資料庫中的運作情形：

並不是所有的View元件都可以指定演算法設定為「MERGE」，以下列查詢員工統計資訊的敘述來說：

如果執行下列建立View元件的敘述，就會產生警告的訊息：

如果View元件包含的查詢敘述有下列的情況，MySQL都會自動把演算法設定為「UNDEFINED」：

• 群組函式：SUM()、MIN()、MAX()、COUNT()
• DISTINCT
• GROUP BY
• HAVING
• UNION或UNION ALL
• 「SELECT」子句中包含一個明確的值，而不是表格的欄位

7 View的維護與資訊

7.1 檢驗View的正確性

在你建立一個View元件的時候，MySQL會檢查View元件包含的查詢敘述是否正確，如果沒有問題的話，才會儲存View元件的設定。不過以下列的範例來說：

如果不小心刪除「EmpSalaryView」這個View元件：

執行查詢「EmpDept20View」的時候，就會產生警告訊息了：

這樣的問題也可以經由使用檢查表格或View元件的敘述發現：

執行檢查「EmpDept20View」的敘述可以發現這是一個有問題的View元件：

7.2 取得View的相關資訊

MySQL資料庫在啟動以後，會有一個很特別的資料庫，名稱是「information_schema」，這個資料庫通常會稱為「系統資訊資料庫」。這個資料庫中有一個表格叫作「VIEWS」，它儲存所有MySQL資料庫中View元件的相關資訊，「VIEWS」表格有下列主要的欄位：

執行下列的敘述就可以查詢資料庫中的View元件資訊：

後續 >> MySQL 超新手入門（12）Prepared Statement