第六章 视图—虚拟存在的表

视图的介绍

含义：
MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的， 只保存了sql逻辑，不保存查询结果。（临时存在的）

应用场景：

• 多个地方用到同样的查询结果
• 该查询结果使用的sql语句较复杂

视图的创建和使用

#查询姓张的学生名和专业名
#传统方法
SELECT `stuname`,`majorID`
FROM `stuinfo` s
INNER JOIN `major` m
ON s.`majorID`=m.`id`
WHERE s.`stuname` LIKE '张%';

#视图法
#1、封装
CREATE VIEW v1
AS
SELECT `stuname`,`majorID`
FROM `stuinfo` s
INNER JOIN `major` m
ON s.`majorID`=m.`id`;
#2、调用
SELECT * FROM v1 WHERE `stuname` LIKE '张%';

CREATE VIEW v2
AS
SELECT `stuname`,`majorID`
FROM `stuinfo` s
INNER JOIN `major` m
ON s.`majorID`=m.`id`
WHERE s.`stuname` LIKE '张%';

SELECT * FROM v2;

#查询姓名中包含a字符的员工名、部门名和工种信息
#1、封装
CREATE VIEW v1
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 v1 WHERE last_name LIKE '%a%';

#查询各部门的平均工资等级
#1、创建视图查看每个部门的平均工资
CREATE VIEW v2
AS
SELECT AVG(salary) ag,`department_id`
FROM `employees` 
GROUP BY `department_id`;

#2、调用
SELECT v2.ag,g.`grade_level`
FROM v2 
JOIN `job_grades` g
ON v2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#查询平均工资最低的部门信息
#1、创建视图查看每个部门的平均工资
CREATE VIEW v2
AS
SELECT AVG(salary) ag,`department_id`
FROM `employees` 
GROUP BY `department_id`;

#2、调用
SELECT * FROM v2 ORDER BY ag LIMIT 1;

#查询平均工资最低的部门名和平均工资
#1、创建视图查看每个部门的平均工资
CREATE VIEW v2
AS
SELECT AVG(salary) ag,`department_id`
FROM `employees` 
GROUP BY `department_id`;

#2、查询平均工资最低的部门信息
CREATE VIEW v3
AS
SELECT * FROM v2 ORDER BY ag LIMIT 1;

#3、调用
SELECT d.`department_name`,v3.`ag`
FROM v3
JOIN `departments` d
ON v3.`department_id`=d.`department_id`;

视图的修改

方法1

/*
create or replace view 视图名
as
查询语句
*/
CREATE OR REPLACE VIEW v1
AS
SELECT AVG(salary),job_id
FROM `employees`
GROUP BY `job_id`;

SELECT * FROM v1;

方法2

/*
alter view 视图名
as
查询语句
*/
ALTER VIEW v1
AS
SELECT *
FROM `employees`;

SELECT * FROM v1;

视图的删除和查看

/*
支持多个删除
drop view 视图名,视图名。。。。
*/
DROP VIEW v1,v2;

/*
查看
drop view 视图名,视图名。。。。
*/
SELECT * FROM `v3`;
DESC v3;
SHOW CREATE VIEW v3;  #适合在命令提示符中运行

视图的更新

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

SELECT * FROM v3;
#1、插入（原始表employees和v3都有张飞）
INSERT INTO v3 VALUES('张飞','af@qq.com');
#2、修改（原始表和v3都改变）
UPDATE v3 SET last_name='张飞' WHERE last_name='张无忌';
#3、删除（原始表和v3都改变）
DELETE FROM v3 WHERE last_name='张无忌';

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。
• 包含以下关键字的sql语句：分组函数、 distinct、 group by、 having、 union或者union all

CREATE OR REPLACE VIEW v3
AS
SELECT MAX(salary) m,`department_id`
FROM `employees`
GROUP BY `department_id`;
SELECT * FROM v3;
#更新（出错，有分组函数）
UPDATE v3 SET m=9000 WHERE `department_id`=10;

• 常量视图

CREATE OR REPLACE VIEW v2
AS
SELECT 'john' NAME;

SELECT * FROM v2;
#更新（出错）
UPDATE v2 SET NAME='lucky';

• Select中包含子查询

CREATE OR REPLACE VIEW v1
AS
SELECT (
SELECT MAX(salary) FROM `employees`
)最高工资;

SELECT * FROM v1;
#更新（出错，类似于常量视图）
UPDATE v1 SET 最高工资=100000;

• join（能update，不能insert）

• from一个不能更新的视图

• where子句的子查询引用了from子句中的表

视图 VS 表

	创建语法的关键字	是否占用物理内存	使用
视图	create view	只是保存了sql逻辑，几乎不占用	增删改查，一般只能查
表	create table	保存了数据	增删改查

delete 和 truncate在视图中的区别

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

SELECT * FROM account;

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

SELECT * FROM account;