含義：本身是一個虛擬表，數(shù)據(jù)來自于表，通過執(zhí)行時動態(tài)生成，和普通表一樣使用（mysql5.1 版本出現(xiàn)的新特征）
好處：
- 簡化復雜的SQL操作，不必知道它的查詢細節(jié)
- 臨時性
- 實現(xiàn)了SQL語句的重用
- 保護數(shù)據(jù)，提高安全性
比如：臨時在23個班中臨時組建舞蹈班
與表的對比
| -- | 創(chuàng)建語法的關鍵字 | 是否實際占用物理空間 | 使用 |
|---|---|---|---|
| 視圖 | CREATE VIEW | 只保存了SQL邏輯所需的空間 | 增刪改查，一般不能增刪改 |
| 表 | CREATE TABLE | 占用 | 增刪改查 |
案例：查詢姓張的學生名和專業(yè)名
SELECT studentname,majorname
FROM student
INNER JOIN major
ON student.majorid = major.`majorid`
WHERE studentname LIKE '%張%';
視圖：
CREATE VIEW v1
AS
SELECT studentname,majorname
FROM student
INNER JOIN major
ON student.majorid = major.`majorid`
WHERE studentname LIKE '%張%';
SELECT * FROM v1;
CREATE VIEW v2
AS
SELECT studentname,majorname
FROM student
INNER JOIN major
ON student.majorid = major.`majorid`;
SELECT * FROM v2
WHERE studentname LIKE '%張%';
一、創(chuàng)建視圖
語法：CREATE VIEW 視圖名
AS
查詢語句;
創(chuàng)建
- 查詢姓名中包含a字符的員工名、部門名和工種信息
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
查詢各部門的工資級別
創(chuàng)建視圖查看每個部門的平均工資
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
使用
SELECT m.ag,g.grade_level
FROM myv2 m
JOIN job_grades g
ON m.ag BETWEEN g.lowest_sal AND g.highest_sal;
查詢平均工資最低的部門信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
查詢平均工資最低的部門名和工資
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.department_id = d.department_id;
視圖的修改
方式一：
CREATE OR REPLACE VIEW 視圖名
AS
查詢語句;
SELECT * FROM myv3;
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
方式二：
語法：
ALTER VIEW 視圖名
AS
查詢語句;
ALTER VIEW myv3
AS
SELECT * FROM employees;
三、刪除視圖
語法：
DROP VIEW 視圖名,視圖名,...;
DROP VIEW myv1,myv2,myv3;
四、查看視圖
DESC myv3;
SHOW CREATE VIEW myv3;
五、視圖的更新
改變簡單的視圖會改變原始表，一般會為其添加權限
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM employees
WHERE last_name = '張無忌';
1.插入
INSERT INTO myv1 VALUES('張飛','zf@qq.com');
2.修改
UPDATE myv1 SET last_name = '張無忌' WHERE last_name = '張飛';
3.刪除
DELETE FROM myv1 WHERE last_name = '張無忌';
具備以下特點的視圖不允許更新
包含分組函數(shù)、DISTINCT、GROUP BY、HAVING、UNION、UNION ALL
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
#更新
UPDATE muv1 SET = 9000 WHERE department_id = 10;
常量視圖
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
#更新
UPDATE myv2 SET NAME = 'luck';
select 中包含子查詢
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工資;
SELECT * FROM myv3;
#更新
UPDATE myv2 SET 最高工資 = '100000';
join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
SELECT * FROM myv4;
#更新(可以update不能insert)
UPDATE myv4 SET last_name= '張飛' WHERE last_name = 'whalen';
INSERT INTO myv4 VALUE('真沉','xxx');
from一個不能更新的視圖
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
SELECT * FROM myv5;
UPDATE myv5 SET 最高工資 = 10000;
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;
UPDATE myv6 SET salary = 10000 WHERE last_name = 'k_ing';
