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);