数据库的高级查询

数据统计分析：聚合函数、分组查询、HAVING子句

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

子查询：单行子查询、多行子查询、WHERE子查询、FROM子查询、SELECT子查询

1.聚合函数

1.SUM函数

用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加。

2.MAX函数

用于获得非空值的最大值

# 查询10和20部门中，月收入最高的员工？
SELECT MAX(sal+IFNULL(comm,0)) FROM t_emp WHERE deptno IN(10,20);
# 查询员工名字最长的是几个字符？
SELECT MAX(LENGTH(ename)) FROM t_emp;

3.MIN函数

用于获取非空值的最小值

SELECT MIN(empno) FROM t_emp;
SELECT MIN(hiredate) FROM t_emp;

4.AVG函数

用于获取非空值的平均值，非数字数据统计结果为0

SELECT AVG(sal + IFNULL(comm,0)) FROM t_emp;
SELECT AVG(ename) FROM t_emp; # 结果为0

5.COUNT函数

COUNT(*)用于包含空值的记录数

SELECT COUNT(*) FROM t_emp; # 返回15，因为数据表中有15条记录

COUNT(列名)用于包含非空值的记录数

SELECT COUNT(comm) FROM t_emp; # 返回5，因为数据表中只有这5个记录是非空的
# 查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数？
SELECT COUNT(*)
FROM t_emp
WHERE deptno in(10,20)
AND DATEDIFF(NOW(),hiredate)/365 > 15
AND sal >= 2000;
# 查询1985年以后入职的员工，底薪超过公司平均底薪的员工数量？
# 错误示例：原因：聚合函数是不能出现在where子句里面的
SELECT COUNT(*) FROM t_emp
WHERE hiredate >="1985-01-01"
AND sal>AVG(sal);
# 如何解决去分组查询中解决吧。往下看：

2.分组查询

GROUP BY子句的作用是通过一定的规则将一个数据集划分城若干个小的区域，然后针对每个小区域分别进行数据汇总处理

# 查询每个部门的平均工资
SELECT deptno,ROUND(AVG(sal))
FROM t_emp GROUP BY deptno;

逐级分组

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

# 查询每个部门里，每种职位的人员数量和平均底薪
SELECT deptno,job,COUNT(*),ROUND(AVG(sal))
FROM t_emp GROUP BY deptno,job
ORDER BY deptno;

对SELECT子句的要求

查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：

SELECT子句中可以包含聚合函数，或者GROUP BY 子句的分组列，其余内容均不可以出现在SELECT子句中！

# 正确示例
SELECT deptno,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno;
# 错误示例
SELECT deptno,COUNT(*),AVG(sal),sal # 这个sal是无法匹配的，所以会出现语法错误
FROM t_emp GROUP BY deptno;

什么原因呢？就是咱们数据匹配的这个问题。因为我们按照这个部门去分组的时候，前面统计的deptno，比如说是10部门的信息，然后是COUNT()他的人数，AVG(sal)平均的底薪，这些字段的记录，在统计10的时候只有1条记录。但是后面这个sal字段，10部门里面员工的人数可就多了，deptno,COUNT(),AVG(sal)这前面的记录只有一条，后面sal的记录有多条，所以select子句怎么结合呢？没法结合。所以就出问题了，所以才会有这个规定。

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

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

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

GROUP_CONCAT函数

GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串

# 查询每个部门内底薪超过2000元的人数和员工姓名
SELECT deptno,GROUP_CONCAT(ename),COUNT(*)
FROM t_emp WHERE sal>=2000
GROUP BY deptno;

GROUP_CONCAT.png

各种子句的执行顺序

查询语句中，GROUP BY子句应该在第几个执行？

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
# FROM  确定数据源
# WHERE 筛选数据
# GROUP BY 数据分组
# SELECT 使用SELECT中的聚合函数进行汇总计算
# ORDER BY 对结果进行排序
# LIMIT 挑选返回哪些分页的数据

练习题

练习题.png

SELECT department,job,COUNT(*),MAX(schoolship)
FROM student
GROUP BY department,job
ORDER BY department;

student表结构

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` varchar(255) NOT NULL,
  `enrollment_date` datetime NOT NULL,
  `tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `department` varchar(255) NOT NULL,
  `job` varchar(255) NOT NULL,
  `schoolship` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, '赵芳芳', '女', '2016-09-12 00:00:00', '13323566523', '信息工程系', '学会会主席', 5000);
INSERT INTO `student` VALUES (2, '王五', '女', '2015-02-01 00:00:00', '1236547895', '食品工程系', '学习委员', 4500);
INSERT INTO `student` VALUES (3, '李思思', '男', '2018-09-15 00:00:00', '15202983625', '食品工程系', '学生会主席', 6000);
INSERT INTO `student` VALUES (4, '欧阳华轩', '女', '2016-09-05 00:00:00', '18763258951', '食品工程系', '学习委员', 3500);
INSERT INTO `student` VALUES (5, '赵三', '男', '2014-01-05 00:00:00', '15935785212', '信息工程系', '学习委员', 8000);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

