視圖

含義：理解成一張虛擬的表，和普通表一樣使用
mysql5.1版本出現的新特性，是通過表動態生成的數據
比如：舞蹈班和普通班級的對比

視圖和表的區別：

名稱創建語法的關鍵字使用占用物理空間

視圖	create view	增刪改查，只是一般不能增刪改	不占用，僅僅保存的是sql邏輯
表	create table	增刪改查	占用

視圖的好處：

1、sql語句提高重用性，效率高 2、和表實現了分離，提高了安全性

視圖的創建

語法： CREATE VIEW 視圖名 AS 查詢語句;

視圖的增刪改查

1、查看視圖的數據 ★SELECT * FROM my_v4; SELECT * FROM my_v1 WHERE last_name='Partners';2、插入視圖的數據 INSERT INTO my_v4(last_name,department_id) VALUES('虛竹',90);3、修改視圖的數據UPDATE my_v4 SET last_name ='夢姑' WHERE last_name='虛竹';4、刪除視圖的數據 DELETE FROM my_v4;

某些視圖不能更新

包含以下關鍵字的sql語句：分組函數、distinct、group by、having、union或者union all 常量視圖 Select中包含子查詢 join from一個不能更新的視圖 where子句的子查詢引用了from子句中的表

視圖邏輯的更新

#方式一： CREATE OR REPLACE VIEW test_v7 AS SELECT last_name FROM employees WHERE employee_id>100;方式二: ALTER VIEW test_v7 AS SELECT employee_id FROM employees;SELECT * FROM test_v7;

視圖的刪除

DROP VIEW test_v1,test_v2,test_v3;

視圖結構的查看

DESC test_v7; SHOW CREATE VIEW test_v7;

一、創建視圖

語法：
create view 視圖名
as
查詢語句;

#案例：查詢姓張的學生名和專業名 SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.`majorid`= m.`id` WHERE s.`stuname` LIKE '張%';CREATE VIEW v1 AS SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.`majorid`= m.`id`;SELECT * FROM v1 WHERE stuname LIKE '張%';#一、創建視圖 /* 語法： create view 視圖名 as 查詢語句;*/ USE myemployees;#1.查詢姓名中包含a字符的員工名、部門名和工種信息 #①創建 CREATE VIEW myv1 ASSELECT 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%';#2.查詢各部門的平均工資級別#①創建視圖查看每個部門的平均工資 CREATE VIEW myv2 AS SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id;#②使用 SELECT myv2.`ag`,g.grade_level FROM myv2 JOIN job_grades g ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;#3.查詢平均工資最低的部門信息SELECT * FROM myv2 ORDER BY ag LIMIT 1;#4.查詢平均工資最低的部門名和工資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
查詢語句;

方式二：

語法：
alter view 視圖名
as
查詢語句;

#二、視圖的修改#方式一： /* 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 視圖名,視圖名,...; */DROP VIEW emp_v1,emp_v2,myv3;

四、查看視圖

#四、查看視圖DESC myv3;SHOW CREATE VIEW myv3;

五、視圖的更新

#五、視圖的更新CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary" FROM employees;CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email FROM employees;SELECT * FROM myv1; SELECT * FROM employees; #1.插入INSERT INTO myv1 VALUES('張飛','zf@qq.com');#2.修改 UPDATE myv1 SET last_name = '張無忌' WHERE last_name='張飛';#3.刪除 DELETE FROM myv1 WHERE last_name = '張無忌';#具備以下特點的視圖不允許更新#①包含以下關鍵字的sql語句：分組函數、distinct、group by、having、union或者union allCREATE OR REPLACE VIEW myv1 AS SELECT MAX(salary) m,department_id FROM employees GROUP BY department_id;SELECT * FROM myv1;#更新 UPDATE myv1 SET m=9000 WHERE department_id=10;#②常量視圖 CREATE OR REPLACE VIEW myv2 ASSELECT 'john' NAME;SELECT * FROM myv2;#更新 UPDATE myv2 SET NAME='lucy';#③Select中包含子查詢CREATE OR REPLACE VIEW myv3 ASSELECT department_id,(SELECT MAX(salary) FROM employees) 最高工資 FROM departments;#更新 SELECT * FROM myv3; UPDATE myv3 SET 最高工資=100000;#④join CREATE OR REPLACE VIEW myv4 ASSELECT last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;#更新SELECT * FROM myv4; UPDATE myv4 SET last_name = '張飛' WHERE last_name='Whalen'; INSERT INTO myv4 VALUES('陳真','xxxx');#⑤from一個不能更新的視圖 CREATE OR REPLACE VIEW myv5 ASSELECT * FROM myv3;#更新SELECT * FROM myv5;UPDATE myv5 SET 最高工資=10000 WHERE department_id=60;#⑥where子句的子查詢引用了from子句中的表CREATE OR REPLACE VIEW myv6 ASSELECT last_name,email,salary FROM employees WHERE employee_id IN(SELECT manager_idFROM employeesWHERE manager_id IS NOT NULL );#更新 SELECT * FROM myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

總結

以上是生活随笔為你收集整理的十一、MySQL视图学习笔记（详解）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。