子查询

最新推荐文章于 2022-08-10 19:36:01 发布
最新推荐文章于 2022-08-10 19:36:01 发布
阅读量878 收藏 1
点赞数
分类专栏： Oracle

数据库技术学习 https://www.itkc8.com

子查询一(WHERE中的子查询)

子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，为了让读者更加清楚子查询的概念。

子查询返回结果
子查询可以返回的数据类型一共分为四种：

    1. 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
    2. 单行多列：返回一行数据中多个列的内容；
    3. 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
    4. 多行多列：查询返回的结果是一张临时表；

在WHERE子句中使用子查询

在WHERE子句之中处理单行单列子查询、多行单列子查询、单行多列子查询。

单行单列子查询

 示例一、查询公司之中工资最低的雇员的完整信息

--查询公司之中工资最低的雇员的完整信息
SELECT * 
FROM emp e
WHERE e.sal=(
  SELECT MIN(sal) 
  FROM emp);

示例二、查询出基本工资比ALLEN低的全部雇员信息

复制代码

-- 查询出基本工资比ALLEN低的全部雇员信息
SELECT * 
FROM emp e
WHERE e.sal<(
 SELECT sal 
 FROM emp 
 WHERE ename='ALLEN'
);

复制代码

示例三、查询基本工资高于公司平均薪金的全部雇员信息

 

--查询基本工资高于公司平均薪金的全部雇员信息
SELECT *
FROM emp e
WHERE e.sal>(
SELECT AVG(sal)
FROM emp);

单行多列子查询。

示例四、查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息,

复制代码

--查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息,
SELECT *
FROM emp e
WHERE e.job=(
  SELECT job
  FROM emp 
  WHERE ename='ALLEN') 
  AND e.sal>(
  SELECT sal
  FROM emp 
  WHERE empno=7521);

复制代码

示例五、查询与SCOTT从事同一工作且工资相同的雇员信息

复制代码

SELECT *
FROM emp e
WHERE (e.job,e.sal) = (
  SELECT job,sal
  FROM emp 
  WHERE ename='SCOTT')
  AND ename<>'SCOTT';

复制代码

示例六、查询与雇员7566从事同一工作且领导相同的全部雇员信息

复制代码

  --查询与雇员7566从事同一工作且领导相同的全部雇员信息
SELECT *
FROM emp e
WHERE (e.job,e.mgr) =(
        SELECT job,mgr
        FROM emp
      WHERE empno=7566
);

复制代码

示例七、查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN）

复制代码

--查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN）
SELECT *
FROM emp e
WHERE (e.job,to_char(e.hiredate,'yyyy'))=(
           SELECT job,to_char(hiredate,'YYYY')
            FROM emp
            WHERE ename='ALLEN'
);

复制代码

多行单列子查询

 

主要使用三种操作符：IN、ANY、ALL

 IN操作

示例八、查询出与每个部门中最低工资相同的全部雇员信息

复制代码

--查询出与每个部门中最低工资相同的全部雇员信息
SELECT *
FROM emp e
WHERE e.sal IN(
     SELECT MIN(sal)
    FROM emp
    GROUP BY deptno
);

复制代码

示例九、查询出不与每个部门中最低工资相同的全部雇员信息

复制代码

--查询出不与每个部门中最低工资相同的全部雇员信息
SELECT *
FROM emp e
WHERE e.sal NOT IN(
     SELECT MIN(sal)
    FROM emp
    GROUP BY deptno
);

复制代码

ANY在使用中有如下三种使用形式：
=ANY：表示与子查询中的每个元素进行比较，功能与IN类似（然而<>ANY不等价于NOT IN）
>ANY：比子查询中返回结果的最小的要大（还包含了>=ANY）
<ANY：比子查询中返回结果的最大的要小（还包含了<=ANY）

示例十、查询出每个部门经理的工资

复制代码

--查询出每个部门经理的工资

