MySQL学习(7)︱GROUP BY、HAVING和GROUP_CONCAT的用法

GROUP BY

group by表示根据某种规则对数据进行分组，它必须配合聚合函数进行使用，对数据进行分组后可以进行count、sum、avg、max、min等运算。

• group by语法：

SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;//aggregate_function表示聚合函数，group by可以对一列或者多列进行分组

HAVING

在SQL中增加HAVING子句原因是，WHERE关键字无法与聚合函数一起使用。HAVING子句可以对分组后的各组数据进行筛选。

• having语法：

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;

GROUP_CONCAT

使用group by可以分组统计每个部门有多少员工，group_by除了统计每个部门的员工数量之外，还能知道具体是哪些员工，实现效果如下图所示。

• group_by语法：

GROUP_BY ([DISTINCT] column_name [ORDER BY column_name ASC/DESC] [SEPARATOR '分隔符']);

例子

1. 连接数据库，查看已有数据库，使用student数据库并查看所有数据表

SHOW DATABASES;
USE student;
SHOW TABLES;

2. 查看employee数据表所有记录

SELECT * FROM employee;

3. 删除旧的employee数据表，然后创建新的employee数据表并插入若干条记录

DROP TABLE employee;
CREATE TABLE employee(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30) COMMENT '姓名',
	sex VARCHAR(1) COMMENT '性别',
	salary INT COMMENT '薪资',
	dept VARCHAR(30) COMMENT '部门'
);

INSERT INTO employee(name, sex, salary, dept) VALUES('张三', '男', 3500, '部门A');
INSERT INTO employee(name, sex, salary, dept) VALUES('李洁', '女', 4500, '部门C');
INSERT INTO employee(name, sex, salary, dept) VALUES('李小梅', '女', 4200, '部门A');
INSERT INTO employee(name, sex, salary, dept) VALUES('欧阳辉', '男', 7500, '部门C');
INSERT INTO employee(name, sex, salary, dept) VALUES('李芳', '女', 8500, '部门A');
INSERT INTO employee(name, sex, salary, dept) VALUES('张江', '男', 6800, '部门A');
INSERT INTO employee(name, sex, salary, dept) VALUES('李四', '男', 12000, '部门B');
INSERT INTO employee(name, sex, salary, dept) VALUES('王五', '男', 3500, '部门B');
INSERT INTO employee(name, sex, salary, dept) VALUES('马小龙', '男', 6000, '部门A');
INSERT INTO employee(name, sex, salary, dept) VALUES('龙五', '男', 3500, '部门B');
INSERT INTO employee(name, sex, salary, dept) VALUES('冯小芳', '女', 10000, '部门C');
INSERT INTO employee(name, sex, salary, dept) VALUES('马小花', '女', 4000, '部门B');
INSERT INTO employee(name, sex, salary, dept) VALUES('刘峰', '男', 8800, '部门A');

4. 查看新employee数据表所有记录

SELECT * FROM employee;

5. 统计男、女员工的数量

SELECT sex, COUNT(*) FROM employee GROUP BY sex;

6. 统计各部门员工数量

SELECT dept, COUNT(*) FROM employee GROUP BY dept;

7. 统计各部门薪资的总和、最大值和最小值

SELECT dept, SUM(salary) FROM employee GROUP BY dept;
SELECT dept, MAX(salary) FROM employee GROUP BY dept;
SELECT dept, MIN(salary) FROM employee GROUP BY dept;

8. 统计员工数量少于5的部门

SELECT dept, COUNT(*) FROM employee GROUP BY dept HAVING COUNT(*)<5;

9. 统计各部门薪资的最大值

SELECT dept, MAX(salary) FROM employee GROUP BY dept HAVING MAX(salary)>=10000;

10. 统计各部门员工数量并列出具体的姓名

SELECT dept, COUNT(*), GROUP_CONCAT(name) FROM employee GROUP BY dept;

11. 统计各部门员工数量并降序列出具体的姓名

SELECT dept, COUNT(*), GROUP_CONCAT(name ORDER BY name DESC) FROM employee GROUP BY dept;

12. 统计各部门员工数量并降序列出具体的姓名且以“:”作为分隔符

SELECT dept, COUNT(*), GROUP_CONCAT(name ORDER BY name DESC SEPARATOR ':') FROM employee GROUP BY dept;

注：本文是博主MySQL学习的总结，不支持任何商用，转载请注明出处！如果你也对MySQL学习有一定的兴趣和理解，欢迎随时找博主交流~