• SQL课堂笔记嵌套查询


         2017.11.15
    六:嵌套查询
     嵌套查询概述:
      一个select-from-where 语句称为一个查询块
      将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询
     
     
     例1:查询选修了2号课程得学生姓名:
      内连接:select sname from student,sc where student.sno=sc.sno and cno=2
            或:select sname from student where sno in   --外层查询/父查询
         (select sno from sc where cno=2)   --内层查询/子查询
      
     引出子查询得谓词:
       带有in谓词的子查询  
       带有比较运算符的子查询
       带有any或all谓词的子查询
       带有exists谓词的子查询
     1.带有in谓词的子查询 

      例2:查询与'刘晨'在同一个专业学习的学生:(涉及2张表)
       select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨')

          查询与'刘晨'在同一个专业学习的学生但是不包括'刘晨':
       select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨') and sname<>'刘晨'
     
      例3:查询选修了课程名为'信息系统'的学生学号和姓名:(涉及3张表)
       select sno,sname from student where sno in  --3.最后在student关系中取出sno和sname
        ( select sno from sc where cno in  --2.然后在sc中找出选修了课程的学号和姓名
         (select cno from course where cname='信息系统'));  --1.首先在course关系中找出'信息系统'的课程号
       select sno,sname from student,sc,course where student.sno=sc.sno....

     2.带有比较运算符的子查询

      例4:查询与'刘晨'在同一个专业学习的学生: 
       select sno,sname,sdept from student where sdept = (select sdept from student where sname='刘晨')

     3.带有any或all谓词的子查询:
      
      >any  >min
      >all
      <any          <max
      <all
      >=any  >=min
      >=all
      <=any         <=max
      <=all
      =any
      =all
      !=(<>)any      不存在
      !=(<>)all not in
      
      例5:查询其他专业中比计算机任意一个(其中某一个)学生年龄小的学生姓名和年龄
       select sname,sage from student where sage<any(select sage from student where sdept='计算机') and sdept<>'计算机';

      例6:查询其他专业中比计算机任意一个(其中某一个)学生年龄小的学生姓名和年龄(用聚合函数):
       select sname,sage from student where sage<(select (max)sage from student where sdept='计算机') and sdept<>'计算机';

      例7:查询...

     4.带有exists谓词的子查询:
      1.exists谓词:
       不返回任何数据,只产生逻辑真值'true'或逻辑假值'false'
        若内层查询结果非空,则返回真值
        若内层查询结果为空,则返回假值 
       由exists引出的子查询,其目标列表达式通常都用*,因为带exists的子查询只返回真或假值,给出列名五实际意义

      2.not exists谓词:
       不返回结果集为真
       

      例8:查询所有选修了1号课程的学生姓名:
       select sname from studnet where sno in (select sno from sc where cno=1)
       select sname from studnet where exists(select * from sc where sc.sno=student.sno and cno='1' )

      例9:查询没有选修1号课程的学生姓名:
       select sname from studnet where not exists(select * from sc where sc.sno=student.sno and cno='1')

       查询与'刘晨'在同一个专业学习的学生:
       select sno,sname,sdept from studnet s1 where exists (select * from student s2 where s2.sdept=s1.sdept and sname='刘晨')

      查询选修了全部课程的学生姓名:
        法1:select sname from student where not exists
         (select * from course where not exists
           (select * from sc where sc.sno=studnet.sno and sc.sno=course.cno))
       
        法2:select sname from student where sno in
         (select sno from sc group by sno having count(*)=(select count(*) from course))

    练习:

    use students
    go
    --1、用嵌套查询所有比“王敏”大的学生的姓名和年龄。
    select sname,sage from student where sage >all (select sage from student where sname='王敏')
     
    --2、用嵌套查询选修了3号课程的学生姓名。
    select sname from student where sno in (select sno from sc where cno=3)

    --3、用嵌套查询选修了课程名为“数学”的学生学号和姓名。
    select sno,sname from student where sno in (select sno from sc where sdept='数学')

    --4、查询其他系中比电子系任意一个学生年龄小的学生姓名和年龄。
    select  sname,sage from student where sage<any (select sage from student where sdept='电子') and sdept<>'电子'

    --5、查询其他系中比电子系所有学生年龄都小的学生姓名及年龄。
    -select sname,sage from student where sage <all(select sage from student where sdept='电子') and sdept<>'电子';
    select sname,sage from student where sage <(select MIN(sage) from student where sdept='电子') and sdept<>'电子';

    --6、用EXISTS嵌套查询所有选修了1号课程的学生姓名。
    select sname from student where exists(select * from sc where sc.sno=student.sno and cno=1)
     
    --7、查询至少选修了一门课程的学生姓名。
    --select sname from student where not exists(select * from sc where sc.sno=student.sno and sdept=null)
    select sname from student where sno in (select sno from sc)

    --8、查询选修了两门及以上课程的学生姓名。
    select sname from student where sno in (select sno from sc group by sc.sno having count(*)>=2 )
    --Select sname from student a join sc b on a.sno=b.sno where having count(b.sno)>=2

    --9、查询没有选修任何课程的学生姓名。
    select sname from student where not exists(select * from sc where student.sno=sc.sno)
    --或select sname from student where not in (select sno from sc )

    --10、查询至少被选修了二次的课程号及课程名称。
    select cno,cname from course where cno in (select cno from sc group by cno having count(*)>=2 )

    --11、查询选修了全部课程的学生姓名。
    select sname from student where not exists
         (select * from course where not exists
           (select * from sc where sc.sno=student.sno and sc.sno=course.cno))

    --12、查询所有学生都选修了的课程信息。
    select cno,cname from course where not exists
         (select * from student where not exists
          (select * from sc where sc.sno=student.sno and sc.cno=course.cno))

  • 相关阅读:
    广义斐波那契数列 矩阵乘法
    GCD
    [SDOI2008]沙拉公主的困惑 线性筛_欧拉函数_逆元_快速幂
    [SDOI2008]仪仗队 欧拉函数
    洛谷P2617 Dynamic Rankings 主席树 单点修改 区间查询第 K 大
    洛谷P3919 【模板】可持久化数组(可持久化线段树/平衡树)
    Codevs 3269 混合背包
    洛谷P3834 【模板】可持久化线段树 1 主席树
    矩形面积求并 扫描线 + 过不去
    灾后重建 Floyd
  • 原文地址:https://www.cnblogs.com/TuringShine/p/7843734.html
Copyright © 2020-2023  润新知