全部教學 » MySQL建立更新的檢視

MySQL建立更新的檢視

2019-10-16 22:56:07

在本教學中，我們將向您展示如何通過檢視建立可更新檢視並更新基礎表中的資料。

MySQL可更新檢視簡介

在MySQL中，檢視不僅是可查詢的，而且是可更新的。這意味著您可以使用INSERT或UPDATE語句通過可更新檢視插入或更新基表的行。 另外，您可以使用DELETE語句通過檢視刪除底層表的行。

但是，要建立可更新檢視，定義檢視的SELECT語句不能包含以下任何元素：

如果使用TEMPTABLE演算法建立檢視，則無法更新檢視。

請注意，有時可以使用內部連線建立基於多個表的可更新檢視。

MySQL可更新檢視範例

讓我們先來看看如何建立一個可更新的檢視。

首先，基於範例資料庫(yiibaidb)中的offices表建立一個名為officeInfo的檢視。該檢視指的是offices表中的三列：officeCode，phone 和 city。

CREATE VIEW officeInfo
 AS 
   SELECT officeCode, phone, city
   FROM offices;

接下來，使用以下語句從officeInfo檢視中查詢資料：

SELECT 
    *
FROM
    officeInfo;

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

mysql> SELECT * FROM officeInfo;
+------------+------------------+---------------+
| officeCode | phone            | city          |
+------------+------------------+---------------+
| 1          | +1 650 219 4782  | San Francisco |
| 2          | +1 215 837 0825  | Boston        |
| 3          | +1 212 555 3000  | NYC           |
| 4          | +33 14 723 4404  | Paris         |
| 5          | +86 33 224 5000  | Beijing       |
| 6          | +61 2 9264 2451  | Sydney        |
| 7          | +44 20 7877 2041 | London        |
+------------+------------------+---------------+
7 rows in set

然後，使用以下UPDATE語句通過officeInfo視圖更改officeCode的值為：4的辦公室電話號碼。

UPDATE officeInfo 
SET 
    phone = '+86 089866668888'
WHERE
    officeCode = 4;

最後，驗證更改結果，通過執行以下查詢來查詢officeInfo檢視中的資料：

mysql> SELECT 
    *
FROM
    officeInfo
WHERE
    officeCode = 4;

+------------+------------------+-------+
| officeCode | phone            | city  |
+------------+------------------+-------+
| 4          | +86 089866668888 | Paris |
+------------+------------------+-------+
1 row in set

檢查可更新檢視資訊

通過從information_schema資料庫中的views表查詢is_updatable列來檢查資料庫中的檢視是否可更新。

以下查詢語句將查詢yiibaidb資料庫獲取所有檢視，並顯示哪些檢視是可更新的。

SELECT 
    table_name, is_updatable
FROM
    information_schema.views
WHERE
    table_schema = 'yiibaidb';

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

+------------------+--------------+
| table_name       | is_updatable |
+------------------+--------------+
| aboveavgproducts | YES          |
| bigsalesorder    | YES          |
| customerorders   | NO           |
| officeinfo       | YES          |
| saleperorder     | NO           |
+------------------+--------------+
5 rows in set

通過檢視刪除行

首先，建立一個名為items的表，在items表中插入一些行，並建立一個查詢包含價格大於700的項的檢視。

USE testdb;
-- create a new table named items
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(11 , 2 ) NOT NULL
);

-- insert data into the items table
INSERT INTO items(name,price) 
VALUES('Laptop',700.56),('Desktop',699.99),('iPad',700.50) ;

-- create a view based on items table
CREATE VIEW LuxuryItems AS
    SELECT 
        *
    FROM
        items
    WHERE
        price > 700;
-- query data from the LuxuryItems view
SELECT 
    *
FROM
    LuxuryItems;

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

+----+--------+--------+
| id | name   | price  |
+----+--------+--------+
|  1 | Laptop | 700.56 |
|  3 | iPad   | 700.5  |
+----+--------+--------+
2 rows in set

其次，使用DELETE語句來刪除id為3的行。

DELETE FROM LuxuryItems 
WHERE
    id = 3;

MySQL返回一條訊息，表示有1行受到影響。

Query OK, 1 row affected

第三步，再次通過檢視檢查資料。

mysql> SELECT * FROM LuxuryItems;
+----+--------+--------+
| id | name   | price  |
+----+--------+--------+
|  1 | Laptop | 700.56 |
+----+--------+--------+
1 row in set

第四步，還可以從基表items查詢資料，以驗證DELETE語句是否實際刪除了該行。

mysql> SELECT  * FROM items;
+----+---------+--------+
| id | name    | price  |
+----+---------+--------+
|  1 | Laptop  | 700.56 |
|  2 | Desktop | 699.99 |
+----+---------+--------+
2 rows in set

如上面所示，ID為3的行在基表中被刪除。

在本教學中，我們向您展示了如何通過建立可更新檢視，並更新基礎表中的資料。