SELECT  * 
FROM emp 
WHERE sal = ANY (
   SELECT MIN (sal)
  FROM emp
  WHERE job='MANAGER'
  GROUP BY deptno
);

复制代码

示例十一、查询出每个部门大于经理的工资

复制代码

--查询出每个部门大于经理的工资
SELECT  * 
FROM emp 
WHERE sal > ANY (
   SELECT MIN (sal)
  FROM emp
  WHERE job='MANAGER'
  GROUP BY deptno
);

复制代码

示例十二、查询出每个部门小于经理的工资

复制代码

--查询出每个部门小于经理的工资
SELECT  * 
FROM emp 
WHERE sal < ANY (
   SELECT MIN (sal)
  FROM emp
  WHERE job='MANAGER'
  GROUP BY deptno
);

复制代码

ALL操作符有以下三种用法：
<>ALL：等价于NOT IN（但是=ALL并不等价于IN）
>ALL：比子查询中最大的值还要大（还包含了>=ALL）
<ALL：比子查询中最小的值还要小（还包含了<=ALL）

示例十三、查询出每个部门不等于经理的工资

复制代码

--查询出每个部门不等于经理的工资
SELECT  * 
FROM emp 
WHERE sal <> ALL (
   SELECT MIN (sal)
  FROM emp
  WHERE job='MANAGER'
  GROUP BY deptno
);

复制代码

示例十四、

复制代码

SELECT  * 
FROM emp 
WHERE sal < ALL (
   SELECT MIN (sal)
  FROM emp
  WHERE job='MANAGER'
  GROUP BY deptno
);

复制代码

示例十五、

复制代码

SELECT  * 
FROM emp 
WHERE sal >ALL (
   SELECT MIN (sal)
  FROM emp
  WHERE job='MANAGER'
  GROUP BY deptno
);

复制代码

空数据判断

在SQL之中提供了一个exists结构用于判断子查询是否有数据返回。如果子查询中有数据返回，则exists结构返回true，反之返回false。

示例十五、验证exists结构

 

--验证exists结构
SELECT * FROM emp
    WHERE EXISTS(   --返回空值，没有内容输出
      SELECT * FROM emp WHERE empno=9999); --没有这个编号的员工

示例十六、

 SELECT * FROM emp
 WHERE EXISTS(SELECT * FROM emp);--有内容将返回数据

示例十七、

  SELECT * FROM emp
 WHERE NOT EXISTS(SELECT * FROM emp);--有数据，但取返，没有内容输出

 

 

 

 

HAVING子句的主要功能是对分组后的数据进行过滤，如果子查询在HAVING中表示要进行分组过滤，一般返回单行单列的数据

示例一、查询部门编号，人数，平均工资，并且要求这些部门的平均工资高于公司的平均工资

复制代码

 --查询部门编号，人数，平均工资，并且要求这些部门的平均工资高于公司的平均工资
 SELECT e.deptno,COUNT(e.empno),ROUND(AVG(e.sal),2)
 FROM emp e
 GROUP BY e.deptno
 HAVING AVG(e.sal)>(
        SELECT AVG(sal)
        FROM emp
 );

复制代码

示例二、查询出部门平均工资高的部门名称和平均工资

复制代码

 --查询出部门平均工资高的部门名称和平均工资
SELECT d.deptno,d.dname,AVG(e.sal) avgsal
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname
HAVING AVG(e.sal)=(
    SELECT MAX(AVG(sal))
     FROM emp
     GROUP BY deptno
);

复制代码

 

FROM子句中使用子查询一般都是返回多行多列，可以将其当作一张数据表

 

示例一、查询出每个部门的编号，名称，位置，部门人数，平均工资

SELECT d.deptno,d.dname,d.loc,temp.con,temp.avgsal
FROM dept d,(SELECT deptno dno,COUNT(empno) con,ROUND (AVG(sal),2) avgsal
             FROM emp 
             GROUP BY deptno) temp
