一、MySQL視圖的介紹和作用
MySQL視圖，加油兄弟們，孰能生巧，完整代碼在最后！！！
視圖是一個虛擬的表，并不是真是存在的，視圖其實并沒有真實的數據，他只是根據一個sql語句獲取的一個數據集，并為其命名，視圖只是代表查出來的數據，如果想要找數據，還是需要從原來的數據里面去找。
原表的數據發生變化，視圖的數據也會發生變化
可以簡化代碼，把重復使用的查詢封裝成視圖重復使用，像子查詢里面的需要在寫一次查詢語句，使用視圖，代碼更簡單一點
可以使用視圖，讓不一樣的人看到的數據不一樣
二、創建視圖
數據添加
因為視圖是根據原來的表產生的，所以原來的表發生變化，視圖也會發生變化
比如原來表把名字改為ikun，視圖也會變化
三、修改視圖
四、更新視圖
更新視圖其實事實上還是更新的視圖所對應的原表的數據，并不是所有情況下都可以使用，會有一些限制，好多情況下都不可以更新
將視圖中的甘寧修改為周瑜，原表的甘寧也變成了周瑜
直接插入兩個數據就不可以，因為原表一行不止兩個數據
比如：視圖包含聚合函數不可更新
視圖包含distinct不可更新
總的來是：視圖包含以下不可更新：聚合函數，distinct，group by，having，union，union all，子查詢，join，常量文字值
視圖雖然可以更新，但是大部分不可以，一般來說不要想通過視圖去修改表，可能會導致數據更新失敗
五、刪除視圖和重命名視圖
記住刪除視圖，并不會刪除表的數據，他只是刪除了視圖的定義
六、練習
看起來很復雜，但是其實只是簡單的四層嵌套，第一層分組算平均數，第二層排序號，第三層找出序號為1的，第四層找出部門名稱
或
創建視圖，可以使代碼更加簡潔
七、總結
八、完整代碼
-- 創建視圖?
?
?create database 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);
?
?use mydb6_view;
create or replace?
? view view1_emp
? as?
? select ename,job from emp;
?
-- 查看表和視圖
show tables;
-- 雖然是一個視圖，但是也是會當作一個表來查看
?
?
-- 查看表和視圖的類型
show full tables;
?
select * from view1_emp;
?
-- 修改視圖
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;?
?
-- 更新視圖
create or replace view view1_emp
as?
select ename,job from emp;
?
select * from view1_emp;
?
update view1_emp set ename = '周瑜' where ename = '甘寧';
insert into view1_emp values('孫權','文員');
-- 視圖包含聚合函數不可更新
create or replace view ciew2_emp
AS
select count(*) cnt from emp;?
select * from ciew2_emp;
insert into ciew2_emp values(100);?
update ciew2_emp set cnt = 100;
?
?-- 視圖包含distinct不可更新
?create or replace view view2_emp
?AS
?select job from emp;
?select * from view2_emp;
?insert into view2_emp values('小雞');
?
?
?create or replace view view3_emp
?AS
?select deptno,count(*) cnt from emp group by deptno having cnt > 2;
?select * from view3_emp;
?insert into view3_emp values(30,100);
?
?-- union 就是把兩個表拼一起
?create or replace view view4_emp
?AS
?select empno,ename from emp where empno <= 1005
?union?
?select empno,ename from emp where empno > 1005;
?select * from view4_emp;
?insert into view4_emp values(1003,'小');
?
?
?-- 重命名視圖
?rename table view1_emp to myview1;
?
?
?-- 刪除視圖
?drop view if exists myview1;
?
?
?-- 查詢部門平均薪水最高的部門名稱
? 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;
?
?create view test_view3
?as?
?select * from test_view2 tt where rn = 1;
?
?select * from test_view3;
?-- 使用視圖簡化后
?select a.deptno,a.dname,a.loc,avg_sal
?from dept a,test_view3 ttt?
?where a.deptno = ttt.deptno;
?
?create view view1
?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 view1;
?-- 查詢員工比所屬領導薪資高的部門名、員工名、員工領導編號
?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 test_view4;
?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 b.sal between '20010' and '30000' and year(hiredate) > '2000' and a.loc = '上海';
? select * from test_view5;
??
?select * from?
?(
?select *,rank() over(order by sal desc) rn?
?from test_view5
?)t?
?where rn <= 3;
?
?
