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_adminRENAME TABLE tv_adminuser TO tv_admin;
删除视图
DROP VIEW能够删除1个或多个视图。必须在每个视图上拥有DROP权限。
可以使用关键字IF EXISTS来防止因不存在的视图而出错。DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
删除视图
删除视图tv_adminDROP VIEW tv_admin;
原创文章,转载请注明出处:https://www.weizhixi.com/article/76.html