第七章子查询练习_【第六章】编写复杂查询

最新推荐文章于 2023-04-20 19:10:47 发布

翎姐超凶

最新推荐文章于 2023-04-20 19:10:47 发布

阅读量208

点赞数

文章标签：第七章子查询练习

本文链接：https://blog.csdn.net/weixin_30999777/article/details/112579911

版权

上一级目录：Mosh_完全掌握SQL课程_学习笔记

其它相关：数据概要

【第六章】编写复杂查询

Writing Complex Query (时长45分钟)

1. 介绍

Introduction (1:28)

主要是子查询（或者说 嵌套查询），有些前面已经讲过

2. 子查询

Subqueries (2:29)

回顾

子查询：任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询，子查询是一个很有用的技巧。子查询的层级用括号实现。

注意

另外发现各种语言，各种语句，各种逻辑结构，各种情形下一般好像多加括号都不会有问题，只有少加括号才会出问题，所以不确定执行顺序时最好加上括号确保万无一失。

案例

在 products 中，找到所有比生菜（id = 3）价格高的

关键：用子查询找到生菜价格

USE sql_store;

SELECT *
FROM products
WHERE unit_price > (
    SELECT unit_price
    FROM products
    WHERE product_id = 3
)

MySQL执行时会先执行括号内的子查询（内查询），将获得的生菜价格作为结果返回给外查询

子查询不仅可用在 WHERE …… 中，也可用在 SELECT …… 或 FROM …… 等子句中，本章后面会讲

练习

在 sql_hr 库 employees 表里，选择所有工资超过平均工资的雇员

关键：由子查询得到平均工资

USE sql_hr;

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

3. IN运算符

The IN Operator (3:39)

案例

在 sql_store 库 products 表中找出那些从未被订购过的产品

思路：
1. order_items 表里有所有产品被订购的记录，用 DISTINCT 去重，得到所有被订购过的产品列表
2. 不在这列表里（NOT IN 的使用）的产品即为从未被订购过的产品

USE sql_store;

SELECT *
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
)

上一节是子查询返回一个值（平均工资），这一节是返回一列数据（被订购过的产品id列表），之后还会用子查询返回一个多列的表

练习

在 sql_invoicing 库 clients 表中找到那些没有过发票记录的客户

思路：和上一个例子完全一致，在invoices里用DISTINCT找到所有有过发票记录的客户列表，再用NOT IN来筛选

USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
)

4. 子查询vs连接

Subqueries vs Joins (5:07)

小结

子查询（Subquery）是将一张表的查询结果作为另一张表的查询依据并层层嵌套，其实也可以先将这些表链接（Join）合并成一个包含所需全部信息的详情表再直接在详情表里筛选查询。两种方法一般是可互换的，具体用哪一种取决于 效率/性能（Performance）和可读性（readability），之后会学习执行计划，到时候就知道怎样编写并更快速地执行查询，现在主要考虑可读性

案例

上节课的案例，找出从未订购（没有invoices）的顾客：

法1. 子查询

先用子查询查出有过发票记录的顾客名单，作为筛选依据

USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    /*其实这里加不加DISTINCT对子查询返回的结果有影响
    但对最后的结果其实没有影响*/
    FROM invoices
)

法2. 链接表

用顾客表 LEFT JOIN 发票记录表，再直接在这个合并详情表中筛选出没有发票记录的顾客

USE sql_invoicing;

SELECT DISTINCT client_id, name …… 
-- 不能SELECT DISTINCT *
FROM clients
LEFT JOIN invoices USING (client_id)
-- 注意不能用内链接，否则没有发票记录的顾客（我们的目标）直接就被筛掉了
WHERE invoice_id IS NULL

就上面这个案例而言，子查询可读性更好，但有时子查询会过于复杂（嵌套层数过多），用链接表更好（下面的练习就是）。总之在选择方法时，可读性是很重要的考虑因素

练习

在 sql_store 中，选出买过生菜（id = 3）的顾客的id、姓和名

分别用子查询法和链接表法实现并比较可读性

法1. 完全子查询

USE sql_store;

SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询2：从订单表中找出哪些顾客买过生菜
    SELECT customer_id
    FROM orders
    WHERE order_id IN (  
        -- 子查询1：从订单项目表中找出哪些订单包含生菜
        SELECT DISTINCT order_id
        FROM order_items
        WHERE product_id = 3
    )
)

法2. 混合：子查询 + 表连接

USE sql_store;

SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询：哪些顾客买过生菜
    SELECT customer_id
    FROM orders
    JOIN order_items USING (order_id)  
    -- 表连接：合并订单和订单项目表得到 订单详情表
    WHERE product_id = 3
)

法3. 完全表连接

