大数据闯关之MySQL基础篇（八）：多表查询-灵析社区

写在前面

大家好，这里是立志于在有生之年看到并参与通用人工智能开发工作的Nobody，由于最近在公司要经常性地接触大数据工具，所以打算开一个大专栏对大数据工具进行学习总结整理。

以下为该部分的前置博客

大数据闯关之MySQL基础篇（七）：约束

大数据闯关之MySQL基础篇（六）：函数

大数据闯关之MySQL基础篇（五）：DCL用户管理

大数据闯关之MySQL基础篇（四）：DQL数据查询操作

大数据闯关之MySQL基础篇（三）：DML数据操作

大数据闯关之MySQL基础篇（二）：数据库操作

大数据闯关之MySQL基础篇（一）：MySQL安装及基础介绍

一、概述

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

一对多（多对一）：在多的一方建立外键，指向一的一方的主键
多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

我们建立以下几张表来创建学生表、课程表以及学生表和课程表的中间表

create table student(
    id int auto_increment primary key comment '主键TO',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null,'黛纳丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course value (null, 'java'), (null, 'PHP'), (null, 'MySQL'), (null, 'Hadoop');

create table student_course(
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';
insert into student_course values (null, 1, 1), (null, 1, 2),(null, 1, 3),(null, 2, 2),(null, 2, 3),(null, 3, 4);

一对一：多用于单表拆分，在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

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

连接查询

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

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

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

子查询

二、内连接

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

隐式内连接

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

显式内连接

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件;

我们试一下查询每一个员工以及其关联部门的名称和其他信息，用显式内连接

select * from emp inner join dept on emp.dept_id = dept.id;

三、外连接

左外连接

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/union all

SELECT 字段列表 FROM 表A 
UNION 
SELECT 字段列表 FROM 表B;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

六、子查询

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

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

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

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

标量子查询：子查询返回的结果是单个值（数字、字符串、日期等）

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

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

常用的操作符：IN、NOT IN、 ANY、SOME、ALL

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

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

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

常用的操作符：IN