MySQL 视图 - 《MySQL》

视图的创建
- 创建视图练习
视图的修改
视图的删除
查看视图
视图的更新
视图和表的区别

视图是一种虚拟的表，行和列的数据来自定义视图的查询中所使用的表，并且是在使用视图时动态生成的，只保存 SQL 逻辑，而不保存查询结果

视图是通过表来动态生成的数据，是临时的，只有需要使用时才会生成

当多个地方需要用到同样的查询结果，并且该查询结果使用的 sql 语句比较复杂时，可以定义一个视图

如下案例

#定义一个视图
create view v1
as
select s.`studentname`, m.`majorname`
from student s inner join major m
on s.`majorid` = m.`majorid`
where s.`studentname` like "张%";

如果后面还想使用这个查询结果，只需要

select * from v1;

即可。甚至可以在视图上进行筛选

select * from v1 where id = 1;

视图的创建

语法

create view 视图名
as
查询语句;

#查询有名字中包含字符 a 的员工名，部门名和工种信息
#创建一个视图
create view v2
as
select e.`last_name`, d.`department_name`, j.*
from employees e inner join departments d
on e.`department_id` = d.`department_id`
inner join jobs j
on e.`job_id` = j.`job_id`;

#然后根据视图做筛选
SELECT * FROM v2 WHERE last_name LIKE "%a%";

#查询各部门的平均工资级别

#创建视图，表示每个部门的平均工资和部门 id
create view v3
as
select avg(salary) ag, department_id
from employees
group by department_id;

#然后和视图连接即可
select s.`grade`, d.`department_name`
from sal_grade s inner join  v3
on ag between s.`min_salary` and s.`max_salary`
inner join departments d
on v3.department_id = d.`department_id`;

#查询平均工资最低的部门信息
#先查出平均工资最低的部门 id，作为标量子查询，作为视图
select department_id
from employees
group by department_id
order by avg(salary)
limit 1;


#标量子查询条件
select d.*
from departments d
where d.`department_id` = (SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1);

#又或者，有了上一题的视图 (每个部门的平均工资)，我们只需要在上一题的视图中进行筛选即可
select department_id from v3 order by v3.`ag` limit 1;

#使用视图
select d.*
from departments d
where d.`department_id` = 
(select department_id from v3 order by v3.`ag` limit 1);

#查询平均工资最低的部门名和工资
select d.`department_name`, ags.ag
from departments d 
inner join 
(SELECT department_id, avg(salary) ag
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1) ags
on d.`department_id` = ags.department_id;

#同样，可以借助视图
select d.`department_name`, ags.ag
from departments d 
inner join 
(SELECT * FROM v3 ORDER BY v3.`ag` LIMIT 1) ags
on d.`department_id` = ags.department_id;

视图的优点

实现了 SQL 重用
拆分复杂 SQL，简化书写
保护数据，提高了安全性 ( 和原始表分离，视情况而言选出部分数据 )

创建视图练习

#创建视图 emp_v1，要求查询电话号码以 001 开头的员工姓名和工资，邮箱
create or replace view emp_v1
as
select last_name, salary, email
from employees
where phone_number like "011%";

select * from emp_v1;

#创建视图emp_v2，查询部门的最高工资高于 12000 的部门信息
create or replace view emp_v2
as
select d.*
from employees e inner join departments d
on e.`department_id` = d.`department_id`
group by e.department_id
having MAX(salary) > 12000;

select * from emp_v2;

视图的修改

语法

create or replace view
as
查询语句;

如果视图存在，则替换；否则创建

语法二

alter view 视图名
as
查询语句;

这只是单纯的修改了，并不能创建

视图的删除

语法

drop view 视图名1, 视图名2 ...;

查看视图

语法

desc 视图名;

或者

show create view 视图名;

视图的更新

即更改视图中的数据

首先，创建一个视图

CREATE OR REPLACE VIEW emp_vn AS 
SELECT 
  last_name,
  email
FROM
  employees ;

插入数据

语法和朝表中插入数据一样

insert into 视图名[(指定字段名)] values(全部字段只 / [部分字段值]);

insert into 视图名 set 字段1=值1 ...;

例如

insert into emp_vn values("zss", "zccc@qq.com");

对视图进行插入操作，会对原始表造成同样的修改

修改数据

语法同表的数据修改

例如

update emp_vn set last_name = "张三李四" where last_name = "zss";

同样，对视图更新操作也会对原始表造成修改

删除数据

语法同表数据的删除

例如

delete from emp_vn where last_name = "张三李四";

同样，对视图的删除操作也会对原始表造成修改

所以，一般对视图的操作是受限的，会为视图添加权限，只允许读，而不允许更新

视图更新的注意事项

视图的可更新性和视图中查询的定义有关，以下类型的视图是不能更新的（这里的更新可能是插入，删除，或修改）

包含以下关键字的 SQL 语句不能更新
- 分组函数
- distinct
- group by
- having
- union
- union all
常量视图 ```mysql create view v2 as select “dancy” name;

update v2 set name = “jjj”;



3. 
select 语句中包含子查询
```mysql
create or replace view v3
as
select (select max(salary) from employees) 最高工资;

update v3 set 最高工资=10000

join
使用了 join 创建的视图，其中的数据可以 update，但是不能 insert

from 一个不能更新的视图

create or replace view v4
as
select * from v3;  #引用了一个不能更新的视图来创建新的视图

where 子句的子查询引用了 from 子句中的表

create or replace view v5
as
select *
from employees
where employee_id in (
 select manager_id
 from employees
 where manager_id is not null
);

所以基本上视图是不会用来更新的！！

视图和表的区别

创建语法不一样
- 视图：create view
- 表：create table
是否占用实际的物理空间
- 视图：没有占用 (只占用少部分用于保存 SQL 逻辑)
- 表：占用，保存了实际的数据
使用
- 视图：增删改查，一般不能增删改，只用于查询
- 表：crud 都支持