直接链接合并3张表（顾客表、订单表和订单项目表）得到带顾客信息的订单详情表，该合并表包含我们所需的所有信息，可直接在合并表中用WHERE筛选买过生菜的顾客（注意 DISTINCT 关键字的运用）。

USE sql_store;

SELECT DISTINCT customer_id, first_name, last_name
FROM customers
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items USING (order_id)
WHERE product_id = 3

这个案例中，先将所需信息所在的几张表全部连接合并成一张大表再来查询筛选明显比层层嵌套的多重子查询更加清晰明了

5. ALL关键字

The ALL Keyword (4:52)

小结

> (MAX (……)) 和 > ALL(……) 等效可互换

“比这里面最大的还大” = “比这里面的所有的都大”

案例

sql_invoicing 库中，选出金额大于3号顾客所有发票金额（或3号顾客最大发票金额）的发票

法1. 用MAX关键字

USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

法2. 用ALL关键字

USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

其实就是把内层括号的MAX拿到了外层括号变成ALL：
MAX法是用MAX()返回一个顾客3的最大订单金额，再判断哪些发票的金额比这个值大；
ALL法是先返回顾客3的所有订单金额，是一列值，再用ALL()判断比所有这些金额都大的发票有哪些。
两种方法是完全等效的

6. ANY关键字

The ANY Keyword (2:36)

小结

> ANY/SOME (……) 与 > (MIN (……)) 等效

= ANY/SOME (……) 与 IN (……) 等效

案例1

> ANY (……) 与 > (MIN (……)) 等效的例子：
sql_invoicing 库中，选出金额大于3号顾客任何发票金额（或最小发票金额）的发票

USE sql_invoicing;

SELECT *
FROM invoices

