文章目录
- MySQL的视图
- 视图基本操作
- 创建视图
- 修改视图
- 练习
MySQL的视图
- 视图是虚拟的表，是从数据库中一个或多个表中导出来的表，作用是可以隐藏一些数据，也可以将一些复杂的查询结果做成视图。
- 数据库只保存视图的定义，而不保存视图中的数据，数据存放在原表也依赖于原表，当原表发生变化时，视图中的数据也会发生变化。
- 好处
– 视图可以简化用户的数据查询操作，可以把重复使用的查询更加方便使用，也可以使复杂的查询易于理解和使用。
– 视图可以保护数据的安全，可以对不同的用户定义不同的查询结果。
视图基本操作
创建视图
将一条select语句封装成一个虚拟表
/*create [or replace] [algorithm={undefined|merge|temptable}]view 视图名称 [(column_list)]as select语句[with [cascasded | local] check option]algrithm: 视图算法，默认是undefined，可选merge或temptablecolumn_list: 指定视图中各个属性的名词，默认与select中的查询属性相同[with [cascasded | local] check option]：指定视图是否允许更新，默认是cascasded
*/
create or replace view v_emp
as
select a.deptno, a.ename, a.sal from emp a;
- 查看表与视图
show full tables;
修改视图
- 更换构造视图的select查询语句
alter view v_emp
as
select a.deptno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno;
- 更新视图（针对更换查询语句前）
update v_emp set ename = 'jack' where ename = 'scott';
insert into v_emp values(10, 'jack', 5000);
- 下面情况不可更新
- 视图包含聚合函数
create or replace view v_emp_b1asselect count(*) cnt from emp;select * from v_emp_b1;update v_emp_b1 set cnt = 20; -- 报错insert into v_emp_b1 values(20); -- 报错
- 视图包含distinct
create or replace view v_emp_b2asselect distinct job from emp;select * from v_emp_b2;update v_emp_b2 set job = 'fff' where job = 'analyst'; -- 报错insert into v_emp_b2 values('fff'); -- 报错
- 视图包含分组函数(group by)或having
create or replace view v_emp_b3asselect a.deptno, count(*) cnt from emp a group by a.deptno having count(*) > 2;select * from v_emp_b3;update v_emp_b3 set cnt = 20 where cnt = 5; -- 报错insert into v_emp_b3 values(10, 20); -- 报错
- 视图包含union或union all
create or replace view v_emp_b4asselect a.deptno, a.ename from emp a where a.deptno = 10unionselect a.deptno, a.ename from emp a where a.deptno <= 20;select * from v_emp_b4;update v_emp_b4 set ename = 'jack' where ename = 'scott'; -- 报错insert into v_emp_b4 values(10, 'jack'); -- 报错
- 视图包含子查询
create or replace view v_emp_b5asselect a.deptno, a.ename from emp a where a.sal > (select avg(a.sal) from emp a);select * from v_emp_b5;update v_emp_b5 set ename = 'ham' where ename = 'king'; -- 报错insert into v_emp_b5 values(10, 'ham'); -- 报错
- 视图包含join
create or replace view v_emp_b6asselect a.deptno, b.dname from emp a join dept b on a.deptno = b.deptno;select * from v_emp_b6;update v_emp_b6 set dname = 'sales' where dname = 'accounting'; -- 报错insert into v_emp_b6 values(10, 'sales'); -- 报错
- select仅引用文字值
create or replace view v_emp_b7asselect 'aaa' dname, '小星星' ename;select * from v_emp_b7;update v_emp_b7 set dname = 'bbb' where ename = '小星星'; -- 报错insert into v_emp_b7 values('bbb', '小星星'); -- 报错
- 视图其他操作
- 重命名视图
rename table v_emp to new_v_emp;
- 删除视图
drop view if exists new_v_emp;
练习
- 找出平均工资最高的部门
– 多重子查询
selecta.dname, a.deptnofromdept a join(select* from(select*,rank() over(order by t.avg_sal DESC) rnfrom(select a.deptno, avg(a.sal) avg_sal from emp a group by a.deptno) t) tt where tt.rn = 1) ttt where a.deptno = ttt.deptno;
– 视图
create or replace view view_avg_sal1asselect a.deptno, avg(a.sal) avg_sal from emp a group by a.deptno;select * from view_avg_sal1;create or replace view view_avg_sal2asselect *, rank() over(order by t.avg_sal DESC) rn from view_avg_sal1 t;select * from view_avg_sal2;create or replace view view_avg_sal3asselect * from view_avg_sal2 where rn = 1;select * from view_avg_sal3;select a.dname, a.deptno from dept a join view_avg_sal3 b on a.deptno = b.deptno;
- 找出工资比领导高的员工
create or replace view v_emp_b8asselect a.deptno _deptno, b.ename _yg_name, a.empno _ld_id from emp a, emp b where a.empno = b.mgr and a.sal < b.sal;select * from v_emp_b8;select a.dname, b._yg_name, b._ld_id from dept a join v_emp_b8 b on a.deptno = b._deptno;
- 找出工资在4级，且入职时间早于1985年的且工资最高的且工作地点是dallas的员工
-- 工资在4级的员工信息create or replace view v_emp_b9asselect * from emp a, salgrade b where a.sal between b.losal and b.hisal and b.grade = 4;select * from v_emp_b9;-- 入职时间早于1985年的员工信息create or replace view v_emp_b10asselect * from v_emp_b9 a where year(a.hiredate) < 1985;select * from v_emp_b10;-- 工作地点是dallas并按工资降序create or replace view v_emp_b11asselect a.empno, a.ename, a.sal from v_emp_b10 a, dept b where a.deptno = b.deptno and b.loc = 'dallas' order by a.sal desc;select * from v_emp_b11;-- 窗函数得到工资序号create or replace view v_emp_b12asselect *, rank() over (order by a.sal desc) rn from v_emp_b11 a;select * from v_emp_b12 where rn = 1;
