SQL 多表查询

多表关系

概述：项目开发中，在进行数据库表结构设计时，会根据业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一)      多对多     一对一

一对多(多对一)

案例：部门和员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

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

多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

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

一对一

案例：用户与用户详情的关系

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

实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询

概述：指从多张表中查询数据

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

消除笛卡儿积样例：

SELECT * FROM emp，dept WHERE emp.dept_id = dept.dept_id

用关联字段去消除无效的笛卡儿积

多表查询分类

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

外连接：

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

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

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

子查询 

内连接查询语法

隐式内连接

SELECT  字段列表  FROM 表1，表二  WHERE 条件.....

显式内连接 

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

2个语法区别是 表的连接形式 、连接条件的表示形式

显式内连接运行速度比隐式内连接快

显式内连接的 [INNER] 可以省略

样例：查询 emp 表的每一位员工的姓名，及关联的 dept 部门的名称

-- 表结构：emp，dept

-- 连接条件：emp.dept_id  =  dept.id

隐式：

SELECT  e.name , d.name   FROM  emp  e，dept  d  WHERE

e.dept_id  =  d.id

显式：

SELECT  e.name , d.name   FROM  emp  e  JOIN  dept  d  ON

e.dept_id  =  d.id

外连接查询语法

左外连接：查询表1的所以数据 包含表1 和 表2交集部分的数据

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

右外连接：查询表2的所以数据 包含表1 和 表2交集部分的数据

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

OUTER 可以省略

样例：

-- 表结构：emp，dept

-- 连接条件：emp.dept_id  =  dept.id

左外连接：查询 emp 表的所有数据，和对应部门的信息

SELECT e.* , d.name   FROM  emp  e  LEFT  JOIN  dept  d  ON  e.dept_id  = d.id

右外连接：查询 dept 表的部门数据，和对应的员工所有数据

SELECT  d.name,e.*   FROM  emp  e  RIGHT  JOIN  dept  d  ON  e.dept_id  = d.id

自连接

自连接查询语法：自连接查询，可以是内连接，也可以是外连接查询

SELECT  字段列表  FROM  表A  别名 (A)  JOIN  表A  别名  (B)  ON  条件

样例：

-- 表结构：emp

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

SELECT  A.name   '员工',  B.name  ‘领导’  FROM emp   A  ，  emp   B  WHERE  A.领导id = B.id.

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

SELECT  A.name ‘员工’, B.name  ‘领导’  FROM  emp  A  LEFT  JOIN  emp  B  ON  A.领导id = B.id.

联合查询 -- UNION,UNION  ALL

概述：对于UNION查询， 就是把多次查询的结果合并起来，形成一个新的查询结果集

语法：

        SELECT  字段列表  FROM  表A ....

        UNION  [ALL]

        SELECT  字段列表  FROM  表B ....

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

UNION 和 UNION  ALL  区别在没加 ALL 会对查询拼接去重  加了 ALL 就是把查询拼接

样例：将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来

SELECT * FROM emp WHERE  薪资 <  5000

UNION   ALL  \   UNION

 SELECT * FROM emp WHERE  年龄 <  5000

子查询

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

语法：

SELECT * FROM  表1  WHERE  x = (SELECT  ...  FROM  表2 )

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

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

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

        列子查询（子查询结果为一列）

        行子查询（子查询结果为一行）

        表子查询（子查询结果为多行多列）

列子查询

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

常用的操作符：

 

样例：查询A部门 和 B部门的所有员工消息

-- 表结构

        emp 员工表

        dept 部门表

SELECT *  FROM  emp  WHERE   emp.dept_id   IN   (SELECT id  FROM dept WHERE  name = 'A'  or  name = 'B)

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

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

样例：查询与 A 的薪资及直属领导相同的员工信息

SELECT  *  FROM  emp  WHERE (薪资，领导)  =  (SELECT  薪资，领导  FROM  emp  WHERE   name  =  'A')

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用的操作符：IN

样例1：查询 A 和 B 的职位相同和薪资相同的员工信息

SELECT *  FROM  emp  WHERE (职位，薪资)  IN  (SELECT 职位，薪资  FROM  emp  WHERE  name = ‘A’  OR  name =  ‘B’)

样例2：查询入职日期是“2022-12-05”之后的员工信息，对应的部门信息

SELECT e.*  ,  d.name   FROM  (SELECT * FROM emp WHERE 日期 > ‘2022-12-05’)  e  LEFT  JOIN  dept  d  ON  e.部门id = d.id