MySQL——视图

---

前言

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

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

案例：查询姓张的学生名和和专业名

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 
WHERE
	s.stuName LIKE '张%';
	
SELECT * FROM v1 WHERE stuName LIKE '张%';

---

一、创建视图

1.语法

CREATE VIEW 视图名 AS 查询语句;

2.案例

#案例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 e.job_id = j.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
	INNER 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.*,
	myv3.ag 
FROM
	myv3
	INNER JOIN departments d ON myv3.department_id = d.department_id;

3.视图的好处

• 重用SQL语句
• 简化了复杂的SQL操作，不必知道他的查询细节
• 保护数据，提高安全性

二、修改视图

1.方式一

CREATE OR REPLACE VIEW 视图名 AS 查询语句;

案例1：

SELECT * FROM myv3;

#改为每个工种的平均工资 
CREATE 
	OR REPLACE VIEW myv3 AS SELECT
	AVG( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id;

2.方式二

ALTER VIEW 视图名 AS 查询语句;

案例2：

ALTER VIEW myv3 AS SELECT * FROM employees;

三、删除视图

1.语法

DROP VIEW 视图名,视图名,...;

2.案例

DROP VIEW myv1,myv2,myv3;

四、查看视图

1.方式一

DESC myv3;

2.方式二

SHOW CREATE VIEW myv3;

五、案例

案例一：创建视图emp_v1，要求查询电话号码以’011’开头的员工姓名和工资、邮箱

CREATE 
	OR REPLACE VIEW emp_v1 AS SELECT
	last_name,
	salary,
	email 
FROM
	employees 
WHERE
	phone_number LIKE '011%';

案例二：创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

#方式一：
#①查询部门的最高工资高于12000的部门id
SELECT
	MAX( salary ) mx,
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	mx > 12000;
#②在①基础上查询部门信息 
SELECT
	d.*,
	m.mx 
FROM
	departments d
	INNER JOIN (
	SELECT
		MAX( salary ) mx,
		department_id 
	FROM
		employees 
	GROUP BY
		department_id 
	HAVING
		mx > 12000 
	) m ON d.department_id = m.department_id;

#方式二：视图
CREATE 
	OR REPLACE VIEW emp_v2 AS SELECT
	MAX( salary ) mx,
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	mx > 12000;
	
SELECT
	d.*,
	m.mx 
FROM
	departments d
	INNER JOIN emp_v2 m ON d.department_id = m.department_id;

六、视图的更新

CREATE 
	OR REPLACE VIEW myv1 AS SELECT
	last_name,
	email,
	salary * 12 *(
	1+IFNULL ( commission_pct, 0 )) "annual salary" 
FROM
	employees;

SELECT * FROM myv1;

1.插入

INSERT INTO myv1 VALUES ('张飞','zf@qq.com');

2.修改

UPDATE myv1 
SET last_name='张无忌' WHERE last_name='张飞';

3.删除

DELETE FROM myv1 WHERE last_name = '张无忌';

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

1. 包含以下关键字的SQL语句：分组函数、ditinct、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 = 9000 WHERE department_id = 10;

2. 常量视图

CREATE 
	OR REPLACE VIEW myv2 AS SELECT
	'john' NAME;
	
SELECT * FROM myv2;

UPDATE myv2 SET NAME = 'lucy';

3. select包含子查询

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

UPDATE myv3 
SET 最高工资=10000;

4. join
5. from一个不能更新的视图
6. where子句的子查询引用了from子句中的表

5.视图和表的对比

	创建语法关键字	是否实际占用物理空间	使用
视图	create view	只是保存了SQL逻辑	增删改查，但是一般不能增删改
表	create table	保存了数据	增删改查

6.delete和truncate在事务使用时的区别

• delete支持回滚，truncate不支持