欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mysql视图初步理解

程序员文章站 2022-03-14 14:24:27
《高性能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

相关标签: Mysql