视图概念
视图是一个虚拟表,称其为虚拟表的原因是:视图内的数据并不属于视图本身,而属于创建视图时用到的基本表。可以认为,视图是一个表中的数据经过某种筛选后的显示方式;或者多个表中的数据经过连接筛选后的显示方式。
视图由一个预定义的查询(SELECT语句)组成,可以像基本表一样用于SELECT语句中。如果视图满足一定条件,还可以用在INSERT、UPDATE和DELETE语句中,对视图所调用的基本表进行插入、更新和删除数据操作。
视图案例
使用一个例题引入视图概念,并让读者初步了解视图的作用、定义视图的方法和使用视图的方法。
例 查询“心理学”考试成绩大于等于90的学生的“学号”、“姓名”和“所属院系”三个字段。
分析:“心理学”是course表中“课名”字段的值,考试成绩是score表中“考试成绩”字段的值,而“学号”、“姓名”和“院系”是student表中的字段。因此想要得到本例要求的结果,则必须对course、score和student三个表进行连接查询,如图 所示。
SELECT student.ID AS 学号, AS 姓名, student.institute AS 所属院系
FROM student, course, score
WHERE course.course='心理学'
AND score.result1>90 AND student.ID=score.s_id AND course.ID= score.c_id;
如果用户经常使用上面的查询,并且每次都要编写这一复杂的SELECT语句,那会很麻烦。如果将上面的SELECT语句保存到数据库里,就是视图。
视图里存放了SELECT语句,而并非是查询结果。每次在SQL语句中使用视图,其实就是在执行视图内存放的SELECT语句,因此通过视图总能够得到最新的数据。
例如 定义一个视图vw1,将上例的SELECT语句存放到该视图内。
CREATE VIEW vw1 AS SELECT student.ID , , student.institute
FROM student,course,score WHERE course.course='心理学'
AND score.result1>90 AND student.ID=score.s_id AND course.ID= score.c_id;
视图被定义后可以像基本表一样使用。例如,下面的例题在SELECT语句中使用了视图vw1。
例 在视图vw1上运行一个简单查询。
SELECT * FROM college.vw1;
使用视图的原因:
(1)能够简化用户的操作
(2)能使用户以多种角度观察同一个数据库
(3)视图对重构数据库提供了一定程度的逻辑独立性
(4)能够对机密数据提供安全保护
因为上述原因,数据库操作中经常使用视图。但使用视图为人们带来好处的同时,也带来了一些隐患。因此使用视图前,应当注意以下问题。
(1)改变基本表的结构后应当删除视图并重建视图。视图不能被修改,因此如果要对视图定义进行改变需要先删除再重建它。
(2)删除基本表时应当删除视图。视图本身没有数据,其数据都是基本表中的数据,当删除了基本表后,再运行视图时会产生错误信息。
(3)潜在的复杂性带来的性能下降问题。如果定义视图的SELECT语句非常复杂,例如连接了多个表或者嵌套了视图,则数据库系统除了执行访问视图的SELECT语句以外,还要执行定义视图的复杂SELECT语句,所以导致了系统性能下降。
视图的规则和限制
不同的数据库系统,对创建视图有不同的限制,所以在创建使用视图之前,应当查看具体数据库系统的帮助文档。下面整理了创建使用视图的一些较常见的规则和限制。
创建视图的SQL语句
下面通过例题,具体说明创建视图的SQL语句的简单用法。
例 创建视图vw_ boy,它用于将表student中全部男生的信息显示出来。并使用视图vw_boy查询计科系的男生。
CREATE VIEW vw_boy AS
SELECT * FROM student
WHERE sex='男';
下面的语句用来显示计科系的男生。
SELECT * FROM vw_boy
WHERE institute='计科系';
例 创建一个基于视图vw_boy的视图vw_boy_computer,用于查询计科系男生的信息。
CREATE VIEW vw_boy_computer AS
SELECT * FROM vw_boy
WHERE institute='计科系';
利用视图提高数据安全性
1、隐藏列数据
有时需要将表中的某些列隐藏起来,只显示指定的列,这时可以使用视图达到这种目的。具体方法如下面的例题所示。
例 创建一个只能查看“学号”、“姓名”和“性别”三个列的视图vw_student1。
CREATE VIEW vw_student1 AS
SELECT ID AS 学号, name AS 姓名, sex AS 性别
FROM student;
2、隐藏行数据
下面学习通过视图只显示指定条件的行数据,而隐藏其它数据的方法。
例 创建一个只能查看计科系学生信息的视图vw_student2。
CREATE VIEW vw_student2 AS
SELECT * FROM student
WHERE institute='计科系';
利用视图得到汇总数据
可以使用视图对表中的数据进行及时汇总。这样通过对视图进行简单查询就可以得到复杂的汇总数据。而且,当基本表中的底层数据被改变时,通过视图得到的永远是最新的数据。
例 创建一个视图vw_student3,显示每个不同院系的学生人数。
CREATE VIEW vw_student3 AS
SELECT institute AS 所属院系, COUNT(*) AS 人数
FROM student GROUP BY institute;
使用视图查询汇总数据时,即使基本表中的数据改变了,视图也总能得到最新的信息,因为它每次都运行定义该视图的SELECT语句,而并非是将以前的查询结果显示出来。
利用视图简化计算字段的使用
使用视图还可以简化计算字段的使用。下面通过一个例题进行详细说明。
例 查询25岁~30岁之间(包含25岁,不包含30岁)的学生和33岁以上(包含33岁)的学生的姓名和年龄,并按年龄降序排序。
在student表中只有“出生日期”列,而并没有“年龄”列,所以必须通过对“出生日期”列进行计算后得出学生的“年龄”。下面先创建一个有年龄列的视图,然后对视图进行简单查询。
CREATE VIEW vw_age AS
SELECT name AS 姓名, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS 年龄
FROM student;
查询:
SELECT * FROM vw_age
WHERE 年龄>20 or 年龄<=18
ORDER BY 年龄 DESC;
本例中,如果不使用视图,则其查询语句不仅会显得很复杂,而且键盘输入量也会大大增加,例如下面的语句。
SELECT name AS 姓名,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS 年龄
FROM student
WHERE TIMESTAMPDIFF(YEAR, birthday, CURDATE())>=20
OR TIMESTAMPDIFF(YEAR, birthday, CURDATE())<=18
ORDER BY 年龄 DESC;
利用视图简化多表连接
例 查询“教育学”考试成绩大于80的学生的“学号”、“姓名”和“所属院系”三个列。
为了让视图更具通用性,在此创建一个只是连接三个基本表相关列数据的视图。
CREATE VIEW vw_student_score AS
SELECT student.ID AS 学号, AS 姓名, student.institute AS 所属院系,
course.course AS 课名,score.result1 AS 考试成绩
FROM student, course, score
WHERE student.ID=score.s_id AND course.ID= score.c_id ;
然后查询80分之上的:
SELECT 学号,姓名,所属院系
FROM vw_student_score
WHERE 课名='教育学' AND 考试成绩>80;
然后需要查看所有学生的平均考试成绩,查询结果按“学号”升序排序。
SELECT 学号,姓名, AVG(考试成绩) AS 平均考试成绩
FROM vw_student_score
GROUP BY 学号,姓名
ORDER BY 平均考试成绩;
视图删除
例 从数据库中删除视图vw1。
DROP VIEW vw1