mysql视图之创建可更新视图的方法详解

本文实例讲述了mysql视图之创建可更新视图的方法。分享给大家供大家参考，具体如下：

我们知道，在mysql中，视图不仅是可查询的，而且是可更新的。这意味着我们可以使用insert或update语句通过可更新视图插入或更新基表的行。 另外，我们还可以使用delete语句通过视图删除底层表的行。但是，要创建可更新视图，定义视图的select语句不能包含以下任何元素：

• 聚合函数，如：min，max，sum，avg，count等。
• DISTINCT子句
• GROUP BY子句
• HAVING子句
• 左连接或外连接。
• UNION或UNION ALL子句
• SELECT子句中的子查询或引用该表的where子句中的子查询出现在FROM子句中。
• 引用FROM子句中的不可更新视图
• 仅引用文字值
• 对基表的任何列的多次引用

我们如果使用temptable算法创建视图，则无法更新视图，不过有时可以使用内部连接创建基于多个表的可更新视图。废话不多说，让我们先来看看如何创建一个可更新的视图。我们先来尝试基于offices表创建一个名为officeInfo的视图，它指的是offices表中的三列：officeCode，phone 和 city：

?

1 2 3 4

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

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

?

1 2 3 4

SELECT    * FROM    officeInfo;

执行上面查询语句，得到以下结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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的办公室电话号码：

?

1 2 3 4 5

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

最后，验证更改结果，通过执行以下查询来查询officeInfo视图中的数据：

?

1 2 3 4 5 6

mysql> SELECT    * FROM    officeInfo WHERE    officeCode = 4;

?

1 2 3 4 5 6

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

完事我们可以通过从information_schema数据库中的views表查询is_updatable列来检查数据库中的视图是否可更新，比如，我们来查询luyaran数据库获取所有视图，并显示哪些视图是可更新的：

?

1 2 3 4 5 6

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

执行上面查询语句，得到以下结果：

?

1 2 3 4 5 6 7 8 9 10

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

我们再来尝试通过视图删除行，首先，创建一个名为items的表，在items表中插入一些行，并创建一个查询包含价格大于700的项的视图：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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;

执行上面查询语句后，得到以下结果：

?

1 2 3 4 5 6 7

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

完事使用DELETE语句来删除id为3的行：

?

1 2 3

DELETE FROM LuxuryItems WHERE    id = 3;

mysql返回一条消息，表示有1行受到影响：

Query OK, 1 row affected

我们来再次通过视图检查数据：

?

1 2 3 4 5 6 7

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

我们还可以从基表items查询数据，以验证DELETE语句是否实际删除了该行：

?

1 2 3 4 5 6 7 8

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

我们可以看到，ID为3的行在基表中被删除。

好啦，本次记录就到这里了。

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://blog.csdn.net/luyaran/article/details/81017477