排序和统计

排序

排序可以对枚举类型，数值类型等进行排序；排序是先拿数据，再对数据做排序处理（因此条件WHERE是在排序ORDER之前的）

排序是我们对查询到的结果进行排序，排序分为降序（从大到小DESC）和升序（从小到大ASC，排序默认是升序的）

• 对年龄进行升序排序：SELECT * FROM stu ORDER BY age ASC;
• 对性别进行降序排序，之后再对年龄进行升序排序：SELECT * FROM stu ORDER BY sex DESC, age ASC;

对于想要获取最大的数据，我们可以先进行排序，再进行选取一条记录：LIMIT 1

根据学生出生的月份进行降序排序：SELECT sname, birthday, MONTH(birthday) as m FROM stu ORDER BY m DESC;

随机排序

当我们想要随机获取一些数据，我们可以使用随机函数进行生成：SELECT RAND();（为某条数据生成一个字段，字段中是随机数）

我们可以使用随机函数产生的随机顺序进行排序：SELECT * FROM stu ORDER BY RAND() DESC;

随机取一条数据：SELECT * FROM stu ORDER BY RAND() DESC LIMIT 1;

自定义排序

自定义排序是使用集合进行排序的

SELECT field('a','f','a','b','c'); 返回的结果为2，表示a在集合（fabc）中第2个位置出现

使用上述的特性可以进行自定义的排序，如希望某些字段在前面出现

按照学生的姓式进行排序：SELECT sname, left(sname,1) as s FROM stu ORDER BY FIELD(s, '金') DESC;

将姓金的排在最前面

增加姓式列表：'金'，’李‘，’陈‘，其他不变，那么陈就在最上面，之后是李，最后是金，（s中于金匹配的，结果就为1，s中与李匹配的，结果就是2，经过降序排列，结果大的就在上面，s中的内容如果集合中没有，结果就取0，就放在最后了）

---

统计

统计数量

统计是对我们查询到的结果进行的一个汇总，使用函数count()进行汇总

• 汇总查询到所有的条目数据：SELECT count(*) FROM stu; 查询到几条数据就返回几

• 查询学生表中所有男同学的数量：SELECT COUNT(*) FROM stu WHERE sex = '男';

*表示对所有的记录进行统计，COUNT(*)表示满足条件的记录的行数；COUNT(列)表示统计某列满足条件的有多少个，但是会排除为null的

CREATE TABLE t (name VARCHAR(20));
INSERT INTO t VALUES('jlc');
INSERT INTO t VALUES('tom');
INSERT INTO t VALUES(NULL);

SELECT COUNT(*) FROM t;  -- 3
SELECT COUNT(name) FROM t;  -- 2

我们可以进行有条件的统计，学生表中有的学生分配了班级，有的学生没有分配班级，我们需要统计分配了班级的学生数量：

• 方法一：SELECT COUNT(*) FROM stu WHERE class_id IS NOT NULL;
• 方法二：SELECT COUNT(class_id) FROM stu; 统计明确的字段，在统计的时候是不会记录NULL值的

最值

对于最值的获取，我们之前是使用排序并取最上面一个的方式进行获取，但是mysql系统提供了两个内置的系统函数：MIN和MAX

• 获取某字段的最大值：SELECT MAX(age) FROM stu;
• 获取某字段的最小值：SELECT MIN(age) FROM stu;
• 获取年龄最小学生的出生年份：SELECT year(max(birthday)) from stu; 出生年份最大，年龄最小

当我们使用分组操作或者聚合函数的时候（统计、最值），如果使用额外的字段，如：SELECT MIN(age), id FROM stu;（其中的id就是额外的字段），在一些新版的mysql中会出现ONLY_FULL_GROUP_BY问题

对于这个错误，可以在一些框架的配置文件中进行配置的更改，配置为允许可以使用其他的字段

如果在sql命令行中继续操作，我们将SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));命令放到SELECT MIN(age), id FROM stu;命令前执行即可

求和和平均

