热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

跟我一起学Oracle11g【9】

前言在前面2个章节,我们比较详细的介绍了一些SQL语句的基本用法,但是在我们实际的项目开发中,其实很多时候这些基本的用法远远不能满足我们项目的需求,这个时候就需要我们的嵌套查询。在SQL语句中,一个select-from-where语句称为一耳光查询快。将一个

前言 在前面2个章节,我们比较详细的介绍了一些SQL语句的基本用法,但是在我们实际的项目开发中,其实很多时候这些基本的用法远远不能满足我们项目的需求,这个时候就需要我们的嵌套查询。 在SQL语句中,一个select-from-where语句称为一耳光查询快。将一个

前言

在前面2个章节,我们比较详细的介绍了一些SQL语句的基本用法,但是在我们实际的项目开发中,其实很多时候这些基本的用法远远不能满足我们项目的需求,这个时候就需要我们的嵌套查询。

在SQL语句中,一个select-from-where语句称为一耳光查询快。将一个查询快嵌套在另外一个的where子句或having 短语的条件的查询称为嵌套查询(Nested Query)。

比如,先举一个简单的例子:

select Sname  --------------外查询语句块------- 
from Student
where Sno in  --------------外查询语句块------- 
      (      --------------内查询语句块-------     
         select Sno
          From Sc
          where Cno=2
             --------------内查询语句块------- 
      );

查询语句块分为外查询语句或叫父查询以及内查询或子查询。

NOTE:SQL语言允许多层嵌套查询,即一个子查询中还可以嵌套其他子查询。需要特别指出的是,子查询的select语句中不能使用order by字句,order by 只能对最终查询结果排序。

一。带有in谓词的子查询

在嵌套查询中,子查询往往是一个集合,所以谓词in是嵌套查询中最经常使用的谓词。

【例1】查询与“XiaoHong”在同一个系的学生

OK,我们一步一步的来分解这个查询步骤:

①确定“XiaoHong”所在的系

select sdept
from student 
where sname='XiaoHong'

得到的结果是:CS。

②查找所有在CS系学习的所有学生

select sno,sname,sdept 
from student 
where sdept='CS';

ok,得到如下结果:

③合并操作。

现在我们只需要把上面的操作合并在一起就好了!

select sno,sname,sdept 
from student 
where sdept in 
(
  select sdept
  from student 
  where sname='XiaoHong'
);

得到的结果也是一样。

本例中,因为子查询的查询条件不依赖于父查询,所以称为不相关查询

当然本例中也还可以有其他解法:自连接方法如下:

select s1.sno,s1.sname,s1.sdept 
from student s1,student s2 
where s1.sdept=s2.sdept and s2.sname='XiaoHong';

可见,一个查询有很多种方法,当然不同的方法执行的效率可能会有很大的不同。

【例2】查询选修了课程名为”Math“的学生学号和姓名

看似这一句很短,其实这里有2个子查询。我们需要先弄清楚里面的关系,然后在来进行解答

思路如下:

1).课程名在course表中才有,所以我们从course表中选出课程名为Math的课程号(Cno)

2).选出课程号之后,在在SC表中,找到课程号所对应的学生号

3).在student表中找出对应的学号和学生名字。

select sno,sname   --第三步,最后从student表中取出数据出来。 
from student 
where sno in
(
     select sno --第二步。。找出SC关系表中的选修了2号课程的学生学号 
     from sc 
     where cno in
           (
               select cno     -- 第一步。。找出course表中的课程名为math的课程号。结果为2.
         from course 
         where cname='Math'
       )
);

结果如下:

代替方法:

select student.sno,sname from 
student,sc,course 
where course.cname='Math' 
and course.cno=sc.cno and sc.sno=student.sno;

结果也是一样滴。

二。带有比较运算符的子查询

带有比较运算符的子查询是指父查询之间用比较运算符进行连接。当用户能确切知道内存查询返回的是单&#20540;时,可以用>,<,>=,<=,!=,<>等等比较运算符。

【例3】和例1 是一样的:查询与“XiaoHong”在同一个系的学生

因为一个学生只可能在一个系学习,也就是说内查询的结果是一个&#20540;,因此可以用“=”代替“in”

如下:

select sno,sname,sdept 
from student 
where sdept =
(
  select sdept
  from student 
  where sname='XiaoHong'
);

这样 也能得到和例1的结果。

需要注意的是,子查询一定要跟在比较符之后,下面写法是错误的:

----------------错误写法---------------------
select sno,sname,sdept 
from student 
where 
(
  select sdept
  from student 
  where sname='XiaoHong') =sdept;
