当前位置: 首页 > news >正文

MySQL多表查询 子查询效率(DQL语句)

多表关系

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:

  • 一对多(多对一)
  • 多对多
  • 一对一

一对多

部门与员工的关系:一个部门对应多个员工,一个员工对应一个部门。

实现:在多的一方建立外键,指向一的一方的主键。
在这里插入图片描述

多对多

学生与课程的关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择。

实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o8qNfbIE-1680060245944)(images/3Ic6T4TA9OAUHutTbsbZc867vSxPBaO-lutoNxZlMIw.png)]

一对一

用户与用户详情的关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率。

实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)。
在这里插入图片描述

笛卡尔积

笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况。
在这里插入图片描述

而在多表查询中,需要消除无效的笛卡尔积,只保留两张表关联部分的数据。
在这里插入图片描述

-- 设置过滤条件 Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- 查询员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

多表查询分类

  • 内连接:相当于查询A、B交集部分数据

  • 外连接:

    • 左外连接:查询左表所有数据,以及两张表交集部分数据
    • 右外连接:查询右表所有数据,以及两张表交集部分数据
  • 自连接:当前表与自身的连接查询,自连接必须使用表别名
    在这里插入图片描述

  • 子查询

表连接

内连接

内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)。

在这里插入图片描述

内连接的语法分为两种:隐式内连接、显式内连接。

  • select 字段名 from 左表, 右表 where 条件隐式内连接,看不到 join 关键字,条件使用 where 指定。
  • select 字段名 from 左表 [inner] join 右表 where 条件显式内连接,可以省略 inner。

总结:

  • 内连接的数据表不一定必须有同名字段,只要字段之间符合逻辑关系就可以。
  • 相同的数据表也可以做表连接。
  • 结果集也可以作为一张“表”来跟其他表连接。
-- 隐式内连接
select * from emp,dept where emp.`dept_id` = dept.`id`;-- 确定表连接条件,员工表.dept_id = 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.`dept_id` = d.`id`;-- 查询与SCOTT相同部门的员工都有谁
select e2.ename
from t_emp e1 join t_emp e2 on e1.deptno = e2.deptno 
where e1.ename = "SCOTT" and e2.ename != "SCOTT"-- 查询月薪超过公司平均月薪的员工信息
select e.*
from t_emp e 
join (select avg(sal) avg from t_emp) t 
on e.sal >= t.avg

外连接

外连接与内连接的区别在于,除了符合条件的记录之外,结果集中还会保留不符合条件的记录。左外连接就是保留左表所有的记录,与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录,就用NULL与左表连接。右外连接也是如此。

内连接只保留符合条件的记录,所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是外连接里,条件写在WHERE子句里,不合符条件的记录是会被过滤掉的,而不是保留下来。

  • select 字段名 from 左表 left [outer] join 右表 on 条件左外连接,outer 可以省略,用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL。

左外连接相当于查询表1(左表)的所有数据,也包含表1和表2交集部分的数据。

  • select 字段名 from 左表 right [outer] join 右表 on 条件右外连接,outer 可以省略,用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示 NULL

右外连接相当于查询表2(右表)的所有数据,也包含表1和表2交集部分的数据。

-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;

自连接

自连接查询就是把一张表连接查询多次。可以是内连接查询,也可以是外连接查询。

  • select 字段列表 from 表A 别名A join 表A 别名B on 条件 ... ;:自连接查询

在自连接查询中,必须要为表起别名,要不然不清楚所指定的条件、返回的字段,到底是哪一张表的字段。

-- 查询员工 及其 所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid = b.id;--  查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来表结构: emp a , emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询

union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。

  • (查询语句) UNION (查询语句) UNION (查询语句) ...:UNION关键字可以将多个查询语句的结果集进行合并
  • 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
  • 如果多条查询语句查询出来的结果,字段数量不一致,会报错。
-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来
-- 不去重
select * from emp where salary < 5000 
union all 
select * from emp where age > 50;

在这里插入图片描述

-- 去重
select * from emp where salary < 5000 
union 
select * from emp where age > 50;

在这里插入图片描述

子查询

子查询可以写在三个地方:WHERE子句、FROM子句、SELECT子句,但是只有FROM子句子查询是最可取的。

根据子查询结果不同,分为:

  • 标量子查询(子查询结果为单个值)
  • 列子查询(子查询结果为一列)
  • 行子查询(子查询结果为一行)
  • 表子查询(子查询结果为多行多列)

查询语句执行的时候要多次的依赖于子查询的结果,这类子查询被称作相关子查询,WHERE子查询和SELECT子查询都属于相关子查询。因为相关子查询要反复多次执行,所以应该避免使用。

EXISTS关键字