求和和平均在mysql中提供了两个系统函数：SUM和AVG（上述两种函数，只对数值有用）

• 获取文章总的点击次数：SELECT SUM(click) FROM article;
• 获取文章平均的点击次数：SELECT AVG(click) FROM article;
• 获取小于平均点击数的文章信息：SELECT * FROM article WHERE click < (SELECT AVG(click) FROM article);

round()函数表示四舍五入，一般和取平均进行结合使用

去重

在我们获取班级的编号时，往往会出现一些相同的值，这个时候进行去重操作，将相同的过滤掉，我们可以使用关键字DISTINCT进行操作：SELECT DISTINCT class_id FROM stu WHERE class_id is not null;

组合去重

SELECT DISTINCT class_id,name FROM stu WHERE class_id is not null;表示将两个字段（class_id和name）的内容放到一起进行过滤去重，只有同一个班级和同一个姓名的内容将会被去重，即将两个重复的归类到一组

分组统计

使用group by子句对列进行分组；使用having子句对分组后的结果进行过滤

比如我们要统计每个班级有多少个学生，这个时候就需要使用到分组统计的方式：先使用班级编号进行分组，再进行统计，具体语句如下：

SELECT count(class_id),class_id from stu GROUP BY class_id;

统计每个班级年龄最大的学生，即出生年月最小的：

SELECT min(birthday),class_id from stu GROUP BY class_id;

在分组统计的时候可以进行条件的添加，如条件每个班级中有多少个男同学：

SELECT count(class_id),class_id from stu WHERE sex = '男' GROUP BY class_id;

总而言之，GROUP BY是对查询结果进行分组，总是出现在WHERE条件的后面

多字段的分组

多字段的分组表示分组的条件不单单只有一个，如在按照班级分组的同时，又要按照性别进行分组，如：

SELECT count(*), class_id, sex from stu GROUP BY class_id, sex;

统计每个班男生有几个，女生有几个

使用多字段分组进行统计，是按照对应字段的先后依次进行统计的

对统计完的分组进行排序，排序的位置是在分组后面，要先分组再进行排序

SELECT count(*),class_id,sex from stu GROUP BY class_id,sex ORDER BY class_id desc;

将分组的内容根据class_id进行大到小排序

筛选分组

对分组的结果进行筛选（过滤），要使用HAVING方法，原先的WHERE条件是对查询到的结果进行筛选

SELECT count(*),class_id from stu GROUP BY class_id HAVING count(*)>=2;

对班级进行分组，并筛选出每组中人数大于等于2的组别

分组筛选用于筛选出一部分满足条件的组

查询本周迟到超过2次的同学信息：

SELECT count(*) as c stu_id FROM stu 
WHERE date(created_at)>=date(date_add(now(),interval 0-WEEKDAY(now()) day)) 
AND time(create_at)>'08:30:00'
GROUP BY stu_id HAVING c>=2;

查询哪个同学本周准时到校次数最多：

SELECT count(*) as c stu_id FROM stu 
WHERE date(created_at)>=date(date_add(now(),interval 0-WEEKDAY(now()) day)) 
AND time(create_at)<='08:30:00'
GROUP BY stu_id ORDER BY c desc limit 1;

查找本周哪天迟到的学生最多的数据：

SELECT * FROM stu 
WHERE date(created_at)>=date(date_add(now(),interval 0-WEEKDAY(now()) day)) 
AND time(create_at)>'08:30:00'
GROUP BY create_at ORDER BY count(*) desc limit 1;

查找哪个姓氏的学生最多：

SELECT left(sname, 1) as s from stu
GROUP BY s ORDER BY count(*) desc limit 1;

先对查询结果进行筛选，筛选完后进行分组，再按照每一组的人数进行排序，取最多的一个

查询超过2个同学的姓氏：

SELECT left(sname, 1) as s from stu
GROUP BY s HAVING count(*)>=2;

如果SELECT语句同时包含group by，having，limit，order by，那么他们的顺序是（由先到后）group by、having、order by、limit，如果顺序不对，语法是不会通过的