HAVING子句

HAVING 子句的用途

# 查询每个部门中，1982年以后入职的员工超过2个人的部门编号
SELECT deptno from t_emp
WHERE hiredate>"1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2
ORDER BY deptno ASC;
# 查询部门平均底薪超过2000元的部门编号
# 下面是错误示例 错误的示例！
SELECT deptno FROM t_emp
WHERE AVG(sal)>=2000
GROUP BY deptno
# 错误原因，因为where子句实在group by子句之前执行，那么where子句中的avg函数就不知道统计范围，所以报错。
# 正确的写法应该是这样： 
SELECT deptno FROM t_emp
GROUP BY deptno HAVING AVG(sal)>=2000;

HAVING 子句不能独立存在，是依赖于group by的。having紧跟着group by子句

HAVING子句和where的功能差不多，只是having是在group by执行完成以后再执行。所以在having子句中我们就可以用聚合函数进行条件判断！

HAVING子句的特殊用途

# 按照数字1分组，MySQL会依据SELECT子句中的列进行分组，HAVING子句也可以正常使用
SELECT deptno,COUNT(*) FROM t_emp GROUP BY 1; # 这里的意思是按照SELECT中的第一个字段分组，也就是deptno
SELECT deptno,COUNT(*) FROM t_emp GROUP BY 1 HAVING deptno IN (10,20);
# 在实际工作中where和having的使用要根据实际情况来确定

3.多表连接查询

从多张表提取数据，必须指定关联的条件。如果不定义关联条件就会出现无条件连接，两张表的数据会交叉连接，产生笛卡尔积。

# 多表连接 无条件
SELECT empno,ename,dname FROM t_emp JOIN t_dept;
# 多表连接 有条件
SELECT e.empno,e.ename,d.dname FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

表连接的分类：内连接和外连接

内连接：是结果集中只保留符合连接条件的记录

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

image-20211015140216230.png

# 内连接语法
SELECT ...... FROM 表1
[INNER] JOIN 表2 ON 条件
[INNER] JOIN 表3 ON 条件
......
# 内连接的多种语法
# 第一种
SELECT ......FROM 表1 JOIN 表2 ON 连接条件;
# 第二种
SELECT ......FROM 表1 JOIN 表2 WHERE 连接条件;
# 第三种
SELECT ......FROM 表1，表2 WHERE 连接条件;
# 查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级。
SELECT e.empno,e.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; # 内连接的数据表不一定必须有同名字段，只要字段之间符合逻辑关系就可以，例如员工表和工资表的关联，只要员工的薪资在工资表规定的等级区间内，就可以进行关联。

# 查询与SCOTT相同部门的员工都有谁？
# 低效的写法，虽然结果正确，但万万不可，写出来要被开除滴！
SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename = 'SCOTT') and ename!='SCOTT';
# 正确的写法应该是这样 N! 相同数据表也可以坐标连接滴！
SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno = e2.deptno
WHERE e1.ename = "SCOTT" and e2.ename != "SCOTT"; 

# 查询底薪超过公司平均底薪的员工信息？
# 错误写法：
SELECT e2.empno,e2.ename,e2.sal
FROM t_emp e1 JOIN t_emp e2 WHERE e.sal >= AVG(e.sal); # where子句中出现聚合函数报错
# 正确写法
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;

# 查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄？
SELECT count(*),max(e.sal),min(e.sal),avg(e.sal),FLOOR(avg(DATEDIFF(NOW(),e.hiredate)/365))
FROM t_dept d JOIN t_emp e ON d.deptno = e.deptno
WHERE d.dname = "RESEARCH";

# 查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级？
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;

# 查询每个底薪超过部门平均底薪的员工信息？
select e.empno,e.ename,e.sal
from t_emp e join
(select deptno,avg(sal) as avg from t_emp GROUP BY deptno) t
on e.deptno=t.deptno and e.sal>=t.avg;

4. 外连接

外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录。

左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。

# 查询每个部门的名称和部门的人数？
select d.dname,count(e.deptno) # 以e表的统计为准
from t_dept d LEFT JOIN t_emp e on d.deptno = e.deptno
GROUP BY d.deptno
# 查询每个部门的名称和部门的人数？如果没有部门的员工，部门名称用NULL代替.
# UNION关键字可以将多个查询语句的结果集进行合并 语法为：
(查询语句)UNION(查询语句)UNION(查询语句).....
 
 
 # 查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门？

外连接的注意事项

内连接只保留符合条件的记录，所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来。

5.子查询

把查询结果集当成一张表来使用，跟其他表做连接，from子句子查询

# 查询底薪超过公司平均底薪的员工的信息/
SELECT empno,ename,sal
FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp); # 每次作比较子查询都要重新执行一次

# from子查询。这种子查询只会执行一次，所以查询效率很高
SELECT e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) as avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal >=avg;

单行子查询和多行子查询

单行子查询的结果集只有一条记录，多行子查询结果集有多行记录

多行子查询只能出现在WHERE子句和FROM子句中