【⑥MySQL多表查询】：让你的数据检索更高效

末蓝、 2023-10-12 15:57 42阅读 0赞

#### 前言 ####

> ✨**欢迎来到[小K][K]的[MySQL专栏][MySQL]，本节将为大家带来`MySQL`中多表查询相关知识的讲解**

#### 目录 ####

*   *   *  前言
         *  一、多表关系
         *  二、多表查询
         *   *  1、交叉连接
             *  2、内连接
             *  3、外连接
         *  三、集合运算
         *  四、七种JOINS实现
         *  五、多表查询练习
         *  六、总结

--------------------

#### 一、多表关系 ####

> **✨项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为以下三种**

*  一对多（多对一）
 *  多对多
 *  一对一

✨✨一对多（多对一）

> **案例：部门与员工的关系**
> 
> **关系：一个部门对应多个员工，一个员工对应一个部门**
> 
> **实现：在多的一方建立外键，关联另一方的主键**

![在这里插入图片描述][52430497835d41ed82674fbd219f88ba.png_pic_center]

✨✨多对多

> **案例：学生与课程的关系**
> 
> **关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择**
> 
> **实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键**

![在这里插入图片描述][808c64b5f6e84763adcea890cf7fe6a4.png_pic_center]

✨✨一对一

> **案例：用户与用户详细的关系  
> 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。  
> 实现：在任意一方加入一个外键，关联另一方的主键，并且设置外键为唯一约束**

![在这里插入图片描述][d107f242e84e4f9cabce0675b69cc6c6.png_pic_center]  
![在这里插入图片描述][c65e5ce72f574012a0b9d4d2c78dd2bc.png_pic_center]

#### 二、多表查询 ####

> **✨前面我们使用的查询，只是对单表进行查询，在具体的应用中，经常需要实现在一个查询语句中显示多张数据表的数据，这就是所谓的多表联合查询**

在具体实现连接操作时，首先将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录

✨✨连接查询分为**交叉连接，内连接，外连接查询三种方式。**

##### 1、交叉连接 #####

> **✨交叉连接不带`WHERE`子句，它返回被连接的两个表所有数据行的笛卡尔积**，笛卡尔乘积公式 : A表中数据条数 \* B表中数据条数 = 笛卡尔乘积

✨✨**交叉连接语法：**

SELECT <字段名> FROM <表1> CROSS JOIN <表2> 
    SELECT <字段名> FROM <表1>, <表2>

✨✨交叉连接练习：查询员工及员工所在部门信息

SELECT * FROM emp,dept;　
    SELECT * FROM emp CROSS JOIN dept;

**结果有56条，员工有14人，部门有4个，但是这样就会产出很多无效记录，特别是两张表的数据量都很大，这时候就需要消除笛卡尔积的无效记录，我们需要使用关联字段**

✨✨范例：利用等值条件来处理笛卡尔积

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

**这样我们处理完之后就只有14条记录了，刚好一一对应**

##### 2、内连接 #####

> 内连接又分为等值连接、非等值连接和自连接三种类型。其中，等值连接是指两个表中需要匹配的列具有相同的值；非等值连接是指两个表中需要匹配的列具有不同的值；自连接是指一个表与自身进行联接。**在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按匹配的条件可以分成等值连接和不等值连接。✨**

✨✨有两种语法，显示的和隐式的，返回连接表中符合连接条件和查询条件的数据行（所谓的连接表就是数据库在做查询形成的中间表）

*  ✨**隐式内连接**
    
        SELECT * FROM 表1，表2 WHERE 条件;
 *  ✨**显示内连接（使用关键字`INNER JOIN`)**
    
        SELECT * FROM 表1 [INNER] JOIN 表2 ON 条件;

✨等值连接： ✨

> **在连接条件中使用等于号(=)运算符比较被连接列的列值**

*  查询员工及部门信息
    
        #隐式连接
        SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
        #显示连接
        SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
    
    **需要注意的是：如果指定了别名，原来的SQL表名就不可以使用了**
 *  等值连接可以使用USING来自动关联两表中相同的列
    
        SELECT * FROM emp INNER JOIN dept USING(deptno);
    
    **效果如下：**

