⑤ Mysql-分组查询以及子查询

分组查询以及子查询

一、分组查询

1）group by子句 用于分组查询

【例】查询每个部门员工的平均工资

select deptno,AVG(sal) from emp
group by deptno;

注意：
1、如果在select后面有组函数，又有普通列，需要使用group by子句，并且所有的普通列必须都
出现在group by子句中
2、group by子句中的普通列可以不在select后面

2）having 子句用于限定分组查询结果

【例】查询员工表中各部门最高工资大于2900.00的部门号及最高工资

select deptno,MAX(sal) from emp
group by deptno
having MAX(sal) > 2900.00;

注意：where后面不能使用组函数；只要有having子句出现就一定有group by ，group by后面不一定有having子句

总结SQL语句基本结构：

select 列1,…	查询
from 表1,表2,…	从指定表查询指定列
where 条件 and/or 条件	限定查询结果
group by 列1,列2,…	分组查询
having 条件(组函数)	限定分组查询结果
order by 列1,列2,… (升序/降序)	排序

二、子查询

【例】找出工资高于Jones的雇员

#找出JONES的工资
select sal from emp 
where ename = 'JONES';  #得到JONES工资为2950.00

#找出工资高于2975.00
select ename,sal from emp
where sal > 2975.00;

#使用子查询
select ename,sal from emp
where sal > (select sal from emp where ename = 'JONES');

1) 单行子查询：返回一行记录

 使用单行比较运算符  =、>、 >=、 < 、<= 、<>(表示不等于)

如: 查询职位与’MARTIN’不同的员工的姓名，工资，职位

select ename,sal,job from emp
where job <> (select job from emp where ename = "MARTIN");

在子查询中使用HAVING子句

【例】查询部门最低工资高于20号部门最低工资的部门及最低工资及最高工资

select deptno,MIN(sal),MAX(sal) from emp
group by deptno
having MIN(sal) > (select MIN(sal) from emp where deptno = 20);

2) 多行子查询

子查询返回多行一列

使用多行比较运算符

• in(子查询语句) 等于列表中的任何值
• any 比较子查询返回的每一个值，只要其中一个值满足条件就返回true
• <any 指小于最大值
• > any 指大于最小值
• all 比较子查询返回的每一个值，要其中每一个值都要满足条件才返回true
• >all 指大于最大值
• <all 指小于最小值

① in 等于列表中的任何值

如: 查询与’ALLEN’或’SMITH’职位相同的员工的姓名，职位

select ename,job from emp 
where job in(select job from emp where ename in("ALLEN","SMITH"));

② any 比较子查询返回的每一个值，只要其中一个值满足条件就返回true

如: 查询员工表中工资高于任一部门平均工资的员工的姓名，工资

select ename,sal from emp 
where sal > any(select AVG(sal) from emp group by deptno);

③ all 比较子查询返回的每一个值，要其中每一个值都要满足条件才返回true

如: 查询入职日期不早于所有部门员工最晚入职的员工的姓名，入职日期

select ename,hiredate from emp
where hiredate >= all(select MAX(hiredate) from emp group by deptno);

3) 多列子查询

子查询返回多行多列
多列子查询使用in运算符

【练习】查询每个部门工资最高的员工的姓名，工资，部门号

select ename,sal,deptno from emp
where (deptno,sal) in(select deptno,MAX(sal) from emp group by deptno);

查询结果

4) 分页子查询

limit 起始行号,返回行数
语法：

sql语句  limit  起始行(第一行的行号为0)  返回行数量

如: 查询员工表中工资收入排名前五的员工的姓名，工资

select ename,sal from emp
order by sal desc
limit 0,5;

可以把子查询的结果当作一张表使用
如: 查询工资高于自己部门平均工资的员工的姓名，职位，工资，部门号

select e.ename,e.job,e.sal,e.deptno from emp e,(select deptno,AVG(sal) avgsal from emp group by deptno) a 
where e.deptno = a.deptno
and e.sal> a.avgsal;

总结SQL语句基本结构：

select	列1,…
from 表1,表2,…	从指定表查询指定列
where 条件 and/or 条件	限定查询结果
group by 列1,列2,…	分组查询
having 条件(组函数)	限定分组查询结果
order by 列1,列2,… (升序/降序)	排序
limit 起始行号,返回行数	分页子查询