本文实例讲述了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