视图

1. 视图

含义：虚拟表，和普通表一样使用。mysql5.1版本出现的新特性，是通过表动态生成的数据。

1.1 创建视图

/*
语法：create view 视图名 as 查询语句;
*/
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#创建
create view myv1 as select
	last_name,
	department_name,
	job_title
from
	employees e
join departments d on
	e.department_id = d.department_id
join jobs j on
	j.job_id = e.job_id;

#使用
select
	*
from
	myv1
where
	last_name like '%a%';

#2.查询各部门的平均工资级别
#创建视图查看每个部门的平均工资
create view myv2 as select
	avg(salary) ag,
	department_id
from
	employees
group by
	department_id;

#使用
select
	myv2.`ag`,
	g.grade_level
from
	myv2
join job_grades g on
	myv2.`ag` between g.`lowest_sal` and g.`highest_sal`;

#3.查询平均工资最低的部门信息
select
	*
from
	myv2
order by
	ag
limit 1;

#4.查询平均工资最低的部门名和工资
#基于视图创建视图
create view myv3 as select
	*
from
	myv2
order by
	ag
limit 1;

#使用
select
	d.*,
	m.ag
from
	myv3 m
join departments d on
	m.`department_id` = d.`department_id`;

1.2 视图的修改

#方式一：
/*
语法：
    create or replace
    view 视图名 as 查询语句;
*/
create or replace
view myv3 as select
	avg(salary),
	job_id
from
	employees
group by
	job_id;

#方式二：
/*
语法：alter view 视图名 as 查询语句;
*/
alter view myv3 as select
	*
from
	employees;

1.3 删除视图

/*
语法：
    drop view 视图名1,
    视图名2,
    ...;
*/
drop view myv1,
myv2,
myv3;

1.4 查看视图

desc myv3;

show create view myv3;

1.5 视图的更新

视图的更新会更新创建视图的表。

create or replace
view myv1 as select
	last_name,
	email
from
	employees;

#1.插入
insert
	into
		myv1
	values('张三',
	'zhangsan@qq.com');

#2.修改
update
	myv1
set
	last_name = '李四'
where
	last_name = '张三';

#3.删除
delete
from
	myv1
where
	last_name = '李四';

具备以下特点的视图不允许更新：

#1.包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
create or replace
view myv1 as select
	max(salary) m,
	department_id
from
	employees
group by
	department_id;

#更新失败
update
	myv1
set
	m = 9000
where
	department_id = 10;

#2.常量视图
create or replace
view myv2 as select
	'john' NAME;

#更新失败
update
	myv2
set
	NAME = 'lucy';

#3.select中包含子查询
create or replace
view myv3 as select
	department_id,
	(
	select
		max(salary)
	from
		employees) 最高工资
from
	departments;

#更新失败
update
	myv3
set
	最高工资 = 100000;

#4.join
create or replace
view myv4 as select
	last_name,
	department_name
from
	employees e
join departments d on
	e.department_id = d.department_id;

#更新成功
update
	myv4
set
	last_name = '张三'
where
	last_name = 'Whalen';

#更新失败
insert
	into
		myv4
	values('李四',
	'xxxx');

#5.from一个不能更新的视图
create or replace
view myv5 as select
	*
from
	myv3;

#更新失败
update
	myv5
set
	最高工资 = 10000
where
	department_id = 60;

#6.where子句的子查询引用了from子句中的表
create or replace
view myv6 as select
	last_name,
	email,
	salary
from
	employees
where
	employee_id in(
	select
		manager_id
	from
		employees
	where
		manager_id is not null);

#更新失败
update
	myv6
set
	salary = 10000
where
	last_name = 'k_ing';

1.6 视图和表的区别

	创建语法的关键字	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	增删改查，只是一般不能增删改
表	create table	保存了数据	增删改查