MySQL~数据库的聚合查询(聚合函数、group by)与联合查询(内连接、外连接)实现多表查询

– 查询每个角色总工资大于1500的

mysql> select role, sum(salary) as totle from emp group by role having totle > 1500;

±----------±--------+

| role | totle |

±----------±--------+

| 教导员 | 1900.00 |

| 老师 | 2000.00 |

±----------±--------+

2 rows in set (0.00 sec)

联合查询

===================================================================

笛卡尔积

---

• 实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积.

• 关联查询的时候可以对关联表使用别名

内连接

---

• 使用内连接的思路:

1. 观察要查询的数据涉及到几张表

2. 多张表连接时使用的是笛卡尔积运算,所以会产生许多无用的数据,此时我们就应该加上表的连接条件去除掉无用的数据

3. 再用选择条件和字段去控制得到的数据表为我们所需要的

– 查询张飞的成绩,其涉及到学生表和成绩表

mysql> select student.id, student.name, score from student, score where student.id = score.student_id and student.name = ‘张飞’;

±—±----------±------+

| id | name | score |

±—±----------±------+

| 3 | 张飞 | 33.0 |

| 3 | 张飞 | 68.0 |

| 3 | 张飞 | 99.0 |

±—±----------±------+

3 rows in set (0.00 sec)

– 还有这种写法

mysql> select student.id, student.name, score from student join score on student.id = score.student_id and student.name = ‘张飞’;

±—±----------±------+

| id | name | score |

±—±----------±------+

| 3 | 张飞 | 33.0 |

| 3 | 张飞 | 68.0 |

| 3 | 张飞 | 99.0 |

±—±----------±------+

3 rows in set (0.00 sec)

– 结合分组查询

– 查询所有同学的成绩

mysql> select student.id, student.name, score from student, score where student.id = score.student_id;

±—±----------±------+

| id | name | score |

±—±----------±------+

| 1 | 诸葛亮 | 70.5 |

| 1 | 诸葛亮 | 98.5 |

| 1 | 诸葛亮 | 33.0 |

| 1 | 诸葛亮 | 98.0 |

| 2 | 刘备 | 60.0 |

| 2 | 刘备 | 59.5 |

| 3 | 张飞 | 33.0 |

| 3 | 张飞 | 68.0 |

| 3 | 张飞 | 99.0 |

| 4 | 关羽 | 67.0 |

| 4 | 关羽 | 23.0 |

| 4 | 关羽 | 56.0 |

| 4 | 关羽 | 72.0 |

| 5 | 孙权 | 81.0 |

| 5 | 孙权 | 37.0 |

| 6 | 吕布 | 56.0 |

| 6 | 吕布 | 43.0 |

| 6 | 吕布 | 79.0 |

| 7 | 曹操 | 80.0 |

| 7 | 曹操 | 92.0 |

±—±----------±------+

20 rows in set (0.00 sec)

– 查询所有同学的总成绩

mysql> select student.id, student.name, sum(score) from student, score where student.id = score.student_id group by student.id;

±—±----------±-----------+

| id | name | sum(score) |

±—±----------±-----------+

| 1 | 诸葛亮 | 300.0 |

| 2 | 刘备 | 119.5 |

| 3 | 张飞 | 200.0 |

| 4 | 关羽 | 218.0 |

| 5 | 孙权 | 118.0 |

| 6 | 吕布 | 178.0 |

| 7 | 曹操 | 172.0 |

±—±----------±-----------+

7 rows in set (0.00 sec)

外连接

---

左外连接

• 联合查询,左侧的表完全显示我们就说是左外连接

– 查成绩,没有成绩也要体现

mysql> select student.id, student.name, score from student left join score on student.id = score.student_id;

右外连接

• 联合查询,右侧的表完全显示我们就说是右外连接

mysql> select student.id, student.name, score from student right join score on student.id = score.student_id;

– 三张表的关联查询

mysql> select student.id, student.name, score from

-> student

-> left join score on student.id = score.student_id

-> left join course on score.course_id = course.id

-> where score < 60 or score <=> null

-> order by score desc;

±—±----------±------+

| id | name | score |

±—±----------±------+

| 2 | 刘备 | 59.5 |

| 4 | 关羽 | 56.0 |

| 6 | 吕布 | 56.0 |

| 6 | 吕布 | 43.0 |

| 5 | 孙权 | 37.0 |

| 1 | 诸葛亮 | 33.0 |

| 3 | 张飞 | 33.0 |

| 4 | 关羽 | 23.0 |

| 8 | 赵云 | NULL |

±—±----------±------+

9 rows in set (0.00 sec)

自连接

---

• 自连接是指在同一张表连接自身进行查询

– 查 计算机原理 比 java高的成绩信息

mysql> select s1.course_id, s1.score, s2.course_id, s2.score

-> from

-> score s1, score s2

-> where

-> s1.student_id = s2.student_id

-> and s1.course_id = (select id from course where name = ‘计算机原理’)

-> and s2.course_id = (select id from course where name = ‘java’)

-> and s1.score > s2.score;

