mysql所有选修课程都及格_MySQL：测试题

一，表关系的练习测试

请创建如下表关系，并建立相关约束

一，创建表结构数据：

创建的话肯定先创建没有关联的表，老师，课程(关联老师)，年级，班级(关联年级)，学生(关联班级)，

班级任职表 (关联老师，课堂)

create table teacher(

tid int primary key auto_increment,

tname varchar(16) not null

);

create table class_grade(

gid int primary key auto_increment,

gname varchar(16) not null unique

);

create table course(

cid int primary key auto_increment,

cname varchar(16) not null,

teacher_id int not null,

foreign key(teacher_id) references teacher(tid)

);

create table class(

cid int primary key auto_increment,

caption varchar(16) not null,

grade_id int not null,

foreign key(grade_id) references class_grade(gid)

on update cascade

on delete cascade

);

create table student(

sid int primary key auto_increment,

sname varchar(16) not null,

gender enum('女','男') not null default '男',

class_id int not null,

foreign key(class_id) references class(cid)

on update cascade

on delete cascade

);

create table score(

sid int not null unique auto_increment,

student_id int not null,

course_id int not null,

score int not null,

primary key(student_id,course_id),

foreign key(student_id) references student(sid)

on delete cascade

on update cascade,

foreign key(course_id) references course(cid)

on delete cascade

on update cascade

);

create table teach2cls(

tcid int not null unique auto_increment,

tid int not null,

cid int not null,

primary key(tid,cid),

foreign key(tid) references teacher(tid)

on delete cascade

on update cascade,

foreign key(cid) references class(cid)

on delete cascade

on update cascade

);

2，插入表数据

插入数据

老师的数据

年级的数据

班级的数据

课程的数据

学生的数据

成绩的数据

老师班级的数据

insert into teacher(tname) values

('张三'),

('李四'),

('王五');

insert into class_grade(gname) values

('一年级'),

('二年级'),

('三年级');

insert into class(caption,grade_id) values

('一年一班',1),

('一年二班',1),

('一年三班',1),

('二年一班',2),

('二年二班',2),

('二年三班',2),

('三年一班',3),

('三年二班',3),

('三年三班',3);

insert into course(cname,teacher_id) values

('生物',1),

('体育',1),

('物理',2),

('数学',2),

('马克思',3),

('外语',3),

('计算机',3);

insert into student(sname,gender,class_id) values

('乔丹','男',1),

('艾弗森','男',1),

('科比','男',2);

insert into score(student_id,course_id,score) values

(1,1,60),

(1,2,59),

(1,3,58),

(2,1,99),

(2,2,99),

(2,3,89),

(3,1,59),

(3,3,30);

insert into teach2cls(tid,cid) values

(1,1),

(1,2),

(1,3),

(1,5),

(2,4),

(2,6),

(2,8),

(2,9),

(2,1),

(2,5),

(3,7),

(3,1),

(3,3),

(3,5),

(3,9);

补充数据

insert into teacher(tname) values

('赵六'),

('苗七');

insert into class_grade(gname) values

('四年级');

insert into class(caption,grade_id) values

('四年一班',4),

('四年二班',4),

('四年三班',4),

('四年四班',4);

insert into course(cname,teacher_id) values

('线性代数',4);

insert into student(sname,gender,class_id) values

('张一','女',3),

('詹姆斯','男',3),

('荷花','女',3),

('杜兰特','男',3),

('哈登','男',4),

('尼克','男',4),

('青青','女',4),

('阿里扎','男',4);

insert into score(student_id,course_id,score) values

(3,4,60),

(4,1,59),

(4,2,100),

(4,3,90),

(4,4,80),

(5,1,59),

(5,2,33),

(5,3,12),

(5,4,88),

(6,1,100),

(6,2,60),

(6,3,59),

(6,4,100),

(7,1,20),

(7,2,36),

(7,3,57),

(7,4,60),

(8,1,61),

(8,2,59),

(8,3,62),

(8,4,59),

(9,1,60),

(9,2,61),

(9,3,21);

insert into teach2cls(tid,cid) values

(4,1),

(4,2),

(4,3),

(4,4),

(5,1),

(5,2),

(5,3),

(5,4);

二，操作表格内容

1、自行创建测试数据；

上面已经完成。

2、查询学生总人数；

select count(sid) from student;

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；

思路：获取所有生物课程的人(学号，成绩)--临时表

获取所有物理课程的人(学号，成绩)--临时表

根据学号连接两个临时表：学号，物理成绩，生物成绩

然后筛选及格的

select sid,sname

from student

where sid in(

select score.student_id from score inner join course on score.course_id=course.cid

where course.cname in('生物','物理') and score.score >=60

group by score.student_id having count(course_id) = 2);

4、查询每个年级的班级数，取出班级数最多的前三个年级；

