MySql中流程控制函数/统计函数/分组查询用法解析

吾爱主题阅读：191 2024-04-05 16:22:04 评论：0

路漫漫其修远兮，吾将上下而求索，又到了周末，我继续带各位看官学习回顾mysql知识。

上次说到了流程控制函数，那就从流程控制函数来继续学习吧！

1 2 3 4 5 6 #五.流程控制函数 #1.if函数:if else 的效果 if(条件表达式,成立返回1,不成立返回2) #与java三元运算相同 select if(10>5, '大' , '小' );

1 2	`select` `last_name,commission_pct,if(commission_pct` `is` `null` `,` `'没奖金呵呵'` `,` `'有奖金嘻嘻'` `)` `as` `备注` `from` `employees;`

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 #2. case 函数的使用一: swirch case 的效果【等值判断】 回顾:switch(变量或表达式){ case 常量1:语句1;break; ... default :语句n;break; } mysql中 case 要判断的字段或表达式或变量 when 常量1 then 要显示的值1或者语句1;[语句要加分号，值不用加] when 常量2 then 要显示的值2或者语句2; ... slse要显示的值n或者语句n;[默认值] end [结尾] case 在 select 后面相当于表达式用，后面不能放语句，只能是值. 在后续的学习中，存储过程与函数内就可以单独,用不搭配slect,就用语句. 先是表达式的操作 案例:查询员工的工资,要求 部门号=30，显示的工资为1.1倍 部门号=40，显示的工资为1.2倍 部门号=50，显示的工资为1.3倍 其他部门，显示的工资为原工资

1 2 3 4 5 6 7 8 select salary 原始工资,department_id, case department_id when 30 then salary*1.1 when 40 then salary*1.2 when 50 then salary*1.3 else salary end as 新工资 from employees;

1 2 3 4 5 6 7 8 9 #2. case 函数的使用二:类是于多重if【区间判断】 回顾java中:if(条件1){ 语句1; } else if(条件2){ 语句2; }... else { 语句n; }

1 2 3 4 5 6 7 8 9 10 11 12 mysql中: case when 条件1 then 要显示的值1或者语句[语句后面要加分号;] when 条件2 then 要显示的值2或者语句[语句后面要加分号;] ... else 要显示的值n或语句n end 案例:查询员工的工资的情况 如果工资>20000,显示a级别 如果工资>15000,显示b级别 如果工资>10000,显示c级别 否则,显示d级别

==============流程函数到此结束，要想熟练运用还需要勤加练习。===============

提供几道习题供读者试试手！

1 2 3 4 #计算有几种工资。 select count ( distinct salary), count (salary) from employees;

1 2	`#5.` `count` `函数的详细介绍` `select` `count` `(salary)` `from` `employees;`

1 2	`#` `count` `(统计所有列)` `select` `count` `(*)` `from` `employees;`

1 2	`#把表的行生成一个列每一列都是1。统计1的总数。` `count` `里面可以用任意常量值。` `select` `count` `(1)` `from` `employees;`

1 2 3 4 #考虑到效率问题: #5.5之前都是myisam 下 count (*)最高，计数器直接返回 #5.5之后默认都是innodb下 count ()与 count (1)都差不多， 比 count (字段)效率高，如果是字段要判断字段是否为 null 。

1 2	`#6.和分组函数一同查询的字段有限制` `select` `avg` `(salary),employee_id` `from` `employees;`

1 2 3 #1.查询公司员工工资的最大值，最小值，平均值，总和。 select max (salary), min (salary), avg (salary), sum (salary) from employees;

1 2 3 select max (salary) as 最大值, min (salary) as 最小值, round( avg (salary)) as 最小值, sum (salary) as 总和 from employees;

1 2 3 #2.查询员工表中最大入职时间和最小入职时间的相差天数。 #datediff计算天数。dateiff(参数1-参数2) select datediff(now(),( '1995-1-1' ));

1 2	`select` `datediff(` `max` `(hiredate),` `min` `(hiredate))` `as` `diffrence` `from` `employees;`

1 2 3 4 #3.查询部门编号为90的员工个数。 select count (*) as 个数 from employees where department_id=90;

1 2 3 4 5 6 7 8 9 10 11 12 #进阶5.分组查询 group by 子句语法。 可以使用 group by 子句将表中的数据分成若干组。 语法: select 分组函数，列(要求出现在 group by 的后面) from 表 【 where 筛选条件】 grop by 分组的列表 【 order by 子句】 注意: 查询列表必须特殊,要求是分组函数和 group by 后出现的字段 特点: 1.分组查询中的筛选条件分为两类

