MySQL数据库12——视图（VIEW）

视图概念

视图是一个虚拟表，称其为虚拟表的原因是：视图内的数据并不属于视图本身，而属于创建视图时用到的基本表。可以认为，视图是一个表中的数据经过某种筛选后的显示方式；或者多个表中的数据经过连接筛选后的显示方式。

视图由一个预定义的查询（SELECT语句）组成，可以像基本表一样用于SELECT语句中。如果视图满足一定条件，还可以用在INSERT、UPDATE和DELETE语句中，对视图所调用的基本表进行插入、更新和删除数据操作。

---

视图案例

使用一个例题引入视图概念，并让读者初步了解视图的作用、定义视图的方法和使用视图的方法。

例 查询“心理学”考试成绩大于等于90的学生的“学号”、“姓名”和“所属院系”三个字段。

分析：“心理学”是course表中“课名”字段的值，考试成绩是score表中“考试成绩”字段的值，而“学号”、“姓名”和“院系”是student表中的字段。因此想要得到本例要求的结果，则必须对course、score和student三个表进行连接查询，如图 所示。

SELECT student.ID AS 学号, student.name 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.name  , 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语句，所以导致了系统性能下降。

---

视图的规则和限制

不同的数据库系统，对创建视图有不同的限制，所以在创建使用视图之前，应当查看具体数据库系统的帮助文档。下面整理了创建使用视图的一些较常见的规则和限制。

1.视图必须惟一命名。视图的名称不能和本数据库中的其它视图或者基本表名相同。

2.对于视图的创建个数没有限制。

3.为了创建视图，必须具有足够的访问权限。权限可以由数据库管理人员授予。

4.视图可以嵌套，即定义视图时的表源也是一个视图而并非基本表。对于嵌套的层数，不同的数据库系统有不同的规定，详细内容可以查看具体数据库系统的帮助文档。

5.有些数据库系统不允许在定义视图时直接使用ORDER BY子句，例如SQL Server，Oracle 8i及其以前的版本。

6.视图不能索引，也不能有相关联的触发器或默认值。

7.有些数据库系统把视图作为只读的查询，只能从视图查询数据，而不能将更改基本表数据。

---

创建视图的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 学号, student.name 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