21、MySQL-分组查询(GROUP BY)及二次筛选(HAVING)

为了测试GROUP BY 语句,我们创建两张表,并往表中添加数据
-- 创建部门表
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
);
- - 添加部门
INSERT department(depName) VALUES('开发部');
INSERT department(depName) VALUES('视频部');
INSERT department(depName) VALUES('教学部');
INSERT department(depName) VALUES('运营部');
- - 创建员工表
CREATE TABLE IF  NOT EXISTS employee(
id Int UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
salary FLOAT(6,2) NOT NULL DEFAULT 0,
sex ENUM('男','女','保密'),
depId TINYINT UNSIGNED
);


- - 添加员工记录
INSERT employee(username,age,addr,salary,sex,depId) VALUES('张三','21','山东','5432.12','男',1);
INSERT employee(username,age,addr,salary,sex,depId) VALUES('李四','32','河北','6432.00','男',2);
INSERT employee(username,age,addr,salary,sex,depId) VALUES('王五','26','北京','5932.92','女',3);
INSERT employee(username,age,addr,salary,sex,depId) VALUES('赵六','32','上海','6232.14','男',4);
INSERT employee(username,age,addr,salary,sex,depId) VALUES('Mr Adword','55','美国','9432.99','男',4);
INSERT employee(username,age,addr,salary,sex,depId) VALUES('田七','19','北京','4932.92','保密',1);
INSERT employee(username,age,addr,salary,sex,depId) VALUES('孙八','62','上海','9932.14','男',2);
INSERT employee(username,age,addr,salary,sex,depId) VALUES('Mr lili','45','美国','9132.99','女',1);

-- 创建省份表
CREATE TABLE IF NOT EXISTS provinces(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> pName VARCHAR(10) NOT NULL UNIQUE
    -> );

- - 添加省份记录
INSERT provinces(pName) VALUES('山东'),('河北'),('北京'),('上海'),('美国');


mysql> SELECT * FROM department;
+----+---------+
| id | depName |
+----+---------+
|  1 | 开发部  |
|  3 | 教学部  |
|  2 | 视频部  |
|  4 | 运营部  |
+----+---------+
4 rows in set (0.06 sec)
mysql> SELECT * FROM employee;
+----+-----------+------+------+---------+------+-------+
| id | username  | age  | addr | salary  | sex  | depId |
+----+-----------+------+------+---------+------+-------+
|  1 | 张三      |   21 | 山东 | 5432.12 | 男   |     1 |
|  2 | 李四      |   32 | 河北 | 6432.00 | 男   |     2 |
|  3 | 王五      |   26 | 北京 | 5932.92 | 女   |     3 |
|  4 | 赵六      |   32 | 上海 | 6232.14 | 男   |     4 |
|  5 | 田七      |   19 | 北京 | 4932.92 | 保密 |     1 |
|  6 | Mr Adword |   55 | 美国 | 9432.99 | 男   |     4 |
|  7 | 田七      |   19 | 北京 | 4932.92 | 保密 |     1 |
|  8 | 孙八      |   62 | 上海 | 9932.14 | 男   |     2 |
|  9 | Mr lili   |   45 | 美国 | 9132.99 | 女   |     1 |
+----+-----------+------+------+---------+------+-------+
mysql> SELECT * FROM provinces;
+----+-------+
| id | pName |
+----+-------+
|  4 | 上海  |
|  3 | 北京  |
|  1 | 山东  |
|  2 | 河北  |
|  5 | 美国  |
+----+-------+


1、只使用GROUP BY语句查询结果只显示每一组的一条记录:
mysql> -- 按照性别分组
mysql> SELECT * FROM employee GROUP BY sex;
+----+----------+------+------+---------+------+-------+
| id | username | age  | addr | salary  | sex  | depId |
+----+----------+------+------+---------+------+-------+
|  1 | 张三     |   21 | 山东 | 5432.12 | 男   |     1 |
|  3 | 王五     |   26 | 北京 | 5932.92 | 女   |     3 |
|  5 | 田七     |   19 | 北京 | 4932.92 | 保密 |     1 |
+----+----------+------+------+---------+------+-------+
3 rows in set (0.05 sec)

