SQL Sever 学习笔记八——视图、子查询

视图

定义

视图是一张虚拟表，保存的是select语句；

从视图中读取数据时，视图会在内部执行该select语句并创建出一张临时表。

优点

• 提高了重用性，简化了用户的操作，就像一个函数。也就是说，重复同一个操作时，只需调用视图，不用再重复写相同的select语句了。
• 视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。
• 节省存储设备的容量。因为使用视图时并不会将数据保存到存储设备以及其他任何地方。而我们在创建表时，会通过insert语句将数据保存到数据库之中，数据库中的数据实际上会被保存到存储设备上。
• 视图中的数据随着原表内容的更新而更新

创建视图

语法：CREATE VIEW语句

create view 视图名称（视图列名1，视图列名2，...)
as 
<select 语句>

演示：
如创建ProductSum:商品种类合计视图

create view ProductSum(product_type,cnt_product)
as 
select Product_type,count(*)
from Product group by product_type;

注意：

• 定义视图时，不能使用ORDER BY子句
• 应该避免在视图的基础上创建视图，多重视图会降低SQL性能：

使用视图

使用视图和使用表完全一样，只需要把视图当成一张表就OK了。视图是一张虚拟表。
如：获取根据商品种类汇总的商品数量

select * from ProductSum;

结果：

更新视图

满足以下条件时视图可以更新，且对应的表也会更新：

①SELECT子句中未使用DISTINCT；
②FROM子句中只有一张表；
③未使用GROUP BY子句；
④未使用HAVING子句；
⑤表格中除插入的值以外的值没有not null约束等。

比如创建一个可以更新的视图：

create view ProductJim (product_id,product_name,product_type,sale_price,purchase_price,regist_date)
as select * from Product where product_type='办公用品';

既没有聚合有没有结合的select语句，则这个视图就可以更新。

更新视图，插入一条数据

insert into ProductJim values('0009','印章','办公用品',95,10,'2009-11-30');

查看以下视图，原表，可以发现同时更新了


删除视图

语法：DROP VIEW 语句

drop view 视图名称（视图列名1，视图列名2，...)

演示：
删除视图ProductSum

drop view ProductSum;

子查询

定义

子查询就是一次性视图（select语句），就是将用来定义视图的select语句直接用于from子句当中。是嵌套在另一个语句(如：SELECT，INSERT，UPDATE或DELETE)中的查询。

特点

与视图不同，子查询在select语句执行完毕之后就会消失。

使用

如用子查询的方法来创建第一节中的productsum视图：

select product_type,cnt_product 
from (select Product_type,count(*) as cnt_product
from Product group by product_type) as ProductSum;

结果与上一小节的一样。

嵌套子查询

嵌套子查询就是增加子查询的层数：子查询的from子句中还可以继续使用子查询。
如：

select product_type,cnt_product 
from (select *
from (select Product_type,count(*) as cnt_product
from Product 
group by product_type) as ProductSum where cnt_product=4) as ProductSum2; 

子查询作为内层会先执行，但是注意要尽量避免使用多层嵌套的子查询。

标量子查询

标量子查询就是返回单一值的子查询。

标量子查询的限制：必须而且只能返回1行1列的结果

就是这样的特殊性，则标量子查询可用在比较运算符之中。能够使用常数或者列名的地方，无论是SELECT语句、GROUP BY子句、HAVING子句，还是ORDER BY子句，几乎所有的地方都可以使用

1、在where子句中使用标量子查询
选取出销售单价高于所有商品平均价的商品

select product_id,product_name,sale_price from Product
where sale_price>(select avg(sale_price) from Product);

2、在select子句中使用标量子查询
加入全部商品的平均单价

select product_id,product_name,sale_price,
(select avg(sale_price) from Product) as avg_price
from Product;

3、在having子句中使用标量子查询

选取出按照商品种类计算出的销售单价高于全部商品的平均销售单价的商品种类

select product_type,avg(sale_price) from Product group by product_type
having avg(sale_price)>(select avg(sale_price) from Product);

关联子查询

在细分的组内进行比较时可以使用，比如找出各种商品种类中高于该类平均售价的商品，如有三类商品就要返回3个值，因此使用上一节的标量子查询就不行了。

如：
选出不同种类商品中其中高于该类商品的销售均价的商品，由于其中有不同的种类，肯定会返回大于1个的值，故不能使用标量子查询，只能使用关联子查询

select product_type,product_name,sale_price from Product as P1
where sale_price>(select avg(sale_price) from Product as P2
where P1.product_type=P2.product_type group by product_type);

该条语句中，先执行内层select语句，p2视图呈现每一组类别的平均售价，一次返回一条（类）结果；

在子查询中添加的WHERE子句‘where P1.product_type=P2.product_type group by product_type’的条件意思就是在同一商品种类中对各商品的销售单价和平均单价进行比较；

然后外层select语句在该类的商品中逐个比较并呈现，如此往复。