【MySQL】6.视图

含义&＃xff1a;虚拟表&＃xff0c;和普通表一样使用
mysql5.1版本出现的新特性&＃xff0c;是通过表动态生成的数据

案例&＃xff1a;查询姓张的学生名和专业名

#没用视图之前
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.&＃96;majorid&＃96;&＃61; m.&＃96;id&＃96;
WHERE s.&＃96;stuname&＃96; LIKE &＃39;张%&＃39;;


#用视图
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.&＃96;majorid&＃96;&＃61; m.&＃96;id&＃96;;SELECT * FROM v1 WHERE stuname LIKE &＃39;张%&＃39;;


一、创建视图

语法&＃xff1a;

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 &＃61; d.department_id
JOIN jobs j ON j.job_id &＃61; e.job_id;#②使用
SELECT * FROM myv1 WHERE last_name LIKE &＃39;%a%&＃39;;


2.查询各部门的平均工资级别

#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;#②使用
SELECT myv2.&＃96;ag&＃96;,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.&＃96;ag&＃96; BETWEEN g.&＃96;lowest_sal&＃96; AND g.&＃96;highest_sal&＃96;;


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.&＃96;department_id&＃96;&＃61;d.&＃96;department_id&＃96;;


二、视图的修改

方式一&＃xff1a;

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;


方式二&＃xff1a;

语法&＃xff1a;

ALTER VIEW 视图名
AS
查询语句;ALTER VIEW myv3
AS
SELECT * FROM employees;


三、删除视图

语法&＃xff1a;

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&＃43;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(&＃39;张飞&＃39;,&＃39;zf&＃64;qq.com&＃39;);


#2.修改
UPDATE myv1 SET last_name &＃61; &＃39;张无忌&＃39; WHERE last_name&＃61;&＃39;张飞&＃39;;


#3.删除
DELETE FROM myv1 WHERE last_name &＃61; &＃39;张无忌&＃39;;


具备以下特点的视图不允许更新

①包含以下关键字的sql语句&＃xff1a;分组函数、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 myv1 SET m&＃61;9000 WHERE department_id&＃61;10;


②常量视图

CREATE OR REPLACE VIEW myv2
ASSELECT &＃39;john&＃39; NAME;SELECT * FROM myv2;#更新
UPDATE myv2 SET NAME&＃61;&＃39;lucy&＃39;;


③Select中包含子查询

CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资&＃61;100000;


④join

CREATE OR REPLACE VIEW myv4
ASSELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id &＃61; d.department_id;#更新SELECT * FROM myv4;
UPDATE myv4 SET last_name &＃61; &＃39;张飞&＃39; WHERE last_name&＃61;&＃39;Whalen&＃39;;
INSERT INTO myv4 VALUES(&＃39;陈真&＃39;,&＃39;xxxx&＃39;);


⑤from一个不能更新的视图

CREATE OR REPLACE VIEW myv5
ASSELECT * FROM myv3;#更新SELECT * FROM myv5;UPDATE myv5 SET 最高工资&＃61;10000 WHERE department_id&＃61;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&＃61;10000 WHERE last_name &＃61; &＃39;k_ing&＃39;;