MySQL 可更新视图

---

简介：在本教程中，我们将向您展示如何通过视图创建可更新视图并更新基础表中的数据。

---

MySQL可更新视图简介

在MySQL中，视图不仅可查询，还可以更新。这意味着您可以使用INSERT或  UPDATE语句通过可更新视图插入或更新基表的行。此外，您可以使用DELETE语句通过视图删除基础表的行。 但是，要创建可更新视图，定义视图的SELECT语句不得包含以下任何元素：

• 聚合函数  ，如MIN，MAX，SUM，AVG和  COUNT。
• DISTINCT
• GROUP BY子句。
• HAVING子句。
• UNION或UNION ALL子句。
• 左连接或外连接。
• 子查询 中的SELECT子句或在引用表WHERE语句出现在FROM子句中。
• 引用FROM子句中的不可更新视图
• 仅引用文字值
• 对基表的任何列的多次引用

如果使用TEMPTABLE算法创建视图，则无法更新视图。

---

MySQL可更新视图示例

让我们创建一个可更新的视图。 首先，我们创建一个officeInfo  基于示例数据库中的offices  表命名的视图。视图引用 offices 表的三列：officeCode， phone和city

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

接下来，我们可以officeInfo使用以下语句从视图中查询数据：

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 (0.01 sec)

然后，我们可以使用以下UPDATE语句通过officeInfo视图更改officeCode为4的办公室的电话号码。

UPDATE officeInfo 
SET 
    phone = '+33 14 723 5555'
WHERE
    officeCode = 4;

最后，为了验证更改，我们可以officeInfo  通过执行以下查询来查询视图中的数据：

SELECT 
    *
FROM
    officeInfo
WHERE
    officeCode = 4;

+------------+-----------------+-------+
| officeCode | phone           | city  |
+------------+-----------------+-------+
| 4          | +33 14 723 5555 | Paris |
+------------+-----------------+-------+
1 row in set (0.00 sec)

检查可更新的视图信息

您可以通过查询数据库中的views表中的is_updatable列来检查数据库中的视图是否可更新information_schema。 以下查询从classicmodels数据库获取所有视图，并显示哪些视图可更新。

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

通过视图删除行

首先，我们创建一个名为items 的表，在items表中插入一些行，并创建一个包含价格大于700的项的视图。

-- 判断是否存在，如果存在则删除
DROP TABLE IF EXISTS items;

-- 创建表 items
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(11 , 2 ) NOT NULL
);
 
-- 向items 中插入数据
INSERT INTO items(name,price) 
VALUES('Laptop',700.56),('Desktop',699.99),('iPad',700.50) ;
 
-- 创建基于 items 的视图
CREATE VIEW LuxuryItems AS
    SELECT 
        *
    FROM
        items
    WHERE
        price > 700;

-- 查询 LuxuryItems 视图
SELECT 
    *
FROM
    LuxuryItems;

+----+--------+--------+
| id | name   | price  |
+----+--------+--------+
|  1 | Laptop | 700.56 |
|  3 | iPad   | 700.50 |
+----+--------+--------+
2 rows in set (0.00 sec)

其次，我们使用DELETE 语句删除id值为3的行。

DELETE FROM LuxuryItems 
WHERE
    id = 3;

MySQL返回一条消息，说明有1行受到影响。 第三，让我们再次通过视图检查数据。

SELECT 
    *
FROM
    LuxuryItems;

+----+--------+--------+
| id | name   | price  |
+----+--------+--------+
|  1 | Laptop | 700.56 |
+----+--------+--------+
1 row in set (0.00 sec)

第四，我们还可以查询基表中的数据，items以验证DELETE语句是否实际删除了行。

SELECT 
    *
FROM
    items;

+----+---------+--------+
| id | name    | price  |
+----+---------+--------+
|  1 | Laptop  | 700.56 |
|  2 | Desktop | 699.99 |
+----+---------+--------+
2 rows in set (0.00 sec)

如您所见，ID为3的行已从基表中删除。 在本教程中，我们向您展示了如何通过视图创建可更新视图并更新基础表中的数据。

---