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);

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

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

• 关联名称的作用域

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