《SQL高级应用和数据仓库基础（MySQL版）》学习笔记 ·010【高级查询】

一、分组函数（聚合函数）

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 emp
where 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 e
join dept d
on e.deptno = d.deptno
group by d.dname
order by 2;

-- 按每个部门、每个岗位显示部门名称、岗位、平均工资，并按照平均工资进行升序排列
select d.dname 部门名称, e.job 岗位, avg(e.sal) 平均工资
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname, e.job
order by avg(e.sal);

注意点

• group by中出现的列，尽可能出现在select中
• group by中未出现的列，在select中应该使用聚合函数

5、having子句

作用
使用having子句排出组结果

使用示例

-- 查询平均工资大于2900的部门
select d.dname, max(sal)
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname
having max(sal) > 2900

-- 
select job, avg(sal) 平均工资
from emp
where job in ('CLERK', 'SALEMAN', 'MANAGER')
group by job
order by avg(sal);

注意点

• where子句不能限制分组函数的条件，必须用having子句来限制
• SQL语句执行顺序：
  select(获取表数据)→where(筛选)→group by(分组)→having(筛选)→select(整理分组后的数据)→order by(排序)

二、子查询（嵌套查询）

1、单行子查询

使用示例

-- 查询比JONES员工工资高的其他员工
select ename
from emp
where sal > (select sal from emp where ename = 'JONES');

-- 错误示例
select ename
from emp
where sal > (select sal from emp where ename in ('JONES', 'SCOTT'));

注意点

• 不能拿单个值和嵌套查询结果的多行值进行大小比较

2、多行子查询

说明
子查询返回的记录条数也可以是多条，这时候往往就需要使用多行操作符（in、any、all）

使用示例

-- 多列相等也可以直接等值判断
select empno, ename, sal
from emo
where (ename, sal) = (select ename, sal from emp where empno = 7902);

-- 查询管理者
select ename, sal
from emp
where empno in (select mgr from emp)

-- 查询工资比JONES或SCOTT中的某一个大的所有员工
select ename, sal
from emp
where sal > any (select sal from emp where ename in ('JONES', 'SCOTT'));

-- 查询工资比JONES或SCOTT中的所有都大的所有员工
select ename, sal
from emp
where sal > all (select sal from emp where ename in ('JONES', 'SCOTT'));

-- 查询管理者的姓名和薪水
select ename, sal
from emp e
where empno exists (select mgr from emp m where m.mgr = e.empno)

-- 查询不是管理者的员工的姓名和薪水
select ename, sal
from emp e
where empno not exists (select mgr from emp m where m.mgr = e.empno)

注意点

• 对于any和all，可以近似理解为数学意义上的存在和任意
• 注意null值的处理，要使用exists关键字而不是in关键字