玩转Mysql系列 - 第15篇:详解视图

打算提升sql技能的,可以加我微信itsoku,带你成为sql高手。

这是Mysql系列第14篇。

环境:mysql5.7.25,cmd命令中进行演示。

需求背景

电商公司领导说:给我统计一下:当月订单总金额、订单量、男女订单占比等信息,我们啪啦啪啦写了一堆很复杂的sql,然后发给领导。

这样一大片sql,发给领导,你们觉得好么?

如果领导只想看其中某个数据,还需要修改你发来的sql,领导日后想新增其他的统计指标,你又会发送一大坨sql给领导,对于领导来说这个sql看起来很复杂,难以维护。

实际上领导并不关心你是怎么实现的,他关心的只是这些指标,并且方便查看、查询,而你却把复杂的实现都发给了领导。

那我们有什么办法隐藏这些细节,只暴露简洁的结果呢?

数据库已经帮我们想到了:使用视图来解决这个问题。

什么是视图

概念

视图是在mysql5之后出现的,是一种虚拟表,行和列的数据来自于定义视图时使用的一些表中,视图的数据是在使用视图的时候动态生成的,视图只保存了sql的逻辑,不保存查询的结果

使用场景

多个地方使用到同样的查询结果,并且该查询结果比较复杂的时候,我们可以使用视图来隐藏复杂的实现细节。

视图和表的区别


语法实际中是否占用物理空间使用
视图create view只是保存了sql的逻辑增删改查,实际上我们只使用查询
create table保存了数据增删改查

视图的好处

  • 简化复杂的sql操作,不用知道他的实现细节

  • 隔离了原始表,可以不让使用视图的人接触原始的表,从而保护原始数据,提高了安全性

准备测试数据

测试数据比较多,放在我的个人博客上了。

浏览器中打开链接:http://www.itsoku.com/article/209

mysql中执行里面的javacode2018_employees库部分的脚本。

成功创建javacode2018_employees库及5张表,如下:

表名描述
departments部门表
employees员工信息表
jobs职位信息表
locations位置表(部门表中会用到)
job_grades薪资等级表

创建视图

语法

  1. create view 视图名
  2. as
  3. 查询语句;

视图的使用步骤

  • 创建视图

  • 对视图执行查询操作

案例1

查询姓名中包含a字符的员工名、部门、工种信息

  1. /*案例1:查询姓名中包含a字符的员工名、部门、工种信息*/
  2. /*①创建视图myv1*/
  3. CREATE VIEW myv1
  4. AS
  5.   SELECT
  6.     t1.last_name,
  7.     t2.department_name,
  8.     t3.job_title
  9.   FROM employees t1, departments t2, jobs t3
  10.   WHERE t1.department_id = t2.department_id
  11.         AND t1.job_id = t3.job_id;
  12. /*②使用视图*/
  13. SELECT * FROM myv1 a where a.last_name like 'a%';

效果如下:

  1. mysql> SELECT * FROM myv1 a where a.last_name like 'a%';
  2. +-----------+-----------------+----------------------+
  3. | last_name | department_name | job_title            |
  4. +-----------+-----------------+----------------------+
  5. Austin    | IT              | Programmer           |
  6. Atkinson  | Shi             | Stock Clerk          |
  7. Ande      | Sal             | Sales Representative |
  8. Abel      | Sal             | Sales Representative |
  9. +-----------+-----------------+----------------------+
  10. 4 rows in set (0.00 sec)

上面我们创建了一个视图:myv1,我们需要看员工姓名、部门、工种信息的时候,不用关心这个视图内部是什么样的,只需要查询视图就可以了,sql简单多了。

案例2

