【数据库学习】17 视图

含义

是虚拟表，和普通表一样使用，它与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

例如：一个年级有22个班级，有一天领导要来视察学校的情况，领导比较喜欢精英教育，如实为了应付领导的视察，学校从22个班级里临时抽调出一部分人组成一个新的临时班级23班，领导走之后这个班级就解散。那么原来的22个班级就是我们普通的表，而23班就是视图，虚拟表，一个临时生成的表。

应用场景

多个地方需要用到相同的查询结果该查询结果使用的SQL语句较为复杂

案例

案例1：

SELECT last_name,department_name FROM employees e,departments d WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;

上面这段用来查询奖金非空(有奖金)的员工名和部门名。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。现在加入将整个查询封装成一个名为departmentid的虚拟表，可以使用有如下的命令轻松检索：

SELECT last_name,department_name FROM departmentid WHERE e.`commission_pct` IS NOT NULL;

这就是视图的作用。

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

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`;

后面再进行查询姓张的学生名和专业名就可以这样：

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

创建视图

create view 视图名 as 查询语句;

 1.查询姓名中包含a字符的员工名、部门名和工种信息

先创建视图：

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;

 再使用视图：

SELECT * FROM myv1 WHERE last_name LIKE '%a%' ;

 2.查询各部门的平均工资级别

先创建视图查看每个部门的平均工资：

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

然后从中进行查询级别：

SELECT myv2.`ag`,g.grade_level FROM myv2 JOIN job_grades g ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

3.查询平均工资最低的部门信息

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;

 方式二：

alter view 视图名 as  查询语句;

例如：

ALTER VIEW myv3 AS SELECT * FROM employees;

 删除视图

语法：drop view 视图名,视图名,...;

 查看视图

DESC 视图名;

 视图的更新(很少用)

我们新建一个视图，然后对它进行修改：

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

查看一下新建的视图：

SELECT * FROM myv1;

1.在视图中插入

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

 然后再次查询发现视图就增加了一行：

2.在视图中修改

UPDATE myv1 SET last_name = 'Lisi' WHERE last_name = 'Zhangsan' ;

 3.在视图中删除

DELETE FROM myv1 WHERE last_name = 'Lisi';

在视图中的增删改也会影响到原始表的数据！

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

常量视图joinSelect中包含子查询from一个不能更新的视图where子句的子查询引用了from子句中的表包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all