聚合函数
min 最小 max 最大sum 总和 avg 平均数count 总记录数
1.查询员工最早的入职时间（最小）和最晚入职时间（最大）
select MIN(hiredate) ,MAX(hiredate)from emp
2.查询20号部门的平均工资
select AVG(e.sal) from emp e JOIN dept d on(e.deptno =d.deptno) where d.deptno=20
3.查询20号部门所有员工每个月的工资总和
select SUM(sal) from emp e join dept d on(e.deptno=d.deptno) where d.deptno=20
4.查询总人数
select COUNT(*) from emp
5.查询有奖金的总人数
select COUNT(comm) from emp
select COUNT(IFNULL(comm,0)) as 奖金总人数 from emp;
6.查询部门20的员工，每个月的工资总和及平均工资。
select SUM(sal) as 总工资 ,AVG( sal) as 平均工资 from emp,dept
where emp.deptno =dept.deptno and dept.deptno=20
7.查询工作在CHICAGO的员工人数，最高工资及最低工资。
select COUNT(e.ename),MAX(sal),MIN(sal) from emp e join dept d on(e.deptno =d.deptno)
8.查询员工表中一共有几种岗位类型。
select COUNT(DISTINCT job) from emp;
分组函数
group by
9.查询每个部门的编号，平均工资
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;
10.查询每个部门每个岗位的工资总和
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY deptno, job;
11.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno as 部门编号,d.dname as 部门名称,COUNT(ename) as 部门人数,MAX(sal) as 最高工资,MIN(sal) as 最低工资,SUM(sal) as 工资总和,AVG(sal) as 平均工资
from emp e join dept d on(e.deptno =d.deptno)
GROUP BY d.deptno;
12.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno as 部门编号,d.dname as 部门名称,COUNT(ename) as 部门人数,MAX(sal) as 最高工资,MIN(sal) as 最低工资,SUM(sal) as 工资总和,AVG(sal) as 平均工资
from emp e join dept d on(e.deptno =d.deptno)
GROUP BY d.deptno , e.job;
13.查询每个经理所管理的人数，经理编号，经理姓名。
select e.mgr,m.ename,count(e.empno) 管理的人数
from emp e,emp m
where e.mgr= m.empno
group by e.mgr;
14.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
select COUNT(w.job) 管理人数,w.job,m.empno as 经理编号,m.ename as 经理姓名
from emp w LEFT JOIN emp m on(w.mgr =m.empno)
GROUP BY w.mgr;
使用组函数的非法查询
having
select
1.聚合函数/DISTINCT from
2.X join on X
3.where
4.GROUP BY
5.HAVING
6.ORDER BY
1.查询部门人数大于2的部门编号，部门名称，部门人数。
select d.deptno,d.dname,COUNT(e.ename) from emp e join dept d
on(e.deptno =d.deptno)
GROUP BY d.deptno
having COUNT(e.ename)>2
2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
select d.deptno as 部门编号,d.dname as 部门名称,COUNT(ename) as 部门人数,AVG(e.sal) as 部门平均工资 from emp e join dept d
on(e.deptno =d.deptno)
GROUP BY d.deptno
HAVING COUNT(ename)>2 and AVG(sal)>2000
ORDER BY COUNT(ename) ASC
子查询
子查询简单的理解就是在where子句，having子句或者from子句中写查询语句（select 子句）
如果from子句中相当于利用查询语句产生了一个临时表
如果放在where 子句当中的，用于条件判断的，并且聚合函数的
如果having子句当中的，也是用于条件判断的，但是条件里包含聚合函数的。
1.查询工资比Jones工资高的员工信息?
第一步Jones的工资多高？----->得到他的工资
select sal from emp where ename ='Jones'
第二步，查询比他高的。(where 子句中用)
SELECT * FROM EMP WHERE SAL>(select sal from emp where ename='JONES');
2.查询工资最低的员工姓名？
分两步：第一步，查询最低的工资是多少
select min(sal)
from emp;
800
第二步，查询工资等于800的员工的信息
select *
from emp
where sal=(select min(sal)
from emp);
3.查询入职日期最早的员工姓名，入职日期
select ename,hiredate from emp where hiredate =(select MIN(hiredate) from emp)
4.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
select sal from emp where ename='SMITH'
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno =dept.deptno and sal>(select sal from emp where ename='SMITH') and dept.dname='SALES'
5.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select MIN(emp.hiredate) from emp,dept where emp.deptno =dept.deptno and dept.deptno=20
select emp.ename,emp.hiredate from emp,dept where emp.deptno =dept.deptno and emp.hiredate<(select MIN(emp.hiredate) from emp,dept where emp.deptno =dept.deptno and dept.deptno=20)
6.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
所有部门的平均人数=总人数/部门个数=14/3
select emp.deptno,count(*) ct,dname from emp,dept
select dept.deptno,COUNT(*) ct from dept LEFT JOIN emp on(emp.deptno =dept.deptno) GROUP BY dept.deptno
having ct>(select count(empno)/COUNT(DISTINCT deptno) from emp)
课后练习18道题
IN 等于列表中的任意一个
ANY 将值与子查询返回的任意一个值进行比较
ALL 将值与子查询返回的每个值进行比较
<ANY 意味着低于最高值。 >ANY 意味着高于最低值。 =ANY 等同于 IN.
<ALL 意味着低于最低值。 >ALL 意味着高于最高值
ALL 所有
# 那些员工的工资比30号部门所有的员工，工资都要高 (大于所有)
select MAX(sal) from emp where deptno=30 2850
select min(sal) from emp where deptno=30 950
select DISTINCT sal from emp where deptno=30 950，1250,1500,1600,2850
和这个比较 > 这里面最高值
select sal from emp where sal> all (select sal from emp where deptno=30)
和这个比较< 这里面最低值
select sal from emp where sal< all (select sal from emp where deptno=30)
Any 任意
select MAX(sal) from emp where deptno=30 2850
select min(sal) from emp where deptno=30 950
select DISTINCT sal from emp where deptno=30 950，1250,1500,1600,2850
>any：不能低于最小值 950
select sal from emp where sal> any (select sal from emp where deptno=30) ORDER BY sal asc
<any: 不能超过最大值 2850
select sal from emp where sal< any (select sal from emp where deptno=30) ORDER BY sal asc
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
select emp.ename,emp.hiredate
from emp,dept
where emp.deptno =dept.deptno and emp.hiredate > any (select emp.hiredate from emp,dept where emp.deptno =dept.deptno and dept.deptno=10) and dept.deptno!=10
2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
select e.ename, e.hiredate from emp e JOIN dept d on(e.deptno =d.deptno)
where e.hiredate > All (select emp.hiredate from emp,dept where emp.deptno =dept.deptno and dept.deptno= 10 ) and d.deptno!=10
3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
select emp.ename,emp.job from emp,dept where emp.deptno =dept.deptno and job = any (SELECT job FROM emp WHERE deptno = 10) and emp.deptno !=10
课后练习18道题
1.查询部门平均工资在2500元以上的部门名称及平均工资。
2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
7.查询工资最高的员工姓名和工资。
8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
10.显示经理是KING的员工姓名，工资。
11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
12.使用子查询的方式查询哪些职员在NEW YORK工作。
13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
- 显示部门名称和人数
- 显示每个部门的最高工资的员工
- 显示出和员工号7369部门相同的员工姓名，工资
- 显示出和姓名中包含“W”的员工相同部门的员工姓名
