MySQL数据库多表查询（隐式内连接、显式内连接、左外连接、右外连接）及子查询的详细解释

创建两张表，部门表（department）、员工表（employee）

create table department(id int primary key auto_increment,name
varchar(20));

insert into department (name) values (‘开发部’),(‘市场部’),(‘财务部’);

create table employee(id int primary key auto_increment,name
varchar(10),sex char(1),num double,join_date date,department_id
int,foreign key (department_id) references department(id));

笛卡尔积现象

通过 select * from department,employee;查询两张数据表，发现不是所有的数据组合都是有用的，只有员工表 department_id = employee_id 的数据是有用的，这种现象称为：笛卡尔积现象，所以需要通过条件过滤掉没用的数据。

通过 select * from employee e,department d where e.department_id = d.id; 查询可以去除笛卡尔积现象，这种查询也叫 隐式内连接查询

一、隐式内连接

上面的去除笛卡尔积现象，也叫隐式内连接，条件使用 where

select * from employee e,department d where e.department_id = d.id;
1

二、显式内连接

使用 ：inner join ... ... on inner可以省略

 select * from employee e inner join department d on e.department_id = d.id;
1

若查询 马云 的信息，则需要联合 2 张表同时查询才能查询出想要的数据

 select * from employee e join department d on e.department_id = d.id where e.name = '马云';
1

查询 开发部 的人员的姓名

查询三个部门的平均分数

select d.name,avg(e.num) from employee e inner join department d on e.department_id = d.id group by d.name;
1

三、左外连接

在上面的查询中可以发现，通过隐式内连接查询不到刚添加的数据（小泽玛利亚）

在上面的查询中可以发现，通过显式内连接也查询不到刚添加的数据（小泽玛利亚）

1、左外连接

• 使用：left outer join ... ... on outer 可以省略
• 用左边表的记录去匹配右边表的记录，有符合条件的就会显示，没有则显示 null
• 理解：在内连接的基础上，保证左边表的数据全部显示（左表：部门；右表：员工）

四、右外连接

• 使用 ： right outer join ... ... on outer 可以省略
• 用右边表的记录去匹配左边表的记录，有符合条件的就会显示，没有则显示 null
• 理解：在内连接的基础上保证右表的数据全部显示

五、子查询

比如：要查询分数在平均分数以上的人员

1、子查询的概述

• 一个查询的结果作为另一个查询的条件
• 有查询的嵌套，括号内部的查询称为子查询
• 子查询要使用括号

2、子查询的结果分为三种情况

• 单行单列
• 多行单列
• 多行多列

3、单行单列
查询分数最高的员工姓名

4、多行单列
查询小于平均分数的员工姓名

5、多行多列
查询开发部所有员工信息