MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15)

Wesley13
• 阅读 486

知识点七:MySQL视图的创建(14)

视图的定义:

什么是视图:

视图数由查询结果形成的一张虚拟的表。

什么时候要用到视图?

如果某个查询结果出现的非常频繁,也就是,要经常拿这个查询结果来做子查询。

使用视图的好处:

1.简化查询语句:

案例:比如求每个人的平均工资。

MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15) MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15)

1 --简化查询语句
2 use mzjj;
3 CREATE OR REPLACE VIEW v_salaries
4 AS
5 SELECT employees.emp_no,first_name,last_name,IFNULL(AVG(salary),0) AS salary FROM employees LEFT JOIN salaries
6 ON employees.emp_no =salaries.emp_no GROUP BY employees.emp_no;
7 
8 SELECT * FROM v_salaries;
9 SELECT * FROM v_salaries WHERE emp_no =1001;

简化查询语句测试

2.可以进行权限控制:

把表的权限封闭,但是开放相应的视图权限,视图只开放部分数据列;

案例:比如把员工的工资表的权限关闭,但是开放员工的发放工资的次数的权限。

MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15) MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15)

1 --权限控制
2 CREATE VIEW v_getSalaryCount
3 AS
4 SELECT employees.emp_no,first_name,last_name,COUNT(salary) AS salaryNum FROM employees LEFT JOIN salaries
5 ON employees.emp_no =salaries.emp_no GROUP BY employees.emp_no;
6 
7 SELECT * FROM v_getSalaryCount;
8 SELECT * FROM v_getSalaryCount WHERE emp_no =1001;

权限管理测试

3.大数据表分类的时候,比如某张表的数据有100万条,那么可以将这张表分为四个视图

按照对ID取余的计算;

MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15) MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15)

 1 --大数据分区
 2 CREATE VIEW v_employees1
 3 AS
 4 SELECT * FROM employees WHERE emp_no % 4 =0;
 5 
 6 CREATE VIEW v_employees2
 7 AS
 8 SELECT * FROM employees WHERE emp_no % 4 =1;
 9 
10 CREATE VIEW v_employees3
11 AS
12 SELECT * FROM employees WHERE emp_no % 4 =2;
13 
14 CREATE VIEW v_employees4
15 AS
16 SELECT * FROM employees WHERE emp_no % 4 =3;

分区测试

视图与表的关系:

视图是表的查询结果,自然表的数据变化了,会影响视图的结果。

能否对视图表进行增删改查:

视图的增删改也会影响表;

但视图并不总是能增删改的

视图的数据与表的数据一一对应时可以修改;

对视图的insert还应注意:视图必须包含表中没有默认值的列。

视图的创建:

视图的创建语法:

