mysql的视图更新是指通过update,insert,delete等操作来更新基本表(自己权限内)。
/*
就以上次建立的有?乱码的视图表person_view1和基本表person为操作的对象。
基本表和视图表改变之前:
*/
mysql> select * from person;
+----------+------+
| name | age |
+----------+------+
| ?Stephen | 19 |
| Elena | 18 |
| Demon | 19 |
| David | 20 |
| jordan | 25 |
| James | 24 |
| Jane | 23 |
| Dannis | 23 |
| Rose | 27 |
| NULL |
+----------+------+
10 rows in set (0.07 sec)
mysql> select * from person_view1;
+----------+------+
| P_nm | P_ag |
+----------+------+
| ?Stephen | 19 |
| Elena | 18 |
| Demon | 19 |
| David | 20 |
| jordan | 25 |
| James | 24 |
| Jane | 23 |
| Dannis | 23 |
| Rose | 27 |
| NULL |
+----------+------+
10 rows in set (0.04 sec)
mysql> update person_view1 set p_nm='Stephen' where p_nm like '%Stephen';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
/*视图表和基本表更改之后:*/
mysql> select * from person_view1;
+---------+------+
| P_nm | P_ag |
+---------+------+
| Stephen | 19 |
| Elena | 18 |
| Demon | 19 |
| David | 20 |
| jordan | 25 |
| James | 24 |
| Jane | 23 |
| Dannis | 23 |
| Rose | 27 |
| NULL |
+---------+------+
10 rows in set (0.00 sec)
mysql> select * from person;
+---------+------+
| name | age |
+---------+------+
| Stephen | 19 |
| Elena | 18 |
| Demon | 19 |
| David | 20 |
| jordan | 25 |
| James | 24 |
| Jane | 23 |
| Dannis | 23 |
| Rose | 27 |
| NULL |
+---------+------+
10 rows in set (0.00 sec)
/* 查看基本表的数据:*/
在下面情况下视图是不能更新的:
视图中有聚合函数得到的值;
视图定义用到了Group by, having,union 等关键字;
创建视图时algorithm为temptable。
综上,视图的数据和基本表的数据不一样不能正常修改视图。
mysql> update per_pet set p1='Stephen' where p1 like '%Stephen';
ERROR 1288 (HY000): The target table per_pet of the UPDATE is not updatable
mysql>
mysql> update per_pet set p1='Stephen' where p1 like '%Stephen';
ERROR 1288 (HY000): The target table per_pet of the UPDATE is not updatable
/*
其他情况下是能够正常工作的:
*/
mysql> update person_view1 set P_nm='Rodman' where P_nm='Dannis';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from person;
+---------+------+
| name | age |
+---------+------+
| Stephen | 19 |
| Elena | 18 |
| Demon | 19 |
| David | 20 |
| jordan | 25 |
| James | 24 |
| Jane | 23 |
| Rodman | 23 |
| Rose | 27 |
| NULL |
+---------+------+
10 rows in set (0.00 sec)
mysql> insert into person_view1 values('Kobe', 24);
Query OK, 1 row affected (0.12 sec)
mysql> select * from person;
+---------+------+
| name | age |
+---------+------+
| Stephen | 19 |
| Elena | 18 |
| Demon | 19 |
| David | 20 |
| jordan | 25 |
| James | 24 |
| Jane | 23 |
| Rodman | 23 |
| Rose | 27 |
| NULL |
| Kobe | 24 |
+---------+------+
11 rows in set (0.00 sec)
/* 额,那个NULL是导入文件数据产生的问题 */
删除视图:
只是删除视图的定义,不会删除数据。
drop view if exists 视图名
mysql> use mydb;
Database changed
mysql> drop view if exists per_pet;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from per_pet;
ERROR 1146 (42S02): Table 'mydb.per_pet' doesn't exist
mysql>