MySQL（2）子查询

子查询：

查询语句中嵌套了查询语句：
子查询=基本查询+条件查询+分组查询+连接查询

格式： select  字段 from 表或者（查询语句） where 条件（查询语句）

查询结果又几种格式：
结果都是以表格的形式返回：

	1.多行多列                  在子查询中当做一张表进行查询，只能在from后面出现）
	2.多行单列(只是查出来某一列值)（可以出现在from后面，一般用于in中）
	3.单行多列				（在子查询中当做一张表进行查询，只能在from后面出现）
	4.单行单列    			（可以使用在任意的条件上）

sql语句的执行顺序：

sql语句是从右向左执行，先判断条件，在执行查询结果，如果有子查询，先执行子语句，在执行主语句。
子查询语句需要用小括号“()”括起来。

在书写查询条件时：条件可能不止一个，
	当多个条件并行查询时，可能是and 也可能是 or ：
		如果是and，一般会将条件结果可能不成立的向后方。
		如果是or， 一般将条件结果可能成立的向后方。
		作用：可以一定程度上的提高查询效率。

子查询的特点：

	有多条简单语句组成的，可以进行分步操作，理解容易。

in any all

in: 条件都必须等于给定的几个值。
any: 条件只要比其中一个大或者比其中一个小，或者等于某个条件

any
<any
=any

all: 比所有的都大或都小

all
<all

在mysql中只能用子查询实现all的效果 any和all在其它数据库中都可以使用。

sql语句示例：

数据库名：qianfeng ,表为：emp(员工表) , dept(部门表)

-- 列出薪资比“王语嫣”或 小帅多的所有员工的编号，姓名，部门名称，领导姓名
-- 分析此次查询需要那些表，
-- 1，查询王语嫣和小帅的工资需要一张emp表，e1
-- 2、查询出比 王语嫣和小帅 工资高的所有员工信息 需要一张emp表e2
-- 3、查询出他们对应的领导姓名需要一张emp表e3
-- 4、最终查询出部门信息需要dept表d

-- 1先查出 王语嫣，小帅的工资
SELECT e1.empsal FROM emp e1 WHERE e1.`empname` IN ("王语嫣","小帅")
-- 2 再将小帅的工资作为子查询的条件，查出比 王语嫣和小帅 工资高的 员工编号 姓名
SELECT e2.empno,e2.empname,e2.empmgr,e2.deptno FROM emp e2 WHERE e2.empsal>ANY(SELECT e1.empsal FROM emp e1 WHERE e1.empname IN("王语嫣","小帅"))
-- 3 再将第二部的查询结果当做左表去左连接emp表查出领导姓名
SELECT t1.empno,t1.empname,e3.empname FROM
(
SELECT e2.* FROM emp e2 WHERE e2.empsal>ANY(SELECT e1.empsal FROM emp e1 WHERE e1.empname IN("王语嫣","小帅"))
)t1 LEFT JOIN emp e3
ON t1.empmgr=e3.empno
-- 4 将第3步的查询结果当做左表去连接dept表，查出部门名称
SELECT t2.empno 编号,t2.empname 姓名,t2.领导姓名,d.deptname 部门
FROM (
SELECT t1.empno,t1.empname,t1.deptno,e3.empname 领导姓名 FROM
(
SELECT e2.empno,e2.empname,e2.empmgr,e2.deptno FROM emp e2 WHERE e2.empsal>ANY(SELECT e1.empsal FROM emp e1 WHERE e1.empname IN("王语嫣","小帅"))
)t1 LEFT JOIN emp e3
ON t1.empmgr=e3.empno
)t2 LEFT JOIN dept d
ON t2.deptno=d.deptno

--  查询所有工资比小博工资还要高的所有雇员信息。
-- 先查出小博的工资
SELECT e1.`empsal` FROM emp e1 WHERE e1.empname="小博"
-- 再将小博的工资作为子查询条件
SELECT e2.* FROM emp e2 WHERE e2.empsal > (SELECT e1.`empsal` FROM emp e1 WHERE e1.empname="小博")

-- 要求查询出每个部门的编号，名称，位置，部门人数，平均工资
SELECT d.deptno 编号,d.deptname 名称,d.deptloc 位置,COUNT(1) 部门人数,AVG(e.empsal) 平均工资
FROM dept d LEFT JOIN emp e
ON d.deptno=e.deptno
GROUP BY d.deptno,d.deptname,d.deptloc

-- 使用子查询实现
-- 1、先查询出部门人数，部门平均工资
SELECT COUNT(e.empno) `count`,AVG(e.empsal) `avg`,e.deptno
FROM emp e
GROUP BY e.deptno
-- 2、使用左连接，查询出相应信息
SELECT d.deptno,d.deptname,d.deptloc,t.count,t.avg
FROM dept d 
LEFT JOIN 
(SELECT COUNT(e.empno) `count`,AVG(e.empsal) `avg`,e.deptno deptno
FROM emp e
GROUP BY e.deptno
)t
ON d.deptno=t.deptno

-- 查询工资是5000 或 6000 或10000
SELECT e.empsal,e.empname FROM emp e WHERE e.empsal IN (5000,6000,10000) ORDER BY e.empsal DESC

-- 查询工资比 5000 6000,10000任何一个大,,ANY后面必须跟子句，只要比子句任何一个数大都满足
SELECT e.empsal,e.empname FROM emp e WHERE e.empsal>ANY(SELECT e.empsal FROM emp e WHERE e.empname IN ("小博","张磊","周芷若")) ORDER BY e.empsal
-- 查询出比'小博','小佳','小帅' 任何一个人工资都低的人全部信息，只要是比任何一个都小就满足条件
SELECT * FROM emp e WHERE e.empsal<ALL(
	SELECT e2.empsal FROM emp e2 WHERE e2.empname IN('小博','小佳','小帅')
)

-- 1 查询出至少含有一个员工的部门。
SELECT d.deptno,d.deptname,d.deptloc,COUNT(e.empno),AVG(e.empsal),MAX(e.empsal),MIN(e.empsal) FROM
dept d RIGHT JOIN emp e
ON e.deptno=d.deptno
GROUP BY d.deptno,d.deptname,d.deptloc

-- 2.2.1 对emp表进行分组统计按照部门进行分组统计平均工资，最低工资，最高工资
SELECT DISTINCT t1.*,t2.*  FROM 
(
	SELECT d.deptno,d.deptname,d.deptloc
		FROM emp e1 LEFT JOIN dept d 
		ON e1.deptno=d.deptno 
		GROUP BY d.deptno,d.deptname,d.deptloc
) t1 LEFT JOIN
(
	SELECT e2.deptno,AVG(e2.empsal),MIN(e2.empsal),MAX(e2.empsal) FROM emp e2 GROUP BY e2.deptno
) t2 ON t1.deptno=t2.deptno;