频道栏目
首页 > 数据库 > MySQL > 正文
MySql(12)------MySql视图实例大全
2016-08-24 09:16:36           
收藏   我要投稿

MySql在5.0.1版本开始提供视图。以下为对视图的分析:

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);

2. 创建或修改视图

******创建视图的语法:

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH[CASCADED | LOCAL] CHECK OPTION]

注意:mysql中创建视图时as的select语句中from后不能包含子查询,

如果做数据库迁移,在迁移前可以将数据库中视图子查询部分修改为视图替代。

eg:

#创建视图

CREATE OR REPLACE VIEW user_main_view

AS

SELECT * FROM t_user_main WHERE f_userId <> 6;

查询结果:

 

\

 

******关于视图更新:

视图是否可以更新,取决于创建视图时的查询定义语句。

查询定义语句包含以下情况,视图是不能进行更新的。

(1) 包含如下关键字的查询SQL建立的视图:

聚合函数(sum,mix,min,count,avg等等),

去重(distinct),分组(group by),having,union,union all

(2)常量视图

(3)SELECT中包含子查询

(4)join

(5)查询语句FROM一个不能更新的视图,嵌套了一个不能更新的视图

(6)WHERE字句的子查询引用from字句中的表

eg:

新建一个包含group by 的视图:

#创建视图

CREATE OR REPLACE VIEW user_main_view

AS

SELECT * FROM t_user_main WHERE f_userId <> 6 GROUP BY f_userId;

测试更新报错如下:

 

\

 

错误: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表数据:

 

\

 

执行更新语句:

#满足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表数据:

 

\

 

从更新语句和数据的变化可以体会到创建视图时with local check option的实际含义,

满足本视图的条件,就能更新,否则,不能更新。对于级联的情况也就是多个视图的限制

集中到一个视图,一个视图要想更新,除满足本身限制的同时,也得满足别人的显示,因为是一体的。

eg:

对于级联视图更新实例,创建两个视图,更新最后的视图,想当与两个视图条件限制:

#创建视图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;

 

\

 

3. 查看视图

在mysql 5.1以后,通过show tables不仅能查看表,也能查看视图,已经没有单独的show views命令。

eg:

 

\

 

显示更全的信息:

SHOW TABLE STATUS [FROM db_name][LIKE 'pattern'];

eg:

 

\

 

4. 删除视图

删除语法:

删除视图的语法,可以删除一个或多个视图:

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

eg:

 

\

 

注意,使用时一定要看是否拥有创建,更新,修改,删除视图的权限。

点击复制链接 与好友分享!回本站首页
相关TAG标签 视图 实例 大全
上一篇:Mysql学习总结(32)——阿里云centos配置MySQL主从复制
下一篇:mysql索引总结----mysql索引类型以及创建
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站