MySQL基础篇——多表查询

2022-03-28 06:39:16

多表查询

01、 一个案例引发的多表连接

1.1、案例说明

MySQL基础篇——多表查询
从多个表中获取数据：

#错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

SELECT *
FROM employees;  #107条记录

SELECT *
FROM departments; # 27条记录
SELECT 107*27 FROM dual;

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。
错误的原因：缺少了多表的连接条件

1.2、 笛卡尔积的理解

  • 笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
  • SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

1.3、案例分析与问题解决

笛卡尔积的错误会在下面条件下产生：

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接
    为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。 
#3、多表查询的正确方式：需要有连接条件(使多个表之间能够相联系起来)

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;
 

#4、如果查询语句出现了多个表中都存在的字段，则必须指明此字段所在的表。
#建议：从SQL优化的角度，建议多表查询时，每个字段前都指明其所在的表 。

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

#5、可以给表起别名，在SELECT和WGERE中使用表的别名。
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

#如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
#原因：在SQL中，FROM语句最先执行，一旦执行就会把我们自定义表的别名覆盖了表的原名，故不能再使用原名。
#如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

#6、结论：如果有n个表实现多表的查询，则至少需要n-1个连接条件，否则会出现笛卡尔积的错误。
#练习：查询员工的`employee_id`，`last_name`，`department_name`，`city`
SELECT emp.`employee_id`,emp.`last_name`,dept.`department_name`,loc.`city`
FROM employees emp,departments dept,locations loc
WHERE emp.`department_id` = dept.`department_id`
AND dept.`location_id` = loc.`location_id`;

02、多表查询

2.1、等值连接 vs 非等值连接

案例

7.1 等值连接  vs  非等值连接
#非等值连接的例子：查询员工`last_name`，`salary`，`grade_level`
SELECT e.`last_name`,e.`salary`,j.`grade_level`
FROM `employees` e,`job_grades` j
WHERE e.`salary` BETWEEN j.`lowest_sal`AND j.`highest_sal`;
#WHERE e.`salary`   >= j.`lowest_sal`AND e.salary <= j.`highest_sal`;

2.2、自连接 vs 非自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

案例

# 7.2、自连接  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`;

2.3、内连接 vs 外连接

  • 内连接: 合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  • 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。
#7.3、内连接  vs  外连接
# 内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;  #满足此等式只有106条记录

# 外连接：合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外，
#         还查询到了左表 或 右表中不匹配的行。

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

# 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
# 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

#练习：查询所有的员工的last_name,department_name信息 （若出现“所有的”字眼，且字段来自不同的表，则一定要使用外连接）

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

#SQL92语法实现内连接：见上，略
#SQL92语法实现外连接：使用 +  ----------MySQL不支持SQL92语法中外连接的写法！Oracle 对 SQL92 支持较好。
#不支持：
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);

03、SQL99语法实现多表查询

3.1、基本语法

使用JOIN…ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件

3.2 内连接的实现

#SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的。
#SQL99语法如何实现多表的查询。

#语法：
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

#SQL99语法实现内连接：INNER JOIN  ... ON  (INNER可以省略)
SELECT e.last_name,d.department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.department_id;

SELECT e.last_name,d.department_name,l.city
FROM employees e JOIN departments d
ON e.`department_id` = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`; #连接条件必须是两者都有的，否则查不出任何数据

3.3 外连接( OUTER JOIN)的实现

3.3.1 左外连接(LEFT OUTER JOIN)

#SQL99语法实现外连接：
#语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

#练习：查询所有的员工的last_name,department_name信息
#左外连接：LEFT OUTER JOIN ...ON (OUTER可以省略)
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

3.3.2 右外连接(RIGHT OUTER JOIN)

#语法：
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

#右外连接：RIGHT  OUTER JOIN ... ON (RIGHT可以省略)
SELECT last_name,department_name
FROM departments d RIGHT  OUTER JOIN employees e
ON e.`department_id` = d.`department_id`;

3.3.3 满外连接(FULL OUTER JOIN)

  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
#满外连接：MySQL不支持FULL OUTER JOIN ... ON ,Oracle支持此写法。
SELECT last_name,department_name
FROM departments d FULL  OUTER JOIN employees e
ON e.`department_id` = d.`department_id`;

04、UNION的使用

UNION和UNION ALL的使用

  • UNION：会执行去重的操作
  • UNION ALL :不会执行去重的操作
  • 结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

05、7种SQL JOINS的实现（重点！！！）

MySQL基础篇——多表查询

# 7种JOIN的实现：

#中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左中图：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

# 右中图：
SELECT employee_id,department_name
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_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
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_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下图：左中图  UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

06、SQL99语法新特性

6.1、自然连接

NATURAL JOIN : 它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

# SQL99语法的新特性1:自然连接

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`;

# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

6.2、USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段进行等值连接。但是只能配合JOIN一起使用。

# SQL99语法的新特性2:USING
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);

07、拓展

  • WHERE：适用于所有关联查询
  • ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

注意：
我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

#拓展：
SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

08、本章练习

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`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 d.`department_id` = 90;


DESC departments;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name ,e.`commission_pct`, d.department_name , d.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; #也应该是35条记录


SELECT *
FROM employees
WHERE commission_pct IS NOT NULL; #35条记录


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 

SELECT e.last_name , e.job_id , e.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 e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e,departments d ,locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

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


DESC departments;

DESC locations;

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	king	100

SELECT emp.last_name "employees",emp.employee_id "Emp#",mgr.last_name "manager", mgr.employee_id "Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工

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

#本题也可以使用子查询：暂时不讲

# 8. 查询哪个城市没有部门 

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;

SELECT department_id
FROM departments
WHERE department_id IN (1000,1100,1200,1300,1600);


# 9. 查询部门名为 Sales 或 IT 的员工信息

SELECT e.employee_id,e.last_name,e.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales','IT');

以上是MySQL基础篇的多表查询，本章的知识内容来源于尚硅谷MySQL基础，按照视频教学顺序总结出来的。文章总结的若有不足，欢迎大家斧正。谢谢！

上一篇：第五章：（3）数据访问 之 整合MyBatis


下一篇：05. 多表查询