WHERE d.deptno=temp.dno;

 

示例二、查询出所有在部门SALES(销售部)工作的员工编号，姓名，基本工资，奖金，职位，入职日期，部门最高和最低工资

复制代码

SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,e.deptno,temp.maxsal,temp.minsal
FROM emp e,(
            SELECT deptno dno,MAX(sal) maxsal,MIN(sal) minsal
            FROM emp
            GROUP BY deptno
            ) temp
WHERE e.deptno=(SELECT deptno
                 FROM dept
                 WHERE dname='SALES')
                 AND e.deptno=temp.dno;

复制代码

 

示例三、查询出所有工资高于公司平均工资的员工编号，姓名，基本工资，职位，入职日期，部门名称，位置，上级领导姓名，公司的工资等级，部门人数，平均工资，平均服务年限

复制代码

SELECT e.empno,e.ename,e.job,e.hiredate,d.dname,d.loc,m.ename,s.grade,temp.avgsal,temp.avgyear
FROM emp e,dept d,emp m,salgrade s,(
                                   SELECT deptno dno,COUNT(empno) con,ROUND (AVG(sal),2) avgsal,ROUND(AVG(months_between(SYSDATE,hiredate)/12),2) avgyear
                                   FROM emp
                                   GROUP BY deptno
                                   ) temp
WHERE e.sal>(SELECT AVG(sal)
              FROM emp)
              AND e.deptno=d.deptno
              AND e.mgr=m.empno(+)
              AND e.sal BETWEEN s.losal AND s.hisal
              AND e.deptno=temp.dno;

复制代码

 

示例四、列出工资比ALLEN或者CLARK多的所有员工的编号，姓名，基本工资，部门名称，领导姓名，部门人数

复制代码

SELECT e.empno,e.ename,e.sal,d.dname,m.ename,temp.con
FROM emp e,dept d,emp m,(
                          SELECT deptno dno,COUNT(empno) con
                          FROM emp
                          GROUP BY deptno
                          )temp
WHERE e.sal>ANY(SELECT sal
                 FROM emp
                 WHERE ename IN('ALLEN','CLARK')
                 )
              AND e.ename NOT IN ('ALLEN','CLARK')
              AND e.deptno=d.deptno
              AND e.mgr=m.empno(+)
              AND e.deptno=temp.dno;

复制代码

 

示例五、列出公司各个部门的经理（一个部门只有一个）的姓名,工资，部门名称，部门人数，部门平均工资

 

复制代码

--列出公司各个部门的经理（一个部门只有一个）的姓名,工资，部门名称，部门人数，部门平均工资
SELECT e.ename,e.sal,d.dname,temp.con,temp.avgsal
FROM emp e,dept d,( 
                   SELECT deptno dno,COUNT(empno) con,ROUND(AVG(sal),2) avgsal
                   FROM emp
                   GROUP BY deptno
                   )temp
WHERE e.job='MANAGER'
       AND e.deptno=d.deptno(+)
       AND e.deptno=temp.dno;

复制代码

 

示例一、查询出每个部门的编号，名称，位置，部门人数，平均工资

SELECT d.deptno,d.dname,d.loc,
  (SELECT  COUNT(empno) FROM emp WHERE emp.deptno=d.deptno GROUP BY deptno) con,
  (SELECT AVG(sal) FROM emp WHERE emp.deptno=d.deptno GROUP BY deptno) avgsal
FROM dept d

WITH 子句

示例二、使用with子句将emp表中的数据定义为临时表

WITH e AS(
SELECT * FROM emp)
SELECT * FROM e;

示例三、查询每个部门的编号，名称，位置，部门平均工资，人数

复制代码

--查询每个部门的编号，名称，位置，部门平均工资，人数
WITH e AS(
  SELECT deptno dno,ROUND(AVG(sal),2) avgsal,COUNT(empno) con
  FROM emp
  GROUP BY deptno
)
SELECT d.deptno,d.dname,d.loc,e.avgsal,e.con
FROM dept d,e
WHERE d.deptno=e.dno(+);

