MySQL-多表查询

server/2024/10/3 23:33:19/

多表查询

多表查询简介

笛卡尔乘积

  1. 笛卡尔乘积的形成，当:

    • 一个连接条件被遗漏时

    • 一个连接条件不正确时

    • 在第一个表中的所有行被连接到第二个表的所有行时

  2. 为了避免笛卡尔乘积的形成，在WHERE子句中应当总是包含正确的连接条件

笛卡尔乘积 ：

当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积(Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。你应该在 WHERE 子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。

多表查询分类

  • sql92标准：内连接(等值连接 、非等值连接 、 自连接)。
  • sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。

SQL92标准中的查询

等值连接

等值连接

为了确定一个雇员的部门名，需要比较 EMPLOYEES 表中的DEPARTMENT_ID 列与DEPARTMENTS 表中的 DEPARTMENT_ID列的值。在 EMPLOYEES 和DEPARTMENTS 表之间的关系是一个相等 (equijoin) 关系，即，两 个 表 中DEPARTMENT_ID 列的值必须相等。

等值连接特点：

  1. 多表等值连接的结果为多表的交集部分；
  2. n表连接，至少需要n-1个连接条件；
  3. 多表不分主次，没有顺序要求；
  4. 一般为表起别名，提高阅读性和性能；
  5. 可以搭配排序、分组、筛选….等子句使用；

注意：

等值连接也被称为简单连接 (simple joins) 或内连接 (innerjoins)。

等值连接的使用

mysql">select 
employees.EMPLOYEE_ID,employees.LAST_NAME,
employees.DEPARTMENT_ID,
departments.DEPARTMENT_ID,departments.LOCATION_ID
from employees,departments
where employees.DEPARTMENT_ID = departments.DEPARTMENT_ID;

在这里插入图片描述

  • SELECT 子句指定要返回的列名：

    − employee last name、employee number 和 departmentnumber，这些是EMPLOYEES 表中的列

    − department number、department name 和 location ID，这些是 DEPARTMENTS 表中的列

  • FROM 子句指定数据库必须访问的两个表：

    − EMPLOYEES 表

    − DEPARTMENTS 表

  • WHERE 子句指定表怎样被连接：

    EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列是两个表的同名列，它必须用表名做前缀以避免混淆。

增加搜索条件

使用AND操作符附加搜索条件

添加查询条件

除连接之外，可能还要求用 WHERE 子句在连接中限制一个或多个表中的行。

限制不能缺的列

限制不明确的列名

  • 需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID列可能来自 DEPARTMENTS 表，也可能来自 EMPLOYEES 表，这种情况下需要添加表前缀来执行查询。
  • 如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为MySQL服务器可以根据表前缀找到对应的列。
  • 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如 SELECT子句或 ORDERBY 子句。

使用表别名

表别名定义原则

  • 表别名不易过长，短一些更好。
  • 表别名应该是有意义的。
  • 表别名只对当前的 SELECT 语句有效。

多表连接

为了连接n个表，你最少需要n-1个连接条件。例如，为了连接3个表，最少需要两个连接

示例一：

查询雇员 King 所在的部门名称。

mysql">select 
d.department_name 
from employees e,departments d   
where e.EMPLOYEE_ID = d.department_id and e.last_name = 'King';

在这里插入图片描述

示例二：

显示每个雇员的 last name、departmentname 和 city。

mysql">SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

在这里插入图片描述

非等值连接

一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES表 和JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个表之间的关系是EMPLOYEES 表中的 SALARY 列必须是JOB_GRADES 表的 LOWEST_SALARY 和HIGHEST_SALARY 列之间的值。使用不同于等于 (=) 的操作符获得关系。

示例一：

创建 job_grades 表，包含 lowest_sal ，highest_sal ，grade_level。

mysql">create table job_grades(
lowest_sal int,
highest_sal int,
grade_level varchar(30));

在这里插入图片描述

示例二：

插入数据

