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

编写复杂查询

1. 子查询
2. IN运算符
3. 子查询VS连接
4. ALL关键字
5. ANY关键字
6. 相关子查询
7. EXISTS运算符
8. SELECT子句中得子查询
9. FROM子句中得子查询

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:

EXISTS(…) 函数相当于是前置的 … IS NULL（共同点：都是根据是否为空返回布林值）
WHERE 确实是逐条验证筛选行/记录的
EXISTS也是相关子查询
对于大电商来说，如果用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