[打卡]瓜瓜瓜瓜瓜瓜

-- 作用：计算指定字段在查询结果中出现的个数
SELECT COUNT(employee_id),COUNT(salary),COUNT(salary*12)
from employees;

SELECT COUNT(1)
from employees;

SELECT COUNT(4)
from employees;

SELECT COUNT(*)
from employees;

-- 计算表中有多少条记录，如何实现？
-- 方式1：COUNT(常数)

-- 方式2：COUNT(*)

-- 方式3：COUNT(具体字段)不一定对(有空的)
-- 注意：计算制定具体字段出现的个数时，是不计算null值的

SELECT COUNT(commission_pct)
FROM employees; -- 应该是107条，结果是35条，有空的

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

SELECT 72+35

-- 空值： AVG=SUM/COUNT（用空值和无空值都适用）
-- SUM(commission_pct)/COUNT(commission_pct)=AVG(commission_pct)
-- 因为有空值
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)/107
from employees;
-- 或者
SELECT SUM(commission_pct)/COUNT(1)
from employees;

-- GROUP BY的使用
-- 需求：查询各个部门的平均工资，最高工资
SELECT AVG(salary),MAX(salary)
FROM employees;-- 不对，把所有员工看成了一个部门

-- 正确做法
SELECT department_id, AVG(salary),MAX(salary)
from employees
GROUP BY department_id;

-- 查询各个job_id的平均工资

-- 比如50号部门，各个工种的工资
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;

-- 错误的 眼见不一定为实
SELECT department_id,job_id, AVG(salary)
from employees
GROUP BY department_id;

'select中出现的非函数的字段必须声明在group by中
-- 反之：group by 中声明的字段可以不出现在select中'

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