数据库SQL语言实战（二）

检索查询

题目一

题目二

题目三

题目四

题目五

题目六

题目七

题目八

题目九（本篇最难的题目）

分析

实现（两种方式）

模板

总结

检索查询

按照要求查找数据库中的数据

题目一

找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生）

select sid,name
from pub.student
where sid in (
    (
        select sid 
        from pub.student
    )
    minus
    (
        select distinct sid
        from pub.student_course
    )
)

关键点：

1、没有选修任何课的学生=所有学生-选了课的学生

所有学生：select sid from pub.student

选了课的学生：select distinct sid from pub.student_course

2、减号就是minus关键字

题目二

找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。

select sid,name
from pub.student
where sid in(
    (
        select distinct sid
        from pub.student_course
        where cid in (
            select cid
            from pub.student_course
            where sid='200900130417'
        )
    )
    minus
    (
        select sid
        from pub.student
        where sid='200900130417'
    )
)

关键点：

找“200900130417”学号的学生选修的全部课程——》找pub.student_course中cid在全部课程中的选课记录——》拿出sid在pub.student中进一步获取name

题目三

找出至少选修了一门其先行课程号为“300002”号课程的学生的学号、姓名。

 select sid, name
 from  pub.student
 where sid in(
    select sid
    from pub.student_course
    where cid in ( 
        select cid
        from pub.student_course
        where pcid='300002'
    )
)

关键点：

1、找先行课程号为“300002”号的课程——》找pub.student_course中cid在前面课程中的选课记录——》拿出sid在pub.student中进一步获取name

2、题目二和题目三的思路是相似的

题目四

找出选修了“操作系统”并且也选修了“数据结构”，但是没有选修“程序设计语言”的学生的学号、姓名。

select sid，name
  from pub.student
  where sid in
  (
    (
      select sid
      from pub.student_course
      where cid=(
        select cid
        from pub.course
        where name='操作系统'
      )
    )
    intersect
    (
      select sid
      from pub.student_course
      where cid=(
        select cid
        from pub.course
        where name='数据结构'
      )
    )
    minus
    (
      select sid
      from pub.student_course
      where cid=(
        select cid
        from pub.course
        where name='程序设计语言'
      )
    )
  )

关键点：

1、干了A，并且干了B，但没有干C=A的结果交 B的结果减 C的结果

2、intersect关键字：取交集

题目五

找出姓名叫“李龙”的学生的学号及其选修全部课程的课程号、课程名和成绩。

create view test3 as
select sid，cid,pub.course_name,score
from pub.student natural join pub.student_course natural join pub.course
where pub.student_name='李龙'

create view test2_05 as
  select sid,cid,name,score
  from pub.student_course natural join pub.course
  where sid in
  (
    select sid
    from pub.student
    where name='李龙'
  )

关键点

1、第一个代码是错误的。因为pub.student和pub.course中都有name属性，而这两者自然连接显然是不合理的

2、将三个自然连接拆为：两个自然连接+一个嵌套select语句

题目六

查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。

  select sid,name,score
  from pub.student_course natural join pub.student
  where class='2010' and dname='计算机科学与技术学院' and cid=(
    select cid
    from pub.course
    where name='操作系统'
  )

关键点：

1、同样考察自然连接的应用

题目七

查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name

select sid,name
from pub.student
where name not like '张%'
and name not like '李%'
and name not like '王%'

关键点：

1、不姓A=not like ‘A%'

2、名字不叫B=not like ‘%B’

题目八

找出有间接先行课的所有课程的课程号、课程名称。

select cid ,name
from pub.course
where fcid in (
  select cid
  from pub.course
  where fcid is not NULL
)

关键点：

1、不为空=is not NULL

2、间接关系的处理（例：祖孙关系）

题目九（本篇最难的题目）

找出选修了所有课程的学生的学号、姓名。

分析

第一想法：拿出所有课程——》令pub.student_course（选用sid，cid）除取所有课程（cid）——》得到sid，这个sid'所选用的课程就是全部课程

问题是：SQL语言不提供直接可以用的除运算

实现（两种方式）

方法一、

select sid,name
from pub.student
where not exists(
  select cid
  from pub.course
  where not exists(
    select * 
    from pub.student_course
    where pub.student_course.cid=pub.course.cid and 
    pub.student_course.sid=pub.student.sid
  )
)

方法二、

select sid,name
from pub.student
where not exists(
  (
    select cid
    from pub.course
  )
  minus
  (
    select cid
    from pub.student_course
    where pub.student_course.sid=pub.student.sid
  )
)

模板

查询做了所有（至少）A的B

SELECT * FROM S
WHERE NOT EXISTS(
	SELECT * FROM C WHERE C.`cno` IN ('C001','C002')
	AND NOT EXISTS(
		SELECT * FROM SC
		WHERE SC.`cno` = C.`cno` AND SC.`sno` = S.`sno` 
		
	)
)