MySQL基础进阶：编写复杂查询

1. 子查询

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

MySQL执行时会先执行括号内的子查询（内查询），将获得的结果返回给外查询，子查询不仅可用在 WHERE …… 中，也可用在 SELECT …… 或 FROM …… 等子句中

SELECT
    *
FROM products
WHERE unit_price > (
     SELECT unit_price
     FROM products
     WHERE name LIKE '%Lettuce%'
)

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

2. IN运算符

当需要判定的条件中有多个数值时，用到IN和NOT IN运算符。（可以是字符也可以是数字）

SELECT *
FROM products
WHERE product_id NOT IN(
    SELECT DISTINCT product_id  -- distinct关键字用于属性名前，得到不重复值
    FROM order_items
)

3. 子查询VS连接

子查询（Subquery）是将一张表的查询结果作为另一张表的查询依据并层层嵌套，其实也可以先将这些表连接（Join）合并成一个包含所需全部信息的详情表再直接在详情表里筛选查询。两种方法一般是可互换的，具体用哪一种取决于性能（Performance）和可读性（readability）。

SELECT *
FROM products
LEFT JOIN order_items USING (product_id)
WHERE order_id IS NULL  
-- 等同于在WHERE中采用子查询

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    
-- 对于多表之间的查询，先连接成大表，然后再查询可读性更强

SELECT 
    customer_id,
    first_name,
    last_name
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM order_items oi
    JOIN orders o USING (order_id)
    WHERE product_id = 3
)  -- 采用子查询和外连接共同作用来进行多表查询

4. ALL关键字

(MAX (……)) 和 > ALL(……) 等效可互换：“比这里面最大的还大” = “比这里面的所有的都大”
从以下代码来进行对比：

SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total) -- 用于表中的属性值计算最大值
    FROM invoices
    WHERE client_id = 3
)

SELECT *
FROM invoices
WHERE invoice_total > ALL ( -- 用于子查询后结构的计算最大值
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

5. ANY关键字

同SOME，表示其中任何一个
= ANY (……) 与 IN (……) 等效；
< ANY/SOME (……) 与 < (MIN (……)) 等效

SELECT *
FROM clients
WHERE client_id = ANY (    -- 可采用IN或= SOME来替换
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) > 2
)

6. 相关子查询

• 非相关子查询：子查询先查出整体的某平均值或满足某些条件的一列id，作为主查询的筛选依据，这种子查询与主查询无关，会先一次性得出查询结果再返回给主查询供其使用。
• 关子查询：子查询是依赖主查询的，子查询的判定中需要引入主查询的属性值（需要为主查询中表名命别名），每一条记录的主查询对应的子查询结果也会发生改变。这种关联查询是在主查询的每一行/每一条记录层面上依次进行的，另外也正因为这一点，相关子查询会比非关联查询执行起来慢一些。

Note:伪代码其实就是注释的表达方式，一种按照程序执行步骤编写的注释，中英文都可以

SELECT *
FROM employees e
WHERE salary > (  -- 相当于执行循环计算，子查询用到了主查询中的属性值
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
)

7. EXISTS运算符

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

SELECT * 
FROM clients
WHERE client_id IN (  -- 判断的是某些数值
    SELECT DISTINCT client_id
    FROM invoices
)

SELECT DISTINCT client_id,name
-- 外连接可能产生大量重复项，需要剔除
FROM clients
JOIN invoices USING (client_id)

SELECT * 
FROM clients c   -- 数据库本质上就是对表格数据的逐条判断筛选，然后联系到大量关系型数据库表格
WHERE EXISTS (
-- 类似判断语句，符合条件返回ture,则将此条记录输出
-- 对于clients表格中记录进行输出，不会产生重复
    SELECT *
    FROM invoices
    WHERE client_id = c.client_id
)

NOTE:

1. EXISTS(…) 函数相当于是前置的 … IS NULL（共同点：都是根据是否为空返回布林值）
2. WHERE 确实是逐条验证筛选行/记录的
3. EXISTS也是相关子查询
4. 对于大电商来说，如果用IN+子查询法，子查询可能会返回一个百万量级的产品列表，这种情况还是用EXIST+相关子查询逐条验证法更有效率

8. SELECT子句中得子查询

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

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

SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    -- '123' AS Test 命名属性值，同时给其赋值。
    -- 当需要记录值逐条输出时，不能直接用聚合函数。必须和GROUP BY语句联合输出
    /*用括号+子查询改变顺序，【子查询 (SELECT AVG(invoice_total) FROM invoices)
    是作为一个数值结果 152.388235 加入主查询语句的】*/
    invoice_total - (SELECT invoice_average) AS difference
    /*SELECT表达式里要用原列名，不能直接用别名invoice_average
    要用列别名的话用子查询（SELECT 同级的列别名）即可*/
FROM invoices
Note:可以理解为给属性列来赋值
SELECT 
    client_id,
    name,
    (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
    -- 子查询相对主查询clients，会进行5次循环查询。若用GRUOP BY则只会产生4组数值
    (SELECT AVG(invoice_total) FROM invoices) AS average,
    (SELECT total_sales - average) AS difference
FROM clients c

Note:形成表格过程中一定注意行中列数要匹配。

9. FROM子句中得子查询

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

复杂的子查询再嵌套进FROM里会让整个查询看起来过于复杂，最好是将子查询结果储存为视图，然后再直接使用该视图作为来源表。（形成新表，然后查询。）

FROM子句中的子查询一般不用。