案例2:查询各部门的平均工资级别

  1. /*案例2:查询各部门的平均工资级别*/
  2. /*①创建视图myv1*/
  3. CREATE VIEW myv2
  4. AS
  5.   SELECT
  6.     t1.department_id 部门id,
  7.     t1.ag            平均工资,
  8.     t2.grade_level   工资级别
  9.   FROM (SELECT
  10.           department_id,
  11.           AVG(salary) ag
  12.         FROM employees
  13.         GROUP BY department_id)
  14.        t1, job_grades t2
  15.   WHERE t1.ag BETWEEN t2.lowest_sal AND t2.highest_sal;
  16. /*②使用视图*/
  17. SELECT * FROM myv2;

效果:

  1. mysql> SELECT * FROM myv2;
  2. +----------+--------------+--------------+
  3. | 部门id   | 平均工资     | 工资级别     |
  4. +----------+--------------+--------------+
  5. |     NULL |  7000.000000 | C            |
  6. |       10 |  4400.000000 | B            |
  7. |       20 |  9500.000000 | C            |
  8. |       30 |  4150.000000 | B            |
  9. |       40 |  6500.000000 | C            |
  10. |       50 |  3475.555556 | B            |
  11. |       60 |  5760.000000 | B            |
  12. |       70 | 10000.000000 | D            |
  13. |       80 |  8955.882353 | C            |
  14. |       90 | 19333.333333 | E            |
  15. |      100 |  8600.000000 | C            |
  16. |      110 | 10150.000000 | D            |
  17. +----------+--------------+--------------+
  18. 12 rows in set (0.00 sec)

修改视图

2种方式。

方式1

如果该视图存在,就修改,如果不存在,就创建新的视图。

  1. create or replace view 视图名
  2. as
  3. 查询语句;
示例
  1. CREATE OR REPLACE VIEW myv3
  2. AS
  3.   SELECT
  4.     job_id,
  5.     AVG(salary) javg
  6.   FROM employees
  7.   GROUP BY job_id;

方式2

  1. alter view 视图名
  2. as 
  3. 查询语句;
示例
  1. ALTER VIEW myv3
  2. AS
  3. SELECT *
  4. FROM employees;

删除视图

语法

drop view 视图名1 [,视图名2] [,视图名n];

可以同时删除多个视图,多个视图名称之间用逗号隔开。

示例

  1. mysql> drop view myv1,myv2,myv3;
  2. Query OK0 rows affected (0.00 sec)

查询视图结构

  1. /*方式1*/
  2. desc 视图名称;
  3. /*方式2*/
  4. show create view 视图名称;

