程序员经验分享-hwhale

多表的查询

2023-05-16

目录

一、为什么需要多表查询？

🐟查询一个员工名为“Abel”的人在哪个城市工作？

二、如何实现多表查询

1.出现笛卡尔积（交叉连接）的错误

①错误的实现方式

②错误的原因

2.多表查询的正确方式 —— 需要有连接条件

①两个表的连接条件

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

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

🐟注意

三、多表查询的分类

1.等值连接&非等值连接

​⚪非等值连接 

2.自连接&非自连接

#查询员工id，员工姓名及其管理者的id和姓名 —— 自连接

3.内连接&外连接

①内连接

②外连接

⚪SQL92语法实现外连接 ：使用 +

⚪SQL99语法实现多表的查询

Ⅰ.SQL99语法实现内连接

Ⅱ.SQL99语法实现外连接 

左外连接：

 右外连接：

满外连接：

Ⅲ.使用SQL99实现7种JOIN操作

·图③的实现 ：内连接

·图①的实现 ：左外连接

·图②的实现 ：右外连接

·图④的实现

·图⑤的实现

·图⑥的实现：满外连接

·图⑦的实现

Ⅳ.补充：UNION和UNION ALL的使用

1.合并查询结果

2.UNION 操作符

3..UNION ALL操作符 

四、SQL99语法的新特性

1.自然连接

2.USING连接

⚪表连接的约束条件可以有三种方式：WHERE, ON, USING

五、相关的表

1.employees表

2.departments表

​3.locations表

一、为什么需要多表查询？

🐟查询一个员工名为“Abel”的人在哪个城市工作？

1.先查询出他的全部信息

SELECT *
FROM employees
WHERE last_name = 'Abel';

2.得知他在80号部门时，查询80号部门的信息

SELECT *
FROM departments
WHERE  department_id = 80;

3.由结果可知城市id为2055

SELECT *
FROM locations
WHERE location_id = 2500;

二、如何实现多表查询

1.出现笛卡尔积（交叉连接）的错误

笛卡尔乘积是一个数学运算。

假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能 组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。

组合的个数即为两个集合中元素个数的乘积数

①错误的实现方式

每个员工都与每个部门匹配了一遍

SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;

②错误的原因

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接

2.多表查询的正确方式 —— 需要有连接条件

①两个表的连接条件

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106条 (不包含NULL)

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

SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106条 (不包含NULL)

department_id在employees和departments表中都存在，未指明在哪个表中时：

SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106条 (不包含NULL)

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

SELECT employees.employee_id,departments.department_name,department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106条 (不包含NULL)

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

SELECT emp.employee_id,dept.department_name,dept.department_id
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;  #106条 (不包含NULL)

🐟注意

如果给表起了别名，一旦在SELECT或WHERE中使用表的别名的话，则必须使用表的别名，而不能再使用表的原名

三、多表查询的分类

1.等值连接&非等值连接

非等值连接的例子

SELECT *
FROM job_grades;

⚪非等值连接 

SELECT last_name,salary,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.自连接&非自连接

#查询员工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;

3.内连接&外连接

①内连接

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

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;  #106条 (不包含NULL)

②外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

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

如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

查询所有的员工的 employee_id,department_name信息

⚪SQL92语法实现外连接 ：使用 +

MySQL不支持SQL92语法中外连接的写法

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);

⚪SQL99语法实现多表的查询

使用JOIN ... ON 的方式实现多表的查询。这种方式也能解决外连接的问题，且MySQL支持

Ⅰ.SQL99语法实现内连接

SELECT employee_id,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语法实现外连接 

左外连接：

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

 右外连接：

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

满外连接：

MySQL不支持FULL OUTER JOIN

SELECT employee_id,department_name,city
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

Ⅲ.使用SQL99实现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;

Ⅳ.补充：UNION和UNION ALL的使用

1.合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。 

2.UNION 操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录。 

 

3..UNION ALL操作符 

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

四、SQL99语法的新特性

1.自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行等值 连接 。

在 SQL99 中你可以写成： 

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

2.USING连接

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

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下 面的 SQL 查询结果是相同的：

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

