sql语句复习总结（视图篇）

视图有点像ps当中的图层，每一个图层可以由用户自己定义，用户可以决定这张图层中应该显示什么样的内容，在《数据库系统概论》中说，视图就像窗口，透过这个窗口可以看到数据库中用户自己感兴趣的数据或者变化，这其中有个关键的地方，就是视图是给用户“看”的，对于数据的真正操作，还是基本表，所以关于视图，首先需要明确的是视图只是一张虚表，视图只是一种定义，而没有存储数据，相对而言，数据库中真正存在的表则是基本表，基本表才真正存储数据，虚表可以从一张或者几张基本表导出，也可以从已经定义好的视图中导出，其实视图的概念不难理解，但是自己实践之后，才能真正体会视图的作用。

创建视图（单表）

原表

创建视图product_item

CREATE VIEW  product_item  AS SELECT pname,price FROM product WHERE category_id='c001';

注意，这里创建视图只是把视图的定义存入数据字典，但是并不执行其中的查询语句，也就是只是告诉数据库我定义了一个视图，仅此而已，所以该语句执行之后，没有任何表的显示。只是告诉你视图创建成功

创建视图成功之后‘’查看‘’视图

SELECT * FROM product_item;

结果如下

product_item视图

 

在视图的基础上建立视图

例如，我现在要在上面创建的视图product_item基础上创建视图product_item01，该视图负责“存储”上一张视图价格列大于2000的数据

CREATE VIEW product_item01 AS SELECT * FROM product_item WHERE price>2000;

查询product_item01视图，结果如下

product_item01视图

 

创建分组视图

CREATE VIEW avg_price(category_id,avgprice) AS  SELECT category_id, AVG(price) FROM product GROUP BY category_id;

查询该分组视图

SELECT * FROM avg_price;

查询视图的过程是视图消解的一个过程，首先数据库管理系统会判断表或者视图是否存在，如果存在，则将对视图的查询转换成对基本表的查询，所以下面的句子其实等价于下面的语句，而视图消解之后，系统真正执行的就是这个语句，这个感觉跟Java当中的泛型擦除有异曲同工之妙。

SELECT category_id,AVG(price) avgprice FROM product GROUP BY category_id;

执行之后，结果跟上面的视图查询时一样的

但是这里有个小问题，就是如果我这么查询的话，我们先看这个语句的执行结果

SELECT * FROM avg_price WHERE avgprice>3000;

结果：

看似没啥问题，但仔细想想，视图消解后真正的语句不应该是下面这样的吗

SELECT category_id,AVG(price) avgprice FROM product WHERE AVG(price)>3000 GROUP BY category_id;

可是where语句是不能跟聚集函数一起用的！(原因我在sql单表篇有说明),那为什么上面那条视图查询语句可以正常执行呢，因为数据库系统帮我们做了一件事，就是对语句进行正确转换，正确的真正在执行的查询语句是这样的。

SELECT category_id,AVG(price) avgprice FROM product  GROUP BY category_id HAVING AVG(price)>3000;

在《数据库系统概论》中有提到对保留了主键的视图这么查询可以正确转换，但是没有保留主键的视图不一定会正确转换，所以可以用子表的形式查找，但是我目前没有遇到这个情况出错，但是还是注意些的好。

 

更新视图

UPDATE product_item SET price=5500 WHERE pname='联想';

结果如下

插入数据

INSERT INTO product_item(pname,price) VALUES('雅诗兰黛护肤水',300);

这里出现了一个有意思的错误

出现这个错误的原因是原表当中的pid是不允许为空的，所以解决这个问题就是pid改成非空，但其实这么做有点舍本求末了~，这个现象也就能够说明，并不是所有的视图都是可更新的。

 

ps:视图除了简化操作之外，还有一个很有意思的作用，就是视图能够对机密的数据提供安全庇护

比如说在设计数据库应用时，对不同用户定义不同的视图，使得用户只能看到自己应该看到的数据，《数据库系统概论》中举了一个生动的例子，学生表涉及全校15个院系的学生数据，可以定义15个视图，每个视图对应一个院系的学生数据，并且只允许每个院系主任查询修改本院视图的学生数据。

本文参考资料《数据库系统概论》