Mysql进阶三板斧（一）带你彻底搞懂View视图的原理及应用

2020-07-06 10:23:05 浏览数 (1)

视图的起源

  • MySQL5.0.1（开天辟地一版本）中增加的三大新功能之一，另外两个师兄弟是存储过程与触发器。均属于相对“高级”一点的数据库必需功能。
  • 视图是一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果
  • MySQL在定义视图上没什么限制，基本上所有的查询都可定义为视图，同时也支持可更新视图（当然只有在视图和行列与基础表的行列之间存在一一对应关系时才能更新），因此从功能上说MySQL的视图功能已经很完善了。

一、视图概述

  • 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
  • 对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
  • 视图是存储在数据库中的查询的SQL 语句，视图有两个特点：
    • 1. 安全。视图可以隐藏一些数据。如：个税表，可以用视图只显示姓名，地址，而不显示个税号和工资数等，
    • 2. 可使复杂的查询易于理解和使用。这个视图就像一个窗口，从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT *，而你看到的将是你在视图定义里给出的那些数据列。

二、视图的意义

既然视图的定义是基于基本表的，哪为什么还要定义视图呢？这是因为合理地使用视图能够带来许多好处：

1、 视图能简化用户操作 视图机制使用户可以将注意力集中在所关心地数据上。如果这些数据不是直接来自基本表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的的数据查询操作。例如，那些定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐藏起来了。换句话说，用户所作的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无需了解。

2、 视图使用户能以多种角度看待同一数据 视图机制能使不同的用户以不同的方式看待同一数据，当许多不同种类的用户共享同一个数据库时，这种灵活性是非常必要的。 例如，Student表涉及全校15个院系学生数据，可以在其上定义15个视图，每个视图只包含一个院系的学生数据，并只允许每个院系的主任查询和修改本原系学生视图。

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

3、 视图对重构数据库提供了一定程度的逻辑独立性 数据的物理独立性是指用户的应用程序不依赖于数据库的物理结构。数据的逻辑独立性是指当数据库重构造时，如增加新的关系或对原有的关系增加新的字段，用户的应用程序不会受影响。层次数据库和网状数据库一般能较好地支持数据的物理独立性，而对于逻辑独立性则不能完全的支持。 在关系型数据库中，数据库的重构造往往是不可避免的。重构数据库最常见的是将一个基本表“垂直”地分成多个基本表。例如：将学生关系Student（ID，Sname，sex，age，class）， 分为SX（SID，Sname，age）和SY（SID，sex，class）两个关系。这时原表Student为SX表和SY表自然连接的结果。如果建立一个视图Student：

代码语言：javascript复制
CREATE VIEW Student（SID，Sname，sex，age，class）AS SELECT SX.ID，SX.Sname，SY.sex，
SX.age，SY.class FROM Student1 SX，Student2 SY WHERE SX.SID=SY.SID；

这样尽管数据库的逻辑结构改变了（变为SX和SY两个表了），但应用程序不必修改，因为新建立的视图定义为用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。 当然，视图只能在一定程度上提供数据的逻辑独立，比如由于视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因为基本表构造的改变而改变。 4、安全性 有了视图机制，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，使机密数据不出现在不应该看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。

6.灵活性

复杂的查询需求。可以进行问题分解，然后将创建多个视图获取数据。将视图联合起来就能得到需要的结果了。

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

视图这样设计有什么好处？节省空间，内容是总是一致的话，那么我们不需要维护视图的内容，维护好真实表的内容，就可以保证视图的完整性了。

三、实际应用

#数据准备

想我初三时常年倒数，成绩稳定，因此我拿当时排名数据来纪念一波。给大家提供一个测试数据；

1、学生表

代码语言：javascript复制
CREATE TABLE `student` (
`ID`  int NOT NULL AUTO_INCREMENT  ,
`SNAME`  varchar(30) NOT NULL ,
`SEX`  char(2) NOT NULL ,
`AGE`  int NOT NULL ,
`CLASS`  varchar(10) NOT NULL ,
`GRADE`  varchar(20) NOT NULL ,
`HOBBY`  varchar(100) NULL ,
PRIMARY KEY (`ID`)
)

