普通查询+聚合函数的使用(8个例子,数值和字符串的比较)

回顾普通查询+聚合函数的使用

表数据

例子1

例子2

例子3

例子4

例子5

例子6

例子7(数值和字符串的比较)

例子8

回顾普通查询+聚合函数的使用

之前我们介绍过聚合函数 --mysql分组查询 -- 聚合函数(介绍,使用),group by使用,分组聚合统计(使用,having介绍),where和having的对比,mysql一切皆表的概念-CSDN博客

表数据

--scott_data.sql · YoungMLet/scott_data - Gitee.com

例子1

查询工资>500或岗位为岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

梳理一下,我们需要同时满足两种条件:
select * from emp where (sal>500 or job='MANAGER') and (left(ename,1)='J');select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';

例子2

按照部门号升序而雇员的工资降序排序

order by后面可以跟多列
select * from emp order by deptno asc , sal desc;
最好还是把排序规则显式写出来

例子3

使用年薪进行降序排序

年薪=月薪*12+comm(奖金)

但因为奖金允许为null,所以一旦相加,结果就变成了null(null不参与运算)
所以可以使用ifnull(comm,0),将null值变为0

因为需要先拿出数据,才能排序,所以在select中使用的别名可以在order by中使用:
select ename,sal*12+ifnull(comm,0) as Annual_Salary from emp order by Annual_Salary desc;

例子4

显示工资最高的员工的名字和工作岗位

之前在介绍聚合函数时,就已经讲过这类sql场景该如何查询:

可以将select查出的结果作为筛选条件 -- 其实这就是子查询,mysql允许select嵌套在另一个sql语句中使用
select ename,job from emp where sal=(select max(sal) from emp) ;

例子5

显示工资高于平均工资的员工信息

这个和前一个是类似的问题

因为本身没有平均工资这个数据,所以需要先计算平均工资,然后再以这个值作为基准值进行比较
select * from emp where sal>(select avg(sal) from emp);

例子6

显示每个部门的平均工资和最高工资

因为针对的是每个部门,而不是整个公司,所以我们需要对它进行分组

以部门编号为分组条件
分组=分表,分成多个子表后,我们对每个子表都进行聚合统计,然后一起显示
select deptno,avg(sal),max(sal) from emp group by deptno ; 
如果觉得平均工资显示的值不太好看,可以使用format来控制其精度+添加千位分隔符,再重命名一下:
select deptno,format(avg(sal),2) avg_sal,format(max(sal),2) max_sal from emp group by deptno;

例子7(数值和字符串的比较)

显示平均工资低于2000的部门号和它的平均工资

要显示平均工资<2000的部门号:
首先要计算出每个部门的平均工资,然后在这个基础之上进行筛选,跟上面类似:
select avg(sal) avg_sal from emp group by deptno having avg_sal<2000;
然后将筛选出来的数据,显示出部门号和平均工资:
当我们添加格式化函数后,会发现查询的结果不符合我们的预期:

是因为format将数值转换成了字符串
当数值和字符串比较时,会将字符串又转回数字,而avg_sal中,字符串的第二位都是逗号,所以数值转回到这一位就结束了
所以三行数据得到的数值分别变成 -> 2,2,1,都比2000小,所以全部显示

如果我们想要得到预期结果,不能在比较结果中使用别名:
select deptno,format(avg(sal),2) avg_sal from emp group by deptno having avg(sal)<2000;