3. mysql-视图

一、什么是视图

/*
含义：虚拟的表 和普通的表一样使用
通过表动态生成的数据，行和列的数据来自定义视图的查询中使用的表，
并且在使用视图时‘动态生成’，只保存了sql逻辑，不保存查询结果

对比	创建语法的关键字	是否实际占用物理空间	使用
视图	create view		是保存了sql逻辑		增删改查，一般只用来查询
表	create table		保存了数据		增删改查
*/
#案例；姓张的学生名和专业名
SELECT `stuname`,`majorName`
FROM `major` m
INNER JOIN `stuinfo` s ON s.`majorId` = m.`id`
WHERE `stuname` LIKE '张%';
#创建视图
CREATE VIEW v1
AS 
SELECT `stuname`,`majorName`
FROM `major` m
INNER JOIN `stuinfo` s ON s.`majorId` = m.`id`;

SELECT * FROM v1 WHERE `stuname` LIKE '张%';

二、创建视图

/*
语法：
create view 视图名
as 
查询语句;

*/
USE `myemployees`;
#案例1. 查询姓名中包含a字符的员工名、部门名、工种信息
#①创建视图
CREATE VIEW myv1
AS
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d ON e.`department_id` = d.`department_id`
INNER JOIN `jobs` j ON j.`job_id` = e.`job_id`;
#使用视图
SELECT * FROM myv1 WHERE `last_name` LIKE '%a%';

#案例2 查询各部门的平均工资级别
#1 创建视图 查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT  AVG(`salary`) ag,`department_id`
FROM `employees`
GROUP BY `department_id`;
#2 使用
SELECT v.ag,`grade_level`
FROM myv2 v
INNER JOIN `job_grades` j ON v.ag BETWEEN `lowest_sal` AND `highest_sal`; 

#案例3 查询平均工资最低的部门信息
SELECT * FROM `departments` WHERE `department_id`  = (
SELECT `department_id` FROM myv2 WHERE ag = (SELECT MIN(ag) FROM myv2)
)

#案例4 查询平均工资最低的部门名和工资
SELECT `department_name`,ag FROM `departments` d
INNER JOIN myv2 v ON d.`department_id` = v.`department_id`
ORDER BY ag 
LIMIT 1;

1. 视图的修改

CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
#方式一：
/*
create or replace view 视图名
as
查询语句;

*/
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(`salary`),`job_id` FROM `employees` GROUP BY `job_id`;

SELECT * FROM myv3;

#方式二：
/*
语法:
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT * FROM myv3;

2. 删除视图

/*
语法： drop view 视图名, 视图名,....;
*/
DROP VIEW myv3;

3. 查看视图

DESC myv3;
SHOW CREATE VIEW myv3;

三、视图更新

/*
CREATE OR REPLACE VIEW myv3
AS
SELECT `last_name`,`email`,`salary`*12*(1+ ifnull(`commission_pct`,0)) 年薪 
from `employees`;
*/

CREATE OR REPLACE VIEW myv3
AS
SELECT `last_name`,`email`
FROM `employees`;

SELECT * FROM myv3;
#1.插入
INSERT INTO myv3 VALUES('张飞','zhangqq.com')
SELECT * FROM myv3 WHERE `last_name` LIKE '张飞';
SELECT * FROM `employees` WHERE `last_name` LIKE '张飞';

#2.修改
UPDATE myv3 SET `last_name` = '张无忌' WHERE last_name = '张飞';
SELECT * FROM `employees` WHERE `last_name` LIKE '张%';

#3.删除
DELETE FROM myv3 WHERE `last_name` LIKE '张%';
SELECT * FROM `employees` WHERE `last_name` LIKE '张%';


#具备一下特点的视图是不可以更改的
#①包含：分组函数、distinct,group by,having,unioin,union all的sql语句
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=9000 WHERE `department_id` = 10;

#②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'join' NAME;
#更新 错误
SELECT * FROM myv2;
UPDATE myv2 SET NAME='lisi';

#③SELECT中包含子查询 不能更新
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(`salary`) FROM `employees` ) 最高工资;
#不能更新
SELECT * FROM myv3;

#④join
CREATE OR REPLACE VIEW myv4
AS
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d ON d.`department_id` = e.`department_id`;
#更新（不能更新）
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '字符' WHERE last_name = 'fay';#更新成功
SELECT * FROM myv4 WHERE last_name = '字符';
#插入 失败
INSERT INTO myv4 VALUES('zs',Mar);

#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
#更新 失败
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=1000; 
#⑥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;