SQL中的子查询

最新推荐文章于 2024-10-07 09:59:42 发布
最新推荐文章于 2024-10-07 09:59:42 发布
阅读量943 收藏 3
点赞数
文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_57276793/article/details/130115838
版权
本文介绍了SQL中的子查询概念，以及它在WHERE、FROM、HAVING和SELECT子句中的使用方法，通过实例展示了如何获取部门平均工资、筛选条件、表关联等操作，帮助理解子查询在数据库查询中的功能和重要性。
摘要由CSDN通过智能技术生成

一、子查询定义

        当我们进⾏语句查询的时候，总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏，就是说A 查询语句需要依赖B 查询语句的查询结果，B 查询就是⼦查询，A 查询语句就是主查询，⼀个SQL语句可以包含多个⼦查询。

二、子查询出现的地方

- 在where⼦句中: ⼦查询的结果可⽤作条件筛选时使⽤的值。
- 在from⼦句中: ⼦查询的结果可充当⼀张表或视图，需要使⽤表别名。
- 在having⼦句中: ⼦查询的结果可⽤作分组查询再次条件过滤时使⽤的值
- 在select⼦句中: ⼦查询的结果可充当⼀个字段。仅限⼦查询返回单⾏单列的情况。

1、子查询出现在where子句中

1、查询⼯资⼤于10号部⻔的平均⼯资的⾮10号部⻔的员⼯信息
分析：
1）10号部⻔的平均⼯资
 select avg(sal) from emp where deptno = 10;
2）查询员工信息
 select * from emp where deptno != 10 and sal > (select avg(sal) from emp where deptno = 10);

+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+


2、查询与7369同部⻔的同事信息

select deptno from emp where empno = 7369;

select * from emp where deptno = (select deptno from emp where empno = 7369)
  and empno != 7369;

+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+

2、子查询出现在from子句中

1、查询员⼯的姓名，⼯资，及其部⻔的平均⼯资
 select ename,sal from emp ;
 思考：
  每一个部门的平均工资是多少？
  select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|    100 | NULL        |
+--------+-------------+
4 rows in set (0.09 sec)
查询员工信息：
select * from emp;
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800.00  | NULL    |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 | 300.00  |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 | 500.00  |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL    |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL    |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL    |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL    |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL    |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 | 0.00    |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL    |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 950.00  | NULL    |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL    |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL    |     10 |
|  8000 | LaoYan | NULL      | NULL | NULL       | NULL    | NULL    |    100 |
+-------+--------+-----------+------+------------+---------+---------+--------+

此时：查询出来的平均工资表可以当做一个虚拟的表，和emp表关联起来

mysql> select * from 
  ( select deptno,avg(sal) avgMoney from emp group by deptno ) avgTable;
+--------+-------------+
| deptno | avgMoney    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|    100 | NULL        |
+--------+-------------+

现在 avgTable 表  和 emp  通过 deptno 相关联
select  
   ename,sal,avgMoney from 
    emp , 
   (select deptno,avg(sal) avgMoney from emp group by deptno) avgTable   
   where emp.deptno=avgTable.deptno;
+--------+---------+-------------+
| ename  | sal     | avgMoney    |
+--------+---------+-------------+
| SMITH  | 800.00  | 2175.000000 |
| ALLEN  | 1600.00 | 1566.666667 |
| WARD   | 1250.00 | 1566.666667 |
| JONES  | 2975.00 | 2175.000000 |
| MARTIN | 1250.00 | 1566.666667 |
| BLAKE  | 2850.00 | 1566.666667 |
| CLARK  | 2450.00 | 2916.666667 |
| SCOTT  | 3000.00 | 2175.000000 |
| KING   | 5000.00 | 2916.666667 |
| TURNER | 1500.00 | 1566.666667 |
| ADAMS  | 1100.00 | 2175.000000 |
| JAMES  | 950.00  | 1566.666667 |
| FORD   | 3000.00 | 2175.000000 |
| MILLER | 1300.00 | 2916.666667 |
| LaoYan | NULL    | NULL        |
+--------+---------+-------------+
最终的答案！

