MySQL：聚合查询&分组查询&联合查询

已于 2024-11-17 09:08:35 修改
阅读量1.2k 收藏 9
点赞数 34
文章标签： mysql 数据库
于 2024-11-16 21:32:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/blamep/article/details/143821283
版权

新建一张表，把旧表的内容插入到新表中，

语法：

INSERTINTOtable_name [(column [, column ...])] SELECT ...

a65fb4fa7f0e4130a1c1a664f64c4159.png

e214b98c83aa4eeb8dd8c58d569eb8e4.png

聚合查询（MySQL中的内置函数）

聚合查询的本质是针对数据库中的行和行进行运算。

 注：之前学的表达式查询，是列与列之间的计算

聚合函数

函数说明
COUNT([DISTINCT] expr)
返回查询到的数据的数量
SUM([DISTINCT] expr)
返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)
返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)
返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)
返回查询到的数据的最小值，不是数字没有意义

注：这些操作都是针对列进行的

 COUNT（）

统计所有的行

f52329ed31b541d390c5c286b14bb98e.png

常量也可以用来统计 

d5d9c7e1a5a5402dba1ebde31d2c1046.png

也可以指定某一个列

9b40d7b6147e4f05b3b431662d0ae2f8.png

注：NULL不参与统计

SUM（）

求和

aa21d18d917c47d8bbd0cf9dfbab90a2.png

查询英语成绩

308f83cdf1774dcba60fe84781c2582b.png

查询语文成绩

5684bc88843243bd8338b20b4f7626cd.png

注：

1.在后面可以跟where 约束

89a38c1f39cd45e8b1e190609c50c05c.png

2.非数值运算会报警告

47d8a8a10a0345bcaf4d2edba4d3d3a2.png

AVG（）

求平均值

求英语的平均值

d0f8fd5062a64d769578a03f520beeda.png

求总分的平均值

a2e507b939714c87bc128a2723155621.png

并且可以在后面跟别名

MAX（）  MIN（）

求最大值，求最小值

求数学的最小值，英语的最大值

8c3fa22d9e2942ec84a793fb67603c59.png

可以使用别名

d86474c73f9e495fa793c7bab931b97f.png

同一列可以使用不同的聚合函数（求数学的最高分和最低分）

b605443642254d3dbf4d53c4cb0dd3d0.png

GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

语法：

select column1, sum(column2), .. fromtable groupby column1,column3;

 02b79c10943b4df8a30b27150ee42321.png

drop table if exists emp;
create table emp (
	id bigint primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary decimal(10, 2) not null
);

insert into emp values (null, '大马', '老板', 1500000.00);
insert into emp values (null, '小马', '老板', 1800000.00);
insert into emp values (null, '小王', '老师', 100000.00);
insert into emp values (null, '小李', '老师', 120000.00);
insert into emp values (null, '小熊', '经理', 90000.00);
insert into emp values (null, '小花', '经理', 80000.00);
insert into emp values (null, '猪悟能', '游戏角色', 700.5);
insert into emp values (null, '沙和尚', '游戏角色', 333.3);

select * from emp;

 e18e0989726f434a80cfaccdcb12d9ca.png

练习：

计算不同角色工资的平均值

select role,avg(salary) from emp group by role;

254e74a5eeec4e51982aec2fc256e0c7.png

ROUND(数值，小数点后的位数)

select role,round(avg(salary),2) from emp group by role;

705c00a6ba6143298836b4fc47c004e1.png

后面可以跟ORDER BY 语句进行排序

select role,round(avg(salary),2) from emp group by role order by round(avg(salary),2) asc;

f59a2aacdbcf4f2ba9da96efee63649e.png

使用别名后：

select role,round(avg(salary),2) 平均工资 from emp group by role order by 平均工资 asc;

d33574e2ab32475e98736719511c8634.png

HAVING

 GROUP BY字句在分组之后，需要对分组的结果进行条件过滤时，不能使用where语句，而是需要HAVING

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

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

 找出平均工资大于50000小于200000的角色

having可以把这个结果集中的数据进行过滤操作，平均工资并不是表中真正的记录，而是通过聚合函数计算得来的。

 select role,round(avg(salary),2) from emp group by role having round(avg(salary),2)>50000 and round(avg(salary),2)<200000;

ce5e792bfd524a4582272cc39423d118.png

查询每个角色的最高工资，最低工资，平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

d2d1a3d50dc547079aad81976ce5600a.png

使用别名后