思路：首先分析班级前三的情况，分为班级数相同的情况和班级数不同的情况

如果班级数相同，那么只需要考虑在班级里面统计班级数量即可，

然后在班级年级表中取出对应的年级数目

如果班级数不相同，那么首先班级里面统计班级数量，

然后在按照降序排列，取前三即可

#包含班级数不相同的排名前三年级

select class_grade.gname from class_grade inner join(

select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)

as t1 on class_grade.gid = t1.grade_id;

#包含了班级数相同的排名前三年级

select gname from class_grade where gid in (

select grade_id from class group by grade_id having count(cid) in (

5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；

create view t1 as

select student_id avg(score) as avg_score from score group by student_id;

select sname,avg_score from t1 left join student on t1.student_id =student.sid

where t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score =

(select min(t1.avg_score) from t1);

6、查询每个年级的学生人数；

思路：先在学生表和班级表对应一下

然后在对应班级表中查找学生人数

select t1.grade_id,count(t1.sid) as count_student from (

select student.sid ,class.grade_id from student,class

where student.class_id =class.cid) as t1 group by t1.grade_id;

7、查询每位学生的学号，姓名，选课数，平均成绩；

思路：学生表中有学生学号，姓名，性别，班级 成绩表中对应成绩，所以

我们可以联立成绩表和学生表，并按学生id分类，直接查找即可。

select score.student_id,student.sname,sum(score.course_id),avg(score.score)

from score left join student on score.student_id = student.sid

group by score.student_id;

8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；

思路：首先在成绩表中查找学生编号为2 的学生的最大最小成绩，学生id，课程id，

然后在课程表和学生表中找到对应的学生姓名和课程名称，

最后联立表格得出学生姓名，课程名称，分数

select student.sname,course.cname,t1.score from (

select student_id,course_id,score from score where student_id = 2 and score in((

select max(score) from score where student_id = 2),

(select min(score) from score where student_id = 2))) as t1

inner join student on t1.student_id = student.sid

inner join course on t1.course_id = course.cid;

9、查询姓“李”的老师的个数和所带班级数；

思路：首先在老师表中寻找姓李老师的id

然后在teach2cls中找到老师和班级的联系，并统计姓李老师所带的班级数

最后在老师表中查询老师id和姓名。

select teacher.tid as '姓李id', teacher.tname as '老师姓名' ,GROUP_CONCAT(teach2cls.cid) as '班级数'

from teacher left join teach2cls on teacher.tid = teach2cls.tid

where teacher.tname like '李%' group by teacher.tid;

10、查询班级数小于5的年级id和年级名；

思路：首先查询班级表中班级小于5的年级id号码

然后在年级表中查找对应班级表中的年级id即可

select gid,gname from class_grade where gid in (

select grade_id from class group by grade_id having count(caption)<5

);

11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；

班级id班级名称年级年级级别

1

一年一班

一年级

低

select

class.cid as '班级id',

class.caption as '班级名称',

class_grade.gname as '年级',

case

when class_grade.gid between 1 and 2 then '低'

when class_grade.gid between 3 and 4 then '中'

when class_grade.gid between 5 and 6 then '高' else 0

end as '年级级别'

from class

left join class_grade on class.grade_id = class_grade.gid;

12、查询学过“张三”老师2门课以上的同学的学号、姓名；

首先找到张三老师的id，

然后联立成绩表和课程表，并在成绩表中查看选修张三老师课程数量大于2的学生id

最后在学生表中查找学生的学号，姓名。

select sid,sname from student

where sid in

(

select score.student_id from score

left join course

on score.course_id = course.cid

where course.teacher_id in

(

select tid from teacher

where tname = '张三'

)

group by student_id

having count(course.cid) >2

);

13、查询教授课程超过2门的老师的id和姓名；

思路：先在course中按照老师的id进行分组，并统计代课大于2门的老师id的总数---临时表

然后在teacher表中查找老师的id和姓名

select tid,tname from teacher where tid in (

select teacher_id from course group by teacher_id having count(cid)>2);

14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；

思路：创建一个虚拟表，用于查找课程中的编号1和编号2课程

然后在学生表中查找学生的学号和姓名

select sid,sname from student where sid in (

select distinct student_id from score where course_id in (1,2));

15、查询没有带过高年级的老师id和姓名；

思路：在班级表中设定高年级为五六年级，---虚拟表

然后在teach2cls中找到老师和班级的联系 ---虚拟表

最后在老师表中查询老师id和姓名

select tid,tname from teacher where tid not in (select tid from teach2cls

where cid in (select cid from class where grade_id in (5,6)));

16、查询学过“张三”老师所教的所有课的同学的学号、姓名；

思路：首先将张三老师的id从课程表中和老师表中对应起来，并找出他教的课程id ————虚拟表

然后在成绩表中查找与上面表对应的课程id所对应的学生的id

最后在学生表中查找学生的学号，姓名。

select sid,sname from student where sid in (

select student_id from score where course_id in (

select cid from course inner join teacher on teacher.tid = course.teacher_id

where teacher.tname = '张三'));

17、查询带过超过2个班级的老师的id和姓名；

思路： 先在teac2cls中找到班级cid大于2的老师id(ｔｉｄ)－－－－虚拟表

然后在老师表中找老师ｉｄ和姓名对应的ｉｄ

select tid,tname from teacher where tid in (

select tid from teach2cls group by tid having count(cid)>2);

18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

思路：先在成绩表中查找课程2 的学生id和成绩，设为表1 ，

再查找课程编号为1的学生id和成绩，设为表二

最后在学生表中查找课程2比课程1的成绩低的学生的学号和姓名。

select sid,sname from student where sid in (

select t1.student_id from (

select student_id, score from score where course_id = 2 group by student_id) as t1,

select student_id, score from score where course_id = 1 group by student_id) as t2

where t1.student_id = t2.student_id and t1.score < t2.score);

19、查询所带班级数最多的老师id和姓名；

思路：首先在老师-课程表中统计老师所带的课程数量，并按照老师id分类，并取一个

然后在老师表中查找对应老师id和姓名

select tid,tname from teacher where tid =(

select tid from teach2cls group by tid order by count(cid) desc limit 1);

20、查询有课程成绩小于60分的同学的学号、姓名；

思路：先在成绩表中查找成绩小于６０分的学生ｉｄ

然后学生表中查找学生id与成绩表中的学生id对应的学生学号，姓名

select sid,sname from student where sid in (

select distinct student_id from score where score<60 );

21、查询没有学全所有课的同学的学号、姓名；

思路：首先分析题目意思，是没有学完所有课的同学

那么考虑学生应该是选完课程，没有考试，视为没有学完

所以首先查找学生选择的课程，在成绩表中是否有对应的成绩，如果有则学完，如果没有则没有学完

select sid ,sname from student where sid not in(

select student_id from score group by student_id having count(course_id)=

(select count(cid) from course)

);

22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

思路：首先查找学号为1的学生的成绩id，

然后在成绩表中按照学号对应上面的成绩id

最后在学生表中查找学生的学号，姓名。

select sid,sname from student where sid in (

select student_id from score where course_id in (

select course_id from score where student_id =1) group by student_id);

23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；

思路：首先查找学号为1的学生所选的课程id，

然后再对应其他学生所选的课程id，

最后在学生表中查找学生的学号，姓名。

select sid,sname from student where sid in (

select student_id from score where course_id in (

select course_id from score where student_id = 1)

group by student_id) and sid !=1;

24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；

思路：首先在成绩表中查询学生2的课程id，

然后进行筛选其他人的课程id和姓名id，不包含2号学生，

最后在学生表中查找学生的学号，姓名。

select sid,sname from student where sid in (

select score.student_id from score ,(

select course_id from score where student_id = 2) as t1

where score.course_id = t1.course_id and score.student_id !=2

group by score.student_id

having count(score.course_id) =(

select count(course_id) from score where student_id = 2));

25、删除学习“张三”老师课的score表记录；

思路：首先在score表中找到对应张三老师课程，

然后删除即可

delete from score where course_id in (

select course.cid from course,teacher where

course.teacher_id =teacher.tid and teacher.tname = '张三');

26、向score表中插入一些记录，这些记录要求符合以下条件：

①没有上过编号“2”课程的同学学号；

②插入“2”号课程的平均成绩；

思路：首先在score找出没有上过编号2课程的同学id，

然后在成绩表中找到编号2的学生的所有成绩，取平均值

最后插入数据即可。

insert into score(student_id,course_id,score)

select t1.sid,2,t2.avg from (

select sid from student where sid not in (

select student_id from score where course_id = 2)) as t1,

(select avg(score) as avg from score group by course_id having course_id =2) as t2;

27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

思路：注意平均成绩是由低到高desc

最重要的是查询各科成绩，

在课程表中找到成绩表中对应的课程id，然后在成绩表中查找对应的成绩

select sc.student_id,

(select score.score from score left join course on score.course_id = course.cid

where course.cname = '语文' and score.student_id = sc.student_id) as Chinese,

(select score.score from score left join course on score.course_id = course.cid

where course.cname = '数学' and score.student_id = sc.student_id) as Math,

(select score.score from score left join course on score.course_id = course.cid

where course.cname = '外语' and score.student_id = sc.student_id) as English,

count(sc.course_id),avg(sc.score)

from score as sc group by sc.student_id order by avg(sc.score) asc;

28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

思路：直接在score中查找课程id，最高分数，最低分数

select course_id ,max(score),min(score) from score

group by course_id;

29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

思路：平均成绩asc 及格率desc

在score中找到学生的平均成绩，并求出及格率。

select course_id,avg(score) as avg_score,

sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent

from score group by course_id order by avg(score) asc,percent desc;

30、课程平均分从高到低显示(现实任课老师)；

思路：查找成绩表中的各科平均分数，并让分数对应课程表中的课程id

然后把课程表中的课程id对应的老师课程表的老师id

最后在老师表中查询老师id和姓名

select t1.course_id,t1.avg_score,teacher.tname from course,teacher,

(select course_id,avg(score) as avg_score from score group by course_id ) as t1

where course.cid = t1.course_id and course.teacher_id = teacher.tid

order by avg_score desc;

31、查询各科成绩前三名的记录(不考虑成绩并列情况)

select score.sid,score.student_id, score.course_id,score.score,

t1.first_score,t1.second_score,t1.third_score

from score inner join (

select s1.sid,(select score from score as s2 where s1.course_id = s2.course_id

order by score desc limit 0,1) as first_score,

(select score from score as s3 where s1.course_id = s3.course_id

order by score desc limit 1,1) as second_score,

(select score from score as s4 where s1.course_id = s4.course_id

order by score desc limit 2,1) as third_score

from score as s1) as t1 on score.sid = t1.sid

where score.score in (t1.first_score,t1.second_score,t1.third_score);

32、查询每门课程被选修的学生数；

思路：在成绩表中查找课程id，每门课的学生总数，

最后在课程表中找到对应的课程名称

select course.cname as '课程名称',t1.student_num as '学生数量' from course,

(select course_id,count(student_id) as student_num from score

group by course_id) as t1 where course.cid = t1.course_id;

33、查询选修了2门以上课程的全部学生的学号和姓名；

思路：在成绩表中查找课程id大于2们的学生id

然后在学生表中查找对应的学生的学号和姓名

select sid,sname from student where sid in (

select student_id from score group by student_id having count(course_id)>2);

34、查询男生、女生的人数，按倒序排列；

思路： 在学生表中按照性别分类 按照数量排序desc

select gender,count(sid) as num from student

group by gender order by num desc;

35、查询姓“张”的学生名单；

思路：在学生表中查找姓张的学生名单

select sid,sname,gender from student where sname like '张%';

36、查询同名同姓学生名单，并统计同名人数；

思路：直接在学生表中查看学生姓名相同的学生，并统计人数

select sname,count(sname) from student group by sname having count(sname)>1;

37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

思路：在成绩表中按照课程id 查找学生的平均成绩

select course_id,avg(score) as avg_score from score

group by course_id order by avg_score,course_id desc;

38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

思路：先在course中查找课程为数学的课程id号码，

然后在score中查找数学分数低于60的学生id,分数

最后在学生表中查找对于id 的学生姓名

select student.sname,score.score from score left join student

on score.student_id = student.sid where score.course_id = (

select cid from course where cname ='数学') and score.score

39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；

思路：先创建一个课程编号为3 且成绩在80分以上的学生id表，在score中 --虚拟表

然后在student中查找对应id的学生姓名

select sid,sname from student where sid in (

select student_id from score where score> 80 and course_id = 3

);

40、求选修了课程的学生人数

思路：直接在成绩表中按照课程id排序，并统计学生id即可

select course_id,count(student_id) from score group by course_id;

41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

思路：首先在老师表中寻找姓王老师的id，

然后对应课程表中对应的所教课程id，

然后在score中查找课程所对应的成绩和学生id

最后在学生表中查找学生的学号，姓名。

select student.sname,score,score from score

left join student on score.student_id = student.sid where course_id in (

select cid from course where teacher_id in (

select tid from teacher where tname = '王五'))

order by score.score desc limit 1;

42、查询各个课程及相应的选修人数；

思路：联立课程表中的课程id和成绩表中的课程id，

然后查找各个课程对应的选修人数

select course.cname,count(student_id) from score

left join course on score.course_id = course.cid group by course_id;

43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

思路：查找不同学生之间，课程不同成绩相同

查找同一个学生，课程不同成绩相同

的学生，课程号，学生成绩

#1，不同学生之间

select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score

from score as s1,score as s2

where s1.course_id != s2.course_id and s1.score = s2.score;

#2，同一个学生

select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score

from score as s1,score as s2

where s1.student_id = s2.student_id and

s1.course_id != s2.course_id and s1.score = s2.score;

44、查询每门课程成绩最好的前两名学生id和姓名；

select

student.sid,

student.sname,

t2.course_id,

t2.score,

t2.first_score,

t2.second_score

from

student

inner join (

select

score.student_id,

score.course_id,

score.score,

t1.first_score,

t1.second_score

from

score

inner join (

select

s1.sid,

(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,

(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score

from

score as s1

) as t1 on score.sid = t1.sid

where

score.score in (

t1.first_score,

t1.second_score

)

) as t2 on student.sid = t2.student_id;

45、检索至少选修两门课程的学生学号；

思路：在score表中直接查找大于2门课程的学生id

select student_id from score group by student_id having

count(course_id)>=2;

46、查询没有学生选修的课程的课程号和课程名；

思路：在成绩表中按照课程id分组作为一个临时表

如果在课程表中，id没有在上面的临时表中，则就是没有学生选修

select cid,cname from course where cid not in

(select course_id from score group by course_id);

47、查询没带过任何班级的老师id和姓名；

思路：在老师-课程表中按照老师分组作为一个临时表

如果在老师表中，id没有在这个临时表，则就是没有带过任何班级

select tid tname from teacher where tid not in (

select tid from teach2cls group by tid);

48、查询有两门以上课程超过80分的学生id及其平均成绩；

思路：首先，在成绩表中获取有两门课程成绩大于80分的学生id，---临时表

然后在成绩表中查找其id和平均成绩

或者在score表中直接查找大于2门课程的学生id和平均成绩

select student_id,avg(score) from score

where student_id in (

select student_id from score where score > 80 group by student_id

having count(course_id) > 2);

select student_id,avg(score) from score

where score >80 group by student_id having count(course_id) >2;

49、检索“3”课程分数小于60，按分数降序排列的同学学号；

思路：查找成绩表中课程三而且分数小于60的学生学号，并按照分数降序排列desc

select student_id,score from score where course_id = 3 and score<60

order by score desc;

50、删除编号为“2”的同学的“1”课程的成绩；

思路：首先在成绩表中，先把编号为2和课程为1的找到，

然后删除在成绩表中对应学生的成绩

delete from score where sid =

(select sid from score where student_id = 2 and course_id=1

);

51、查询同时选修了物理课和生物课的学生id和姓名；

思路：在课程中首先找到物理，生物的id，

然后在成绩表中对应课程的id，此时找到了生物课和物理课的id

最后在学生表中，找到学生id和姓名

select sid,sname from student where sid in(

select student_id from score where course_id in (

select cid from course where course.cname in('物理','生物'))

group by student_id having count(course_id) = 2

);

所有的题目代码*的意思：

题目中打***    表示难，掌握不深

题目中打*****  表示复制别人的

1、自行创建测试数据；

见create_tabledata.txt

insert_tabledata.txt2、查询学生总人数；select count(sid) fromstudent;3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；

思路：获取所有生物课程的人(学号，成绩)--临时表

获取所有物理课程的人(学号，成绩)--临时表

根据学号连接两个临时表：学号，物理成绩，生物成绩

然后筛选及格的selectsid,snamefromstudentwhere sid in(select score.student_id from score inner join course on score.course_id=course.cidwhere course.cname in('生物','物理') and score.score >=60group by score.student_id having count(course_id)= 2);***4、查询每个年级的班级数，取出班级数最多的前三个年级；

思路：首先分析班级前三的情况，分为班级数相同的情况和班级数不同的情况

如果班级数相同，那么只需要考虑在班级里面统计班级数量即可，

然后在班级年级表中取出对应的年级数目

如果班级数不相同，那么首先班级里面统计班级数量，

然后在按照降序排列，取前三即可

#包含班级数不相同的排名前三年级select class_grade.gname fromclass_grade inner join(select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)as t1 on class_grade.gid =t1.grade_id;

#包含了班级数相同的排名前三年级select gname from class_grade where gid in(select grade_id from class group by grade_id having count(cid) in(*****5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；

create view t1as

select student_id avg(score) as avg_score fromscore group by student_id;select sname,avg_score from t1 left join student on t1.student_id =student.sidwhere t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score =(select min(t1.avg_score) fromt1);6、查询每个年级的学生人数；

思路：先在学生表和班级表对应一下

然后在对应班级表中查找学生人数select t1.grade_id,count(t1.sid) as count_student from(select student.sid ,class.grade_id from student,class

where student.class_id =class.cid) ast1 group by t1.grade_id;7、查询每位学生的学号，姓名，选课数，平均成绩；

思路：学生表中有学生学号，姓名，性别，班级 成绩表中对应成绩，所以

我们可以联立成绩表和学生表，并按学生id分类，直接查找即可。selectscore.student_id,student.sname,sum(score.course_id),avg(score.score)from score left join student on score.student_id =student.sid

group by score.student_id;***8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；

思路：首先在成绩表中查找学生编号为2 的学生的最大最小成绩，学生id，课程id，

然后在课程表和学生表中找到对应的学生姓名和课程名称，

最后联立表格得出学生姓名，课程名称，分数select student.sname,course.cname,t1.score from(select student_id,course_id,score from score where student_id = 2 and score in((select max(score) from score where student_id = 2),

(select min(score) from score where student_id = 2))) ast1

inner join student on t1.student_id=student.sid

inner join course on t1.course_id=course.cid;9、查询姓“李”的老师的个数和所带班级数；

思路：首先在老师表中寻找姓李老师的id

然后在teach2cls中找到老师和班级的联系，并统计姓李老师所带的班级数

最后在老师表中查询老师id和姓名。select teacher.tid as '姓李id', teacher.tname as '老师姓名' ,GROUP_CONCAT(teach2cls.cid) as '班级数'

from teacher left join teach2cls on teacher.tid =teach2cls.tidwhere teacher.tname like '李%'group by teacher.tid;10、查询班级数小于5的年级id和年级名；

思路：首先查询班级表中班级小于5的年级id号码

然后在年级表中查找对应班级表中的年级id即可select gid,gname from class_grade where gid in(select grade_id from class group by grade_id having count(caption)<5);*****11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，

示例结果如下；

班级id 班级名称 年级 年级级别1一年一班 一年级 低select

class.cid as '班级id',class.caption as '班级名称',

class_grade.gnameas '年级',casewhen class_grade.gid between1 and 2 then '低'when class_grade.gid between3 and 4 then '中'when class_grade.gid between5 and 6 then '高' else 0endas '年级级别'

from classleft join class_grade onclass.grade_id =class_grade.gid;12、查询学过“张三”老师2门课以上的同学的学号、姓名；

首先找到张三老师的id，

然后联立成绩表和课程表，并在成绩表中查看选修张三老师课程数量大于2的学生id

最后在学生表中查找学生的学号，姓名。select sid,sname fromstudentwhere sid in(select score.student_id fromscore

left join course

on score.course_id=course.cidwhere course.teacher_id in(select tid fromteacherwhere tname = '张三')

group by student_id

having count(course.cid)>2);13、查询教授课程超过2门的老师的id和姓名；

思路：先在course中按照老师的id进行分组，并统计代课大于2门的老师id的总数---临时表

然后在teacher表中查找老师的id和姓名select tid,tname from teacher where tid in(select teacher_id from course group by teacher_id having count(cid)>2);14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；

思路：创建一个虚拟表，用于查找课程中的编号1和编号2课程

然后在学生表中查找学生的学号和姓名select sid,sname from student where sid in(select distinct student_id from score where course_id in (1,2));15、查询没有带过高年级的老师id和姓名；

思路：在班级表中设定高年级为五六年级，---虚拟表

然后在teach2cls中找到老师和班级的联系---虚拟表

最后在老师表中查询老师id和姓名select tid,tname from teacher where tid not in (select tid fromteach2clswhere cid in (select cid from class where grade_id in (5,6)));16、查询学过“张三”老师所教的所有课的同学的学号、姓名；

思路：首先将张三老师的id从课程表中和老师表中对应起来，并找出他教的课程id ————虚拟表

然后在成绩表中查找与上面表对应的课程id所对应的学生的id

最后在学生表中查找学生的学号，姓名。select sid,sname from student where sid in(select student_id from score where course_id in(select cid from course inner join teacher on teacher.tid =course.teacher_idwhere teacher.tname = '张三'));17、查询带过超过2个班级的老师的id和姓名；

思路： 先在teac2cls中找到班级cid大于2的老师id(ｔｉｄ)－－－－虚拟表

然后在老师表中找老师ｉｄ和姓名对应的ｉｄselect tid,tname from teacher where tid in(select tid from teach2cls group by tid having count(cid)>2);18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

思路：先在成绩表中查找课程2 的学生id和成绩，设为表1 ，

再查找课程编号为1的学生id和成绩，设为表二

最后在学生表中查找课程2比课程1的成绩低的学生的学号和姓名。select sid,sname from student where sid in(select t1.student_id from(select student_id, score from score where course_id = 2 group by student_id) ast1,select student_id, score from score where course_id = 1 group by student_id) ast2where t1.student_id = t2.student_id and t1.score

思路：首先在老师-课程表中统计老师所带的课程数量，并按照老师id分类，并取一个

然后在老师表中查找对应老师id和姓名select tid,tname from teacher where tid =(select tid from teach2cls group by tid order by count(cid) desc limit 1);20、查询有课程成绩小于60分的同学的学号、姓名；

思路：先在成绩表中查找成绩小于６０分的学生ｉｄ

然后学生表中查找学生id与成绩表中的学生id对应的学生学号，姓名select sid,sname from student where sid in(select distinct student_id from score where score<60);21、查询没有学全所有课的同学的学号、姓名；

思路：首先分析题目意思，是没有学完所有课的同学

那么考虑学生应该是选完课程，没有考试，视为没有学完

所以首先查找学生选择的课程，在成绩表中是否有对应的成绩，如果有则学完，如果没有则没有学完select sid ,sname from student where sid not in(select student_id from score group by student_id having count(course_id)=(select count(cid) fromcourse)

);22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

思路：首先查找学号为1的学生的成绩id，

然后在成绩表中按照学号对应上面的成绩id

最后在学生表中查找学生的学号，姓名。select sid,sname from student where sid in(select student_id from score where course_id in(select course_id from score where student_id =1) group by student_id);23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；

思路：首先查找学号为1的学生所选的课程id，

然后再对应其他学生所选的课程id，

最后在学生表中查找学生的学号，姓名。select sid,sname from student where sid in(select student_id from score where course_id in(select course_id from score where student_id = 1)

group by student_id) and sid!=1;***24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；

思路：首先在成绩表中查询学生2的课程id，

然后进行筛选其他人的课程id和姓名id，不包含2号学生，

最后在学生表中查找学生的学号，姓名。select sid,sname from student where sid in(select score.student_id fromscore ,(select course_id from score where student_id = 2) ast1where score.course_id = t1.course_id and score.student_id !=2group by score.student_id

having count(score.course_id)=(select count(course_id) from score where student_id = 2));25、删除学习“张三”老师课的score表记录；

思路：首先在score表中找到对应张三老师课程，

然后删除即可

deletefrom score where course_id in(select course.cid from course,teacher wherecourse.teacher_id=teacher.tid and teacher.tname = '张三');26、向score表中插入一些记录，这些记录要求符合以下条件：

①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；

思路：首先在score找出没有上过编号2课程的同学id，

然后在成绩表中找到编号2的学生的所有成绩，取平均值

最后插入数据即可。

insert into score(student_id,course_id,score)select t1.sid,2,t2.avg from(select sid from student where sid not in(select student_id from score where course_id = 2)) ast1,

(select avg(score) as avg from score group by course_id having course_id =2) ast2;27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，

按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

思路：注意平均成绩是由低到高desc

最重要的是查询各科成绩，

在课程表中找到成绩表中对应的课程id，然后在成绩表中查找对应的成绩selectsc.student_id,

(select score.score from score left join course on score.course_id =course.cidwhere course.cname = '语文' and score.student_id = sc.student_id) asChinese,

(select score.score from score left join course on score.course_id =course.cidwhere course.cname = '数学' and score.student_id = sc.student_id) asMath,

(select score.score from score left join course on score.course_id =course.cidwhere course.cname = '外语' and score.student_id = sc.student_id) asEnglish,

count(sc.course_id),avg(sc.score)from score assc group by sc.student_id order by avg(sc.score) asc;28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

思路：直接在score中查找课程id，最高分数，最低分数select course_id ,max(score),min(score) fromscore

group by course_id;29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

思路：平均成绩asc 及格率desc

在score中找到学生的平均成绩，并求出及格率。select course_id,avg(score) asavg_score,

sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 aspercentfromscore group by course_id order by avg(score) asc,percent desc;30、课程平均分从高到低显示(显示任课老师)；

思路：查找成绩表中的各科平均分数，并让分数对应课程表中的课程id

然后把课程表中的课程id对应的老师课程表的老师id

最后在老师表中查询老师id和姓名select t1.course_id,t1.avg_score,teacher.tname fromcourse,teacher,

(select course_id,avg(score) as avg_score from score group by course_id ) ast1where course.cid = t1.course_id and course.teacher_id =teacher.tid

order by avg_score desc;*****31、查询各科成绩前三名的记录(不考虑成绩并列情况)selectscore.sid,score.student_id, score.course_id,score.score,

t1.first_score,t1.second_score,t1.third_scorefromscore inner join (select s1.sid,(select score from score as s2 where s1.course_id =s2.course_id

order by score desc limit0,1) asfirst_score,

(select score from score as s3 where s1.course_id =s3.course_id

order by score desc limit1,1) assecond_score,

(select score from score as s4 where s1.course_id =s4.course_id

order by score desc limit2,1) asthird_scorefrom score as s1) as t1 on score.sid =t1.sidwhere score.score in(t1.first_score,t1.second_score,t1.third_score);32、查询每门课程被选修的学生数；

思路：在成绩表中查找课程id，每门课的学生总数，

最后在课程表中找到对应的课程名称select course.cname as '课程名称',t1.student_num as '学生数量' fromcourse,

(select course_id,count(student_id) as student_num fromscore

group by course_id)as t1 where course.cid =t1.course_id;33、查询选修了2门以上课程的全部学生的学号和姓名；

思路：在成绩表中查找课程id大于2们的学生id

然后在学生表中查找对应的学生的学号和姓名select sid,sname from student where sid in(select student_id from score group by student_id having count(course_id)>2);34、查询男生、女生的人数，按倒序排列；

思路： 在学生表中按照性别分类 按照数量排序descselect gender,count(sid) as num fromstudent

group by gender order by num desc;35、查询姓“张”的学生名单；

思路：在学生表中查找姓张的学生名单select sid,sname,gender from student where sname like '张%';36、查询同名同姓学生名单，并统计同名人数；

思路：直接在学生表中查看学生姓名相同的学生，并统计人数select sname,count(sname) from student group by sname having count(sname)>1;37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

思路：在成绩表中按照课程id 查找学生的平均成绩select course_id,avg(score) as avg_score fromscore

group by course_id order by avg_score,course_id desc;38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

思路：先在course中查找课程为数学的课程id号码，

然后在score中查找数学分数低于60的学生id,分数

最后在学生表中查找对于id 的学生姓名select student.sname,score.score fromscore left join student

on score.student_id= student.sid where score.course_id =(select cid from course where cname ='数学') and score.score

思路：先创建一个课程编号为3 且成绩在80分以上的学生id表，在score中--虚拟表

然后在student中查找对应id的学生姓名select sid,sname from student where sid in(select student_id from score where score> 80 and course_id = 3);40、求选修了课程的学生人数

思路：直接在成绩表中按照课程id排序，并统计学生id即可select course_id,count(student_id) fromscore group by course_id;41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

思路：首先在老师表中寻找姓王老师的id，

然后对应课程表中对应的所教课程id，

然后在score中查找课程所对应的成绩和学生id

最后在学生表中查找学生的学号，姓名。select student.sname,score,score fromscore

left join student on score.student_id= student.sid where course_id in(select cid from course where teacher_id in(select tid from teacher where tname = '王五'))

order by score.score desc limit1;42、查询各个课程及相应的选修人数；

思路：联立课程表中的课程id和成绩表中的课程id，

然后查找各个课程对应的选修人数select course.cname,count(student_id) fromscore

left join course on score.course_id=course.cid group by course_id;43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

思路：查找不同学生之间，课程不同成绩相同

查找同一个学生，课程不同成绩相同

的学生，课程号，学生成绩

#1，不同学生之间selectdistinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.scorefrom score as s1,score ass2where s1.course_id != s2.course_id and s1.score =s2.score;

#2，同一个学生selectdistinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.scorefrom score as s1,score ass2where s1.student_id =s2.student_id and

s1.course_id!= s2.course_id and s1.score =s2.score;*****44、查询每门课程成绩最好的前两名学生id和姓名；selectstudent.sid,

student.sname,

t2.course_id,

t2.score,

t2.first_score,

t2.second_scorefromstudent

inner join (selectscore.student_id,

score.course_id,

score.score,

t1.first_score,

t1.second_scorefromscore

inner join (selects1.sid,

(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) asfirst_score,

(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) assecond_scorefromscoreass1

)as t1 on score.sid =t1.sidwherescore.scorein(

t1.first_score,

t1.second_score

)

)as t2 on student.sid =t2.student_id;45、检索至少选修两门课程的学生学号；

思路：在score表中直接查找大于2门课程的学生idselect student_id fromscore group by student_id having

count(course_id)>=2;46、查询没有学生选修的课程的课程号和课程名；

思路：在成绩表中按照课程id分组作为一个临时表

如果在课程表中，id没有在上面的临时表中，则就是没有学生选修select cid,cname from course where cid not in(select course_id fromscore group by course_id);47、查询没带过任何班级的老师id和姓名；

思路：在老师-课程表中按照老师分组作为一个临时表

如果在老师表中，id没有在这个临时表，则就是没有带过任何班级select tid tname from teacher where tid not in(select tid fromteach2cls group by tid);48、查询有两门以上课程超过80分的学生id及其平均成绩；

思路：首先，在成绩表中获取有两门课程成绩大于80分的学生id，---临时表

然后在成绩表中查找其id和平均成绩

或者在score表中直接查找大于2门课程的学生id和平均成绩select student_id,avg(score) fromscorewhere student_id in(select student_id from score where score > 80group by student_id

having count(course_id)> 2);select student_id,avg(score) fromscorewhere score >80 group by student_id having count(course_id) >2;49、检索“3”课程分数小于60，按分数降序排列的同学学号；

思路：查找成绩表中课程三而且分数小于60的学生学号，并按照分数降序排列descselect student_id,score from score where course_id = 3 and score<60order by score desc;50、删除编号为“2”的同学的“1”课程的成绩；

思路：首先在成绩表中，先把编号为2和课程为1的找到，

然后删除在成绩表中对应学生的成绩

deletefrom score where sid =(select sid from score where student_id = 2 and course_id=1);51、查询同时选修了物理课和生物课的学生id和姓名；

思路：在课程中首先找到物理，生物的id，

然后在成绩表中对应课程的id，此时找到了生物课和物理课的id

最后在学生表中，找到学生id和姓名select sid,sname from student where sid in(select student_id from score where course_id in(select cid from course where course.cname in('物理','生物'))

group by student_id having count(course_id)= 2);

View Code