虚拟机的表解释：--任何的查询结果都可以当做一个虚拟机的表来使用！
mysql> select ename,job,sal from emp where job is not null;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| SMITH  | CLERK     | 800.00  |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     | 950.00  |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+
14 rows in set (0.09 sec)

mysql> select * from (select ename,job,sal from emp where job is not null) eemp;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| SMITH  | CLERK     | 800.00  |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     | 950.00  |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+
14 rows in set (0.06 sec)

3、子查询出现在select后面

2、查询员⼯的姓名，⼯资，及其部⻔的平均⼯资
说一个情况： emp表和emp表也是可以自己关联自己的。
 select * from emp e1, emp e2 where e1.empno = e2.empno;


select ename,sal, (select avg(sal) from emp e2 where e2.deptno = e1.deptno ) 
 部门平均工资 from emp e1;
拆解：
select avg(sal) from emp e2 where e2.deptno = 10;
select avg(sal) from emp e2 where e2.deptno = 20;

最终的答案：
+--------+---------+--------------+
| ename  | sal     | 部门平均工资 |
+--------+---------+--------------+
| SMITH  | 800.00  | 2175.000000  |
| ALLEN  | 1600.00 | 1566.666667  |
| WARD   | 1250.00 | 1566.666667  |
| JONES  | 2975.00 | 2175.000000  |
| MARTIN | 1250.00 | 1566.666667  |
| BLAKE  | 2850.00 | 1566.666667  |
| CLARK  | 2450.00 | 2916.666667  |
| SCOTT  | 3000.00 | 2175.000000  |
| KING   | 5000.00 | 2916.666667  |
| TURNER | 1500.00 | 1566.666667  |
| ADAMS  | 1100.00 | 2175.000000  |
| JAMES  | 950.00  | 1566.666667  |
| FORD   | 3000.00 | 2175.000000  |
| MILLER | 1300.00 | 2916.666667  |
| LaoYan | NULL    | NULL         |
+--------+---------+--------------+

优化一下：工资 保留小数点后2位
select ename,sal, 
   round(
    ( select avg(sal) from emp e2 where e2.deptno = e1.deptno),2) 部门平均工资 
  from emp e1;
+--------+---------+--------------+
| ename  | sal     | 部门平均工资 |
+--------+---------+--------------+
| SMITH  | 800.00  | 2175.00      |
| ALLEN  | 1600.00 | 1566.67      |
| WARD   | 1250.00 | 1566.67      |
| JONES  | 2975.00 | 2175.00      |
| MARTIN | 1250.00 | 1566.67      |
| BLAKE  | 2850.00 | 1566.67      |
| CLARK  | 2450.00 | 2916.67      |
| SCOTT  | 3000.00 | 2175.00      |
| KING   | 5000.00 | 2916.67      |
| TURNER | 1500.00 | 1566.67      |
| ADAMS  | 1100.00 | 2175.00      |
| JAMES  | 950.00  | 1566.67      |
| FORD   | 3000.00 | 2175.00      |
| MILLER | 1300.00 | 2916.67      |
| LaoYan | NULL    | NULL         |
+--------+---------+--------------+

再来一遍，仔细品味：
mysql> select ename,sal, ( select avg(sal) from emp e2 where e2.deptno = 10 ) from emp;
+--------+---------+------------------------------------------------------+
| ename  | sal     | ( select avg(sal) from emp e2 where e2.deptno = 10 ) |
+--------+---------+------------------------------------------------------+
| SMITH  | 800.00  | 2916.666667                                          |
| ALLEN  | 1600.00 | 2916.666667                                          |
| WARD   | 1250.00 | 2916.666667                                          |
| JONES  | 2975.00 | 2916.666667                                          |
| MARTIN | 1250.00 | 2916.666667                                          |
| BLAKE  | 2850.00 | 2916.666667                                          |
| CLARK  | 2450.00 | 2916.666667                                          |
| SCOTT  | 3000.00 | 2916.666667                                          |
| KING   | 5000.00 | 2916.666667                                          |
| TURNER | 1500.00 | 2916.666667                                          |
| ADAMS  | 1100.00 | 2916.666667                                          |
| JAMES  | 950.00  | 2916.666667                                          |
| FORD   | 3000.00 | 2916.666667                                          |
| MILLER | 1300.00 | 2916.666667                                          |
| LaoYan | NULL    | 2916.666667                                          |
+--------+---------+------------------------------------------------------+
15 rows in set (0.10 sec)