EXISTS关键字是把原来在子查询之外的条件判断,写到了子查询的里面。

  • select …… from 表名 where [not] exists ( 子查询 );

标量子查询(结果是一个值)

子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、= 等。

  • select 查询字段 from 表 where 字段 = (子查询);
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);

列子查询(结果是多行单列)

子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。

常用的操作符:in、not in、any、some、all

操作符描述
in在指定的集合范围之内,多选一
not in不在指定的集合范围之内
any子查询返回列表中,有任意一个满足即可
some与ANY等同,使用SOME的地方都可以使用ANY
all子查询返回列表的所有值都必须满足
  • select 查询字段 from 表 where 字段 in (子查询);
-- 先查询大于 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);-- 先查询开发部与财务部的 id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));-- 查询比 财务部 所有人工资都高的员工信息
-- 1.查询所有 财务部 人员工资
select salary from emp where dept_id = (select id from dept where name = '财务部');
-- 2.比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );-- 查询比研发部其中任意一人工资高的员工信息
-- 1.查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- 2.比研发部其中任意一人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询(结果是一行多列)

子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。

常用的操作符:= 、<> 、IN 、NOT IN

-- 查询与 "张无忌" 的薪资及直属领导相同的员工信息
-- 1. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
-- 2. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询(结果是多行多列)

子查询返回的结果是多行多列,这种子查询称为表子查询。子查询结果只要是多列,一般在 from 后面作为表。

  • select 查询字段 from (子查询) 表别名 where 条件;

子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段。

-- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
-- 1.  查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
-- 2. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- 1. 入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- 2. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

子查询效率问题

WHERE子查询

这种子查询最简单,最容易理解,但是却是效率很低的子查询。
在这里插入图片描述

用表连接替代WHERE子查询:

表连接的优点是子查询只执行一次,查询效率特别高
在这里插入图片描述

FROM子查询

这种子查询只会执行一次,所以查询效率很高。
在这里插入图片描述

SELECT子查询

这种子查询每输出一条记录的时候都要执行一次,查询效率很低。
在这里插入图片描述

相关文章:

MySQL多表查询 子查询效率(DQL语句)

多表关系 项目开发中&#xff0c;在进行数据库表结构设计时&#xff0c;会根据业务需求及业务模块之间的关系&#xff0c;分析并设计表结构&#xff0c;由于业务之间相互关联&#xff0c;所以各个表结构之间也存在着各种联系&#xff0c;基本上分为三种&#xff1a; 一对多(多…...

Linux中 ps命令详解

一、基础概念 指令&#xff1a; ps 作用&#xff1a;查看系统进程&#xff0c;比如正在运行的进程有哪些&#xff0c;什么时候开始运行的&#xff0c;哪个用户运行的&#xff0c;占用了多少资源。 参数&#xff1a; -e 显示所有进程-f 显示所有字段&#xff08;UID&…...

【Python语言基础】——Python 关键字

Python语言基础——Python 关键字 文章目录Python语言基础——Python 关键字一、Python 关键字一、Python 关键字 Python 有一组关键字&#xff0c;这些关键字是保留字&#xff0c;不能用作变量名、函数名或任何其他标识符&#xff1a; 关键字 描述 and 逻辑运算符。 as 创建别…...

Java SE 基础(8)关键字和保留字

关键字 定义&#xff1a;被Java 语言赋予了特殊含义&#xff0c;用做专门用途的字符串&#xff08;单词&#xff09; 特点&#xff1a; 关键字中所有字母都为小写 用于定义数据类型的关键字 class、interface、 enum 、byte 、short、 int 、long、 float、 double、 char 、…...

Thinkphp 6.0响应输出和重定向

本节课我们来学习一下响应操作&#xff0c;响应输出和重定向。 一&#xff0e;响应操作 1. 响应输出&#xff0c;有好几种&#xff1a;包括 return、json()和 view()等等&#xff1b; 2. 默认输出方式是以 html 格式输出&#xff0c;如果你发起 json 请求&#xff0c;则输出 js…...

Centos html 中文 显示为乱码

0 &#xff1a; CentOS发布静态网页 之 httpd开启 https://blog.csdn.net/weixin_39689870/article/details/118146160 #yum install -y httpd #systemctl start httpd.service/etc/httpd/conf&#xff1a;该目录存放Apache服务器的配置文件 /var/www/html&#xff1a;该目录是…...

Helm学习笔记

文章目录概念定义helm组件helm的工作流程helm安装helm仓库helm部署应用helm应用的更新或回退或卸载概念 定义 学习helm首先得了解helm是什么&#xff0c;我们先来看一下helm的定义&#xff1a;helm是将kubernetes的各种资源对象打包&#xff0c;类似于Linux中的yum工具&#…...

