[打卡]徐某梁

#COunt
#计算制定字段在查询结果中出现的个数
SELECT count(employee_id),count(salary)
,count(salary*12)
from employees;

SELECT count(1)
from employees;

SELECT count(985)
from departments;

SELECT count(*)
from employees;

#计算记录
#count(&)

#count(100)

#count(coployee_id) 不一定对
#注意：计算指定字段的个数是，因为计算的是个数
SELECT count(commission_pct)
from employees;

SELECT commission_pct
from employees
where commission_pct is not null;

#avg
#公式：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)/107
from employees;

#GROUP BY的使用
#查询各个部门的平均工资,ui高工资
SELECT department_id,avg(salary),max(salary)
from employees
GROUP BY department_id;

#需求：查询各个job_id的平均工资
SELECT job_id,avg(salary)
from employees
GROUP BY job_id;

#查询department_id,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;
#错误的！#结论1 select 中出现的非组函数要在GROUP BY中出现
#反之 GROUP BY 中声明的可以不在 select 中
#结论2 GROUP BY 声明在 from的后边，GROUP BY 在where的后边
#在 ORDER BY 的前边 在limit的后边
SELECT department_id,job_id,avg(salary)
from employees
GROUP BY department_id;