Mysql视图初步理解
程序员文章站
2022-07-05 23:41:52
《高性能mysql》中定义：mysql5.0版本之后开始引入视图。视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是mysql从其他表中生成。视图和表是在同一个命名空间，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