WHERE invoice_total > ANY (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

或

WHERE invoice_total > (
    SELECT MIN(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

案例2

= ANY (……) 与 IN (……) 等效的例子:
选出至少有两次发票记录的顾客

USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id IN (  -- 或 = ANY ( 
    -- 子查询：有2次以上发票记录的顾客
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

7. 相关子查询

Correlated Subqueries (5:36)

小结

之前都是非关联主/子（外/内）查询，比如子查询先查出整体的某平均值或满足某些条件的一列id，作为主查询的筛选依据，这种子查询与主查询无关，会先一次性得出查询结果再返回给主查询供其使用。

而下面这种相关联子查询例子里，子查询要查询的是某员工所在办公室的平均值，子查询是依赖主查询的，注意这种关联查询是在主查询的每一行/每一条记录层面上依次进行的，这一点可以为我们写关联子查询提供线索（注意表别名的使用），另外也正因为这一点，相关子查询会比非关联查询执行起来慢一些。

案例

选出 sql_hr.employees 里那些工资超过他所在办公室平均工资（而不是整体平均工资）的员工
关键：如何查询目前主查询员工的所在办公室的平均工资而不是整体的平均工资？
思路：给主查询 employees表设置别名 e，这样在子查询查询平均工资时加上 WHERE office_id = e.office_id 筛选条件即可相关联地查询到目前员工所在地办公室的平均工资

USE sql_hr;

SELECT *
FROM employees e  -- 关键 1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id  -- 关键 2
    -- 【子查询表字段不用加前缀，主查询表的字段要加前缀，以此区分】
)

相关子查询很慢，但很强大，也有很多实际运用

练习

在 sql_invoicing 库 invoices 表中，找出高于每位顾客平均发票金额的发票

USE sql_invoicing;

SELECT *
FROM invoices i
WHERE  invoice_total > (
    -- 子查询：目前客户的平均发票额
    SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
)

8. EXISTS运算符

The EXISTS Operator (5:39)

小结

IN + 子查询 等效于 EXIST + 相关子查询，如果前者子查询的结果集过大占用内存，用后者逐条验证更有效率。另外 EXIST() 本质上是根据是否为空返回 TRUE 和 FALSE，所以也可以加 NOT 取反。

案例

找出有过发票记录的客户，第4节学过用子查询或表连接来实现

法1. 子查询

USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
)

法2. 链接表

USE sql_invoicing;

SELECT DISTINCT client_id, name …… 
FROM clients
JOIN invoices USING (client_id)
-- 内链接，只留下有过发票记录的客户

第3种方法是用EXISTS运算符实现

USE sql_invoicing;

SELECT *
FROM clients c
WHERE EXISTS (
    SELECT */client_id  
    /* 就这个子查询的目的来说，SELECT的选择不影响结果，
    因为EXISTS()函数只根据是否为空返回 TRUE 和 FALSE */
    FROM invoices
    WHERE client_id = c.client_id
)

这还是个相关子查询，因为在其中引用了主查询的 clients 表。这同样是按照主查询的记录一条条验证执行的。具体说来，对于 clients 表（设置别名为 c）里的每一个顾客，子查询在 invoices 表查找这个人的发票记录（即 client_id = c.client_id 的发票记录），有就返回相关记录否者返回空，然后 EXISTS() 根据是否为空得到 TRUE 和 FALSE（表示此人有无发票记录），然后主查询凭此确定是否保留此条记录。

对比一下，法1是用子查询返回一个有发票记录的顾客id列表，如（1，3，8 ……），然后用IN运算符来判断，如果子查询表太大，可能返回一个上百万千万甚至上亿的id列表，这个id列表就会很占内存非常影响性能，对于这种子查询会返回一个很大的结果集的情况，用这里的EXIST+相关子查询逐条筛选会更有效率

另外，因为 SELECT() 返回的是 TRUE/FALSE，所以自然也可以加上NOT取反，见下面的练习

练习

在sql_store中，找出从来没有被订购过的产品。

USE sql_store;

SELECT *
FROM products 
WHERE product_id NOT IN (
    SELECT product_id 
    -- 加不加DISTINCT对最终结果无影响
    FROM order_items
)

或

SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT *
    FROM order_items
    WHERE product_id = p.product_id
)

对于亚马逊这样的大电商来说，如果用IN+子查询法，子查询可能会返回一个百万量级的产品id列表，这种情况还是用EXIST+相关子查询逐条验证法更有效率

9. SELECT子句的子查询

Subqueries in the SELECT Clause (4:29)

小结

不仅 WHERE 筛选条件里可以用子查询，SELECT 选择子句和 FROM 来源表子句也能用子查询，这节课讲 SELECT 子句里的子查询

简单讲就是，SELECT选择语句是用来确定查询结果选择包含哪些字段，每个字段都可以是一个表达式，而每个字段表达式里的元素除了可以是原始的列，具体的数值，也同样可以是其它各种花里胡哨的子查询的结果

任何子查询都是简单查询的嵌套，没什么新东西，只是多了一个层级而已，由内向外地一层层梳理就很清楚

要特别注意记住以子查询方式实现在SELECT中使用同级列别名的方法

案例

得到一个有如下列的表格：invoice_id, invoice_total, avarege（总平均发票额）, difference（前两个值的差）

USE sql_invoicing;

SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    /*不能直接用聚合函数，因为“比较强势”，会压缩聚合结果为一条
    用括号+子查询(SELECT AVG(invoice_total) FROM invoices) 
    将其作为一个数值结果 152.388235 加入主查询语句*/
    invoice_total - (SELECT invoice_average) AS difference
    /*SELECT表达式里要用原列名，不能直接用别名invoice_average
    要用列别名的话用子查询（SELECT 同级的列别名）即可
    说真的，感觉这个子查询有点难以理解，但记住会用就行*/
FROM invoices

练习

得到一个有如下列的表格：client_id, name, total_sales（各个客户的发票总额）, average（总平均发票额）, difference（前两个值的差）

USE sql_invoicing;

SELECT 
    client_id,
    name,
    (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
    -- 要得到【相关】客户的发票总额，要用相关子查询 WHERE client_id = c.client_id
    (SELECT AVG(invoice_total) FROM invoices) AS average,
    (SELECT total_sales - average) AS difference   
    /* 如前所述，引用同级的列别名，要加括号和 SELECT，
    和前两行子查询的区别是，引用同级的列别名不需要说明来源，
    所以没有 FROM …… */
FROM clients c

注意第四个客户的 total_sales 和 difference 都是空值 null

10. FROM子句的子查询

Subqueries in the FROM Clause (2:58)

小结

子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表，即将筛选查询结果作为来源再进行进一步的筛选查询。但注意只有在子查询不太复杂时进行这样的嵌套，否则最好用后面讲的视图先把子查询结果储存起来再使用。

案例

将上一节练习里的查询结果当作来源表，查询其中 total_sales 非空的记录

USE sql_invoicing;

SELECT * 
FROM (
    SELECT 
        client_id,
        name,
        (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
        (SELECT AVG(invoice_total) FROM invoices) AS average,
        (SELECT total_sales - average) AS difference   
    FROM clients c
) AS sales_summury
/* 在FROM中使用子查询，即使用 “派生表” 时，
必须给派生表取个别名（不管用不用），这是硬性要求，不写会报错：
Error Code: 1248. Every derived table（派生表、导出表）
must have its own alias */
WHERE total_sales IS NOT NULL

复杂的子查询再嵌套进 FROM 里会让整个查询看起来过于复杂，上面这个最好是将子查询结果储存为叫 sales_summury 的视图，然后再直接使用该视图作为来源表，之后会讲。