创建 MySQL 可更新视图

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

MySQL 可更新视图简介

在 MySQL 中，视图不仅是可查询的，而且是可更新的。这意味着您可以使用INSERT或UPDATE语句通过可更新视图插入或更新基表的行。此外，您可以使用DELETE语句通过视图删除基础表的行。

但是，要创建可更新视图，定义视图的SELECT 语句不得包含以下任何元素：

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

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

请注意，有时可以使用内部联接基于多个表创建可更新视图。

MySQL 可更新视图示例

让我们创建一个可更新的视图。

首先，我们根据示例数据库中的offices表创建一个名为officeInfo的视图。该视图引用了offices表的三列： officeCode phone,和city 。

CREATE VIEW officeInfo
 AS 
   SELECT officeCode, phone, city
   FROM offices;Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    *
FROM
    officeInfo;Code language: SQL (Structured Query Language) (sql)

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

UPDATE officeInfo 
SET 
    phone = '+33 14 723 5555'
WHERE
    officeCode = 4;Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    *
FROM
    officeInfo
WHERE
    officeCode = 4;Code language: SQL (Structured Query Language) (sql)

检查可更新的视图信息

您可以通过从information_schema数据库中的视图表中查询 is_updatable 列来检查数据库中的视图是否可更新。

以下查询从classicmodels 数据库获取所有视图并显示哪些视图可更新。

SELECT 
    table_name, 
    is_updatable
FROM
    information_schema.views
WHERE
    table_schema = 'classicmodels';Code language: SQL (Structured Query Language) (sql)

通过视图删除行

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

-- 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;Code language: SQL (Structured Query Language) (sql)

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

DELETE FROM LuxuryItems 
WHERE
    id = 3;Code language: SQL (Structured Query Language) (sql)

MySQL 返回一条消息，指出 1 行受到影响。

第三，我们再通过视图检查一下数据。

SELECT 
    *
FROM
    LuxuryItems;Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    *
FROM
    items;Code language: SQL (Structured Query Language) (sql)

如您所见，id 为 3 的行已从基表中删除。

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

本教程有帮助吗？