select role,max(salary) 最大工资,min(salary) 最少工资,avg(salary) 平均工资 from emp group by role;

31aecbbc878f418c9a510bd35143711a.png

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

 select role,avg(salary) from emp group by role having avg(salary)<100000;

0fb07ba1a7be4cdaada071ced940c4a9.png 注：having要跟在group by之后

联合查询（270adc3a888f4a71a7996e69f471b6ac.png）

联合多个表进行查询

1.取多张表的笛卡尔积

语法：select   *   from  表名，表名 

30fc733c3d52431e81dbc773ecaa7e15.png

注：大多数的数据都是无效数据 

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

 select * from class,student1 where class.class_id=student1.class_id;

edb6a013ea4747da8e3dddf4ff075df2.png

 3.通过指定的列循环，精简结果集

查询列表中通过表名.列名的方式指定查询字段

select student.id,student.name,class.name from class,student where class.class_id=student1.class_id;

45d034864dbd445fb683b182475926b0.png

给表名起别名简化SQL语句

select st.id,st.name,cl.name from class cl,student st where cl.class_id=st.class_id;

注：

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

内连接

语法：

select字段from表1 别名1 [inner] join表2 别名2 on连接条件and其他条件;
select字段from表1 别名1,表2 别名2 where连接条件and其他条件;

 先创建几个表

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




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;



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




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;



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, '英文');




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;


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);



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;

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.取两张表的笛卡尔积

select * from student,score;

336f55e4954b46e1bcf16520588b99d0.png

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

select * from student,score where student.student_id=score.student_id;

90b85bf1056f4d198b2f8956308fa9bf.png

确定集的过滤条件

 select * from student,score where student.student_id=score.student_id and student.name='许仙';

bef7fe74645f4fa0a431c0ce44f07482.png

3.精简字段 

 select student.name,score.score from student,score where student.student_id=score.student_id and student.name='许仙';

57249aca62144c4f9e766999bf43d762.png

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

1.取两张表的笛卡尔积

select * from student,score;

f0c9860600354dcbbdea717f7c572ee6.png

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

select * from student,score where student.student_id=score.student_id;

90b85bf1056f4d198b2f8956308fa9bf.png

确定集的过滤条件

select stu.student_id,sum(sc.score) 总分 from student stu,score sc  where stu.student_id=sc.student_id group by sc.student_id ;

4fe230e145eb4d8d8c9582956417d466.png

 3.精简字段 

select stu.student_id,stu.name,sum(sc.score) 总分 from student stu,score sc  where stu.student_id=sc.student_id group by sc.student_id ;

3e7163cec4514e26adc190e12b5ab15d.png

希望能对大家有所帮助！！！！