复制代码

示例四、查询每个部门工资最高的员工编号，姓名，职位，入职日期，工资，部门编号，部门名称，显示结果按部门编号进行排序

复制代码

WITH e AS(
   SELECT deptno dno,MAX(sal) maxsal
   FROM emp
   GROUP BY deptno
)
SELECT em.ename,em.job,em.hiredate,em.sal,d.deptno,d.dname
FROM emp em,dept d,e
WHERE em.deptno=e.dno
       AND em.sal=e.maxsal
       AND d.deptno=e.dno
       ORDER BY em.deptno;

复制代码

数据库技术学习 https://www.itkc8.com

 

最是那一低头的温柔
关注
专栏目录
sql中的查询（模糊查询，子查询，联表查询）
weixin_44823472的博客
03-10 1989
sql中的查询（模糊查询，子查询，联表查询） 关键字的执行优先级 from -->on -->join -->where -->group by -->having -->select -->distinct -->union -->order by 模糊查询 like关键字：select * from table where u_name l...
Mysql：子查询
不怕猫的耗子A
11-21 2010
子查询 1、子查询是指：一个查询语句嵌套在另一个查询语句内部的查询 ⑴嵌套在内部的查询语句：叫做子查询，又叫内部查询 ⑵相对于内部查询，包含内部查询的就称为外部查询 2、子查询可以包含普通SELECT语句可以包括的任何子句，比如：distinct、group by、order by、limit、join和union等 ⑴但是对应的外部查询必须是以下语句之一：SELECT、INSERT、UPDATE、DELETE、SET或者DO。可以进行多层嵌套 3、子查询的位置：SELECT中...
子查询的几种情况
Lin_SkySea的博客
07-22 490
子查询进阶 子查询结果是单行单列（一个值）： select * from 表 where = (子查询) 子查询结果是多个值（单列多行） select * from 表 where in (子查询) 子查询结果是多行多列，看成一个表，可以接在from select * from (子查询) where 条件 ...
MYSQL查询语句2——子查询
weixin_50828895的博客
12-28 2571
子查询一、为什么会使用子查询二、什么是子查询三、子查询的具体使用+实例1、WHERE子句后使用子查询2、FROM子句后使用子查询 一、为什么会使用子查询 虽然可以通过连接查询来实现多表查询数据记录，但不建议使用，因为连接查询的性能很差，为什么呢？我们来进行分析，例如 我们要查询部门表dept 和雇员表employee中的数据记录，一般可能会写成： SELECT * FROM Course c1,Scroe c2 WHERE c1.c_id=c2.c_id; 对于这条SQL语句，在数据库执行的时候，会先对
mybatis利用association或collection传递多参数子查询
08-26
"MyBatis多参数子查询使用association或collection" MyBatis是一款流行的Java持久层框架，提供了多种方式来实现复杂的数据库查询操作。其中，使用association或collection来传递多参数子查询是一种常用的方法。本文...
MySQL优化之使用连接(join)代替子查询
12-16
本文将探讨如何通过使用连接（JOIN）操作来替代子查询（Sub-Queries），以提高查询效率。 子查询是一种强大的SQL工具，它允许在一个查询中嵌套另一个查询，以获取所需的结果。然而，虽然子查询提供了方便和灵活性，...
MySQL中表子查询与关联子查询的基础学习教程
09-10
MySQL中的子查询是一种强大的工具，它允许在一个查询中嵌套另一个查询，以获取更复杂的查询结果。本教程主要关注两种类型的子查询：表子查询和关联子查询。 **表子查询**指的是子查询返回的结果是一个N行N列的表格...
Mybatis框架（子查询）
06-30
Mybatis框架（子查询）
Mysql数据库性能优化之子查询
09-09
6. **考虑子查询的顺序**：MySQL可能会根据子查询的复杂度和数据量调整执行顺序，有时改变子查询的位置可以提高性能。 7. **使用LIMIT优化**：如果子查询只需要返回几个结果，使用LIMIT可以减少处理的数据量。 8. ...
几个sql语句例子
y8805655的博客
03-22 615
--【1】查询出每个部门的编号、名称、位置、部门人数、平均工资 ---多字段分组统计 1400*400=560000 select d.deptno,dname,loc,count(empno),avg(sal) from emp e ,dept d where e.deptno(+)=d.deptno group by d.deptno,dname,loc; ---统计操作都放在子
MYSQL 子查询
weixin_62927851的博客
08-10 4170
MYSQL的子查询还是比较常用 好好掌握
MySQL练习题(二)多表查询、子查询(含数据库脚本)
qq_37649504的博客
04-13 1252
数据库脚本准备 -- ----------------------------------- -- 创建db30库、dept表、emp表并插入记录 -- ----------------------------------- -- 删除db30库(如果存在) drop database if exists db30; -- 重新创建db30库 create database db30 charset utf8; -- 选择db30库 use db30; -- 删除部门表, 如果存在 drop tab
数据库经典查询23道题（附答案）
热门推荐
偶尔有点小迷糊的博客
06-11 1万+
数据库经典查询23道题（附答案，仅供参考） 1.查询平均工资最高的部门的部门编号、部门名称和该部门的平均工资 select t.deptno,t.num,d.dname from (select deptno,avg(sal) num from emp group by deptno) t, dept d where t.deptno=d.deptno and t.num= (select max(avg(sal)) num from emp group by deptno) 2.查询所有员工的年薪、
MySQL高级部分全集
weixin_47174945的博客
04-07 3168
一、函数 一.字符函数 select LOWER('SQL Course')//转小写 select UPPER('SQL Course')//转大写 select CONCAT('Hello', 'World')//拼接 select SUBSTR('HelloWorld',1,5)//截取 select LENGTH('HelloWorld')//长度 select INSTR('HelloWorld', 'W')//字符出现索引值 select TRIM('H' FROM 'HelloWorld')/
查询每个部门最低工资的雇员信息
JavaNotes的博客
11-27 2871
来源： https://www.2cto.com/database/201506/412236.html
查询各部门中工资最低的雇员信息（不要使用int关键字）、查询工资最高的雇员信息、查询工资高于部门20中所有员工的雇员信息、查询负责管理其他雇员的管理员信息
weixin_44253139的博客
11-02 3156
数据是Oracle数据库默认的数据 各个字段含义： 雇员编号，部门，职位，管理者编号，入职日期，工资，奖金，部门id 要求查询工资最高的雇员信息。 -- 要求查询工资最高的雇员信息。 select max(SAL) from emp --查询得到最高工资 select * from emp where SAL=(select max(SAL) from emp) --以最高工资为条件，查询员工 要求查询工资高于部门20中所有员工的雇员信息。 -- 要求查询工资高于部门20中所有员工的雇员信
MySQL查询操作(2)
njsunll的博客
09-27 889
MySQL查询操作(2) 5 统计查询 5.1 单字段分组统计 eg1: 查询每个部门的人数 eg2: 查询每个部门的部门名称，部门人数，平均工资 如下若使用左连接，我们可以看到结果中少了一个部门，因此正确的做法是使用右连接。 通过右连接解决eg2的问题，并将无人的部门平均工资null设为0输出： 5.2 多字段分组统计 eg: 查询每个部门的部门编号，部门名称，部门人数、平均工资 注：上...
子查询替换与EXISTS应用
"这篇资料主要讨论了如何在数据库查询中替换不同形式的子查询，特别是关注于使用EXISTS和NOT EXISTS谓词的子查询。它强调了所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都可以用带EXISTS谓词的子查询等价替换，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值