Mysql_视图

视图 一、视图 　　1、什么是视图？（计算机数据库术语）

　　　　　　视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。简单来说就是视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

　　2、视图的作用？

　　　　　　通过视图，可以展现基表的部分数据； 视图数据来自定义视图的查询中使用的表，使用视图动态生成。 基表：用来创建视图的表叫做基表

　　3、为什么要使用视图？

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

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

　　　　　　数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。 总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

　　4、MySQL中的视图操作

　　　　　　因为视图是需要基表才能构建，因此在讲解视图的时候，我们需要先创建两张数据表用于后面演示视图操作，下面是测试表和测试数据创建的SQL语句。

 1 CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARSET  utf8;
 2 USE demo;
 3 # 创建作者表
 4 CREATE TABLE IF NOT EXISTS author(
 5     id INT NOT NULL AUTO_INCREMENT,
 6     author_name VARCHAR(50) DEFAULT NULL,
 7     PRIMARY KEY(id)
 8 );
 9 INSERT INTO author(author_name)VALUES('张三'),('李四'),('王五'),('赵柳');
10 # 创建博客表;
11 CREATE TABLE IF NOT EXISTS blog(
12     id INT PRIMARY KEY AUTO_INCREMENT,
13     title VARCHAR(50) DEFAULT NULL,
14     content VARCHAR(100) DEFAULT NULL,
15     author_id INT DEFAULT NULL
16 );
17 INSERT INTO blog(
18 title,content,author_id)VALUES
19 ('测试博客01' , '博客内容01' , 1),
20 ('测试博客02','博客内容02',2),
21 ('测试博客03','博客内容03',3),
22 ('测试博客04 ','博客内容04',4);

　　　　　4.1、创建视图

　　　　　　　　书写格式：

　　　　　　　　CREATE VIEW <视图名>[(列名组)]AS <子查询>     #创建视图

　　　　　　　　DROP VIEW <视图名>    #删除视图

　　　　　　　　注意：视图可以和基本表一样被查询，但是利用视图进行数据增，删，改操作，会受到一定的限制。 　　　　　　　（1）由两个以上的基本表导出的视图（2）视图的字段来自字段表达式函数（3）视图定义中有嵌套查询（4）在一个不允许更新的视图上定义的视图

　　　　

 1 # 创建视图：逻辑上的虚拟表
 2 CREATE VIEW v_author(编号,姓名)  
 3 AS
 4 SELECT * FROM author 
 5 WITH CHECK OPTION;   # 这个在博客后面讲
 6 
 7 # 查询视图  必须保证基表内有数据，才能查询到数据
 8 SELECT * FROM v_author;
 9 
10 # 创建多表查询
11 CREATE VIEW v_a_b(姓名,标题,内容)
12 AS
13 SELECT a.author_name,b.title,b.content FROM author a LEFT JOIN blog b ON b.author_id= a.id;
14 
15 #查询多表视图  必须保证查询到的基表都有数据
16 SELECT * FROM v_a_b;

　　　　　　　　

　　　　4.2、视图的更改

　　　　　　create or replace view view_name as select语句; 在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图

　　　　　　ps.在MySQL中视图不能重复创建，不能重名。

 1 CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET=utf8;
 2 USE info;
 3 #创建作者表
 4 CREATE TABLE IF NOT EXISTS author(
 5 id INT NOT NULL AUTO_INCREMENT,
 6 author_name VARCHAR(50) DEFAULT NULL,
 7 PRIMARY KEY(id)
 8 );
 9 INSERT INTO author(author_name)VALUES('naamman'),
10 ('lucy'),('lily'),('jack');
11 #创建博客表
12 CREATE TABLE IF NOT EXISTS blog(
13 id INT PRIMARY KEY AUTO_INCREMENT,
14 title VARCHAR(50) DEFAULT NULL,
15 content VARCHAR(100) DEFAULT NULL,
16 author_id INT DEFAULT NULL
17 );
18 INSERT INTO blog(title,content,author_id)VALUES
19 ('测试博客01','博客内容01',1),
20 ('测试博客02','博客内容02',2),
21 ('测试博客03','博客内容03',3),
22 ('测试博客04','博客内容04',4);
23 #创建视图:逻辑上的虚拟表   
24 CREATE  VIEW v_author(编号,姓名)
25 AS
26 SELECT * FROM author ORDER BY id ASC WITH CHECK OPTION;
27 DROP VIEW v_author
28 #查询视图
29 SELECT * FROM v_author;
30 #创建多表视图(自己写)
31 CREATE VIEW v_a_b(作者名字,博客标题,博客内容)
32 AS
33 SELECT a.author_name,b.title,b.content FROM author a LEFT JOIN blog b ON a.id=b.author_id; 
34 SELECT * FROM v_a_b;
35 
36 ###############################
37 
38 #修改视图:没有就创建,有就替换
39 CREATE OR REPLACE VIEW v_blog(编号,标题,内容,作者编号) 
40 AS SELECT * FROM blog
41 WITH CHECK OPTION;

　　　　4.3、DML操作更新视图

　　　　　　因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中，比如我们执行以下操作

 1 UPDATE v_blog SET 内容 = '修改后的内容' WHERE 编号 = 1; 

　　　4.4、使用WITH CHECK OPTION约束

　　　　　　对于可以执行DML操作的视图，定义时可以带上WITH CHECK OPTION约束 作用：对视图所做的DML操作的结果，不能违反视图的WHERE条件的限制。

1 #部分数据创建视图
2 CREATE OR REPLACE VIEW v_blog_1(编号,标题,内容,作者编号)
3 AS SELECT * FROM blog WHERE author_id=3
4 WITH CHECK OPTION;
5 #with check option:where条件约束
6 #该语句会报错违反了 WHERE author_id=3 的约束
7 INSERT INTO v_blog_1(编号,标题,内容,作者编号)VALUES(7,'123','123',4);

　　　　4.5、6.定义视图时的其他选项

 

 　　　　 ALGORITHM选项

　　　　 　　选择在处理定义视图的select语句中使用的方法

　　　　　　　　 – UNDEFINED：MySQL将自动选择所要使用的算法

　　　　　　　　– MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分

　　　　　　　　– TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句

　　　　　　　　缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED

　　　　DEFINER选项 指出谁是视图的创建者或定义者

　　　　　　　　 – definer= ‘用户名’@’登录主机’ – 如果不指定该选项，则创建视图的用户就是定义者，指定关键字CURRENT_USER(当前用户)和不指定该选项效果相同

　　　　SQL SECURITY选项 要查询一个视图，首先必须要具有对视图的select权限，如果同一个用户对于视图所访问的表没有select权限，那会怎么样？

　　　　SQL SECURITY选项决定执行的结果：

　　　　　　　　– SQL SECURITY DEFINER：定义(创建)视图的用户必须对视图所访问的表具有select权限，也就是说将来其他用户访问表的时候以定义者的身份，此时其他用户并没有访问权限。

　　　　　　　　– SQL SECURITY INVOKER：访问视图的用户必须对视图所访问的表具有select权限。

　　　　　　　　缺省SQL SECURITY选项等同于SQL SECURITY DEFINER

　　　　 视图权限总结： 使用root用户定义一个视图(推荐使用第一种)：u1、u2

　　　　　　　　1）u1作为定义者定义一个视图，u1对基表有select权限，u2对视图有访问权限：u2是以定义者的身份访问可以查询到基表的内容；

　　　　　　　　2）u1作为定义者定义一个视图，u1对基表没有select权限，u2对视图有访问权限，u2对基表有select权限：u2访问视图的时候是以调用者的身份，此时调用者是u2，可以查询到基表的内容。