视图
含义: 虚拟表,和普通表一样使用
一、创建视图
语法:
CREATE view 视图名
AS
查询语句
FROM 表名
WHERE condition
案例:
1.查询姓名中包含a字符的员工名,部门名和工种信息
#创建
create view myv1
as
select last_name,depname,jobtitle
from employees e
join departments d
on e.dep_id = d.dep_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 departmet_id;
#使用视图
select myv2.ag,g.grade_level
from myv2
jion job_grades g
on myv2.ag between g.lowest_sal and g.highest_sal;
二、视图的修改
方式一:
create or replace view 视图名
as
查询语句;
方式二:
alter view 视图名
as
查询语句;
三、删除视图
语法:
drop view 视图名,视图名,…;
四、查看视图
desc myv3;
show create view myv3;
五、视图的更新
案例:
create or replace view myv4
as
select last_name,email,salary12(1+ifnull(commission_pct,0)) ‘annual salary’
from employees;
#1、插入
inert into myv4 values(‘syz’,‘syx@qq.com’,100000);
#2、修改
update myv3 set last_name = ‘张’ where last_name = ‘syx’;
#3、删除
delete from myv3 where last_name = ‘张’;
以下试图不允许更新
分组函数,distinct,group by,having,union或者union all
视图和表对比
视图:create view;只是保存了sql逻辑,增删改查(一般只做查)
表: create table;保存了数据,增删改查