USE atguigudb; -- count -- 计算字段中查询中出现的xx个数 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（1） -- 3 count（具体的字段）不一定对 SELECT COUNT(commission_pct) FROM employees; SELECT commission_pct FROM employees WHERE commission_pct IS NULL; 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)/COUNT(1) FROM employees; -- group by 的使用 -- 查询各个部门平均工资 -- 以下是不对的 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; -- 50号部门 各个工种的工资 SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id,job_id;
