含义：

虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改
表 create table 保存了数据 增删改查

案例：查询姓张的学生名和专业名

  1. SELECT stuname,majorname FROM stuinfo s
  2. INNER JOIN major m ON s.`majorid`= m.`majorid`
  3. WHERE s.`stuname` LIKE '张%';
create view v1 as(
SELECT stuname,majorname FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`majorid`
WHERE s.`stuname` LIKE '张%'
)
select * from v1;

一、创建视图

语法：

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 ag,g.grade_level
from myv2
join job_grades g
on ag between lowest_sal and 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

二、视图的修改

方式一：

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;

三、删除视图

语法：drop view 视图名,视图名,…;

drop view my_v1,myv1,myv2;

四、查看视图

desc myv3;
show create view myv3;

五、视图的更新

create or replace view myv1
as
select last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary" from employees;
select * from myv3;

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

SELECT * FROM myv1;
SELECT * FROM employees;

1.插入

insert into myv1 values('张飞','zf@qq.com');
select * from myv1;

2.修改

update myv1 set last_name='张无忌' where last_name='张飞';
select * from myv1 ORDER BY last_name desc;

3.删除

delete from myv1 where last_name='张无忌';

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

①包含以下关键字的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;
select * from myv1;

更新

update myv1 set m=9000 where department_id=10;

image.png

②常量视图

create or replace view myv2
as 
select 'join' name;
select * from myv2;

更新
image.png