CREATE [OR REPLACE] [ALGORITHM ={UNDEFINED | MERGE | TEMPEABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

注释:该语句能创建新的视图,如果给定了OR REPLACE子句,该语句还能替换已有的视图。

select_statement是一种SELECT语句,它给出了视图的定义。该语句可从基表或其他视图进行选择。

深入理解视图:

视图的存放位置:

视图存放在information_schema数据库的views表里。

存在两种执行算法:

Merge:合并的执行方式,每当执行的时候,先将我们的视图的sql语句与外部查询视图的sql语句,混合在一起,最终执行。

Temptable:临时表模式,每当查询的时候,将视图所使用的SELECT语句生成一个结果的临时表,再在当前的临时表内进行查询。

指的是一个视图是在什么时候执行,依据那些方式执行:

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

对于REMPTABLE,视图的结果将被置于临时表中,然后使用它执行语句。

对于UNDEFINED,MySQL将选择所需要使用的算法。如果可能,它倾向于MERGE而不是REMPTABLE,这是因为MERGE通常更有效,而且如果使用了临时表,视图是不可更新的。

视图的管理:

查看视图的定义:

SHOW TABLE STATUS [FROM 数据库名称] [LIKE ‘视图名称匹配’];

删除视图

只能删除视图的定义,不能删除数据,必须有drop权限。

DROP VIEW [IF EXISTS] view_name [RESTRICT | CASCADE]

查看权限:

SELECT drop_priv FROM mysql.user WHERE USER=’root’;

删除视图:

DROP VIEW IF EXISTS worker_view1;

删除多个:

DROP VIEW IF EXISTS department_view1, department_view2;

关于视图更新的问题:

某些视图是可跟新的。也就是说,可以在诸如UNDATE,DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图

中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。更巨头的讲,如果视图包含下述结构的任意一种,那么它就是不可更新的:
1.聚合函数(SUM(),MIN(),MAX(),COUNT(),等

2.DISTINCT

3.GROUP BY

4.HAVING

5.UNION或UNION ALL
6.位于选择列表的子查询

7.JOIN

8.FROM子句中的不可更新视图

9.WHERE子句中的子查询,引用FROM子句中的表。

10.仅引用文字值(在该情况下,没有要更新的基本表)

11.ALGORITHM = TEMPTABLE(使用临时表总会使视图成为不可更新的)。

MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15) MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15)

 1 --视图更新
 2 --原表
 3 CREATE VIEW v_salaries
 4 AS
 5 SELECT emp_no,salary FROM salaries;
 6 
 7 UPDATE v_salaries SET salary=2500 WHERE emp_no =1001;
 8 
 9 --修改后
10 CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v_salaries
11 AS
12 SELECT emp_no,salary FROM salaries;
13 
14 --更新不了,因为存在 ALGORITHM = TEMPTABLE,破坏了更新条件
15 UPDATE v_salaries SET salary=2500 WHERE emp_no =1001;

视图的更新测试

关于WITH CHECK OPTION的理解及应用:

with check option可以这么解释:通过视图进行的修改,必须也能通过该视图看到修改后的结果。

with check option:更新视图的数据,那么必须先满足视图的条件,满足之后才能够更新到基表中。

MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15) MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15)

 1 --对WITH CHECK OPTION的理解及应用
 2 CREATE OR REPLACE  VIEW v_salaries
 3 AS
 4 SELECT emp_no,salary FROM salaries WHERE emp_no=1001
 5 WITH CHECK OPTION;
 6 
 7 --不能更新,因为1002不在视图条件内
 8 UPDATE v_salaries SET salary=2000 WHERE emp_no =1002;
 9 --可以跟新
10 UPDATE v_salaries SET salary=2000 WHERE emp_no =1001;

WITH CHECK OPTION的理解及应用测试

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
mysql(视图 事务 索引 外键)
视图视图本质就是对查询的封装创建视图(定义视图 起名以v\_开头)createviewv\_studentsasselectclasses.nameas c\_name,students.\fromstudentsinnerjoinclassesonstudents.cls\_idclasses.id;查
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL学习(十二)
视图view在查询中,我们经常把查询结果当成临时表来看,view是什么?view可以看成一张虚拟表,是表通过某种运算得到的一个投影。表的变化会影响到视图既然视图只是表的某种查询的投影,所以主要步骤在于查询上,查询的结果命名为视图就可以了。创建视图的语法CREATEVIEW视图ASSELECT语句;mysqlcreate
Wesley13 Wesley13
3年前
MySQL学习笔记20
二十、使用视图1、视图视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。例如:SELECTcust\_name,cust\_contact     FROMcustomers,orders,orderitems     WHEREcustomers.cust\_idorder
Wesley13 Wesley13
3年前
MySQL 5.7查询sys.schema_redundant_indexes居然慢如蜗牛...
大家好,我是知数堂SQL优化班老师网名:骑龟的兔子作为DBA经常使用SYS视图,但是如下情况。查询mysql自己创建好的sys系统视图,超过70秒还没结果,超时了。一般情况下,不会出现问题,但是,如本案例,表很多的时候就会出现问题selectcount()frominformation_
Easter79 Easter79
3年前
SwiftUI 跨组件数据传递
作者:Cyandev,iOS和MacOS开发者,目前就职于字节跳动0x00前言众所周知,SwiftUI的开发模式与React、Flutter非常相似,即都是声明式UI,由数据驱动(产生)视图,视图也会与数据自动保持同步,框架层会帮你处理“绑定”的问题。在声明式UI中不存在命令式地让一个视图变成xxx
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究