如:

  1. mysql> desc myv1;
  2. +-----------------+-------------+------+-----+---------+-------+
  3. Field           | Type        | Null | Key | Default | Extra |
  4. +-----------------+-------------+------+-----+---------+-------+
  5. | last_name       | varchar(25) | YES  |     | NULL    |       |
  6. | department_name | varchar(3)  | YES  |     | NULL    |       |
  7. | job_title       | varchar(35) | YES  |     | NULL    |       |
  8. +-----------------+-------------+------+-----+---------+-------+
  9. 3 rows in set (0.00 sec)
  1. mysql> show create view myv1;
  2. +------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  3. View | Create View                                                                                                                                                                                                                                                                                                                                                               | character_set_client | collation_connection |
  4. +------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  5. | myv1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myv1` AS select `t1`.`last_name` AS `last_name`,`t2`.`department_name` AS `department_name`,`t3`.`job_title` AS `job_title` from ((`employees` `t1` join `departments` `t2`) join `jobs` `t3`) where ((`t1`.`department_id` = `t2`.`department_id`) and (`t1`.`job_id` = `t3`.`job_id`)) | utf8                 | utf8_general_ci      |
  6. +------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  7. 1 row in set (0.00 sec)

show create view显示了视图的创建语句。

更新视图【基本不用】

视图的更新是更改视图中的数据,而不是更改视图中的sql逻辑。

当对视图进行更新后,也会对原始表的数据进行更新。

为了防止对原始表的数据产生更新,可以为视图添加只读权限,只允许读视图,不允许对视图进行更新。

一般情况下,极少对视图进行更新操作。

示例

  1. CREATE OR REPLACE VIEW myv4
  2.   AS
  3.   SELECT last_name,email
  4.   from employees;
  5. /*插入*/
  6. insert into myv4 VALUES ('路人甲Java','javacode2018@163.com');
  7. SELECT * from myv4 where email like 'javacode2018%';
  8. /*修改*/
  9. UPDATE myv4 SET last_name = '刘德华' WHERE last_name = '路人甲Java';
  10. SELECT * from myv4 where email like 'javacode2018%';
  11. /*删除*/
  12. DELETE FROM myv4 where last_name = '刘德华';
  13. SELECT * from myv4 where email like 'javacode2018%';

注意:视图的更新我们一般不使用,了解即可。

总结

  1. 了解视图的用途及与表的区别。

  2. 掌握视图的创建、使用、修改、删除。

Mysql系列目录

  1. 第1篇:mysql基础知识

  2. 第2篇:详解mysql数据类型(重点)

  3. 第3篇:管理员必备技能(必须掌握)

  4. 第4篇:DDL常见操作

  5. 第5篇:DML操作汇总(insert,update,delete)

  6. 第6篇:select查询基础篇

  7. 第7篇:玩转select条件查询,避免采坑

  8. 第8篇:详解排序和分页(order by & limit)

  9. 第9篇:分组查询详解(group by & having)

  10. 第10篇:常用的几十个函数详解

  11. 第11篇:深入了解连接查询及原理

  12. 第12篇:子查询

  13. 第13篇:细说NULL导致的神坑,让人防不胜防

  14. 第14篇:详解事务

java高并发系列全集

  1. 第1天:必须知道的几个概念

  2. 第2天:并发级别

  3. 第3天:有关并行的两个重要定律

  4. 第4天:JMM相关的一些概念

  5. 第5天:深入理解进程和线程

  6. 第6天:线程的基本操作

  7. 第7天:volatile与Java内存模型

  8. 第8天:线程组

  9. 第9天:用户线程和守护线程

  10. 第10天:线程安全和synchronized关键字

  11. 第11天:线程中断的几种方式

  12. 第12天JUC:ReentrantLock重入锁

  13. 第13天:JUC中的Condition对象

  14. 第14天:JUC中的LockSupport工具类,必备技能

  15. 第15天:JUC中的Semaphore(信号量)

  16. 第16天:JUC中等待多线程完成的工具类CountDownLatch,必备技能

  17. 第17天:JUC中的循环栅栏CyclicBarrier的6种使用场景

  18. 第18天:JAVA线程池,这一篇就够了

  19. 第19天:JUC中的Executor框架详解1

  20. 第20天:JUC中的Executor框架详解2

  21. 第21天:java中的CAS,你需要知道的东西

  22. 第22天:JUC底层工具类Unsafe,高手必须要了解

  23. 第23天:JUC中原子类,一篇就够了

  24. 第24天:ThreadLocal、InheritableThreadLocal(通俗易懂)

  25. 第25天:掌握JUC中的阻塞队列

  26. 第26篇:学会使用JUC中常见的集合,常看看!

  27. 第27天:实战篇,接口性能提升几倍原来这么简单

  28. 第28天:实战篇,微服务日志的伤痛,一并帮你解决掉

  29. 第29天:高并发中常见的限流方式

  30. 第30天:JUC中工具类CompletableFuture,必备技能

  31. 第31天:获取线程执行结果,这6种方法你都知道?

  32. 第32天:高并发中计数器的实现方式有哪些?

  33. 第33篇:怎么演示公平锁和非公平锁?

  34. 第34篇:google提供的一些好用的并发工具类

mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!

路人甲java

640?wx_fmt=png

▲长按图片识别二维码关注

路人甲java:工作10年的前阿里P7分享Java、算法、数据库方面的技术干货!坚信用技术改变命运,让家人过上更体面的生活。

来源:https://itsoku.blog.csdn.net/article/details/101805969
posted @ 2022-04-23 22:18  程序员小明1024  阅读(208)  评论(0编辑  收藏  举报