MySQL练习题50道：查询没学过”张三”老师授课的同学的信息

背景

MySQL经典练习题及答案，常用SQL语句练习50题

题目描述

查询没学过”张三”老师授课的同学的信息

答案

s_id s_name s_birth s_sex
06 吴兰 1992-03-01 女
08 王菊 1990-01-20 女

题目解析

这个题目采用取反的方法，他要求的是没有学过，可以直接算出学过的学生，然后取反。

Step1. 查询学过”张三”老师授课的同学的信息

1
2
3
4
5
6
7
8
9
 select stu.*, c.c_name, t.t_name
from student stu,
     course c,
     score sc,
     teacher t
where c.c_id = sc.c_id
  and t.t_id = c.t_id
  and sc.s_id = stu.s_id
  and t.t_name = '张三';

Step2. 取反

取反有两种方法not in 和not exists,这两种是不一样的，但是结果是一样的.

  • not in
    not in 后边跟的是s_id，完整sql如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
select *
from student
where s_id not in (
    select stu.s_id
    from student stu,
        course c,
        score sc,
        teacher t
    where c.c_id = sc.c_id
     and t.t_id = c.t_id
     and sc.s_id = stu.s_id
     and t.t_name = '张三'
 );
  • not exists

这个后边跟的是一个子查询，sql如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select *
from student
where not exists (
  select 1
  from 
      (select stu.s_id
        from student stu,
             course c,
             score sc,
             teacher t
        where c.c_id = sc.c_id
          and t.t_id = c.t_id
          and sc.s_id = stu.s_id
          and t.t_name = '张三') t
  where t.s_id = student.s_id
);