数据库的高级查询

内容概述

数据库的高级查询的内容概述：

● 数据统计分析：

（1）使用聚合函数对数据进行统计分析，如求最大值、最小是、平均值等；

（2）单纯用聚合函数只能做全表范围的统计分析，如果想让把记录分组，分别统计，就需要使用GROUP BY 和HAVING这样的分组语句了；

● 多表连接查询： 重点内容！！！

（1）MySQL是关系型数据库，数据是拆分存储在多个数据表中的，所以，我们势必要从多个数据表里提取数据；通过SQL语句的内连接，外连接，就可以实现多表查询；

● 子查询：

（1）子查询是在查询语句里又嵌套一个查询；这是因为在提取数据的时候，有很多不知道的数据产生了依赖关系，就必须先查询一个数据，然后把这个数据作为下次查询的条件；

（2）比如，向查询员工表中，工资比张三工资高的记录：就需要先查询出张三的工资，然后以张三的工资作为查询条件，去查询比张三工资高的员工；

（3） 子查询有很多种；

聚合函数

聚合函数又称作汇总函数，是用来做简单的数据统计的；比如统计员工表里的平均工资，员工表里一共有多少条记录等；

需要说明一点：聚合函数是写在SELECT子句中；根据前面的内容知道，是先执行WHERE子句筛选出符合条件的记录，然后再由SELECT获取结果集！！！所以，一般是可以先使用WHERE子句做筛选，然后使用聚合函数在筛选后的数据上做统计的。。。好吧承认自己啰嗦了……

---

一：聚合函数简介

---

二：聚合函数详解

1.SUM函数：求和函数

如：

---

2.MAX函数：求最大值

MAX函数可用于时间，求最晚的时间。

---

3.MIN函数：求最小值

MIN函数可用于时间，求最早的时间。

---

4.AVG函数：求平均值

---

5.COUNT函数：求记录数量

示例1：查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数：

示例2：查询1985年以后入职的员工，底薪超过公司的平均底薪的员工：

这是一个错误的案例，目的是引入后面的分组查询。

这个问题解决不了，也就是需要后面的分组查询：包括GROUP BY 子句和其中的Having子句了。

分组查询

聚合函数默认统计的是全表范围的数据，配合上WHERE子句就可以缩小统计的范围；

但是，仅靠这些并不能满足有些要求；如：查询每个部门的平均底薪，这个就需要对员工记录按照部门编号进行分组了；

本篇博客需要注意的地方：WHERE 子句主要在GROUP BY子句前面。在【 4.GROUP_CONCAT函数 】这部分中有说明；

---

一：分组查询：GROUP BY 子句

1.最基本的分组：单级分组

---

2.逐级分组

GROUP BY 子句可以有多个字段，以实现逐级分组

---

一个容易犯的错误：

---

3.对分组结果再次做汇总计算：WITH ROLLUP关键字

---

4.GROUP_CONCAT函数

CONCAT的英文意思就是：函数; 合并数组; 合并多个字符串; 联系我们；

【2.逐级分组】部分，SELECT子句要求那儿提到【汇总函数返回的一条记录，跟非分组字段的多条结果没法匹配】；；；；；如果想要匹配，就需要【把非分组字段的多条记录转换成一条记录】，MySQL提供了这样的函数：【GROUP_CONCAT函数】；

---

5.各种子句的执行顺序

Having子句

这篇博客写的太啰嗦，究其原因还是理解的不到位，以后理解深入之后，回头简化吧。

● Having子句要和GROUP BY子句联合起来才能使用；Having子句不能单独使用。

● WHERE子句和Having子句功能是相同的，都是做数据筛选的；【普通条件】的判断建议放在WHERE子句中，【聚合函数和其他的数据】的条件判断需要放在Having子句中；

● 在 Having 子句中，拿聚合函数跟具体的数据做比较是没有问题的，比如“HAVING COUNT(*)>=2;”是没有问题的；但是，拿聚合函数跟某个字段作条件判断是不行的，这个必须用表连接才能实现；表连接的内容在后面会介绍；

---

零：为什么需要【Having子句】

引入【Having子句】纯属无奈。（ 具体，Having子句的作用，以后用着用着就能更好的理解了吧… ）

因为，【GROUP BY子句】确实很强大，但是有些查询业务，仅仅靠【GROUP BY子句】并不能满足要求；

如：

注：（1）在数据库的高级查询三这篇文章中有各个子句的执行顺序说明；

