MySQL基础篇之七：多表查询

MySQL多表查询

一、概述

笛卡尔积

MySQL多表查询指的是从多张表中查询数据

回忆单表查询：select * from emp;

• 最简单的多表查询（笛卡尔积）

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A和B的所有组合情况。（在多表查询的时候，需要消除无效的笛卡尔积）。

我们现在同时查询emp员工表和dept部门表的所有数据

select * from emp,dept;

我们可以看到，现在一共有102条数据，数据的组成是：17条员工表数据*6条部门表数据。

此时我们消除笛卡尔积的条件：添加一个条件，即emp表的dept_id等于dept表的id即可

select * from emp,dept where emp.dept_id = dept.id;

多表查询分类

• 1、连接查询

  • 内连接：相当于查询A、B交集部分数据

  • 外连接

    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据

  • 自连接：当前表与自身的连接查询，自连接必须使用表的别名（AS）

• 2、子查询

二、内连接（Inner Join）

内连接又称等值连接，它是指在多张表中找到共同的数据行，并将这些数据行合并成一张新表格。内连接仅仅返回那些两个表之间匹配的数据行，也就是两个表之间的交集。

• 隐式内连接

select 字段列表 from 表1,表2 where 条件...;

• 显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

举例：查询每一个员工的姓名，以及管理的部门的名称

select emp.name,dept.name
from emp
inner join dept
on emp.dept_id = dept.id;

三、外连接（Outer Join）

• 概念：外连接是一种在两个或多个表之间建立关联的查询方式。外连接根据指定条件从左表和右表中返回所有的行，如果右表中没有匹配的行，则以NULL值填充结果集。常用的外连接包括左外连接、右外连接和全外连接。

• 和内连接的区别：与内连接不同的是，内连接仅返回两个表之间匹配的行，而外连接则返回两个表之间匹配的行以及未匹配的行。因此，在某些情况下，外连接可以帮助我们更清楚地了解两个表之间的关系，同时也可以用于查找确实数据或者进行数据统计分析等操作。

• 左外连接

  • 语法：

    select 字段列表 from 表1 left join 表2 on 条件 ...;
    

    相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据

• 右外连接

  • 语法：

    select 字段列表 from 表1 right join 表2 on 条件...;
    

    相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据

四、自连接

• 概念：自连接查询是指在一个表中进行连接操作，将该表中的一些字段与该表中的其他记录进行匹配。这种查询通常用于需要比较同一表中不同记录之间的数据时，或者需要找出具有类似属性或相互关联的数据行时。

• 自连接查询，可以是内连接查询，也可以是外连接查询。

• 语法：

  • select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
    

  • 举例1：查询员工及其所属领导的名字（内连接）

  • select A.name,B.name 
    from emp as A
    join emp as B
    on A.managerid = b.id;
    

• 举例2：查询所有员工及其领导的名字，如果员工没有领导，也需要查询出来（外连接）

• select a.name,b.name
  from emp as a
  left join emp as b
  on a.managerid = b.id;
  

五、联合查询

MySQL联合查询（Union）是将多个SELECT语句的结果集合并起来返回给客户端的一种查询方式。通常情况下，联合查询用于将两个或多个表中的数据进行组合显示，以达到某种特定的查询目的。

• 语法

MySQL联合查询的基本语法如下所示：

SELECT column1, column2, ... FROM table1
UNION [ALL]
SELECT column1, column2, ... FROM table2
[UNION [ALL]
SELECT column1, column2, ... FROM table3 ...];

• column1, column2, ...：要查询的列名称，可以使用通配符*代替所有列。
• table1, table2, ...：要查询的表名称。
• UNION：将两个或多个SELECT语句的结果集合并起来，并去除重复行。
• ALL：可选参数，如果加上该参数，则不去除重复行。

请注意，每个SELECT语句必须具有相同的列数和相似的数据类型。此外，MySQL联合查询还需要在每个SELECT语句中使用相同的列名或别名。

• 示例

下面是一个简单的MySQL联合查询示例，演示如何将两个表中的数据组合显示：

-- 查询学生表中的姓名和年龄信息
SELECT name, age FROM student
UNION
-- 查询教师表中的姓名和年龄信息
SELECT name, age FROM teacher;

在上面的示例中，我们将学生表和教师表中的姓名和年龄信息组合在一起，并返回给客户端。如果两个表中有相同的记录，则只返回一条记录。

除了基本语法之外，MySQL联合查询还支持其他高级用法，如使用GROUP BY子句对结果集进行分组、使用HAVING子句对分组后的结果进行筛选等。这些高级用法可以进一步提升MySQL联合查询的灵活性和功能性。

• 总结

MySQL联合查询是将多个SELECT语句的结果集合并起来返回给客户端的一种查询方式。它可以将两个或多个表中的数据进行组合显示，以达到某种特定的查询目的。在编写MySQL联合查询时需要注意每个SELECT语句必须具有相同的列数和相似的数据类型，而且每个SELECT语句中也需要使用相同的列名或别名。

六、子查询

• 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

• 根据子查询结果不同，分为：

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

• 根据子查询位置，分为：WHERE之后，FROM之后，SELECT之后

标量子查询

• 概念：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

• 例一：查询"销售部"的所有员工信息

  • 1、查询“销售部”部门ID

  • select id from dept where name = '销售部';
    

  • 2、根据销售部门ID，查询员工信息

  • select *
    from emp
    where dept_id = (select id from dept where name = '销售部');
    

• 例二：查询在”方东白“入职之后的员工信息

  • 1、查询“方东白“的入职日期

  • select entrydate from emp where name = '方东白';
    

  • 2、查询指定入职日期之后入职的员工信息

  • select *
    from emp
    where entrydate > (select entrydate from emp where name = '方东白');
    

列子查询

• 概念：列子查询返回的结果是一列，这种子查询称为列子查询。
• 常用的操作符：IN、NOT IN、ANY、SOME、ALL

• 例一：查询“销售部”和“市场部”所有员工信息

  • 1、查询“销售部“和”市场部“的部门id

  • select id from dept where name='销售部' or name = '市场部';
    

  • 2、根据部门id，查询员工信息

  • select *
    from emp
    where dept_id in (select id from dept where name='销售部' or name = '市场部');
    

• 例二：查询比“财务部”所有人工资都高的员工信息

  • 1、查询财务部所有人员工资

  • select id from dept where name='财务部';
    

  • 2、查询所有财务部的人员工资

  • select salary
    from emp
    where dept_id = (select id from dept where name='财务部');
    

  • 3、比财务部所有人员工资都高的员工信息

  • select *
    from emp
    where salary > all(select salary from emp where dept_id = (select id from dept where name='财务部'));
    

行子查询

• 概念：行子查询返回的结果是一行。

• 常用的操作符：=、<>、IN、NOT IN

• 例一：查询与“张无忌“的薪资以及直属领导相同的员工信息

  • 1、查询张无忌的薪资以及直属领导

  • select salary,managerid from emp where name = '张无忌';
    

  • 2、查询相同的员工信息

  • select * from emp
    where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');
    

表子查询

• 概念：表子查询但会的结果是多行多列。

• 常用操作符：IN

• 例一：查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息

  • 1、查询“鹿杖客”，“宋远桥”的职位和薪资

  • select job,salary from emp where name = '鹿杖客' or name = '宋远桥';
    

  • 2、查询与这两个人职位和薪资相同的员工信息

  • select * from emp
    where (job,salary)
    in
    (select job,salary from emp where name = '鹿杖客' or name = '宋远桥');