当前位置：首页 > news >正文

MySQL复合查询

news 来源：原创 2025/8/11 21:37:26

多表查询

自连接

子查询

单行子查询

多行子查询

in关键字

all关键字

any关键字

多列子查询

from中使用子查询

合并查询

union 操作符

union all 操作符

内外连接

内连接

外连接

左外连接

右外连接

前几期我们已经学习了MySQL的基本查询，但是基本查询一般情况即要查询的字段都在一个表中，所以基本查询就是单表查询，但是有时候我们要查找的字段可能会存在于多张表中，基于多张表的查询我们称之为复合查询，基于此，本期我们将进行复合查询的学习。

多表查询

还是我们之前的三张表。dept(部门表)，emp(雇员表)，salgrade(薪资等级表)。

情景：显示雇员名、雇员工资以及所在部门的名字。

不难发现，雇员名和雇员工资都在emp表里，但是部门名称并不在emp表里，而在dept表里，所以此时，我们就不能单单从emp表里获取我们想要的字段，此时就要在emp表和dept表中联合进行查询，通过emp表中的deptno(外键)和dept表中的deptno(主键)建立两个表的关联关系，最终查找需要的字段。

上述查询的方式我们就称之为复合查询，也即多表查询。

但是多表查询仅仅是从两个表中查询吗，如果我们不给定筛选条件，图示如下。

不给定筛选条件，我们发现emp表中所有数据会和dept表中的所有数据进行任意组合，这种组合方式我们就称之为笛卡尔积，但是笛卡尔积之后的数据大部分是无效的，我们可以看到，不相同的部门编号被整合成了一条记录，那么这条记录是无意义的。

基于此，我们在进行多表查询时，必须在where子句中给出指定的筛选条件，此时多表查询之后的数据才是有效的。

上述情景，多表查询结果如下。

显示部门号为10的部门名，员工名和工资。

显示各个员工的姓名，工资，及工资级别。

在salgrade表中有losal和hisal字段，所以可以用雇员的sal与这两个字段进行匹配筛选，最终筛选出来的记录就是有效的记录，然后再次进行多表查询。

自连接

何为自连接？

简单来说就是自己的表和自己的表做笛卡尔积，然后对笛卡尔积之后的记录进行查询。

有这样的使用场景吗？

有的，如emp表。情景：我们要查询某个雇员的领导的姓名。

在雇员表中，每个雇员的记录中都会含有其领导的编号，而在雇员表中又有雇员编号，和雇员姓名，所以就可以通过自连接，使用领导编号和雇员编号相同作为筛选条件，筛选出复合条件的记录，最终查询出领导姓名。

子查询

何为子查询？

子查询，就是一次select的结果可以作为另一个select的筛选条件。

单行子查询

何为单行子查询？

单行子查询，就是作为筛选条件的select子句，查询出来的记录只有一条。

显示和 SMITH 同一部门的员工。

多行子查询

多行子查询，就是作为筛选条件的select子句，查询出来的记录有多条。

in关键字

查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10号自己的。

all关键字

查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

any关键字

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）。

我们发现，any和all的区别就是any是只要条件满足子查询中的所有记录中的一个记录就可以，且最终的查询结果是包含子查询的记录的。

多列子查询

何为多列子查询，就是子查询的查询结果中包含多个字段。

查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人。

from中使用子查询

在使用select子句时，我们可以将select出来的结果也看作一张表。

显示每个高于自己部门平均工资的员工的姓名，部门，工资，部门的平均工资。

查询每个部门工资最高的人的姓名，工资，部门，最高工资。

显示每个部门的信息（部门名，编号，地址）和人员数量。

合并查询

为了合并多个 select 的执行结果，可以使用 union 和 union all 集合操作符。

union 操作符

查询工资大于2500或职位是MANAGER员工。

一般情况下，工资大于2500的条件查询出来的员工记录和职位是MANAGER的查询条还能查询出来的员工记录可能为一条记录，但是 union 操作符会将重复的查询记录去重。

union all 操作符

查询工资大于2500或职位是MANAGER员工。

我们发现，union all 操作符并不会对查询的记录进行去重。

内外连接

内连接

之前我们的所有多表查询都是内连接。

但是大家要有一个认识，任意的两张表都可以形成内连接。

如果两张表如果没有意义相同的字段进行内连接。没有查询条件进行查询，查询的结果就是两张表记录笛卡尔积，此时的笛卡尔积之后的记录都是毫无意义的，就算给定了任何查询条件，查询出来的结果也一定是空。如果两张表有意义相同的字段。

如果两张表有意义相同的字段进行内连接。没有查询条件进行查询，查询的结果仍然是两张表记录的笛卡尔积，但是这个笛卡尔积中有有效的记录，如果给定了合适查询条件，那么查询出来的结果一般不会是空的。

综上，两个表如果没有相同意义的字段，那么就不要内连接，因为内连接是没有意义的。

所以基于此，内连接一定要是两个表有意义相同的字段，这样笛卡尔积之后，可以根据筛选条件，筛选出有效的记录。

内连接语法：select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

显示SMITH的名字和部门名称。

下图分别为以往的多表查询和内连接查询。

外连接

外连接分为左外连接和右外连接，仍然是当两个表有相同意义的字段时，再进行外连接。

左外连接

左外连接其实就是两个表进行内连接时，保留左边的表。

左外连接语法：select 字段名 from 表名1 left join 表名2 on 连接条件

创建两张表stu(学生表),exam(成绩表)，并插入数据。

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来。

右外连接

右外连接其实是两个就表进行内连接时，保留右边的表。

右外连接语法：select 字段名 from 表名1 right join 表名2 on 连接条件

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来。

但是要注意的是，内连接可以没有 on 条件，但是外连接必须有 on 条件。

以上便是本期的复合查询的全部内容。

本期内容到此结束^_^

多表查询

自连接

子查询

单行子查询

多行子查询

in关键字

all关键字

any关键字

多列子查询

from中使用子查询

合并查询

union 操作符

union all 操作符

内外连接

内连接

外连接

左外连接

右外连接

相关文章：