知名做网站的公司,联合早报 即时消息,dede网站地图制作,什么事网站开发这是Mysql系列第15篇。
环境#xff1a;mysql5.7.25#xff0c;cmd命令中进行演示。
需求背景
电商公司领导说#xff1a;给我统计一下#xff1a;当月订单总金额、订单量、男女订单占比等信息#xff0c;我们啪啦啪啦写了一堆很复杂的sql#xff0c;然后发给领导。
…这是Mysql系列第15篇。
环境mysql5.7.25cmd命令中进行演示。
需求背景
电商公司领导说给我统计一下当月订单总金额、订单量、男女订单占比等信息我们啪啦啪啦写了一堆很复杂的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
ASSELECTt1.last_name,t2.department_name,t3.job_titleFROM employees t1, departments t2, jobs t3WHERE t1.department_id t2.department_idAND 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
ASSELECTt1.department_id 部门id,t1.ag 平均工资,t2.grade_level 工资级别FROM (SELECTdepartment_id,AVG(salary) agFROM employeesGROUP BY department_id)t1, job_grades t2WHERE 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
ASSELECTjob_id,AVG(salary) javgFROM employeesGROUP 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 ALGORITHMUNDEFINED DEFINERrootlocalhost 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 myv4ASSELECT last_name,emailfrom employees;/*插入*/
insert into myv4 VALUES (路人甲Java,javacode2018163.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%;注意视图的更新我们一般不使用了解即可。
总结 了解视图的用途及与表的区别。 掌握视图的创建、使用、修改、删除。
