Mysql 05 子查询

编写复杂查询
子查询就是在另一个sql语句中的选择语句

1. 子查询/内查询（Subqueries)

SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
	)
--查询薪水高于平均薪水的员工

2. IN运算符

-- 比如查找没有发票的客户（先写子查询，再套入）
SELECT *
FROM clients
WHERE client_id NOT IN(
	SELECT DISTINCT client_id
	FROM invoices
	)

3. 子查询 VS 连接

如上个模块，也可以用左连接来表示同样的结果

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

--两者达到的效果相同，相比之下，这种情况下第一种看起来更直观。但有时添加子查询会使一个查询太过复杂，所以更适合连接

4. ALL关键字

--假如要查询发票金额大于客户3的最大发票金额数的信息
SELECT *
FROM invoices
WHERE invoice_total >(
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id = 3
	)		--子查询返回单一值

--如果要用ALL关键字的话
SELECT *
FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
	FROM invoices
	WHERE client_id = 3
	)		--子查询返回一列值
--ALL——大于子查询中的所有项

-- MAX和ALL两种方法均可

5. ANY/SOME关键字

-- 如果要返回订单次数大于2的客户信息
SELECT *
FROM clients
WHERE client_id IN (
	SELECT  client_Id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) > 2
)

-- 另一种方法就是用ANY关键字
SELECT *
FROM clients
WHERE client_id = ANY (		--翻译一下就是如果客户id等于这段子查询返回值里的任意一个，那位客户就会被返回到最终结果
	SELECT  client_Id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) > 2
)

--IN 和 = ANY等效

6. 相关子查询

-- 选择工资超过部门平均的员工（因为一个公司有多个部门，每个部门的员工平均工资不同，所以要用到相关子查询 
SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
	WHERE office_id = e.office_id
	)

-- 引用了外查询里出现的别名，所以是相关子查询
--非关联在运算时，只执行一次子查询，然后将结果值全部扔给外查询筛选。 相关子查询在运算时，子查询执行一次就会去对应外查询的一条数据进行外层WHERE条件的判定。

7. EXISTS运算符

SELECT *
FROM clients c
WHERE EXISTS (		--使用EXISTS运算符来查看发票表里是否存在符合这个条件的行
	SELECT client_id
	FROM invoices
	WHERE client_id = c.client_id
)

-- 与之前的不同： 当用IN时，会执行下面的子查询，并将结果返回到WHERE子句。而上述案例中，返回的结果就是一个有。。个客户id的列表
--在子查询返回的结果集比较小的时候，用IN效率更高，在返回的结果集较大的时候，用EXISTS效率更高
--因为EXISTS是逐行筛选，所以可以理解为IN用于非关联子查询，EXISTS用于关联子查询

8. SELECT子句中的子查询

SELECT 
	invoice_id,
	invoice_total,
	(SELECT AVG(invoice_total)
		FROM invoices) AS invoice_average，		--（）里先用聚合函数求出平均值，然后（）外把值给到每一列,(如果AVG放在括号外面话就只有一行的值）
	invoice_total - (SELECT invoice_average) AS difference	--因为不能用别名，所以可以这么写或者直接复制。	相当于select 一个常数，后面不用from
FROM invoices
		

9. FROM子句中的子查询

--运用之前自己查询出来的虚拟的一个表格
SELECT *
FROM (
	之前自己导出来的虚拟表格
)

--在选择语句中的FROM语句中写子查询，会让主查询变得更复杂，所以这种情况下可以使用视图，中间的查询可以作为视图存储在数据库中，并给其取名