玩转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. /*②使用视图*/
  14. 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. /*②使用视图*/
  18. 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 OK, 0 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. /*插入*/
  7. insert into myv4 VALUES ('路人甲Java','javacode2018@163.com');
  8. SELECT * from myv4 where email like 'javacode2018%';
  9.  
  10. /*修改*/
  11. UPDATE myv4 SET last_name = '刘德华' WHERE last_name = '路人甲Java';
  12. SELECT * from myv4 where email like 'javacode2018%';
  13.  
  14. /*删除*/
  15. DELETE FROM myv4 where last_name = '刘德华';
  16. 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  阅读(197)  评论(0)  编辑  收藏  举报