±----------±------±----------±------+

| course_id | score | course_id | score |

±----------±------±----------±------+

| 3 | 98.5 | 1 | 70.5 |

| 3 | 68.0 | 1 | 33.0 |

±----------±------±----------±------+

2 rows in set (0.00 sec)

子查询

==================================================================

• 在一个查询语句里套用查询语句称为子查询

单行子查询

---

• 子查询返回一条结果

– 查询与刘备同班同学的总成绩

mysql> select student.id, student.name, sum(score)

-> from

-> student, score

-> where

-> student.id = score.student_id

-> and student.classes_id = (select student.classes_id from student where name = ‘刘备’)

-> group by student.id;

±—±----------±-----------+

| id | name | sum(score) |

±—±----------±-----------+

| 1 | 诸葛亮 | 300.0 |

| 2 | 刘备 | 119.5 |

| 3 | 张飞 | 200.0 |

| 4 | 关羽 | 218.0 |

| 5 | 孙权 | 118.0 |

±—±----------±-----------+

5 rows in set (0.00 sec)

多行子查询

---

• 子查询返回多行结果

IN 处理多行

– 查询语文和高阶数学的成绩

mysql> select score.course_id, score

-> from

-> score

-> where

-> score.course_id

-> in

-> (select id from course where name = ‘语文’ or name = ‘高阶数学’);

±----------±------+

| course_id | score |

±----------±------+

| 5 | 33.0 |

| 5 | 59.5 |

| 5 | 99.0 |

| 5 | 56.0 |

| 5 | 37.0 |

| 4 | 43.0 |

±----------±------+

6 rows in set (0.00 sec)

• 还有not in

EXISTS 处理多行

• 括号不能省

mysql> select

-> score.course_id, score

-> from

-> score

-> where exists

-> (select score.course_id from course where (name = ‘语文’ or name = ‘高阶数 学’)

-> and course.id = score.course_id);

±----------±------+

| course_id | score |

±----------±------+

| 5 | 33.0 |

| 5 | 59.5 |

| 5 | 99.0 |

| 5 | 56.0 |

| 5 | 37.0 |

| 4 | 43.0 |

±----------±------+

6 rows in set (0.00 sec)

IN 与 EXISTS 的区别

1. in 的子查询是 先执行子查询,把结果保存到内存中,再进行主查询,最后结合子查询的结果进行筛选

2. exists 的子查询是 先执行主查询,再触发子查询,最后子查询的结果会对主表查询结果的每一条进行筛选.

3. 如果子查询的结果小,就用in

4. 如果子查询大,而主查询小,就用 exists

合并查询

===================================================================

• 合并多个select的查询结果,但必须保证合并表的字段需一致

UNION

---

• 将俩个结果集进行并集, 会去掉集中的重复行

mysql> select * from student where id > 3 or id < 7;

±—±------±----------±-----------------±-----------+

| id | sn | name | qq_mail | classes_id |

±—±------±----------±-----------------±-----------+

| 1 | 09982 | 诸葛亮 | xuanfeng@qq.com | 1 |

| 2 | 00835 | 刘备 | NULL | 1 |

| 3 | 00391 | 张飞 | NULL | 1 |

| 4 | 00031 | 关羽 | xuxian@qq.com | 1 |

| 5 | 00054 | 孙权 | NULL | 1 |

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）

最后希望可以帮助到大家！

千千万万要记得：多刷题！！多刷题！！

之前算法是我的硬伤，后面硬啃了好长一段时间才补回来，算法才是程序员的灵魂！！！！

篇幅有限，以下只能截图分享部分的资源！！

（1）多线程（这里以多线程为代表，其实整理了一本JAVA核心架构笔记集）

（2）刷的算法题（还有左神的算法笔记）

（3）面经+真题解析+对应的相关笔记（很全面）

（4）视频学习（部分）

ps：当你觉得学不进或者累了的时候，视频是个不错的选择

在这里，最后只一句话：祝大家offer拿到手软！！

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

容对你有帮助，可以添加V获取：vip1024b （备注Java）**
[外链图片转存中…(img-DkxIvwQ5-1712801765236)]

最后希望可以帮助到大家！

千千万万要记得：多刷题！！多刷题！！

之前算法是我的硬伤，后面硬啃了好长一段时间才补回来，算法才是程序员的灵魂！！！！

篇幅有限，以下只能截图分享部分的资源！！

（1）多线程（这里以多线程为代表，其实整理了一本JAVA核心架构笔记集）

[外链图片转存中…(img-05qmuJYO-1712801765236)]

（2）刷的算法题（还有左神的算法笔记）

[外链图片转存中…(img-mTc8pXQV-1712801765236)]

（3）面经+真题解析+对应的相关笔记（很全面）

[外链图片转存中…(img-Z8cWvqRy-1712801765237)]

（4）视频学习（部分）

ps：当你觉得学不进或者累了的时候，视频是个不错的选择

在这里，最后只一句话：祝大家offer拿到手软！！

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
[外链图片转存中…(img-HpXf3Xqo-1712801765237)]