MySQL多表联接与查询优化技巧详解

MySQL多表联接与查询优化技巧详解

引用

CSDN

1.

https://m.blog.csdn.net/qq_53123067/article/details/144452491

多表关系概述

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

一对多(多对一)

比较典型的就是部门和员工之间的关系，一个部门对应多个员工。要想实现一对多或多对一关系，这里我们可以通过在多的一方建立外键指向一的那方的主键，如下图所示：

多对多

比较典型的就是学生和课程的关系，一个学生可以选修多门课程同时一门课程也可以供多个学生进行选择。要想实现多对多关系，这里我们可以建立第三张中间表，中间表至少包含两个外键分别去关联两方主键，如下图所示：

这里我们做一下演示，首先我们先创建好学生表和课程表并插入几条数据，执行如下语句：

-- 创建学生表
create table students (
    id int primary key auto_increment comment '主键ID',
    name varchar(10) not null comment '姓名',
    no varchar(10) not null comment '学号'
) comment '学生表';
insert into students values (null, '张三', '20141'), (null, '李四', '20142'), (null, '王五', '20143'), (null, '赵六', '20144');

-- 创建课程表
create table cources (
    id int primary key auto_increment comment '主键ID',
    name varchar(10) not null comment '课程名称'
) comment '课程表';
insert into cources values (null, '语文'), (null, '数学'), (null, '英语'), (null, '物理'), (null, '化学');

接下来我们开始创建一条中间表，中间表关联就是学生表和课程表之间的数据，执行如下语句：

-- 创建中间表来维护学生表和课程表的关联关系
create table student_cource (
    id int primary key auto_increment comment '主键ID',
    student_id int not null comment '学生ID',
    cource_id int not null comment '课程ID',
    constraint fk_student foreign key (student_id) references students(id),
    constraint fk_cource foreign key (cource_id) references cources(id)
) comment '学生课程中间表';
insert into student_cource values (null, 1, 2), (null, 1, 3), (null, 2, 4), (null, 3, 1), (null, 4, 2);

创建好中间表之后我们可以看到学生表与课程表的已经被我们关联到了，这里我们也可以通过可视化界面的形式来展示多表之间的关系，如下所示可以看到学生和课程之间的关系是通过一张中间表取得关联的，中间表当中有两个外键分别对应学生表的主键和课程表的主键：

一对一

比较典型的就是用户与用户详情之间的关系，一对一关系多用于单表的拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中以提升效率。要想实现一对一的关系，这里我们可以在任意一方加入外键，关联另外一方的主键，并设置外键唯一的(UNIQUE)，如下图所示：

这里我们可以执行如下语句进行演示一对一关系表的关联：

-- 用户基本信息表
create table tb_user (
    id int primary key auto_increment comment '主键ID',
    name varchar(20) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1:男 2:女',
    phone varchar(11) comment '手机号'
) comment "用户基本信息表";

