視圖的創(chuàng)建
視圖:MySql從5.0.1版本開始提供視圖功能,。一種虛擬存在的表,，
行和列的數(shù)據(jù)來定義視圖的查詢中使用的表,，并且是在使用視圖的動態(tài)生成,，只保存了SQL邏輯,，
不保存查詢結果,。
應用場景:
多個地方用到同樣的的查詢結果,。
該查詢結果使用的SQL語句教復雜,。

先來一個案例領略一下視圖的魅力,。
#正常查詢寫法
查詢姓張的同學與學科
SELECT s.studentname,m.majorname
FROM student s
INNER JOIN major m ON s.majorid = m.majorid
WHERE s.studentname LIKE '%張%';

#視圖封裝寫法
CREATE VIEW shitu
AS
SELECT s.studentname,m.majorname
FROM student s
INNER JOIN major m ON s.majorid = m.majorid;

#運用視圖(得到與上面圖片一樣的結果)
SELECT * FROM shitu WHERE studentname LIKE '%張%';

#創(chuàng)建視圖

語法:
CREATE VIEW 視圖名
AS
查詢語句
優(yōu)點:
重用SQL語句
簡化復雜的SQL的操作，不必知道它的查詢細節(jié)
保護數(shù)據(jù),，提高安全性(隱藏原始表的信息)

#切換庫
USE myemployees;
#1.查詢郵箱中包含a字符的員工名,，部門名和工種信息。
#1.正常查詢方法
SELECT e.last_name,d.department_name,j.job_title,e.email
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.email LIKE'%a%';
#視圖查詢方法

#①創(chuàng)建視圖
CREATE VIEW ayuan AS
SELECT e.last_name,d.department_name,j.job_title,e.email
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id;

#②使用視圖查詢
SELECT * FROM ayuan WHERE email LIKE '%a%';

#2.查詢各部門的平均工資級別
#第一步創(chuàng)建視圖,，查出平均工資
CREATE VIEW pj
AS
SELECT ROUND(AVG(salary),2) ag,department_id
FROM employees
GROUP BY department_id;

#第二步連接查詢
SELECT pj.ag,g.grade_level
FROM pj
INNER JOIN job_grades g
ON pj.ag BETWEEN g.lowest_sal AND g.highest_sal;

#3.查詢平均工資最低的部門
#復用上一題的SQL語句邏輯,，查出平均工資,。
CREATE VIEW pj
AS
SELECT ROUND(AVG(salary),2) ag,department_id
FROM employees
GROUP BY department_id;
#查出最低工資
SELECT * FROM pj ORDER BY ag LIMIT 1;

#4.查詢平均工資最低的部門名和工資
#思路1.視圖也可以套視圖
#1.第一步
CREATE VIEW pj2
AS
SELECT *
FROM pj
ORDER BY ag LIMIT 1;
#2.第二步
SELECT d.department_id,d.department_name,p.ag
FROM pj2 p
INNER JOIN departments d
ON p.department_id=d.department_id;

#思路2.復用第二題的SQL語句邏輯,，查出平均工資
SELECT d.department_name,pj.ag,d.department_id
FROM pj
INNER JOIN departments d
ON pj.department_id=d.department_id
ORDER BY ag LIMIT 1;

視圖的修改
修改視圖:
語法方式一
CREATE OR REPLACE VIEW 視圖名
AS
查詢語句;
視圖存在修改，視圖不存在創(chuàng)建。

#示例:修改視圖
#1.查看原視圖
SELECT * FROM pj2;

#2.修改視圖
CREATE OR REPLACE VIEW pj2
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

修改視圖
語法:方式二
ALTER VIEW 視圖名
AS
查詢語句;
只能修改,，不能創(chuàng)建,。

ALTER VIEW pj2
AS
SELECT * FROM employees;

刪除視圖
語法:
DROP VIEW 視圖名,...;
可以一次性刪除多個,。

DROP VIEW pj,pj2;

查看視圖結構
#第一種方式
DESC pj2;

#第二種方式:
完整的展示視圖創(chuàng)建的語句(LINUX下查看)
SHOW CREATE VIEW pj2;

案例:視圖的創(chuàng)建
#一.創(chuàng)建視圖emp_v1,要求查詢電話號碼以'011’開頭的員工姓名和工資,，郵箱,。
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,first_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
查看視圖
SELECT * FROM emp_v1;

#二.創(chuàng)建視圖emp_v2,要求查詢部門的最高工資高于12000的部門信息。

#常規(guī)寫法,，第一步查出每個部門最高工資
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
#第二步,查詢最高工資高于12000的部門信息
SELECT d.*,m.mx_dep
FROM departments d
INNER JOIN (
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000
) m
ON m.department_id=d.department_id;

#視圖第一步，獨立最高工資
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;

#視圖第二步.視圖也可以起別名
SELECT d.*,m.mx_dep
FROM departments d
INNER JOIN emp_v2 m
ON m.department_id = d.department_id;

視圖的更新
(視圖內(nèi)的數(shù)據(jù)修改)
#不能更新/不是所有的視圖都能更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;

#重新建立視圖
CREATE OR REPLACE VIEW myv2
AS
SELECT last_name,email
FROM employees;

INSERT INTO myv2 VALUES('guangtouqiang','[email protected]');
/* Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 
query: 0.016 sec. */
受影響的行：1 找到的行：0 警告：0 1 查詢的持續(xù)時間：0.016 秒,。

#查看視圖中的數(shù)據(jù)
SELECT * FROM myv2;
SELECT * FROM employees;
/數(shù)據(jù)插入成功后,，視圖與原始表會同時更新數(shù)據(jù)

#2修改數(shù)據(jù)
UPDATE myv2 SET last_name = '高倉健' WHERE last_name = 'guangtouqiang';

#查看視圖中的數(shù)據(jù)
SELECT * FROM myv2;
SELECT * FROM employees;

#3.刪除數(shù)據(jù)
DELETE FROM myv2 WHERE last_name = '高倉健';
/語句執(zhí)行后，視圖與原始表會同時刪除數(shù)據(jù)/

#視圖的可更新性和視圖中查詢的定義有關系,，以下類型的視圖是不能更新的,。
發(fā)現(xiàn)包含以下的字段都是涉及到全表的數(shù)據(jù)，所以不允許修改,。
包含以下關鍵字的SQL語句:分組函數(shù),，DISTINCT，GROUP BY,HAVING,
常量視圖
UNION或者UNION ALL
SELECT中包含子查詢
JOIN
FROM一個不能更新的視圖
WHERE子句的子查詢引用了FROM 子句中的表,。

①　#分組函數(shù),，新建視圖
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) M,department_id
FROM employees
ORDER BY department_id;

#查看視圖
SELECT * FROM myv1;

②　#常量視圖
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;

③　#SELECT中包含子查詢
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT AVG(salary) FROM employees) AS 平均工資;
SELECT * FROM myv3;

④　#JOIN
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;
SELECT * FROM myv4;

#更新
UPDATE myv4 SET last_name = 'GuangTouQiang' WHERE last_name='Fay';

⑤　#FROM一個不能更新的視圖
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT AVG(salary) FROM employees) AS 平均工資,department_id
FROM employees;
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
SELECT * FROM myv5;

⑥　#WHERE子句的子查詢引用了FROM 子句中的表,。
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
SELECT * FROM myv6;