MySQL学习笔记之视图的创建、修改、删除、查看和更新以及视图与表的区别

前言：视图的含义与用途

• 视图的定义：

  视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，有点类似高级
  编程语言中的函数，他实现对查询步骤的封装，可以很好的进行复用SQL语句

• 视图的好处：

  1、重用SQL语句
  2、简化复杂的SQL操作,不必知道他的查询细节
  3、保护数据等

一、创建视图

CREATE VIEW 视图名 AS 查询语句...

案例1 创建视图并利用该视图查询员工中名字带有a的员工和员工的部门名字

USE myemployees;
CREATE VIEW v1 AS
SELECT e.last_name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM v1;

案例2 查询邮箱中包含a字符的员工名、部门名和工种信息

CREATE VIEW v2 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 v2 WHERE last_name LIKE '%a%';

案例3 查询各部门的平均工资级别

CREATE VIEW v3 AS
SELECT AVG(salary) ag,department_id 
FROM employees e
GROUP BY department_id;

SELECT ag,department_id,grade_level
FROM v3 v
INNER JOIN job_grades j ON v.`ag` BETWEEN j.`lowest_sal` AND j.`highest_sal`; 

案例4 查询平均工资最低的部门信息

CREATE VIEW v4 AS 
SELECT * FROM v3 ORDER BY v3.`ag` LIMIT 1;

SELECT d.* ,v.*
FROM departments d
INNER JOIN v4 v ON v.department_id = d.`department_id`;

二、视图的修改

2.1 方式一

语法：

create or replace view 视图名 AS 查询语句; 

案例1

CREATE OR REPLACE VIEW v4 AS
SELECT v3.`ag` FROM v3;

2.2 方式二

语法：

alter view 视图名 AS  查询语句;

案例2

ALTER VIEW v4  AS SELECT * FROM v3;

三、删除视图

语法：

drop view 视图名，视图名，...

案例3：删除视图v3 v4

DROP VIEW v3,v4;

四、查看视图

语法：

desc 视图名; --方式一
show create view 视图名; --方式二

案例 4：

DESC v2;
SHOW CREATE VIEW v2;

五、视图的更新，通过视图的更新来更新背后的表

5.1 插入视图

INSERT INTO 视图名 VALUES(....);

5.2.修改

UPDATE 视图名 SET 字段名 = 值

5.3删除

DELETE FROM 视图名 WHERE 筛选条件...

5.4视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的

1、包含以下关键字的sql语句：分组函数（max min avg…）、distinct、group by、having、union、union all
2、常量视图
3、select 中包含子查询
4、含有join 多表连接
5、from 一个不能更新的视图
6、where子句的子查询应用了 from 子句中的表 如下案例1

案例1

CREATE OR REPLACE VIEW my_v1
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
	SELECT manager_id 
	FROM employees
	WHERE manager_id IS NOT NULL
);
 -- 因为where子句中的查询引用了employees，所以下面的更新语句将无法执行
 UPDATE my_v1 SET last_name = 'hello' WHERE last_name = 'K_ing'; -- 执行失败

六、视图和表的区别

	创建语法和关键字	是否实际占用了物理空间	应用场景
视图	create view 视图名 as..	只保存了sql逻辑	查，一般情况不能增删改
表	create table 表名(...);	保存了实际数据	增删改查