![在这里插入图片描述][33182603c4384806aed61800f13cb9e9.png_pic_center]

✨非等值连接： ✨

> **在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>等**

*  查询员工工资级别
    
        #隐式连接
        SELECT * FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
        #显示连接
        SELECT * FROM emp e INNER JOIN salgrade s ON  e.sal BETWEEN s.losal AND s.hisal;
    
    **效果如下：**

![在这里插入图片描述][822b254a9c1a49b3970ef32f2e09d2d7.png_pic_center]

✨自连接： ✨

> **自连接就是指表与其自身进行连接**

![在这里插入图片描述][56edaec081f9451faa4590fab58c28f6.png_pic_center]

✨**我们可以看上面的员工表，如果我们现在要查询每个员工对应的领导姓名，需要通过自连接**

SELECT e.ename,e.mgr,me.ename 领导 FROM emp e,emp me WHERE e.mgr=me.empno;

![在这里插入图片描述][73ce63f40bfa4a0a83179e471a6f9f69.png_pic_center]

##### 3、外连接 #####

> **✨在表关系的笛卡尔积中，不仅保留表关系中所有匹配的数据记录，而且还保留部分不匹配的记录。按照保留不匹配条件数据记录来源可以分为左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)。**

✨**语法：**

SELECT * FROM 表1 LEFT|RIGHT|FULL [OUTER] JOIN 表2 ON 条件;

*  ✨左外链接：
    
    表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联**左边表**中不匹配的数据记录。
    
    查询员工及对应的部门信息（没有部门的员工也**显示**出来，没有员工的部门**不显示**）
    
        SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
    
    **效果如下：**

![在这里插入图片描述][d97a5a2f3d44484380f3d353e3f426e1.png_pic_center]

*  ✨右外连接：
    
    在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联**右边表**中不匹配的数据记录。
    
    查询员工及对应的部门信息（没有部门的员工**不显示**，没有员工的部门**显示**）
    
        SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
    
    **效果如下：**

![在这里插入图片描述][b05fa09b85be4161ad3178d1bf2ff7b0.png_pic_center]

*  ✨全连接：
    
    在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联**左右两边表**中不匹配的数据记录。
    
    查询员工及对应的部门信息（没有部门的员工**显示**，没有员工的部门**显示**）
    
        SELECT *
        FROM emp  LEFT JOIN dept
        ON emp.deptno=dept.deptno
        UNION
        SELECT *
        FROM emp  RIGHT JOIN dept
        ON emp.deptno=dept.deptno
        WHERE emp.deptno IS NULL;
    
    **这个看不懂没关系，因为MySQL不支持全连接，所以我们这里用了集合运算实现的，下面会讲~**
    
    **效果如下：**

![在这里插入图片描述][41952c2bb20a4bcabde05c43038f2c47.png_pic_center]

#### 三、集合运算 ####

> **✨MySQL支持并集运算，并集即两个集合的所有部分**  
> ![在这里插入图片描述][5ba67c3fd4994238a23c3b35b7b33ba4.png_pic_center]

*  ✨`UNION DISTINCT`：
    
     *  **UNION ALL 不会删除重复行**
     *  **相同的行在结果中只出现一次**
        
            SELECT * FROM emp
            UNION
            SELECT * FROM emp WHERE deptno=10;
 *  ✨`UNION ALL`:
    
     *  **UNION 会删除重复行**
     *  **相同的行在结果中可能出现多次**
        
            SELECT * FROM emp
            UNION ALL
            SELECT * FROM emp WHERE deptno=10;

✨✨要求：

*  **输入的查询不能包含`ORDER BY`字句，可以为整个集合运算结果选择性地增加一个`ORDER BY`字句**
 *  **两个查询必须包含相同的列数**
 *  **相应列必须具有兼容的数据类型。兼容的数据类型：优先级较低的数据类型必须能隐式转换为较高级的数据类型。比如输入的查询1的第一列为int类型，输入的查询2的第一列为float类型，则较低的数据类型int类型可以隐式地转换为较高级float类型。如果输入的查询1的第一列为char类型，输入的查询2的第一列为datetime类型，则会提示转换失败：从字符串转换日期或字符串转时间时，转换失败；**
 *  **集合运算结果中列名由输入的查询1决定，如果要为结果分配结果列，应该在输入的查询1中分配相应的别名**
 *  **集合运算时，对行进行时，集合运算认为两个`NULL`相等**

