超详细多表查询详解-多表关系-多表查询-子查询

多表关系

• 一对多关系：这是最常见的关系类型，它表示在两个表之间，一个表中的记录可以与另一个表中的多个记录相关联。例如，一个班级（父表）可以有多个学生（子表），但每个学生只能属于一个班级。

• 多对多关系：表示一个表中的多个记录可以与另一个表中的多个记录相关联。例如，一个学生可以选修多门课程，而一门课程也可以被多个学生选修。

• 一对一关系：关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另 一张表中，以提升操作效率.实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

多表查询就是指从多张表中查询数据。

当直接查询两个表时

select * from emp , dept ;

会出现笛卡尔积问题

多表查询中的笛卡尔积问题通常指的是在没有指定有效的连接条件时，两个或多个表之间会产生所有可能的行组合。

 为了避免笛卡尔积的问题，可以采取以下措施：

明确连接条件：在进行多表查询时，应该明确指定表与表之间的连接条件，确保只有相关的数据才会被组合在一起。

SELECT * FROM emp,dept where emp.dept_id =  dept.id;

使用内连接：使用内连接（INNER JOIN）来确保只有满足连接条件的记录才会出现在结果集中。

检查关联条件：确保所有的关联条件都是有效的，避免因为错误的关联条件导致查询结果出现笛卡尔积。

使用外连接：如果需要包含某个表中的所有记录，即使它们在另一个表中没有匹配的记录，可以使用外连接（LEFT OUTER JOIN 或 RIGHT OUTER JOIN）来避免笛卡尔积的问题。\

多表查询分类

连接查询

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

2. 外连接

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

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

5. 自连接：当前表与自身的连接查询，自连接必须使用表别名

内连接

内连接的语法分为两种: 隐式内连接、显式内连接

隐式内连接

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

显式内连接

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

有一个员工表（emp）和一个部门表（dept），并且想要查询每个员工的姓名及其对应的部门名称，可以使用以下查询语句： (显式内连接实现) --- INNER JOIN ... ON ...

SELECT e.name, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

表结构: emp , dept

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

select e.name,d.name from emp e , dept d where e.dept_id = d.id;

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

外连接

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为

左外连接

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

右外连接

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

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

由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。

表结构: emp, dept

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

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

查询dept表的所有数据, 和对应的员工信息(右外连接)

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

左外连接和右外连接可以相互替换，只需要调整先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接

自连接查询

自连接是SQL中一种表与自身进行连接的操作，它常用于解决连续性问题或比较表中的数据.

自己连接自己,把一张表连接查询多次

自连接可以看作是内连接的一种特殊情况，它允许我们使用同一张表的不同别名来执行连接操作。这样做的目的是为了更好地比较表中的记录或者查找具有特定关系的行。自连接在处理例如员工层级、朋友关系等具有层次或网络结构的数据时尤为有用。以下是一些自连接的关键点：

• 别名的使用：在进行自连接时，需要为同一张表设置两个不同的别名，以便在查询中区分它们。这两个别名通常被视为两个独立的表来处理。
• 连接条件：自连接的连接条件通常基于某些字段的相等性或其他逻辑关系。例如，如果我们想要找出年龄相同且籍贯也相同的学生信息，我们可以使用自连接来实现这一查询。
• 结果优化：在使用自连接时，可能会出现重复的记录或者不需要的字段。因此，需要在查询中添加适当的条件来去除这些冗余信息，以得到更精确的结果。
• 性能考虑：由于自连接涉及到同一张表的多次引用，这可能会导致查询性能的下降。因此，在设计查询时，应考虑到性能的影响，并尽可能优化。

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

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

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

表结构: emp

select a.name , b.name from emp a , emp b where a.managerid = b.id;

 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 表结构: emp a , emp b

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;

在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底 是哪一张表的字段。

联合查询

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

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

当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们 也可以通过union/union all来联合查询.

select * from emp where salary < 5000
union all
select * from emp where age > 50;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

union 联合查询，会对查询出来的结果进行去重处理。

子查询

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

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

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

根据子查询结果不同

A. 标量子查询（子查询结果为单个值）

标量子查询是一种特殊的子查询，它返回单一值，通常用在WHERE子句中与比较运算符结合使用。

