七、MySQL基础系列笔记之SQL DQL分组、统计查询

分组查询

分组查询是对数据按照某个或多个字段进行分组。

语法

[group by 字段] [having <条件表达式>]

按照字段值进行分组，having对分组的结果进行筛选。

# 根据工资进行分组统计
mysql> select salary,count(*) as total from emp group by salary;

group_concat-将多行数据合并为一行

mysql> select salary,count(*) as total,group_concat(name) from emp group by salary having total >= 2;

with rollup-查询分组记录总和

mysql> select salary,count(*) as total from emp group by salary with rollup;
+---------+-------+
| salary  | total |
+---------+-------+
|    NULL |     2 |
|  800.00 |     1 |
|  900.00 |     2 |
| 1000.00 |     1 |
|    NULL |     6 |
+---------+-------+

group by结合排序

mysql> select o_num, sum(quantity*item_price) as total from orderitems group by o_num having sum(quantity*item_price) >= 100 order by total desc;

limit-限制查询结果

语法

limit [偏移量,] 行数;

# 查询前2个结果
mysql> select * from emp limit 2;

# 从第2 条记录开始查询2条数据
mysql> select * from emp limit 2,2;

聚合函数

聚合函数有age()、count()、max()、min()、sum()

count()-统计数据表中包含记录的总行数

• count(*)计算表中的总行数，不管某列有数值或者为空值
• count(字段名)计算指定列下的总函数，计算时忽略空值行

mysql> select * from emp;
+-----------+---------+
| name      | salary  |
+-----------+---------+
| wangwu    |  800.00 |
| lily      | 1000.00 |
| aimi      |  900.00 |
| lucy      |    NULL |
| wangmeili |    NULL |
| zhaoliu   |  900.00 |
+-----------+---------+
6 rows in set (0.00 sec)

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|        6 |
+----------+

mysql> select count(salary) from emp;
+---------------+
| count(salary) |
+---------------+
|             4 |
+---------------+

sum()-求总和

mysql> select count(*) as number ,sum(salary) as total from emp group by salary;
+--------+---------+
| number | total   |
+--------+---------+
|      1 |  800.00 |
|      1 | 1000.00 |
|      2 | 1800.00 |
|      2 |    NULL |
+--------+---------+

avg()-求平均值

mysql> select avg(salary) as avg_salary from emp;
+------------+
| avg_salary |
+------------+
| 900.000000 |
+------------+

max()-求最大值

mysql> select max(salary) from emp;

min()-求最小值

mysql> select min(salary) from emp;

2020-10-10