第四章 多表查询

一、多表关系

1. 一对多

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

2. 多对多

实现：建立第三张表作为中间表，中间表至少包含两个外键，分别关联两方主键。

3.一对一

关系：一对一关系用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提高操作效率。

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

二、多表查询概述

1.笛卡尔积

笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。
而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。

2. 多表查询的分类

• 连接查询

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

• 子查询

三、内连接

1. 隐式内连接

select 字段列表 from 表1，表2 where 条件……；

2.显式内连接

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

注意 起了别名后，就不能再使用原来的名字了。

四、外连接

1. 左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件……；

2. 右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件……；

将表的顺序调换就可以用右外连接执行左外连接了。

五、自连接

select 字段列表 from 表A 别名A join 表B 别名B on 条件……；

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

六、联合查询

把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A……
union [all]
select 字段列表 from 表B……；

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all 会将全部的数据直接合并在一起， union 会对合并的数据进行去重。

七、子查询

1.简介

• 概念

SQL 语句中嵌套 select 语句，称为嵌套查询，或子查询。

• 分类

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

• 根据子查询位置，分为：where后、from后、select后。

2. 标量子查询

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

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

3. 列子查询

返回的结果是一列（可以是多行）

操作符	描述
in	在指定的集合范围内，多选一
not in	不在指定的集合范围内
any	子查询返回列表中，有任意一个满足要求即可
some	与 any 等同，使用 some 的地方都可以使用 any
all	子查询返回列表的所有值都必须满足

4. 行子查询

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

常用操作符：=、<>、in、not in。

5. 表子查询

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

常用操作符：in

案例：查询低于本部门平均工资的员工信息

select * from emp e2 where e2.salary < (select avg(salary) from emp e1 where e1.dept_id = e2.dept_id);

==> 注意语句的执行顺序，子查询不是最先执行的语句。