首页 > 数据库 >【重学 MySQL】二十四、笛卡尔积的错误和正确的多表查询

【重学 MySQL】二十四、笛卡尔积的错误和正确的多表查询

时间:2024-09-11 22:49:51浏览次数:10  
标签:JOIN 笛卡尔 查询 course student MySQL 多表 id

【重学 MySQL】二十四、笛卡尔积的错误和正确的多表查询

在这里插入图片描述

在数据库查询中,特别是涉及到多表查询时,理解笛卡尔积(Cartesian Product)及其避免方法是非常重要的。笛卡尔积是指在没有明确指定连接条件的情况下,将两个或多个表中的每一行与其他表中的每一行进行组合。这通常会导致结果集急剧增加,且包含大量无用的数据。

笛卡尔积的理解和错误

笛卡尔积的理解

笛卡尔积(Cartesian Product)是数学中的一个重要概念,尤其在集合论和数据库管理中有着广泛的应用。简单来说,笛卡尔积是两个或多个集合中所有元素的所有可能组合。

定义

假设有两个集合A和B,那么A和B的笛卡尔积记作A×B,是一个新的集合,其中包含所有可能的有序对(a, b),其中a是A中的元素,b是B中的元素。

  • 如果A有m个元素,B有n个元素,那么A×B将有m×n个元素。
  • 笛卡尔积中的元素是有序的,即(a, b)和(b, a)是不同的,除非A和B是相同的集合且元素可以互换(这通常不是笛卡尔积讨论的重点)。

例子

假设有两个集合:

  • A = {1, 2}
  • B = {x, y}

那么A和B的笛卡尔积A×B为:

  • A×B = {(1, x), (1, y), (2, x), (2, y)}

在数据库中的应用

在数据库查询中,特别是SQL查询中,笛卡尔积通常是不希望出现的结果,因为它会导致查询结果集急剧膨胀,并且包含大量无用的数据。例如,如果有两个表:一个学生表和一个课程表,没有指定连接条件时直接查询这两个表会产生它们的笛卡尔积,即每个学生与每门课程都会组合成一行数据。
1
为了避免这种情况,SQL提供了多种连接(JOIN)操作,如INNER JOIN、LEFT JOIN、RIGHT JOIN等,允许你指定表之间的关联条件,从而只获取有意义的数据组合。

总结

笛卡尔积是集合论中的一个基础概念,表示两个或多个集合中所有元素的所有可能组合。在数据库查询中,如果不加注意,可能会产生笛卡尔积,导致查询结果集过大且包含大量无用的数据。因此,在编写SQL查询时,应该明确指定连接条件,以避免不必要的笛卡尔积。

笛卡尔积的错误

假设我们有两个表:students(学生表)和courses(课程表)。

  • students 表有字段:student_idstudent_name
  • courses 表有字段:course_idcourse_name

如果我们执行一个 SQL 查询,但没有指定连接条件,如:

SELECT * FROM students, courses;

这将产生一个笛卡尔积,即 students 表中的每一行都会与 courses 表中的每一行进行组合。如果 students 表有 100 行,courses 表有 20 行,那么结果集将包含 2000 行(100 * 20)。这通常不是我们想要的结果,因为它包含了大量无意义的行组合。

正确的多表查询

为了避免笛卡尔积,我们应该使用明确的连接条件来指定表之间的关系。这通常通过 INNER JOINLEFT JOINRIGHT JOINFULL JOIN(MySQL 中没有 FULL JOIN,但可以通过其他方式模拟)来实现。

使用 INNER JOIN

假设 studentscourses 之间通过一个 enrollment 表(选课表)关联,该表有字段 student_idcourse_id

SELECT s.student_name, c.course_name
FROM students s
INNER JOIN enrollment e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

这个查询将返回所有选课的学生及其所选课程的名称,没有产生笛卡尔积。

使用 WHERE 子句(隐式内连接)

虽然使用 JOIN 语句是更清晰和推荐的方式,但你也可以通过 WHERE 子句来指定连接条件,这实际上执行了一个隐式的内连接。

SELECT s.student_name, c.course_name
FROM students s, courses c, enrollment e
WHERE s.student_id = e.student_id AND e.course_id = c.course_id;

这个查询与上面的 INNER JOIN 示例产生相同的结果,但使用了不同的语法。

