基础篇(3) 聚合函数、子查询

長夜難明
302 阅读3分钟

聚合函数

1. 聚合函数

  • 作用于一组数据，返回一个值

1.1 AVG() SUM()

  • 对数值型数据使用

1.2 MIN() MAX()

  • 对任意数据类型
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

1.3 COUNT()

  • COUNT(*) 和 COUNT(1) 相同，都是直接读行数，返回表中记录总数
  • COUNT(expr) 返回 expr 不为空的记录的总数

2. GROUP BY

  • 将表中数据分组

image.png

2.1 使用多个列分组

  • 按列的组合来分，多个列的话从左到右排
-- 先按department_id排，相同的则按job_id排
SELECT department_id, job_id, SUM(salary) FROM employees 
GROUP BY department_id, job_id ;

2.2 GROUP BY 中使用 WITH ROLLUP

  • 在最后新增一条记录，计算查询出的所有记录的总和
  • 不能和 ORDER BY 同用
GROUP BY department_id WITH ROLLUP

image.png

3. HAVING

过滤分组

  1. 行已经被分组。
  2. 使用了聚合函数。
  3. 满足HAVING 子句中条件的分组将被显示。
  4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
SELECT department_id, MAX(salary) FROM employees
GROUP BY department_id HAVING MAX(salary)>10000 ;

3.1 WHERE 和 HAVING比较

多表连接，筛选和关联的顺序决定效率 image.png

4. SELECT 执行过程

4.1 执行过程

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SELECT 语句执行这些步骤时，每个步骤都会产生一个虚拟表并传入下个步骤。

4.2 执行原理

  • 先执行 FROM 。如果是多表联查，还会经历下面的几个步骤：
    1. 通过 CROSS JOIN 求笛卡尔积，得到虚拟表 vt1-1；
    2. 通过 ON 筛选，在 vt1-1 的基础上进行筛选得到vt1-2；
    3. 添加外部行。如果我们使用的是左连接、右链接或者全连接。在 vt1-2 的基础上增加外部行，得到vt1-3。
  • 如果操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
  • 拿到了查询数据表的原始数据 vt1 ，进行 WHERE 阶段 。根据 vt1 表的结果进行筛选过滤，得到 vt2 。
  • GROUP 和 HAVING 阶段 。在 vt2 的基础上进行分组和分组过滤，得到 vt3 和 vt4 。
  • SELECT 和 DISTINCT 阶段 。提取想要的字段后过滤掉重复的行，分别得到 vt5-1 和 vt5-2 。
  • ORDER BY 阶段 ，得到 vt6 。
  • LIMIT 阶段 ，取出指定行的记录 得到 vt7 。

子查询

1.子查询

查询中嵌套查询 image.png

  • 子查询在主查询前执行完成。
  • 注意事项
    • 子查询要包含在括号内
    • 将子查询放在比较条件的右侧
    • 单行操作符对应单行子查询，多行操作符对应多行子查询

1.1 子查询分类

  • 子查询返回的记录数量
    • 单行子查询
    • 多行子查询
  • 按子查询是否被多次执行
    • 相关子查询
    • 不相关子查询

2. 单行子查询

  • 示例：返回公司中薪水最低的员工的姓名
SELECT last_name FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

3. 多行子查询

3.1 多行比较操作符

image.png

  • 示例：查询平均工资最低的部门Id
SELECT department_id FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
		SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id
);

4. 相关子查询

  • 子查询的执行依赖于外部查询，通常情况是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次。 image.png

示例：查询员工中 工资大于本部门平均工资 的员工 的名字

  • 每查询一条记录都拿着该记录的 department_id 进子查询筛选出相应部门的平均工资
SELECT last_name  FROM employees outer
WHERE salary > 
        (SELECT AVG(salary) FROM employees  WHERE department_id = outer.department_id);

4.1 EXISTS 与 NOT EXISTS关键字

  • 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行：
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目：查询公司管理者的employee_id，last_name

SELECT employee_id, last_name FROM employees e1
WHERE EXISTS ( SELECT * FROM employees e2 WHERE e2.manager_id = e1.employee_id);

5.自连接和子查询

  • 自连接速度比子查询快

子查询相当于对未知表进行查询后的条件判断， 而自连接是对自身已知数据的条件判断。

avatar
文章
阅读
粉丝