mysql 视图应用与特点

mysql视图在特定业务需求下也会被应用到，只要简化操作和定制数据之类的。

视图的概述

视图被看成是虚拟表，并不表示任何物理数据，只是用来查看数据的视窗而已。

视图是由一组命名的列和数据行组成，内容由查询语句来定义，与真正的数据表很相似。

视图不是以数据形式存在的数据库中，而是存储视图的定义（即select语句），由该语句的结果构成视图返回的虚表。

视图数据随数据库数据变化而变化。

视图数据是在视图被引用动态生成的。

视图可以集中、简化、定制用户的数据表显示，可以通过视图来访问数据，不必访问视图相关的数据库表。

视图的作用与应用：

将用户限定在数据库表的特定行上。如：用户只看自己的订单信息

将用户限定在特定列上。如：只允许查看内容列

将多个表中的列连接起来，形成一个虚表。如：报表的制作

聚合信息而非提供详细信息。如：列的和、均值、最大值和最小值等。

视图的特点

优点：

数据保密。如：不同用户定义不同权限视图

简化查询操作。如：为复杂的查询构建视图来简化查询

保证数据的逻辑独立性。如：查询只依赖视图的定义，数据变化时，基于视图的查询不用改变。

缺点：

当更新视图中的数据时，实际上是对数据库表的数据更新。

当从视图中插入或删除时某些视图不能更新数据，例如：

有union等集合炒作的视图。

有group by子句的视图。

有如avg、sum或者max等函数的视图。

使用distinct的视图

连接表的视图，其中有一些例外

创建视图的语法

创建mysql 视图CREATE VIEW语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

REPLACE：如果存在同名视图，覆盖原来视图。

ALGORITHM：选择视图的算法

UNDEFINED：让mysql自动选择算法

MERGE：将使用视的图语句与视图定义合并，使视图的定义部分取代语句的对应部分。

TEMPTABLE：视图的结构保存到临时表，然后使用临时表执行语句。

CASCADED：更新视图时需要满足所有相关视图和表的条件

LOCAL：更新视图时满足该视图本身定义的条件即可。

CHECK OPTION：更新视图时要保证在该视图的权限范围之内。

视图的列名不能重复，要么全部指定要么全部省略，聚合函数需要明确指定列名。

实例应用

如有一用户表，为了方便查询，建立一个管理员用户视图，一个普通用户视图。

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) unsigned DEFAULT '0',
  `type` tinyint(1) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有如下数据：

mysql> select * from user;
+----+----------+------+------+
| id | username | sex  | type |
+----+----------+------+------+
|  1 | user1    |    1 |    1 |
|  2 | user2    |    0 |    1 |
|  3 | user3    |    1 |    2 |
|  4 | user4    |    0 |    2 |
+----+----------+------+------+
4 rows in set (0.00 sec)

建立一个管理员用户视图，一个普通用户视图

-- 管理员
CREATE VIEW tv_adminuser AS SELECT * FROM `user` WHERE `type`=1;

-- 普通用户
CREATE VIEW tv_commuser AS SELECT * FROM `user` WHERE `type`=2;

查询管理员用户

mysql> SELECT * FROM tv_adminuser;
+----+----------+------+------+
| id | username | sex  | type |
+----+----------+------+------+
|  1 | user1    |    1 |    1 |
|  2 | user2    |    0 |    1 |
+----+----------+------+------+
2 rows in set (0.00 sec)

接下来向在tv_adminuser插入一条普通用户的记录，结果可以插入成功。

mysql> INSERT INTO tv_adminuser(`username`,`sex`,`type`) VALUES('user5',0,2);
Query OK, 1 row affected (0.02 sec)

再接下来使用CHECK OPTION选项来创建视图

CREATE VIEW tv_adminuser AS SELECT * FROM `user` WHERE `type`=1 WITH CHECK OPTION;

再向tv_adminuser插入一条普通用户的记录，结果失败

mysql> INSERT INTO tv_adminuser(`username`,`sex`,`type`) VALUES('user6',0,2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.tv_adminuser'

后再向tv_adminuser插入一条管理员的记录，结果成功

mysql> INSERT INTO tv_adminuser(`username`,`sex`,`type`) VALUES('user6',0,1);
Query OK, 1 row affected (0.05 sec)

运行结果证明：CHECK OPTION 视图时要保证在该视图的权限范围之内。

视图管理

查询视图

查询视图的基本情况

SHOW TABLE STATUS LIKE 'tv_adminuser';
SHOW TABLE STATUS LIKE 'tv_%';

查询已建立的视图情况

SELECT * FROM information_schema.tables WHERE table_type='view';

查询一个视图的定义

SHOW CREATE VIEW tv_commuser;

查询视图结构定义

DESC tv_adminuser;

修改视图

该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图tv_adminuser管理员视图

ALTER VIEW tv_adminuser AS SELECT * FROM `user` WHERE `type`=1;

修改视图名称tv_adminuser为tv_admin

RENAME TABLE tv_adminuser TO tv_admin;

删除视图

DROP VIEW能够删除1个或多个视图。必须在每个视图上拥有DROP权限。

可以使用关键字IF EXISTS来防止因不存在的视图而出错。

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

删除视图

删除视图tv_admin

DROP VIEW tv_admin;

原创文章，转载请注明出处：https://www.weizhixi.com/article/76.html