MySQL学习笔记（八）-视图

作者：就叫易易好了
日期：2020/11/25

视图就是一种虚拟表，和普通表一样使用，是通过表动态生成的数据。视图的数据来自于表，只保存了sql的逻辑，不保存查询结果，不占储存空间。

一、创建视图

语法：create view 视图名

as 查询语句

使用myemployees数据库

一旦创建了一个视图，在后面需要多次使用的时候，直接调用就可以了，这样就减少了代码冗余。

#查询邮箱中包含a字符的员工名、部门名和工种信息
1、创建视图
CREATE VIEW myv1
AS 
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
2、使用视图
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#案例：查询各部门的平均工资级别
1、
CREATE VIEW myv2 
	AS
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id;
2、
SELECT myv2.`ag`,g.grade_level
	FROM myv2
	JOIN job_grades g
	ON muv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#案例：查询平均工资最低的部门信息
平均工资已经在上一个题里面创建视图了
SELECT * from myv2 ORDER BY ag LIMIT 1;
#案例：查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;

视图的好处：

  • 重用sql语句
  • 简化复杂的sql操作，不必知道它的查询细节
  • 保护数据，提高安全性，比如学校要在官网公布教师信息，只能对外提供教师姓名，照片，而身份证号和电话号码等不应该提供，此时就应该使用视图的方法来提高安全性

二、修改视图

方式一：

create or replace view 视图名

as

查询语句；

方式二：

alter viwe 视图名

as

查询语句

那么现在要改一下myv3，改为查询工种的平均工资

方式一：

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

方式二：

ALTER VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

三、删除视图

语法：

DROP VIEW 视图名，视图名……

现在我要删除刚才创建的三个视图

DROP VIEW myv1,myv2,myv3;

哦豁

四、查看视图

假如我要查看视图myview（假装有这个视图）

可以用这个语句：

DESC myview;

也可以用：

SHOW CREATE VIEW myview;

五、视图更新

先创建一个视图

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

1、插入

INSERT INTO myv1 VALUES('马保国','baoguo@qq.com');

同时，在原始表也出现了马保国。

2、修改

UPDATE myv1 SET last_name = '梁非凡' 
WHERE last_name='马保国';

3、删除

DELETE FROM myv1 WHERE last_name='梁非凡';

以上三个语句具有的应用场景并不多，简单的视图支持增删改，但一般我们要为视图增加权限。

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

  • 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all

我们先来创建一个简单的视图：

CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id;

如果我要对这个视图进行更新：

UPDATE myv1 SET m=9000 WHERE department_id=10;

就会出现这么一个错误：

The target table myv1 of the UPDATE is not updatable

也就是说，这个视图不允许更新，有GROUP BY这个关键词。

  • select中包含子查询
CREATE OR REPLACE VIEW myv3
AS 
SELECT (SELECT MAX(salary) FROM employees) 最高工资;

那现在我要篡改最高工资的值：

UPDATE myv3 SET 最高工资=10000;

依然会报错，不允许更新。