[打卡]daka

#COUNT

#作用：计算指定字段在查询结果中出现的个数

Select count(employee_id),count(salary),count(salary*12)
from employees;

select count(1)
from employees;

select count(4)
from employees;

#计算表中有多少条记录，如何实现？

#方法1：COUNT(常数)

#方式2：COUNT(*)

#方式3：COUNT(具体的字段)不一定对

#注：计算指定字段出现的个数，是不计算空值null；

select count(commission_pct)
FROM employees;

select commission_pct
from employees
where commission_pct is not null;

#公式：AVG=sum/count

select AVG(salary),Sum(salary)/COUNT(salary),

avg(commission_pct), sum(commission_pct)/count(commission_pct),

sum(commission_pct/107)

from employees;

#查询：查询公司中的平均奖金率

#不对的
select avg(commission_pct)
FROM employees;

#以下正确
select sum(commission_pct)/count(1)
from employees;

#GROUP BY

#需求：查询各个部门的平均工资，最高工资
#以下不对，这是把所有107员工看成1各部门
select avg(salary),max(salary)
from employees;

#以下正确

select department_id,avg(salary),max(salary)
from employees
GROUP BY department_id;

#要求:查寻各部门

select job_id,avg(salary)
from employees
GROUP BY job_id;
-- ------------------------  
select department_id,job_id,avg(salary)
from employees
GROUP BY department_id,job_id;

select department_id,job_id,avg(salary)
from employees
GROUP BY job_id,department_id;

#错误的
select department_id,job_id,avg(salary)
from employees
GROUP BY department_id;

#结论1：select中出现的非组函数的字段必须声明在GROUP BY里

#反之，group by中声明的字段可以不出现在select里

#结论2：group by声明在from后面，在where的后面，order by 在前面，在limit的前面