【MySQL】基本查询（三）聚合函数+group by

建立如下表

//创建表结构
mysql> create table exam_result(
    -> id int unsigned primary key auto_increment,
    -> name varchar(20) not null comment '同学姓名',
    -> chinese float default 0.0 comment '语文成绩',
    -> math float default 0.0 comment '数学成绩',
    -> english float default 0.0 comment '英语成绩'
    -> );


//插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

一. 聚合函数

统计本次考试的数学成绩分数个数

mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           7 |
+-------------+

//去重后的数学成绩数量
mysql> select count(distinct math) from exam_result;
+----------------------+
| count(distinct math) |
+----------------------+
|                    6 |
+----------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

统计数学成绩总分

mysql> select sum(math) 数学总成绩 from exam_result;
+-----------------+
| 数学总成绩      |
+-----------------+
|             649 |
+-----------------+

//统计数学成绩<90的同学们的数学成绩总分
mysql> select sum(math) from exam_result where math<90;
+-----------+
| sum(math) |
+-----------+
|       153 |
+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

统计平均分

//sum和count的结合
mysql> select sum(math+english+chinese)/count(math)
from examm_result;
+---------------------------------------+
| sum(math+english+chinese)/count(math) |
+---------------------------------------+
|                    229.14285714285714 |
+---------------------------------------+
//avg的使用
mysql> select avg(english+chinese+math) from exam_result;
+---------------------------+
| avg(english+chinese+math) |
+---------------------------+
|        229.14285714285714 |
+---------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

---

返回英语最高分

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1
2
3
4
5
6

返回数学>70的最低分

mysql> select min(math) from exam_result where math>70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1
2
3
4
5
6

二. group by子句

在select中使用group by 子句可以对指定列进行分组查询

select 属性1，属性2 ... from table_name group by 属性

实验：使用oracle 9i的经典测试表

CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);

CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编>号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号
'
);

CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);

mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO   |     | NULL    |       |
| dname  | varchar(14)              | YES  |     | NULL    |       |
| loc    | varchar(13)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+

mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+

mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES  |     | NULL    |       |
| losal | int(11) | YES  |     | NULL    |       |
| hisal | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

emp员工表
dept部门表
salgrade工资等级表

如何显示每个部门的平均工资和最高工资

mysql> select deptno,avg(sal) 平均工资,max(sal) 最大工资 fromm emp group by deptno;
+--------+--------------+--------------+
| deptno | 平均工资     | 最大工资     |
+--------+--------------+--------------+
|     10 |  2916.666667 |      5000.00 |
|     20 |  2175.000000 |      3000.00 |
|     30 |  1566.666667 |      2850.00 |
+--------+--------------+--------------+
1
2
3
4
5
6
7
8

显示每个部门的每种岗位的平均工资和最低工资

mysql> select avg(sal) 平均工资,min(sal) 最小工资,job,deptno  from emp group by deptno,job;
+--------------+--------------+-----------+--------+
| 平均工资     | 最小工资     | job       | deptno |
+--------------+--------------+-----------+--------+
|  1300.000000 |      1300.00 | CLERK     |     10 |
|  2450.000000 |      2450.00 | MANAGER   |     10 |
|  5000.000000 |      5000.00 | PRESIDENT |     10 |
|  3000.000000 |      3000.00 | ANALYST   |     20 |
|   950.000000 |       800.00 | CLERK     |     20 |
|  2975.000000 |      2975.00 | MANAGER   |     20 |
|   950.000000 |       950.00 | CLERK     |     30 |
|  2850.000000 |      2850.00 | MANAGER   |     30 |
|  1400.000000 |      1250.00 | SALESMAN  |     30 |
+--------------+--------------+-----------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

显示平均工资低于2000的部门和其平均工资
having和group by配合使用，筛选group by的结果
having和where的效果相同，但是因为where的执行顺序比group by先，所以无法筛选结果，having在group by 后，可以配合group by进一步筛选结果

mysql> select deptno,avg(sal) 平均工资 from emp group by depttno having 平均工资<2000;
+--------+--------------+
| deptno | 平均工资     |
+--------+--------------+
|     30 |  1566.666667 |
+--------+--------------+
1
2
3
4
5
6

SQL查询中各个关键字的执行先后顺序：
from>on>join>where>group by>with>having>select>distinct>order by>limit

结束语

感谢你的阅读

如果觉得本篇文章对你有所帮助的话，不妨点个赞支持一下博主，拜托啦，这对我真的很重要。