博客

一、介绍

顾名思义就是多张表同时进行查询（之前了解过的DQL语句是基于单表查询）。

二、多表关系

• 
  1.一对一
  

关系

：如一个人只对应一张身份证，一张身份证只对应一个人。


实现

：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一（UNION）。

• 
  2. 一对多
  

关系

：如一个员工对应一个部门，一个部门对应多个员工。


实现

：在多的一方建立外键，指向一的一方的主键。

• 
  3.多对多
  

关系

：如一个学生可以有多个老师，一个老师也可以有多个学生。


实现

：建立第三张中间表，其至少包含两个外键分别对应两个多方的主键。

三、多表查询简述

• 
  1.描述
  

  • 之前单表查询时，执行的SQL语句为：
    
    select * from [表名];
    
  • 现在多表查询，只需使用
    
    逗号将多张表隔开
    
    即可。如：
    
    select * from student,teacher;
    
    
    但是当我们执行上条语句时，查询结果包含大量的结果集，即两张表的所有组合情况，这种现象被称为
    
    笛卡尔积
    
    ，即数学当中
    
    两个集合的所有组合情况
    
    。
  • 而我们平常查询只想得到有效的数据，那
    
    如何去掉无效的的笛卡尔积
    
    ，就需要给多表查询语句加上
    
    连接查询
    
    的条件即可。

• 
  2.多表查询分类
  

  • 
    2.1 连接查询
    

    • 
      2.1.1 内连接
      

    相当于查询两表交集部分的数据

    • 
      2.1.2 外连接
      

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

    • 
      2.1.3 自连接
      

    
    自连接查询
    
    ：当前表与自身的连接查询(自连接必须使用表别名)，就是自己连接自己，也就是把一张表连接查询多次。
    
    
    联合查询
    
    ：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

  • 
    2.2 子查询
    

  SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

  
  标量子查询
  
  ：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式
  
  
  列子查询
  
  ：子查询返回的结果是一列（可以是多行）。
  
  
  行子查询
  
  ：子查询返回的结果是一行（可以是多列）。
  
  
  表子查询
  
  ：子查询返回的结果是多行多列。

四、多表查询操作

1.连接查询–内连接

相当于查询两表交集部分的数据

（1）隐式内连接

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

例

查询每一个员工的姓名 , 及关联的部门的名称

表结构: emp , dept

连接条件: emp.dept_id = dept.id

（2）显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 … ;

例

查询每一个员工的姓名 , 及关联的部门的名称

表结构: emp , dept

连接条件: emp.dept_id = dept.id

查询结果与上图无二异。


注意:

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

2.连接查询–外连接

左外连接

：查询左表所有数据，以及两张表交集部分数据


右外连接

：查询右表所有数据，以及两张表交集部分数据

（1）左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 … ;

例

查询emp表的所有数据, 和对应的部门信息

表结构: emp, dept

连接条件: emp.dept_id = dept.id

（2）右外连接

例

查询dept表的所有数据, 和对应的员工信息

表结构: emp, dept

连接条件: emp.dept_id = dept.id


注意:

左外连接和右外连接是可以

相互替换

的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。

3.连接查询–自连接

（1）自连接查询

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 … ;

例1

查询员工 及其 所属领导的名字

表结构: emp

例2

查询所有员工及其领导的名字 , 如果员工没有领导, 也需要查询出来

表结构: emp


注意:


（1）对于自连接查询，可以是内连接查询，也可以是外连接查询。

（2）在自连接查询中，

必须要为表起别名

，（要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。）

（2）联合查询

SELECT 字段列表 FROM 表A …

UNION [ ALL ]

SELECT 字段列表 FROM 表B …;

例

将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来。

但是通过上图查询结果可以看出并未去重（第1行和第8行），所以**union all查询出来的结果，仅仅进行简单的合并，并未去重。**则可通过如下语句：


注意:


（1）对于联合查询的多张表的

列数

必须保持一致，

字段类型

也需要

保持一致

；

（2）

union all

会将

全部的数据

直接合并在一起，

union

会对合并之后的

数据去重

。

4.子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

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

根据子查询位置分有

WHERE之后、FROM之后、SELECT之后

；

根据子查询结果不同，分有如下几类：

（1）标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式。

常用的操作符：= <> > >= < <=

例1

查询 “销售部” 的所有员工信息.

我们可以分解为两步：

先查询“销售部”的部门ID，再根据该部门ID查询所有员工信息。

例2

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

我们可以分解为两步：

先查询“东方白”的入职时间，再查询指定日期之后入职的所有员工信息

（2）列子查询

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

例1

查询 “销售部” 和 “市场部” 的所有员工信息

我们可以分为两步：

先查询“销售部”和“市场部”的部门ID，再根据部门ID查询所有员工信息

例2

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

我们可以分解为两步：

先查询所有财务部人员工资（这里也可以分成先查询“财务部”的部门ID，再根据部门ID查询员工工资），再查询比“财务部”所有人员工资都高的员工信息

结果为空，没人比财务部工资高哈哈

例3

查询比“研发部”其中任意一人工资高的员工信息

我们可以分解为两步：

先查询“研发部”所有人员工资，再查询比“研发部”其中任意一人工资都高的员工信息

（3）行子查询

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

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

例

查询与 “张无忌” 的薪资及直属领导相同的员工信息

我们可以分解为两步：

先查询“张无忌”的薪资和直属领导，再查询与该员工的薪资和直属领导相同的员工信息

（4）表子查询

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

常用的操作符：IN

例1

查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息

我们可以分解为两步：

先查询“鹿杖客”，“宋远桥”的职位和薪资。再查询与该两位职位和薪资相同的员工信息

例2

查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

我们可以分解为两步：

先查询入职日期是 “2006-01-01” 之后的员工信息，再查询该部分员工对应的部门信息。