专辑：MySQL教程_视图-Java充电社【公众号：Java充电社】

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

这是Mysql系列第15篇。

环境：mysql5.7.25，cmd命令中进行演示。

## 需求背景

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

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

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

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

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

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

## 什么是视图

### 概念

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

### 使用场景

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

### 视图和表的区别

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

### 视图的好处

- 简化复杂的sql操作，不用知道他的实现细节
- 隔离了原始表，可以不让使用视图的人接触原始的表，从而保护原始数据，提高了安全性

## 准备测试数据

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

浏览器中打开链接：<a href="http://www.itsoku.com/article/196" target="_blank">http://www.itsoku.com/article/196</a>

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

成功创建`javacode2018_employees`库及5张表，如下：

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

## 创建视图

### 语法

```java
create view 视图名
as
查询语句;
```

### 视图的使用步骤

- 创建视图
- 对视图执行查询操作

### 案例1

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

```java
/*案例1：查询姓名中包含a字符的员工名、部门、工种信息*/
/*①创建视图myv1*/
CREATE VIEW myv1
AS
  SELECT
    t1.last_name,
    t2.department_name,
    t3.job_title
  FROM employees t1, departments t2, jobs t3
  WHERE t1.department_id = t2.department_id
        AND t1.job_id = t3.job_id;

/*②使用视图*/
SELECT * FROM myv1 a where a.last_name like 'a%';
```

效果如下：

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

### 案例2

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

```java
/*案例2：查询各部门的平均工资级别*/
/*①创建视图myv1*/
CREATE VIEW myv2
AS
  SELECT
    t1.department_id 部门id,
    t1.ag            平均工资,
    t2.grade_level   工资级别
  FROM (SELECT
          department_id,
          AVG(salary) ag
        FROM employees
        GROUP BY department_id)
       t1, job_grades t2
  WHERE t1.ag BETWEEN t2.lowest_sal AND t2.highest_sal;

/*②使用视图*/
SELECT * FROM myv2;
```

效果：

```java
mysql> SELECT * FROM myv2;
+----------+--------------+--------------+
| 部门id   | 平均工资     | 工资级别     |
+----------+--------------+--------------+
|     NULL |  7000.000000 | C            |
|       10 |  4400.000000 | B            |
|       20 |  9500.000000 | C            |
|       30 |  4150.000000 | B            |
|       40 |  6500.000000 | C            |
|       50 |  3475.555556 | B            |
|       60 |  5760.000000 | B            |
|       70 | 10000.000000 | D            |
|       80 |  8955.882353 | C            |
|       90 | 19333.333333 | E            |
|      100 |  8600.000000 | C            |
|      110 | 10150.000000 | D            |
+----------+--------------+--------------+
12 rows in set (0.00 sec)
```

## 修改视图

2种方式。

### 方式1

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

```java
create or replace view 视图名
as
查询语句;
```

#### 示例

```java
CREATE OR REPLACE VIEW myv3
AS
  SELECT
    job_id,
    AVG(salary) javg
  FROM employees
  GROUP BY job_id;
```

### 方式2

```java
alter view 视图名
as 
查询语句;
```

#### 示例

```java
ALTER VIEW myv3
AS
SELECT *
FROM employees;
```

## 删除视图

### 语法

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

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

### 示例

```java
mysql> drop view myv1,myv2,myv3;
Query OK, 0 rows affected (0.00 sec)
```

## 查询视图结构

```java
/*方式1*/
desc 视图名称;
/*方式2*/
show create view 视图名称;
```

如：

```java
mysql> show create view myv1;
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                                                                                                                                                                                                               | character_set_client | collation_connection |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| 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      |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
```

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

## 更新视图【基本不用】

> 视图的更新是更改视图中的数据，而不是更改视图中的sql逻辑。
>
> 当对视图进行更新后，也会对原始表的数据进行更新。
>
> 为了防止对原始表的数据产生更新，可以为视图添加只读权限，只允许读视图，不允许对视图进行更新。
>
> 一般情况下，极少对视图进行更新操作。

### 示例

```java
CREATE OR REPLACE VIEW myv4
  AS
  SELECT last_name,email
  from employees;

/*插入*/
insert into myv4 VALUES ('路人甲Java','javacode2018@163.com');
SELECT * from myv4 where email like 'javacode2018%';

/*修改*/
UPDATE myv4 SET last_name = '刘德华' WHERE last_name = '路人甲Java';
SELECT * from myv4 where email like 'javacode2018%';

/*删除*/
DELETE FROM myv4 where last_name = '刘德华';
SELECT * from myv4 where email like 'javacode2018%';
```

**注意：视图的更新我们一般不使用，了解即可。**

## 总结

1. 了解视图的用途及与表的区别。
2. 掌握视图的创建、使用、修改、删除。

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)