【基础】视图 た 入场券 2022-07-16 23:53 160阅读 0赞 # 前言 # Github:[https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql][https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql] MySql在5.0.1版本开始提供视图。以下为对视图的分析: # 概述 # 视图是一种虚拟存在的表,对于使用视图的用户来说,视图基本是透明的。 你可以看到视图建立的逻辑。视图并不在数据库中真实存在,视图的行,列定义都来自于视图创建时查询实际表中的字段, 对于视图中的数据,每次使用时都是动态去生成的。 # **一 实例分析准备条件** # 创建表: CREATE TABLE `t_user_main` ( `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键', `f_userName` varchar(5) DEFAULT NULL COMMENT '用户名', `f_age` int(3) DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`f_userId`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 插入数据: INSERT INTO t_user_main (f_userName, f_age) VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29); # 二 创建视图 # ## 1、创建视图的语法 ## CREATE \[OR REPLACE\] \[ALGORITHM=\{UNDEFINED | MERGE | TEMPTABLE\}\] VIEW view\_name \[(column\_list)\] AS select\_statement \[WITH\[CASCADED | LOCAL\] CHECK OPTION\] 注意:mysql中创建视图时as的select语句中from后不能包含子查询, 如果做数据库迁移,在迁移前可以将数据库中视图子查询部分修改为视图替代。 #创建视图 CREATE OR REPLACE VIEW user_main_view AS SELECT * FROM t_user_main WHERE f_userId <> 6; ## 2、查询结果 ## ![Center][] # 三 视图更新 # 视图是否可以更新,取决于创建视图时的查询定义语句。查询定义语句包含以下情况,视图是不能进行更新的。 1) 包含如下关键字的查询SQL建立的视图: 聚合函数(sum,mix,min,count,avg等等),去重(distinct),分组(group by),having,union,union all 2)常量视图 3)SELECT中包含子查询 4)join(包含有连接行为) 5)查询语句FROM一个不能更新的视图,嵌套了一个不能更新的视图 6)WHERE字句的子查询引用from字句中的表 新建一个包含group by 的视图: #创建视图 CREATE OR REPLACE VIEW user_main_view AS SELECT * FROM t_user_main WHERE f_userId <> 6 GROUP BY f_userId; 测试更新报错如下: ![Center 1][] 错误:The target table user\_main\_view of the UPDATE is not updatable,错误直接告诉我们, 我们所更新的目标对象是不能更新的。 WITH\[CASCADED | LOCAL\] CHECK OPTION 决定视图更新的条件限制,满足相应的条件就可以更新,否则不能更新。 LOCAL: 只要满足本视图的条件即可以更新 CASCADED: 必须满足所有针对该视图的所有视图的条件才可以更新,默认为CASCADED。 #创建视图 CREATE OR REPLACE VIEW user_main_view AS SELECT * FROM t_user_main WHERE f_userId <> 6 WITH LOCAL CHECK OPTION; #查询视图 SELECT * FROM user_main_view; 执行更新语句前的t\_user\_main表数据: ![Center 2][] 执行更新语句: #满足f_userId不等于6就可以更新成功,更新之后f_userName变为o1 UPDATE user_main_view SET f_userName = 'o1' WHERE f_userId = 1; #当f_userId为6的时候,更新失败,数据库f_userName没有变成s6,说明没有更改 UPDATE user_main_view SET f_userName = 's6' WHERE f_userId = 6; 执行更新语句后的t\_user\_main表数据: ![Center 3][] 从更新语句和数据的变化可以体会到创建视图时with local check option的实际含义, 满足本视图的条件,就能更新,否则,不能更新。对于级联的情况也就是多个视图的限制 集中到一个视图,一个视图要想更新,除满足本身限制的同时,也得满足别人的显示,因为是一体的。 对于级联视图更新实例,创建两个视图,更新最后的视图,想当与两个视图条件限制: #创建视图1 CREATE OR REPLACE VIEW user_main_view AS SELECT * FROM t_user_main WHERE f_userId <> 6 WITH LOCAL CHECK OPTION; #创建视图2 CREATE OR REPLACE VIEW user_main_view2 AS SELECT * FROM user_main_view WHERE f_userId <> 3 WITH CASCADED CHECK OPTION; #查询视图 SELECT * FROM user_main_view2; #满足f_userId不等于6就可以更新成功,更新之后f_userName变为o1 UPDATE user_main_view2 SET f_userName = 'o2' WHERE f_userId = 1; #当f_userId为6的时候,更新失败,数据库f_userName没有变成s6,说明没有更改 UPDATE user_main_view2 SET f_userName = 's6' WHERE f_userId = 6; UPDATE user_main_view2 SET f_userName = 't3' WHERE f_userId = 3; id为1,更新成功,id为3,6没有更新。可以看出视图user\_main\_view2中更新时用了 视图1和视图2的限制,更新条件限制等于滚雪球的思想。 更新总结: 对于视图更新限制,能否更新,其实从视图查询出来就能更新,查询不出来就无法更新。 # 四 修改视图的语法 # ALTER \[ALGORITHM=\{UNDEFINED | MERGE | TEMPTABLE\}\] VIEW view\_name \[(column\_list)\] AS select\_statement \[WITH\[CASCADED | LOCAL\] CHECK OPTION\] eg: #更改视图,将条件编程5 ALTER VIEW user_main_view AS SELECT * FROM t_user_main WHERE f_userId <> 5 WITH LOCAL CHECK OPTION; ![Center 4][] # 五 查看视图 # 在mysql 5.1以后,通过show tables不仅能查看表,也能查看视图,已经没有单独的show views命令。 ![Center 5][] 显示更全的信息: SHOW TABLE STATUS \[FROM db\_name\]\[LIKE 'pattern'\]; ![Center 6][] # 六 删除视图 # 删除语法,删除视图的语法,可以删除一个或多个视图: DROP VIEW \[IF EXISTS\] view\_name\[,view\_name,....view\_name\]...\[RESTRICT | CASCADED\] ![Center 7][] 注意,使用时一定要看是否拥有创建,更新,修改,删除视图的权限。 [https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]: https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql [Center]: /images/20220717/53b78e01e55246d0a8c87953d6ef4182.png [Center 1]: /images/20220717/81b4ff04154040deb50633e90630e158.png [Center 2]: /images/20220717/93a051bbe9a54588b11dd929a4ce4ffe.png [Center 3]: /images/20220717/9f96acf098e54512b2cdfc33b1601c9a.png [Center 4]: /images/20220717/8bb66027b6a04833b994c94e996e5fcb.png [Center 5]: /images/20220717/ea9baf7e470a4427b4b687b7d0ca319b.png [Center 6]: /images/20220717/fa47fe4f7da34ce5997d52f3b0d98ddc.png [Center 7]: /images/20220717/c80eac37847b46ed93246e3417a15f59.png
还没有评论,来说两句吧...