MySQL视图VIEW(含实例)

MySQL视图VIEW

  为什么要使用视图？

  因为视图的诸多优点，如下

  1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

  2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

  3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

  总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

1. 创建视图

1.1 语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

  1）OR REPLACE：表示替换已有视图

  2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表

  3）select_statement：表示select语句

  4）[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内

  cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件

  local表示更新视图的时候，要满足该视图定义的一个条件即可

  WITH CHECK OPTION 的作用是什么???

  5）软考书上的解释：WITH CHECK OPTION 表示对UPDATE、INSERT和DELETE操作时保持更新、插入或删除的行满足视图定义的谓词条件（即子查询中的条件表达式(where…)）

  TIPS：推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性

1.2 例子

  比如我创建如下视图:

create view study_view as select * from study_test where id > 3 with check option;

  当我插入如下id > 3 的语句是没有问题的:

insert into study_view(id,name) value(10,'meizu'); 

  当我插入如下id < 3 的语句是会报错:

insert into study_view(id,name) value(2,'cuizi'); 

  表结构和数据：

DROP TABLE IF EXISTS `study_test`;
CREATE TABLE `study_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `study_test` VALUES ('1', 'hwei');
INSERT INTO `study_test` VALUES ('2', 'xmi');
INSERT INTO `study_test` VALUES ('3', 'oppo');
INSERT INTO `study_test` VALUES ('4', 'vivo');
INSERT INTO `study_test` VALUES ('5', 'apple');
INSERT INTO `study_test` VALUES ('10', 'meizu');

2. 查看视图

  视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询

select * from study_view

3. 视图的更改

3.1 ALTER语句修改视图

  语法：

ALTER VIEW view_name AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

  例子：

alter view study_view as select * from study_test where id > 5 

3.2 CREATE OR REPLACE VIEW语句修改视图

  例子：

create or replace  view study_view as select * from study_test where id > 1

3.3 增删改查操作更新视图

  视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义。

  某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。

  还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

  ①select子句中包含distinct

  ②select子句中包含聚合函数 SUM()、MIN()、MAX()、COUNT() 等

  ③select语句中包含group by子句

  ④select语句中包含order by子句

  ⑤select语句中包含union 、union all等集合运算符

  ⑥where子句中包含相关子查询

  ⑦from子句中包含多个表

  ⑧如果视图中有计算列，则不能更新

  ⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

  ⑩ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

  插入语句例子：

insert into study_view VALUES(13,'sanxing')

  更新语句例子：

update study_view set name = 'zuizi' where id = 13

  查询语句例子：

select * from study_view 

  删除语句例子：

delete from study_view where id = 12

4. 删除视图

4.1 语法

DROP VIEW [IF EXISTS]   
view_name [, view_name] ..

4.2 例子

drop view study_view