比较复杂的数据库查询案例，建表语句和测试数据[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

create DATABASE test
CREATE TABLE Student ( S VARCHAR(64), Sname VARCHAR(64) ,Sage INT, Ssex VARCHAR(2), PRIMARY KEY (S) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE Course ( C VARCHAR(64),Cname VARCHAR(64), T VARCHAR(64) ) engine=InnoDb DEFAULT charset=utf8;
CREATE TABLE SC ( S VARCHAR(64), C VARCHAR(64), score INT) engine=InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE Teacher ( T VARCHAR(64), Tname VARCHAR(64) ) engine=Innodb DEFAULT charset=utf8;

insert into test.course (C,Cname,T) VALUES('001','数学','001');
insert into test.course (C,course.Cname,course.T) values('002','语文','002'),('003','英语','003');

insert into test.sc (sc.C,sc.score,sc.S) values('001',50,'001'),('001',90,'002'),
                                        ('002',77,'001'),('002',99,'002'),
                                        ('003',100,'001'),('003',33,'002');
insert INTO test.teacher (T,Tname) VALUES('001','刘老师'),('002','王老师'),('003','丧老师');
insert INTO test.teacher (T,Tname) VALUES('004','刘老师')
insert into test.student (student.S,student.Sage,student.Sname,student.Ssex) values('01',12,'小明','男'),('02',12,'小红','女');
insert into test.student (student.S,student.Sage,student.Sname,student.Ssex) values('03',13,'小花','女');
insert into test.student (student.S,student.Sage,student.Sname,student.Ssex) values('04',13,'小绿','女');

查询条件：

//15、删除学习“丧老师”老师课的SC表记录：
delete from test.sc where c=
(select c from test.teacher,test.course where teacher.T=course.T and teacher.Tname='丧老师')
        
//14、查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名：
select s,student.Sname from test.student
        where student.s  in (select s from test.sc where c in (select c from sc where s='02')
        group by s having count(*) =(select count(*) from sc where s='02')) and s !='02'
//13、把“SC”表中“刘老师”老师教的课的成绩都更改为此课程的平均成绩：
update Sc set  score =(
        select a.aa from (
                select avg(sc2.score) aa from sc sc2 ,course where sc2.c=course.c
                       ) a)
        where c in 
                (select c from test.course cs inner join test.teacher th on cs.T=th.T and th.Tname='刘老师')
//12、查询至少学过学号为“01”同学所有一门课的其他同学学号和姓名；
select DISTINCT student.S,student.Sname from test.student,test.sc
        where student.s=sc.S
                and sc.c in(select c from test.course where sc.S='01')
//11、查询至少有一门课与学号为“01”同学所学相同的同学的学号和姓名：
SELECT DISTINCT(student.s),student.Sname from test.student,test.sc,test.course
        where student.s = sc.s
                and sc.C in (select c from sc where sc.s='01')
//10、查询没有学全所有课的同学的学号、姓名：
select student.s,student.Sname from test.student,test.sc
        where sc.s=student.s 
                group by student.s,student.Sname
                        having count(sc.c)<(select count(*) from test.course)
//9、查询所有课程成绩小于60的同学的学号、姓名：
select student.s,student.Sname from test.student
        where student.S not in (select student.s from test.student ,sc where student.s=sc.S and sc.score >60);
//8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：

//7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名：
select student.s,student.Sname from test.student,test.sc where student.s=sc.s and sc.c='001'and
        EXISTS (select * from sc as sc_2 where sc_2.S=sc.S and sc_2.c='002')
//6、查询学过“刘老师”老师所教的所有课的同学的学号、姓名：
select student.s,student.Sname from test.student 
        where s  in 
                (select sc.s from sc,test.course,test.teacher where sc.c=course.c and teacher.t=course.t and teacher.Tname='刘老师')
//5、查询没有学过“刘老师”老师可的同学的学号、姓名：
select student.s,student.Sname from test.student 
        where s not in 
                (select sc.s from sc,test.course,test.teacher where sc.c=course.c and teacher.t=course.t and teacher.Tname='刘老师')
//4查询姓‘刘’的老师的个数：
select count(distinct(teacher.Tname)) num from test.teacher where teacher.Tname like '刘%'
//1查询“001”课程比“002”课程成绩高的所有学生的学号
select a.s from 
        (SELECT S,score FROM test.sc WHERE sc.C='001') a ,(select S,sc.score from test.sc where sc.C='002')b 
                where a.score>b.score and a.S=b.S
//1.2查询“001”课程比“002”课程成绩高的所有学生的姓名                        
select * from test.student where student.S=
       ( select a.s from 
                (SELECT S,score FROM test.sc WHERE sc.C='001') a ,(select S,sc.score from test.sc where sc.C='002')b 
                        where a.score>b.score and a.S=b.S)
 //2查询平均成绩大于60分的同学的学号和平均成绩
select sc.s, avg(score) from test.sc group by sc.S having avg(Score)>60 
//3、查询所有同学的学号、姓名、选课数、总成绩    
select student.S,student.Sname,count(sc.C),sum(sc.score)from test.student  
        left outer JOIN test.sc on student.S= sc.S   
                GROUP BY student.S,student.Sname