SQL：五复杂查询

视图

视图和表

从SQL的角度来看，视图和表是相同的，表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。
视图的优点
- 第一点是由于视图无需保存数据，因此可以节省存储设备的容量
- 第二个优点就是可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了

创建视图的方法

-- 创建视图的 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;
-- 视图定义中的主体（内容 只是一条SELECT语句）

-- 使用视图
SELECT product_type, cnt_product  
FROM ProductSum;

SELECT 语句中列的排列顺序和视图中列的排列顺序相同
视图和表一样，可以书写在 SELECT 语句的 FROM 子句之中
使用视图的查询
- 在FROM 子句中使用视图的查询，通常有如下两个步骤
  - ①首先执行定义视图的 SELECT 语句
  - ②根据得到的结果，再执行在 FROM 子句中使用视图的 SELECT 语句
- 应该尽量避免在视图的基础上创建视图。这是因为对多数DBMS来说，多重视图会降低SQL的性能
- ```
-- 创建多重视图 ProductSumJim 
CREATE VIEW ProductSumJim (product_type, cnt_product) 
AS 
SELECT product_type, cnt_product  
FROM ProductSum 
WHERE product_type = '办公用品'; 
```

视图的限制

① 定义视图时不能使用ORDER BY子句

数据行都是没有顺序的

-- 不能像这样定义视图，定义视图时不能使用ORDER BY子句 
CREATE VIEW ProductSum (product_type, cnt_product) 
AS 
SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type 
ORDER BY product_type;

② 对视图进行更新
- 如果定义视图的 SELECT 语句能够满足某些条件，那么这个视图就可以被更新
  - ① SELECT 子句中未使用 DISTINCT
  - ② FROM 子句中只有一张表
  - ③ 未使用 GROUP BY 子句
  - ④ 未使用 HAVING 子句
- 如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了，从而无法更新
- 视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。
- ```
-- 可以更新的视图 
CREATE VIEW ProductJim (product_id, product_name, product_type, 
 sale_price, purchase_price, regist_date) 
AS 
SELECT *  
FROM Product 
WHERE product_type = '办公用品';

-- 向视图中添加数据行 
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30'); 
```

删除视图

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

-- 删除视图 
DROP VIEW ProductSum;

子查询

子查询和视图

子查询的特点概括起来就是一张一次性视图
子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中
子查询作为内层查询会首先执行
增加子查询的层数
- 子查询的 FROM 子句中还可以继续使用子查询，该子查询的 FROM 子句中还可以再使用子查询……
随着子查询嵌套层数的增加，SQL 语句会变得越来越难读懂，性能也会越来越差

-- 根据商品种类统计商品数量的视图 
CREATE VIEW ProductSum (product_type, cnt_product) 
AS 
SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type;

-- 在FROM子句中直接书写定义视图的SELECT语句 
SELECT product_type, cnt_product  
FROM ( SELECT product_type, COUNT(*) 
　　　　AS cnt_product FROM Product GROUP BY product_type ) AS ProductSum;

子查询的名称

为子查询设定名称时需要使用 AS 关键字，该关键字有时也可以省略

标量子查询

什么是标量
- 标量就是单一的意思
- 标量子查询则有一个特殊的限制，那就是必须而且只能返回1行1 列的结果，也就是返回表中某一行的某一列的值，例如“10”或者“东京都” 这样的值。
- 由于返回的是单一的值，因此标量子查询的返回值可以用在 =或者 <> 这样需要单一值的比较运算符之中

在WHERE子句中使用标量子查询

SELECT语句的执行顺序（标量子查询）：内层子查询 => 将结果带入到外层的查询

-- 在WHERE子句中不能使用聚合函数 
SELECT product_id, product＿name, sale_price  
FROM Product 
WHERE sale_price > AVG(sale_price);

-- 计算平均销售单价的标量子查询 
SELECT AVG(sale_price)  
FROM Product;

-- 选取出销售单价（sale_price）高于全部商品的平均单价的商品 
SELECT product_id, product_name, sale_price
FROM Product 
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用
能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用

-- 在SELECT子句中使用标量子查询 
SELECT product_id, product_name, sale_price, (SELECT AVG(sale_price)FROM Product) AS avg_price  
FROM Product;

-- 在HAVING子句中使用标量子查询 
SELECT product_type, AVG(sale_price)  
FROM Product 
GROUP BY product_type 
HAVING AVG(sale_price) > (SELECT AVG(sale_price) FROM Product);

使用标量子查询时的注意事项

标量子查询绝对不能返回多行结果
如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 =或者<> 等需要单一输入值的运算符当中，也不能用在SELECT 等子句当中。

关联子查询

普通的子查询和关联子查询的区别

关联子查询在子查询中添加的WHERE 子句的条件
使用关联子查询时，通常会使用“限定（绑定）”或者“限制”这样的语言
在细分的组内进行比较时，需要使用关联子查询。

-- 按照商品种类计算平均价格 
SELECT AVG(sale_price)  
FROM Product 
GROUP BY product_type;

-- 发生错误的子查询 
SELECT product_id, product_name, sale_price  
FROM Product 
WHERE sale_price > (SELECT AVG(sale_price) 
                    FROM Product 
                    GROUP BY product_type);

-- 通过关联子查询按照商品种类对平均销售单价进行比较
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);

关联子查询也是用来对集合进行切分的

结合条件一定要写在子查询中

关联名称的作用域
- 关联名称存在一个有效范围的限制
- 子查询内部设定的关联名称，只能在该子查询内部使用

SQL：五 复杂查询

视图

视图和表

创建视图的方法

视图的限制

删除视图

子查询

子查询和视图

子查询的名称

标量子查询

标量子查询的书写位置

使用标量子查询时的注意事项

关联子查询

普通的子查询和关联子查询的区别

关联子查询也是用来对集合进行切分的

结合条件一定要写在子查询中

SQL：五复杂查询