#### 四、七种JOINS实现 ####

![在这里插入图片描述][3f7c19c9948544549ba2b5ef987e6a25.png_pic_center]

图一就是左外连接：

SELECT *
    FROM emp  LEFT JOIN dept
    ON emp.deptno=dept.deptno;

图二为右外连接：

SELECT * 
    FROM emp RIGHT JOIN dept
    ON emp.deptno=dept.deptno;

图三为左外连接去掉交集部分：

SELECT *
    FROM emp  LEFT JOIN dept
    ON emp.deptno=dept.deptno
    WHERE emp.deptno IS NULL;

图四为等值连接，只要交集：

SELECT *
    FROM emp e,dept d
    WHERE e.deptno=d.deptno;

图五为右外连接去掉交集部分：

SELECT *
    FROM emp  RIGHT JOIN dept
    ON emp.deptno=dept.deptno
    WHERE emp.deptno IS NULL;

图六可以可以为1,5或2,3或4,7的并集：

SELECT *
    FROM emp  LEFT JOIN dept
    ON emp.deptno=dept.deptno
    UNION
    SELECT *
    FROM emp  RIGHT JOIN dept
    ON emp.deptno=dept.deptno
    WHERE emp.deptno IS NULL;

图七为3,5的并集：

SELECT *
    FROM emp  LEFT JOIN dept
    ON emp.deptno=dept.deptno
    WHERE emp.deptno IS NULL
    UNION
    SELECT *
    FROM emp  RIGHT JOIN dept
    ON emp.deptno=dept.deptno
    WHERE emp.deptno IS NULL;

**效果如下：**

![在这里插入图片描述][7baf7ecc67ce4365aa581660102a1a75.png_pic_center]  
![在这里插入图片描述][72e32e33dce44ebe95ba8373f5a37a2f.png_pic_center]  
![在这里插入图片描述][c57f4e5397c14a66a26896e4fab7397b.png_pic_center]

#### 五、多表查询练习 ####

✨题目：

> ✨✨查询出雇佣日期在1981年的所有员工的编号、姓名、雇佣日期、工作、领导姓名、雇佣月工资、雇佣年工资(基本工资+奖金)，工资等级、部门编号、部门名称、部门位置，并且求这些员工的月基本工资在1500~3500之间，将最后的结果按照年工资的降序排列，如果年工资相等，则按照工作进行排序。

✨**需求很多，逐步分析**

*  确定所需要的数据表
    
     *  `emp`：编号、姓名、雇佣日期，工作、月工资、年薪
     *  `emp`：领导姓名
     *  `dept`：部门编号、名称、位置
     *  `salgrade`：工资等级
 *  确定一致的关联字段
    
     *  员工和领导：`e.mgr=e1.empno`
     *  员工和部门：`e.deptno=dept.deptno`
     *  员工和工资等级：`e.sal BETWEEN s.losal AND s.hisal`

✨✨**步骤一**：查询出所有在1981年雇佣的雇员编号、姓名、御用日期、工作、月工资、年工资，并且月薪在1500~3500之间。只需要emp单张表即可。

SELECT e.empno,e.ename,e.hiredate,e.job,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪
    FROM emp e
    WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN 1500 AND 3500;

✨✨**步骤二**：加入领导信息，使用自身关联。

SELECT e.empno,e.ename,e.hiredate,e.job,me.ename 领导,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪
    FROM emp e,emp me
    WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN 1500 AND 3500
    AND e.mgr=me.empno;

✨✨**步骤三：加入工资等级和部门信息**

