首页 > 数据库 >【MySQL】复合查询

【MySQL】复合查询

时间：2025-01-03 17:33:42浏览次数：3  
标签：复合 查询 dept emp MySQL deptno where select

复合查询

一、表的笛卡尔积

1、介绍

  • 表的笛卡尔积（Cartesian product）是指当进行两个或多个表的连接查询时，如果没有指定有效的连接条件，导致每个表的每一行都与另一个表的每一行进行组合，从而生成的结果集的行数等于各个表行数的乘积。
  • 具体来说，如果表A有M行，表B有N行，那么它们之间的笛卡尔积将会是一个有M*N行的结果集，其中每一行都是表A中的一行与表B中的一行的组合。
  • 在实际应用中，笛卡尔积通常是不希望得到的，因为它会产生大量无用的数据组合。因此，在进行表的连接查询时，通常会指定一个或多个连接条件（如ON子句），以确保只返回有意义的组合。

2、示例

  • 本次以及下面的示例中，所用的表以及数据参见【MySQL】scott。
  • emp表与dept表进行笛卡尔积，显示emp表中的deptno字段等于dept表中的deptno字段的记录。
select * from emp,dept where emp.deptno=dept.deptno;

在这里插入图片描述

  • 显示部门号为10的部门编号、部门名、员工名和工资。
select emp.deptno, dname, ename, sal from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;

在这里插入图片描述

二、自连接

1、介绍

  • MySQL自连接是指在同一张表中进行数据关联和查询的一种特殊的SQL查询技术。它主要是通过使用别名来引用同一张表中的不同行，从而实现数据的关联和查询。
  • MySQL自连接的原理是基于SQL语句的SELECT命令实现的。在SELECT命令中，可以使用子查询来实现数据的关联和查询，而MySQL自连接就是一种特殊的子查询。

2、语法

SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b ON a.common_field = b.common_field;
  • 自连接的语法与普通连接查询类似，但需要在FROM子句中为同一张表指定不同的别名，并在连接条件中使用这些别名来引用表中的不同行。
  • table_name 是要进行自连接的表名，a 和 b 是该表的两个不同实例（别名），common_field 是用于连接两个实例的公共字段。

三、子查询

1、介绍

  • 子查询（Subquery）也称为嵌套查询，是指在一个SQL查询中嵌套的另一个查询。子查询可以放在SELECT、FROM、WHERE、HAVING等SQL语句中，用于从另一个查询结果集中检索数据。
  • 子查询可能会导致性能下降，特别是当子查询嵌套层次过多或者数据量很大时。可以通过优化查询逻辑、使用连接（JOIN）代替子查询或者使用临时表来提高性能。
  • 子查询依赖于外部查询的数据，如果外部查询的数据发生变化，可能会影响子查询的结果。因此，需要确保数据的一致性和完整性。
  • 如果子查询返回的结果集过大，可能会导致内存不足或者查询超时。可以通过限制子查询返回的数据量或者优化子查询逻辑解决。

2、类型

类型作用示例
标量子查询返回单个值的子查询，通常用于SELECT列表或者WHERE子句中查找薪资最高的员工，可以使用标量子查询来返回最大的薪资值
多行子查询返回多行数据的子查询，通常与IN、ANY或ALL等运算符结合使用查找所有位于某个位置的部门中的员工，可以使用多行子查询来返回所有符合条件的部门ID
表子查询返回一整张表的结果集，通常用于FROM子句中。表子查询允许将一个子查询的结果作为临时表来使用可以创建一个子查询来筛选薪资大于5000的员工，然后将这个结果作为一个临时表来进行进一步的查询
相关子查询子查询依赖于外层查询的某些列，即子查询的执行取决于外层查询中的每一行查找薪资高于所在部门平均薪资的员工，子查询需要计算每个部门的平均薪资，这依赖于外层查询中的部门ID。
非相关子查询子查询与外层查询无关，独立执行并返回结果大多数情况下，非相关子查询的执行速度会比相关子查询更快，因为它只需要执行一次。

3、语法

SELECT column_list_of_subquery
FROM (subquery) AS subquery_alias
  • column_list_of_subquery是子查询中要返回的列列表，subquery是嵌套查询，subquery_alias是子查询的别名，用于在外部查询中引用子查询的结果。

4、多行子查询运算符

运算符作用与NULL值的关系
IN检查一个值是否存在于子查询返回的结果集中。如果值存在于子查询的结果集中，则条件为真忽略
ANY将某个值与子查询返回的结果集中的任意一个值进行比较。ANY 可以与比较运算符（如 =, >, <, >=, <=）结合使用考虑
ALL将某个值与子查询返回的结果集中的所有值进行比较。ALL 可以与比较运算符（如 =, >, <, >=, <=）结合使用考虑

