文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

【MySQL联合查询】轻松实现数据关联

2023-09-15 10:38

关注

联合查询又称为多表查询,它的基本执行过程就是笛卡尔积

1.1 认识笛卡尔积

那么什么是笛卡尔积呢?

答:笛卡尔积就是将两张表放在一起进行计算,把第一张表的每一行分别取出来和第二张表的每一行进行连接,得到一个新的行。

举例说明笛卡尔积:

假设我们现在有两张表分别为学生信息表和班级信息表

现在我们把这两张表进行笛卡尔积操作:

笛卡尔积相当于乘法运算,列数是两个表列数之和,行数是两个表行数之积

注:笛卡尔积执行后产生的结果大多是无效的,此时我们就可以采用条件进行筛选

1.2 笛卡尔积进行多表查询

接下来我们就来试一下如何进行笛卡尔积操作:

首先创建两张表,分别为上述的学生信息表和班级信息表,创建完两张表后并给它们添加上述表中的内容

select * from student;+----+------+---------+| id | name | classid |+----+------+---------+|  1 | 张三 |       1 ||  2 | 李四 |       2 ||  3 | 王五 |       1 |+----+------+---------+
select * from class;+---------+-----------+| classid | classname |+---------+-----------+|       1 | 舞蹈班    ||       2 | 跆拳道班  |+---------+-----------+

然后对这两张表进行笛卡尔积操作

select * from student,class;+----+------+---------+---------+-----------+| id | name | classid | classid | classname |+----+------+---------+---------+-----------+|  1 | 张三 |       1 |       1 | 舞蹈班    ||  1 | 张三 |       1 |       2 | 跆拳道班  ||  2 | 李四 |       2 |       1 | 舞蹈班    ||  2 | 李四 |       2 |       2 | 跆拳道班  ||  3 | 王五 |       1 |       1 | 舞蹈班    ||  3 | 王五 |       1 |       2 | 跆拳道班  |+----+------+---------+---------+-----------+

上述的笛卡尔积执行后产生的结果大多数是无效的,此时我们就可以采用条件进行筛选

当 student 表中的 classid 和 class 表中的 classid 相等时,则这条数据是有效的数据

select * from student,class where classid = classid;ERROR 1052 (23000): Column 'classid' in where clause is ambiguous

如果直接用 classid = classid 则会报错,因为它无法分辨哪个classid 和 哪个 classid 进行比较

select * from student,class where student.classid = class.classid;+----+------+---------+---------+-----------+| id | name | classid | classid | classname |+----+------+---------+---------+-----------+|  1 | 张三 |       1 |       1 | 舞蹈班    ||  2 | 李四 |       2 |       2 | 跆拳道班  ||  3 | 王五 |       1 |       1 | 舞蹈班    |+----+------+---------+---------+-----------+

那么此时就可以采用 表名.列名 的方式进行区分

注:多表查询除了可以加上连接条件外,还可以加上其他条件

多表查询出来用 from 多个表加逗号分隔 来连接多个表外,还可以用 join on 来连接,还可以是 inner join on

join on 实现多表查询:

select * from student join class on student.classid = class.classid;+----+------+---------+---------+-----------+| id | name | classid | classid | classname |+----+------+---------+---------+-----------+|  1 | 张三 |       1 |       1 | 舞蹈班    ||  2 | 李四 |       2 |       2 | 跆拳道班  ||  3 | 王五 |       1 |       1 | 舞蹈班    |+----+------+---------+---------+-----------+

join 连接的是两个表,on 后面跟的是连接条件

inner join on 实现多表查询:

select * from student inner join class on student.classid = class.classid;+----+------+---------+---------+-----------+| id | name | classid | classid | classname |+----+------+---------+---------+-----------+|  1 | 张三 |       1 |       1 | 舞蹈班    ||  2 | 李四 |       2 |       2 | 跆拳道班  ||  3 | 王五 |       1 |       1 | 舞蹈班    |+----+------+---------+---------+-----------+

inner join on 其实跟 join on 一样,此处不做过多解释

from 多个表 和 join on 的主要区别:

1.3 内连接和外连接

内连接 和 外连接的主要区别:

1.3.1 两张表一一对应

现在有两张表,分别为 student 学生表 和 score 成绩表:

select * from student;+----+------+| id | name |+----+------+|  1 | 张三 ||  2 | 李四 ||  3 | 王五 |+----+------+
select * from scoretable;+-----------+-------+| studentId | score |+-----------+-------+|         1 |    97 ||         2 |    86 ||         3 |    73 |+-----------+-------+

id 和 studentId 是一一对应的,所有内连接和外连接没什么区别

内连接:

