mysql数据查询之五子句(where、group by、having、order by和limit)

概念了解：

select 语句中包含一个查询子句 （五子句，包括 where 、 group by 、 order by  、 having 和 limit ）

1.where 

经常用到，就不细说了，可以搭配一些比较运算符>,<…；逻辑运算符&&，||，!

2.group by 

(1) 将表中的数据按sex进行分组，并用统计函数进行统计

select sex,count(*),max(age),min(age),avg(age),sum(age) from student group by sex;

cout()：统计分组后，每组的总记录数；

(2)按sex进行分组之后并排序

select sex,count(*) from student group by sex asc;

(3)先按grade进行分组，再按sex进行分组

select *,count(*) from student group by grade,sex;

(4)group_connect(字段名) 可以对分组的结果的某个字段值进行字符串连接，保留该组某个字段的所有值

select grade,count(*),group_concat(name)  from student group by grade;

+-------+----------+--------------------+

| grade | count(*) | group_concat(name) |

+-------+----------+--------------------+

|     3 |        4 | qiao,zhang,li,chen |

|     4 |        3 | li,zhao,qian       |

+-------+----------+--------------------+

(5)with rollup 关键字，可以在每次分组之后，根据当前分组的字段进行统计，并向上一级进行汇报，可以和ifnull()混用

select grade,count(*) from student group by grade with rollup;

+-------+----------+

| grade | count(*) |

+-------+----------+

|     3 |        4 |

|     4 |        3 |

|  NULL |        7 |

+-------+----------+

3 rows in set (0.00 sec)

select ifnull(grade,'总计'),count(*) from student group by grade with   llup;

+------------------------+----------+

| ifnull(grade,'总计')   | count(*) |

+------------------------+----------+

| 3                      |        4 |

| 4                      |        3 |

| 总计                   |        7 |

+------------------------+----------+

3.having

可以把where看作where的子集，having有一些where之外的能力

(1)统计函数只能having使用

select grade,count(*) from student group by grade having count(*) >= 2;

(2)having可以使用字段别名,where不能

select grade,count(*) as total from student group by grade having total >= 2

4.order by +  [asc/desc]

将表中的数据先按age升序排序(默认)，再按grade降序排序

select * from student order by age , grade desc;

5.limit

(1)用来限制长度，查询表中的指定条数据

select * from student limit 3;

(2)限制起始值，限制指定长度

select * from student limit 2,2;