深入学习JavaScript系列(二)——作用域和作用域链

本篇为第二篇&#xff0c;本系列文章会在后续学习后持续更新。 第一篇&#xff1a;#深入学习JavaScript系列&#xff08;一&#xff09;—— ES6中的JS执行上下文 第二篇&#xff1a;# 深入学习JavaScript系列&#xff08;二&#xff09;——作用域和作用域链 第三篇&#x…...

【计算机视觉 | 目标检测】DETR风格的目标检测框架解读

文章目录一、前言二、理解2.1 DETR的理解2.2 DETR的细致理解2.2.1 Backbone2.2.2 Transformer encoder2.2.3 Transformer decoder2.2.4 Prediction feed-forward networks (FFNs)2.2.5 Auxiliary decoding losses2.3 更具体的结构2.4 编码器的原理和作用2.5 解码器的原理和作用…...

【LeetCode】剑指 Offer 41. 数据流中的中位数 p214 -- Java Version

题目链接&#xff1a;https://leetcode.cn/problems/shu-ju-liu-zhong-de-zhong-wei-shu-lcof 1. 题目介绍&#xff08;41. 数据流中的中位数&#xff09; 如何得到一个数据流中的中位数&#xff1f;如果从数据流中读出奇数个数值&#xff0c;那么中位数就是所有数值排序之后位…...

CSS3 知识总结

1&#xff0c;什么是CSS 用于定义网页的样式&#xff0c;包括不同设备和屏幕尺寸的设计、布局和显示变化。 2&#xff0c;CSS的作用优点 CSS 描述 HTML 元素如何在屏幕、纸张或其他媒体上显示 CSS 节省了大量工作。它可以一次控制多个网页的布局 3&#xff0c;css构成 CSS 规…...

回溯算法37:解数独

主要是我自己刷题的一些记录过程。如果有错可以指出哦&#xff0c;大家一起进步。 转载代码随想录 原文链接&#xff1a; 代码随想录 leetcode链接&#xff1a;37. 解数独 题目&#xff1a; 编写一个程序&#xff0c;通过填充空格来解决数独问题。 数独的解法需 遵循如下规则…...

【蓝桥杯-筑基篇】动态规划

&#x1f353;系列专栏:蓝桥杯 &#x1f349;个人主页:个人主页 目录 1.最大连续子段和 2.LCS 最大公共子序列 3.LIS 最长上升子序列 4.数塔 5.最大子矩阵和 6.背包问题 ①01背包问题 ②完全背包 1.最大连续子段和 这段代码是一个求最大子数组和的算法&#xff0c;使用…...

Unity利用Photon PUN2框架快速实现多人在线游戏实例分享

简介 Photon 是一个泛用性的 ScoketServer 套装软件,可用于多人在线游戏、聊天室、大厅游戏,并同时支持 Windows、Unity3D、iOS、Android、Flash 等平台。Photon 包含两个部分,一部分是 Socket 服务器,另一部分是其针对各个平台编写的 SDK,Unity3D 平台对应的 SDK 为 Pho…...

ChatGPT直出1.5w字论文查重率才30% - 基于物联网技术的智能家居控制系统设计与实现

文章目录ChatGPT直出1.5w字论文查重率才30% - 基于物联网技术的智能家居控制系统设计与实现一、绪论1.1 研究背景与意义1.2 国内外研究现状分析1.3 研究内容与目标1.4 研究方向和思路二、物联网技术与智能家居概述2.1 物联网技术原理与应用2.2 智能家居的概念与发展历程2.3 智能…...

特斯拉的操作系统是用什么语言编写的?

总目录链接>> AutoSAR入门和实战系列总目录 文章目录特斯拉车辆操作系统特斯拉GitHub中使用的语言Ruby和GoPythonSwift 和 Objective CQt我们知道操作系统至少需要一些非常低级的代码&#xff0c;这些代码在系统首次启动时运行&#xff0c;必须使用接近硬件的语言编写。…...

C++学习8-C++提高编程

文章目录前言一、模板1.1 模板的概念1.2 函数模板1.2.1 函数模板语法1.2.2 函数模板注意事项1.2.3 函数模板案例复习&#xff1a;计算数组长度1.2.4 普通函数与函数模板的区别1.2.5 普通函数与函数模板的调用规则1.2.6 模板的局限性1.3 类模板1.3.1 类模板语法1.3.2 类模板与函…...

ubuntu安装git server

一安装 要在Ubuntu上安装Git服务器,需要按照以下步骤进行操作: 安装Git: sudo apt-get update sudo apt-get install git 创建一个Git用户和一个Git仓库目录: sudo adduser git sudo mkdir /home/git/repo.git sudo chown git:git /home/git/repo.git 初始化Git仓库: c…...

