数据库原理与应用系列_02子查询

2022-05-27 306

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 出现在其他语句中的select语句，称为子查询或内查询外部的查询语句，称为主查询或外查询

一、子查询概述

定义：

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

为什么要学习子查询？

例：查询工资比张三高的员工信息

 -- 查询工资比张三高的员工信息
 -- 1.查询张三的工资
 select sal from emp where ename='张三';
 -- 2. 比3000元高的员工信息
 select * from emp where sal >3000;

张三的工资是3000元，这3000元是作为条件，在第二步当作为条件使用。

这里需要两个步骤，而使用子查询之后，使用一步就可以解决该问题。

因此子查询要解决的问题是：问题不能一步求解的问题。

二、子查询的使用

子查询的语法：select语句的嵌套。

 SELECT select_list 
 FROM table 
 WHERE expr operator (    
    SELECT select_list 
    FROM table
 )

使用子查询解决：查询工资比张三高的员工信息

 select * 
 from emp 
 where sal > (
      select sal
      from emp 
      where ename='张三'
);

子查询注意的10个问题：

【1】子查询语法中的小括号

select e.*
from sal > (select min(sal) from emp where deptno=30)

【2】子查询的书写风格

【3】可以使用子查询的位置：where，select，having，from

【4】不可以使用子查询的位置：group by

【5】强调：from 后面的子查询语句

【6】子查询和主查询可以不是同一张表

只要子查询的结果在主查询当中可以使用就行了。

例子：查询部门名称是行政部的员工信息

--方法一：使用子查询
-- 这里子查询是使用表dept，而外层则是使用emp表
select * 
from emp 
where deptno=(select deptno
        from dept 
        where dname='行政部')

--方法二：使用多表查询
-- 从理论上面来讲，使用多表查询好于子查询
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.dname='行政部'

【7】一般不在子查询中，使用排序（因为没有意义）；

但是在Top-N分析问题中，必须对子查询排序

Top-N分析问题：指的是按照某个规律排序以后，取出前几条记录。

【8】一般先执行子查询，再执行主查询，但相关子查询例外

【9】单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

【10】注意子查询中是null值问题：子查询分为单行子查询和多行子查询

1、单行子查询

单行子查询：子查询只返回一条记录

单行操作符：= , > , >= ,< <= ，<>

单行子查询事例：

select * 
from emp 
where job=(select job from emp where empno=7655) and 
    sal>(select sal from emp where empno=6088);

-- 查询工资最低的员工的信息
select * 
from emp 
where sal=(select min(sal) from emp) -- 在子查询当中也可以使用分组函数

-- 查询最低工资大于20号部门最低工资的部门号和部门的最低工资：
--先得到每个部门的最低工资，在查出20号部门的最低工资，然后筛选
select deptno ,min(sal)
from emp 
group by deptno 
having min(sal) > (select min(sal)
            from emp 
            where deptno=20)

2、多行子查询

多行子查询：子查询返回多条记录

多行操作符：

【1】in

【1】IN：等于列表中的任何一个

例子：查询部门名称是销售部或者财务部的员工信息

方法一：子查询+in

-- 方法一：子查询+in 
select * 
from emp 
where deptno in (select deptno 
        from dept
        where dname='销售部' or dname='财务部')

方法二：使用多表查询

select e.* -- 查询员工的所有信息
from emp e,dept d
where e.deptno=d.deptno and (d.dname='销售部' or d.dname='财务部')

【2】any

【2】ANY：和子查询返回的任意一个值比较

ANY在使用中有如下三种使用形式：

=ANY：表示与子查询中的每个元素进行比较，功能与IN类似（然而<>ANY不等价于NOT IN）

>ANY：比子查询中返回结果的最小的要大（还包含了>=ANY）
<ANY：比子查询中返回结果的最大的要小（还包含了<=ANY）

例子：查询工资比30号部门任意一个员工高的员工信息

方法一：

select e.*
from sal > any (select sal from emp where deptno=30)

方法二：

-- 分析：大于任意一个，只要大于最小的那个就行了
select e.*
from sal > (select min(sal) from emp where deptno=30)

【3】all

ALL操作符有以下三种用法：

<>ALL：等价于NOT IN（但是=ALL并不等价于IN）

>ALL：比子查询中最大的值还要大（还包含了>=ALL）

<ALL：比子查询中最小的值还要小（还包含了<=ALL）

【3】ALL：和子查询返回的所有值比较

例子:查询工资比30号部门所有员工高的员工信息

方法一：使用子查询的any

select e.*
from sal>  any ( select  sal from emp where deptno=30)

方法二：大于子查询的最大值即可

select e.*
from sal>  ( select  max(sal) from emp where deptno=30)

在where后面使用子查询

 select * 
 from emp 
 where sal>(
      select sal
      from emp 
      where ename='张三'
);

在select后面使用子查询