（2） 聚合函数不能放在WHERE子句中！！！聚合函数的使用之前，需要先明确无误的确定数据的范围；在WHERE中使用聚合函数是万万不能的，这在逻辑上就说不通，MySQL自然也不支持这样做！！！

---

一：Having子句的一个例子：初体验

通过下面这个例子，感觉having子句或许可以看成是一个分组后，再筛选一次的操作。

例子：查询部门平均底薪超过2000的部门：

​

  SELECT deptno
    FROM t_emp
    GROUP BY deptno HAVING AVG(sal)>=2000;

前面提到过，聚合函数写在了SELECT子句中的，这是因为，SELECT子句的执行顺序是在WHERE子句之后的，WHERE子句执行完了之后，基本的数据范围就确定了，自然可以使用聚合函数；

经过这个例子之后，可以发现，聚合函数并不一定非得写在SELECT子句中；；；只要WHERE子句执行完了之后，后续执行的子句中都可以使用聚合函数；；；；（目前来看，这种理解大概率是没有问题的，在逻辑上也可以理解。。。）

话句话说：（假设下面的子句都有的话），执行顺序是 【FROM】 → 【WHERE】 → 【GROUP BY】→【SELECT】 → 【ORDER BY】→【LIMIT】 ：：黑色的部分是没有必要使用聚合函数的；红色的部分是不能使用聚合函数的；绿色的部分可以使用聚合函数，只是【GROUP BY】在使用聚合函数的时候，需要结合Having子句！！！

---

二：Having子句：基本内容

（1）基本内容

● Having子句的作用和WHERE子句差不多，都是用来做条件筛选的；

● 只是Having子句需要写在GROPY BY子句的后面；

● GROPY BY 子句执行完毕之后，才会轮到Having子句去执行；自然Having子句的执行顺序是在WHERRE子句之后的，所以在Having子句中可以使用聚合函数做条件判断。

如下面的例子，【WHERE子句】是做普通条件判断的，即WHERE子句是作淳朴的判断的，如下面1982年之后入职的这个就是个简单的判断；具体点说，WHERE的在判断的主要目的是：基于原表获取“最初的结果集”；【Having子句】是做聚合函数和其他数据做条件判断的；换句话说Having子句的的筛选是：【WHERE已经获取了“最初的结果集”，然后GROUP BY对“最初的结果集”做了分组处理，然后having子句再上场，做进一步筛选】；

即一句关键的话：【普通条件】的判断放在了WHERE子句中，【聚合函数和其他的数据】的条件判断放在了Having子句中了！！！

…………………………………………………………………………………………

（2）一个容易出错的地方：

还需要注意一个特别容易出错的点！！！：

在 Having 子句中，拿聚合函数跟具体的数据做比较是没有问题的，比如“HAVING COUNT(*)>=2;”是没有问题的；

但是，拿聚合函数跟某个字段作条件判断是不行的，这个必须用表连接才能实现；

AND子句也可以用在Having子句中哎。

---

三：Having子句的特殊用法：

表连接查询：内连接

---

零：笛卡尔积

笛卡尔积介绍：（此图转载自SQL的多表查询(笛卡尔积原理)）；

（1）注意一下【2×2=4】，【4×2=8】；

（2）笛卡尔积：将两张表记录可以组合的所有可能，得到一张新的表；，

（3）笛卡尔积的语法：只是用JOIN来连接两站表，并没有连接条件；

​ SELECT * FROM t_emp CROSS JOIN t_dept;

或者：有没有CROSS，效果一样

​ SELECT * FROM t_emp JOIN t_dept;

（4）两张表中相同的字段，都会保留，如：

---

一：表连接：内连接简介

1.做表连接的时候，需要指定关联的条件。如果只是用JOIN来连接两站表，而没有ON指定连接条件，两张表连接的结果就是笛卡尔积了；

2.JOIN连接表，ON规定连接条件

（1）使用ON来规定连接的条件，这儿的条件是：emp表的deptno字段值=dept表的deptno字段值；

（2）在FROM子句中，给表起了一个别名；

（3）在SELECT子句中，ON子句中都使用了表的别名，来具体确定是哪张表；

（4）因为，有了ON后面的连接条件，符合条件的才会被保留下来，自然不会出现笛卡尔积那种情况了；

---

二：表连接的分类之：内连接

……………………………………………………

注：（20210406）经过实测，内连接可以不使用ON，话句话说，内连接后面可以不跟限定条件：

……………………………………………………

1.内连接简介

内连接的语法有多种变形的形式：（虽然下面几种内连接的写法不一样，但效果是一样的，而且执行的效率也都是一样的）

写法一：内连接最标准的写法：