5、示例

  • 标量子查询：显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号【empno】）。
select empno, ename from emp where empno=(select mgr from emp where ename='FORD');

在这里插入图片描述

  • 显示SMITH同一部门的员工。
select * from emp where deptno=(select deptno from emp where ename='SMITH');

在这里插入图片描述

  • 多行子查询：查询和10号部门的工作岗位相同的雇员的名字、岗位、工资和部门号，但是不包含10号部门的。
select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;

在这里插入图片描述

  • 显示工资比30号部门所有员工的工资高的员工的姓名、工资和部门号。
select ename, sal, deptno from emp where sal>all(select sal from emp where deptno=30);

在这里插入图片描述

  • 显示工资比30号部门的任意员工的工资高的员工的姓名、工资和部门号（包含30号部门的员工）。
select ename, sal, deptno from emp where sal>any(select sal from emp where deptno=30);

在这里插入图片描述

  • 表子查询：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人。
select deptno, job, ename from emp where (deptno, job) = (select deptno, job from emp where ename='smith') and ename <> 'smith';

在这里插入图片描述

  • 显示每个高于自己部门平均工资的员工的姓名、部门号、部门名、工资和平均工资。
select ename, dept.deptno, dname, sal, avg_sal from emp, (select deptno, avg(sal) avg_sal from emp group by deptno) tmp, dept where emp.deptno=tmp.deptno and emp.deptno=dept.deptno and emp.sal>avg_sal;

在这里插入图片描述

  • 显示每个部门的信息（部门编号，部门名，地址）和人员数量，下方分别为多表和子查询方法。
select dept.deptno, dname, loc, count(*) from emp, dept where emp.deptno=dept.deptno group by deptno, dname, loc;

select dept.deptno, dname, loc, count_sum from dept, (select deptno, count(*) count_sum from emp group by deptno) tmp where dept.deptno=tmp.deptno;

在这里插入图片描述

四、UNION和UNION ALL

1、介绍

操作符作用
UNION合并两个或多个 SELECT 语句的结果集，并自动去除重复的记录
UNION ALL合并两个或多个 SELECT 语句的结果集，但不会去除重复的记录

2、注意

  • 列数和数据类型：使用 UNION 或 UNION ALL 合并的 SELECT 语句必须具有相同数量的列，并且对应列的数据类型必须兼容。
  • 列名：最终合并后的结果集的列名将基于第一个 SELECT 语句中的列名。如果需要在结果集中使用别名，可以在第一个 SELECT 语句中为列指定别名。
  • 排序和限制：如果需要对合并后的结果集进行排序或限制返回的行数，可以使用 ORDER BY 和 LIMIT 子句。但这些子句应该放在最后一个 SELECT 语句之后。

3、语法

SELECT column1, column2, ...
FROM table1
UNION [ALL]
SELECT column1, column2, ...
FROM table2;

4、示例

  • 显示工资大于2500或职位是MANAGER的员工。
select * from emp where sal>2500 union select * from emp where job='manager';

在这里插入图片描述

select * from emp where sal>2500 union all select * from emp where job='manager';

在这里插入图片描述

五、表的连接查询

1、介绍

  • MySQL中的表的连接查询是指将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。
  • 连接查询是关系型数据库中最常用的查询方式之一，它允许用户从多个表中获取相关数据。即将多张表连接成一个大的数据集，再选择合适的结果返回。

2、类型

操作符作用
INNER JOIN（内连接）只返回两个表中符合连接条件的记录。如果某个记录在其中一个表中没有匹配的记录，那么该记录将不会出现在结果集中
LEFT JOIN（左连接）返回左表中的所有记录以及右表中符合连接条件的记录。如果右表中没有匹配的记录，结果集中的这些记录将包含NULL
RIGHT JOIN（右连接）返回右表中的所有记录以及左表中符合连接条件的记录。如果左表中没有匹配的记录，结果集中的这些记录将包含NULL

3、语法

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

4、注意

  • 连接条件：在进行连接查询时，必须指定连接条件，否则会产生笛卡尔积，导致结果集过于庞大且无用。
  • 别名使用：当连接的表较多或字段名重复时，可以使用别名来简化查询语句和提高可读性。
  • 性能优化：连接查询可能会涉及大量的数据计算和传输，因此在进行大规模数据查询时，需要注意性能优化，如使用索引、分区等技术。

5、示例

  • 显示SMITH的名字和部门名称。
select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='smith';

select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='smith';
  • 左外连接。
