七、MySQL基础系列笔记之SQL DQL分组、统计查询
分组查询
分组查询是对数据按照某个或多个字段进行分组。
语法
[group by 字段] [having <条件表达式>]
按照字段值进行分组，having对分组的结果进行筛选。
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 根据工资进行分组统计</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> salary,<span style="box-sizing: border-box;color: rgb(199, 146, 234)">count</span>(*) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> total <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">group</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">by</span> salary;</span>
group_concat-将多行数据合并为一行
mysql> select salary,count(*) as total,group_concat(name) from emp group by salary having total >= 2;
with rollup-查询分组记录总和
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> salary,<span style="box-sizing: border-box;color: rgb(199, 146, 234)">count</span>(*) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> total <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">group</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">by</span> salary <span style="box-sizing: border-box;color: rgb(199, 146, 234)">with</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">rollup</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| salary | total |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">800.00</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">900.00</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1000.00</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+---------+-------+</span>
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 [偏移量,] 行数;
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查询前2个结果</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">limit</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 从第2 条记录开始查询2条数据</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">limit</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span>,<span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span>;</span>
聚合函数
聚合函数有age()、count()、max()、min()、sum()
count()-统计数据表中包含记录的总行数
- count(*)计算表中的总行数，不管某列有数值或者为空值
- count(字段名)计算指定列下的总函数，计算时忽略空值行
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-----------+---------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| name | salary |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-----------+---------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| wangwu | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">800.00</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| lily | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1000.00</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| aimi | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">900.00</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| lucy | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| wangmeili | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| zhaoliu | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">900.00</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-----------+---------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">count</span>(*) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(199, 146, 234)">count</span>(*) |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">count</span>(salary) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+---------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(199, 146, 234)">count</span>(salary) |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+---------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+---------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span>
sum()-求总和
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">count</span>(*) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> number ,<span style="box-sizing: border-box;color: rgb(199, 146, 234)">sum</span>(salary) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> total <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">group</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">by</span> salary;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+--------+---------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| number | total |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+--------+---------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">800.00</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1000.00</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1800.00</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+--------+---------+</span>
avg()-求平均值
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">avg</span>(salary) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> avg_salary <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| avg_salary |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(255, 83, 112)">900.000000</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------------+</span>
max()-求最大值
mysql> select max(salary) from emp;
min()-求最小值
mysql> select min(salary) from emp;
我是小白，期待和优秀的你一起同行!
小白
2020年10月10日
请登录后再评论