MySQL管理檢視

2019-10-16 22:56:12

在本教學中,您將學習如何管理MySQL中的檢視,包括顯示,修改和刪除檢視。

檢視檢視定義

MySQL提供了用於顯示檢視定義的SHOW CREATE VIEW語句。

以下是SHOW CREATE VIEW語句的語法:

SHOW CREATE VIEW [database_name].[view_ name];

要顯示檢視的定義,需要在SHOW CREATE VIEW子句之後指定檢視的名稱。

為了更好的演示,我們先來建立一個檢視

假設根據employees表建立一個簡單的檢視用來顯示公司組織結構:

USE yiibaidb;
CREATE VIEW organization AS
    SELECT 
        CONCAT(E.lastname, E.firstname) AS Employee,
        CONCAT(M.lastname, M.firstname) AS Manager
    FROM
        employees AS E
            INNER JOIN
        employees AS M ON M.employeeNumber = E.ReportsTo
    ORDER BY Manager;

從以上檢視中查詢資料,得到以下結果 -

mysql> SELECT * FROM organization;
+------------------+------------------+
| Employee         | Manager          |
+------------------+------------------+
| BondurLoui       | BondurGerard     |
| CastilloPamela   | BondurGerard     |
| JonesBarry       | BondurGerard     |
| HernandezGerard  | BondurGerard     |
.......此處省略了一大波資料.......
| KatoYoshimi      | NishiMami        |
| KingTom          | PattersonWilliam |
| MarshPeter       | PattersonWilliam |
| FixterAndy       | PattersonWilliam |
+------------------+------------------+
24 rows in set

要顯示檢視的定義,請使用SHOW CREATE VIEW語句如下:

SHOW CREATE VIEW organization;

還可以使用任何純文字編輯器(如記事本)顯示檢視的定義,以開啟資料庫檔案夾中的檢視定義檔案。

例如,要開啟organization檢視定義,可以使用以下路徑找到檢視定義檔案:\data\yiibaidb\organization.frm

但是,不應該直接在.frm檔案中修改檢視的定義。

修改檢視

MySQL提供兩個語句,允許您修改現有檢視:ALTER VIEWCREATE OR REPLACE VIEW

使用ALTER VIEW語句修改檢視

建立檢視後,可以使用ALTER VIEW語句修改檢視。

ALTER VIEW語句的語法類似於CREATE VIEW語句,除了CREATE關鍵字被ALTER關鍵字替換外,其它都一樣。

ALTER
 [ALGORITHM =  {MERGE | TEMPTABLE | UNDEFINED}]
  VIEW [database_name].  [view_name]
   AS 
 [SELECT  statement]

以下語句通過新增email列來演示如何修改organization檢視。

ALTER VIEW organization
  AS 
  SELECT CONCAT(E.lastname,E.firstname) AS Employee,
         E.email AS  employeeEmail,
         CONCAT(M.lastname,M.firstname) AS Manager
  FROM employees AS E
  INNER JOIN employees AS M
    ON M.employeeNumber = E.ReportsTo
  ORDER BY Manager;

要驗證更改,可以從organization檢視中查詢資料:

SELECT 
    *
FROM
    Organization;

執行上面查詢語句,得到以下結果 -



使用CREATE OR REPLACE VIEW語句修改檢視

ALTER VIEW語句外,還可以使用CREATE OR REPLACE VIEW語句來建立或替換現有檢視。如果一個檢視已經存在,MySQL只會修改檢視。如果檢視不存在,MySQL將建立一個新的檢視。

以下語句使用CREATE OR REPLACE VIEW語法根據employees表建立一個名稱為v_contacts的檢視:

CREATE OR REPLACE VIEW v_contacts AS
    SELECT 
        firstName, lastName, extension, email
    FROM
        employees;
-- 查詢檢視資料
SELECT * FROM v_contacts;

執行上面查詢語句,得到以下結果 -

+-----------+-----------+-----------+--------------------------------+
| firstName | lastName  | extension | email                          |
+-----------+-----------+-----------+--------------------------------+
| Diane     | Murphy    | x5800     | [email protected]             |
| Mary      | Hill      | x4611     | [email protected]           |
| Jeff      | Firrelli  | x9273     | [email protected]           |
| William   | Patterson | x4871     | [email protected]          |
| Gerard    | Bondur    | x5408     | [email protected]              |
| Anthony   | Bow       | x5428     | [email protected]                 |
| Leslie    | Jennings  | x3291     | [email protected]           |
.............. 此處省略了一大波資料 ..................................
| Martin    | Gerard    | x2312     | [email protected]              |
| Lily      | Bush      | x9111     | [email protected]           |
| John      | Minsu     | x9112     | [email protected] |
+-----------+-----------+-----------+--------------------------------+
25 rows in set

假設您要將職位(jobtitle)列新增到v_contacts檢視中,只需使用以下語句 -

CREATE OR REPLACE VIEW v_contacts AS
    SELECT 
        firstName, lastName, extension, email, jobtitle
    FROM
        employees;
-- 查詢檢視資料
SELECT * FROM v_contacts;

執行上面查詢語句後,可以看到新增一列資料 -

+-----------+-----------+-----------+--------------------------------+----------------------+
| firstName | lastName  | extension | email                          | jobtitle             |
+-----------+-----------+-----------+--------------------------------+----------------------+
| Diane     | Murphy    | x5800     | [email protected]             | President            |
| Mary      | Hill      | x4611     | [email protected]           | VP Sales             |
| Jeff      | Firrelli  | x9273     | [email protected]           | VP Marketing         |
...................  此處省略了一大波資料 ....................................................
| Yoshimi   | Kato      | x102      | [email protected]                | Sales Rep            |
| Martin    | Gerard    | x2312     | [email protected]              | Sales Rep            |
| Lily      | Bush      | x9111     | [email protected]           | IT Manager           |
| John      | Minsu     | x9112     | [email protected] | SVP Marketing        |
+-----------+-----------+-----------+--------------------------------+----------------------+
25 rows in set

刪除檢視

建立檢視後,可以使用DROP VIEW語句將其刪除。下面說明了DROP VIEW語句的語法:

DROP VIEW [IF EXISTS] [database_name].[view_name]

IF EXISTS是語句的可選子句,它允許您檢查檢視是否存在。它可以避免刪除不存在的檢視的錯誤。

例如,如果要刪除organization檢視,可以按如下所示使用DROP VIEW語句:

DROP VIEW IF EXISTS organization;

每次修改或刪除檢視時,MySQL會將檢視定義檔案備份到/database_name/arc/目錄中。 如果您意外修改或刪除檢視,可以從/database_name/arc/檔案夾獲取其備份。

在本教學中,您已經學會了如何管理MySQL中的檢視,包括顯示,修改和刪除檢視。