【MySQL】第五弹——表的CRUD进阶(三)聚合查询(上)

🌅聚合函数

聚合查询本质上是针对数据表中的行与行进行运算

之前我么你介绍的表达式查询,是 对一行记录中的列和列之间进行运算

比如:语文成绩+数学成绩+英语成绩

• 常见的联合查询函数

都是一些在MYSQL里内置的一些函数

注意:这些操作都是针对一行记录中的某一列进行运算的

🌊1.COUNT();统计所有行

但是在工作中还是推荐大家使用count(*),因为这种写法是SQL标准中规定的

• 如果表中某一列数据出现NULL会怎样?

count(列名),如果列中有NULL值,则不会被统计在内

🌊2. SUM(列名); 求和

把查询结果中所有行中的指定列进行相加

注意:列中的数据类型必须是数值型,不能是字符或日期…

• 计算所有学生语文成绩总分

• 如果表中某一列数据出现NULL会怎样?–NULL进行运算结果会是NULL吗

之前我们演示过NULL与任何类型的值进行运算结果都是NULL

在SUM()求和时,NULL值不参与运算

虽然我们介绍过NULL 与任何类型得到值进行运算结果都是NULL,但是MYSQL的创始人写这个内置函数的目的是为了check有没有NULL值吗–并不是,所以我们以后再进行一些设计的时候也要考虑到用户的具体需求,我们只是为了得到总分,不是要检查是否存在NULL值,注意用户需要这个功能的主要目的,再进行设计

如果以后我们做一些公共接口开发时,一定要考虑到用户的真实意图是什么,把特殊值做特殊处理

• 如果对非数字类型的列进行求和运算,会得到一些警告信息

🌊3. AVG() 求平均

对所有行的指定列进行求平均值运算

1. 对所有同学的语文成绩求平均值

2. 求语文,数学,英语三门课的总分的平均值

🌊4. MAX(),MIIN()

求所有行中指定列的最大值,最小值

1. 找出语文成绩的最高分和英语成绩的最低分

2. 查找语文成绩的最高分和最低分

在使用关于运算的聚合函数时,不要使用在非数值的列上,比如sum(),avg(),max(),min()

🌅分组查询

🌊GROUP BY 子句

select 中使用 group by 子句可以对指定列进行分组查询

使用group by 进行分组查询时,select指定的字段必须是’分组依据字段’,其他字段若想出现在select 中则必须包含在聚合函数中

语法:

• 示例:使用emp表进行group by子句演示

1. 计算不同角色工资平均值

要分组的是 role 列,MYSQL 内部先分组再计算

这样小数点太多了,观感不好,我们使用ROUND(数值,小数点位数) 来规定一下工资的格式

group by 子句之后可以跟 order by 子句

🌊HAVING

GROUP BY 子句进行分组后,需要对分组结果再进行条件过滤,不能使用 WHERE 语句,而需要使用 HAVING

where 是对表中每一行的真实数据进行过滤

having 是对group by 之后,计算出来的结果进行过滤

where 用在 from 表名之后么也就是分组之前
having 跟在 order by 子句之后
如果需要对真实数据进行过滤,也需要对分组的结果进行过滤
那只需要在合适的位置写入 where 和 having 即可

• 查询每个角色的最高工资、最低工资和平均工资

步骤:
①.按角色进行分组
②.使用相应的聚合函数

• 显示平均工资低于1500的角色和他的平均工资

步骤:
1.按角色分组
2.使用相应的聚合函数
3.使用having子句对分组的结果进行过滤

注意: having要跟在 group by 列名之后

🌅联合查询

工作中用的最多的查询,而且面试中也经常考察

🌊联合多个表进行查询

设计表时把表进行拆分,为了消除表中的字段间的依赖关系,比如部分函数依赖和传递依赖(第二、第三范式)

这是会导致一条SQL语句查出来的数据,对于业务来说是不完整的,我们就可以使用联合查询把关系表中的数据全部查出来,在一个数据行中显示详细的信息

两张表产生了主外键关系,但这并不是我们想要的结果集

这个结果集才是我们想要的

🏄‍♂️联合查询是MYSQL内部是如何执行的?

💦1. 取多张表的笛卡尔积

对多张表进行笛卡尔积的过程
1.先从第一张表中取一条记录,然后再与第二张表中的第一条记录进行组合,生成一条新的记录
2.先从第一张表中取一条记录,然后再与第二张表中的第二条记录进行组合,生成一条新的记录
…

最后得到的结果就是一个全排列的结果集

语法:select * from 表名,表名

两张表取笛卡尔积之后,有些数据是无效数据,如何过滤掉无效数据?

💦2. 通过连接条件过滤无效数据

两个表之间是有主外键关系的,只需要判断两个表主外键字段是否相等即可

因为class_id 在两张表中都存在,MYSQL 分不清楚当前语句的class_id 应该取自哪个表

所以通过表名.列名的方式来解决这个问题

💦3.通过指定列查询,来精简结果集

查询时通过表名.列名指定要查询出的字段

这样查询要写的子句太长了,我们通常使用别名来简化SQL语句

student–>s
class–>c

联合查询也叫做表连接查询
1.首先确定哪几张表要参与查询
2.根据表与表之间的主外键关系,确定过滤条件
2.精简查询字段,得到想要的结果

