mysql视图

摘要：视图与表有很多相似的地方，视图也是由若干个字段以及若干条记录构成，视图也可以作为select语句的数据源。视图中保存的仅仅是一条select语句，保存的是视图的定义，并没有保存真正的数据。视图中的源数据都来自于数据库表，数据库表称为基本表或者基表，视图称为虚表。
视图是MySQL 5.0中增加的三大新功能之一（另外两个是存储过程与触发器），也是一般稍微“高级”一点的数据库所必需要有的功能。MySQL在定义视图上没什么限制，基本上所有的查询都可定义为视图，并且也支持可更新视图和不可更新视图，因此从功能上说MySQL的视图功能已经很完善了。

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中存储数据。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。定义的视图可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
数据库中只存放了视图的定义(sql语句)，而没有存放视图中的数据，这些数据存放在原来的表中。，它主要出于两种原因：安全原因， 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

一、为什么要使用视图

1.安全性。一般是这样做的:创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定。这样的方式是使用到了一个特性：grant语句可以针对视图进行授予权限。
2.查询性能提高。

3.有灵活性的功能需求后，需要改动表的结构而导致工作量比较大。那么可以使用虚拟表的形式达到少修改的效果。这是在实际开发中比较有用的
4.复杂的查询需求。可以进行问题分解，然后将创建多个视图获取数据。将视图联合起来就能得到需要的结果了。

视图的工作机制:当调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。

视图这样设计有什么好处？节省空间，内容是总是一致的话，那么我们不需要维护视图的内容，维护好真实表的内容，就可以保证视图的完整性了。
二、数据准备
准备两张表，一张存储学生信息，一张存储学生成绩
表1 学生信息表：
CREATE TABLE student_info(
number INT PRIMARY KEY COMMENT ‘学号’,
NAME VARCHAR(20) COMMENT ‘姓名’,
sex ENUM(‘男’,‘女’),
id_number CHAR(18) COMMENT ‘身份证号’,
department VARCHAR(30) COMMENT ‘学院’,
major VARCHAR(30) COMMENT ‘专业’,
enrollment_time DATE COMMENT ‘入学时间’,
UNIQUE KEY(id_number)
);

INSERT INTO student_info VALUES(20180101,‘杜子腾’,‘男’,‘158177199010447921’,‘计算机学院’,‘计算机科学与工程’,‘2018-09-01’),
(20180102,‘杜琦燕’,‘女’,‘158177199011785921’,‘计算机学院’,‘计算机科学与工程’,‘2018-09-01’),
(20180103,‘范统’,‘男’,‘178177199011169590’,‘计算机学院’,‘软件工程’,‘2018-09-01’),
(20180104,‘史珍香’,‘女’,‘168477199111169602’,‘计算机学院’,‘软件工程’,‘2018-09-01’),
(20180105,‘范剑’,‘男’,‘189177199102169893’,‘航天学院’,‘飞行器设计’,‘2018-09-01’),
(20180106,‘朱意群’,‘男’,‘197777199012169785’,‘航天学院’,‘电子信息’,‘2018-09-01’);