⚪表连接的约束条件可以有三种方式：WHERE, ON, USING

WHERE：适用于所有关联查询

ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起 写，但分开写可读性更好。

USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字 段值相等 

五、相关的表

1.employees表

2.departments表

3.locations表

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

多表的查询 的相关文章

  • 最详细的手工LAMP环境搭建

    环境 xff1a 阿里云服务器ECS xff0c Alibaba Cloud Linux 3 2104 LTS 64位 xff0c 2核 vCPU 2 GiB LAMP 是搭建Web应用时最常用的环境 xff0c LAMP 分别表示 Lin

  • python 语音播报 简单入门

    coding utf 8 import pyttsx3 import time 初始化 pt 61 pyttsx3 init 说什么 pt say 34 你好 xff0c dbirder 34 开始说吧 pt runAndWait time

  • 【optimizer详解】

    optimizer 定义 optimizer就是在深度学习反向传播过程中 xff0c 指引损失函数 xff08 目标函数 xff09 的各个参数往正确的方向更新合适的大小 xff0c 使得更新后的各个参数让损失函数 xff08 目标函数 x

  • docker网络配置

    开放容器端口 执行docker run的时候有个 p选项 xff0c 可以将容器中的应用端口映射到宿主机中 xff0c 从而实现让外部主机可以通过访问宿主机的某端口来访问容器内应用的目的 p选项能够使用多次 xff0c 其所能够暴露的端口必

  • Ubuntu使用SSH工具默认用root用户连接；解决SSH不能用root用户连接。

    ROOT是什么意思 xff1f Root xff0c 也称为根用户 xff0c 是Unix 如 Solaris AIX BSD xff09 和类UNIX系统 如 Linux QNX 等 xff0c 及Android和iOS移动设备系统中的唯

  • LeetCode55. 跳跃游戏(贪心)

    力扣 解题思路 xff1a 1 设想一下 xff0c 对于数组中的任意一个位置 y xff0c 我们如何判断它是否可以到达 xff1f 根据题目的描述 xff0c 只要存在一个位置 x xff0c 它本身 可以到达 xff0c 并且它跳跃的

  • LeetCode300. 最长递增子序列（动态规划 / 贪心）

    力扣 解题思路 xff1a 1 动态规划 状态定义 xff1a dp i 的值代表 nums 以 nums i 结尾的最长子序列长度 转移方程 xff1a 设 j 0 i j 0 i xff0c 考虑每轮计算新 dp i 时 xff0c 遍

  • LeetCode299. 猜数字游戏

    力扣 解题思路 1 使用一个vector flag 记录哪个位置的下的字符相同 xff0c 使用一个Hash map记录secret中剩余字符与次数的映射 2 遍历guess 找到guess中的字符与 secret 相同但是在不同位置的字符

  • 八大排序 - (详解)

    目录 一 直接插入排序 1 思想 2 实现 3 特性总结 二 希尔排序 1 思想 2 实现 3 特性总结 三 选择排序 1 思想 2 实现 3 特性总结 四 堆排序 1 思想 2 实现 3 特性分析 五 冒泡排序 1 思想 2 实现 3 特

  • 牛客 - 另类加法

    另类加法 解题思路 1 二进制位异或运算相当于对应位相加 xff0c 不考虑进位 比如 xff1a 1 1 61 0 gt 1 43 1 61 0 当前位值为0 xff0c 进一位 1 0 61 1 gt 1 43 0 61 1 当前位值为

  • Jmeter添加MD5方法插件

    1 xff1a 下载 https jmeter plugins org install Install 2 xff1a jmeter plugins manager 1 3 jar放到 apache jmeter 5 0 lib ext目录

  • Linux常用指令(详解)

    目录 1 ls指令 2 pwd 3 clear 4 whoami 5 cd 6 tree 7 mkdir 8 touch 9 rmdir 10 rm 11 man 12 cp 13 mv 14 cat 15 more 16 less 17

  • 进程间通信详解

    目录 一 进程间通信介绍 1 进程间通信的目的 2 进程间通信的本质 3 进程间通信分类 二 什么是管道 三 匿名管道 1 匿名管道只能用于具有亲缘关系的进程之间进行通信 xff0c 常用于父子 2 pipe函数 3 匿名管道的使用 4 管

  • 大厂笔试真题

    1 复数相乘 2 K个一组翻转链表 include lt iostream gt include lt vector gt include lt string gt using namespace std void Reverse vect

  • 文件系统概念

    1 文件逻辑结构 1 有结构文件和无结构文件 定长记录 可变长记录 2 顺序文件 3 索引文件 4 索引顺序文件 5 多级索引顺序文件 2 文件目录 1 文件控制块 2 目录结构 3 索引节点 3 文件的物理结构 1 文件块 xff0c 磁

  • Makefile

    1 基本规则 目标 依赖 目标 要生成的目标文件 tab 命令 依赖 目标文件由那些文件生成 命令 通过执行该命令由依赖文件生成目标 举例 add o add c gcc c add c o add c 1 其他规则 目标的时间必须晚于依赖

  • 计算机组成原理测试题

    随堂测试 1 单项选择题 第1题 主频为10MHZ xff0c 则时钟周期为 10ns 100ns xff08 答案 xff09 1000ns 第2题 冯 诺伊曼机工作方式的基本特点是 D 存储器按内容选择地址 C 堆栈操作 B 按地址访问

  • js练习题(3)

    1 序列 xff1a 1 xff0c 2 xff0c 3 xff0c 5 xff0c 8 xff0c 13 找出第20个数是多少 得出前20个数之和是多少 xff1f span class token keyword function sp

  • JAVA 两数求商

    题目描述 xff1a 给定两个整数 a 和 b xff0c 求它们的除法的商 a b xff0c 要求不得使用乘号 除号 以及求余符号 注意 xff1a 整数除法的结果应当截去 xff08 truncate xff09 其小数部分 xff0

  • 约瑟夫环总结

    约瑟夫环 N个人围成一圈 xff0c 从第一个人开始报数 xff0c 报到m的人出圈 xff0c 剩下的人继续从1开始报数 xff0c 报到m的人出圈 xff0c 如此往复 问题一 xff1a 所有人都出圈 xff0c 求出圈的人的编号顺序

