MySQL 视图更新及删除

最新推荐文章于 2024-08-01 19:05:56 发布

theArcticOcean

最新推荐文章于 2024-08-01 19:05:56 发布

阅读量3.7k

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/theArcticOcean/article/details/50641004

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

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>