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

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

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

  #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倍  其他部门，显示的工资为原工资

  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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  #案例:按员工姓名的长度分组,查询每一组的员工个数，筛选员工个数>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;

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

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

  #案例:查询部门不能为空的，每个部门每个工种的员工的平均工资,并且按平均工资的高低显示。  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;

  #案例:查询部门不能为空的，每个部门每个工种的员工的平均工资高于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.查询各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;

  #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;

  #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;