MySQL高级操作之视图、自定义函数、存储过程、触发器、事务

视图

MySQL视图是一个虚拟表

语法

CREATE VIEW <视图名> AS <SELECT 语句>

语法说明

• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
• <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
  对于创建视图中的SELECT语句存在如下限制：
• 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
• SELECT 语句不能引用系统或用户变量。
• SELECT 语句不能包含 FROM 子句中的子查询。
• SELECT 语句不能引用预处理语句参数。

视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。 可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。
视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。
视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。 WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件
视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作；
视图与表是一对多关系情况：如果只修改一张表的数据，且没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行改数据操作。

视图和临时表的区别

• 视图只是一条预编译的 SQL 语句，并不保存实际数据；临时表是保存在 tempdb 中的实际的表
• 物理空间的分配不一样，视图不分配空间，临时表会分配空间
• 视图是一个快照是一个虚表；临时表是客观存在的表类型对象 Create TEMPORARY table
• 使用show tables;时，会出现视图，而临时表不存在；
• 退出重连mysql后，视图还存在，而临时表已经释放了

作用

1. 提高了重用性，就像一个函数。
2. 对数据库重构，却不影响程序的运行。
3. 提高了安全性能。可以对不同的用户，设定不同的视图。
4. 让数据更加清晰。想要什么样的数据，就创建什么样的视图。

实例

mysql> select * from person_tbl;
+----+----------+
| id | name     |
+----+----------+
|  1 | king     |
|  2 | clarence |
|  3 | tom      |
+----+----------+
3 rows in set (0.00 sec)

mysql> create view view_person as select * from person_tbl;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | king     |
|  2 | clarence |
|  3 | tom      |
+----+----------+
3 rows in set (0.00 sec)

mysql> insert into person_tbl(name) values('milo');
Query OK, 1 row affected (0.00 sec)

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | king     |
|  2 | clarence |
|  3 | tom      |
|  4 | milo     |
+----+----------+
4 rows in set (0.00 sec)

mysql> update person_tbl set name='admin' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | admin    |
|  2 | clarence |
|  3 | tom      |
|  4 | milo     |
+----+----------+
4 rows in set (0.00 sec)
mysql> exit;
#重新连接后
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| copy_clone       |
| copy_source      |
| learn            |
| person_tbl       |
| t2               |
| view_person      |
+------------------+

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | admin    |
|  2 | clarence |
|  3 | tom      |
|  4 | milo     |
+----+----------+

mysql> show create view view_person;
+-------------+---------------------------------------------------------------------------------------------------------------