MySQL学习4：多行函数

1.五个常用函数

①avg/sum

• SELECT AVG(salary) AS ‘平均工资’,SUM(salary) AS ‘总计工资’,AVG(last_name) AS ‘0’
  FROM employees
• 注意点：avg和sum只适用于数值型数据。

②max/min

• SELECT MAX(salary) AS ‘最高工资’,MIN(last_name) AS ‘靠前的名字’,MIN(hire_date) AS ‘最早入工’
  FROM employees
• 注意点：适用于数值型，字符串，日期。可以排序比较

③count

• SELECT COUNT(*),COUNT(1),COUNT(commission_pct)
  FROM employees
• 注意点：count（具体字段）如果为null的话不计入总行
• 注意点：三者执行效率 *=1>commission_pct

④公式

• avg=sum/count成立
• 三者都不考虑null的情况
• 注意：具体字段的求全部人的平均值，需要用count(*)
• SELECT AVG(salary) ,SUM(salary)/COUNT(*)#没有null两者相等
  FROM employees
  SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(1)#后者比前者小，除数算了null的个数
  FROM employees

2.group by分组查询

①group by的简单使用

• 需求：求各个部门的平均工资
  SELECT department_id,AVG(salary)
  FROM employees
  GROUP BY department_id
• 需求：求各个部门的各个job_id的平均工资
  SELECT department_id,job_id,AVG(salary)
  FROM employees
  GROUP BY department_id,job_id
• 注意：两个id可以交换顺序，没差别，先分哪个无所谓
• 注意：select声明的字段一定需要在groupby中，而groupby声明后不一定要在select中使用
  SELECT department_id,job_id,AVG(salary)#此时一个job_id对应多条department_id,如何选取无意义
  FROM employees
  GROUP BY job_id

3.having的过滤数据

①having简单实例

• 需求：部门中最高工资比10000高的部门信息
  SELECT department_id,MAX(salary)
  FROM employees
  GROUP BY department_id
  HAVING MAX(salary)>10000

②注意点

• having放在groupby的后面，开发中使用having的前提是使用了groupby
  过滤条件中使用聚合函数，必须写在having中，写在where中无意义

③对比where和having的过滤

• having能够过滤的更加广泛
  在未分组时提前where过滤可以提高效率
• 结论：当过滤条件中有聚合函数，必须写在having中，过滤条件中无聚合函数，则写在where中

4.select语句的完整结构

①声明顺序

• select…from…where…group by…having…order by…limit…
  注意sql99中利用left join …on… 替换from…，且连接条件写在on，where写过滤条件

②执行顺序

• from(笛卡尔积)…on（过滤掉数据）…left join(填补数据)…where(过滤数据)…
  group by(数据分组)…having(过滤组的数据)…select(选取数据)…distinct(筛选)…
  order by(数据排序)…limit(数据分页)…

③利用执行顺序解决问题

• 没有涉及聚合函数的条件放在where的效率更高：先过滤数据，再去分组，使得后面操作变少
  select中定义的别名只能在order by中使用：因为只有其在后面执行