【MySQL多表查询】:让你的数据检索更高效

这篇具有很好参考价值的文章主要介绍了【MySQL多表查询】:让你的数据检索更高效。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

前言

欢迎来到小K的MySQL专栏,本节将为大家带来MySQL中多表查询相关知识的讲解


一、多表关系

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

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

✨✨一对多(多对一)

案例:部门与员工的关系

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

实现:在多的一方建立外键,关联另一方的主键

【MySQL多表查询】:让你的数据检索更高效

✨✨多对多

案例:学生与课程的关系

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

实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

【MySQL多表查询】:让你的数据检索更高效

✨✨一对一

案例:用户与用户详细的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率。
实现:在任意一方加入一个外键,关联另一方的主键,并且设置外键为唯一约束

【MySQL多表查询】:让你的数据检索更高效
【MySQL多表查询】:让你的数据检索更高效

二、多表查询

✨前面我们使用的查询,只是对单表进行查询,在具体的应用中,经常需要实现在一个查询语句中显示多张数据表的数据,这就是所谓的多表联合查询

在具体实现连接操作时,首先将两个或两个以上的表按照某个条件连接起来,然后再查询到所要求的数据记录

✨✨连接查询分为交叉连接,内连接,外连接查询三种方式。

1、交叉连接

✨交叉连接不带WHERE子句,它返回被连接的两个表所有数据行的笛卡尔积笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积

✨✨交叉连接语法:

SELECT <字段名> FROM <表1> CROSS JOIN <表2> 
SELECT <字段名> FROM <表1>, <表2> 

✨✨交叉连接练习:查询员工及员工所在部门信息

SELECT * FROM emp,dept; 
SELECT * FROM emp CROSS JOIN dept; 

结果有56条,员工有14人,部门有4个,但是这样就会产出很多无效记录,特别是两张表的数据量都很大,这时候就需要消除笛卡尔积的无效记录,我们需要使用关联字段

✨✨范例:利用等值条件来处理笛卡尔积

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

这样我们处理完之后就只有14条记录了,刚好一一对应

2、内连接

内连接又分为等值连接、非等值连接和自连接三种类型。其中,等值连接是指两个表中需要匹配的列具有相同的值;非等值连接是指两个表中需要匹配的列具有不同的值;自连接是指一个表与自身进行联接。在表关系的笛卡尔积数据记录中,保留表关系中所有匹配的数据记录,舍弃不匹配的数据记录。按匹配的条件可以分成等值连接和不等值连接。✨

✨✨有两种语法,显示的和隐式的,返回连接表中符合连接条件和查询条件的数据行(所谓的连接表就是数据库在做查询形成的中间表)

  • 隐式内连接

    SELECT * FROM 表1,表2 WHERE 条件;
    
  • 显示内连接(使用关键字INNER JOIN)

    SELECT * FROM 表1 [INNER] JOIN 表2 ON 条件; 
    

等值连接:

在连接条件中使用等于号(=)运算符比较被连接列的列值

  • 查询员工及部门信息

    #隐式连接
    SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
    #显示连接
    SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
    

    需要注意的是:如果指定了别名,原来的SQL表名就不可以使用了

  • 等值连接可以使用USING来自动关联两表中相同的列

    SELECT * FROM emp INNER JOIN dept USING(deptno);
    

    效果如下:

【MySQL多表查询】:让你的数据检索更高效

非等值连接:

在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>等

  • 查询员工工资级别

    #隐式连接
    SELECT * FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
    #显示连接
    SELECT * FROM emp e INNER JOIN salgrade s ON  e.sal BETWEEN s.losal AND s.hisal;
    

    效果如下:

【MySQL多表查询】:让你的数据检索更高效

自连接:

自连接就是指表与其自身进行连接

【MySQL多表查询】:让你的数据检索更高效

我们可以看上面的员工表,如果我们现在要查询每个员工对应的领导姓名,需要通过自连接

SELECT e.ename,e.mgr,me.ename 领导 FROM emp e,emp me WHERE e.mgr=me.empno;

【MySQL多表查询】:让你的数据检索更高效

3、外连接

✨在表关系的笛卡尔积中,不仅保留表关系中所有匹配的数据记录,而且还保留部分不匹配的记录。按照保留不匹配条件数据记录来源可以分为左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)。

语法:

