聚合函数（max最大，min最小，avg平均，sum求和，count数量）
查询年龄最大的学生mysql> select id,name,max(age),grade,tel,sex from t_people;
+----+------+----------+--------+-------------+-----+
| id | name | max(age) | grade | tel | sex |
+----+------+----------+--------+-------------+-----+
| 1 | 张三 | 43 | 一年级 | 18989898899 | 1 |
+----+------+----------+--------+-------------+-----+
1 row in set
查询年龄最小的学生
mysql> select id,name,min(age),grade,tel,sex from t_people;
+----+------+----------+--------+-------------+-----+
| id | name | min(age) | grade | tel | sex |
+----+------+----------+--------+-------------+-----+
| 1 | 张三 | 18 | 一年级 | 18989898899 | 1 |
+----+------+----------+--------+-------------+-----+
1 row in set
查询所有学生的平均年龄
mysql> select avg(age) from t_people;
+----------+
| avg(age) |
+----------+
| 26.7857 |
+----------+
1 row in set
求所有学生年龄的总和
mysql> select sum(age) from t_people;
+----------+
| sum(age) |
+----------+
| 375 |
+----------+
1 row in set
查询每个班的有几个人
mysql> select grade,count(*) from t_people group
by grade;
+--------+----------+
| grade | count(*) |
+--------+----------+
| 一年级 | 4 |
| 三年级 | 4 |
| 二年级 | 4 |
| 四年级 | 2 |
+--------+----------+
4 rows in set
查询每个性别的平均年龄
mysql> select sex,avg(age) from t_people group by sex;
+-----+----------+
| sex | avg(age) |
+-----+----------+
| 0 | 21.3333 |
| 1 | 28.2727 |
+-----+----------+
2 rows in set
查询每个年级的平均年龄
mysql> select grade,avg(age) from t_people group by grade;
+--------+----------+
| grade | avg(age) |
+--------+----------+
| 一年级 | 18.2500 |
| 三年级 | 30.2500 |
| 二年级 | 24.5000 |
| 四年级 | 41.5000 |
+--------+----------+
4 rows in set
查询平均年龄大于20的班级平均年龄
mysql> select grade,avg(age) from t_people group by grade having avg(age)>20;
+--------+----------+
| grade | avg(age) |
+--------+----------+
| 三年级 | 30.2500 |
| 二年级 | 24.5000 |
| 四年级 | 41.5000 |
+--------+----------+
3 rows in set
having 相当于给分组查询加限定条件 注意 和聚合一起使用
注意：分组查询（ groupby ）一般与聚合函数一起使用。
I:模糊查询
查询名字是张开头的学生信息mysql> select * from t_people where name like '张%';
+----+------+-----+--------+-------------+-----+
| id | name | age | grade | tel | sex |
+----+------+-----+--------+-------------+-----+
| 1 | 张三 | 18 | 一年级 | 18989898899 | 1 |
| 8 | 张飞 | 23 | 二年级 | 18918823784 | 1 |
+----+------+-----+--------+-------------+-----+
2 rows in set
查询名字中带三的学生的信息
mysql> select * from t_people where name like '%三
%';
+----+------+-----+--------+-------------+-----+
| id | name | age | grade | tel | sex |
+----+------+-----+--------+-------------+-----+
| 1 | 张三 | 18 | 一年级 | 18989898899 | 1 |
+----+------+-----+--------+-------------+-----+
1 row in set
查询名字有两个字，其中一个是张的学生的信息
mysql> select * from t_people where name like '张_
';
+----+------+-----+--------+-------------+-----+
| id | name | age | grade | tel | sex |
+----+------+-----+--------+-------------+-----+
| 1 | 张三 | 18 | 一年级 | 18989898899 | 1 |
| 8 | 张飞 | 23 | 二年级 | 18918823784 | 1 |
+----+------+-----+--------+-------------+-----+
2 rows in set
J:分页查询
App中 数据量过大 无法一次展示完 都进行分页展示
分页一般有两种模式
A 方案：前台控制分页（js） 将数据全部查询出来 返回值前台网页，但是展示的时候 通过js 控制只展示某一页的内容