这是Mysql系列第15篇。
环境: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%';
注意:视图的更新我们一般不使用,了解即可。
总结
-
了解视图的用途及与表的区别。文章来源:https://www.toymoban.com/news/detail-702774.html
-
掌握视图的创建、使用、修改、删除。文章来源地址https://www.toymoban.com/news/detail-702774.html
到了这里,关于玩转Mysql系列 - 第15篇:详解视图的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!