MySQL-多表查询

目录

多表关系

概述

多对一 

多对多

一对一

多表查询概述

定义

笛卡尔积

多表查询的分类

连接查询

内连接

隐式自连接

显式自连接 

外连接

左外连接（查询左表的全部数据，两表交集的数据）

右外连接（查询右表的全部数据，两表的交集数据）

自连接

联合查询

子查询

概念

分类

标量子查询

列子查询

ALL

IN

行子查询

表子查询

总结

---

多表关系

概述

在项目开发中，在进行数据库表设计时，会根据业务模块，分析并设计表结构，由于业务之间的相互关联，所以表与表之间往往涉及多种关系，一般包括：

1. 多对一（一对多）
2. 多对多
3. 一对一

• 多对一 

案例：

部门与员工的关系

关系：

一个部门可以对用多个员工，但是一个员工只能对应一个部门

实现：

在多的一方设置外键，指向一的一方

在员工表中emp设置外键 deft_id，与部门表deft中的主键 id 关联：

• 多对多

 案例：

学生与课程的关系

关系：

学生可以对应多门课程，同样一门课程也可以对应多位学生

实现：

建立的三张中间表，中间表至少包含两个外键，用于关联两张表中的主键

中间表：

这里介绍一个技巧，在DataGrip软件中，能够将表结构的关系可视化： 

右击想要查看的表结构，点击 图->显示图：

这样能够清楚的观察到，两张表通过外键department_id相关联。

• 一对一

案例：

用户与用户详情

关系：

一位用户与其用户详情肯定是一一对应的，一般用于单表拆分，将基础字段放在一张表中，详细字段放在另一张表中，能够提高效率。

实现：

在任意一方设置外键，与另一方的主键相关联，并且将外键设置为UNIQUE

这是一张用户表，其中不仅有用户的基本信息，还包括了一些详细信息，在一定程度上会影响信息查询的效率，所以我们进行单表拆分：

将其拆分成了用户的基本信息，与用户的详细信息，如此一来大大提高了，信息查询的效率，我可以在用户教育信息表中加入一个字段，id 将其与基本信息表中的id关联起来。

多表查询概述

定义

从多张表中查询数据

笛卡尔积

是指数学中，集合A与集合B的所有组合（在进行多表查询时需要消除多余的的卡尔积）

例如我要进行多表查询时：

select * from employees,departments;
--emp中有5条数据，dept中有5条数据

那么此时将会查询出5*5=25条数据，这就是笛卡尔积，所以在实际的多表查询时，要消除多余的笛卡尔积，比如：

select * from employees,departments where employees.department_id=departments.department_id;

我在多表查询语句后面加上约束函数，以外键关联的两个值作桥梁，来消除多表查询中多余的数据。

多表查询的分类

连接查询

• 内连接

查询两张表的交集部分

隐式自连接

SELECT   字段列表  FROM  表1 ，表2 ，WHERE  条件;

select * from employees,departments where employees.department_id=departments.department_id;

上面演示就是一个隐式自连接查询

这里要注意的是，如果我在查询时候对表起了别名，那么在后面使用约束函数时，就必须使用表的别名

select * from employees,departments where employees.department_id=departments.department_id;

上面我将将两张表分别起别名叫做，e、d，此时如果进行查询就会报错：

显式自连接 

SELECT  字段列表  FROM  表名 表别名  INNER  JOIN  表名 表别名  ON  条件;

select e.employee_id,d.department_name from employees e inner join 
departments d on d.department_id = e.department_id;

• 外连接

左外连接（查询左表的全部数据，两表交集的数据）

 SELECT  字段列表  FROM  表名 表别名  LEFT  OUTER  JOIN  表名 表别名  ON  条件;

select e.*,d.department_name from employees e left outer join 
departments d on e.department_id = d.department_id;

这里有一个小技巧，当一个表中有null数据时，应该使用左表查询，因为左表查询能包含左表的所有数据。

右外连接（查询右表的全部数据，两表的交集数据）

只需要将 LEFT OUTER 改为 RIGHT  OUTER

• 自连接

当前表与自身的连接查询，子连接必须使用表别名。

举例，一张员工表中，员工之间会存在领导关系，假设有一个字段是员工直属领导的编号，那么就可以通过以下自连接的方式：

select a.name b.name from emp a emp b where a.manager.id b.id;

如此便能够，查询到员工姓名。及其直属领导姓名 。

注意的是：自连接查询可以是，外连接，也可以是内连接。

联合查询

语法：

SELECT  *FROM  EMP  WHERE  AGE>=50

UNION  [ ALL ]

SELECT  *FROM  EMP  WHERE  SALARY<5000;

如果将ALL加上的话，就会将上下两次的查询结果直接拼成一张表；如果不加ALL，就会自动将查询到的相同结果合并：

如下图，‘鹿鼎客’的相关信息出现了两次，这就是UNION ALL的结果

而UNION的结果会是：

 在使用UNION时，要注意，如果前后查询得到的表的字段数不相同，就会报错无法进行合并：

 

子查询

概念

SQL语句中嵌套查询语句，叫做嵌套查询，又称子查询。

SELECT *FROM 表名 WHERE COLUMNS = (SELECT *FROM 表名 );

分类

• 标量子查询

子查询返回的是一个单一的值（日期、字符、数字）。

举例：我要查询所有销售部门员工的信息

常规拆解：

• 先在部门表中找到销售部门对应ID

select id from dept where dept_name = '销售部';
--假如返回的id=4

• 使用该ID作为查询员工信息表的约束条件

select *from emp where deft_id=4;

如果使用标量子查询：

select *from emp where deft_id = (select id from dept where dept_name = '销售部');

列子查询

子查询结果返回的是一列数据（可以是多行）

常用操作符：

ALL

select *from emp where salary > all(select salary from emp where deft_id = 4);

满足返回的所有条件 

IN

select *from emp where dept_id IN (select id from deft where deft_name='销售部' deft_name='财务部');

在指定范围内即可

行子查询

子查询返回的结果是一行数据（可以是多列）。

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

上面语句是，查询与张无忌工资、直属领导相同的员工信息。

表子查询

子查询结果返回的是多行多列。

举例讲解：

我要查询与‘宋远桥’、‘鹿杖客’的工资、职位相同的员工信息

常规拆解：

1. 先查询到‘宋远桥’、‘鹿杖客’的工资、职位
2. 再查询与‘宋远桥’、‘鹿杖客’的工资、职位相同的员工信息

那么使用表子查询连接起来就是：

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

总结