1000 2999 A
2000 4999 B
5000 7999 C
8000 12000 D

mysql">insert into job_grades values(1000,2999,'A');
insert into job_grades values(2000,4999,'B');
insert into job_grades values(5000,7999,'C');
insert into job_grades values(8000,12000,'D');

在这里插入图片描述

示例三：

查询所有雇员的薪水级别。

mysql">select e.last_name,j.grade_level 
from employees e ,job_grades j 
where e.salary
between j.lowest_sal and j.highest_sal;

在这里插入图片描述

自连接

连接一个表到它自己。有时需要连接一个表到它自己。为了找到每个雇员的经理的名字，则需要连接EMPLOYEES 表到它自己，或执行一个自连接。

示例一：

查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。

mysql">select
worker.LAST_NAME W,manager.LAST_NAME M
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID;

在这里插入图片描述

示例二：

查询Fox的经理是谁？显示他的名字。

mysql">select
worker.LAST_NAME,manager.LAST_NAME
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID
AND worker.LAST_NAME = 'Fox';

在这里插入图片描述

SQL99标准中的查询

MySQL5.7 支持部分的SQL99 标准。

SQL99中的交叉连接(CROSS JOIN)

  • CROSS JOIN 子句导致两个表的交叉乘积
  • 该连接和两个表之间的笛卡尔乘积是一样的

示例：

使用交叉连接查询 employees 表与 departments 表。

mysql">select * 
from employees 
cross join departments;

在这里插入图片描述

SQL99中的自然连接(NATURAL JOIN)

  • NATURAL JOIN 子句基于两个表之间有相同名字的所有列
  • 它从两个表中选择在所有的匹配列中有相等值的行
  • 如果有相同名字的列的数据类型不同，返回一个错误

自然连接

连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。

自然连接查询

示例一：

mysql">select 
DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID,CITY
from departments
NATURAL JOIN locations;

在这里插入图片描述

例子中，LOCATIONS 表被用 LOCATION_ID 列连接到DEPARTMENT表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们，连接条件的关系为and。

自然连接也可以被写为等值连接：

SELECT

d.department_id, d.department_name, d.location_id , l.city
FROM departments d , locations l
WHERE d.location_id = l.location_id;

示例二：

使用自然连接查询所有有部门的雇员的名字以及部门名称。

mysql">select e.last_name,d.department_name 
from employees e 
natural join departments d;

在这里插入图片描述

SQL99中的内连接(INNER JOIN)

语法：

  • SELECT 查询列表;
  • FROM 表1 别名;
  • INNER JOIN 连接表(INNER关键字可省略);
  • ON 连接条件;

用ON子句指定连接条件

示例：

查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

mysql">select
e.employee_id,e.salary,d.department_name 
from employees e 
inner JOIN departments d on
e.department_id = d.department_id 
where e.last_name = 'Fox';

在这里插入图片描述

外连接查询(OUTER JOIN)

  • 在SQL: 1999中，连接两个表，仅返回匹配的行的连接，称为内连接
  • 在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左(或右)表的连接，称为左(或右)外连接
  • 在两个表之间的连接，返回内连接的结果，同时还返回左和右连接,称为全外连接

孤儿数据(Orphan Data)

孤儿数据是指被连接的列的值为空的数据。

左外连接(LEFT OUTER JOIN)

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询也会取回 EMPLOYEES 表中所有的行。

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

mysql">select e.last_name,d.department_name 
from employees e 
LEFT OUTER JOIN departments d on 
e.DEPARTMENT_ID = d.department_id;

在这里插入图片描述

右外连接(RIGTH OUTER JOIN)

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询也会取回 DEPARTMENTS 表中所有的行。

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

mysql">select e.last_name,d.department_name  
from employees e 
RIGHT  OUTER JOIN departments d on 
e.DEPARTMENT_ID = d.department_id;

在这里插入图片描述

