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

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)

)

#插入如下：

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是一个查询语句，这个查询语句可从表或其它的视图中查 询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。

表和数据库共享数据库中相同的名称空间，因此，数据库不能包含相同名称的表和视图，并且，视图的列名也不能重复。

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

SELECT SELECT_priv,create_view_priv from mysql.user WHERE user=‘root’

# Y表示有创建的权限

2、单表上创建视图

#在员工表是创建视图

CREATE VIEW S_VIEW1(ID, SNAME, SEX, AGE,CLASS,GRADE,HOBBY) AS SELECT ID, SNAME, SEX,

AGE,CLASS,GRADE,HOBBY FROM student;

#然后是显示内容：

SELECT * FROM S_VIEW1

3、多表上创建视图

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;

#然后是显示内容

SELECT * FROM V_VIEW2

4、查看视图详情

（1）DESCRIBE ：查询视图结构

DESCRIBE V_VIEW2

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

show TABLE status LIKE ‘V_VIEW2’

（3）SHOW CREATE view ：

show CREATE view V_VIEW2

5、修改视图SQL

(1)CREATE OR REPLACE 命令

CREATE OR REPLACE VIEW S_VIEW1(ID, SNAME, SEX) AS SELECT ID, SNAME, SEX

FROM student;

(2) ALTER 命令

ALTER VIEW S_VIEW1(ID, SNAME) AS SELECT ID, SNAME FROM student;

#查询该视图

SELECT * FROM S_VIEW1

6、更新视图数据

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

更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

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

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

UPDATE V_VIEW2 SET TOTAL_SCORE=1000 WHERE SNAME=‘陈哈哈’

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

SELECT * FROM student_score

#不可更新的视图：

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

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

· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。

· DISTINCT

· GROUP BY

· HAVING

· UNION或UNION ALL

· 位于选择列表中的子查询

· Join

· FROM子句中的不可更新视图

· WHERE子句中的子查询，引用FROM子句中的表。

· 仅引用文字值（在该情况下，没有要更新的基本表）。

· ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

#注意

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

CASCADED和LOCAL能不能决定视图是否能更新？

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

LOCAL：表示更新视图时只要满足该视图本身定义的条件即可。

CASCADED：表示更新视图时需要满足所有相关视图和表的条件。没有指明时，该参数为默认值。

#With check option的用法：with c

《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享

heck option对于没有where条件的视图不起作用的

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）

INSERT INTO S_VIEW3(ID, SNAME, SEX, AGE, CLASS, GRADE, HOBBY)

VALUES(‘7’,‘陈子凝’,‘女’,15,‘18班’,‘9年级’,‘看电影’);

#看下结果：

SELECT * FROM S_VIEW3

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

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

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、删除视图

DROP VIEW IF EXISTS ‘视图名’