create table tb_user_edu (
    id int primary key auto_increment comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(20) comment '专业',
    primary_school varchar(20) comment '小学',
    junior_high_school varchar(20) comment '初中',
    high_school varchar(20) comment '高中',
    university varchar(20) comment '大学',
    user_id int unique comment '用户ID',
    -- 设置用户教育信息表的外键user_id与用户表的主键ID关联
    constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment "用户教育信息表";

insert into tb_user (id, name, age, gender, phone) values
    (null, '张三', 20, '1', '13800000001'),
    (null, '李四', 21, '2', '13800000002'),
    (null, '王五', 22, '1', '13800000003'),
    (null, '赵六', 23, '2', '13800000004');

insert into tb_user_edu (id, degree, major, primary_school, junior_high_school, high_school, university, user_id) values
    (null, '本科', '计算机', '北京小学', '北京初中', '北京高中', '北京大学', 1),
    (null, '专科', '软件', '上海小学', '上海初中', '上海高中', '上海大学', 2),
    (null, '专科', '网络', '南京小学', '南京初中', '南京高中', '南京大学', 3),
    (null, '本科', '网络', '武汉小学', '武汉初中', '武汉高中', '华中科技大学', 4);

多表查询概述

多表查询是指从多张表中查询数据，比如上面我们创建的用户基本信息表和用户教育信息表，如果我们同时查询这两张表的话就会出现两张表组合的所有情况，这种现象就叫做笛卡儿积，如下：

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

但是我们真实想要的仅仅只是与外键对应的主键的两张表的具体信息，这里我们需要在多表查询的时候得消除无效的笛卡儿积，达到如下的效果：

这里我们只需要再进行多表查询的时候通过where判断，当前的外键与主键一一对应即可：

当然多表查询还细分几个分类，接下来对这几个分类进行一一讲解，如下所示：

内连接

内连接查询的是两张表的交集部分，如下图所示：

内连接的查询语法主要有以下两种：

隐式内连接：通过where条件获取两张表中某字段相同的数据，如下所示：

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

显式内连接：通过join...on的形式来进行联表查询数据，如下所示：

-- 显式内连接（inner可以省略）
select 字段列表 from 表1 inner join 表2 on 连接条件

尽管两者在功能上没有实质性区别，但使用显式的join更具清晰性，建议在实际开发中优先使用。

外连接(左外连接、右外连接)

外连接的查询语法主要有以下两种：

左外连接：相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据，如下所示：

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

右外连接：相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据，如下所示：

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

自连接

自连接查询，可以是内连接查询也可以是外连接查询，其语法如下所示：

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

接下来我们执行如下的语句创建一张员工信息表：

create table emp (
    id int primary key auto_increment comment '主键ID',
    name varchar(20) not null comment '姓名',
    age int not null comment '年龄',
    job varchar(20) not null comment '职位',
    salary int not null comment '薪资',
    enter_date date not null comment '入职时间',
    manager_id int comment '直属上级ID'
) comment '员工表';

insert into emp values
    (1, '金庸', 66, '总经理', 2000000, '1954-06-08', null),
    (2, '张无忌', 33, '部门经理', 1500000, '1984-06-23', 1),
    (3, '张翠山', 28, '部门经理', 1400000, '1987-05-06', 1),
    (4, '张三丰', 25, '部门经理', 1300000, '1990-08-16', 2),
    (5, '张翠山之子', 23, '部门经理', 1000000, '1994-06-08', 3),
    (6, '张无忌之子', 20, '部门经理', 800000, '1997-05-06', 4),
    (7, '张翠山之女', 18, '部门经理', 500000, '2000-06-09', 4),
    (8, '张三丰之子', 15, '部门经理', 300000, '2004-06-09', 5);

该员工表可以看作是两张表，员工的领导ID对应的就是主键id的领导，如下所示：

这里我们可以通过内连接查询所有员工对应领导的交集内容，如下所示：

这里我们也可以使用外连接的方式（左外连接）查询，即使没有上司也要把数据展示出来：

联合查询

对于union查询就是把多次查询的结果合并起来形成一个新的查询结果集，其语法格式如下：

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;

对于联合查询的多张表的列数必须保持一致，字段类型也必须保持一致，union all会将全部的数据直接合并在一起，union会对合并的数据进行去重，联合查询在逻辑上属于逻辑or的概念：

子查询

在SQL语句当中嵌套select语句称为嵌套查询，又称为子查询。子查询外部的语句可以是insert/update/delete/select的任何一个，其基本语句如下所示：

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

根据子查询结果的不同，主要分为以下四种情况，如下所示：

标量子查询：子查询结果为单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询，常用操作符：= <> > >= < <=，如下所示：

如下我们可以先查询id为2的员工的工作身份，然后在通过工作身份查询整张表，如下所示：

列子查询：子查询结果为一列(可以是多行)，这种查询称为列子查询，常用操作符如下所示

举例：如下我们先查询员工表当中年龄是23或35的员工id，然后通过id查询对应的所有员工信息，可以通过in语句查询id在集合 3，5，6 范围内的所有数据：

行子查询：子查询结果为一行(可以是多列)，这种子查询称为行子查询，常用操作符：=、<>、in、not in，如下所示：

如下我们想查询与张翠山薪资和上司ID相同的员工信息：

表子查询：子查询结果为多行多列，这种子查询称为表子查询，常用操作符：in，如下：

如下我们想查询名称为张翠山或者张三丰的薪资和上司ID号相同的所有员工信息，如下所示：