MySQL表中的联合查询

上一篇有关聚合查询的博客: MySQL表中的聚合查询_徐憨憨！的博客-CSDN博客主要是针对单个表进行查询操作,但是实际的开发环境中,数据往往来自己于不同的数据表,此时就需要使用联合查询进行操作!

联合查询

多表查询的基本执行过程:进行笛卡尔积然后设置条件删除无效数据进行筛选

1.示例一

假设创建两个表,学生表(包含id,name,classId),班级表(包含classId,name),在不使用外键的情况下查询学生所属班级的情况:

第一步:笛卡尔积

笛卡尔积之后产生的新表的行数等于两个表的行数之积(因为笛卡尔积的结果就是排列组合的结果),列数等于两个表的列数之和

第二步:指定连接条件删除无效数据

进行笛卡尔积之后,发现很多数据都是错误的数据(即无效数据),比如这里的张三同时属于classId为1和classId为2两个班级,这里设置的条件也是基于两个表中的classId应该相同

注意:这里设置的条件是两个表的classId要相等,所以在SQL语法中,要明确指出每个classId属于哪个表的,使用时需要使用表名.classId,否则会报错(提示字段名有歧义)!

第三步:对查询结果进行精简(删除不必要的列)

通过查询所需要的列来对查询结果进行精简

2.示例二

假设创建四个表,班级表(包含id,name,desc),课程表(id,name),分数表(score,student_id,course_id),学生表(包含id,sn,name,qq_mail,classes_id),其中学生和班级是一对多的关系,学生和课程是多对多的关系,在没有设置外键约束的情况下进行查询时需要使用多表查询.

此时我们需要查询'许仙'同学的成绩(涉及到学生表和成绩表,所以需要对这两个表进行多表查询)

第一步:笛卡尔积

第二步:指定连接条件删除无效数据

第三步:加入条件进行筛选

第四步:对查询结果进行精简(删除不必要的列)

同样使用join on也可以实现多表查询(查询时也可以设置别名)

3.内连接和外连接

内连接和外连接大多数情况下是没什么区别的:上述查询时使用from就是内连接,而join on既可以实现外连接也可以实现内连接;
当要连接的两个表里面的数据是一一对应的时候没有区别,
当要连接的两个表里面的数据不是一一对应的时候有区别.

两个表之间的数据一一对应时:

两个表之间的数据不是一一对应时:

但是如果使用了外连接则会不同(外连接分为左外连接和右外连接);

左外连接:会把左表的结果尽量列出来,哪怕在右表中没有对应的记录,就使用NULL填充

右外连接:会把右表的结果尽量列出来,哪怕在左表中没有对应的记录,就使用NULL填充

4.自连接

自连接也是多表查询的一种,只是自连接是针对于同一个表进行笛卡尔积运算,自连接的效果就是把行转成列(因为SQL中无法针对行与行之间使用条件,但是有的需求需要行与行进行比较,此时就可以使用自连接把行转换成列进行比较)

现在有一个需求:查询所有"计算机原理"成绩比"Java"成绩高的成绩信息,这个需求需要将成绩表进行自连接

第一步:笛卡尔积

注意:这里对同一个表进行笛卡尔积运算时,必须对表名起别名(所以起别名不仅针对表达式,也可以针对表)

第二步:指定连接条件删除无效数据

第三步:找出满足左侧为该同学计算机原理成绩,右侧为java成绩的记录

第四步:筛选出计算机原理成绩比Java成绩高的记录

5.子查询

子查询的本质就是把多个SQL语句组合成一个复杂的语句,但是复杂的代码可读性会较差,需要谨慎使用

子查询分为单行子查询(返回一行记录的子查询)和多行子查询(返回多行记录的子查询);

单行子查询:

查询与"不想毕业"同学的同班同学:先去查询"不想毕业"这个同学的班级id,再按照班级id来查哪些同学和他一个班;

分步查询:

子查询:

注意:后面的子查询必须只返回一条记录,此时写=才是可以的(把一个查询作为另一个查询的一部分条件)

多行子查询(使用关键字in):

查询"语文"或"英文"课程的成绩信息:先根据名字查询出课程id,再根据课程id查询出课程分数

分步查询:

子查询:

注意:查询结果是在内存中,如果查询结果太大了,内存放不下,in就用不了了,就可以使用exists代替(exists关键字可读性较差,执行效率也大大低于in写法,使用这个一般都是用来解决特殊场景)

6.合并查询

合并查询本质上就是把两个查询的结果集(要求这两个结果集的列相同才能合并)

查询id小于3,或者名字为"英文"的课程

union和or的区别:

or只能查询来自于同一个表的数据,union查询结果可以来自于不同的表,只要查询的结果的列匹配即可!