mysql查询sql语句实现

学生表 ：Student(S(学号),Sname(姓名),Sage(年龄),Ssex(性别))
课程表 ：Course(C(课程编号),Cname(课程名称),T(教师编号))
成绩表 ：SC(S(学号),C(课程编号),score(分数))
教师表 ：Teacher(T(教师编号),Tname(姓名))

img

问题：查询没学过"张三"授课的同学的信息

select st.*
  from Student st
 where not exists (select 1
          from SC, Course c, Teacher t
         where st.s = sc.s
           and sc.c = c.c
           and t.t = c.T#
           and t.T = '01');

大概就这样了

SELECT
    * 
FROM
    Student 
WHERE
    S NOT IN (
    SELECT
        DISTINCT Sc.S 
    FROM
        Course
        LEFT JOIN SC ON SC.C = Course.C
        LEFT JOIN Teacher ON Teacher.T = Course.T 
    WHERE
    Teacher.Tname = '张三' 
    )


select  S,Sname,Sage, Ssex  from Student a
left join SC b with (nolock) on a.s=b.s
left join  Course c with (nolock) on c.c=b.c
left join Teacher d  with (nolock) on d.t=c.t
where d.TnameSname<>'张三'
group by a.S,a.Sname


select DISTINCT student.* from student
join SC on SC.S = student.S
join Course on Course.C = SC.C
join Teacher on Teacher.T = Course.T 
where Teacher.TnameSname !='张三'

或

select DISTINCT student.* from student
join SC on SC.S = student.S
join Course on Course.C = SC.C
where not EXISTS(select 1 from Teacher where Teacher.T=Course.T and Teacher.TnameSname='张三')

select *
from Student a
left join SC b on a.S = b.S
where b.C NOT IN (
select c.C
from Course c
inner join Teacher d on c.T = d.T
)

select * from Student where S in (select S from SC where C not in (select C from Course where T# in (select T form Teacher where TnameSname = "张三")) group by S)

img