mysql> select ename,sal, ( select avg(sal) from emp e2 where e2.deptno = emp.deptno ) from emp;
+--------+---------+--------------------------------------------------------------+
| ename  | sal     | ( select avg(sal) from emp e2 where e2.deptno = emp.deptno ) |
+--------+---------+--------------------------------------------------------------+
| SMITH  | 800.00  | 2175.000000                                                  |
| ALLEN  | 1600.00 | 1566.666667                                                  |
| WARD   | 1250.00 | 1566.666667                                                  |
| JONES  | 2975.00 | 2175.000000                                                  |
| MARTIN | 1250.00 | 1566.666667                                                  |
| BLAKE  | 2850.00 | 1566.666667                                                  |
| CLARK  | 2450.00 | 2916.666667                                                  |
| SCOTT  | 3000.00 | 2175.000000                                                  |
| KING   | 5000.00 | 2916.666667                                                  |
| TURNER | 1500.00 | 1566.666667                                                  |
| ADAMS  | 1100.00 | 2175.000000                                                  |
| JAMES  | 950.00  | 1566.666667                                                  |
| FORD   | 3000.00 | 2175.000000                                                  |
| MILLER | 1300.00 | 2916.666667                                                  |
| LaoYan | NULL    | NULL                                                         |
+--------+---------+--------------------------------------------------------------+
15 rows in set (0.11 sec)


再来一题：
查询每个员⼯的信息及其部⻔的平均⼯资，⼯资之和，部⻔⼈数
select *,
  (select avg(sal) from emp e1 where e1.deptno = emp.deptno) 平均工资,
  (select sum(sal) from emp e2 where e2.deptno = emp.deptno) 工资之和,
  (select count(*) from emp e3 where e3.deptno = emp.deptno) 部门人数
from emp;

尝试-- 这种不行，报 Operand should contain 1 column(s)
select *,(
   select avg(sal) 平均工资,sum(sal) 工资之和,count(*) 部门人数  from emp e1 where e1.deptno = emp.deptno) from emp;

改变思路：在玩一种很新的东西

mysql> select deptno,avg(sal) 平均工资,sum(sal) 工资之和,count(*) 部门人数  from emp e1
   group by deptno ;
+--------+-------------+----------+----------+
| deptno | 平均工资    | 工资之和 | 部门人数 |
+--------+-------------+----------+----------+
|     10 | 2916.666667 | 8750.00  |        3 |
|     20 | 2175.000000 | 10875.00 |        5 |
|     30 | 1566.666667 | 9400.00  |        6 |
|    100 | NULL        | NULL     |        1 |
+--------+-------------+----------+----------+

硬套进去：失败了，原因是这个地方只允许出现一列数据
select * , (select * from (select deptno,avg(sal) 平均工资,sum(sal) 工资之和,count(*) 部门人数  from emp e1
   group by deptno) pgb where pgb.deptno =emp.deptno ) from emp;

4、子查询出现在having子句中

查询平均⼯资⼤于30号部⻔的平均⼯资的部⻔号，和平均⼯资
分析：30号部⻔的平均⼯资
select avg(sal) from emp where deptno = 30;
查询所有部门的平局工资：
select deptno,avg(sal) from emp group by deptno;

整合
select deptno,avg(sal) avgMoney from emp group by deptno 
   having avgMoney > (select avg(sal) from emp where deptno = 30);