#插入数据：

代码语言：javascript复制
INSERT INTO `student` (`ID`, `SNAME`, `SEX`, `AGE`, `CLASS`, `GRADE`, `HOBBY`) VALUES ('1', '陈哈哈', '男', '15', '18班', '9年级', '上网');
INSERT INTO `student` (`ID`, `SNAME`, `SEX`, `AGE`, `CLASS`, `GRADE`, `HOBBY`) VALUES ('2', '扈亚鹏', '男', '15', '18班', '9年级', '美食');
INSERT INTO `student` (`ID`, `SNAME`, `SEX`, `AGE`, `CLASS`, `GRADE`, `HOBBY`) VALUES ('3', '刘晓莉', '女', '14', '18班', '9年级', '金希澈');
INSERT INTO `student` (`ID`, `SNAME`, `SEX`, `AGE`, `CLASS`, `GRADE`, `HOBBY`) VALUES ('4', '朱志鹏', '男', '15', '18班', '9年级', '睡觉');
INSERT INTO `student` (`ID`, `SNAME`, `SEX`, `AGE`, `CLASS`, `GRADE`, `HOBBY`) VALUES ('5', '徐立楠', '女', '14', '18班', '9年级', '阅读');
INSERT INTO `student` (`ID`, `SNAME`, `SEX`, `AGE`, `CLASS`, `GRADE`, `HOBBY`) VALUES ('6', '顾昊', '男', '15', '5班', '9年级', '篮球');

#插入的结果：

2、再定义一张学生排名表：

代码语言：javascript复制
create TABLE student_score(
SID INT PRIMARY KEY,
TOTAL_SCORE INT NOT NULL,
RANK INT NOT NULL,
CONSTRAINT `FK_ID` FOREIGN KEY(SID) REFERENCES student(ID)
)

#插入如下：

代码语言：javascript复制
INSERT INTO `student_score` (`SID`, `TOTAL_SCORE`, `RANK`) VALUES ('1', '405', '1760');
INSERT INTO `student_score` (`SID`, `TOTAL_SCORE`, `RANK`) VALUES ('2', '497', '1000');
INSERT INTO `student_score` (`SID`, `TOTAL_SCORE`, `RANK`) VALUES ('3', '488', '1170');
INSERT INTO `student_score` (`SID`, `TOTAL_SCORE`, `RANK`) VALUES ('4', '405', '1770');
INSERT INTO `student_score` (`SID`, `TOTAL_SCORE`, `RANK`) VALUES ('5', '530', '701');
INSERT INTO `student_score` (`SID`, `TOTAL_SCORE`, `RANK`) VALUES ('6', '485', '1286');

#内容：

#使用案例

1. 语法

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

通过该语句可以创建视图，若给定了[OR REPLACE]，则表示当已具有同名的视图时，将覆盖原视图。select_statement是一个查询语句，这个查询语句可从表或其它的视图中查 询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。 表和数据库共享数据库中相同的名称空间，因此，数据库不能包含相同名称的表和视图，并且，视图的列名也不能重复。

#在创建视图前应先看看是否有权限：

代码语言：javascript复制
SELECT SELECT_priv,create_view_priv from mysql.user WHERE user='root'

# Y表示有创建的权限

2、单表上创建视图

#在员工表是创建视图

代码语言：javascript复制
CREATE VIEW S_VIEW1(ID, SNAME, SEX, AGE,CLASS,GRADE,HOBBY) AS SELECT ID, SNAME, SEX,
AGE,CLASS,GRADE,HOBBY FROM student;

#然后是显示内容：

代码语言：javascript复制
SELECT * FROM S_VIEW1

3、多表上创建视图

代码语言：javascript复制
CREATE VIEW V_VIEW2(ID, SNAME, SEX, AGE, CLASS, GRADE, HOBBY, TOTAL_SCORE, RANK) 
AS SELECT a.ID, a.SNAME, a.SEX, a.AGE,a.CLASS, a.GRADE, a.HOBBY, b.TOTAL_SCORE, b.RANK
FROM student a,student_score b WHERE a.ID=b.SID;

#然后是显示内容