写法二：ON子句中规定的是连接的条件，是用来筛选符合条件的记录的；那么自然可以将ON子句中的条件，写在WHERE子句中；

忽略了ON子句，而是使用WHERE，将连接条件写在WHERE子句中。

写法三：省略JOIN关键字，用逗号代替。。。私以为这种简写形式不咋地；

注：SELECT……FROM 表1，表2 ON 连接条件，这样是不行的！！！

2.内连接案例（核心，非常重要）

（1）查询每个员工的工号，姓名，部门名称，底薪，职位，工资等级 ：：这个例子，简单一点，直接连接，然后筛选符合条件的记录就行；

我的解决办法：

​ SELECT e.empno,ename,d.dname,e.sal,e.job,s.grade FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno JOIN t_salgrade s ON ((e.sal>=s.losal) AND (e.sal⇐s.hisal))

改进的一种写法：

​ SELECT e.empno,ename,d.dname,e.sal,e.job,s.grade FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

这个例子的一个点：内连接的数据不一定必须有同名字段，只要字段之间符合逻辑关系就行；

（2）查找与SCOTT相同部门的员工都有谁 ：：这例子复杂点，需要先查询SCOTT的部门，然后获取这个部门的其他员工；；；但是这个例子不涉及聚合函数。

最容易想到的策略：利用子查询的方法：

​ SELECT ename FROM t_emp WHERE deptno=(SELECT deptno FROM t_emp WHERE ename=“SCOTT”) AND ename!=“SCOTT”;

但是这种子查询的方法，虽然容易理解，非常符合一般的思考逻辑。但是数据库在执行子查询的时候，速度是非常的慢的。上面每执行一次WHERE语句，都要执行一次【SELECT deptno FROM t_emp WHERE ename=“SCOTT”】，执行的次数直接平方了，特别耗时，能懂？？？

利用表连接的方法：

​ SELECT e2.ename FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno WHERE e1.ename=“SCOTT” AND e2.ename!=“SCOTT”;

要理解上面表连接的方法，多想想【从笛卡尔积中筛选出符合条件的记录】这句话，脑子里想一下表连接成笛卡尔积的过程和从笛卡尔积中筛选的过程就明白了。

这个例子的一个点：相同数据表也可以是做表连接的。

（3）查询底薪超过公司平均底薪的员工信息 ：：这例子复杂点，需要先查询部门的平均底薪，然后获取这个部门底薪超过公司平均底薪的员工；；；但是这个例子涉及聚合函数。聚合函数不能放在WHERE或ON这种筛选的语句中哦！！！

这个问题，也是数据库的高级查询二：聚合函数这篇博客最后的遗留问题

最容易想到的策略，依旧是子查询：

​ SELECT ename,sal FROM t_emp WHERE sal>(SELECT AVG(sal) FROM t_emp);

这种策略，符合一般的思考逻辑，但就是复杂度太大，效率太低；

利用表连接的方法：

利用表连接的方法：

​ SELECT e.empno,e.ename,e.sal FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t ON e.sal>t.avg;

分析：【(SELECT AVG(sal) avg FROM t_emp) t】， 把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接 。然后再从连接的笛卡尔积表中筛选出符合条件的记录；

（4）查询RESEARCH部门的人数，最高底薪，最低底薪，平均底薪，平均工龄：：： 这个例子还好，启发：【同一个问题有多种解决方案，多多练习，更加深入后就能选择更优的解决方案】。

我的做法：

​ SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate)) FROM t_emp e JOIN (SELECT deptno dno FROM t_dept WHERE dname=“RESEARCH”) d ON e.deptno=d.dno;

标准的做法：

​ SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),FLOOR(AVG(DATEDIFF(NOW(),e.hiredate)/365))-1 FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno WHERE d.dname=“RESEARCH”;

………………………………

注解：FLOOR()，ROUND()，CEIL()三个函数。

---

（5）查询每种职业的最高工资，最低工资，平均工资，最高工资等级，最低工资等级：：：这个例子的教训：动脑子好吧，梳理清楚逻辑和过程！！！

我的做法：基本思路还是在笛卡尔积上做筛选。其中用到了【 把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接 。】

​ SELECT e.maxx,e.minn,e.avgg,s.grade,e.job FROM (SELECT job,MAX(sal) maxx,MIN(sal) minn,AVG(sal) avgg FROM t_emp GROUP BY job) e JOIN t_salgrade s ON (s.losal⇐e.maxx AND s.hisal>=e.maxx) OR (s.losal⇐e.minn AND s.hisal>=e.minn);

