mysql——复杂查询

多表查询

• from clause
• select clause
• Subqueries, the in operator
• The IN Operator
• Subqueries vs Joins
• distinct 会作用在多列上，只能写在第一列前
• left join, the using keyword

-- tags: subqueries in the from clause from中的子查询
-- gist: 所有商品单价大于商品id为3的商品 
SELECT *
FROM products
WHERE unit_price > (
    SELECT unit_price
    FROM products
    WHERE product_id = 3
);

-- gist: 高于平均工资的雇员 
SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

-- tags: The IN Operator
-- gist: 没有订单记录的商品
SELECT *
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
);

-- gist: 没有发票记录的客户
SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
);

-- tags: Subqueries vs Joins 子查询和连接
-- gist: 找出没有发票的客户（没买过东西的客户）
SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id -- distinct避免多余的判断，提供语句执行效率
    FROM invoices
);
SELECT DISTINCT client_id, name
FROM clients
LEFT JOIN invoices USING (client_id) -- 保留没有发票的客户！！
WHERE invoice_id IS NULL;

-- tags: distinct 会作用在多列上，只能写在第一列前
-- 在使用 DISTINCT 时，NULL 也被视为一类数据。 NULL 存在于多行中时，也会被合并为一条NULL 数据
-- DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列，多关键字去重
-- 所以建议使用distinct时要注意是否其他列会影响去重
SELECT DISTINCT product_type, regist_date
FROM Product;
-- 实际效果
-- product_type |regist_date
-- --------------+------------
-- 衣服     | 2009-09-20
-- 办公用品 | 2009-09-11
-- 办公用品 | 2009-11-11
-- 衣服    |	
-- 厨房用具 | 2009-09-20
-- 厨房用具 | 2009-01-15
-- 厨房用具 | 2008-04-28

-- tags: Subqueries, the in operator
-- gist: 选出买过生菜（id=3）的消费者的id、姓名
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    SELECT customer_id
    FROM orders
    WHERE order_id IN (  
        SELECT DISTINCT order_id
        FROM order_items
        WHERE product_id = 3
    )
);

-- tags: Subqueries, the in operator, join
-- gist: 选出买过生菜（id=3）的消费者的id、姓名
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    SELECT DISTINCT customer_id -- 最好去重
    FROM orders
    JOIN order_items USING (order_id)  
    WHERE product_id = 3
);

-- tags: left join, the using keyword
-- gist: 选出买过生菜（id=3）的消费者的id、姓名
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;

-- tags: aggregate function MAX()
-- gist: 发票大于3号顾客发票的最大金额
SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
);

-- tags: the ALL keyword
-- ides: ALL 前面可以跟 >, <, =
-- gist: 发票大于3号顾客发票的最大金额（大于3号顾客发票的所有金额）
SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
);

-- tags: the ANY keyword
-- ides: ANY 前面可以跟 >, <, =, = any 等价 in()
-- gist: 发票金额大于3号顾客任意一张发票的金额
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
);

-- tags: in operator or any 
-- gist: 至少有两次发票记录的顾客
SELECT *
FROM clients
WHERE client_id IN ( -- = any()
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
);

-- tags: Correlated Subqueries 相关子查询
-- gist: 员工工资高于部门平均工资
-- idea: 与其他子查询不能，相关子查询会随着关联值的改变去循环执行子查询
SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
);

-- tags: Correlated Subqueries 相关子查询
-- gist: 每个顾客的发票大于他自己发票的平均金额
SELECT *
FROM invoices i
WHERE  invoice_total > (
    SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
);

-- tags: EXISTS operator vs in operator
-- mean: 有发票记录的客户
-- idea: 相比于in操作符，如果只需确定子查询是否有记录
--       使用exists会更加节省内存和提高效率
SELECT *
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
);
SELECT *
FROM clients c
WHERE EXISTS ( -- 有记录就为true
    SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
);

-- tags: EXISTS operator vs in operator
-- mean: 没有出售记录的商品
SELECT *
FROM products 
WHERE product_id NOT IN ( -- 可能返回几百万个值，导致速度降低
    SELECT DISTINCT product_id 
    FROM order_items
);

-- tags: EXISTS operator vs in operator
-- mean: 没有出售记录的商品
SELECT *
FROM products p
WHERE NOT EXISTS ( -- 相关子查询 + exists 提高效率
    SELECT *
    FROM order_items
    WHERE product_id = p.product_id
);

-- tags: subqueries in the select clause select中的子查询
-- mean: 查询发票id，发票总金额，所以发票的平均值，两者的差
SELECT
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS different -- 表达式中不能使用列名，所以采用selct
FROM
    invoices;

-- tags: subqueries in the select clause, correlated subqueries from中的子查询, 相关子查询
-- mean: 客户id，这个客户的总花费（需要连接发票表），平均值，差值
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;

-- tags: subqueries in the from clause from中的子查询
-- mean: 演示，无实际业务，建议使用视图简化
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
WHERE total_sales IS NOT NULL;