SELECT * FROM 表1 LEFT|RIGHT|FULL [OUTER] JOIN 表2 ON 条件; 
  • 左外链接

    表关系的笛卡尔积中,出了选择相匹配的数据记录,还包含关联左边表中不匹配的数据记录。

    查询员工及对应的部门信息(没有部门的员工也显示出来,没有员工的部门不显示

    SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
    

    效果如下:

【MySQL多表查询】:让你的数据检索更高效

  • 右外连接

    在表关系的笛卡尔积中,出了选择相匹配的数据记录,还包含关联右边表中不匹配的数据记录。

    查询员工及对应的部门信息(没有部门的员工不显示,没有员工的部门显示

    SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
    

    效果如下:

【MySQL多表查询】:让你的数据检索更高效

  • 全连接:

    在表关系的笛卡尔积中,出了选择相匹配的数据记录,还包含关联左右两边表中不匹配的数据记录。

    查询员工及对应的部门信息(没有部门的员工显示,没有员工的部门显示

    SELECT *
    FROM emp  LEFT JOIN dept
    ON emp.deptno=dept.deptno
    UNION
    SELECT *
    FROM emp  RIGHT JOIN dept
    ON emp.deptno=dept.deptno
    WHERE emp.deptno IS NULL;
    

    这个看不懂没关系,因为MySQL不支持全连接,所以我们这里用了集合运算实现的,下面会讲~

    效果如下:

【MySQL多表查询】:让你的数据检索更高效

三、集合运算

✨MySQL支持并集运算,并集即两个集合的所有部分
【MySQL多表查询】:让你的数据检索更高效

  • UNION DISTINCT

    • UNION ALL 不会删除重复行

    • 相同的行在结果中只出现一次

      SELECT * FROM emp
      UNION
      SELECT * FROM emp WHERE deptno=10;
      
  • UNION ALL:

    • UNION 会删除重复行

    • 相同的行在结果中可能出现多次

      SELECT * FROM emp
      UNION ALL
      SELECT * FROM emp WHERE deptno=10;
      

✨✨要求:

  • 输入的查询不能包含ORDER BY字句,可以为整个集合运算结果选择性地增加一个ORDER BY字句
  • 两个查询必须包含相同的列数
  • 相应列必须具有兼容的数据类型。兼容的数据类型:优先级较低的数据类型必须能隐式转换为较高级的数据类型。比如输入的查询1的第一列为int类型,输入的查询2的第一列为float类型,则较低的数据类型int类型可以隐式地转换为较高级float类型。如果输入的查询1的第一列为char类型,输入的查询2的第一列为datetime类型,则会提示转换失败:从字符串转换日期或字符串转时间时,转换失败;
  • 集合运算结果中列名由输入的查询1决定,如果要为结果分配结果列,应该在输入的查询1中分配相应的别名
  • 集合运算时,对行进行时,集合运算认为两个NULL相等

四、七种JOINS实现

【MySQL多表查询】:让你的数据检索更高效

图一就是左外连接:

SELECT *
FROM emp  LEFT JOIN dept
ON emp.deptno=dept.deptno; 

图二为右外连接:

SELECT * 
FROM emp RIGHT JOIN dept
ON emp.deptno=dept.deptno;

图三为左外连接去掉交集部分:

SELECT *
FROM emp  LEFT JOIN dept
ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL;

图四为等值连接,只要交集:

SELECT *
FROM emp e,dept d
WHERE e.deptno=d.deptno;

图五为右外连接去掉交集部分:

SELECT *
FROM emp  RIGHT JOIN dept
ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL;

图六可以可以为1,5或2,3或4,7的并集:

SELECT *
FROM emp  LEFT JOIN dept
ON emp.deptno=dept.deptno
UNION
SELECT *
FROM emp  RIGHT JOIN dept
ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL;

图七为3,5的并集:

SELECT *
FROM emp  LEFT JOIN dept
ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL
UNION
SELECT *
FROM emp  RIGHT JOIN dept
ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL;

效果如下:

【MySQL多表查询】:让你的数据检索更高效
【MySQL多表查询】:让你的数据检索更高效
【MySQL多表查询】:让你的数据检索更高效

五、多表查询练习

题目:

✨✨查询出雇佣日期在1981年的所有员工的编号、姓名、雇佣日期、工作、领导姓名、雇佣月工资、雇佣年工资(基本工资+奖金),工资等级、部门编号、部门名称、部门位置,并且求这些员工的月基本工资在1500~3500之间,将最后的结果按照年工资的降序排列,如果年工资相等,则按照工作进行排序。

需求很多,逐步分析

  • 确定所需要的数据表
    • emp:编号、姓名、雇佣日期,工作、月工资、年薪
    • emp:领导姓名
    • dept:部门编号、名称、位置
    • salgrade:工资等级
  • 确定一致的关联字段
    • 员工和领导:e.mgr=e1.empno
    • 员工和部门:e.deptno=dept.deptno
    • 员工和工资等级:e.sal BETWEEN s.losal AND s.hisal

✨✨步骤一:查询出所有在1981年雇佣的雇员编号、姓名、御用日期、工作、月工资、年工资,并且月薪在1500~3500之间。只需要emp单张表即可。

SELECT e.empno,e.ename,e.hiredate,e.job,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪
FROM emp e
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN 1500 AND 3500;

✨✨步骤二:加入领导信息,使用自身关联。

SELECT e.empno,e.ename,e.hiredate,e.job,me.ename 领导,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪
FROM emp e,emp me
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN 1500 AND 3500
AND e.mgr=me.empno;

✨✨步骤三:加入工资等级和部门信息

SELECT e.empno,e.ename,e.hiredate,e.job,me.ename 领导,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,s.grade,d.deptno,d.dname,d.loc
FROM emp e,emp me,salgrade s,dept d
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN 1500 AND 3500
AND e.mgr=me.empno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.deptno=d.deptno;

✨✨步骤四:排序

SELECT e.empno,e.ename,e.hiredate,e.job,me.ename 领导,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,s.grade,d.deptno,d.dname,d.loc
FROM emp e,emp me,salgrade s,dept d
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN 1500 AND 3500
AND e.mgr=me.empno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.deptno=d.deptno
ORDER BY 年薪 DESC,job ASC;

效果如下:

【MySQL多表查询】:让你的数据检索更高效

六、总结

MySQL多表查询的优点有很多,例如可以减少数据冗余,提高查询效率,方便数据分析等等。文章来源地址https://www.toymoban.com/news/detail-488596.html

到了这里,关于【MySQL多表查询】:让你的数据检索更高效的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 深入学习MYSQL-数据检索

    前言 由于大部分基础知识都已经学过了,这里只把觉得应该记录一下的知识点做个笔记。然后以下笔记和sql均来自书籍(MYSQL必会知识),会根据看的其它书记继续调整和优化笔记。 LIMIT 注:这个平时的SQL查询没有什么区别,我主要展示一下在命令行里面怎么展示结果。 总共8条

    2024年02月05日
    浏览(36)
  • MySQL索引:让你的数据库查询快到起飞!

    💕世界上最美好的东西之一,就是你每天都有机会开始全新的一天。💕 🐼作者:不能再留遗憾了🐼 🎆专栏:MySQL学习🎆 🚗本文章主要内容:详细介绍如何查看、创建和删除MySQL索引,以及MySQL索引的底层原理:B+树。🚗 各位朋友们,大家好!前面我们已经介绍了MySQL的库

    2024年02月09日
    浏览(48)
  • MySQL正则表达式检索数据

    目录 一、使用正则表达式进行基本字符匹配 1.使用regexp 2.使用正则表达式  .  二、进行OR匹配 1.为搜索两个串之一,使用   |   2.匹配几个字符之一[] 3.匹配范围  4.匹配特殊字符 过滤数据允许使用 匹配、比较、通配符 操作来寻找数据,但是随着过滤条件的复杂性增

    2024年02月14日
    浏览(37)
  • 【MySQL】一文带你了解检索数据

    🎬 博客主页:博主链接 🎥 本文由 M malloc 原创,首发于 CSDN🙉 🎄 学习专栏推荐:LeetCode刷题集! 🏅 欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正! 📆 未来很长,值得我们全力奔赴更美好的生活✨ ------------------❤️分割线❤️------------------------- —————————

    2024年02月09日
    浏览(50)
  • 使用FlinkCDC从mysql同步数据到ES,并实现数据检索

    随着公司的业务量越来越大,查询需求越来越复杂,mysql已经不支持变化多样的复杂查询了。 于是,使用cdc捕获MySQL的数据变化,同步到ES中,进行数据的检索。 springboot集成elasticSearch(附带工具类)

    2024年04月13日
    浏览(33)
  • 图数据库Neo4J 中文分词查询及全文检索(建立全文索引)

    Neo4j的全文索引是基于Lucene实现的,但是Lucene默认情况下只提供了基于英文的分词器,下篇文章我们在讨论中文分词器(IK)的引用,本篇默认基于英文分词来做。我们前边文章就举例说明过,比如我要搜索苹果公司?首先我们要做的第一步在各个词条上创建全文索引,第二步

    2024年02月03日
    浏览(42)
  • Tapdata Cloud 场景通关系列:将数据导入阿里云 Tablestore,获得毫秒级在线查询和检索能力

    【前言】作为中国的 “Fivetran/Airbyte”, Tapdata Cloud 自去年发布云版公测以来,吸引了近万名用户的注册使用。应社区用户上生产系统的要求,Tapdata Cloud 3.0 将正式推出商业版服务,提供对生产系统的 SLA 支撑。Tapdata 目前专注在实时数据同步和集成领域,核心场景包括以下几大

    2024年01月16日
    浏览(40)
  • 信息检索与数据挖掘 | 【实验】排名检索模型

    在Experiment1的基础上实现最基本的Ranked retrieval model Input :a query (like Ron Weasley birthday) Output : Return the top K (e.g., K = 100) relevant tweets. Use SMART notation: lnc.ltn Document: logarithmic tf (l as first character), no idf and cosine normalization Query: logarithmic tf (l in leftmost column), idf (t in second column), no norma

    2024年02月08日
    浏览(40)
  • AI数据技术02:RAG数据检索

            在人工智能的动态环境中,检索增强生成(RAG)已成为游戏规则的改变者,彻底改变了我们生成文本和与文本交互的方式。RAG 使用大型语言模型 (LLM) 等工具将信息检索的强大功能与自然语言生成无缝结合,为内容创建提供了一种变革性的方法。         在

    2024年02月03日
    浏览(42)
  • 【pandas基础】--数据检索

    pandas 的数据检索功能是其最基础也是最重要的功能之一。 pandas 中最常用的几种数据过滤方式如下: 行列过滤:选取指定的行或者列 条件过滤:对列的数据设置过滤条件 函数过滤:通过函数设置更加复杂的过滤条件 本篇所有示例所使用的测试数据如下: pandas 中最常用的按

    2024年02月03日
    浏览(44)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包