MySQL数据库多表查询

前言

在之前的文章MySQL数据库之SQL入门中，我们讲到了四类SQL中的DQL(数据库查询语言，用于查询表中数据)，但是我仅仅用DQL去查询一张表中数据，而在实际的应用中，经常是对多张数据库表进行查找。本文介绍MySQL数据库的多表查询，介绍内连接查询、外连接查询以及子查询三种多表查询方式及其区别。

零、创建用于查询的示例数据库表

1. 创建一张部门表

CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20)
);

2. 为dept表添加数据

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

3. 创建一张员工表

CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dept_id INT,FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
);

4. 为emp表添加数据

NSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

5. 部门表dept和员工表emp之间的关系
   

一、笛卡尔积

• 如果我们使用如下sql查询员工表和部门表，查询得到的是笛卡尔积结果。

SELECT * FROM emp,dept;

• 笛卡尔积：A笛卡尔积B=A与B组合得到的所有情况。
  
• 笛卡尔积的结果包含所有可能的组合数据，但是有很多数据是不合理，不正确的（如孙悟空的部门开发部，而查询结果中有两行数据显示他不是开发部，这两行数据很明是不正确的数据，是我们所不需要的数据），因此，我们需要从笛卡尔积的结果中筛选出我们需要的正确数据。
• 如对于上表笛卡尔积的结果中，我们需要添加员工表中的dept_id要与部门表中的id相同这一条件。而添加这一条件有三种方式，分别对应内连接查询，外连接查询和子查询三种查询方式。

二、内连接查询

1. 隐式内连接：使用where条件消除无用数据

SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id`=t2.`id`;

2. 显式内连接

• 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件

SELECT * FROM emp t1 INNER JOIN dept t2 ON t1.`dept_id`=t2.`id`;

三、外连接查询

1. 左外连接

• 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
• 查询的是左表所有数据以及其交集部分。 即用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL
• 可以理解为：在内连接的基础上保证左表的数据全部显示
• 示例：

-- 在部门表中增加一个销售部
INSERT INTO dept(dept.`NAME`) VALUES('销售部');
-- 使用内连接查询两张表
SELECT * FROM emp t1 INNER JOIN dept t2 ON t1.`dept_id`=t2.`id`;
-- 使用左外连接查询两张表
SELECT * FROM dept t1 LEFT OUTER JOIN emp t2 ON t1.`id`=t2.`dept_id`; 

使用内连接查询两张表结果如下图（仅仅展示了双方都有的数据，因为员工没有属于销售部的，所以没有查询出销售部的信息）

使用左外连接查询两张表结果如下图（查询左表即部门表的所有数据，如果右有匹配的数据则展示，否则赋值为null）

2. 右外连接

• 右连接查询和左连接查询正好相反，一般掌握一个即可，只要将左右表交换，即可实现左右连接的效果。
• 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
• 查询的是右表所有数据以及其交集部分。 即用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL
• 可以理解为：在内连接的基础上保证右表的数据全部显示
• 示例：

-- 在员工表中增加一个员工
INSERT INTO emp VALUES (NULL, '沙僧','男',6666,'2013-12-05',NULL);
-- 使用内连接查询
SELECT * FROM dept INNER JOIN emp ON dept.`id` = emp.`dept_id`;
-- 使用右外连接查询
SELECT * FROM dept RIGHT JOIN emp ON dept.`id` = emp.`dept_id`;

• 使用内连接查询结果如下图
  
• 使用右外连接查询结果如下图
  

四、子查询

• 概念：查询中嵌套查询，称嵌套查询为子查询。
• 示例：查询工资最高的员工信息

SELECT 
*
FROM 
emp 
WHERE 
emp.`salary` = 
(
SELECT 
MAX(salary) 
FROM 
emp);

• 子查询不同情况
  1.子查询的结果是单行单列的：子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

2.子查询的结果是多行单列的：子查询可以作为条件，使用运算符in来判断

-- 查询'财务部'和'市场部'所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

3.子查询的结果是多行多列的：子查询可以作为一张虚拟表参与查询

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2WHERE t1.id = t2.dept_id;