全外连接(FULL OUTER JOIN)

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

  • UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了DISTINCT。
  • UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

语法结构

mysql">(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名
ON 连接条件)
UNION
(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表
名 ON 连接条件)

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇 员。

mysql">(select e.last_name,d.department_name 
from employees e LEFT OUTER JOIN departments d  
on e.department_id = d.department_id)  
UNION  
(select e1.last_name,d1.department_name
from employees e1 RIGHT OUTER JOIN departments d1 
on d1.department_id = e1.department_id);

在这里插入图片描述


http://www.ppmy.cn/server/20897.html

相关文章

机器学习中的 SVM(支持向量机)和随机森林及其优缺点

机器学习中的 SVM(支持向量机)和随机森林及其优缺点

SVM（支持向量机）和随机森林是机器学习中常用的两种算法。 支持向量机是一种监督学习算法，主要用于二分类问题。其基本原理是通过在数据集中找到一个超平面，将两个不同的类别分隔开来。SVM的核心思想是尽可能将超平面与两个类别的…
阅读更多...
力扣HOT100 - 124. 二叉树中的最大路径和

力扣HOT100 - 124. 二叉树中的最大路径和

解题思路： class Solution {int max Integer.MIN_VALUE;public int maxPathSum(TreeNode root) {maxGain(root);return max;}public int maxGain(TreeNode node) {if (node null) return 0;int leftGain Math.max(maxGain(node.left), 0);int rightGain Math.ma…
阅读更多...
ETL中元数据处理的方式

ETL中元数据处理的方式

ETLCloud平台是用于处理从数据抽取、转换、加载到持续数据捕获等一系列数据管理活动。除此之外还支持微服务治理与快速开发，并且具备数据库监听功能，能够以不同的方式监听并同步源数据库表数据到目标系统，比如直接传输、通过ETL流程处理或者传…
阅读更多...
深入剖析Tomcat(四) 剖析Tomcat的默认连接器

深入剖析Tomcat(四) 剖析Tomcat的默认连接器

上篇文章已经实现了一个简易版的连接器，理解了那个连接器，再看Tomcat的默认连接器就会轻松很多，本章中的默认连接器指的是Tomcat4的默认连接器，尽管该连接器已经被弃用了，被另一个运行速度更快的连接器 Coyote 取代&am…
阅读更多...
深度学习-线性代数

深度学习-线性代数

目录 标量向量矩阵特殊矩阵特征向量和特征值 标量由只有一个元素的张量表示将向量视为标量值组成的列表通过张量的索引来访问任一元素访问张量的长度只有一个轴的张量，形状只有一个元素通过指定两个分量m和n来创建一个形状为mn的矩阵矩阵的转置对称矩阵的转置逻辑运…
阅读更多...
主题乐园私域精细化运营

主题乐园私域精细化运营

主题乐园私域精细化运营是指在细分用户群体的基础上，通过个性化、精准的运营方式，为用户提供定制化服务和体验。以下是一些常见的主题乐园私域精细化运营玩法： 会员制度和会员专属服务：建立完善的会员制度，为会员提供专…
阅读更多...
Linux 环境变量的增删改

Linux 环境变量的增删改

写在前面： 本文章旨在总结备份、方便以后查询，由于是个人总结，如有不对，欢迎指正；另外，内容大部分来自网络、书籍、和各类手册，如若侵权请告知，马上删帖致歉。 目录 shell 和 bash获…
阅读更多...
docker容器技术篇：容器集群管理实战mesos+zookeeper+marathon（一）

docker容器技术篇：容器集群管理实战mesos+zookeeper+marathon（一）

容器集群管理实战mesoszookeepermarathon（一） mesos概述 1.1 Mesos是什么 Apache Mesos 是一个基于多资源调度的集群管理软件，提供了有效的、跨分布式应用或框架的资源隔离和共享，可以运行 Hadoop、Spark以及docker等。 1.2 为…
阅读更多...
最新文章

Copyright @ 2022~2023