----------------错误写法---------------------

【例4】找出每个学生超过他选修课课程平均成绩的课程号

这个怎么理解呢?我们先把结果写出来,然后再来剖析它。

select sno,cno 
from sc x 
where grade>=
(
    select avg(grade) 
    from sc y 
    where y.
    sno=x.sno);

x是Sc表的别名,y也是一样。内存查询是求一个学生所有选修课程平均成绩,至于是哪个学生的平均成绩要看s.sno的&#20540;。,而该&#20540;是和父查询有关的,所以这类查询叫做相关子查询。

执行过程可能如下:

①从外层查询中取出SC表中的一行(也就是元组 x),将x的&#20540;sno

select avg(Grade) 
from sc y 
where y.sno=1;

得到结果是98.5

②执行内查询得到的结果是:98.5,所以得到外查询是:

select sno,cno 
from sc x 
where Grade>=98.5;

得到结果如下:

然后,外层查询取出下一个元组重复做上面的动作。。。

得到的结果是:

三。带有any(some)或all谓词的子查询

子查询返回单&#20540;可以用比较算术法,但是返回多&#20540;时要用any(有些系统用some)或者all谓词修饰符。而是用any或all谓词时则必须同时是用比较运算符。
>any 大于子查询结果中的某个&#20540;
>all 大于子查询结果中的所有&#20540;

>=any大于等于子查询结果中的某个&#20540;
<=any小于等于子查询结果中的某个&#20540;
>=all 大于等于子查询结果中的所有&#20540;
<=all 小于等于子查询结果中的所有&#20540;

=any 等于子查询结果中的中的某个&#20540;
=all 等于子查询结果中的所有&#20540;(通常没有实际意思)

!=(或<>)any 不等于子查询结果中的某个&#20540;
!=(或<>)all 不等于子查询结果中的的任何一个&#20540;

【例5】查询其他系中比计算机科学系某一个学生年龄小的学生姓名和年龄

SQL语句如下:

select sname,sage 
from student
where sage < any
(
    select sage 
        from student 
    where sdept='CS'
) and sdept <> 'CS'; -- 注意这是父查询的条件

数据库执行执行此查询的时候,首先处理子查询,找出CS系中所有学生的年龄,找出一个集合(18,22)。任何处理父查询,找出所有不是CS系并且年龄小于18或22的学生即可。

本查询也可以用聚集函数来实现。首先用子查询查出CS系中最大年龄(22),任何在父查询中找出所有非CS系并且年龄小于22岁的学生即可。

select sname,sage 
from student
where sage <
(
    select max(sage)
    from student
    where sdept='CS'
) and sdept<>'CS'

也可以得到上面的结构。结果如下:

【例6】查询其他系中比计算机科学系所有学生年龄都小的学生姓名和年龄

经过上面的分析,这个很简单,如下

select sname,sage 
from student
where sage 
(
    select sage
    from student
    where sdept='CS'
) and sdept<> 'CS';


这里需要向大家说声对不起,刚开始构建数据的时候,没有构建好,所以这里查询为空,为了试验的完整性,我这里修改一下数据结构。

update student set sage=17 where sname='XiaoZhang';

ok,我们现在在执行刚才的操作,可以得到如下结果。

本查询也可以使用聚集查询来实现,如下:

select sname,sage
from student
where sage <
(
    select min(sage)
    from student
    where sdept='CS'
) and Sdept !='CS';

事实上,聚集函数实现子查询通常比直接用all或any的效率更好。对应关系表如下:

= <>或!= < <= > >=
ANY IN == <=Max >Min >=Min
ALL == NOT IN <=Min <=Max
四。带有exists谓词的子查询

带有exists谓词的子查询不返回任何数据,只产生逻辑真或假,也就是true或false。

【例6】查询所有选修了1号课程的学生姓名

select sname 
from student
where exists
(
    select *
    from sc
    where Sno=Student.Sno and Cno=1
)

那上面的执行流程是怎么样的呢?

①从外查询student表中取出一行数据

②把这条数据的Sno和内查询Sc表中的Sno进行比较

③若比较结果为真,则把这数据的sname放入一个结果集合中。

④然后再取student表的吓一条数据,直到取完为止。

使用exists量词后,若内存结果非空,则外层的where字句返回真&#20540;,否则返回假&#20540;。

由于exists引出的子查询,起目标列表达式通常都是“*”,因为带有exists的子查询只返回true或false,给出列名无具体意思。

