MySQL学习笔记（三） 编写复杂查询

MySQL 编写复杂查询

Subqueries VS Correlated Subqueries VS Joins

Subqueries 子查询

Subqueries 子查询，也称非相关子查询，意思是其执行不依赖与外部的查询。

执行过程：

1. 行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。
2. 行外部查询，并显示整个结果。

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

Correlated Subqueries 相关子查询

Correlated Subqueries 相关子查询，意思是其执行依赖于外部查询。

多数情况下是子查询的WHERE子句中引用了外部查询的表。

执行过程：

1. 从外层查询中取出一个元组，将元组相关列的值传给内层查询。
2. 执行内层查询，得到子查询操作的值。
3. 外查询根据子查询返回的结果或结果集得到满足条件的行。
4. 然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

USE sql_hr
SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
	WHERE office_id = e.office_id
)
-- 语句大意:
-- 对每一个员工进行子查询，子查询里面计算他所在部门的所有人的薪水的平均值。
-- 然后把这个员工的薪水和返回的平均值比较，如果大于平均值的就返回。

Subqueries VS Correlated Subqueries 非相关子查询与相关子查询

1. 非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
2. 相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。
3. 故非相关子查询比相关子查询效率高。

Subqueries VS Joins 子查询和连接

子查询和连接都能达到我们的目标，但是子查询更加直观。

有时候子查询会过于繁杂，那么我们就要用连接。

-- Using Subqueries
USE sql_store;
SELECT
	DISTINCT c.customer_id,
	c.first_name,
	c.last_name
FROM customers c
WHERE customer_id IN (
	SELECT o.customer_id
	FROM order_items oi
	JOIN orders o on oi.order_id = o.order_id
	WHERE product_id = 3
)
ORDER BY c.customer_id;
-- Using Joins
USE sql_store;
SELECT
	DISTINCT c.customer_id,
	c.first_name,
	c.last_name
FROM customers c
JOIN orders o on c.customer_id = o.customer_id
JOIN order_items oi on o.order_id = oi.order_id
WHERE product_id=3
ORDER BY customer_id;

IN VS EXISTS

IN 操作符

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

EXISTS 操作符

-- Using IN operator finish the query
USE invoicing;
SELECT *
FROM clients
WHERE client_id IN (
	SELECT DISTINCT client_id
	FROM invoices
);
-- Using EXISTS operator finish the query
USE invoicing;
SELECT *
FROM clients c
WHERE EXISTS (
	SELECT client_id
	FROM invoices
	WHERE client_id = c.client_id
);

IN 和 EXISTS 的区别

使用 IN 和 EXISTS的区别：

1. 使用IN的时候，返回的个是一个可迭代的id列表。也就是说 (1,2,3,4…)，然后再用 clients_id 在这个列表中去取值，再查询返回。
2. 使用EXISTS的时候，在子查询中判断子查询的 client_id 是否和父查询中的 client_id 相匹配。如果匹配成功，则直接返回信息当中。
3. 当数据量很大的时候，EXISTS的效率比IN要高

ALL VS ANY/SOME

ALL 关键词

父查询中的结果集大于子查询中每一个结果集中的值，则为真。

-- Not using ALL keyword
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id=3
);
-- Using All keyword
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ALL (
	SELECT invoice_total
	FROM invoices
	WHERE client_id=3
);

ANY/SOME关键词

ANY和SOME的功能一致没有区别。

父查询中的结果集大于子查询中任意一个结果集中的值，则为真。

SELECT *
FROM clients
WHERE client_id = ANY (
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
)