select * from student,scoreTable where student.id = scoretable.studentId;+----+------+-----------+-------+| id | name | studentId | score |+----+------+-----------+-------+|  1 | 张三 |         1 |    97 ||  2 | 李四 |         2 |    86 ||  3 | 王五 |         3 |    73 |+----+------+-----------+-------+

外连接:

select * from student join scoreTable on student.id = scoretable.studentId;+----+------+-----------+-------+| id | name | studentId | score |+----+------+-----------+-------+|  1 | 张三 |         1 |    97 ||  2 | 李四 |         2 |    86 ||  3 | 王五 |         3 |    73 |+----+------+-----------+-------+

1.3.2 两张表不一一对应

现在有两张表,分别为 student 学生表 和 score 成绩表:

select * from student;+----+------+| id | name |+----+------+|  1 | 张三 ||  2 | 李四 ||  3 | 王五 |+----+------+
select * from scoretable;+-----------+-------+| studentId | score |+-----------+-------+|         1 |    97 ||         2 |    86 ||         6 |    73 |+-----------+-------+

现在我们可以看到 student 中的 id 为 3 的在 scoretable 中没有对应的 studentId

内连接:

select * from student,scoreTable where student.id = scoretable.studentId;+----+------+-----------+-------+| id | name | studentId | score |+----+------+-----------+-------+|  1 | 张三 |         1 |    97 ||  2 | 李四 |         2 |    86 |+----+------+-----------+-------+

当进行内连接的时候因为student中的id为3 的和scoretable中的studentId为6的不匹配所以就被筛除了没有查询出来

外连接:

当两张表不是一一对应的时候,外连接又可以分为 左外连接右外连接

select * from student left join scoreTable on student.id = scoretable.studentId;+----+------+-----------+-------+| id | name | studentId | score |+----+------+-----------+-------+|  1 | 张三 |         1 |    97 ||  2 | 李四 |         2 |    86 ||  3 | 王五 |      NULL |  NULL |+----+------+-----------+-------+

左外连接会把左表的结果尽量显示出来,如果右表中没有对应的记录,就使用NULL填充

select * from student right join scoreTable on student.id = scoretable.studentId;+------+------+-----------+-------+| id   | name | studentId | score |+------+------+-----------+-------+|    1 | 张三 |         1 |    97 ||    2 | 李四 |         2 |    86 || NULL | NULL |         6 |    73 |+------+------+-----------+-------+

右外连接会把右表的结果尽量显示出来,如果左表中没有对应的记录,就使用NULL填充

1.4 自连接

自连接:自己和自己进行笛卡尔积

自连接使用场景:当行与行进行比较时,就可以使用自连接,将行转成列进行比较

现在有两张表,分别为 scoretable 和 course

scoretable 表:

select * from scoretable;+-------+------------+-----------+| score | student_id | course_id |+-------+------------+-----------+|    70 |          1 |         1 ||    96 |          1 |         2 ||    97 |          1 |         3 ||    80 |          2 |         1 ||    92 |          2 |         2 ||    86 |          2 |         3 ||    91 |          3 |         1 ||    76 |          3 |         2 ||    77 |          3 |         3 |+-------+------------+-----------+

course 表:

select * from course;+----+------+| id | name |+----+------+|  1 | 语文 ||  2 | 数学 ||  3 | 英语 |+----+------+

现在我们要查询哪些同学的语文成绩比英语成绩低

首先自连接,将行转换成列:

select * from scoretable,scoretable;ERROR 1066 (42000): Not unique table/alias: 'scoretable'

自己跟自己连接名字不能重复

那表名不能重复,那还如何自连接呢?

答:可以起别名,起别名不光可以对列,还可以对表

select * from scoretable as s1,scoretable as s2;

自连接排列组合的时候会产生大量无效的数据,所有就需要指定连接条件

指定连接条件,将有效数据筛选出来:

select * from scoretable as s1,scoretable as s2 where s1.student_id = s2.student_id;

自连接的时候只有当 student_id 相等时才表示有效数据

添加条件,将左边表的语文成绩和右边表的英语成绩查询出来:

有效成绩查询出来后,就需要加上条件查询出左边的语文成绩和右边的英语成绩

select * from scoretable as s1,scoretable as s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 3;+-------+------------+-----------+-------+------------+-----------+| score | student_id | course_id | score | student_id | course_id |+-------+------------+-----------+-------+------------+-----------+|    70 |          1 |         1 |    97 |          1 |         3 ||    80 |          2 |         1 |    86 |          2 |         3 ||    91 |          3 |         1 |    77 |          3 |         3 |+-------+------------+-----------+-------+------------+-----------+

这样就将左侧的语文成绩查询出来了,右侧的英语成绩查询出来了

添加条件,将语文成绩比英语成绩低的同学查询出来:

接下来就要查询哪些同学的语文成绩比英语成绩低

