高级查询 group by 子查询

1.分组函数

MIN(某列名) 最小   MAX(某列名) 最大    SUM(某列名) 和    AVG(某列名) 平均   

查询所有员工平均工资、最低工资、最高工资、工资和。
SELECT    AVG(sal), MAX(sal),MIN(sal), SUM(sal)
FROM    emp;

COUNT(某列名 或者*)数据行数  代表所查数据有多少行数据 返回满足条件的非空值数据的行数

查询部门30有多少个员工
SELECT COUNT(*)
FROM emp
where deptno = 30;
显示结果为所有记录条数14

组函数中的DISTINCT  会合并重复的数据

查询emp表中工作种类有哪些
SELECT COUNT(DISTINCT job)
FROM emp;

组函数中的空值 IFNULL

其他函数运行时也会自动忽略为空值的数据 可以用ifnull来强制性控制函数包括空值运行

此时 由于函数会自动忽略空值计算 平均奖金是把四个有奖金的人除以4来计算 不是除以总人数14 
SELECT AVG(comm) FROM emp;
所以此时可以强制代码包括空值 使用ifnull函数
SELECT AVG(IFNULL(comm,0)) FROM emp;

2.创建数据组 GROUP BY

通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组

GROUP BY子句指定要分组的列

SELECT 里面如果想写列名 那必须也要写在GROUP BY字句里

按多个列分组

group by 后面可以写多个列名

查询  每个部门  每个岗位 的工资总和
因为select后面需要返回job 和 deptno 所以group by 后面也需要写deptno和job
SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY deptno,job;
但凡出现每个修饰的列名都需要写在GROUP BY里面分组

查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT dname,dept.deptno,job,COUNT(ename),MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM dept
LEFT JOIN emp
ON emp.deptno = dept.deptno
GROUP BY dname,dept.deptno,job
ORDER BY dept.deptno

3.子查询

1.使用子查询

• 单行运算符：>、=、>=、<、<>、<=
• 多行运算符： IN、ANY、ALL

子查询可以嵌于 where from having子句中

查询出比JONES为雇员工资高的其他雇员
SELECT ename
FROM   emp
WHERE  sal > 
        (SELECT sal
         FROM   emp   
         WHERE  ename='JONES');

2.多行子查询

in 意为其中之一 只能用于等值的子查询中

any意为其中之一 可以连接><=来使用

all意为所有 可以连接><=来使用

查询入职日期比10部门其中一个员工晚的员工姓名、入职日期，不包括10部门员工

SELECT ename,hiredate
FROM emp
WHERE hiredate > ANY(SELECT hiredate FROM emp WHERE deptno = 10 )
AND deptno !=10;

查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ename,hiredate
FROM emp
where hiredate > ALL(select hiredate FROM emp WHERE deptno =10)
AND deptno !=10;

查询职位和10部门其中一个员工职位相同的员工姓名，职位，不包括10部门员工
SELECT ename,job
FROM emp
WHERE job in(SELECT job FROM emp WHERE deptno =10)
AND deptno !=10;

3.子查询中的空值

只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符

4.多列子查询

查询每个部门最低工资的员工
select *
from emp
where (deptno, sal) in (select deptno, min(sal) from emp);

5.在 FROM 子句中使用子查询

查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
SELECT  a.ename, a.sal, a.deptno, b.salavg
FROM    emp a, (SELECT   deptno, AVG(sal) salavg
                 FROM     emp
                 GROUP BY deptno) b
WHERE   a.deptno = b.deptno  AND    a.sal > b.salavg;

显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序
SELECT manager.ename,manager.empno,worker.aa
FROM  (SELECT mgr,MIN(sal) aa
						FROM emp
						GROUP BY mgr
						) worker
LEFT JOIN emp manager
ON worker.mgr = manager.empno
WHERE worker.aa >= 3000;

6.相关子查询

可以把子连接条件写在子查询里面

查询比自己部门平均工资高的员工姓名，工资
select ename, sal
from emp e
where sal > (select avg(sal) from emp where deptno = e.deptno);