数据库基本查询(函数)

汇总(聚合函数)

1. 查询所有50部门平均工资.Select * from employees where deparement_id = 50

在mysql中一共内置了 5 个汇总函数 .  

• sum 求和  
• avg求平均值
• max求最大值
• min求最小值
• count计数

1. SELECT AVG(salary) as avg_salarly form employees where department_id = 50
2. select COUNT(Eemployee_id) as emp_count FROM employees
   1. Eemployee_id COUNT :在所选列有数据为空时不会统计
   2. 
   3. COUNT(1) 查询出所有的数据;不会漏数据
3. SELECT  AVG(score) FROM scores  默认不算null
   1. AVG求平均时,默认忽略null值  SELECT AVG(IFNULL(score,0)) from scores
   2. COUNT()

分组查询

group by 分组    order by 排序

对每个部门求平均工资: 

SELECT department_id, AVG(salary) avg_salary   FROM employees  GROP BY department_id

SELECT department_id, AVG(salary) avg_salary   FROM employees  EHERE department_id IS NOT NULL GROP BY department_id ORDER BY avg_salary DESC

 

求每个部门平均工资>10000的

HAVING 必须和GROP BY 一起使用

SELECT department_id, AVG(salary) avg_salary   FROM employees  WHERE department_id IS NOT NULL GROP BY department_id HAVING avg_salary>=10000

WHERE 和HAVING的区别  

1. HAVING 必须和 GROP BY 一起使用 WHERE->GROP BY ->HAVING
2. WHERE对所有的记录操作 
3. HAVING 是对分组后的记录再分组  WHERE 在分组之前就运行了  

统计每个部门2014年入职员工人数

1. SELECT department_id,COUNT(1) as cnt  FROM employees WHERE YEAR(hiredate) =2014 AND department_id IS NOT NULL GROUP BY department_id 

统计至少有两名员工工资在10000以上的部门编号以及对应符合条件的员工人数   

1. select department_id,COUNT(1) as cnt  FROM employees WHERE salary>10000 AND department_id IS NOT NULL GROUP BY department_id
   HAVING cnt>=2     

需求:统计每个部门每年分别的入职人数

1. select  department_id,year(hiredate),COUNT(1)
   FROM employees 
   where department_id is not NULL
   GROUP BY department_id,YEAR(hiredate)

总结: