六、MySQL多表查询

主要内容：

• 能够使用内连接进行多表查询
• 能够使用左外连接和右外连接进行多表查询
• 能够使用子查询进行多表查询能够使用多表进行查询

---

准备数据：

# 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'),('销售部');

# 创建员工表
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) -- 外键，关联部门表(部门表的主键)
);
INSERT 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);
-- 添加一个无部门的员工“沙僧”
INSERT INTO emp VALUES (NULL,'沙僧','男',6666,'2013-12-05',NULL);

1 内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。
从表.外键 = 主表.主键

1.1 隐式内连接

使用WHERE条件消除无用数据。
SELECT 字段列表 FROM 左表,右表 WHERE 条件;

-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp, dept WHERE emp.`dept_id` = dept.`id`;

-- 查询员工表的名称，性别、部门表的名称
SELECT
	emp.name, emp.gender, dept.name
FROM
	emp, dept
WHERE
	emp.`dept_id` = dept.`id`;

-- 使用别名
SELECT 
	t1.name, -- 员工表的姓名
	t1.gender,-- 员工表的性别
	t2.name -- 部门表的名称
FROM
	emp t1, dept t2
WHERE 
	t1.`dept_id` = t2.`id`;

1.2 显式内连接

使用INNER JOIN … ON语句，可以省略INNER。
SELECT 字段列表 FROM 左表 [INNER] JOIN 右表 ON 条件;

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

如果不加ON条件，则等于 SELECT 字段列表 FROM 左表,右表;
查询结果为两个表的笛卡尔积：两个集合A,B的所有组成情况。要完成多表查询，需要消除无用的数据，即增加条件。

SELECT * FROM emp INNER JOIN dept;
SELECT * FROM emp, dept;

-- 查询的是唐僧的信息，员工表.name='唐僧'
SELECT * FROM emp e INNER JOIN dept d ON e.`dept_id` = d.`id` WHERE e.`name`='唐僧';

--查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
SELECT
	e.`id` 编号,
	e.`name` 姓名,
	e.`gender` 性别,
	e.`salary` 工资,
	d.`name` 部门名字 
FROM
	emp e
	INNER JOIN dept d ON e.`dept_id` = d.`id` 
WHERE
	e.`name` = '唐僧';

1.3 总结内连接查询步骤

1. 确定查询哪些表；
2. 确定表连接的条件；
3. 确定查询的条件；
4. 确定查询的字段。

2 外连接

2.1 左外连接

使用LEFT OUTER JOIN … ON，OUTER可以省略。
SELECT 字段列表 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;
查询的是左表所有数据以及两表交集部分，如果右表中无数据则显示NULL。可以理解为：在内连接的基础上保证左表完整显示。

-- 查询所有员工信息，如果员工有部门则显示
SELECT * FROM emp e LEFT JOIN dept d ON e.`dept_id` = d.`id`;

2.2 右外连接

使用RIGHT OUTER JOIN … ON，OUTER可以省略。
SELECT 字段列表 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
查询的是右表所有数据以及两表交集部分，如果左表中无数据则显示NULL。可以理解为：在内连接的基础上保证右表完整显示。

-- 查询所有部门信息，如果部门有员工则显示
SELECT * FROM emp e RIGHT JOIN dept d ON e.`dept_id` = d.`id`;

3 子查询

3.1 概念

1. 查询中嵌套查询，称内部查询为子查询；
2. 一个查询的结果做为另一个查询的条件；
3. 子查询要使用括号。

3.2 子查询结果是单行单列

子查询在WHERE后面作为条件，父查询使用比较运算符，如：> 、<、<>、= 等。
SELECT 字段列表 FROM 表 WHERE 字段 = (子查询);

1. 查询工资最高的员工：

   -- 1) 查询最高工资是多少
   SELECT max(salary) FROM emp; --9000
   -- 2) 根据最高工资到员工表查询到对应的员工信息
   SELECT * FROM emp WHERE salary = (SELECT max(salary) FROM emp);
   

   
2. 查询工资小于平均工资的所有员工：

   -- 1) 查询平均工资是多少
   SELECT avg(salary) FROM emp; -- 5994.333333333333
   -- 2) 到员工表查询小于平均的员工信息
   SELECT * FROM emp WHERE salary < (SELECT avg(salary) FROM emp);
   

   

3.3 子查询结果是多行单列

结果集类似于一个数组，父查询使用IN运算符。
SELECT 字段列表 FROM 表 WHERE 字段 IN (子查询);

1. 查询工资大于5000的员工所属部门的名字：

   -- 先查询大于5000的员工所在的部门id
   SELECT dept_id FROM emp WHERE salary > 5000;
   -- 再查询在这些部门id中部门的名字
   -- Subquery returns more than 1 row
   -- SELECT NAME FROM dept WHERE id = (SELECT dept_id FROM emp WHERE salary > 5000);
   SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);
   

   
2. 查询开发部与财务部所有的员工信息：

   -- 先查询开发部与财务部的id
   SELECT id FROM dept WHERE NAME IN ('开发部','财务部');
   -- 再查询在这些部门id中有哪些员工
   SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN ('开发部','财务部'));
   

   

3.4 子查询结果是多行多列

在FROM后面作为表，表需要取别名，否则这张表没有名称则无法访问表中的字段。
SELECT 字段列表 FROM (子查询) 表别名 WHERE 条件;

1. 查询出2011年以后入职的员工信息，包括部门名称：

   -- 在员工表中查询2011-1-1以后入职的员工
   SELECT * FROM emp WHERE join_date >= '2011-1-1';
   -- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
   SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date >= '2011-1-1') e WHERE d.`id` = e.dept_id;
   

   
2. 通过内连接来完成上述查询：

   SELECT * FROM emp e, dept d WHERE e.dept_id = d.id AND e.join_date >= '2011-1-1'
   SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id` WHERE join_date >= '2011-1-1'; 
   SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id` AND join_date >= '2011-1-1';
   

3.5 子查询小结

1. 子查询结果只要是单列，则在WHERE后面作为条件；
2. 子查询结果只要是多列，则在FROM后面作为表进行二次查询。