+--------+-------------+
| deptno | avgMoney    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
+--------+-------------+
三木5-29
关注
SQL查询：如何在where条件中使用子查询
sg_knight的专栏
03-28 1907
子查询在 WHERE 条件中的使用能够极大地增强 SQL 查询的灵活性和表达能力，可以满足复杂查询需求。但是在使用子查询时，需要注意查询效率和结果集的大小，避免性能问题和不必要的复杂性。
SQL 子查询
LGF124241877的专栏
07-07 231
[size=xx-large]SQL子查询[/size] 子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，检索条件值又是来自该表本身的内部数据时，子查询非常有用。 子查询可以嵌入以下SQL子句中：where子句、having子句和from子句。 例：查询工资比编号为7566雇员工资高的雇员姓名。 SQL>select ename from ...
sql中子查询
最新发布
qq_48763502的博客
10-07 485
SQL子查询是指在一个SQL查询的内部嵌套另一个查询。子查询可以用来返回单个值、多个值或表格数据，通常用于复杂查询中，以便从一个查询的结果集中进一步筛选或处理数据。
嵌套套娃，MySQL子查询，单行与多行子查询，相关和不相关（关联）子查询，完整详细可收藏
叶绿体不忘呼吸
11-24 1557
文章目录1、需求分析与问题解决2、单行子查询3、多行子查询4、相关子查询5、抛一个思考题 子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。 1、需求分析与问题解决 1.1 实际问题 #方式一： SELECT salary FROM employees WH
sql子查询
weixin_44050355的博客
10-21 1038
子查询 某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果，这个时候，就要用到子查询。 例如： 为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）被叫做子查询。 一般根据子查询的嵌入位置分为，where型子查询,from型子查询，exists型子查询。 1、where型子查询 where型子查询即把内层sql语句查询的结果作为外层sql查询的条件.  ...
SQL——子查询
小平凡的记录的博客
04-14 2302
当查询需分步时,只能用子查询,如例56; 不能将列值与统计结果值进行比较的条件写在WHERE中,只能用子查询; 查询目标列来自多个表时,只能用多表连接实现.
sqlserver子查询练习.sql
08-16
Microsoft SQL Server 2008技术内幕：T-SQL语言基础（子查询）练习sql文件
sqlserver 子查询语句
03-23
表ta userno(编号),username(名字) 表tb userno(编号),score(分数) 想查询结果为 userno(编号),username(名字)..."子查询"是SQL语言中的一个重要概念，它允许在一个查询语句（主查询）中嵌套另一个查询语句（子查询）
SQL子查询语句简单示例
01-19
SQL 中的 SELECT 命令不仅能够直接用来查询数据库中的数据，还能够嵌入到 WHERE 中作为子查询语句，或者将查询结果插入到另一个表中。 　将子查询用于 WHERE 子句 　子查询能够与 SELECT、INSERT、UPDATE 和 ...
SQLServer中的子查询
L_ZWge的博客
06-19 1025
嵌套子查询即一个子查询可以嵌套在另一个子查询中。 在SQL Server中，最多支持 32 个嵌套级别。
SQL-子查询
杜
01-08 1059
最近在和小伙伴搭档补漏洞，之前学过的知识大多是囫囵吞枣，一知半解的样子，为了复习巩固，外加自制力差，就和小伙伴组团学习了。现阶段是SQL语句的使用。在学到第六章SQL子查询中遇到以下几个问题：      1.已经创建好的表中添加外键：       通常我们会通过语句来创建表与表之间的主外键关系，如下：     Create table User        (UserId int not
SQL子查询
m0_74310542的博客
04-30 1229
- 查询比张三高的工资​where name="张三"where money>"张三的工资"​-- 完整的语句where name="张三")子查询：就是在查询语句嵌套一个查询语句子查询是嵌套在查询语句的查询语句，就像语句里嵌套语句标准结构语句：select 列1,列2,(子查询) as 列别名 from 表面员工表（t_employee）：(外键，与部门dept的主键关联)部门表（t_dept）：pk_deptid（部门主键） pk_deptName（职位）
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值