在select后面使用子查询必须是单行子查询

-- 错误写法，因为此时子查询是多行记录
select empno,ename,sal,(select job from emp )
--正确写法：
select empno,ename,sal,(select job from emp where empno=7893) from emp;

在having后面使用子查询

select deptno,avg(sal)
from emp
group by deptno 
having avg(sal) > (select max(sal)
          from emp
          where deptno=30);

在from后面使用子查询

select *
from ( select empno,ename,sal from emp )

--例一：查询员工信息:员工号，姓名，月薪
select * 
from (select empno,ename,sal from emp)
--例二：查询员工信息:员工号，姓名，月薪，年薪
select * 
from (select empno,ename,sal,sal*12 as annsal from emp)

【4】exists [考]

【1】当EXISTS语句中的查询结果为非空时返回值为True

【2】当EXISTS语句中的查询结果为空时返回值为False

【3】exists是存在一个即可返回真，not exists是全都不存在才返回真

EXISTS也可进行相关的子查询，带有EXISTS的子查询不返回任何实际的数据，它只得到逻辑值‘真’或者‘假’。当子查询的结果集合为非空时，外层WHERE子句返回针织，否则返回假值。

NOT EXISTS 则相反，如果子查询的结果为非空，而外面使用了NOT EXISTS，则得到的结果为假。

-- 关于exists和 not exists
 SELECT EMPID,EMPNAME FROM EMPLOYEES
 WHERE NOT EXISTS
 (
  SELECT EMPRESPECT FROM EMPLOYEES
  WHERE NOT EXISTS
  (
    SELECT * FROM ORDERS WHERE 1=2
  )
 )

如果想要得到一张表的框架，而不得到表的结构，则也可以使用上面的方法：

SELECT EMPID,EMPNAME INTO TEMP123 FROM EMPLOYEES
WHERE 1=2

使用嵌套EXISTS：

最后一个示例是双嵌套 NOT EXISTS查询。也就是说，它在NOT EXISTS子句中还有一个NOT EXISTS子句。在形式上，它回答了“一个城市是否存在一个不在商店里的商店”﹖但更容易说，嵌套的不存在回答了问题“X对所有Y都是真的吗?”。

SELECT  A FROM RRR R1
WHERE NOT EXISTS
(
  SELECT B,C FROM TTT
  WHERE  NOT EXISTS
  (
    SELECT * FROM RRR R2
    WHERE R2.B=TTT.B AND R2.C=TTT.C AND R2.A=R1.A
  )
)

双重否定等于肯定：

SELECT  A FROM RRR R1
WHERE NOT EXISTS
(
  SELECT B,C FROM TTT
  WHERE  NOT EXISTS
  (
    SELECT * FROM RRR R2
    WHERE (R2.B+R2.C)=(TTT.B+TTT.C) AND R2.A=R1.A
  )
)

 --查询购买了所有产品的客户姓名
 -- 出来的结果是所有的公司，用了三重否定
 -- 如果在三层嵌套中任意两层使用not 剩下一层使用exists，则得到的就是正确的结果：即没有一家公司购买的是所有产品
 SELECT CUSTNAME 
 FROM CUSTOMERS C1
 WHERE NOT EXISTS
 (
  SELECT * FROM ORDERS O1
  WHERE NOT EXISTS
  (
    SELECT * FROM ORDERDETAIL O2
    WHERE  EXISTS
    (
      SELECT * FROM PRODUCTS P
      WHERE P.PNAME=O2.PNAME AND O1.ORDERID=O2.ORDERID AND O1.CUSTID=C1.CUSTID
    )
  )
 )

3、子查询中的空值问题

【1】单行子查询当中的空值问题

必须使用in或者

由于条件为假，因此子查询不返回任何值。

【2】多行子查询当中的空值问题

例子：查询不是老板的员工

在emp表当中，有一列MGR，代表该员工的老板的编号

为什么not in得不到正确的结果呢：

因为not in 等价于不等于集合的所有元素

a not in(10,20,null)
-- 等价于
a != 10 and a!=20 and a!=null
-- 而a!=null 这个条件永远为假 ，因为判断一个值等不等于空值，
--不能使用等于或者不等于，因为永远是不等于。

因此多行子查询中如果存在空值，就不能使用not in 。但是如果子查询的结果包含空值，是可以使用in，因为这里in等同于any，any是取集合当中的一个值，不用和集合中的所有值进行比较。

在多行子查询当中，且包含空值，使用not in 的正确写法如下：

select *
from emp
where empno not in( 
          select mgr
          from emp
          where mgr is not null)

数据库原理与应用系列_02子查询

一、子查询概述

二、子查询的使用

1、单行子查询

2、多行子查询

【1】in

【2】any

【3】all

3、子查询中的空值问题

热门文章

最新文章

相关课程

相关电子书

相关实验场景