🌊内连接

语法:

三种写法
1.使用select from … where and
2.使用select from … inner join on …
3.使用select from … join on … --内连接标准写法省略inner

以上三种写法,工作学习中自己选一个合适的即可

🌊示例练习

DROP TABLE IF EXISTS `score`;
DROP TABLE IF EXISTS `student`;
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `course`;

-- ----------------------------
-- Table structure for class
-- ----------------------------

CREATE TABLE `class`  (
  `class_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '计算机系2019级1班');
INSERT INTO `class` VALUES (2, '中文系2019级3班');
INSERT INTO `class` VALUES (3, '自动化2019级5班');

-- ----------------------------
-- Table structure for course
-- ----------------------------

CREATE TABLE `course`  (
  `course_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java');
INSERT INTO `course` VALUES (2, '中国传统文化');
INSERT INTO `course` VALUES (3, '计算机原理');
INSERT INTO `course` VALUES (4, '语文');
INSERT INTO `course` VALUES (5, '高阶数学');
INSERT INTO `course` VALUES (6, '英文');

-- ----------------------------
-- Table structure for student
-- ----------------------------

CREATE TABLE `student`  (
  `student_id` bigint NOT NULL AUTO_INCREMENT,
  `sn` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `class_id` bigint NULL DEFAULT NULL,
  PRIMARY KEY (`student_id`) USING BTREE,
  UNIQUE INDEX `sn`(`sn` ASC) USING BTREE,
  INDEX `class_id`(`class_id` ASC) USING BTREE,
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO `student` VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO `student` VALUES (3, '00391', '白素贞', NULL, 1);
INSERT INTO `student` VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO `student` VALUES (5, '00054', '不想毕业', NULL, 1);
INSERT INTO `student` VALUES (6, '51234', '好好说话', 'say@qq.com', 2);
INSERT INTO `student` VALUES (7, '83223', 'tellme', NULL, 2);
INSERT INTO `student` VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 2);

-- ----------------------------
-- Table structure for score
-- ----------------------------

CREATE TABLE `score`  (
  `score_id` bigint NOT NULL AUTO_INCREMENT,
  `student_id` bigint NULL DEFAULT NULL,
  `course_id` bigint NULL DEFAULT NULL,
  `score` decimal(5, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`score_id`) USING BTREE,
  INDEX `student_id`(`student_id` ASC) USING BTREE,
  INDEX `course_id`(`course_id` ASC) USING BTREE,
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 70.50);
INSERT INTO `score` VALUES (2, 1, 3, 98.50);
INSERT INTO `score` VALUES (3, 1, 5, 33.00);
INSERT INTO `score` VALUES (4, 1, 6, 98.00);
INSERT INTO `score` VALUES (5, 2, 1, 60.00);
INSERT INTO `score` VALUES (6, 2, 5, 59.50);
INSERT INTO `score` VALUES (7, 3, 1, 33.00);
INSERT INTO `score` VALUES (8, 3, 3, 68.00);
INSERT INTO `score` VALUES (9, 3, 5, 99.00);
INSERT INTO `score` VALUES (10, 4, 1, 67.00);
INSERT INTO `score` VALUES (11, 4, 3, 23.00);
INSERT INTO `score` VALUES (12, 4, 5, 56.00);
INSERT INTO `score` VALUES (13, 4, 6, 72.00);
INSERT INTO `score` VALUES (14, 5, 1, 81.00);
INSERT INTO `score` VALUES (15, 5, 5, 37.00);
INSERT INTO `score` VALUES (16, 6, 2, 56.00);
INSERT INTO `score` VALUES (17, 6, 4, 43.00);
INSERT INTO `score` VALUES (18, 6, 6, 79.00);
INSERT INTO `score` VALUES (19, 7, 2, 80.00);
INSERT INTO `score` VALUES (20, 7, 6, 92.00);

在之前创建好的表结构中,导入初始化数据

🏄‍♂️内连接练习

1.首先确定哪几张表要参与查询
2.根据表与表之间的主外键关系,确定过滤条件
3.精简查询字段,得到想要的结果

• 查询许仙同学的成绩

三个步骤一个一个来

1.确定哪几张表参与查询(取笛卡尔积) – 成绩表 学生表

取两张表的笛卡尔

2.根据表与表之间的主外键关系,确定过滤条件

两张表中通过student_id作为主外键关联字段

根据具体需求确定结果集的过滤条件

在where语句中,添加 student.name = ‘许仙’ 的过滤条件

3.精简查询列表中的字段

学生名 分数

🏄‍♂️联合查询练习

联合查询步骤细化之后:
1.确定查询中涉及哪些表
2.对目标表取笛卡尔积
3.确定连接条件
4.确定对整个结果集的过滤条件
5.精简查询字段

• 查询所有同学的总成绩及同学的个人信息

总成绩–>需要用到聚合函数SUM 分组查询group by

1.确定哪几张表参与查询 – 成绩表 学生表

分组查询时用学生编号好,还是用学生姓名好?–编号的目的就是为了保证记录的唯一性

2.目标表取笛卡尔积

3.确定连接关系(过滤条件)

4.按学生id进行分组,并在查询列表中,使用聚合函数就散总成绩

5.在查询列表中精简确定要查询的字段