SELECT e.empno,e.ename,e.hiredate,e.job,me.ename 领导,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,s.grade,d.deptno,d.dname,d.loc
    FROM emp e,emp me,salgrade s,dept d
    WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN 1500 AND 3500
    AND e.mgr=me.empno
    AND e.sal BETWEEN s.losal AND s.hisal
    AND e.deptno=d.deptno;

✨✨**步骤四：排序**

✨效果如下： ✨

![在这里插入图片描述][fb28f6e533f247a2be9fdc1e720ead4d.png_pic_center]

#### 六、总结 ####

> ✨**MySQL多表查询的优点有很多，例如可以减少数据冗余，提高查询效率，方便数据分析等等。**

[K]: https://blog.csdn.net/qq_72157449?spm=1000.2115.3001.5343
[MySQL]: https://blog.csdn.net/qq_72157449/category_12344760.html?spm=1001.2014.3001.5482
[52430497835d41ed82674fbd219f88ba.png_pic_center]: https://img-blog.csdnimg.cn/52430497835d41ed82674fbd219f88ba.png#pic_center
[808c64b5f6e84763adcea890cf7fe6a4.png_pic_center]: https://img-blog.csdnimg.cn/808c64b5f6e84763adcea890cf7fe6a4.png#pic_center
[d107f242e84e4f9cabce0675b69cc6c6.png_pic_center]: https://img-blog.csdnimg.cn/d107f242e84e4f9cabce0675b69cc6c6.png#pic_center
[c65e5ce72f574012a0b9d4d2c78dd2bc.png_pic_center]: https://img-blog.csdnimg.cn/c65e5ce72f574012a0b9d4d2c78dd2bc.png#pic_center
[33182603c4384806aed61800f13cb9e9.png_pic_center]: https://img-blog.csdnimg.cn/33182603c4384806aed61800f13cb9e9.png#pic_center
[822b254a9c1a49b3970ef32f2e09d2d7.png_pic_center]: https://img-blog.csdnimg.cn/822b254a9c1a49b3970ef32f2e09d2d7.png#pic_center
[56edaec081f9451faa4590fab58c28f6.png_pic_center]: https://img-blog.csdnimg.cn/56edaec081f9451faa4590fab58c28f6.png#pic_center
[73ce63f40bfa4a0a83179e471a6f9f69.png_pic_center]: https://img-blog.csdnimg.cn/73ce63f40bfa4a0a83179e471a6f9f69.png#pic_center
[d97a5a2f3d44484380f3d353e3f426e1.png_pic_center]: https://img-blog.csdnimg.cn/d97a5a2f3d44484380f3d353e3f426e1.png#pic_center
[b05fa09b85be4161ad3178d1bf2ff7b0.png_pic_center]: https://img-blog.csdnimg.cn/b05fa09b85be4161ad3178d1bf2ff7b0.png#pic_center
[41952c2bb20a4bcabde05c43038f2c47.png_pic_center]: https://img-blog.csdnimg.cn/41952c2bb20a4bcabde05c43038f2c47.png#pic_center
[5ba67c3fd4994238a23c3b35b7b33ba4.png_pic_center]: https://img-blog.csdnimg.cn/5ba67c3fd4994238a23c3b35b7b33ba4.png#pic_center
[3f7c19c9948544549ba2b5ef987e6a25.png_pic_center]: https://img-blog.csdnimg.cn/3f7c19c9948544549ba2b5ef987e6a25.png#pic_center
[7baf7ecc67ce4365aa581660102a1a75.png_pic_center]: https://img-blog.csdnimg.cn/7baf7ecc67ce4365aa581660102a1a75.png#pic_center
[72e32e33dce44ebe95ba8373f5a37a2f.png_pic_center]: https://img-blog.csdnimg.cn/72e32e33dce44ebe95ba8373f5a37a2f.png#pic_center
[c57f4e5397c14a66a26896e4fab7397b.png_pic_center]: https://img-blog.csdnimg.cn/c57f4e5397c14a66a26896e4fab7397b.png#pic_center
[fb28f6e533f247a2be9fdc1e720ead4d.png_pic_center]: https://img-blog.csdnimg.cn/fb28f6e533f247a2be9fdc1e720ead4d.png#pic_center