我的做法是不对的，做的时候只想到： 当【聚合函数，表连接都需要的时候】，【把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接。】是一种不错的解决思路！！ 但是这个思路虽然可以解决很多具体需求，但并不是普遍使用的，有的情况，就不能用这种思路。

标准的做法：

​ SELECT e.job,MAX(e.sal+IFNULL(e.comm,0)),MIN(e.sal+IFNULL(e.comm,0)),AVG(e.sal+IFNULL(e.comm,0)),MAX(s.grade),MIN(s.grade) FROM t_emp e JOIN t_salgrade s ON e.sal+IFNULL(e.comm,0) BETWEEN s.losal AND s.hisal GROUP BY e.job

………………………………………………………………………………

（6）查询每个底薪超过部门底薪的员工信息：：：

我的做法： 当【聚合函数，表连接都需要的时候】，【把聚合函数的查询结果先算出来，然后把算出的来结果当成一张表；；然后把这个表再和其他表做连接。】是一种不错的解决思路！！

​ SELECT e2.ename,e2.empno,e2.sal,e1.avv,e2.deptno FROM (SELECT deptno,AVG(sal) avv FROM t_emp GROUP BY deptno) e1 JOIN t_emp e2 ON e2.sal>e1.avv AND e1.deptno=e2.deptno;

注意一下，e1中查询了deptno，因为e1和e2做笛卡尔积后，其中会有很多配对错误的记录，需要利用e1.deptno=e2.deptno筛选配对正确的记录。 这也是非常重要的一个点！！！即，先查询一个东西当成一个表和其他表做连接的时候，为了筛选连接后配对的数据，先查询的那个可能需要查询一些额外的、附带的信息；

标准做法：

​ SELECT e.sal,e.empno,e.ename,t.avv FROM t_emp e JOIN (SELECT deptno,AVG(sal) AS avv FROM t_emp GROUP BY deptno) t ON e.deptno=t.deptno AND e.sal>=t.avv;

表连接查询：外连接

---

一：外连接引入：

如下：

● t_emp在左侧，称为左表；t_dept在右侧，称为右表；；；

● LEFT JOIN是，在左表和右表连接的时候，保留左表的记录；

● 如果右表有符合连接条件的记录，就正常连接；如果右表没有符合连接条件的记录，右表就出null值去跟左表连接；

如：案例如下

---

二：左外连接，右外连接

外连接包括：左外连接和右外连接。

如下面两种结果是一样的：

---

三：外连接案例

案例1：查询每个部门的名称和部门的人数、

我的做法：

​ SELECT COUNT(*),d.deptno FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno GROUP BY d.deptno;

标准做法：

​ SELECT d.dname,COUNT(e.deptno),d.deptno FROM t_dept d LEFT JOIN t_emp e ON d.deptno=e.deptno GROUP BY d.deptno;

---

案例2：查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用NULL代替：UNION关键字

我的做法：

​ SELECT COUNT(e.deptno),d.dname FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno GROUP BY d.dname;

改进后的我的做法：

​ SELECT d.dname,d.deptno,COUNT(*) FROM (SELECT deptno noo,COUNT(*) FROM t_emp GROUP BY deptno) e LEFT JOIN t_dept d ON e.noo=d.deptno GROUP BY d.dname;

标准做法：使用UNION关键字：UNION关键字把多个查询语句的结果集进行合并（当然会排出重复部分的内容）！！！！！！！！！！！

UNION合并不同的查询语句的结果集，这些不同的结果集需要字段的名称相同，字段的数量也需要相同；只有这样才能合并。

因为，员工表中的陈浩没有部门，部门表中的OPERATIONS没有员工，为了两个都能保留下来，需要分别左右连接，，

然后，使用UINON关键字，将左右连接的结果集进行合并。

---

案例3：查询每名员工的编号，姓名，部门，月薪，工资等级，上司编号，上司姓名，上司部门（这个例子有一个非常重要的点）

我的解决方案：核心就是各种表连接，（心里想着先笛卡尔积然后筛选的过程）；缺点：一连到底，有点乱，模块性不强。。。

​ SELECT e1.empno,e1.ename,d1.dname,(e1.sal+IFNULL(e1.comm,0)),s1.grade,FLOOR(DATEDIFF(NOW(),e1.hiredate)/365),e1.mgr,e2.ename,d2.dname FROM t_emp e1 LEFT JOIN t_dept d1 ON e1.deptno=d1.deptno JOIN t_salgrade s1 ON (e1.sal+IFNULL(e1.comm,0)) BETWEEN s1.losal AND s1.hisal LEFT JOIN t_emp e2 ON e1.mgr=e2.empno LEFT JOIN t_dept d2 ON e2.deptno=d2.deptno;

