学习笔记 2021.12.9cont

2024-03-02 13:46:40

2021.12.9

聚合函数

常见的聚合函数

具体的count的使用场景的比较（默认具体字段没有非空的）。这里涉及到更多底层方面的东西，到后面索引的时候再去具体了解，这里就大概知道各自的结果就可以了：

其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

group by

需求：查询各个不同部门的聚合数据。

示例：

此时也可以多个列同时group by，即在一个类中再细分，示例如下：

使用的注意事项：

select中出现的非组函数的字段必须声明在group by中，反之则不是必然要求。
group by声明在from后面，where后面，order和limit前面。
在后面添加with rollup可以多显示一个总体的计算后的结果。知道有这么个东西就好，一般也不常用。

having的使用

作用：也是过滤数据的。

过滤分组：HAVING子句

行已经被分组。
使用了聚合函数。
满足HAVING 子句中条件的分组将被显示。
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

显示各个部门中最高工资比10000高的部门信息。

使用tips:

如果过滤条件中使用了聚合函数，则必须用having来替换where，作为一种习惯去适应。但是如果没有聚合函数，两种都可以，但是推荐用where。
且having必须在group by后面。

下面的这种方式也是合理的，注意两种筛选条件的区分：

但是这种方式也不推荐，最后还是按照规范来写。

where和having的对比

having的使用范围更广。
没有聚合函数时，where的效率要比having好。

具体的分析后面涉及到底层的时候再去详细了解。

	优点	缺点
WHERE	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
HAVING	可以使用分组中的计算函数	在最后的结果集中进行筛选，执行效率较低

select的执行过程

常见的sql99的全部包含的语法：

但执行的顺序却是如下，而不是上面的编写顺序

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

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

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

即是先找表，再分组和筛选。然后再按照select的条件来呈现。
这时就可以去理解select和having的功能区别了，where在遍历的过程中即实行了筛选的操作，这样在后面的group和having操作就可以省略很多，但是如果在having中进行实现的话，则会对所有的分组进行计算，但是实际上也不需要其他组的计算。

子查询

即理解为查询的嵌套。子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

比如下面这种问题即是符合子查询的场景:

子查询的写法：

即可以直观的看到是查询的嵌套。

子查询的基本语法结构：

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用。
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

单行子查询和多行子查询。即内查询的结果是一个还是多个。

相关子查询和不相关子查询。即内查询是否被执行多次。相关子查询的需求：查询工资是否大于本部门平均工资的员工信息。

单行子查询

最好理解的一种

单行比较操作符

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

这里就看一个例子就行

Q:查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN
		  (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (141)
AND     department_id IN 
		  (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (141)
AND	employee_id NOT IN(141);

having中的子查询

直接看例子

Q：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);```

case中的子查询

题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id, last_name,
       (CASE department_id
        WHEN
             (SELECT department_id FROM departments
	      WHERE location_id = 1800)           
        THEN 'Canada' ELSE 'USA' END) location
FROM   employees;

其他需要注意的

子查询中的空值问题

SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');

子查询不返回任何行

非法使用子查询

SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

多行子查询使用单行比较符

多行子查询

也称为集合比较子查询
内查询返回多行
使用多行比较操作符

多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

注意此时in对于单行查询自然也是可以的。

例子

返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE job_id <> 'IT_PROG'AND salary < ALL(		SELECT salary		FROM employees		WHERE  	job_id = 'IT_PROG'		);

查询平均工资最低的部门id

注意聚合函数是没法去嵌套的。

SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary)=(SELECT MIN(asal)FROM(SELECT department_id,AVG(salary) asalFROM employeesGROUP BY department_id) dd);

这种情况注意一点就是因为group by后生成的也是一系列的数据，可以将其看作数据去比较，也自然可以看作表填到from后面的子循环里面，这也是上面方法的思路。

码农公寓