Mysql视图初步理解
一、基本概念
《高性能mysql》中定义:mysql5.0版本之后开始引入视图。视图本身是一个虚拟表,不存放任何数据。在使用SQL语句访问视图的时候,它返回的数据是mysql从其他表中生成。视图和表是在同一个命名空间,mysql在很多地方对于视图和表是同样对待的。
不过视图和表也有不同,例如,不能对视图创建触发器,也不能使用drop table命令删除视图。
二、视图的优缺点
优点:
- 简单:使用视图的用户完全不需要关心所对应的表的结构、关系等,直接使用即可。
- 安全:可限制某些用户看到的一些关键信息和字段(登录密码、工资金额等)。
- 数据独立:基表更改了结构后,不会对视图造成影响。
缺点:
- 查询性能差:查询性较慢,无法使用索引。
- 修改复杂:基表改变与其它表关联的时候,也要修改视图结构。
三、视图的三种算法
-
UNDEFINED
:默认算法。UNDEFINED算法使MySQL可以选择使用MERGE或TEMPTABLE算法。MySQL优先使用MERGE算法进行TEMPTABLE算法,因为MERGE算法效率更高。 -
MERGE
:MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。 然后MySQL执行组合查询返回结果集。 如果SELECT语句包含集合函数(如MIN,MAX,SUM,COUNT,AVG等)或DISTINCT,GROUP BY,HAVING,LIMIT,UNION,UNION ALL,子查询,则不允许使用MERGE算法。 如果SELECT语句无引用表,则也不允许使用MERGE算法。 如果不允许MERGE算法,MySQL将算法更改为UNDEFINED。请注意,将视图定义中的输入查询和查询组合成一个查询称为视图分辨率。 -
TEMPTABLE
:MySQL首先根据定义视图的SELECT语句创建一个临时表,然后针对该临时表执行输入查询。因为MySQL必须创建临时表来存储结果集并将数据从基表移动到临时表,所以TEMPTABLE算法的效率比MERGE算法效率低。 另外,使用TEMPTABLE算法的视图是不可更新的。
四、视图的基本操作
先查看所有的表
:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| news |
| player |
| student |
+----------------+
3 rows in set (0.00 sec)
下面以player
表进行测试,先看下player
表中的数据:
mysql> select * from player;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 易建联 | 33 |
| 2 | 刘翔 | 34 |
| 3 | 姚明 | 32 |
+----+--------+-----+
3 rows in set (0.00 sec)
4.1 创建视图
创建一个名为player_view
视图:
mysql> create view player_view as select name from player;
Query OK, 0 rows affected (0.00 sec)
再次查看所有的表
:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| news |
| player |
| player_view |
| student |
+----------------+
4 rows in set (0.00 sec)
查询一下player_view
视图中的数据:
mysql> select * from player_view;
+--------+
| name |
+--------+
| 易建联 |
| 刘翔 |
| 姚明 |
+--------+
3 rows in set (0.00 sec)
更新一下基表(player)
中的某一条数据:
mysql> update player set name="丁彦雨航" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
再次查询player_view
视图中的数据是否有变化:
mysql> select * from player_view;
+----------+
| name |
+----------+
| 丁彦雨航 |
| 刘翔 |
| 姚明 |
+----------+
3 rows in set (0.00 sec)
修改一下视图中的数据:
mysql> update player_view set name="勒布朗" where name="丁彦雨航";
Query OK, 1 row affected (0.00 sec)
再看下基表中的变化:
mysql> select * from player;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 勒布朗 | 33 |
| 2 | 刘翔 | 34 |
| 3 | 姚明 | 32 |
+----+--------+-----+
3 rows in set (0.00 sec)
4.2 查看视图
查看所有视图
:
mysql> show table status where comment='view';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| player_view | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)
查看视图结构
:
mysql> DESCRIBE player_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
查看视图创建情况:
mysql> show create view player_view;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| player_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `player_view` AS select `player`.`id` AS `id`,`player`.`name` AS `name` from `player` | gb2312 | gb2312_chinese_ci |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
4.3 修改视图
先看下视图中的数据:
mysql> select * from player_view;
+--------+
| name |
+--------+
| 勒布朗 |
| 刘翔 |
| 姚明 |
+--------+
3 rows in set (0.00 sec)
修改一下视图中的结构
:
mysql> alter view player_view as select id,name from player;
Query OK, 0 rows affected (0.00 sec)
在查看一下视图中的数据:
mysql> select * from player_view;
+----+--------+
| id | name |
+----+--------+
| 1 | 勒布朗 |
| 2 | 刘翔 |
| 3 | 姚明 |
+----+--------+
3 rows in set (0.00 sec)
4.4 删除视图
mysql> drop view player_view;
Query OK, 0 rows affected (0.01 sec)
五、使用视图注意事项(列出一部分)
- select语句不能包含from语句中的子查询。
- select语句不能引用系统或用户变量。
- select语句不能引用预处理语句参数。
- 不能给视图添加索引。
- 不能将出发程序与视图关联在一起。
- 不得有重复的视图列名称。
- algorithm=temptable会成为不可更新的。
六、总结
最近做的是医院的项目,我需要给医院那边使用视图提供一些数据,例如,患者姓名、卡号、排队实况等信息。
给他们设置权限,只能查询,不能删除添加和修改。
视图在实际应用中很方便,但是在某种程度上也能进行优化。
本篇为基础讲解,仅此记录。
参考文章:《高性能mysql》、《PHP核心技术与最佳实践》、创建mysql视图。
本文地址:https://blog.csdn.net/qq_42249896/article/details/107604966