[MySQL] MySQL 聚合函数排序

• MySQL 结果排序-- 聚集函数
  
  
  
  • 环境
    
  • 查询结果排序
    
  • 查询的分组与汇总
    
  • 查一下 学生们平均年龄
    
  • 查一下总人数是多少
    
  • 查一下每个年龄有多少人
    
  • 查出最大年龄
    
  
  
• 总结
  

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `student_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别\r\n',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `result` double(10, 0) NULL DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES (1, '小王', '男', 18, 90);
INSERT INTO `student` VALUES (2, '小李', '女', 19, 80);
INSERT INTO `student` VALUES (3, '小明', '男', 20, 85);
INSERT INTO `student` VALUES (4, '小张', '男', 21, 87);

SELECT 字段名1… FROM 表名 ORDER BY 字段名1 [ASC | DESC ] ，字段名2 [ASC | DESC ]…;

SELECT * FROM  student ORDER BY  age DESC ;

select AVG(age) as "年龄"  from student;

select count(id) as "总人数" from student;

select age, count(id) as "总人数" from student GROUP BY age;

select MAX(age) as "最大年龄" from student ;

select sex ,count(*) AS "人数" from student GROUP BY sex;
#GROUP BY 是将结果按照 后面跟的字段名分组

select sum(result) as "成绩总分" FROM student;