3.4.3 嵌套查询

3.4.3 嵌套查询

一．带有in谓词 的  子查询

用自身连接完成        .note

select s1.sno, s1.sname,  s1.sdept 

 from student s1, student s2

where s1.sdept=s2.sdept  and s2.sname='刘晨'

 

student 表

39 查询 与 刘晨 在同一个系  学习的学生.note

---

假设 一个学生 只可能在一个系,,并且   属于一个系

此时 in    可用    =

 

 

select sno, sname, sdept  from student

where sdept =(

   select sdept  from student  where sname='刘晨' )

 

40 查询 选修了 课程名为   信息系统  的 学生 学号   和  姓名.note

 

select sno,sname

from student

where sno  in

( 

      select sno

      from sc

      where cno in

                       (

                           select cno

                           from course

                             where cname='信息系统'  )

) 

1.   course 表                     '信息系统'的   课程

2   sc   表             选修3号课程                                  的  学生学号

3    student  表                                             取出  sno  sname

用 连接查询   40.note

 

select student.sno,  sname

from student,  sc  ,  course

where 

            student.sno=sc.sno       and

            course.cno=sc.cno        and

            course.cname='信息系统'

 

查询 选修了 2号课程 的 学生  的 姓名

select sname

from student

where sno 

in(

   select sno

   from sc

   where cno='2'

)

二．不相关 子查询

39     查询 与 刘晨 在同一个系  学习的学生             不相关  子查询.note

假设 一个学生 只可能在一个系,,并且   属于一个系

此时 in    可用    =

select sno, sname, sdept  from student

where sdept =(

   select sdept  from student  where sname='刘晨' )

p104     查询  选修了 2号课程   的 学生 的 姓名.note

---

40 查询 选修了 课程名为   信息系统  的 学生 学号   和  姓名.note

 

select sno,sname

from student

where sno  in

( 

      select sno

      from sc

      where cno in

                       (

                           select cno

                           from course

                             where cname='信息系统'  )

) 

1.   course 表                     '信息系统'的   课程

2   sc   表             选修3号课程                                  的  学生学号

3    student  表                                             取出  sno  sname

嵌套 39.note

1.

select sdept 

from student

where  sname='刘晨'

2.

select sno,  sname , sdept 

from student

where sdept

in('cs')

法(二  39.note

由里向外

1. 先执行   子查询

 

 

select sno,  sname , sdept 

from student

where sdept='cs'

 

 

 

 

 

2.

select sno,  sname , sdept 

from student

where sdept

in(

  'cs'

)

 

 

 

 

三．相关子查询

41   找出每个学生    超过  他    选修课程 平均成绩 的   课程号.note

---

 

select sno, cno

from sc x

 

where grade>=

(

         select avg(grade)

        from sc y

       where  y.sno=x.sno

)

 

 

四．带有any all谓词的  子查询

 

42   某一个 学生 .note

---

 

select sname, sage

from student

where sage<any

(

  select  sage

   from student

  where sdept='cs' 

)

 

and sdept <>  'cs'

 

 

43   所有学生.note

---

select sname, sage

from student

where sage<all

(

  select  sage

   from student

  where sdept='cs' 

)

 

and sdept <>  'cs'

 

 

 

 

 

 

 

用聚集函数实现  42.note

---

 

 

select sname, sage

from student

where sage<

(

  select max(sage)

   from student

  where sdept='cs' 

)

 

and sdept <>  'cs'

 

 

 

 

      聚集函数 43.note

---

46   查询 选修了全部课程的   学生姓名.note

---

select sname

 

from student 

 

where  not exists(

 

         select *

         from course

       where not exists

                       (

                      select *

                      from sc

                       where sno=student.sno and  cno=course.cno 

                           )

)

 

 

 

五．带有exist谓词 的子查询

嵌套查询  44    查询  所有选修了  1号课程的  学生 姓名.note

---

 

 

select sname

from student

where  exists

(

      select *

     from sc

     where sc.sno=student.sno   and cno='1'

)

 

 

 

 

 

 

连接运算  44.note

---

 

 

 select sname

     from student,sc

     where sc.sno=student.sno   and cno='1'

 

 

 

45 查询  没有 选修了  1号课程的  学生 姓名.note

---

select sname

from student

where  not  exists

(

      select *

     from sc

     where sc.sno=student.sno   and cno='1'

)

 

 

 

 

39     查询 与 刘晨 在同一个系  学习的学生             不相关  子查询.note

---

假设 一个学生 只可能在一个系,,并且   属于一个系

 

此时 in    可用    =

 

 

select sno, sname, sdept  from student

 

where sdept =(

 

   select sdept  from student  where sname='刘晨' )

 

 

 

 exist 39  .note

---

 

六．集合查询

（一）union 交 or  或

48  .note

---

 

select *

from student

where sdept='cs' or sage<=19

 

 

 

 

 

48   union  并   or.note

---

查询 计算机系 的 学生   及  年龄不大于  19岁的  学生

 

 

select *

from student

where sdept='cs'

 

union

 

select *

from student

where sage<=19

 

 

 

49   .note

---

查询 选修了 1号课程  或  2号 课程的学生

 

select sno

from sc

where cno='1'

union

select sno

from sc

where cno='2'

（二）.intersect 并 and  和

MySQL不支持INTERSECT.note

---

MySQL不支持INTERSECT

 

50  查询 计算机系 的 学生   与  年龄不大于  19岁的  学生.note

 

select *

from student s1

where s1.sdept='cs' and

 exists(

         select *

         from  student s2

         where s2.sage<=19 and s1.sno=s2.sno

 

     )

 

 

 

exists    51.note

---

 

select sc1.sno

from sc sc1

where sc1.cno='1' and  exists

 

    (        select sno

                 from sc sc2

               where  sc1.sno=sc2.sno and sc2.cno='2'

              )

 

 

 

 in   51  查询 选修了 1号课程    和    2号 课程的学生.note

---

select sno

from sc

where cno='1' and  sno in

 

(     select sno

       from student

      where cno='2'

)

 

 

 

 

 

 

（三）except 差 减

 

52    查询 计算机系中  年龄大于  19岁的 学生.note

---

 查询 计算机系中  年龄大于  19岁的 学生

 

 

 

 

select *

from student

where sdept='cs'   and sage>19

 

 

 

MySQL不支持  except.note

---

 

MySQL不支持  except

 

 

52   查询 计算机系中 的学生      与       年龄  不大于  19岁的 学生        的 差集.note

---

 

select *

from student s1

where s1.sdept='cs' and not exists

 

   (               select *

                from student s2

                 where s2.sage<=19 and s1.sno=s2.sno

 )