学习MySQL、03数据库的高级查询(面试常问！)

标题目录

1、学习目标
2、聚合函数的使用
3、分组查询
4、HAVING子句
5、表的内连接
6、表的外连接

标题1、学习目标

查询可以做到如：求最大值最小值、平均值

1、数据统计分析，涉及聚合函数、分组查询、HAVING子句

2、多表连接，涉及内连接、外连接，以及多表查询的多种语法

3、子查询（再查询结果中嵌套一个查询），涉及单行子查询，多行子查询，WHERE子查询，FROM子查询、SELECT子查询

标题2、聚合函数

在数据的查询分析中，应用十分广泛。可以求最大值最小值、平均值等等。

如求公司员工平均月收入

SELECT AVG(sal+IFNULL(comm,0)) FROM t_emp;

几个函数：SUM()、MAX()、MIN()、AVG()、COUNT()

MAX()：用于获得非空值的最大值

SELECT MAX(sal+IFNULL(comm,0)) FROM t_emp WHERE deptno IN(10,20);

MIN()：用于获得非空值的最小值

SELECT MIN(hiredate) FROM t_emp;

AVG()：用于获得非空值的平均值，非数字数据统计结果为0

COUNT()：COUNT(*)获得包含空值的记录数，CPUNT(列名)用于获得包含非空值的记录数

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

SELECT COUNT(*) FROM t_emp
WHERE deptno IN(10,20) AND sal>=2000
AND DATEIFF(NOW(),hiredate)/365>=15;

查询1985年以后入职的员工，底薪超过公司平均底薪的员工数量

错误例子如下：

SELECT FROM t_emp
WHERE hiredate>="1985-01-01"
AND sal>AVG(sal);#错误

注意：聚合函数是永远不能出现在WHERE字句中的。比如说上例，因为where都没有划定数据筛选的范围，就用条件去判断sal>AVG(sal)是错的

标题3、分组查询

3.1 为什么要分组呢？

因为默认情况下汇总函数是对全表范围内的数据做统计

GROUP BY子句的作用是通过一定的规则讲一个数据集划分成若干个小的取与，然后针对每个小区域分别做数据汇总处理，如

SELECT deptno,AVG(sal) FROM t_emp GROUP BY deptno;

3.2 逐级分组

数据库支持多列分组条件，执行的时候逐级分组

查询每个部门里，每种职位的人员数量和平均底薪

SELECT deptno,job,COUNT(*),AVG(sal) FROM t_emp 
GROUP BY deptno,job
ORDER BY deptno;

3.3 对分组结果集再次做汇总计算

如

SELECT deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal) 
FROM t_emp
GROUP BY deptno
WITH ROLLUP;

查询结果为

3.4 GROUP_CONCAT()函数

SELECT deptno,COUNT(*),GROUP_CONCATT(ename)
FROM t_emp
WHERE sal>=200
GROUP BY deptno

查询结果为

标题4.HAVING子句

为什么引入HAVING子句？

分组查询时候有时候会遇到困难，如查询部门平均底薪超过2000元的部门编号，逻辑上是这样写，但是是一定会报错的，

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

原因在于WHERE的优先级是高于GROUP BY的，WHERE子句中出现了汇总函数AVG(), GROUP BY执行之前，MYSQL不知道按照怎么样的统计规则去汇总这个数据，所以语法上就是有错误的。正确的思路是讲AVG()这个汇总的函数从WHERE子句里面抽出来。

HAVING子句是在GROUP BY后面，不能单独出现。

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

查询每个部门中，1982年以后入职的员工超过2个人的部门编号

SELECT FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2
ORDER BY deptno ASC;

注意：在HAVING子句里面的规则和在WHERE子句里面规则是一样的，聚合函数不能和某一个字段去比较，比如sal>=AVG(sal)是不行的。解决这个问题要引入表连接。

标题5、表的内连接

5.1 表连接的分类
内连接是结果集只保留符合链接条件的记录

外连接是不管符不符合连接条件，记录都要保留在结果集中

内连接

案例演示：

#查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级
SELECT e.empno,e.ename,d.dname,
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;

从这个例子可以看出，内连接的数据表不一定必须有同名字段，只要字段之间符合逻辑关系就可以。

这种嵌套查询的方式效率比较低下，如下图

一个技巧：表跟自己做表链接，也就是说相同的表也可以做表连接。

这样子的查询速度就会快很多很多

标题6、表的外连接

因为我们一开始的连接条件是部门表的部门编号和员工表的部门标号相同，而陈浩这个人没有部门编号，所以做内连接的时候就会漏掉

外连接案例演示：

SELECT e.empno,e.ename,d.dname 
FROM t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno

LEFT JOIN 就是保留左表的记录然后和右表做连接，加入说员工表中陈浩这条记录没有部门编号，那么想要在结果集中显示出陈浩，就需要拿t_emp做左表，进行左外连接。右外连接同理。

练习题：(待更新)