表2 学生成绩表
CREATE TABLE student_score(
number INT COMMENT ‘学号’,
SUBJECT VARCHAR(30) COMMENT ‘学科’,
score TINYINT COMMENT ‘分数’,
PRIMARY KEY(number,SUBJECT),
CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

INSERT INTO student_score VALUES(20180101,‘母猪的产后护理’,78),
(20180101,‘资本论’,88),
(20180102,‘母猪的产后护理’,100),
(20180102,‘资本论’,98),
(20180103,‘母猪的产后护理’,59),
(20180103,‘资本论’,61),
(20180104,‘母猪的产后护理’,55),
(20180104,‘资本论’,46),
(20180105,‘母猪的产后护理’,85),
(20180105,‘资本论’,90),
(20180106,‘母猪的产后护理’,66),
(20180106,‘资本论’,75);

三、视图的操作（创建、删除、查询，数据的增删改查）
对视图的操作和真实表类似
1、创建视图
创建视图的语法格式如下。
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
参数含义：
其中，CREATE：表示新建视图；

OR REPLACE：表示替换已有视图(有重名的就替换)

ALGORITHM ：表示视图选择算法

view_name ：视图名

column_list：属性列

select_statement：表示select语句

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

可选的ALGORITHM子句是对标准SQL的MySQL扩展。

ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。

如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。

对于MERGE，会将引用视图的语句与视图定义合并起来，使得视图定义的某一部分**取代引用语句**的对应部分。

对于TEMPTABLE，视图的结果将被置于**临时表**中(暂时存在内存中)，然后使用它执行语句(外面的select语句就调

用了这些中间结果(临时表))。

对于UNDEFINED，MySQL自己选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，

这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

with check option：对视图进行更新操作的时，需要检查更新后的值是否还是满足视图公式定义的条件。通俗点，就是所更新的结果是否还会在视图中存在。如果更新后的值不在视图范围内，就不允许更新。如果创建视图的时候，没有加上with check option，更新视图中的某项数据的话，mysql并不会进行有效性检查。删掉了就删掉了。在视图中将看不到了。
LOCAL和CASCADED为可选参数，决定了检查测试的范围，默认值为CASCADED。
在创建时，可以指定WITH LOCAL CHECK OPTION或者WITH CASCADED CHECK OPTION，前者的意思是，只要满足本视图的条件就可以更新，而后者要求满足针对该视图的，所有视图的条件，才可以更新。默认是CASCADED
　　 举个例子，有一张表T，里面放着2个班学生分数，从T中创建一个视图V1，可以看到80分以上的同学（条件是>=80分）；再从V1中创建一个视图V2，可以看到V1中，1班的同学的分数（条件是班级）。即，V1有2个班80分以上的同学，而V2有1班80分以上的同学（这个隐含的80分的限制来自于V1）。

假设V1是LOCAL的，V2是CASCADED的。

那么，你不能把V1中某个同学的分数改到80分以下，这样就会脱离这个视图。对于V2，来说，你既不能改一个同学的班级，把他改为2班，也不能把他的成绩改为80分以下，因为它是级联的（CASCADED）

例：
mysql> CREATE VIEW male_student_info AS SELECT s1.number, s1.name, s1.major, s2.subject, s2.score FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.sex = ‘男’;
Query OK, 0 rows affected (0.02 sec)

mysql>
这样，这个名称为male_student_info的视图就代表了那一串又长又复杂的查询语句了。

2、查看视图结构（查看视图的信息，类似于查看表的信息）
desc viewName;
show create view viewName;
…
查看有哪些视图

我们想查看当前数据库中有哪些视图的话，其实和查看有哪些表的命令是一样的：

mysql> SHOW TABLES;
±--------------------+
| Tables_in_xiaohaizi |
±--------------------+
| by_view |
| first_table |
| male_student_info |
| second_table |
| student_info |
| student_info_view |
| student_score |
| t |
| t1 |
| t2 |
| t3 |
| zero_table |
±--------------------+
12 rows in set (0.00 sec)

mysql>
可以看到，我们创建的几个视图，包括by_view、male_student_info、student_info_view就都显示出来了。需要注意的是，因为视图是一张虚拟表，所以新创建的视图的名称不能和当前数据库中的其他视图或者表的名称冲突！

查看视图的定义

因为视图是一张虚拟表，所以用来查看表结构的语句都可以用来查看视图的结构，不过我们经常使用的查看视图定义语句是这个：

SHOW CREATE VIEW 视图名
我们来查看一下student_info_view视图的定义：

mysql> SHOW CREATE VIEW student_info_view \G
*************************** 1. row ***************************
View: student_info_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW student_info_view AS select student_info.number AS no,student_info.name AS n,student_info.major AS m from student_info
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>
3、删除视图
如果某个视图不再使用，可以使用drop view语句将该视图删除，语法格式如下。
drop view 视图名
然后再查看当前数据库中的表：
mysql> SHOW TABLES;
可发现数据库下的表列表中没有被删除的视图了

4、查询视图

视图也被称为虚拟表，我们可以对视图进行一些类似表的增删改查操作，只不过我们对视图的相关操作都会被映射到查询语句对应的底层的表上。，比方说male_student_info这个视图对应的查询语句中的查询列表是number、name、major、subject、score，它们也是male_student_info视图的虚拟列。

比如我们可以使用平常的查询语句从视图中查询我们需要的信息可以这么写：

这里我们的查询列表是*，这也就意味着male_student_info所代表的查询语句的结果集将作为整个查询的结果集返回。从这个例子中我们也可以看到，我们不再需要使用那句又臭又长的连接查询语句了，只需要从它对应的视图中查询即可。

除此之外，我们在真实表中使用的那些查询语句都可以被用到视图这个虚拟表中，比方说这个查询语句：

我们再次强调一遍，视图其实就是某个查询的别名，而不是某个查询的结果集，换句话说就是，创建视图的时候并不会把那个查询语句的结果集维护在硬盘或者内存里！在对视图进行查询时，undefined模式下，MySQL服务器将会帮助我们把对视图的查询语句转换为对底层表的查询语句然后再执行，所以上边这个查询其实会被转换成下边这个查询语句去执行：

SELECT subject, AVG(score) FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.sex = ‘男’ AND score > 60 GROUP BY subject HAVING AVG(score) > 75;
只不过这个转换的过程我们并不能看到，所以主观上认为硬盘或内存里真的维护了一个视图对应的表而已～ 更复杂的一些查询语句，比如子查询、连接查询什么的，都可以被用到视图上，我们这里就不举例子了。

有一点比较有趣的是，在查询时，视图可以和表一起使用，包括子查询和连接查询，比如这样：

所以在使用层面，我们完全可以把视图当作一个表去使用，但是它的实现原理却是在执行语句时转换为对底层表的操作。使用视图的好处也是显而易见的，我们可以复用某个查询语句，从而简化了查询操作，避免了每次查询时都要写一遍语句；对视图的操作更加直观，而不用考虑它底层的查询细节。

5、利用视图来创建新视图

我们前边说视图是某个查询语句的别名，其实这个查询语句不仅可以从普通的表中查询数据，也可以从另一个视图中查询数据，只要是个合法的查询语句就好了。比方说我们利用male_student_info视图来创建另一个新视图可以这么写：

mysql> CREATE VIEW by_view AS SELECT number, name, score FROM male_student_info;
Query OK, 0 rows affected (0.02 sec)

mysql>
我们查询一下这个从另一个视图中生成的视图：

这种利用其他的视图来生成的新视图也被称为嵌套视图，在对某个嵌套视图执行查询时，查询语句会先被转换成对它依赖的视图的查询，再转换成对底层表的查询。

创建视图时指定自定义列名

我们前边说过视图的虚拟列其实是这个视图对应的查询语句的查询列表，我们也可以在创建列表的时候为这些虚拟列自定义列名，这些自定义列名写到视图名后边，用逗号,分隔就好了，不过需要注意的是，自定义列名一定要和查询列表中的查询对象一一对应。比如我们新创建一个自定义列名的视图：

mysql> CREATE VIEW student_info_view(no, n, m) AS SELECT number, name, major FROM student_info;
Query OK, 0 rows affected (0.02 sec)

mysql>
我们的自定义列名列表是no, n, m，分别对应查询列表中的number, name, major。有了自定义列名之后，我们之后对视图的查询语句都要基于这些自定义列名，比如我们可以这么查询：

如果仍旧使用与视图对应的查询语句的查询列表中的列名就会报错，比如这样：

mysql> SELECT number, name, major FROM student_info_view;
ERROR 1054 (42S22): Unknown column ‘number’ in ‘field list’
mysql>
6、更新视图

我们前边进行的都是对视图的查询操作，其实也可以对视图进行更新，也就是在视图上执行INSERT、DELETE、UPDATE语句。对视图执行更新语句的本质上是对该视图对应的底层表进行更新。比方说视图student_info_view的底层表是student_info，所以如果我们对student_info_view执行更新语句就相当于对student_info表进行更新，比方说我们执行这个语句：

mysql> UPDATE student_info_view SET n = ‘111’ WHERE no = 20180101;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
我们再到student_info表中看一下这个学生的名称是否被改了：

mysql> SELECT name FROM student_info WHERE number = 20180101;
±-----+
| name |
±-----+
| 111 |
±-----+
1 row in set (0.00 sec)

mysql>
名称的确被更改成功了！

如下两种方式更新，都能把基表student_score中所以记录的score列修改为95
UPDATE student_score set score=95;

create view view_score as select * from student_score;
UPDATE view_score SET score=95;
四、不可更新的视图
以下类型的视图是不可更新的
1.包含以下关键字的sql语句：聚合函数(sum、min、max、count)、distinct、group by 、having、union或者uinon all
2.常量视图
3.select 中包含子查询
4.join(连接)
5.from一个不可更新的视图
6.where子句的子查询引用了from字句中的表
7. WITH[CASCADED | LOCAL] CHECK OPTION确定了更新视图的条件。
LOCAL代表只要满足本视图的条件就可以更新
CASCADED 则必须满足所有针对该视图的所有视图条件才可以更新