mysql> -- 按照部门编号分组
mysql> SELECT * FROM employee GROUP BY depId;
+----+----------+------+------+---------+------+-------+
| id | username | age  | addr | salary  | sex  | depId |
+----+----------+------+------+---------+------+-------+
|  1 | 张三     |   21 | 山东 | 5432.12 | 男   |     1 |
|  2 | 李四     |   32 | 河北 | 6432.00 | 男   |     2 |
|  3 | 王五     |   26 | 北京 | 5932.92 | 女   |     3 |
|  4 | 赵六     |   32 | 上海 | 6232.14 | 男   |     4 |
+----+----------+------+------+---------+------+-------+
4 rows in set (0.00 sec)

mysql> -- 根据多个字段分组
mysql> SELECT * FROM employee GROUP BY sex,depId;
+----+----------+------+------+---------+------+-------+
| id | username | age  | addr | salary  | sex  | depId |
+----+----------+------+------+---------+------+-------+
|  1 | 张三     |   21 | 山东 | 5432.12 | 男   |     1 |
|  2 | 李四     |   32 | 河北 | 6432.00 | 男   |     2 |
|  4 | 赵六     |   32 | 上海 | 6232.14 | 男   |     4 |
|  9 | Mr lili  |   45 | 美国 | 9132.99 | 女   |     1 |
|  3 | 王五     |   26 | 北京 | 5932.92 | 女   |     3 |
|  5 | 田七     |   19 | 北京 | 4932.92 | 保密 |     1 |
+----+----------+------+------+---------+------+-------+

2、分组查询配合GROUP_CONCAT()来使用,可以看到每个组中的详细信息:

mysql> -- 按照性别分组,得到每组中人员的名称
mysql> SELECT *,GROUP_CONCAT(username) FROM employee GROUP BY sex;
+----+----------+------+------+---------+------+-------+-------------------------------+
| id | username | age  | addr | salary  | sex  | depId | GROUP_CONCAT(username)        |
+----+----------+------+------+---------+------+-------+-------------------------------+
|  1 | 张三     |   21 | 山东 | 5432.12 | 男   |     1 | 张三,李四,赵六,Mr Adword,孙八 |
|  3 | 王五     |   26 | 北京 | 5932.92 | 女   |     3 | 王五,Mr lili                  |
|  5 | 田七     |   19 | 北京 | 4932.92 | 保密 |     1 | 田七,田七                     |
+----+----------+------+------+---------+------+-------+-------------------------------+

3、配合聚合函数来使用
  • COUNT():统计记录的数目
  • SUM():求字段的和
  • AVG():求字段的平均值
  • MAX():求字段的最大值
  • MIN():求字段的最小值
mysql>  -- 统计员工表中员工数目,以及薪水的总和、最大值、最小值、平均值
mysql> SELECT id AS '编号',username AS '用户名',COUNT(*) AS '员工总数',SUM(salary) AS '总薪水',MAX(s
alary) AS '最高薪水',MIN(salary) AS '最低薪水',AVG(salary) AS '平均薪水' FROM employee\G;
*************************** 1. row ***************************
    编号: 1
  用户名: 张三
员工总数: 9
  总薪水: 62393.14
最高薪水: 9932.14
最低薪水: 4932.92
平均薪水: 6932.571126
1 row in set (0.00 sec)
mysql> -- 按照性别分组,统计出每个组中年龄最大值、最小值,薪水最大值,每个组中的人数,人名,以及平均薪水。
mysql> SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total_peo,AVG(salary)
AS avg_salary ,GROUP_CONCAT(username)FROM employee GROUP BY sex;
+----+------+---------+---------+------------+-----------+-------------+-------------------------------+
| id | sex  | max_age | min_age | max_salary | total_peo | avg_salary  | GROUP_CONCAT(username)        |
+----+------+---------+---------+------------+-----------+-------------+-------------------------------+
|  1 | 男   |      62 |      21 |    9932.14 |         5 | 7492.278027 | 张三,李四,赵六,Mr Adword,孙八 |
|  3 | 女   |      45 |      26 |    9132.99 |         2 | 7532.955078 | 王五,Mr lili                  |
|  5 | 保密 |      19 |      19 |    4932.92 |         2 | 4932.919922 | 田七,田七                     |
+----+------+---------+---------+------------+-----------+-------------+-------------------------------+


4、使用HAVING 对分组结果进行二次筛选

mysql>  -- 按照性别分组,并找到分组后组中人数大于3的组
mysql> SELECT id,sex,COUNT(*) AS total_peo FROM employee GROUP BY sex HAVING COUNT(*)>3;
+----+------+-----------+
| id | sex  | total_peo |
+----+------+-----------+
|  1 | 男   |         5 |
+----+------+-----------+

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值