1、GROUP BY:分组函数
使用要求:可以包含任意数目的列,可以进行分组嵌套;
GROUP BY列出的每一列都必须是检索列或是有效的表达式(不能是聚集函数);
如果SELECT 列使用了表达式,则分组时GROUP BY也必须使用表达式,不能使用别名,例如一些时间的提取时使用YEAR(字段) 别名,则分组应该使用YEAR(字段),而不是别名;
GROUP BY 字段一般不允许包含长度可变的数据类型,例如一些备注字段;
GROUP BY 通常是和聚集函数(AVG,SUM,COUNT,MAX,MIN)一起使用的,GROUP BY 列需要包含SELECT部分除了聚合函数外的全部字段;
如果GROUP BY列中有NULL值存在,则NUL值会分为一组;
GROUP BY 使用位置必须在WHERE之后,ORDER BY之前。
#1、计算各学系有多少人次,其中男女各有多少人次
SELECT
t.dept ,
t.ssex,
COUNT(t.sno)num
FROM
student t
where 1=1
group by t.dept, t.ssex;
#2、计算各学系有多少人次,其中男女各有多少人次,并排序,默认升序,降序DESC
SELECT
t.dept ,
t.ssex,
COUNT(t.sno)num
FROM
student t
where 1=1
group by t.dept, t.ssex
ORDER BY num; /* 这里也可以写成 ORDER BY COUNT(t.sno) 或是 ORDER BY 3,表达一个意思。ORDER BY 之后可以使用别名或者表达式,还能使用栏位的下标*/
2、HAVING子句
类似WHERE子句,不同之处在于WHERE子句过滤行,HAVING子句过滤分组数据。当使用了分组后,想要再过滤数据,就用到HAVING子句了。HAVING子句位置在GROUP BY 之后,ORDER BY 之前。
#3、计算各学系有多少人次,统计人数大于等于3的,
SELECT
t.dept ,
count(t.sno)num
FROM
student t
where 1=1
group by t.dept
having count(t.sno)>=3;
#4、统计各学系人次,统计人数大于等于3的,并按人数降序排列
SELECT
t.dept ,
count(t.sno)num
FROM
student t
where 1=1
group by t.dept
having count(t.sno)>=3
order by count(t.sno) desc;
3、ORDER BY :排序函数
ORDER BY 一般使用都在SQL查询的最后一行,不过在MYSQL中,它还要在LIMIT之前;
可以单字段排序,也可以多字段排序;
排序字段可以使用表达式,也可以使用别名,还可以使用栏位的下标。
#5、计算各学系有多少人次,其中男女各有多少人次
SELECT t.dept ,t.ssex, count(t.sno)num FROM student t where 1=1 group by t.dept, t.ssex ORDER BY num;
SELECT t.dept ,t.ssex, count(t.sno)num FROM student t where 1=1 group by t.dept, t.ssex ORDER BY count(t.sno);
SELECT t.dept ,t.ssex, count(t.sno)num FROM student t where 1=1 group by t.dept, t.ssex ORDER BY 3;
-- 多字段排序,可以给每个字段指定排序方式
SELECT t.dept ,t.ssex, count(t.sno)num FROM student t where 1=1 group by t.dept, t.ssex ORDER BY t.dept ASC ,t.ssex DESC,num;
SELECT t.dept ,t.ssex, count(t.sno)num FROM student t where 1=1 group by t.dept, t.ssex ORDER BY 1 ASC,2 DESC,3 DESC;
4、以上演示数据表语句,演示环境MYSQL8.0。
/*
Navicat Premium Data Transfer
Source Server : mysql8.0
Source Server Type : MySQL
Source Server Version : 80028
Source Host : localhost:3306
Source Schema : 测试库2
Target Server Type : MySQL
Target Server Version : 80028
File Encoding : 65001
Date: 28/05/2022 18:15:39
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` varchar(7) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
`sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`ssex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
`sage` int(0) NULL DEFAULT NULL COMMENT '年龄',
`dept` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所在系',
PRIMARY KEY (`sno`) USING BTREE,
INDEX `SNO`(`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('0811101', '李勇', '男', 21, '计算机系');
INSERT INTO `student` VALUES ('0811102', '刘晨', '男', 20, '计算机系');
INSERT INTO `student` VALUES ('0811103', '王敏', '女', 20, '计算机系');
INSERT INTO `student` VALUES ('0811104', '张小红', '女', 19, '计算机系');
INSERT INTO `student` VALUES ('0821101', '张立', '男', 20, '信息管理系');
INSERT INTO `student` VALUES ('0821102', '吴宾', '女', 19, '信息管理系');
INSERT INTO `student` VALUES ('0821103', '张海', '男', 20, '信息管理系');
INSERT INTO `student` VALUES ('0831101', '钱小平', '女', 21, '通信工程系');
INSERT INTO `student` VALUES ('0831102', '王大力', '男', 20, '通信工程系');
INSERT INTO `student` VALUES ('0831103', '张珊珊', '女', 19, '通信工程系');
SET FOREIGN_KEY_CHECKS = 1;