SQL视图:CREATE VIEW

 
视图(View)是一个由 SELECT 查询所定义出来的虚拟表。

我们知道,SELECT 查询会产生一个包含行和列的结果集,它在结构上和真实的物理表是类似的,您可以把这个结果集看做一个临时表或者虚拟表;给结果集起一个名字,放在数据库中供大家使用,它就叫做“视图”了。

语法

在 SQL 中,您可以基于一个表、多个表或者另外一个视图来创建新的视图,被视图引用的表通常称为“基础表”。

注意,用户必须拥有足够的权限才能创建视图。

SQL 使用 CREATE VIEW 语句用来创建视图,基本的语法格式如下:

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

view_name 是视图的名字,以后可以通过 view_name 来使用此视图。您看,所谓的视图,就是给某个普通的 SELECT 查询起了一个名字,方便以后使用而已。

示例

有包含如下记录的 CUSTOMERS 表:
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

基于 CUSTOMERS 表创建一个名为 CUSTOMERS_VIEW 的视图,该视图用于在 CUSTOMERS 表中获取薪水大于 2000 的客户的 ID、姓名和年龄,具体代码如下所示:
SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT id, name, age
FROM  CUSTOMERS
WHERE SALARY > 2000;

现在,您可以像普通表一样使用 SELECT 语句来查询 CUSTOMERS_VIEW 中的数据,如下所示:
SQL > SELECT * FROM CUSTOMERS_VIEW;
执行结果:
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  4 | Chaitali |  25 |
|  5 | Hardik   |  27 |
|  6 | Komal    |  22 |
|  7 | Muffy    |  24 |
+----+----------+-----+

视图究竟是什么?

本文开头将视图看做 SELECT 查询的结果集是为了方便大家理解,因为从使用者的角度看,视图和结果集的确没有什么两样。现在让我们回归本质,看看视图究竟是不是结果集。

创建视图其实就是给 SELECT 查询起了一个名字而已,并不会真的执行 SELECT 语句,更不会产生结果集。除非您为视图添加索引(Index),否则视图就是一段存储在内存中的 SELECT 语句的文本,它没有任何数据(记录)存储到物理数据库中。只有当使用视图时,数据库引擎才会真正执行 SELECT 查询,产生一个结果集。

正因为视图没有真正的数据存储到物理数据库中,所以它才被称为虚拟表。

视图和表的主要区别在于:
  • 表占用物理存储空间,也包含真正的数据;
  • 视图不需要物理存储空间(除非您为视图添加索引),也不包含真正的数据,它只是从表中引用数据。

为什么使用视图?

使用视图有以下几点好处:
  • 简化数据访问,让复杂的 SQL 语句简单化。用户只需要对视图写简单的代码就能返回需要的数据,一些复杂的逻辑放在视图中完成。
  • 防止敏感的字段被选中,同时仍然提供对其它重要数据的访问。
  • 可以对视图添加一些额外的索引,来提高查询的效率。

视图并没有改变任何事情,也不存在任何形式的优化,只是对访问的数据进行了某种形式的筛选,这意味着在数据请求和被交付的数据之间多加了一层开销,因此视图没有直接运行底层 SELECT 语句那样快速。

视图存在的一个重要原因,就是它的安全性和它为用户所做的简化,您需要在您的需求和开销之间进行权衡,找到合适的解决方案,过多使用视图或者不使用视图都不是好的习惯。

视图的修改

用户在通过视图修改数据(INSERT、UPDATE、DELETE 操作)时,需要满足以下两点:
  • 视图既然被看做一张虚拟表,它的行为也应该和普通表类似,通过视图进行的修改,也必须能够通过该视图看到修改后的结果。
  • 视图只是基础表的一种映射,视图中的数据依赖于基础表,通过视图修改的数据必须能被基础表接受,不能被基础表接受的数据没有理由呈现到视图中。

但是在默认情况下,视图并没有第二点限制,对于视图的修改,只要满足基础表的要求即可,不用在视图中呈现出来。为了使得修改操作在视图中同步呈现,需要在创建视图时增加WITH CHECK OPTION子句。

下面我们通过举例来进一步说明。

1) 通过视图修改的数据也必须呈现到视图中

使用 INSERT 语句向视图中插入数据时,该条数据被成功插入基础表以后,也必须能够在视图中看到;这意味着,该条数据必须满足创建视图时的 WHERE 子句设定的条件。通过视图插入的数据却不能在视图中呈现,是一种奇怪的行为。

对于上面的视图 CUSTOMERS_VIEW,插入一条 SALARY 为 1800 的数据是不应该被允许的,因为视图要求 SALARY 大于 2000,该条数据被插入到基础表以后,不能出现在视图中。

使用 UPDATE 更新数据,或者使用 DELETE 删除数据时,也应该受到类似的限制。

修改上面示例中的代码,在创建 CUSTOMERS_VIEW 视图时增加 WITH CHECK OPTION 子句:
SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT id, name, age, address, salary
FROM  CUSTOMERS
WHERE SALARY > 2000
WITH CHECK OPTION;
执行完该语句,视图内容如下:
+----+----------+-----+---------+--------+
| id | name     | age | address | salary |
+----+----------+-----+---------+--------+
|  4 | Chaitali |  25 | Mumbai  |   6500 |
|  5 | Hardik   |  27 | Bhopal  |   8500 |
|  6 | Komal    |  22 | MP      |   4500 |
|  7 | Muffy    |  24 | Indore  |  10000 |
+----+----------+-----+---------+--------+

使用 INSERT 语句向视图中插入一条数据,它的 SALARY 为 3300,如下所示:
INSERT INTO CUSTOMERS_VIEW (name, age, address, salary)
VALUES ('Tom', 30, 'MP', 3300.00 );
该语句可以执行成功,在视图中也可以看到插入的数据。

如果将插入数据的 SALARY 修改为 1800,看看有什么结果呢?
INSERT INTO CUSTOMERS_VIEW (name, age, address, salary)
VALUES ('Abel', 30, 'MP', 1800.00 );
该语句将执行失败,数据库引擎将给出错误提示,类似于:

CHECK OPTION failed 'test.CUSTOMERS_VIEW'

2) 视图中的数据必须满足基础表的要求

重新创建一个 CUSTOMERS_VIEW 视图,代码如下:
SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT id, name, age, salary
FROM  CUSTOMERS
WHERE SALARY > 2000
WITH CHECK OPTION;
相比于基础表,视图没有选取 address 字段,这意味着通过视图插入数据时也无法为 address 字段赋值,此时如果基础表中的 address 字段没有默认值,那么将无法插入数据。例如:
INSERT INTO CUSTOMERS_VIEW (name, age, salary) VALUES ('Abel', 30, 2800.00 );
该语句将执行失败,数据库引擎给出错误提示,类似于:

Field of view 'test.CUSTOMERS_VIEW' underlying table doesn't have a default value

对于视图的插入,最终是对于基础表的插入,如果基础表不接受插入的数据,那么该数据在视图中也不能插入成功。

删除视图

当不需要视图时可以删除,删除视图的基本语法为:

DROP VIEW view_name;

view_name 为视图的名字。

例如,删除上面创建的 CUSTOMERS_VIEW 视图:
DROP VIEW CUSTOMERS_VIEW;

注意,删除视图仅仅是删除创建视图的 SELECT 查询语句,并不会删除基础表中的数据。

关注公众号「站长严长生」,在手机上阅读所有教程,随时随地都能学习。本公众号由站长亲自运营,长期更新,坚持原创,专注于分享创业故事+学习历程+工作记录+生活日常+编程资料。

公众号二维码
微信扫码关注公众号