MySQL 视图更新及删除

MySQL 视图更新及删除

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 updatablemysql>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: 0mysql> 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 changedmysql> 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 existmysql>

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。