物流云数据分析平台

物流云数据分析服务平台 http://project.webcats.cn/bx/36569/2455/index.html 本次系统模拟的是湖南省数据&#xff0c;解释权归杭氏集团所有&#xff01; 1、系统简介: 物流大数据集成展示系统旨在通过大屏幕全面显示指定地区的物流运营车辆、物流公司和货主的相关信息和…...

配置OBS存储功能、新搭建obs

通过应用开发环境与OBS&#xff08;Object-based Storage Service&#xff09;对接&#xff0c;实现对象或者Widget资产存储功能。 背景信息 对象存储服务&#xff08;Object-based Storage Service&#xff0c;OBS&#xff09;是一个基于对象的海量存储服务&#xff0c;为客…...

在软件开发中正确使用MySQL日期时间类型的深度解析

在日常软件开发场景中&#xff0c;时间信息的存储是底层且核心的需求。从金融交易的精确记账时间、用户操作的行为日志&#xff0c;到供应链系统的物流节点时间戳&#xff0c;时间数据的准确性直接决定业务逻辑的可靠性。MySQL作为主流关系型数据库&#xff0c;其日期时间类型的…...

突破不可导策略的训练难题:零阶优化与强化学习的深度嵌合

强化学习&#xff08;Reinforcement Learning, RL&#xff09;是工业领域智能控制的重要方法。它的基本原理是将最优控制问题建模为马尔可夫决策过程&#xff0c;然后使用强化学习的Actor-Critic机制&#xff08;中文译作“知行互动”机制&#xff09;&#xff0c;逐步迭代求解…...

基于距离变化能量开销动态调整的WSN低功耗拓扑控制开销算法matlab仿真

目录 1.程序功能描述 2.测试软件版本以及运行结果展示 3.核心程序 4.算法仿真参数 5.算法理论概述 6.参考文献 7.完整程序 1.程序功能描述 通过动态调整节点通信的能量开销&#xff0c;平衡网络负载&#xff0c;延长WSN生命周期。具体通过建立基于距离的能量消耗模型&am…...

三维GIS开发cesium智慧地铁教程(5)Cesium相机控制

一、环境搭建 <script src"../cesium1.99/Build/Cesium/Cesium.js"></script> <link rel"stylesheet" href"../cesium1.99/Build/Cesium/Widgets/widgets.css"> 关键配置点&#xff1a; 路径验证&#xff1a;确保相对路径.…...

安宝特方案丨XRSOP人员作业标准化管理平台:AR智慧点检验收套件

在选煤厂、化工厂、钢铁厂等过程生产型企业&#xff0c;其生产设备的运行效率和非计划停机对工业制造效益有较大影响。 随着企业自动化和智能化建设的推进&#xff0c;需提前预防假检、错检、漏检&#xff0c;推动智慧生产运维系统数据的流动和现场赋能应用。同时&#xff0c;…...

LeetCode - 394. 字符串解码

题目 394. 字符串解码 - 力扣&#xff08;LeetCode&#xff09; 思路 使用两个栈&#xff1a;一个存储重复次数&#xff0c;一个存储字符串 遍历输入字符串&#xff1a; 数字处理&#xff1a;遇到数字时&#xff0c;累积计算重复次数左括号处理&#xff1a;保存当前状态&a…...

1688商品列表API与其他数据源的对接思路

将1688商品列表API与其他数据源对接时&#xff0c;需结合业务场景设计数据流转链路&#xff0c;重点关注数据格式兼容性、接口调用频率控制及数据一致性维护。以下是具体对接思路及关键技术点&#xff1a; 一、核心对接场景与目标 商品数据同步 场景&#xff1a;将1688商品信息…...

Auto-Coder使用GPT-4o完成:在用TabPFN这个模型构建一个预测未来3天涨跌的分类任务

通过akshare库&#xff0c;获取股票数据&#xff0c;并生成TabPFN这个模型 可以识别、处理的格式&#xff0c;写一个完整的预处理示例&#xff0c;并构建一个预测未来 3 天股价涨跌的分类任务 用TabPFN这个模型构建一个预测未来 3 天股价涨跌的分类任务&#xff0c;进行预测并输…...

[10-3]软件I2C读写MPU6050 江协科技学习笔记(16个知识点)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16...

前端开发面试题总结-JavaScript篇(一)

文章目录 JavaScript高频问答一、作用域与闭包1.什么是闭包&#xff08;Closure&#xff09;&#xff1f;闭包有什么应用场景和潜在问题&#xff1f;2.解释 JavaScript 的作用域链&#xff08;Scope Chain&#xff09; 二、原型与继承3.原型链是什么&#xff1f;如何实现继承&a…...