mysql有关视图的详细解释

什么是视图 (View)

– 虚拟表
– 内容与真实的表相似,包含一系列带有名称的列和行数据。
– 视图并不在数据库中以存储的数据的形式存在。
– 行和列的数据来自定义视图时查询所引用的基表,并且在具体引用视图时动态生成。
– 更新视图的数据,就是更新基表的数据
– 更新基表数据,视图的数据也会跟着改变

视图优点

• 简单
– 使用视图的用户完全不需要关心视图中的数据是通过什么查询得到的。
– 视图中的数据对用户来说已经是过滤好的符合条件的结果集。
• 安全
– 用户只能看到视图中的数据。
• 数据独立
– 一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响。

 

使用视图的限制

• 不能在视图上创建索引
• 在视图的 FROM 子句中不能使用子查询
• 以下情形中的视图是不可更新的
– 包含以下关键字的 SQL 语句:聚合函数 (SUM 、 MIN、 MAX 、 COUNT 等 ) 、 DISTINCT 、 GROUP BY、 HAVING UNIO或 UNION ALL
– 常量视图
– JOIN
– FROM 一个不能更新的视图
– WHERE 子句的子查询引用了 FROM 子句中的表
– 使用了临时表,视图是不可更新

 

视图的基本使用语法

– create view 视图名称 as SQL 查询;
– create view 视图名称 (字段名列表) as SQL 查询;
mysql> create view t11 as select * from t1;
Query OK, 0 rows affected (0.05 sec)

在视图表中不定义字段名的话，默认使用表中的字段名，若定义字段名的话，视图表中的字段名
个数必须和基本中的字段个数相等。

 

查看视图

 

查看当前库下所有表的状态信息
– show table status;
– show table status where comment="view"\G;
mysql> show table status where comment=“view”\G;
*************************** 1. row ***************************
Name: t11
Engine: NULL
Auto_increment: NULL
...
...
Create_options: NULL
Comment: VIEW
// 视图表

 

查看创建视图的具体命令

– show create view 视图名;
mysql> show create view t11\G;
*************************** 1. row ***************************
View: t11
Create View: CREATE ALGORITHM=UNDEFINED
DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t11` AS
select `t1`.`name` AS `name` from `t1`
character_set_client: utf8
collation_connection: utf8_general_ci

 

使用视图

• 查询记录
– Select 字段名列表
from 视图名 where 条件;
• 插入记录
– Insert into 视图名 ( 字段名列表 ) values( 字段值列表);
• 更新记录
– Update 视图名 set 字段名 = 值 where 条件;
• 删除记录
– Delete from 视图名 where 条件;
• 删除视图
– drop view 视图名;
mysql> drop view t11;
Query OK, 0 rows affected (0.00 sec)
注意:对视图操作即是对基本操作,反之亦然!!!

 

设置字段别名

 

• 命令格式
– 视图中的字段名不可以重复 所以要定义别名
Create view 视图名
as
select 表别名 . 源字段名 as 字段别名
from 源表名 表别名 left join 源表名 表别名
on 条件;
关联查询建的视图 默认不允许修改视图字段的值
mysql> create view v2
as
select a.name as aname , b.name as bname , a.uid as auid , b.uid as buid
from user a left join info b on a.uid=b.uid;

ALGORITHM 
• 定义处理视图的方式
– ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}
• MERAGE (替换方式)
– 视图名直接使用视图的公式替换掉,把视图公式合并到了 select 中。
• TEMPTABLE (具体化方式)
– 先得到视图的执行结果,该结果形成一个中间结果暂时存在内存中,之后,外面的 select 语句就调用了这些中间结果。
• UNDEFINED (未定义)
– ALGORITHM 选项的值是 UNDEFINED 表示使用的是 MERAGE 替换方式。

OR REPLACE  
• 语法格式
– 创建时,若视图已存在,会替换已有的视图
– Create or replace view 视图名 as select 查询
mysql> create view v2 as select * from t1;
Query OK, 0 rows affected (0.01 sec)
mysql> create view v2 as select * from t1;
ERROR 1050 (42S01): Table ‘v2’ already exists // 提示已存在

WITH CHECK OPTION

• 当视图是根据另一个视图定义时 , 对视图更新 / 删除 / 插入
– LOCAL 和 CASCADED 关键字决定了检查的范围。
– LOCAL 仅检查当前视图的限制。
– CASCADED 同时要满足基表的限制

当选择cascaded的时候，更改视图的时候需要满足基本视图的限制，比如视图1是根据基表user创建的，视图2是根据视图1创建的，当在视图2上进行数据修改的时候，要符合视图1和视图2的条件。
mysql>create view v1
mysql>as
mysql>select * from user2 where uid <=30
mysql>select * from v1;
mysql>create table user2 select  name,uid,shell from user;
mysql>create view v1 as select * from user2 where uid <=30;
mysql>create view v2 as select * from v1 where uid >=20 with check option;
mysql>update v2 set uid=19  where name="mysql";
mysql>select * from v1;
mysql>select * from v2;
mysql>select uid from user2 where name="mysql";

 

当是同一是根据另一个视图定义时，对视图更新/删除/插入
local和cascaded关键决定检查的范围。
local只检查视图的限制
cascaded 同时要满足基本的表的限制。
WITH LOCAL CHECK OPTION 

当为视图设置local限制的时候，在更改视图的时候只是检查视图的受制条件，当满足的时候则可以更改，如果更改的时候
新数据不满足限制的时候，数据才会在视图中消失，但是在基本上可以查询到相关的信息。视图3是根据视图1创建的，
当修改视图3的时候，只需要满足视图3的条件，更改后的数据将会根据条件出现在相应的视图中。

mysql> create view v3
    -> as
    -> select * from v1 where uid>=20
    -> with local check option;
mysql> show create view v3;
WITH LOCAL CHECK OPTION
 mysql> select * from v3;
mysql> update v3 set uid=10 where name="mysql";
ERROR 1369 (HY000): CHECK OPTION failed 'zhuhaiyan.v3'
mysql> update v3 set uid=35 where name="mysql";
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from v3 where name="mysql";
mysql> select * from v3;
mysql> select * from v1 where name="mysql";
Empty set (0.00 sec)
mysql> select * from user2 where name="mysql";