MySQL课程笔记---第五章：复杂子查询

主讲教师：Mosh；课程链接：【【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！-哔哩哔哩】 https://b23.tv/MqVRzdk

-- 第五章 编写复杂查询
-- 子查询
SELECT *
FROM products
WHERE unit_price > ( -- 导出id为3的产品的价格
	SELECT unit_price
    FROM products
    WHERE product_id = 3)
-- 找出比id是3的产品价格更高的产品的信息

SELECT * -- 练习
FROM employees
WHERE salary > (
	SELECT avg(salary)
	FROM employees
)

-- IN运算符
USE sql_store;
-- 查询从来没被买过的产品
SELECT *
FROM products
WHERE product_id NOT IN (
-- NOT IN 表示不在已经被买过的产品表单里，即没被买过
	SELECT DISTINCT product_id
    FROM order_items
)

USE sql_invoicing; -- 练习

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

-- 子查询&链接
SELECT * 
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id
    FROM invoices
)
-- 上面代码可以用左连接达到相同的效果：
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL
-- 但左连接的可读性低于NOT IN

SELECT customer_id,first_name,last_name -- 练习-IN运算符
FROM customers
where customer_id IN (
	SELECT customer_id
    FROM orders o
    JOIN order_items oi
		USING (order_id)
    WHERE product_id =3
)
SELECT -- 练习-链接
	DISTINCT c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3 -- 第二种更具有可读性，因为第一种要在子查询里join，而第二种不用两层


-- ALL关键字
SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3 -- 该子查询返回单一值
)
-- 上面的代码与下面一段有相同的效果
SELECT *
FROM invoices
WHERE invoice_total > ALL( -- 此处我们用ALL关键词代表invoice_total大于子查询里所有的结果
	SELECT invoice_total
    FROM invoices
    WHERE client_id = 3 -- 该子查询返回一系列值
)

-- ANY关键字（与SOME关键字等效）
SELECT *
FROM clients
WHERE client_id = ANY( -- =ANY和IN等效，意为是子查询中的任意值
	SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >=2
)

-- 相关子查询
SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id -- 此处用于选择在相同office下的employee，可以理解为双重for循环，对于外层查询中的每一个office_id值，
    -- 在子查询中，查询与其有相同office_id的employee
)

SELECT * -- 练习
FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
)

-- EXISTS运算符
SELECT *
FROM clients c
WHERE EXISTS (
	SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
) -- 与IN运算符不同的是，EXISTS运算符不返回具体值，而是返回逻辑真假，表示外查询中的client_id是否在子查询中有相关性
-- 因此当数据较多时，用EXISTS运算符更快捷

SELECT * -- 练习
FROM products p
WHERE NOT EXISTS (
	SELECT product_id
	FROM order_items
    WHERE product_id = p.product_id -- 前一个product_id是order_items里的product_id
    -- 外面product_id与相关子查询product_id相同，则返回真，又因为NOT运算符，真变为假，最终返回不在order_items里的product_id的信息
)

-- SELECT子句中的子查询
SELECT
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
     FROM invoices) AS invoice_average, -- 在SELECT中使用子查询，返回invoice_total平均值
     -- 注意平均值只有一个，此处可以简化理解为，常数 152 AS invoice_average,并补充到每一行
     invoice_total-(SELECT invoice_average) AS difference
     -- 在SELECT子句数学公式里不能使用列别名，必须重新SELECT一遍，但可以简写为SELECT invoice_average
FROM invoices

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 -- 此处将(SELECT total_sales) - (SELECT average)简写了
FROM clients c
-- 要习惯写子查询，不要总是join

-- FROM子句中的子查询
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 -- 注意，在FROM子句中用子查询时必须起一个表别名,作为新的表格使用，仅限简单查询，复杂查询时用视图更好
WHERE total_sales IS NOT NULL