詳解MySQL中的檢視

create [or replace] [algorithm = {undefined | merge | temptable}]
 
view view_name [(column_list)]
 
as select_statement
 
[with [cascaded | local] check option]
 
引數說明：
（1）algorithm：可選項，表示檢視選擇的演演算法。
（2）view_name ：表示要建立的檢視名稱。
（3）column_list：可選項，指定檢視中各個屬性的名詞，預設情況下與SELECT語句中的查詢的屬性相同。
（4）select_statement
：表示一個完整的查詢語句，將查詢記錄匯入檢視中。
（5）[with [cascaded | local] check option]：可選項，表示更新檢視時要保證在該檢視的許可權範圍之內。

create database mydb6_view;

create or replace view view1_emp
as 
select ename,job from emp; 
 
-- 檢視表和檢視 
show full tables;

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;

--  ---------更新檢視-------
create or replace view view1_emp
as 
select ename,job from emp;
 
update view1_emp set ename = '周瑜' where ename = '魯肅';  -- 可以修改
insert into view1_emp values('孫權','文員');  -- 不可以插入
 
-- ----------檢視包含聚合函數不可更新--------------
create or replace view view2_emp
as 
select count(*) cnt from emp;
 
insert into view2_emp values(100);
update view2_emp set cnt = 100; 

-- ----------檢視包含distinct不可更新---------
create or replace view view3_emp
as 
select distinct job from emp;
 
insert into view3_emp values('財務');
 
-- ----------檢視包含goup by 、having不可更新------------------
 
create or replace view view4_emp
as 
select deptno ,count(*) cnt from emp group by deptno having  cnt > 2;
 
insert into view4_emp values(30,100);

-- ----------------檢視包含union或者union all不可更新----------------
create or replace view view5_emp
as 
select empno,ename from emp where empno <= 1005
union 
select empno,ename from emp where empno > 1005;
 
insert into view5_emp values(1015,'韋小寶');
 
-- -------------------檢視包含子查詢不可更新--------------------
create or replace view view6_emp
as 
select empno,ename,sal from emp where sal = (select max(sal) from emp);
 
insert into view6_emp values(1015,'韋小寶',30000);

-- ----------------------檢視包含join不可更新-----------------
create or replace view view7_emp
as 
select dname,ename,sal from emp a join  dept b  on a.deptno = b.deptno;
 
insert into view7_emp(dname,ename,sal) values('行政部','韋小寶',30000);
 
-- --------------------檢視包含常數文字值不可更新-------------------
create or replace view view8_emp
as 
select '行政部' dname,'楊過'  ename;
 
insert into view8_emp values('行政部','韋小寶');

-- rename table 檢視名 to 新檢視名; 
rename table view1_emp to my_view1

-- drop view 檢視名[,檢視名…];
drop view if exists view_student;

-- 1：查詢部門平均薪水最高的部門名稱
select dname from dept  a ,(select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 1) b
where a.deptno = b.deptno;  
 
-- 2：查詢員工比所屬領導薪資高的部門名、員工名、員工領導編號
select * from dept x,
(select a.ename aname ,a.sal asal,b.ename bname,b.sal bsal,a.deptno
from emp a, emp b 
where a.mgr = b.empno and a.sal > b.sal) y
where x.deptno = y.deptno;

-- 3：查詢工資等級為4級，2000年以後入職的工作地點為北京的員工編號、姓名和工資，並查詢出薪資在前三名的員工資訊
create view xxx
as       
SELECT e.empno,e.ename,e.sal,e.hiredate
FROM emp e,dept d,salgrade s
WHERE (e.sal BETWEEN  losal AND hisal) AND s.GRADE = 4
AND year(e.hiredate) > '2000'
AND d.loc = '北京';
 
select * from 
(
select 
 *,
 dense_rank() over(order by sal desc ) rn
from xxx
) t
where t.rn <=3;