【MySQL】第六弹——表的CRUD进阶(四)聚合查询(下)

🌅复习

🌅联合查询

🌊联合查询练习

• 查询所有同学的每门课的成绩及同学的个人信息

1.确定要参与查询的表 – 学生表 成绩表 课程表

2.取笛卡尔积

3.确定表与表之间连接条件

也可以通过表结构来确定连接条件

4.根据具体需求确定过滤条件

此练习中不需要,已经没有多余的无效数据了

5.精简查询字段

• 使用 join on 的方式进行查询

join两边是参与查询的表，on后面跟的是连接条件，既然是三个表的查询，那就在join 一个表就行了

select * from student st join score sc on st.student_id = sc.student_id
join course co on co.course_id = sc.course_id;

这样就可以进行三表查询，满足日常开发没问题，工作中最好不要超过三个表联合查询，不然数据量太大了

🌊外连接

外连接分为左外连接和右外连接,如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接

内连接时有的班级并没有学生记录，所以没有显示自动化班级的名称

🏄‍♂️右外连接

以join右边的表为基准,这个表中的数据会全部显示出来,左边的表没有与之匹配的记录全部用NULL填充

🏄‍♂️左外连接

返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显示为NULL

• 查询哪位同学没有考试成绩

1.在学生表中有记录
2.在分数表中没有该同学的记录

MYSQL中不支持全外连接 FULL JOIN
不同厂商的数据库,在SQL的使用上略有差别
MySQL --> 分页查询 limit s,n;
SQL Server ,Oracle --> select top N from…

其中student 为基准表，数据会全部显示 join后的表为从表 没有与基准表匹配的记录会使用NULL填充

🌊自连接

自连接就是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者说实现行与行之间的比较。在做表连接时为表起不同的别名

• 显示所有计算机原理成绩比Java成绩高的信息

• 需要行与行之间的比较

  1.确定所涉及的表 – 课程表 成绩表

  2.取笛卡尔积

计算机原理与Java成绩进行比较,需要course_id = 1 和course_id =3 的两行之间进行 行比较

3.连接条件:student_id 必须相等

共62行记录

要么s1表中的course_id = 1 并且 s2表的course_id = 3
或者是 s1表中的course_id = 3 并且 s2表的course_id = 1
都可以完成计算机原理和Java成绩的比较

4.最后的条件 查出计算机原理大于Java的成绩记录

🌊子查询

子查询也叫嵌套查询,
子查询是吧一条SQL的查询结果,当做另一条SQL的查询条件(嵌入到其他SQL语句的select语句中)
可以嵌套很多层

🏄‍♂️单行子查询

• 查询与"不想毕业"同学的同班同学

1.参与查询的表 – 只有一个学生表

2.先查出"不想毕业"这位同学的班级编号

3.在学生表中查出"不想毕业"的同班同学

使用子查询的方式,把查询条件中的1 用其他SQL语句代替

对整体进行过滤

子查询只是把单独的SQL语句拼装在一起而已

单行子查询:返回一行记录的子查询 嵌套中返回的是一个对象

🏄‍♂️多行查询

多行查询 返回多行记录的子查询(返回的是一个集合,集合中包含多个对象)

语法:

• 查询语文或英语课程的成绩信息

1.确定涉及哪些表 – 课程表 成绩表

2.在课程表中获取语文英文的课程编号

3.根据获取的课程编号,在成绩表中查询对应课程的分数

4.把以上分步查询的SQL拼装起来–>子查询

不包含语文和英文的所有成绩

使用多列包含

• 查询重复的分数

使用分组查询

1.同一个学生,同一门课,同样的成绩,按这三个列同时去分组
2.分组之后使用having子句,用count(*)判断分组中的记录数

其中记录>1 的都是有重复记录的

🏄‍♂️[NOT] EXISTS 关键字 (了解即可)

语法:

• 内层查询返回非空结果集

• 内层查询返回空结果集

外层也返回空结果集,也可以说外层查询没有执行

• select NULL

🏄‍♂️ 在from子句中使用子查询

子查询语句出现在from 子句中 这里要用到数据查询的技巧,把一个子查询当做一个临时表使用

这个结果集在临时表中就是有学生表和课程表组合而成的

• 查询所有比中文系2019级3班平均分高的成绩信息

确定涉及那些表 – 班级表 成绩表 分数表

1.先算出中文系2019级3班的平均分
①.先从班级表中根据班级名找到班级编号
②.根据班级编号在学生表中找到班里的学生以及学生编号
③.根据学生的编号在分数表中计算平均分

2.再用表中学生的真实成绩和以上平均分比较

🌊合并查询-union | union all

合并多个查询结果到一个结果集中

使用union 把结果集合并在一起

在单表中还是推荐使用 or 去连接不同的查询条件
在多表中,就没办法用 or ,如果最终的结果是从多个表中获取,必须要用union来进行合并

根据一张表结构,创建新表(复制表)like

• 通过union 把两张表中的数据显示在一个结果集中

• union 该操作符用于取得两个结果集的并集 会自动去掉结果集中的重复行

• union all 该操作符用于取得两个结果集的并集 不会去掉结果集中的重复行