NNNN

#COUNT 查询结果中的出现的个数

SELECT COUNT(employee_id),COUNT(salary),COUNT(salary*12)
FROM employees;

SELECT COUNT(1)
FROM employees;

SELECT COUNT(*)
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 平均值
#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 SUM(commission_pct)/COUNT(1)
FROM employees;

#GROUP BY
#需求 查询各个部门的最高工资

#不对 这是把107个员工看成一个部门
SELECT AVG(salary),MAX(salary)
FROM employees;

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

#查询各个部门各个job_ID的平均工资

SELECT job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;                                   

#结论1 SELECT中出现的非组函数的字段必须声明在group by里
#反之 GROUP BY 中声明的字段可以不出现在select

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