Java学习 --- MySQL的group by与having

目录

一、group by

二、WITH ROLLUP

三、HAVING

四、 WHERE和HAVING的对比

五、sql语法编写顺序 

5.1 sql95语法编写顺序

5.2sql99语法编写顺序

 六、sql语句执行过程

七、练习

---

一、group by

select department_id,avg(salary) "部门平均工资" from employees group by department_id;

 总结：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。相反GROUP BY中声明的字段可以不出现在SELECT中。

GROUP BY声明在FROM,WHERE后面，ORDER BY 和LIMIT前面

二、WITH ROLLUP

SELECT department_id,AVG(salary) "部门平均工资" FROM employees GROUP BY department_id WITH ROLLUP;

 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

三、HAVING

SELECT department_id,MAX(salary)  FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;

 总结：使用聚合函数就必须使用having替代where，having必须在group by后面，having不能脱离group by使用

四、 WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一
个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

五、sql语法编写顺序 

5.1 sql95语法编写顺序

SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

5.2sql99语法编写顺序

SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

 六、sql语句执行过程

FROM -> ON-> (LEFT/RIGNT)JOIN-> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

七、练习

#group by与having过滤
#创建表
CREATE TABLE dept( -- 部门表
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  dname VARCHAR(32) NOT NULL DEFAULT "",
  loc VARCHAR(32) NOT NULL DEFAULT ""
);
-- 添加数据
INSERT INTO dept VALUES(1,'人事部','管理人员'),(2,'财务室','管财务'),(3,'开发部','技术研发'),(4,'宣传部','宣传'),(5,'运维部','售后服务')
SELECT*FROM dept

CREATE TABLE emp(
 empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,-- 编号
 ename VARCHAR(32) NOT NULL DEFAULT "",-- 名字
 job VARCHAR(32) NOT NULL DEFAULT "",-- 工作
 mgr MEDIUMINT UNSIGNED ,-- 上级编号
 hiredate DATE NOT NULL,-- 入职时间
 sal DECIMAL(7.2) NOT NULL,-- 薪水
 comm DECIMAL(7.2),-- 红利
 deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 -- 部门编号
);
-- 添加数据
INSERT INTO emp VALUES(1,'小王','写代码',12,'2021-4-4',6000.00,400.00,3),
(2,'小李','改代码',12,'2020-4-4',5500.00,200.00,3),
(3,'小亮','架构师',12,'2019-5-6',8000.00,600.00,3),
(4,'小丽','会计',11,'2018-6-4',5000.00,100.00,2),
(5,'小飞','运维',13,'2020-10-4',3000.00,800.00,5),
(6,'小蓝','财务',11,'2015-4-4',4000.00,400.00,2),
(7,'小美','前台',10,'2021-3-4',6000.00,100.00,1),
(8,'小星','人事主管',10,'2019-12-12',9000.00,1000.00,1),
(9,'小黑','宣传',14,'2021-11-4',7000.00,200.00,4),
(10,'小聪','宣传',14,'2021-8-4',5500.00,600.00,4)
SELECT*FROM emp
#工资表
CREATE TABLE salgrade(
  grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,-- 工资级别
  losal DECIMAL(17,2) NOT NULL, -- 最低工资级别
  hisal DECIMAL(17,2) NOT NULL -- 最高工资级别
);
INSERT INTO salgrade VALUES(1,700,1300);
INSERT INTO salgrade VALUES(2,1301,3000);
INSERT INTO salgrade VALUES(3,3001,4000);
INSERT INTO salgrade VALUES(4,4001,5000);
INSERT INTO salgrade VALUES(5,5001,1000);
SELECT*FROM salgrade

#查询每个部门的平均工资和最高工资
SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP BY deptno
#查询每个部门的每个岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal), deptno,job FROM emp GROUP BY deptno,job
#查询平均工资低于5000的部门号和它的平均工资
SELECT AVG(sal)AS avg_sal,deptno FROM emp GROUP BY deptno HAVING avg_sal<5000

#1.where子句可否使用组函数进行过滤? 
#不能
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees GROUP BY job_id ;
#4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*) FROM employees GROUP BY job_id;
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
 SELECT MAX(salary) - MIN(salary)"DIFFERENCE" FROM employees;
# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
  SELECT manager_id,MIN(salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) > 6000;
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 
SELECT d.department_name,d.location_id,COUNT(e.employee_id),AVG(salary) FROM `departments` d  LEFT JOIN `employees` e ON d.department_id = e.department_id 
GROUP BY d.department_name,d.location_id
HAVING AVG(salary) IS NOT NULL
ORDER BY AVG(salary) DESC;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资 
SELECT department_name,job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
GROUP BY department_name,job_id;