数据库学习-MySQL的视图

介绍
视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

作用
简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图。

创建视图

语法：

create [or replace] [algorithm={undefined | merge | temptable}]
view view_name [(colum_list)]
as select_statement
[with [cascaded | local] check option]
参数说明：
algorithm：可选项，表示视图选择的算法
view_name：表示要创建的视图名称
column_list：可选项，指定视图中各个属性的名词，默认情况下与select语句中的查询的属性相同
select_statement：表示一个完整的查询语句，将查询记录导入视图中
[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内

数据准备

-- 准备数据
create database if not exists mydb6_view;
use mydb6_view;
create table dept(
	deptno int primary key,
  dname varchar(20),
	loc varchar(20)
);
insert into dept values(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

create table emp(
	empno int primary key,
	ename varchar(20),
	job varchar(20),
	mgr int,
	hiredate date,
	sal numeric(8,2),
	comm numeric(8, 2),
	deptno int,
-- 	FOREIGN KEY (mgr) REFERENCES emp(empno),
	FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
insert into emp values
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),
(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);

create table salgrade(
	grade int primary key,
	losal int,
	hisal int
);
insert into salgrade values
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

创建视图

create or replace view view1_emp
as
select ename,job from emp;
-- 查看表和视图
show tables;
show full tables;
select * from view1_emp;

修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过create or replace view语句和alter view语句来修改视图。

格式：

alter view 视图名 as select 语句;

alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a,emp b where a.deptno=b.deptno;
select * from view1_emp;

更新视图

某些视图是可更新的。也就是说，可以在update、delete或insert等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：
聚合函数（sum(),min(),max(),count()等）
distinct
group by
having
union或union all
位于选择列表中的子查询
join
from子句中的不可更新视图
where子句中的子查询，引用from子句中的表
仅引用文字值（在该情况下，没有要更新的基本表）

重命名视图

rename table 视图名 to 新视图名;

删除视图

drop view if exists 视图名;

练习

-- 查询部门平均薪水最高的部门名称
select
	a.deptno,
	a.dname,
	a.loc,
	avg_sal 
from
	dept a,
	(
	select
		* 
	from
		(
		select
			*,
			rank() over ( order by avg_sal desc ) rn 
		from
			( select deptno, avg( sal ) avg_sal from emp group by deptno ) t 
		) tt 
	where
		rn = 1 
	) ttt 
where
	a.deptno = ttt.deptno;
--------------------------------------
create view test_view1
as
select deptno, avg( sal ) avg_sal from emp group by deptno;
create view test_view2
as
select
			*,
			rank() over ( order by avg_sal desc ) rn 
		from
			test_view1 t 
create view test_view3
as
select
		* 
	from
		test_view2 tt 
	where
		rn = 1 

select
	a.deptno,
	a.dname,
	a.loc,
	avg_sal 
from
	dept a,
	test_view3 ttt 
where
	a.deptno = ttt.deptno;
--------------------------------------------
create view test_view11
as
select
	a.deptno,
	a.dname,
	a.loc,
	avg_sal 
from
	dept a,
	(
	select
		* 
	from
		(
		select
			*,
			rank() over ( order by avg_sal desc ) rn 
		from
			( select deptno, avg( sal ) avg_sal from emp group by deptno ) t 
		) tt 
	where
		rn = 1 
	) ttt 
where
	a.deptno = ttt.deptno;
	
select * from test_view11;

-- 查询员工比所属领导薪资高的部门名、员工名、员工领导编号
	-- 查询员工比领导工资高的部门号
create view test_view4
as
select
	a.ename ename,
	a.sal esal,
	b.ename mgrname,
	b.sal msal,
	a.deptno 
from
	emp a,
	emp b 
where
	a.mgr = b.empno 
	and a.sal > b.sal;
 -- 将第一步查询出来的部门号和部门表进行链表查询
select * from dept a join test_view4 b on a.deptno=b.deptno;

-- 查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，并查询出薪资在前三名的员工信息
create view test_view5
as
select
	a.deptno,
	a.dname,
	a.loc,
	b.empno,
	b.ename,
	b.sal 
from
	dept a
	join emp b on a.deptno = b.deptno and year(hiredate)>'2000' and a.loc='上海'
	join salgrade c on grade=4 and b.sal between c.losal and c.hisal;

select *
from
(
select
*,
rank() over(order by sal desc) rn
from
 test_view5
)t
where rn<=3;