[打卡]多表查询333

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

-- 计算表中有多少条记录
-- S1:COUNT(*)

-- S2:COUNT(1) 相当于COUNT(常数)

-- S3:COUNT(具体的字段)
#注意:计算指定字段出现的个数时，是不计算NULL值的
SELECT COUNT(commission_pct)
from employees

SELECT commission_pct
from employees
where commission_pct is not null
-- 35条
SELECT commission_pct
from employees
where commission_pct is null
-- 72条


#公式：AVG=SUM/COUNT
-- AVG是求平均数的
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

-- 查询每一个中国人的平均工资是多少
#若有2亿人目前没有工作，，求平均数时也要将这2亿算入
SELECT SUM(commission_pct)/COUNT(1)
from employees

-- 查询各部门平均工资
#错误写法
SELECT AVG(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



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