select * from scoretable as s1,scoretable as s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score;+-------+------------+-----------+-------+------------+-----------+| score | student_id | course_id | score | student_id | course_id |+-------+------------+-----------+-------+------------+-----------+|    70 |          1 |         1 |    97 |          1 |         3 ||    80 |          2 |         1 |    86 |          2 |         3 |+-------+------------+-----------+-------+------------+-----------+2 rows in set (0.00 sec)

这样就把语文成绩比英语成绩低的学生信息查询出来了

1.5 子查询

子查询:把多个 SQL 组合成一个

在实际开发中,子查询得慎用。因为子查询可能会构造出非常复杂,非常不好理解的 SQL。

写代码一般要么追求可读性和可维护性,要么追求程序的运行速度

1.5.1 单行子查询

单行子查询:返回一行记录的子查询

student 表:

select * from student;+----+----------+------+| id | class_id | name |+----+----------+------+|  1 |        1 | 张三 ||  2 |        1 | 李四 ||  3 |        2 | 王五 ||  4 |        3 | 赵六 ||  5 |        2 | 王七 |+----+----------+------+

现在查询“张三”的同班同学,根据 class_id 进行查询

分开查询:

//查询出张三的class_idselect class_id from student where name = '张三';+----------+| class_id |+----------+|        1 |+----------+
//查询出来的张三的class_id为 1,再查询除了张三以外的class_id 为1的同学select * from student where class_id = 1 and name != '张三';+----+----------+------+| id | class_id | name |+----+----------+------+|  2 |        1 | 李四 |+----+----------+------+

单行子查询:

select * from student where class_id = ( select class_id from student where name = '张三') and name != '张三';+----+----------+------+| id | class_id | name |+----+----------+------+|  2 |        1 | 李四 |+----+----------+------+

1.5.2 多行子查询

多行子查询:返回多行记录的子查询

scoretable 表:

select * from scoretable;+-------+------------+-----------+| score | student_id | course_id |+-------+------------+-----------+|    70 |          1 |         1 ||    96 |          1 |         2 ||    97 |          1 |         3 ||    80 |          2 |         1 ||    92 |          2 |         2 ||    86 |          2 |         3 ||    91 |          3 |         1 ||    76 |          3 |         2 ||    77 |          3 |         3 |+-------+------------+-----------+

course 表:

select * from course;+----+------+| id | name |+----+------+|  1 | 语文 ||  2 | 数学 ||  3 | 英语 |+----+------+

查询每位同学的“语文”“英语”课程的成绩信息

普通查询:

//首先查询出语文和英语成绩对应的idselect id from course where name = '语文' or name = '英语';+----+| id |+----+|  1 ||  3 |+----+
//再根据查询出来的语文英语对应的id,在 scoretable表中查询select * from scoretable where course_id = 1 or course_id = 3;+-------+------------+-----------+| score | student_id | course_id |+-------+------------+-----------+|    70 |          1 |         1 ||    97 |          1 |         3 ||    80 |          2 |         1 ||    86 |          2 |         3 ||    91 |          3 |         1 ||    77 |          3 |         3 |+-------+------------+-----------+

多行子查询:

select * from scoretable where course_id in(select id from course where name = '语文' or name = '英语');+-------+------------+-----------+| score | student_id | course_id |+-------+------------+-----------+|    70 |          1 |         1 ||    97 |          1 |         3 ||    80 |          2 |         1 ||    86 |          2 |         3 ||    91 |          3 |         1 ||    77 |          3 |         3 |+-------+------------+-----------+

1.5.3 合并查询

合并查询:就是将两个查询结果集,合并成一个

在实际应用中,为了合并多个查询的执行结果,可以使用集合操作符 union,union all。使用UNION 和UNION ALL时,前后查询的结果集中,字段需要一致

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

course 表:

select * from course;+----+------+| id | name |+----+------+|  1 | 语文 ||  2 | 数学 ||  3 | 英语 ||  6 | 化学 ||  7 | 物理 |+----+------+

现在查询 id 小于等于 2 或者 name 为 "英文" 的课程信息

select * from course where id <= 2 union select * from course where name = '英语';+----+------+| id | name |+----+------+|  1 | 语文 ||  2 | 数学 ||  3 | 英语 |+----+------+

看到这里大家可能有个疑问,明明可以用 or 也能实现,为什么还要用 union?

答:用 or 查询只能时来自于同一个表,如果用 union 查询可以时来自于不同的表,子要查询的结果列匹配即可,匹配就是列的类型一样、列的一样、列的名字一样

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

select * from course where id < 3 union all select * from course where name = '数学';+----+------+| id | name |+----+------+|  1 | 语文 ||  2 | 数学 ||  2 | 数学 |+----+------+

来源地址:https://blog.csdn.net/m0_66488562/article/details/129435287

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