随机推荐

  • Linux操作系统实验：生产者和消费者问题

    一 实验目的及要求 生产者消费者 问题是一个著名的同时性编程问题的集合 通过编写经典的 生产者消费者 问题的实验 xff0c 读者可以进一步熟悉 Linux 中多线程编程 xff0c 并且掌握用信号量处理线程间的同步互斥问题 二 实验仪器设

  • COCO数据集解析

    1 简介 官方网站 xff1a http cocodataset org 全称 xff1a Microsoft Common Objects in Context xff08 MS COCO xff09 支持任务 xff1a Detecti

  • U盘启动盘cmd制作

    U盘 移动硬盘启动盘cmd手工制作 插入 gt 61 8G的u盘 移动硬盘 1 win 43 r打开运行窗口 xff0c 输入cmd回车打开命令行 xff08 命令提示符不分大小写 xff09 2 在DOS命令行窗口中输入 diskpart

  • 系统调用的概念和作用

    一 什么是系统调用 xff0c 有何作用 1 概念 用户接口 命令接口 允许用户直接使用 程序接口 允许用户通过程序间接使用 xff1a 由一组系统调用组成 系统调用 系统调用 是操作系统提供给应用程序 xff08 程序员 编程人员 xff

  • linux系统下键盘按键的重新映射——xmodmap工具和xev工具

    虽然linux下小键盘还是不能用 xff0c 但是找到一篇好文章 linux系统下键盘按键的重新映射 xmodmap工具和xev工具 文章目录 1 xev工具 xff1a 2 xmodmap工具 大家会不会有时候 xff0c 感觉键盘上的某

  • Sorry, you have been blocked（Chatgpt登录被屏蔽）

    解决办法 xff1a 关闭浏览器 xff0c 重新打开 vpn换个地区 xff0c 换个美国的vpn

  • Linux查看ip地址出错，ens33不显示inet解决

    在我的私人博客中也有写 xff0c 大家去看看哦 Linux 查看 ip 地址出错 xff0c ens33 不显示 inet 解决 白都 baidu2001 top 问题 xff1a 在 CentOS7中输入 ip addr 时 xff0c

  • Java多线程（超详解）

    目录 1 线程简介 1 1 程序 1 2 进程 1 3 线程 1 4 多线程 1 5 普通方法调用和多线程 2 线程创建 2 1 继承Thread类 2 2 实现Runnable接口 2 3 实现Callable接口 xff08 了解 xf

  • 学习Java的路线

    JavaSE 18 20天 xff09 数据库 xff08 4天 xff09 前端 xff08 7天 xff09 Javaweb xff08 7天 xff09 SSM框架 xff08 9天 xff09 Linux xff08 7天 xff0

  • Java常用类的使用方法小结

    文章目录 前言一 常用类有哪些 xff1f 二 常用类使用方法1 Object类0 xff09 所用的对象1 xff09 getClass方法2 xff09 hasCode方法3 xff09 toString方法4 xff09 equals

  • Java I/O流

    文章目录 前言一 什么是流 xff1f 二 流的分类1 根据流向来分1 xff09 输入流2 xff09 输出流 2 根据单位来分1 xff09 字节流2 xff09 字符流 3 根据功能来分1 xff09 节点流2 xff09 过滤流 三

  • Java 网络编程

    文章目录 前言一 什么是计算机网络 xff1f 二 网络通信的两个重要因素1 通讯双方的地址1 xff09 ip2 xff09 端口号 2 规则 xff1a 网络协议 三 Ip地址1 InetAddress2 Ip的分类1 xff09 ip

  • ScrumMaster的教练职责

    ScrumMaster是Scrum团队的敏捷教练 Ken Rubin说 xff0c 类似于运动团队的教练 xff0c ScrumMaster观察团队使用Scrum的过程 xff0c 帮助团队提高工作绩效 教练不是顾问 xff0c 不提供解决

  • Java多线程

    文章目录 前言一 多线程1 进程 xff0c 线程 xff0c 多线程2 继承Thread类1 xff09 同时下载多个网图通过继承Thread类 3 实现Runnable接口1 xff09 下载多个网图通过实现Runnable接口2 xf

  • MySQL数据库随笔

    提示 xff1a 本文章仅供个人学习使用 xff0c 如有错误请及时私信通知 文章目录 前言一 MySQL的基本操作命令 xff08 1 xff09 连接数据库 xff08 2 xff09 修改用户密码 xff08 3 xff09 刷新权限

  • mybiatis学习笔记

    mybiatis学习笔记参考于尚硅谷mybatis文档 96 提示 xff1a 这如果有错误的地方 xff0c 请联系博主已更正 xff0c 文章目录 mybiatis学习笔记参考于尚硅谷mybatis文档前言一 Mybatits的特性二

  • KVM 介绍及安装

    KVM 安装 KVM KVM 简介 KVM 全称是 Kernel based Virtual Machine 是 Linux 下 x86 硬件平台上的全功能虚拟化解决方案 xff0c 包含一个可加载的内核模块 kvm ko 提供和虚拟化核心

  • 接口（interface）

    目录 一 简介 二 接口的使用 使用interface来定义 在Java中 xff0c 接口和类是两个并列的结构 如何定义接口 xff1a 定义接口中的成员 JDK7以及以前 xff1a 只能定义全局常量和抽象方法 JDK8中 xff1a

  • FileReader && FileWriter && FileInputStream && FileOutputStream

    目录 一 读入数据的基本操作 将day09下的hello txt文件内容读入程序中 xff0c 并输出到控制台 1 步骤 xff1a 2 完整代码 3 异常的处理 xff1a 为了保证流资源一定可以执行关闭操作 xff0c 需要使用try

  • 多表的查询

    目录 一 为什么需要多表查询 xff1f x1f41f 查询一个员工名为 Abel 的人在哪个城市工作 xff1f 二 如何实现多表查询 1 出现笛卡尔积 xff08 交叉连接 xff09 的错误 错误的实现方式 错误的原因 2 多表查询的

热门标签