mysql更新视图添加字段 mysql 更新视图

转载

mob64ca13feda16 2024-08-14 10:48:59

文章标签 mysql更新视图添加字段 sql mysql java 文章分类 MySQL 数据库

打算提升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	薪资等级表

创建视图

语法

create view 视图名

 


as

 


查询语句;

视图的使用步骤

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

案例1

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

/*案例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%';

效果如下：

mysql> SELECT * FROM myv1 a where a.last_name like 'a%';

 


+-----------+-----------------+----------------------+

 


| last_name | department_name | job_title            |

 


+-----------+-----------------+----------------------+

 


| Austin    | IT              | Programmer           |

 


| Atkinson  | Shi             | Stock Clerk          |

 


| Ande      | Sal             | Sales Representative |

 


| Abel      | Sal             | Sales Representative |

 


+-----------+-----------------+----------------------+

 



4 rows in set (0.00 sec)

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

案例2

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

/*案例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;

效果：

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

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

create or replace view 视图名

 


as

 


查询语句;

示例

CREATE OR REPLACE VIEW myv3

 


AS

 


  SELECT


 


    job_id,

 


    AVG(salary) javg

 


  FROM employees

 


  GROUP BY job_id;

方式2

alter view 视图名

 



as 

 


查询语句;

示例

ALTER VIEW myv3

 


AS

 



SELECT *

 



FROM employees;

删除视图

语法

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

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

示例

mysql> drop view myv1,myv2,myv3;

 



Query OK, 0 rows affected (0.00 sec)

查询视图结构

/*方式1*/

 


desc 视图名称;

 


/*方式2*/

 


show create view 视图名称;

如：

mysql> desc myv1;

 


+-----------------+-------------+------+-----+---------+-------+

 


| Field           | Type        | Null | Key | Default | Extra |

 


+-----------------+-------------+------+-----+---------+-------+

 


| last_name       | varchar(25) | YES  |     | NULL    |       |

 


| department_name | varchar(3)  | YES  |     | NULL    |       |

 


| job_title       | varchar(35) | YES  |     | NULL    |       |

 


+-----------------+-------------+------+-----+---------+-------+

 



3 rows in set (0.00 sec)

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

示例

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%';

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