exists谓词相对应的是not exists。使用not exists后,如果内存查询结果为空,则外层的where字句返回真&#20540;,否则返回假&#20540;。

【例7】查询没有选修了1号课程的学生姓名

select sname 
from student
where not exists
(
    select *
    from sc
    where Sno=Student.Sno and Cno=1
)

一些带exists或not exists谓词的子查询不能被其他形式的子查询等价替换,但是其他带有in谓词、比较运算符、any、all谓词的子查询都能用exists谓词的子查询替换。


推荐阅读
  • 详解MyBatis二级缓存的启用与配置
    本文深入探讨了MyBatis二级缓存的启用方法及其配置细节,通过具体的代码实例进行说明,有助于开发者更好地理解和应用这一特性,提升应用程序的性能。 ... [详细]
  • 利用Git GUI将本地项目同步至GitHub的方法
    GitHub作为开发者不可或缺的工具,不仅提供了丰富的开源项目资源,还极大地便利了个人项目的管理和版本控制。本文将详细介绍如何使用Git GUI工具将本地开发的项目上传至GitHub。 ... [详细]
  • 本文提供了一个SQL脚本,用于在Microsoft SQL Server中创建一个数据字典视图,该视图详细列出了表名、表描述、字段名称、字段描述、字段类型、字段大小、字段精度、是否可为空、默认值以及是否为标识或主键等信息。 ... [详细]
  • MyBatis入门指南:环境搭建与基础配置详解
    本文详细介绍了MyBatis的基础配置流程,包括在Maven项目中添加MyBatis依赖、IDEA中配置数据库连接、导入SQL脚本以及编写mybatis-config.xml配置文件等关键步骤。 ... [详细]
  • 本文基于最新版SQLite 3.33.0(发布于2020年8月20日),详细介绍如何使用ORDER BY语句进行数据排序,包括单列和多列排序的方法。 ... [详细]
  • 本文探讨了Android系统中联系人数据库的设计,特别是AbstractContactsProvider类的作用与实现。文章提供了对源代码的详细分析,并解释了该类如何支持跨数据库操作及事务处理。源代码可从官方Android网站下载。 ... [详细]
  • 本文详细介绍了PHP中的几种超全局变量,包括$GLOBAL、$_SERVER、$_POST、$_GET等,并探讨了AJAX的工作原理及其优缺点。通过具体示例,帮助读者更好地理解和应用这些技术。 ... [详细]
  • 本文详细介绍了在MyBatis框架中如何通过#和$两种方式来传递SQL查询参数。使用#方式可以提高执行效率,而使用$则有助于在复杂SQL语句中更好地查看日志。此外,文章还探讨了不同场景下的参数传递方法,包括实体对象、基本数据类型以及混合参数的使用。 ... [详细]
  • 本文通过一系列实验,探讨了Oracle 11g数据库中密码错误验证延迟特性对用户登录速度的影响。实验旨在验证当某个用户因输入错误密码而触发延迟时,是否会影响其他用户的正常登录速度。 ... [详细]
  • SQL查询与事务管理:深入解析
    本文详细介绍了SQL查询的基本结构和高级特性,包括选择、分组查询以及权限控制等内容,并探讨了事务管理中的并发控制策略,旨在为数据库管理员和开发人员提供实用指导。 ... [详细]
  • PHP 图形函数中实现汉字显示的方法
    本文详细介绍了如何在 PHP 的图形函数中正确显示汉字,包括具体的步骤和注意事项,适合初学者和有一定基础的开发者阅读。 ... [详细]
  • 2023年1月28日网络安全热点
    涵盖最新的网络安全动态,包括OpenSSH和WordPress的安全更新、VirtualBox提权漏洞、以及谷歌推出的新证书验证机制等内容。 ... [详细]
  • 本文由公众号【数智物语】(ID: decision_engine)发布,关注获取更多干货。文章探讨了从数据收集到清洗、建模及可视化的全过程,介绍了41款实用工具,旨在帮助数据科学家和分析师提升工作效率。 ... [详细]
  • 本文深入探讨了MySQL中的高级特性,包括索引机制、锁的使用及管理、以及如何利用慢查询日志优化性能。适合有一定MySQL基础的读者进一步提升技能。 ... [详细]
  • 将XML数据迁移至Oracle Autonomous Data Warehouse (ADW)
    随着Oracle ADW的推出,数据迁移至ADW成为业界关注的焦点。特别是XML和JSON这类结构化数据的迁移需求日益增长。本文将通过一个实际案例,探讨如何高效地将XML数据迁移至ADW。 ... [详细]
author-avatar
manassatromble
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有