文章目录
一、分组函数（聚合函数）1、介绍2、格式3、常用函数(1).MIN函数、MAX函数(2).SUM函数、AVG函数(3).COUNT函数4、group by子句5、having子句二、子查询（嵌套查询）1、单行子查询2、多行子查询一、分组函数（聚合函数）
1、介绍
分组查询对数据行的集合进行操作并按组给出一个结果
2、格式
select [列名列表,] 分组函数(列)from 表名[where 条件语句][group by 列][having 关于 分组函数(列) 的条件表达式][order by 列 | 表达式]
3、常用函数
(1).MIN函数、MAX函数
使用示例
select min(hiredate), max(hiredate)from emp;
注意点
min或max在没有使用group by子句的情况下，不要查询其他列（因为这样没有意义）
(2).SUM函数、AVG函数
使用示例
select max(sal) 最高工资, min(sal) 最低工资, avg(sal) 平均工资, sum(sal) 工资总和from empwhere job="SALESMAN";-- 发奖金的员工的平均奖金select avg(comm) from emp;-- 所有员工的平均奖金select avg(ifnull(comm, 0)) from emp;
注意点
所有聚合函数在有where子句的情况下，where子句会被执行，然后再分组sum函数、avg函数不会对null值进行统计，可以使用ifnull函数强制对空值进行统计
(3).COUNT函数
使用示例
-- 员工人数select count(*) from emp;-- 拿薪水的员工人数select count(sal) from emp;-- 拿奖金的员工人数select count(COMM) from emp;-- 员工的部门数select count(distinct deptno) from emp;
注意点
count不会为null的列进行计数要消除重复行的计数需要加上关键字distinct
4、group by子句
使用示例
-- 查询所有部门的平均工资，并进行升序排列select d.dname 部门名称, avg(sal) 部门平均工资from emp ejoin dept don e.deptno = d.deptnogroup by d.dnameorder by 2;-- 按每个部门、每个岗位显示部门名称、岗位、平均工资，并按照平均工资进行升序排列select d.dname 部门名称, e.job 岗位, avg(e.sal) 平均工资from emp ejoin dept don e.deptno = d.deptnogroup by d.dname, e.joborder by avg(e.sal);
注意点
group by中出现的列，尽可能出现在select中group by中未出现的列，在select中应该使用聚合函数
5、having子句
作用
使用having子句排出组结果
使用示例
-- 查询平均工资大于2900的部门select d.dname, max(sal)from emp ejoin dept don e.deptno = d.deptnogroup by d.dnamehaving max(sal) > 2900-- select job, avg(sal) 平均工资from empwhere job in ('CLERK', 'SALEMAN', 'MANAGER')group by joborder by avg(sal);
注意点
where子句不能限制分组函数的条件，必须用having子句来限制SQL语句执行顺序：
select(获取表数据)→where(筛选)→group by(分组)→having(筛选)→select(整理分组后的数据)→order by(排序)
二、子查询（嵌套查询）
1、单行子查询
使用示例
-- 查询比JONES员工工资高的其他员工select enamefrom empwhere sal > (select sal from emp where ename = 'JONES');-- 错误示例select enamefrom empwhere sal > (select sal from emp where ename in ('JONES', 'SCOTT'));
注意点
不能拿单个值和嵌套查询结果的多行值进行大小比较
2、多行子查询
说明
子查询返回的记录条数也可以是多条，这时候往往就需要使用多行操作符（in、any、all）
使用示例
-- 多列相等也可以直接等值判断select empno, ename, salfrom emowhere (ename, sal) = (select ename, sal from emp where empno = 7902);-- 查询管理者select ename, salfrom empwhere empno in (select mgr from emp)-- 查询工资比JONES或SCOTT中的某一个大的所有员工select ename, salfrom empwhere sal > any (select sal from emp where ename in ('JONES', 'SCOTT'));-- 查询工资比JONES或SCOTT中的所有都大的所有员工select ename, salfrom empwhere sal > all (select sal from emp where ename in ('JONES', 'SCOTT'));-- 查询管理者的姓名和薪水select ename, salfrom emp ewhere empno exists (select mgr from emp m where m.mgr = e.empno)-- 查询不是管理者的员工的姓名和薪水select ename, salfrom emp ewhere empno not exists (select mgr from emp m where m.mgr = e.empno)
注意点
对于any和all，可以近似理解为数学意义上的存在和任意注意null值的处理，要使用exists关键字而不是in关键字
