MySQL--》全面掌握多表联接与查询优化技巧

目录
多表关系概述
多表查询概述
内毗连
外毗连(左外毗连、右外毗连)
自毗连
团结查询
子查询

---

多表关系概述

   多表关系：在项目开发过程中，在进行数据库表结构设计的时候会根据业务需求及业务模块之间的关系分析并设计表结构，由于业务之间相互关联所以各个表结构之间也存在各种联系，这些联系基本上分为三种，如下所示： 
  一对多(多对一)：比较典范的就是部分和员工之间的关系，一个部分对应多个员工。要想实现一对多或多对一关系，这里我们可以通过在多的一方创建外键指向一的那方的主键，如下图所示：

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

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

1. -- 创建学生表
2. create table students (
3.     id int primary key auto_increment comment '主键ID',
4.     name varchar(10) not null comment '姓名',
5.     no varchar(10) not null comment '学号'
6. ) comment '学生表';
7. insert into students values (null, '张三', '20141'), (null, '李四', '20142'), (null, '王五', '20143'), (null, '赵六', '20144');
8. -- 创建课程表
9. create table cources (
10.     id int primary key auto_increment comment '主键ID',
11.     name varchar(10) not null comment '课程名称'
12. ) comment '课程表';
13. insert into cources values (null, '语文'), (null, '数学'), (null, '英语'), (null, '物理'), (null, '化学');

复制代码

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

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

复制代码

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

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

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

1. -- 用户基本信息表
2. create table tb_user (
3.     id int primary key auto_increment comment '主键ID',
4.     name varchar(20) comment '姓名',
5.     age int comment '年龄',
6.     gender char(1) comment '1:男 2:女',
7.     phone varchar(11) comment '手机号'
8. ) comment "用户基本信息表";
10. create table tb_user_edu (
11.     id int primary key auto_increment comment '主键ID',
12.     degree varchar(20) comment '学历',
13.     major varchar(20) comment '专业',
14.     primary_school varchar(20) comment '小学',
15.     junior_high_school varchar(20) comment '初中',
16.     high_school varchar(20) comment '高中',
17.     university varchar(20) comment '大学',
18.     user_id int unique comment '用户ID',
19.     -- 设置用户教育信息表的外键user_id与用户表的主键ID关联
20.     constraint fk_user_id foreign key (user_id) references tb_user(id)
21. ) comment "用户教育信息表";
23. insert into tb_user (id, name, age, gender, phone) values
24.     (null, '张三', 20, '1', '13800000001'),
25.     (null, '李四', 21, '2', '13800000002'),
26.     (null, '王五', 22, '1', '13800000003'),
27.     (null, '赵六', 23, '2', '13800000004');
28. insert into tb_user_edu (id, degree, major, primary_school, junior_high_school, high_school, university, user_id) values
29.     (null, '本科', '计算机', '北京小学', '北京初中', '北京高中', '北京大学', 1),
30.     (null, '专科', '软件', '上海小学', '上海初中', '上海高中', '上海大学', 2),
31.     (null, '专科', '网络', '南京小学', '南京初中', '南京高中', '南京大学', 3),
32.     (null, '本科', '网络', '武汉小学', '武汉初中', '武汉高中', '华中科技大学', 4);

复制代码

多表查询概述

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

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

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

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

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

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

内毗连的查询语法主要有以下两种：
   隐式内毗连：通过where条件获取两张表中某字段相同的数据，如下所示：

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

复制代码

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

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

复制代码

只管两者在功能上没有实质性区别，但使用显式的join更具清晰性，建议在实际开发中优先使用。 
外毗连(左外毗连、右外毗连)

外毗连的查询语法主要有以下两种：
   左外毗连：相当于查询表1(左表)的所有数据包罗表1和表2交集部分的数据，如下所示：
  

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

复制代码

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

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

复制代码

自毗连

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

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

复制代码

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

1. create table emp (
2.     id int primary key auto_increment comment '主键ID',
3.     name varchar(20) not null comment '姓名',
4.     age int not null comment '年龄',
5.     job varchar(20) not null comment '职位',
6.     salary int not null comment '薪资',
7.     enter_date date not null comment '入职时间',
8.     manager_id int comment '直属上级ID'
9. ) comment '员工表';
10. insert into emp values
11.     (1, '金庸', 66, '总经理', 2000000, '1954-06-08', null),
12.     (2, '张无忌', 33, '部门经理', 1500000, '1984-06-23', 1),
13.     (3, '张翠山', 28, '部门经理', 1400000, '1987-05-06', 1),
14.     (4, '张三丰', 25, '部门经理', 1300000, '1990-08-16', 2),
15.     (5, '张翠山之子', 23, '部门经理', 1000000, '1994-06-08', 3),
16.     (6, '张无忌之子', 20, '部门经理', 800000, '1997-05-06', 4),
17.     (7, '张翠山之女', 18, '部门经理', 500000, '2000-06-09', 4),
18.     (8, '张三丰之子', 15, '部门经理', 300000, '2004-06-09', 5);

复制代码

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

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

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

团结查询

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

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

复制代码

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

子查询

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

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

复制代码

根据子查询结果的不同，主要分为以下四种情况，如下所示：
   标量子查询：子查询结果为单个值(数字、字符串、日期等)，最简单的情势，这种子查询称为标量子查询，常用操纵符：= <> > >= < <=，如下所示：
  如下我们可以先查询id为2的员工的工作身份，然后在通过工作身份查询整张表，如下所示：

   列子查询：子查询结果为一列(可以是多行)，这种查询称为列子查询，常用操纵符如下所示
  操纵符描述in在指定的聚集范围内，多选一not in不在指定的聚集范围之内any子查询返回列表中，有恣意一个满足即可some与any等同，使用some的地方都可以使用anyall子查询返回列表的所有值都必须满足 举例：如下我们先查询员工表当中年龄是23或35的员工id，然后通过id查询对应的所有员工信息，可以通过in语句查询id在聚集 3，5，6 范围内的所有数据：

   行子查询：子查询结果为一行(可以是多列)，这种子查询称为行子查询，常用操纵符：=、<>、in、not in，如下所示：
  如下我们想查询与张翠山薪资和上司ID相同的员工信息：

   表子查询：子查询结果为多行多列，这种子查询称为表子查询，常用操纵符：in，如下：
  如下我们想查询名称为张翠山大概张三丰的薪资和上司ID号相同的所有员工信息，如下所示：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。