SQL排序/聚合函数/分组

  • 1)排序
  • 2)聚合函数
  • 3)分组

1)排序

  • 排序:order by 字段
  • asc : 从小到大排序,即升序
  • desc:从大到小排序,即降序

建表语句:students.sql

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `id` int(3) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '姓名',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  `height` double(5,0) DEFAULT NULL COMMENT '身高',
  `gender` varchar(5) COLLATE utf8_bin DEFAULT NULL COMMENT '性别',
  `cls_id` int(1) DEFAULT NULL COMMENT '班级编号',
  `is_delete` varchar(1) COLLATE utf8_bin DEFAULT NULL COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('1', '小明', '18', '180', '男', '1', '');
INSERT INTO `students` VALUES ('2', '小月', '18', '180', '女', '2', null);
INSERT INTO `students` VALUES ('3', '彭于晏', '29', '185', '男', '1', null);
INSERT INTO `students` VALUES ('4', '刘德华', '59', '175', '男', '2', null);
INSERT INTO `students` VALUES ('5', '黄蓉', '38', '160', '女', '1', null);
INSERT INTO `students` VALUES ('6', '凤姐', '28', '155', '保密', '2', null);
INSERT INTO `students` VALUES ('7', '王祖贤', '18', '172', '女', '1', null);
INSERT INTO `students` VALUES ('8', '刘亦菲', '30', '168', '女', '2', null);
INSERT INTO `students` VALUES ('9', '金星', '35', '170', '保密', '3', null);
INSERT INTO `students` VALUES ('10', '郭靖', '38', '175', '男', '4', null);
INSERT INTO `students` VALUES ('11', '大刘', '39', '175', '男', '5', null);

表如下所示:

mysql由大到小排序 mysql 从小到大排序_mysql由大到小排序

1.1)查询年龄在18-34之间的男性。按照年龄从小到大排序

SELECT * FROM students WHERE (age BETWEEN 18 AND 35) AND gender = '男' ORDER BY age ASC;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_四舍五入_02


1.2)查询年龄在18-34之间的女性,年龄从大到小排序,年龄相同的情况下按照身高从高到矮排序

SELECT * FROM students WHERE (age BETWEEN 18 AND 35) AND gender = '女' ORDER BY age DESC,height DESC;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_mysql_03

2)聚合函数

  • 总数:count ,查询男性和女性人数
  • 最小值: min ;
  • 最大值:max;
  • 平均值:avg—计算平均年龄— sum(age) / count(*)
  • 四舍五入:round(123.45 , 1) 保留一位小数

2.1)查询男性人数

SELECT COUNT(*) AS 男性人数 FROM students WHERE gender = '男'

表如下所示:

mysql由大到小排序 mysql 从小到大排序_mysql_04


2.2)查询年龄最大值

SELECT MAX(age) AS 年龄最大值 FROM students;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_数据库_05


2.3)计算所有人的年龄总和

SELECT SUM(age) AS 年龄总和 FROM students;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_四舍五入_06


2.4)计算所有人的年龄平均值,保留两位小数

SELECT ROUND(AVG(age),2) AS 年龄平均值 FROM students;
SELECT ROUND(SUM(age)/COUNT(*),2) AS 年龄平均值 FROM students;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_mysql_07

3)分组

3.1)通常和聚合函数结合起来使用

  • group by: 按照性别分组,查询所有的性别
SELECT ... FROM students GROUP BY gender;
... 中放置能够唯一区分按照gender分组的条件
SELECT gender AS 性别 FROM students GROUP BY gender;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_数据库_08


3.2)计算每种性别中的人数

SELECT gender AS 性别 ,COUNT(*) AS 人数 FROM students GROUP BY gender;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_mysql_09

SELECT gender AS 性别 ,GROUP_CONCAT(name) AS 姓名 FROM students GROUP BY gender;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_聚合函数_10


3.2)查询男性人数

SELECT gender AS 性别 ,COUNT(*) AS 人数 FROM students WHERE gender = '男' GROUP BY gender;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_mysql由大到小排序_11


3.3)查询同性别中的编号、姓名、年龄信息

SELECT gender AS 性别 ,GROUP_CONCAT(id,"_",name,"_",age) AS 编号_姓名_年龄 FROM students WHERE gender = '男' GROUP BY gender;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_聚合函数_12


3.4)查询平均年龄超过30的性别和姓名

SELECT gender AS 性别 ,GROUP_CONCAT(name) AS 姓名 ,AVG(age) FROM students GROUP BY gender HAVING AVG(age) > 30;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_数据库_13


3.5)查询每种性别中的人数多于2个的信息

SELECT gender AS 性别 ,GROUP_CONCAT(name) AS 姓名 FROM students GROUP BY gender HAVING COUNT(*) > 2;

表如下所示:

mysql由大到小排序 mysql 从小到大排序_数据库_14