MySQL之视图

含义:
虚拟表,和普通表一样使用,mysql5.1版本出现的新特性,是通过表动态生成的数据
比如:
舞蹈班和普通班级的对比
优点:

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

视图和表的对比:

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

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

use students;

drop table if exists stuinfo;
create table if not exists stuinfo(
	id int,
	stuname varchar(20),
	gender char(1),
	age int default 18,
	seat int,
	majorid int
) default charset=utf8;

drop table if exists major;
create table major(
	id int,
	majorName varchar(20)
);

select stuname,majorname
from stuinfo s
inner join major m on s.majorid=m.id
where s.stuname like '张%';

create view myv1
as
select stuname,majorname
from stuinfo s
inner join major m on s.majorid=m.id;
select * from myv1 where stuname like '张%';

一.创建视图

语法:
create view 视图名 as 查询语句;

use myemployees;

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;

二、视图的修改

方式一：

create or replace view 视图名
as
查询语句;

use myemployees;
select * from myv3;

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;
select * from myv3;

三.删除视图

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

drop view myv1,myv2,myv3;

四.查看视图

desc myv3;
show create view  myv3;

五.视图的更新(可以更新原始表)

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

1.插入

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

2.修改

set SQL_SAFE_UPDATES = 0;
update myv1 set last_name ='张无忌' where last_name='张飞';

3.删除

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

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

• 包含以下关键字的sql语句：
  分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

drop view myv1;

①group by

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; # 失败error1288

②常量视图

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

更新

update myv2 set name='lucy'; # 失败error1288

③Select中包含子查询

create or replace view myv3
as
select (select max(salary) from employees) 最高工资;
```s
更新
```sql
select * from myv3;
update myv3 set 最高工资=10000; # 失败error1288

练习题

一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

create or replace view emp_v1
as
select last_name,salary,email
from employees
where phone_number like '011%';
select * from emp_v1;

二、要求将视图emp_v1修改为查询电话号码以‘011’开头的并且邮箱中包含e字符的员工姓名和邮箱、电话号码

create or replace view emp_v2
as
select max(salary) mx_dep,department_id
from employees
group by department_id
having max(salary)>12000;

select d.*,emp_v2.mx_dep
from departments d
join emp_v2
on emp_v2.department_id=d.department_id;