标准做法：“模块化”处理了，更清晰

​ SELECT e.empno,e.ename,d.dname,e.sal+IFNULL(e.comm,0),s.grade,FLOOR(DATEDIFF(NOW(),e.hiredate)/365),t.empno,t.ename,t.dname FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal LEFT JOIN (SELECT e1.empno,e1.ename,d1.dname FROM t_emp e1 JOIN t_dept d1 ON e1.deptno=d1.deptno) t ON e.mgr=t.empno;

标准做法中需要注意的地方：十分重要的一个点，，， 知道可以这样做以后，以后在做表连接的时候可以更加灵活和模块化了，，能懂，OK！！！！！！！！！

---

四：外连接注意事项：条件写在ON子句中，写在WHERE子句中的不同

1.内连接只保留符合条件的记录，所以查询条件写在ON子句中，还是写在WHERE子句中，效果是一样的；

2.但在外连接中，查询条件写在ON子句中，还是写在WHERE子句中，效果就不一样了；

在外连接中，条件写在WHERE子句中，不符合条件的记录是会被过滤掉的；；；（还好，可以勉强理解，毕竟ON子句是依附在LEFT JOIN或者RIGHT JOIN或者JOIN子句上的，其本身就是JOIN子句的附属品；；；；但是WHERE子句是独立的。）

---

注：（20210406）经过实测，外连接必须要使用ON，如果不使用ON会报错！！！！！！或许外连接不使用ON，会让MySQL产生迷惑，，，

子查询

说明：仅仅建议在FORM子句中使用子查询。其他在SELECT中的子查询、WHERE中的子查询在实际工作中，不建议使用，如有涉及，仅仅是出于演示和介绍目的。

---

一：子查询：根据【子查询所在位置】的角度

● WHERE子句中的子查询不推荐使用：通过前面的内容了解到，子查询符合一般的思考逻辑，但是在WHERE子句中使用子查询可能会导致查询的次数直接扩大一个数量级，“平方”的效果增大实际查询次数。

● FROM子句中子查询推荐使用：像以前接触的，把查询结果集当成一张表，跟其他表做连接：这是在FROM子句中的子查询，这种子查询还是推荐使用的。

1.子查询简介

……………………………………………………

2.子查询分类：WHERE子句中的子查询，FROM子句中的子查询，SELECT子句中的子查询

……………………………………………………

3.相关子查询：WHERE和SELECT中的子查询

最后再重复一下相关子查询：

● 查询语句执行的时候要 多次 的依赖于子查询的结果，这类子查询被称作相关子查询；

● WHERE子查询和SELECT子查询都属于相关子查询；

● 因为相关子查询要反复多次执行，所以应该避免使用。

● 仅仅建议在FROM子句中使用子查询。

---

二：子查询：根据【子查询的结果集中的记录的数量】的角度

1.子查询：单行子查询，多行子查询

● 子查询的结果集，如果只有一条记录，就是单行子查询；；；；子查询的结果集中如果有多条记录，就是多行子查询；

● 单行子查询可以出现在：SELECT子句中，WHERE子句中，FROM子句中；多行子查询只能出现在：WHERE子句，FROM子句中；

（1） 多行子查询在FROM子句中是没有问题的：因为，可以把子查询的结果集可以当成一个临时表，和其他数据表做连接啊；

（2）多行子查询出现在WHERE子句中也是没有问题的：因为，可以特殊的表达式符号（IN，ALL，ANY，EXISTS）来做判断，后面会介绍。

（3）多行子查询出现在SELECT子句中是万万不行的：因为，SELECT子句的其他字段的一条记录，是没有办法和多行子查询的多条记录做合并的。

如案例：利用子查询查找FORD和MARTIN两个人的同事：（同部门的其他人）

………………………………………………………………………………

2.WHERE子句中的多行子查询的处理方法；；；；（强烈不建议，在WHERE子句中使用子查询，本部分仅为演示介绍目的）

（2.1）IN，ALL，ANY关键字的使用

…………………………

（2.2）EXISTS关键字

案例：查询工资等级是3级或者4级的员工信息；（正常情况下，使用表连接最简单，不建议使用WHERE子句中的子查询的方式的。。。这儿仅仅是为了演示，学习了解目的，实际中不建议使用WHERE子句中的子查询方式的）

好吧，承认EXISTS关键字的用法很别扭，很多东西没有明确写出来，而是省略了很多，有点偏重于简写形式的赶脚；