42、MySQL-更新视图

更新视图格式：

      UPDATE 视图名 ...

mysql> ALTER VIEW v2

-> (id,name,age)

-> AS

-> SELECT id,username,age FROM employee;

Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM v2;

+----+-----------+------+

| id | name | age |

+----+-----------+------+

| 1 | 张三 | 21 |

| 2 | 李四 | 32 |

| 3 | 王五 | 26 |

| 4 | 赵六 | 32 |

| 5 | Mr Adword | 55 |

| 6 | 田七 | 19 |

| 7 | 孙八 | 62 |

| 8 | Mr lili | 45 |

+----+-----------+------+

— 更新数据库

mysql> UPDATE v2 SET age=25 WHERE id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

— 可以看到原数据表已经修改

mysql> SELECT * FROM employee;

+----+-----------+------+---------+--------+-------+------+

| id | username | age | salary | sex | depId | pId |

+----+-----------+------+---------+--------+-------+------+

| 1 | 张三 | 25 | 5432.12 | 男 | 1 | 1 |

| 2 | 李四 | 32 | 6432.00 | 男 | 2 | 2 |

| 3 | 王五 | 26 | 5932.92 | 女 | 3 | 3 |

| 4 | 赵六 | 32 | 6232.14 | 男 | 4 | 4 |

| 5 | Mr Adword | 55 | 9432.99 | 男 | 4 | 5 |

| 6 | 田七 | 19 | 4932.92 | 保密 | 1 | 3 |

| 7 | 孙八 | 62 | 9932.14 | 男 | 2 | 4 |

| 8 | Mr lili | 45 | 9132.99 | 女 | 1 | 5 |

+----+-----------+------+---------+--------+-------+------+