聚合查询----### 1 聚合函数主要对于行与行之间的操作聚合函数的作用是对一组数据进行计算通常返回一个单一的结果。它们常用于数据汇总、统计和分析。常见的统计总数、计算平局值等操作可以使用聚合函数来实现常见的聚合函数有函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值不是数字没有意义案例#### COUNT *作用返回某列的非 NULL 值的数量或返回满足条件的行数。语法COUNT(column_name) – 统计指定列非 NULL 的值的数量 COUNT() – 统计所有行的数量当具体对某个列查询的时候 count会关注null值 并且不会包含它如果对整个表进行count 则不会关注null####SUM()作用返回某列数值型数据的总和。语法 SUM(column_name) 求语文分数的总和求语文数学英语分数的总和####AVG()作用返回某列数值型数据的平均值。*语法 AVG(column_name)统计总分平均总分####MAX()作用返回某列中的最大值。*语法 MAX(column_name) ####MIN()作用返回某列中的最小值。*语法MIN(column_name) 求语文的最高分 和 英语的最低分* *### GROUP BY子句SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足使用 GROUP BY 进行分组查询时SELECT 指定的字段必须是“分组依据字段”其他字段若想出现在SELECT 中则必须包含在聚合函数中。查询每个岗位的人数按照role进行分组 再根据分组的结果使用count聚合函数统计它的人数即可*统计每个岗位的平均工资* 查询每个角色的人数和最高工资、最低工资和平均工资* *### HAVING*HAVING是一个用于对查询结果进行过滤的 SQL 语句它通常与GROUP BY一起使用用于过滤分组后的结果。与WHERE不同HAVING是在对数据进行分组GROUP BY之后进行过滤的而WHERE是在分组之前对数据进行过滤的。显示平均工资大于10000的角色和它的平均工资*联合查询----**实际开发中往往数据来自不同的表所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积啥是笛卡尔积笛卡尔积CROSS JOIN将两个表中的每一行与另一表的每一行进行组合生成结果集的行数是两个表行数的乘积通常会产生非常大的结果集。示例以上所有数据就是笛卡尔积 但是有很多错误信息 他们并不匹配 所有需要进一步细分此时就可以发现数据一一对应上了 这就是多表查询初始化数据以便下一步的学习 insert into classes(name,desc) values (‘计算机系2019级1班’, ‘学习了计算机原理、C和Java语言、数据结构和算法’), (‘中文系2019级3班’,‘学习了中国传统文学’), (‘自动化2019级5班’,‘学习了机械自动化’); insert into student(sn, name, qq_mail, classes_id) values (‘09982’,‘黑旋风李逵’,‘xuanfengqq.com’,1), (‘00835’,‘菩提老祖’,null,1), (‘00391’,‘白素贞’,null,1), (‘00031’,‘许仙’,‘xuxianqq.com’,1), (‘00054’,‘不想毕业’,null,1), (‘51234’,‘好好说话’,‘sayqq.com’,2), (‘83223’,‘tellme’,null,2), (‘09527’,‘老外学中文’,‘foreignerqq.com’,2); insert into course(name) values (‘Java’),(‘中国传统文化’),(‘计算机原理’),(‘语文’),(‘高阶数学’),(‘英文’); insert into score(score, student_id, course_id) values – 黑旋风李逵 (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), – 菩提老祖 (60, 2, 1),(59.5, 2, 5), – 白素贞 (33, 3, 1),(68, 3, 3),(99, 3, 5), – 许仙 (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), – 不想毕业 (81, 5, 1),(37, 5, 5), – 好好说话 (56, 6, 2),(43, 6, 4),(79, 6, 6), – tellme (80, 7, 2),(92, 7, 6);### 1 内连接语法 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;俩种写法都可以案例1查询“许仙”同学的 成绩 首先看到题目先确定查询的信息来自于哪张表 很明显来自 student表和score表 接着对俩张表进行笛卡尔积 select * from student,score; 这些数据是全部排列组合的结果并不是我们全部想要的 缩小范围 加上条件 student.id score.id select * from student,score where student.id score.student_id; 最后补下其他条件即可 select student.name,score.score from student,score where student.id score.student_id and student.name ‘许仙’; join on 写法 select student.name,score.score from student join score on student.id score.student_id and student.name ‘许仙’; 注意 如果在实际开发中 谨慎使用笛卡尔积 如果表中数据很大容易把mysql弄崩溃 2查询所有同学的总成绩及同学的个人信息 成绩在score表 个人信息在学生表 总成绩需要使用到聚合函数 可以按照学生名字进行分组 select * from student,score where student.id score.student_id group by student.name; 进行求和 并且留下指定列 select student.name,sum(score) as ‘总成绩’ from student,score where student.id score.student_id group by student.name; join on 写法 select student.name,sum(score) as ‘总成绩’ from student join score on student.id score.student_id group by student.name; 3查询所有同学的成绩及同学的个人信息 列出 同学的名称 课程的名字 以及课程的成绩 提取关键词成绩在score表 同学在student表 个人信息在course表 select * from student,course,score; 数据相当大 对它进行细节划分 select * from student,course,score where student.id score.student_id and score.course_id course.id; 对列进行精简即可 select student.name as ‘学生名称’ , course.name as ‘科目名称’ , score.score from student,course,score where student.id score.student_id and score.course_id course.id; join on 写法 select student.name as ‘学生名称’ , course.name as ‘科目名称’ , score.score from student join course on student.id course.id join score on score.course_id course.id; * *### 2 外连接外连接分为左外连接和右外连接。如果联合查询左侧的表完全显示我们就说是左外连接右侧的表完全显示我们就说是右外连接。*语法– 左外连接表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件; – 右外连接表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件; 内连接 左外连接 右外连接 俩张表存在一一对应的情况 所有内连接和外连接找到的结果都是一致的 当修改其中某个值让它们不保存一一对应的情况 内连接 左外连接 右外连接 当对应不存在的列 会用null填充* *### 3 自连接自连接是指在同一张表连接自身进行查询。****案例显示所有“计算机原理”成绩比“Java”成绩高的成绩信息 行与行比较大小 怎么办 只能将它们转化成列与列比较大小自连接将可以完成操作 自己与自己笛卡尔积 进行不断筛选 并且让s1 为计算机原理 课程id为3 s2为java 课程id 为1 最后进行大于 筛选 select * from score as s1 ,score as s2 where s1.student_id s2.student_id and s1.course_id 3 and s2.course_id 1 and s1.score s2.score; * *### 4 子查询子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询*简单来说就是把多个sql语句写成一个sql单行子查询返回一行记录的子查询 查询与“不想毕业” 同学的同班同学 思路在student表中找到不想毕业” 同学的classes_id 再在student表中找到与它id相同的同学 1. select name from student where classes_id 1; 2. select name from student where classes_id 1 and name ! ‘不想毕业’; 一行写完 select name from student where classes_id (select classes_id from student where name ‘不想毕业’) and name ! ‘不想毕业’; 多行子查询返回多行记录的子查询 案例查询“语文”或“英文”课程的成绩信息 思路在course表中找到“语文”或“英文”课程的id 再在score表找到 id为它们的分数 1. select id from course where name ‘语文’ or name ‘英文’; 2. select score from score where course_id in(4,6); 一行写完 select score from score where course_id in(select id from course where name ‘语文’ or name ‘英文’); ### 5 合并查询在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all。使用UNION和UNION ALL时前后查询的结果集中字段需要一致。将最终结果合并在一起整体过程与执行多个sql操作类似查询id小于3或者名字为“英文”的课程 select * from course where id 3 union select * from course where name ‘英文’; 也可以直接使用or; 但是union的作用可以在不同的表的使用 并于合并 而or只能在同一张表中使用 【MySQL篇】聚合查询,联合查询
聚合查询----### 1 聚合函数主要对于行与行之间的操作聚合函数的作用是对一组数据进行计算通常返回一个单一的结果。它们常用于数据汇总、统计和分析。常见的统计总数、计算平局值等操作可以使用聚合函数来实现常见的聚合函数有函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值不是数字没有意义案例#### COUNT *作用返回某列的非 NULL 值的数量或返回满足条件的行数。语法COUNT(column_name) – 统计指定列非 NULL 的值的数量 COUNT() – 统计所有行的数量当具体对某个列查询的时候 count会关注null值 并且不会包含它如果对整个表进行count 则不会关注null####SUM()作用返回某列数值型数据的总和。语法 SUM(column_name) 求语文分数的总和求语文数学英语分数的总和####AVG()作用返回某列数值型数据的平均值。*语法 AVG(column_name)统计总分平均总分####MAX()作用返回某列中的最大值。*语法 MAX(column_name) ####MIN()作用返回某列中的最小值。*语法MIN(column_name) 求语文的最高分 和 英语的最低分* *### GROUP BY子句SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足使用 GROUP BY 进行分组查询时SELECT 指定的字段必须是“分组依据字段”其他字段若想出现在SELECT 中则必须包含在聚合函数中。查询每个岗位的人数按照role进行分组 再根据分组的结果使用count聚合函数统计它的人数即可*统计每个岗位的平均工资* 查询每个角色的人数和最高工资、最低工资和平均工资* *### HAVING*HAVING是一个用于对查询结果进行过滤的 SQL 语句它通常与GROUP BY一起使用用于过滤分组后的结果。与WHERE不同HAVING是在对数据进行分组GROUP BY之后进行过滤的而WHERE是在分组之前对数据进行过滤的。显示平均工资大于10000的角色和它的平均工资*联合查询----**实际开发中往往数据来自不同的表所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积啥是笛卡尔积笛卡尔积CROSS JOIN将两个表中的每一行与另一表的每一行进行组合生成结果集的行数是两个表行数的乘积通常会产生非常大的结果集。示例以上所有数据就是笛卡尔积 但是有很多错误信息 他们并不匹配 所有需要进一步细分此时就可以发现数据一一对应上了 这就是多表查询初始化数据以便下一步的学习 insert into classes(name,desc) values (‘计算机系2019级1班’, ‘学习了计算机原理、C和Java语言、数据结构和算法’), (‘中文系2019级3班’,‘学习了中国传统文学’), (‘自动化2019级5班’,‘学习了机械自动化’); insert into student(sn, name, qq_mail, classes_id) values (‘09982’,‘黑旋风李逵’,‘xuanfengqq.com’,1), (‘00835’,‘菩提老祖’,null,1), (‘00391’,‘白素贞’,null,1), (‘00031’,‘许仙’,‘xuxianqq.com’,1), (‘00054’,‘不想毕业’,null,1), (‘51234’,‘好好说话’,‘sayqq.com’,2), (‘83223’,‘tellme’,null,2), (‘09527’,‘老外学中文’,‘foreignerqq.com’,2); insert into course(name) values (‘Java’),(‘中国传统文化’),(‘计算机原理’),(‘语文’),(‘高阶数学’),(‘英文’); insert into score(score, student_id, course_id) values – 黑旋风李逵 (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), – 菩提老祖 (60, 2, 1),(59.5, 2, 5), – 白素贞 (33, 3, 1),(68, 3, 3),(99, 3, 5), – 许仙 (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), – 不想毕业 (81, 5, 1),(37, 5, 5), – 好好说话 (56, 6, 2),(43, 6, 4),(79, 6, 6), – tellme (80, 7, 2),(92, 7, 6);### 1 内连接语法 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;俩种写法都可以案例1查询“许仙”同学的 成绩 首先看到题目先确定查询的信息来自于哪张表 很明显来自 student表和score表 接着对俩张表进行笛卡尔积 select * from student,score; 这些数据是全部排列组合的结果并不是我们全部想要的 缩小范围 加上条件 student.id score.id select * from student,score where student.id score.student_id; 最后补下其他条件即可 select student.name,score.score from student,score where student.id score.student_id and student.name ‘许仙’; join on 写法 select student.name,score.score from student join score on student.id score.student_id and student.name ‘许仙’; 注意 如果在实际开发中 谨慎使用笛卡尔积 如果表中数据很大容易把mysql弄崩溃 2查询所有同学的总成绩及同学的个人信息 成绩在score表 个人信息在学生表 总成绩需要使用到聚合函数 可以按照学生名字进行分组 select * from student,score where student.id score.student_id group by student.name; 进行求和 并且留下指定列 select student.name,sum(score) as ‘总成绩’ from student,score where student.id score.student_id group by student.name; join on 写法 select student.name,sum(score) as ‘总成绩’ from student join score on student.id score.student_id group by student.name; 3查询所有同学的成绩及同学的个人信息 列出 同学的名称 课程的名字 以及课程的成绩 提取关键词成绩在score表 同学在student表 个人信息在course表 select * from student,course,score; 数据相当大 对它进行细节划分 select * from student,course,score where student.id score.student_id and score.course_id course.id; 对列进行精简即可 select student.name as ‘学生名称’ , course.name as ‘科目名称’ , score.score from student,course,score where student.id score.student_id and score.course_id course.id; join on 写法 select student.name as ‘学生名称’ , course.name as ‘科目名称’ , score.score from student join course on student.id course.id join score on score.course_id course.id; * *### 2 外连接外连接分为左外连接和右外连接。如果联合查询左侧的表完全显示我们就说是左外连接右侧的表完全显示我们就说是右外连接。*语法– 左外连接表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件; – 右外连接表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件; 内连接 左外连接 右外连接 俩张表存在一一对应的情况 所有内连接和外连接找到的结果都是一致的 当修改其中某个值让它们不保存一一对应的情况 内连接 左外连接 右外连接 当对应不存在的列 会用null填充* *### 3 自连接自连接是指在同一张表连接自身进行查询。****案例显示所有“计算机原理”成绩比“Java”成绩高的成绩信息 行与行比较大小 怎么办 只能将它们转化成列与列比较大小自连接将可以完成操作 自己与自己笛卡尔积 进行不断筛选 并且让s1 为计算机原理 课程id为3 s2为java 课程id 为1 最后进行大于 筛选 select * from score as s1 ,score as s2 where s1.student_id s2.student_id and s1.course_id 3 and s2.course_id 1 and s1.score s2.score; * *### 4 子查询子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询*简单来说就是把多个sql语句写成一个sql单行子查询返回一行记录的子查询 查询与“不想毕业” 同学的同班同学 思路在student表中找到不想毕业” 同学的classes_id 再在student表中找到与它id相同的同学 1. select name from student where classes_id 1; 2. select name from student where classes_id 1 and name ! ‘不想毕业’; 一行写完 select name from student where classes_id (select classes_id from student where name ‘不想毕业’) and name ! ‘不想毕业’; 多行子查询返回多行记录的子查询 案例查询“语文”或“英文”课程的成绩信息 思路在course表中找到“语文”或“英文”课程的id 再在score表找到 id为它们的分数 1. select id from course where name ‘语文’ or name ‘英文’; 2. select score from score where course_id in(4,6); 一行写完 select score from score where course_id in(select id from course where name ‘语文’ or name ‘英文’); ### 5 合并查询在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all。使用UNION和UNION ALL时前后查询的结果集中字段需要一致。将最终结果合并在一起整体过程与执行多个sql操作类似查询id小于3或者名字为“英文”的课程 select * from course where id 3 union select * from course where name ‘英文’; 也可以直接使用or; 但是union的作用可以在不同的表的使用 并于合并 而or只能在同一张表中使用