在本教程中，我们将向您展示如何通过视图创建可更新视图并更新基础表中的数据。
MySQL可更新视图简介
在MySQL中，视图不仅是可查询的，而且是可更新的。这意味着您可以使用INSERT或UPDATE语句通过可更新视图插入或更新基表的行。
另外，您可以使用DELETE语句通过视图删除底层表的行。
但是，要创建可更新视图，定义视图的SELECT语句不能包含以下任何元素：
聚合函数，如：MIN，MAX，SUM，AVG，COUNT等。
DISTINCT子句
GROUP BY子句
HAVING子句
UNION或UNION ALL子句
左连接或外连接。
SELECT子句中的子查询或引用该表的WHERE子句中的子查询出现在FROM子句中。
引用FROM子句中的不可更新视图
仅引用文字值
对基表的任何列的多次引用
如果使用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行受到影响。
第三步，再次通过视图检查数据。
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的行在基表中被删除。
在本教程中，我们向您展示了如何通过创建可更新视图，并更新基础表中的数据。 |