代码语言：javascript复制
SELECT * FROM V_VIEW2

4、查看视图详情

（1）DESCRIBE ：查询视图结构

代码语言：javascript复制
DESCRIBE V_VIEW2

（2）SHOW TABLE STATUS ： 查询视图状态

代码语言：javascript复制
show TABLE status LIKE 'V_VIEW2'

（3）SHOW CREATE view ：

代码语言：javascript复制
show CREATE view V_VIEW2

5、修改视图SQL

(1)CREATE OR REPLACE 命令

代码语言：javascript复制
CREATE OR REPLACE VIEW S_VIEW1(ID, SNAME, SEX) AS SELECT ID, SNAME, SEX  
FROM student;

(2) ALTER 命令

代码语言：javascript复制
ALTER VIEW S_VIEW1(ID, SNAME) AS SELECT ID, SNAME FROM student;

#查询该视图

代码语言：javascript复制
SELECT * FROM S_VIEW1

6、更新视图数据

在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）原表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。 更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

比如，我想把我的分数改高一些。更新前如下：

#COOL！！把我的总分改成1000分了！！更新后发现忘了改名字。。还是并列倒数第一 -_-''|（更新语句如下）：

代码语言：javascript复制
UPDATE V_VIEW2 SET TOTAL_SCORE=1000 WHERE SNAME='陈哈哈'

#对应的真实表上的数据也发生改变了

代码语言：javascript复制
SELECT * FROM student_score

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

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

代码语言：javascript复制
· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 位于选择列表中的子查询
· Join
· FROM子句中的不可更新视图
· WHERE子句中的子查询，引用FROM子句中的表。
· 仅引用文字值（在该情况下，没有要更新的基本表）。
· ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

#注意

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

CASCADED和LOCAL能不能决定视图是否能更新？ WITH[CASCADED|LOCAL] CHECK OPTION能不能决定视图是否能更新？这两个参数的基本定义如下：

代码语言：javascript复制
LOCAL：表示更新视图时只要满足该视图本身定义的条件即可。
CASCADED：表示更新视图时需要满足所有相关视图和表的条件。没有指明时，该参数为默认值。

#With check option的用法：with check option对于没有where条件的视图不起作用的

代码语言：javascript复制
CREATE VIEW S_VIEW3(ID, SNAME, SEX, AGE, CLASS, GRADE, HOBBY) AS SELECT 
ID, SNAME, SEX, AGE, CLASS, GRADE, HOBBY FROM student 
WHERE CLASS='18班' WITH LOCAL CHECK OPTION;

#表示只限定插入班级为18班的人。

#然后插入一条：

注：向视图中插入数据时无法触发基本表的自动递增属性，所以ID不能直接写null，否则报错（[Err] 1048 - Column 'ID' cannot be null）

代码语言：javascript复制
INSERT INTO S_VIEW3(ID, SNAME, SEX, AGE, CLASS, GRADE, HOBBY) 
VALUES('7','陈子凝','女',15,'18班','9年级','看电影');

#看下结果：

代码语言：javascript复制
SELECT * FROM S_VIEW3

#同时看真实表中的数据，已经成功插入：

#再来插入一条其他班级的数据：

代码语言：javascript复制
INSERT INTO S_VIEW3(ID, SNAME, SEX, AGE, CLASS, GRADE, HOBBY) 
VALUES('7','吴迪','男',15,'20班','9年级','吹牛逼');

结果显示插入失败 对于with check option用法，总结如下： 通过有with check option选项的视图操作基表(只是面对单表，对连接多表的视图正在寻找答案)，有以下结论： 插入后的数据，通过视图能够查询出来就符合WITH CHECK OPTION 否则就不符合； 首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。 1.对于update,有with check option，要保证update后，数据要被视图查询出来 2.对于delete,有无with check option都一样 4.对于insert,有with check option，要保证insert后，数据要被视图查询出来 对于没有where 子句的视图，使用with check option是多余的

7、删除视图

代码语言：javascript复制
DROP VIEW IF EXISTS '视图名'
数据分析 数据库 sql 云数据库SQLServer 云推荐引擎

0 人点赞