• 定义和用法：标量子查询的结果是一个单一的值，即一行一列的数据。这种查询可以用作另一个查询的一部分，通常是在WHERE子句中，以筛选满足特定条件的记录。例如，想要找出分数高于平均分的学生学号和成绩，可以使用标量子查询来计算平均分数并将其作为比较的条件。
• 语法结构：基本的语法结构是将子查询放置在括号内，并在外部查询的WHERE子句中使用比较运算符。例如：SELECT 学号， 分数 FROM score WHERE 分数 > (SELECT AVG(分数) FROM score);。这里的子查询 (SELECT AVG(分数) FROM score) 返回一个值，即score表中的平均分数。
• 性能考虑：虽然标量子查询在功能上非常强大，但它们可能会影响查询的性能。因为子查询需要先执行并取得结果，然后外部查询才能使用这个结果。如果主查询返回大量唯一的连接列值，那么子查询可能需要执行多次，这可能会导致性能问题。优化标量子查询的一种方法是重写为JOIN操作，或者在某些情况下使用索引来改善性能。

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

查询 "销售部" 的所有员工信息

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

B. 列子查询(子查询结果为一列)

列子查询是子查询的一种类型，它返回单列多行的结果集，通常与主查询结合使用来筛选数据。

列子查询的特点是它返回一个字段的多个值，这些值通常用于WHERE子句中与IN、ANY、ALL或EXISTS等操作符结合使用，以便根据子查询的结果来过滤主查询的数据。

以下是列子查询的一些典型用法：

• 与IN操作符结合使用：当需要根据一组值来筛选数据时，可以使用IN操作符。例如，如果想要选取所有年龄为18岁的学生的成绩信息，可以使用列子查询来获取这些学生的学号，然后根据这些学号来查询成绩。
• 与ANY和ALL操作符结合使用：ANY和ALL操作符可以用于比较主查询中的值与子查询返回的一组值。ANY用于比较是否满足子查询结果中的任意一个条件，而ALL用于比较是否满足子查询结果中的所有条件。
• 与EXISTS操作符结合使用：EXISTS操作符用于检查子查询是否返回了结果。如果子查询返回至少一行数据，EXISTS操作符返回真，否则返回假。这在检查是否存在相关记录时非常有用。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

查询所有 财务部 人员工资

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

C. 行子查询(子查询结果为一行)

行子查询返回的结果集包含一行多列

• 返回值：行子查询可以返回一个或多个字段的值，这些值可以是单一的记录，也可以是由多条记录组成的集合。
• 使用场景：当需要根据一组相关的数据来筛选主查询结果时，可以使用行子查询。例如，如果想要找出工资最高的员工信息，可能需要根据员工的工资和员工编号来进行查询。
• 比较运算符：在单行子查询中，通常使用单行比较运算符（如=, >, <, >=, <=, <>），而在多行子查询中，则使用多行运算符（如IN, ANY, ALL）。

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

查询与 "小白" 的薪资及直属领导相同的员工信息 ;

select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '小白');

D. 表子查询(子查询结果为多行多列)

表子查询是子查询的一种类型，它返回的结果集包含多行多列，并且需要在查询中为其指定别名。

• 别名的使用：由于表子查询返回的是多行多列的结果集，因此在使用时必须为其指定别名，以便在外部查询中引用这些字段。
• 语法结构：表子查询通常放在圆括号中，并且在外部查询的FROM子句后面，通过别名来引用。例如，如果要找出参加了“01”课程的学生信息及课程分数，可以使用表子查询来实现更复杂的查询条件。
• 性能考虑：表子查询可能会影响查询的性能，特别是当子查询返回大量数据时。因此，在设计查询时，应当考虑效率，并在必要时对查询进行优化。
• 与外部查询的关系：表子查询的结果不被直接显示，而是传递给外部查询，作为外部查询的条件使用。外部查询根据子查询的结果来过滤数据，并最终显示结果。

常用的操作符：IN

查询入职日期是 "2000-6-6" 之后的员工信息 , 及其部门信息

select e.*, d.* from (select * from emp where entrydate > '2000-6-6') e left join dept d on e.dept_id = d.id ;

根据子查询位置

A. WHERE之后

B. FROM之后

C. SELECT之后