📖Mosquito's Note📖

多表查询

多表关系

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

  • 一对多
  • 多对多
  • 一对一

一对多

  • 案例：部门与员工的关系
  • 关系：一个部门对应多个员工，一个员工对应一个部门
  • 实现：在多的一方建立外键，指向一的一方的主键

多对多

  • 案例：学生与课程的关系
  • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选修
  • 实现：建立第三张中间表，中间表值扫包含两个外键，分别关联两方主键

一对一

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

概述

  • 概述：指从多张表中查询数据
  • 笛卡尔积：笛卡儿积指子数学中，两个集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

分类

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

内连接

内连接其实也是自然连接，相当于先做笛卡儿积再根据条件筛选(probably)，查询两张表交集的部分

# 隐式内连接
select 字段列表 from 表 1, 表 2 where 条件...;

# 显式内连接
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 表 A 别名 B on 条件...;

联合查询

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

select 字段列表 from 表 A...;

union[ALL]

select 字段列表 from 表 B...;

子查询

sql 语句中嵌套 select 语句，称为嵌套查询，又称为子查询

select * from table1 where column1 = (select column1 from table2);

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

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

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

标量子查询

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

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

列子查询

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

常见的操作符：in , not in , any , some , all

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

例： 
select * from emp where salary > some (select salary from emp where dept_id  = (select id from dept where name = '研发部'));

行子查询

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


例： 
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = 'xxx')

多表查询

即将子查询的结果表作为表，进行进一步的查询


例： 
select * from emp where (job, salary) in (select job, salary from emp where name = 'a' or name = 'b');


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