mysql 视图应用与特点

标签: 视图
2018-02-22 阅读(158)

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/user/index/article/id/76.html

分类:
点击(188) 阅读(158) 举报