blammmp
关注
【MySQL】聚合函数
学习笔记。
08-06 538
函数 说明 COUNT([DISTINCT] expr) 返回查询到的数据的数量 SUM([DISTINCT] expr) 返回查询到的数据的总和，不是数字没有意义 AVG([DISTINCT] expr) 返回查询到的数据的平均值，不是数字没有意义 MAX([DISTINCT] expr) 返回查询到的数据的最大值，不是数字没有意义 MIN([DISTINCT] ...
MySQL总结：02_条件查询
cai_ing的博客
10-04 1128
条件查询 语法： select 查询列表 from 表名 where 筛选条件; 分类： 一、按条件表达式筛选 简单条件运算符： &gt; &lt; = != &lt;&gt; &gt;= &lt;= #案例1：查询工资&gt;12000的员工信息 SELECT * FROM employees WHERE salary&gt;12000; #案例2：查询部门编号不等于90号的员工名和部门编号 SELECT last_name, department_id
MySQL基础——聚合查询（全面解析）
最新发布
心碎烤肠的博客
04-25 779
聚合查询又叫函数查询，它是通过⼀些特定的函数根据需求来查询相关的信息，常见的聚合函数有：COUNT：求总数，不包含某字段为null值SUM： 求和，不包含某字段为null值AVG： 求平均值，不包含某字段为null值MAX： 求最大值，适用于数值、日期、字符串类型，不包含某字段为null值MIN： 求最小值，适用于数值、日期、字符串类型，不包含某字段为null值创建⼀个名为。
MySQL条件查询
2403_83831433的博客
07-02 906
MySQL从数据表中查询数据的基本语句是SELECT语句，语法如下:红色圈起来的是主语句，下面的为子语句。需要注意各句子的位置，如果位置不对，数据库会执行不了命令而报错。
MySQL之条件查询
IT_Helianthus的博客
05-10 281
一、定义 根据条件过滤原始表的数据，查询到想要的数据。 二、语法 select 查询列表 from 表名 where 筛选条件。 三、分类 1、按条件表达式筛选：简单条件运算符：&gt; &lt; = != &lt;&gt; &gt;= &lt;= &lt;=&gt;(安全等于) 2、按逻辑表达式筛选： 逻辑运算符(用于连接条件表达式)：&amp;&amp; and || or ！ not &a...
【MYSQL】聚合查询、分组查询、联合查询
yj20040627的博客
09-23 7250
聚合查询：聚合函数，分组查询（group by），having 联合查询：笛卡尔积，内连接，外连接，自连接，子查询，合并查询
【数据库】MySql复杂查询的用法：聚合查询、分组查询、子查询、多表查询的用法及示例
zhongqingtian的博客
05-02 1171
【数据库】MySql复杂查询的用法：聚合查询、分组查询、子查询、多表查询的用法及示例
MySQL高级查询详解：聚合函数、分组、联合与连接
&quot;MySQL高级查询课程大纲，包括聚合函数、分组查询、联合查询、连接查询和子查询等内容，旨在帮助学生和专业人士掌握MySQL数据库的实用技能。&quot; 在MySQL数据库中，高级查询是数据处理的核心部分，它允许用户从海量...
MySQL：复杂查询(二)——联合查询02
2401_83595513的博客
08-12 2313
联合查询02
MySQL 条件查询
weixin_45672157的博客
01-27 338
MySQL 条件查询 一、语法： select 查询列表 from 表名 where 筛选条件; 执行顺序①先找表名②判断筛选条件③查询列表； 二、分类： 1、按条件表达式筛选： 条件运算符：&gt;、&lt; 、=、!=、&lt;&gt;、&gt;=、&lt;=、 2、按逻辑表达式筛选： 作用：用于连接条件运算符 逻辑运算符：&amp;&amp;、||、!、and 、or、not； &amp;&amp;和and：如果两个条件都为true 那么结果为true反...
mysql条件查询
qq_56299755的博客
07-24 409
进阶二：条件查询 / 语法： select 查询列表 from 表名 where 筛选条件; 分类: 1.按条件表达式筛选 条件运算符&lt; &gt; = != &lt;&gt; &gt;= &lt;= 2.按逻辑表达式筛选 逻辑运算符：&amp;&amp; || ! and or not &amp;&amp;和and：两个条件均为true，结果为true，反之为false ||或or：只要有一个条件为true，结果就为True，反之为false !或not：如果连接的本身为false，结果为true，反
mysql入门之条件查询
Zero的博客
11-27 1012
不会把_下划线当成通配符了，当然啦，你也可以使用其他字符作为转义字符，不一定要使用$美元符号作为转义字符，你可以使用a啦，b啦作为转义字符。#还有另外一种方式，使用ESCAPE关键字，ESCAPE关键字的作用是转义，mysql数据库中推荐大家使用ESCAPE，如下。使用escape关键字来定义转义符，把$定义为转义字符，如此一来，此时会把$后面的_下划线当成一个普通的_下划线字符，而。使用escape关键字来定义转义符，把a定义为转义字符，如此一来，此时会把a后面的_下划线当成一个普通的_下划线字符，而。
mysql数据库的条件查询（二）
长安白猫
05-22 492
1. 排序 select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…] 语法说明: 先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推 asc从小到大排列，即升序 desc从大到小排序，即降序 默认按照列值从小到大排序（即asc关键字） 例1：查询未删除男生信息，按学号降序: select * from students wher...
MySQL语句的条件查询
weixin_45890113的博客
05-17 3752
SQL语句的条件查询，包括查询时常用到的条件，如in、is null、like、between...and...等
MySQL学习一：条件查询
大面鱼的博客
03-03 1192
*2.数据库说明：**本文使用的数据库名称为 myemployees，其中有 8 张表，分别为 customers 表，departments 表，employees 表，job_grades 表，jons 表，locations 表，orders 表，salespeople 表。
MySQL：查询条件
栗筝i的博客
09-08 3457
查询条件 1、使用 WHERE 子句从数据表中读取数据的通用语法： SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2..... 查询语句中你可以使用一个或者多个表，表之间使用逗号**,** 分割，并使用WHERE语句来设定查询条件。 你可以在 WHERE 子句中指定任何条件。 你可以使用 AND 或者 OR 指定一个或多个条件。 WHERE 子句
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值