总结

  • 笛卡尔积是在没有指定连接条件时,将两个或多个表的每一行进行组合的结果,通常会导致大量无用的数据。
  • 为了避免笛卡尔积,应该使用明确的连接条件,如 INNER JOINLEFT JOIN 等,来指定表之间的关系。
  • 虽然可以使用 WHERE 子句来指定连接条件(隐式内连接),但使用 JOIN 语句通常更清晰、更易于维护。
  • 建议:从sql优化的角度,建议多表查询时,每个字段前都指明其所在的表。如果给表起了别名,一旦在SELECT或WHERE中使用表名的话,则必须使用表的别名,而不能再使用表的原名。
  • 如果有n个表实现多表的查询,则需要至少n-1个连接条件

标签:JOIN,笛卡尔,查询,course,student,MySQL,多表,id
From: https://blog.csdn.net/weixin_43344151/article/details/142151668

相关文章

  • MySQL原理之UUID主键分析,插入或更新语法分析
    目录1MySQL不能用UUID做主键1.1前言1.2mysql和程序实例1.2.1准备工作1.2.2开始测试1.2.3程序写入结果1.2.4效率测试结果1.3使用uuid和自增id的索引结构对比1.3.1自增id1.3.2uuid1.4自增id缺点1.5雪花算法2插入或更新2.1onduplicatekey2.1.1定义2.1.2values函数2......
  • DBeaver 连接 mysql 报错:Public Key Retrieval is not allowed
    前言DBeaver连接mysql报错:PublicKeyRetrievalisnotallowed遇到"PublicKeyRetrievalisnotallowed"错误时,通常意味着你正在使用的身份验证方法需要加密连接,但是没有正确地配置客户端或服务器来支持这种加密。解决第一种可以在连接字符串中添加allowPublicKeyRe......
  • mysql 连接失败:message from server: "Host '192.168.xx.xxx' is not allowed to conn
    前言mysql连接失败:messagefromserver:"Host'192.168.xx.xxx'isnotallowedtoconnecttothisMySQLserver"解决错误信息表明你尝试从IP地址192.168.xx.xxx连接到MySQL服务器,但是该IP地址没有被授权连接权限。为了解决这个问题,你需要确保你的MySQL用户权限......
  • Linux 安装MySQL
    前言:根据对应的系统位数,选择需要下载的文件。查看系统位数getconfLONG_BIT一、下载MySQL1.手动去官网下载官网地址:MySQL::DownloadMySQLCommunityServer(ArchivedVersions)2.Linux命令下载通过Linux命令下载:wgethttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-对......
  • BUG: pymysql executemany不支持insert on duplicate key update
    pymysql的executemany()方法支持传入单个SQL和一个sequenceofrecords(sequenceormapping)来同时写入多条数据。例如:sql="insertintot(c1,c2)values(%s,%s)"args=[(1,2),(3,4)]cursor.executemany(sql,args)#Ifargsisalistortuple,%scanbeusedas......
  • MySQL常见面试题
    使⽤索引⼀定可以提升效率吗?索引就是排好序的,帮助我们进⾏快速查找的数据结构.简单来讲,索引就是⼀种将数据库中的记录按照特殊形式存储的数据结构。通过索引,能够显著地提⾼数据查询的效率,从⽽提升服务器的性能。索引的优势与劣势优点提⾼数据检索的效率,降低数据库的IO成本......
  • MySQL——主从复制、读写分离
    目录前言一、MySQL主从复制的概述1、MySQL主从复制的概念2、Mysql主从复制功能和使用场景2.1、Mysql主从复制功能2.2、Mysql主从复制使用场景3、MySQL支持的复制类型3.1、基于语句的复制3.2、基于行的复制3.3、混合复制4、主从复制的工作过程5、MySQL三种同步方式......
  • 笛卡尔树
    解决的问题有\(n\)个值,每个值有两个信息\((a_i,b_i)\)。你需要在这\(n\)个值间连边并形成一棵二叉树,使得:每个点的\(a_i\)满足二叉搜索树的性质。即对于所有\(v\in\text{subtree}_{lson}\)都有\(a_v\lea_u\),对于所有\(v\in\text{subtree}_{rson}\)都有\(a_v......
  • 初级练习[4]:多表查询——表联结
    目录多表查询:表联结示例 查询有两门以上的课程不及格的同学的学号及其平均成绩 查询所有学生的学号、姓名、选课数、总成绩 查询平均成绩大于85的所有学生的学号、姓名和平均成绩 查询学生的选课情况:学号,姓名,课程号,课程名称 查询出每门课程的及格人数和不及格人数......
  • 初级练习[5]:多表查询——多表连接
    目录多表查询:表联结(续) 课程编号为"01"且课程分数小于60,按分数降序排列的学生信息查询所有课程成绩在70分以上的学生的姓名、课程名称和分数,按分数升序排列 查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩 查询课程编号为“01”的课程比“02”的课程成绩......