MySQL多表查询

多表查询

---

多表关系

• 概述

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

  • 一对多（多对一）

  • 多对多

  • 一对一

    一对多（多对一）

    • 案例：部门与员工的关系

    • 一个部门下有多个员工，一个员工只能对应一个部门

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

      

    多对多

    • 案例：学生与课程的关系

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

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

      

    一对一

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

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

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

      

---

多表查询概述

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

• 笛卡尔积

• 多表查询分类

  • 连接查询

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

    外连接：

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

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

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

  • 子查询

    

---

内连接

内连接查询的时两张表交集的部分（交集）

内连接查询语法：

• 隐式内连接

  select 字段列表 from 表1，表2 where 连接条件 ...;
  

• 显式内连接

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

---

外连接

外连接查询语法：

• 左外连接

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

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

• 右外连接

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

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

---

自连接

自连接查询语法：

select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;

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

---

联合查询-union，union all

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

-- 将表A和表B 合并成一个新的查询结果
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;

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

---

子查询

• 概念：SQL语句中嵌套select语句，成为嵌套查询，又称子查询。

  select * from t1 where column1=(select column1 from t2);
  

  子查询外部的语句可以是 insert / update / delete / select 的任何一个

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

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

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

  以下表为例，创建 emp表

  -- 创建emp表
  create table emp(
      id int auto_increment primary key comment 'ID',
      name varchar(10) not null comment '姓名',
      age int check ( age>0 && age<=120 ) not null comment '年龄',
      job char(10) comment '工作',
      salary int unsigned comment '薪水',
      entryDate date not null  comment '入职日期',
      managerId int comment '直属领导',
      dept_id int not null comment '部门id',
      dept_name char(10) comment '部门名'
  ) comment '员工表';
  
  -- 插入数据
  insert into emp (id, name, age, job, salary, entryDate, managerId, dept_id, dept_name)
  values (null,'ztt',20,'总裁',20000,'2002-05-01',null,1,'人事部'),
         (null,'zs',20,'总裁',20000,'2002-05-10',null,1,'人事部'),
         (null,'nxl',20,'项目经理',19000,'2009-05-01',1,1,'人事部'),
         (null,'xcc',20,'项目经理',19000,'2009-05-01',1,2,'财务部'),
         (null,'ls',20,'职员',2000,'2022-05-01',3,2,'财务部');
         
  -- ---------------------------------------------------------------------
  
  -- 创建部门表     
  create table dept(
      id int primary key comment 'ID',
      name char(10) comment '部门名'
  ) comment '部门表';
  
  -- 插入数据
  insert into dept values (1,'人事部'),(2,'财务部'),(3,'销售部');
  

  标量子查询

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

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

    示例：

    -- 标量子查询
    -- 1.查询“销售部”的所有员工信息
    
    -- ---a.查询“销售部”部门ID
    select id from dept where name = '销售部';
    
    -- ---b.根据销售部部门ID，查询员工信息
    select * from emp where debt_id=(select id from dept where name = '销售部');
    

  列子查询

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

  • 常用的操作符：in、not in、any、some、all

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

  示例：

  -- 列子查询
  -- 例 1
  -- 1.查询”销售部“和”市场部“的所有员工信息
  
  -- ---a.查询“人事部” 和”财务部“的部门ID
  select id from dept where name='人事部' || name='财务部';
  
  -- ---b.根据部门ID，查询员工信息
  select * from emp where dept_id in (select id from dept where name='人事部' || name='财务部');
  
  -- ----------------------------------------------------------------------
  
  -- 例 2
  -- 2.查询比财务部所有人工资都高的人员信息
  -- ---a.查询所有 财务部 人员工资
  select id from dept where name='财务部';
  
  select salary from emp where dept_id = (select id from dept where name='财务部');
  
  -- ---b.比 财务部所有人工资都高的员工信息
  select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name='财务部'));
  
  /*****************************************************
  *其中 salary > all() 指的是 比all() 中的所有 salary 都高
  *
  ******************************************************/
  

  行子查询

  • 子查询返回的结果是一行（可以是多列），这种子查询成为行子查询。
  • 常用的操作符：=、<>、in、not in

  示例：

  -- 查询与”nxl“的薪资及直属领导相同的员工信息
  
  -- ---a.查询 ”nxl“的薪资以及直属领导
  select salary,managerid from emp where name='nxl';
  
  -- ---b.查询 查询与”nxl“的薪资及指数领导相同的员工信息
  select * from emp where (salary,managerid)=(select salary,managerid from emp where name='nxl');
  

  表子查询

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

  • 常用的符号：in

    示例：

    -- 表子查询
    -- 1.查询与”nxl“和”ztt“的职位和薪资相同的员工信息
    -- ---a.查询”nxl“和”ztt“的职位和薪资
    select job,salary from emp where name='nxl' || name='ztt';
    
    -- ---b.查询与”nxl“和”ztt“的职位和薪资相同的员工信息
    select * from emp where (job,salary) in (select job,salary from emp where name='nxl' || name='ztt');