1 2 3 2. group by 子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求) 也支持表达式或函数分组(用的较少) 3.也可以添加排序(排序放在整个分组查询的最后)

1 2 3 #引入:查询每个部门的平均工资，保留两位小数。 select round( avg (salary),2) as 平均工资 from employees;

1 2 3 4 5 #简单的分组查询，添加分组前的筛选 where #案例1:查询每个工种的最高工资。 select max (salary) as 最高工资,job_id as 工种编号 from employees group by job_id;

1 2 3 4 #案例2:查询每个位置上的部门个数。 select count (*) as 总数,location_id from departments group by location_id;

1 2 3 4 5 6 #添加筛选条件 #查询邮箱中包含a字符的，平均工资保留两位小数，每个部门的平均工资 select email,round( avg (salary),2),department_id from employees where email like '%a%' group by department_id;

1 2 3 4 5 #案例2:查询每个领导手下员工有奖金的的最高工资 select max (salary),manager_id from employees where commission_pct is not null group by manager_id;

1 2 3 4 5 6 添加复杂的筛选,添加分组后的筛选 having #案例1：查询哪个部门的员工个数>2 #①查询每个部门的员工个数 select count (*),department_id from employees group by department_id;

1 2 3 4 5 #②根据①的结果，查询哪个部门的员工个数>2 select count (*) as 总数,department_id as 部门编号 from employees group by department_id having count (*)>2;

1 2 3 #案例2.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资。 #①查询每个工种有奖金的员工的最高工资。 #原始表能筛选的就放在 from 后面用 where 。

1 2 3 4 select job_id as 员工编号, max (salary) as 最高工资 from employees where commission_pct is not null group by job_id;

1 2 3 4 5 6 #②根据①结果继续筛选，最高工资>12000。 select job_id as 员工编号, max (salary) as 最高工资 from employees where commission_pct is not null group by job_id having max (salary)>12000;

1 2 3 4 5 6 #案例3.查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资。 #①查询领导编号>102的每个领导手下的最低工资 select min (salary) as 最低工资,manager_id as 领导编号 from employees where manager_id > 102 group by manager_id;

1 2 3 4 5 6 #②最低工资大于5000的。 select min (salary) as 最低工资,manager_id as 领导编号 from employees where manager_id > 102 group by manager_id having 最低工资 > 5000;

1 2 3 4 5 6 7 #案例:按员工姓名的长度分组,查询每一组的员工个数，筛选员工个数>5的有哪些 #mysql内 group by 与 having 后面支持别名， where 不支持别名。但oracle数据库的 group by 与 having 是不支持别名的。 select length(concat(last_name,first_name)) as 姓名长度, count (*) as 个数 from employees group by 姓名长度 having 姓名长度 > 5;

1 2 3 4 5 #按多个字段分组 #案例:查询每个部门工种的员工的平均工资(保留两位小数)。 select round( avg (salary),2) as 平均工资,department_id as 部门,job_id as 工种 from employees group by 部门,工种;

1 2 3 4 5 6 #添加排序 #案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示。 select round( avg (salary),2) as 平均工资,department_id as 部门,job_id as 工种 from employees group by 工种,部门 order by 平均工资 desc ;

1 2 3 4 5 6 #案例:查询部门不能为空的，每个部门每个工种的员工的平均工资,并且按平均工资的高低显示。 select round( avg (salary),2) as 平均工资,department_id as 部门,job_id as 工种 from employees where department_id is not null group by 工种,部门 order by 平均工资 desc ;

1 2 3 4 5 6 7 #案例:查询部门不能为空的，每个部门每个工种的员工的平均工资高于10000的,并且按平均工资的高低显示。 select round( avg (salary),2) as 平均工资,department_id as 部门,job_id as 工种 from employees where department_id is not null group by 工种,部门 having 平均工资 > 10000 order by 平均工资 desc ;

加强练习:

1 2 3 4 5 #1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序。 select job_id, max (salary), min (salary), avg (salary), sum (salary) from employees group by job_id order by job_id asc ;

1 2 3 4 5 6 #2.查询员工最高工资和最低工资的差距(difference)。 select max (salary) - min (salary) as difference from employees; ======================================================= select max (salary) as 最高, min (salary) as 最低, max (salary)- min (salary) as difference from employees;

1 2 3 4 5 6 #3.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不能计算在内。 select min (salary),concat(last_name,first_name),manager_id from employees where manager_id is not null group by manager_id having min (salary)>6000;