create table stu (id int, name varchar(30));
insert into stu values(1,'jack'),(2,'mike'),(3,'kity'),(4,'alice');
create table exam (id int, grade int);
insert into exam values(1, 56),(2,66),(11, 18);
select * from stu left join exam on stu.id=exam.id;

在这里插入图片描述

  • 右外连接。
select * from stu right join exam on stu.id=exam.id;

在这里插入图片描述

本文到这里就结束了，如有错误或者不清楚的地方欢迎评论或者私信
本文只是在学习过程中所做的总结，不会涉及过深的概念
创作不易，如果觉得博主写得不错，请点赞、收藏加关注支持一下

标签：复合,查询,dept,emp,MySQL,deptno,where,select
From： https://blog.csdn.net/Snow_Dragon_L/article/details/144355415

相关文章

  • MySQL:一文弄懂时区&time_zone
    你还在被以下问题困扰吗：MySQL的安装规范中应该设置什么时区？JAVA应用读取到的时间和北京时间差了14个小时，为什么？怎么解决？已经运行一段时间的业务，修改MySQL的时区会影响已经存储的时间类型数据吗？迁移数据时会有导致时间类型数据时区错误的可能吗？…看完这篇文章，你......
  • MySQL备份脚本.241217
    MYSQL备份，每天全量备份（排除由于GTID的缘故造成恢复问题的系统库），然后通过Gzip进行压缩。#!/bin/bash#DEFINEVARIABLESBACKUPUSER=rootBACKUPPASS=root密码BACKUPPORT=mysql端口#BACKUPSOCKET=/home/data/$BACKUPPORT/logs/mysql.sockBACKUPIP=localhostMYSQL=/usr/local......
  • RockyLinux 9.5 MySQL5.7_二进制方式+jdk1.8+tomcat9+jpress
    安装MySQL5.7#cd/opt#yuminstall-ywget#wgethttps://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz#tar-xvfmysql-5.7.10-linux-glibc2.5-x86_64.tar.gz-bash:tar:commandnotfound#yuminstall-ytar#tar-xvfmysql-5.7.10......
  • 欧拉OpenEuler安装MySQL8.241227
    1.安装mysqltar-xvfmysql-8.0.21-linux-glibc2.12-x86_64.tarmvmysql-8.0.21-linux-glibc2.12-x86_64/usr/local/mysql2.配置mysqlvim/etc/my.cnf[client]default-character-set=utf8mb4[mysqld]#nd-address=0.0.0.0port=3306user=mysqlbasedir=/usr/local/m......
  • 如何快速查询脱硫煤电价
    随着信息化的发展，查询脱硫煤电价已变得相对便捷。脱硫煤是指经过脱除硫化物的煤炭，其燃烧产生的二氧化硫排放低于普通煤炭，因此脱硫煤电价的查询对于能源消费和环保方面都具有重要意义。在众多查询方式中，“光伏一点通”小程序是一个值得推荐的工具。脱硫煤电价的查询需求通常来......
  • 【基础篇重点】六、MySQL表的增删查改
    文章目录前言Ⅰ.创建新数据1、`insert`语句2、插入否则更新--替换3、替换--`replace`Ⅱ.检索数据1、`select`语句①全列查询②指定列查询③查询字段为表达式④为查询结果指定别名`as`⑤结果去重`distinct`2、`where`条件......
  • 【基础篇】七、MySQL内置函数
    文章目录Ⅰ.日期函数案例一案例二Ⅱ.字符串函数常见字符串函数使用案例1、显示对应的字符集--`charset`2、要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”--`concat`3、求学生表中学生姓名占用的字节数--`length`4、......
  • 实践项目-数据库主从高可用（MySQL-MHA、ProxySQL、Backup）
    (250103)实践目标备份策略：定期全量备份和增量备份，备份文件异地存储。恢复测试：定期在测试库上恢复备份，确保备份文件可用。权限管理：严格控制数据库访问权限，避免误操作。变更管理：所有数据库变更需经过审批，并在非高峰时段执行。监控告警：实时监控数据库状态，设置告警机制，及时发现......
  • 最新MySQL面试题(2025超详细版)
    2025最新超详细MySQL面试题文章目录2025最新超详细MySQL面试题@[toc]一、SQL和基本操作1.SQL的执行顺序2.如何优化MySQL查询3.常用的聚合函数4.数据库事务5.事务的四大特性(ACID)6.视图7.MySQL中使用LIMIT子句进行分页8.MySQL中使用变量和用户定义的函数9.My......
  • MySQL优化--插入数据优化和主键优化
    一、插入数优化（insert）平时我们插入数据的时候一般都是一个语句插一个数据，如下所示：insertintotb_testvalues(1,'tom');insertintotb_testvalues(2,'cat');insertintotb_testvalues(3,'jerry');如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99