第20课:灵活的视图

本节要点

视图简介
视图与表的区别
视图的使用建议
常见的8个视图使用场景

视图简介

先来看一个前面讲过的例子:

(1)、查询学生信息时,同时查询出老师姓名:

SELECT
    a.*,b.teacher_name
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id;

对于这个SQL语句,我们思考一下这样一个场景:如果有很多地方都需要按上面的逻辑查询,那么上面这个LEFT JOIN的脚本就需要写很多遍,有没有一种简写的方式呢?

这就是本节课要讲解的视图。

视图与表一样,都是数据库中非常重要的对象。但它的特性与表又有很大的区别。

那视图到底是什么呢?其实,视图就是一个结果集的定义,相当于为我们查看表中的数据打开了一扇窗户。

视图的定义,使用CREATE VIEW关键字,语法如下:

CREATE VIEW 视图名
AS
SELECT子句;

比如,我们定义一个视图,在查询学生信息时,同时查询出老师姓名:

CREATE VIEW v_student
AS
SELECT
    a.*,b.teacher_name
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id;

视图创建完成后,可以像表一样,对它进行SELECT查询,不仅可以指定查询的字段,还可以对其限定过滤条件:

SELECT * FROM v_student;
SELECT student_id,student_name,teacher_id,teacher_name from v_student;
SELECT * FROM v_student WHERE teacher_id = 'T0003';

而且,还可以基于一张已经存在的视图,创建另一张视图:

CREATE VIEW v_student_nesting
AS
SELECT * FROM v_student
WHERE score >= 60;

这样,当我们想查询考试及格的学生时,直接使用下面的SQL语句就可以了。不需要我们再次显式的写出查询条件。

SELECT * FROM v_student_nesting;

视图与表的区别

通过前面的课程,我们知道表是用来存储数据的,所以它会占用一定的物理存储空间,而且它的存在不依赖于其他表或视图。表创建完成后,我们可以对其进行增、删、改、查。

而视图不存储数据,所以不占用物理存储空间(有些数据库支持特殊的视图)。并且视图在创建时,必须来源于已经存在的表或其他视图,通常情况下,我们也只是使用视图进行查询,而不适合用来增、删、改视图中的数据(当然也有的数据库支持对视图进行增、删、改)。

所以说,虽然在SELECT查询时,表与视图基本没有什么区别,但在其他方面,这两类数据库对象的区别还是蛮大的。

常见的8个视图使用场景

那为什么还需要视图呢?

其实,需要使用视图的场景还是蛮多的。

场景一:仅提供需要的数据。

(1)、只想查询学生编号、学生姓名、分数三个字段的信息:

CREATE VIEW v_student1
AS
SELECT
    a.student_id,a.student_name,a.score
FROM student a;

那么,我们在访问视图v_student1,就只会访问到学生编号、学生姓名、分数三个字段的数据,而不会访问到多余的student表中其他字段的数据。

场景二:对特定的用户仅开放特定的数据,达到保护敏感数据的目的,提升了数据安全性;

(1)、只想将学生编号、学生姓名、分数三个字段的信息暴露给用户u_read:

GRANT SELECT ON v_student1 TO u_read@localhost;

那么,我们可以创建场景一的视图v_student1,利用视图将student表中的其他字段全部隐藏起来,然后将这个视图的访问权限开放给需要的用户,而不是直接开放students整个表的访问权限。从而达到了隐藏信息目的。

场景三:仅筛选需要的数据

(1)、只查询成绩及格的学生信息:

CREATE VIEW v_student3
AS
SELECT
    *
FROM student a
WHERE a.score >= 60;

这是之前举过的一个例子,通过视图v_student3,我们就能很轻松的访问到成绩及格的学生信息,而自动过滤掉成绩不及格的学生。

场景四:简化复杂的操作

(1)、有多个地方,都需要在查询学生信息时,同时查询出老师姓名:

CREATE VIEW v_student4
AS
SELECT
    a.*,b.teacher_name
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id;
SELECT * FROM v_student4;

通过创建上面的视图v_student4,我们可以很容易在获取学生信息的同时,获取到老师姓名的信息。

场景五:重新格式化出新的字段

(1)、查询学生出生日期,年月日单独一个字段展示:

CREATE VIEW v_student5
AS
SELECT
    a.student_id,a.student_name,
    year(a.birth_day) birth_year,
    month(a.birth_day) birth_month,
    day(a.birth_day) birth_day
FROM student a;

有时候,我们在查询学生的出生日期时,可能想将出生日期的年、月、日拆分开来,单独显示,那么我们就可以像上面这样创建一个视图。

注意,这里使用函数对原有字段进行拆分后,必须为拆分后的字段取一个别名,否则视图创建就会报错。

场景六:使用计算表达式生成新的字段

(1)、考试总分100分,查询所有学生做错的题目的分数:

CREATE VIEW v_student6
AS
SELECT
    a.student_id,a.student_name,
    a.score,100 - a.score as 'wrong_score'
FROM student a;

因为考试总分是100分,那么我们可以通过100-score计算出每个学生做错的题目所占的分数,从而生成一个新的字段wrong_score。

注意,这里也必须像场景五的例子中一样,为100 - a.score这个表达式取一个字段别名。

场景七:屏蔽底层实现逻辑及频繁的变更

(1)、考试总分150分,查询所有学生做错的题目的分数:

CREATE VIEW v_student7
AS
SELECT
    a.student_id,a.student_name,
    a.score,150 - a.score as 'wrong_score'
FROM student a;

针对场景六创建的视图v_student6,其中计算wrong_score的值时,使用的表达式是100-score,这是因为考试的总分是100分,可如果考试的总分变为150分了呢?

这时候,我们像视图v_student7的定义一样,直接修改100-score这个表达式为150-score就可以了。

其他使用到这个视图的地方都不需要修改,从而向下游屏蔽了底层实现逻辑的变动。

场景八:合并多个分离的子表

(1)、假如有3个学校,每个学校的学生数据在各自的表中,如何一次性查询所有学生的信息:

CREATE VIEW v_student8
AS
SELECT * FROM student1
UNION ALL
SELECT * FROM student2
UNION ALL
SELECT * FROM student3;

通过创建视图v_student8,我们可以一次性访问3个学校的所有学生数据。

视图的使用建议

这么看下来,是不是视图的作用还是不可小觑的呢?

但是,我在这里给大家一个忠告:慎用视图!

为什么呢?

因为,视图通常是由多张表,通过复杂的关联,甚至一层层视图的嵌套生成的。这样的视图,会导致查询时执行计划不可控,最直接的表现就是,查询性能直线下降。

所以,在使用视图时,一定要慎重考虑,是不是真的必须要使用视图。

课后习题:

1、对于第6课课后习题中的论坛注册用户表、第15课课后习题中的文章表、用户访问的文章记录表,请编写SQL完成以下查询。

(1)、创建视图,实现查看每篇文章每个月的访问人数及访问次数。

(2)、创建视图,实现查看每个用户每个月的访问文章数及访问次数。

(3)、创建视图,实现查看每个省、市、区的注册用户数及用户最近一次注册时间。

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。

Captcha Code