【MySQL学习记录】第六章：多表查询（内连接、外连接、等值查询、非等值查询）

最新推荐文章于 2024-04-23 10:38:35 发布

原创最新推荐文章于 2024-04-23 10:38:35 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #学习 #数据库

MySQL 专栏收录该内容

6 篇文章

订阅专栏

这篇博客介绍了MySQL中多表查询的基本概念和操作，包括内连接、外连接（左外、右外、满外）的实现方式，以及等值连接和非等值连接的区别。通过实例讲解了如何避免笛卡尔积错误，并讨论了自连接、自然连接和USING连接的用法。最后，提出了课后练习题，帮助读者巩固所学知识。

1. 熟悉几张表

DESC employees;
DESC departments;
DESC locations;

employees
departments
locations
查询员工名为 "Abel"的工作城市

SELECT * 
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;

SELECT city
FROM locations
WHERE location_id = 2500;

2. 为什么使用多张表

解决存储时数据冗余的问题
加载数据到内存时，数据少、速度快
维护简单

3. 多表查询实现

笛卡尔积错误

查询employee_id，department_name

SELECT employee_id, department_name
FROM employees, departments;	# 共2889条记录

/*
出错原因：每个员工都与每个部门匹配了一遍，出现笛卡尔积错误

产生原因：
缺少连接条件
*/

在这里插入图片描述

笛卡尔积（交叉连接）

在这里插入图片描述

正确方式

查询employee_id，department_name

SELECT employee_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在这里插入图片描述

如果查询语句出现了多个表中都存在的字段，则必须指明此字段所在的

查询employee_id，department_name，department_id

SELECT employee_id, department_name, department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;	# 报错

错误：department_id不明确

建议：从sql优化的角度，建议多表查询时，每个字段都指明其所在的表

SELECT employees.employee_id, departments.department_name, departments.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在这里插入图片描述

可以给表起别名，在SELECT和WHERE中使用表的别名。

SELECT emp.employee_id, dept.department_id, dept.department_name
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`;

如果给表起了别名，就必须使用别名，不可以再用表的原名

练习：查询员工的employee_id, last_name, department_name, city

SELECT emp.employee_id, emp.last_name, dept.department_id, loc.city
FROM employees emp, departments dept, locations loc
WHERE emp.`department_id` = dept.`department_id` AND dept.`location_id` = `loc.location_id`;

在这里插入图片描述
若有n个表，则至少需要n-1个连接条件

4. 多表的分类

等值连接 VS 非等值连接

非等值连接举例：

查询员工last_name, salary, grade_level

SELECT last_name, salary, grade_level
FROM employee e, job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

在这里插入图片描述

自连接 VS 非自连接

自连接

查询员工姓名，员工id，及其管理者的id和姓名

SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
FROM employees emp, employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

在这里插入图片描述

内连接 VS 外连接

内连接

定义：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`	# 106条记录

外连接

定义：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行外，还包含左表或右表中不匹配的行。

外连接的分类：左外连接、右外连接、满外连接

查询所有员工的last_name, department_name信息。

一旦多表查询中涉及到所有：外连接

SQL92语法实现外连接：使用+，MySQL不支持此种写法。

SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`(+);	# 左外连接

SQL99语法实现外连接：使用JOIN…ON（也可实现内连接）

SQL99实现内连接（INNER JOIN …ON）

SELECT last_name, department_name, city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

SQL99实现左外连接（LEFT OUTER JOIN … ON）

查询所有的员工的last_name，department_name信息

SELECT last_name, department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;	# 107条记录

在这里插入图片描述

SQL99实现右外连接（RIGHT OUTER JOIN … ON）

查询所有的员工的last_name，department_name信息

SELECT last_name, department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;	# 122条记录

在这里插入图片描述

SQL99实现满外连接（MySQL不支持FULL OUTER JOIN … ON，Oracle可以）

查询所有的员工的last_name，department_name信息

SELECT last_name, department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;	# 123条记录

合并查询结果（UNION、UNION ALL）

UNION：返回并集，去除重复记录
UNION ALL：合并不去重

尽量使用UNION ALL，需要的资源少，效率高

7种SQL JOINS的实现

在这里插入图片描述
中图：内连接

SELECT e.last_name, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

左上图：左外连接

SELECT e.last_name, d.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

右上图：右外连接

SELECT e.last_name, d.department_id
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

左中图：

SELECT employee_id, department_id
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

右中图：

SELECT employee_id, department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

左下图：

方式1：左上图 UNION ALL 右中图

SELECT employee_id, department_id
FROM employees e LEFT JOIN departments d
WHERE e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id, department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.department_id IS NULL;

方式2：左中图 UNION ALL 右上图

SELECT employee_id, department_id
FROM employees e LEFT JOIN departments d
WHERE e.`department_id` = d.`department_id`
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id, department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

右下图：左中图 UNION ALL 右中图

SELECT employee_id, department_id
FROM employees e LEFT JOIN departments d
WHERE e.`department_id` = d.`department_id`
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id, department_id
FROM employees e RIGHT JOIN departments d
WHERE e.`department_id` = d.`department_id`
WHERE e.department_id IS NULL;

5. 自然连接（NATURAL JOIN）

自动查询两张表中的相同字段，进行等值连接

SELECT employee_id, last_name, department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;

在这里插入图片描述

SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;

在这里插入图片描述

6. USING连接

USING 指定了具体的相同的字段名称，需要在 USING 的括号中填入要指定的同名字段。可以简化 JOIN…ON 的等值连接

SELECT employee_id, last_name, department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id;

在这里插入图片描述

SELECT employee_id, last_name, department_name
FROM employees e 
JOIN departments d
USING (department_id);

在这里插入图片描述

课后练习


# 1.显示所有员工的姓名，部门号和部门名称。 
SELECT last_name, emp.department_id, department_name
FROM employees emp 
LEFT JOIN departments dept
ON emp.department_id = dept.department_id;


# 2.查询90号部门员工的job_id和90号部门的location_id 
SELECT e.job_id, d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;

SELECT e.job_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = 90;


# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city 
SELECT e.last_name, d.department_name, l.location_id, l.city
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
SELECT e.last_name, e.job_id, d.department_id, d.department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE l.city = 'Toronto';

# SQL92
SELECT last_name , job_id , e.department_id , department_name 
FROM employees e, departments d, locations l 
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT e.last_name, j.job_title, e.salary, d.department_name, l.street_address
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN jobs j
ON e.job_id = j.job_id
JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_name = 'Executive';

SELECT department_name, street_address, last_name, job_id, salary 
FROM employees e JOIN departments d 
ON e.department_id = d.department_id 
JOIN locations l 
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive';


# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
SELECT e.last_name AS 'employees', e.employee_id AS 'Emp#', m.last_name AS 'manager', m.employee_id AS 'Mgr#'
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;


# 7.查询哪些部门没有员工 
SELECT d.department_id 
FROM departments d 
LEFT JOIN employees e 
ON e.department_id = d.`department_id`
WHERE e.department_id IS NULL;

# 拓展：子查询
SELECT department_id
FROM departments d
WHERE NOT EXISTS(
	SELECT * 
	FROM employees e
	WHERE e.department_id = d.department_id
);


# 8. 查询哪个城市没有部门
SELECT city, d.location_id
FROM locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.location_id IS NULL;

# 拓展
SELECT city
FROM locations l
WHERE NOT EXISTS(
	SELECT *
	FROM departments d
	WHERE l.location_id = d.location_id
);


# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN('Sales', 'IT');