多表查询

多表查询¹

1 多表关系

1.1. 概述

1. 概述：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构也存在着各种关系，基本分为三种：一对多（多对一）、多对多、一对一
2. 一对多（多对一）
   • 案例：部门与员工的关系
   • 关系：一个部门对应多个员工，一个员工对应一个部门
   • 实现：在多的一方建立外键，指向一的一方的主键
3. 对对多
   • 案例：学生 与 课程 的关系
   • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
   • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
4. 一对一
   • 案例：用户 与 用户详情 的关系
   • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
   • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（*UNIQUE*）

2 多表查询概述

2.1. 概述

指从多张表中查询数据

2.2. 笛卡尔积

1. 笛卡尔乘积是指在数学中，两个集合 A集合 和 B集合 的所有组合情况

   （在多表查询时，要消除无效的笛卡尔积）

   

2. 示例

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

2.3. 多表查询的分类

1. 连接查询

   • 内连接：相当于查询 A、B交集 部分的数据
   • 外连接：
     • 左外连接：查询 左表 所有数据，以及两张表的交集部分数据
     • 右外连接：查询 右表 所有数据，以及两张表的交集部分数据
     • 自连接：当前表与自身的连接查询，自连表必须使用表别名

2. 子查询

   

3 内连接

3.1. 查询语法

1. 隐式内连接

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

2. 显式内连接

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

3. 内连接查询的是两张表交集的部分

   

4 外连接

4.1. 查询语法

1. 左外连接

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

   相当于查询表1（左表）的所有数据 包含 表1和表2交集部分的数据

   

2. 右外连接

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

   相当于查询表2（右表）的所有数据 包含 表1和表2交集部分的数据

   

5 自连接

5.1. 语法

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

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

6 联合查询(UNION,UNION ALL)

6.1. 概述

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

6.2. 语法

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

对于联合查询的多张表的 **列数** 必须保持一致，**字段类型** 也需要保持一致 `UNION ALL` 会将全部的数据直接合并在一起， `UNION` 会对合并之后的数据去重

7 子查询

7.1. 概述

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

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

子查询外部的语句可以是 INSERT UPDATE DELETE SELECT

7.2. 分类

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

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

     • 概述：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为 标量子查询
     • 常用的操作符：= <> < >= < <=

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

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

     • 常用操作符：

       

     • 示例：

       SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));
       

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

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

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

     • 示例：

       SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary,managerid FROM emp WHERE name = '张无忌');
       

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

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

     • 常用的操作符：IN

     • 示例：

       SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE name = '鹿杖客' OR name = '宋远桥');
       

       SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate >'2006-01-01') e LEFT JOIN dept d ON e.dept_id = d.id;
       

2. 根据子查询位置，分为：

   • WHERE 之后
   • FROM 之后
   • SELECT 之后

1. 参考黑马程序员 ↩