MySQL 超新手入門(11)Views
專欄作者新書出版:Android App程式開發剖析 第三版(適用Android 8 Oreo與Android Studio 3) 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中的查詢敘述也有下列的規定:
註:「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 資料維護與ViewView元件除了提供比較方便的查詢方式外,你也可以使用View元件來執行資料維護的工作。與View元件應用在查詢資料時提供的方便性一樣,不使用表格元件,而使用View元件來執行新增、修改或刪除的工作,也可以增加資料維護的方便性。 要使用View元件來執行新增、修改或刪除的工作,View元件所包含的查詢敘述必須符合下列的規則:
如果符合上列規定的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」:
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元件資訊: |
Roya
07/26
您好,看到一個應該是手誤(?)的部分?
5 資料維護與View
可以使用在「INSERT」、「UPDATE」或「UPDATE」敘述中執行資料維護的工作。
→UPDATE寫了兩次
David Hui
04/22
您好,請問
5 資料維護與View 中的第2點 只允許一對一的結合查詢
是指view只能是2個table的joining還是指可以由多個table以INNER JOIN結合?謝謝您
mythnc
09/12
這篇文章沒有貼出下一篇的連結網址 :p
http://www.codedata.com.tw/database/mysql-tutorial-12-prepared-statement/