SQL复杂查询

参考资料： 《SQL基础教程》

复杂查询

视图

视图和表

从SQL的角度来看，视图就是一张表，两者的区别在于是否保存了实际的数据。

1. 创建表时，会通过INSERT语句将数据保存到数据库中，而数据库中的数据实际上会保存到计算机的存储设备。
2. 使用视图时，并不会将数据保存到任何地方，实际上视图保存的是SELECT语句。从视图读取数据时，视图会在内部执行该SELECT语句并创建出一张临时表。

视图和表

总结成一句话：表中存储的是实际数据，视图本质上就是SELECT语句。

视图的优点

1. 视图无需保存数据，节省存储设备容量；
2. 可以将频繁使用的SELECT语句保存成视图，就不用每次都重新书写了。

创建和使用视图

创建视图的CREATE VIEW语句

CREATE VIEW 视图名称 (<视图列名1>, <视图列名2>, ...)
AS
<SELECT语句>

注意：SELECT语句中列的排列顺序和视图中列的排列顺序相同，SELECT语句中的第1列就是视图中的第1列，以此类推。视图的列名在视图名称之后的列表中定义。

创建ProductSum视图

CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

使用视图

SELECT
    product_type,
    cnt_product
FROM ProductSum;

执行结果：

在FROM子句中使用视图查询的两个步骤

1. 创建视图：执行定义视图的SELECT语句；
2. 使用视图：根据得到的结果，再执行在FROM子句中使用视图的SELECT语句。

当然，我们还可以以视图为基础再创建视图，因此，使用视图的查询通常需要执行2条以上的SELECT语句。但是，多重视图会降低SQL的性能，因此希望大家使用单一视图。

视图的限制

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

为什么不能使用ORDER BY子句？ 因为视图和表一样，数据行都是没有顺序的。

对视图进行更新

在SELECT语句中，视图可以和表一样使用，那么，更新语句（INSERT、DELETE、UPDATE）呢？ 标准SQL中规定：如果定义视图的SELECT语句能够满足某些条件，那么这个视图就可以被更新。 几个具有代表性的条件：

1. SELECT子句中未使用DISTINCT
2. FROM子句中只有一张表
3. 未使用GROUP BY子句
4. 未使用HAVING子句

删除视图

删除视图的DROP VIEW语句

DROP VIEW 视图名称

删除视图ProductSum

DROP VIEW ProductSum;

子查询

子查询和视图

子查询就是一次性的视图（SELECT语句），子查询在SELECT语句执行完毕之后就会消失。

子查询

-- 和上面使用ProductSum视图实现相同功能的子查询语句
SELECT
    product_type,
    cnt_product
FROM (
SELECT
    product_type,
    COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type
) AS ProductSum;

执行结果：

子查询SELECT语句的执行顺序

由内到外：

1. 首先执行FROM子句中的SELECT语句；
2. 根据1的结果执行外层的SELECT语句。

注意：子查询的层数原则上没有限制，可以无限嵌套下去，但是，随着层数增加，SQL语句会变得越来越难读懂，性能也会越来越差。因此，尽量避免使用多层嵌套的子查询。

子查询的名称 原则上子查询必须设定名称，设定名称是需要使用AS关键字，有时也可以省略。

标量子查询

标量子查询就是返回单一值的子查询，必须而且只能返回表中某一行的某一列的值。

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

如何查询出销售单价高于平均销售单价的商品？ 我们可能会想到以下错误的SQL语句：

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

虽然这样的SELECT语句看上去能够满足我们的要求，但是由于在WHERE子句中不能使用聚合函数，因此这样的SELECT语句是错误的。 这时，标量子查询就可以发挥它的功效了。

计算平均销售单价的标量子查询

SELECT AVG(sale_price)
FROM Product;

执行结果：

选出销售单价高于平均单价的商品

SELECT
    product_id,
    product_name,
    sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
                    FROM Product);

执行结果：

标量子查询的书写位置

通常任何可以使用单一值的位置都可以使用标量子查询，也就是说，能够使用常数或者列名的地方，无论是SELECT子句、GROUP BY子句、HAVING子句，还是ORDER BY子句，都可以使用。

注意：如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅只是一个普通的子查询，因此不能被用在需要单一输入值的地方。

关联子查询

通过关联子查询按照商品种类对平均销售单价进行比较

SELECT
    product_id,
    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);

执行结果：

这里起关键作用的就是在子查询中添加WHERE子句的条件，该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。