本文提供了一组MySQL查询练习题，涵盖了多种复杂查询场景，如多条件查询、联接查询、聚合函数应用等，旨在帮助读者提升数据库操作技能。
1、自行创建测试数据
#年级表
create table class_grade(
gid int primary key auto_increment,
gname char(10) unique
);
#插入数据
insert into class_grade values
(1,'一年级'),
(2,'二年级'),
(3,'三年级');
#班级表
create table class(
cid int primary key auto_increment,
caption char(10) unique,
grade_id int not null,
foreign key(grade_id) references class_grade(gid)
on delete cascade
on update cascade
);
#插入数据
insert into class values
(1,'一年一班',1),
(2,'二年一班',2),
(3,'三年一班',3);
#学生表
create table student(
sid int primary key auto_increment,
sname char(15) not null,
gender enum('男','女'),
class_id int not null,
foreign key(class_id) references class(cid)
on delete cascade
on update cascade
);
#插入数据
insert into student values
(1,'乔丹','女',1),
(2,'艾弗森','女',1),
(3,'科比','男',2);
#老师表
create table teacher(
tid int primary key auto_increment,
tname char(15) not null
);
#插入数据
insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');
#课程表
create table course(
cid int primary key auto_increment,
cname char(10) unique,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
on delete cascade
on update cascade
);
#插入数据
insert into course values
(1,'生物',1),
(2,'体育',1),
(3,'物理',2);
#成绩表
create table score(
sid int primary key auto_increment,
student_id int not null,
course_id int not null,
score int not null,
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
);
#插入数据
insert into score values
(1,1,1,60),
(2,1,2,59),
(3,2,2,99);
#班级任职表
create table teach2cls(
tcid int primary key auto_increment,
tid int not null,
cid int not null,
foreign key(tid) references teacher(tid)
on delete cascade
on update cascade,
foreign key(cid) references class(cid)
on delete cascade
on update cascade
);
#插入数据
insert into teach2cls values
(1,1,1),
(2,1,2),
(3,2,1),
(4,3,2);
2、查询学生总人数
select count(sid) as '学生总人数' 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 t1.gname
from class_grade as t1 inner join
(select grade_id,count(cid)
from class
group by grade_id
order by count(cid) desc
limit 3) as t2
on t1.gid = t2.grade_id;
5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
select t1.sid,t1.sname,t2.avg_score
from student as t1 inner join
(
select student_id,avg(score) as avg_score
from score
group by student_id
having avg(score ) in (
(
select avg(score)
from score
group by student_id
order by avg(score) desc
limit 1
),
(
select avg(score)
from score
group by student_id
order by avg(score) asc
limit 1
)
)
) as t2
on t1.sid = t2.student_id;
6、查询每个年级的学生人数；
select caption,stu_num
from class as t1 inner join
(
select class_id,count(class_id) as stu_num
from student
group by class_id
) as t2
on t1.cid = t2.class_id;
7、查询每位学生的学号，姓名，选课数，平均成绩；
select t1.sid,t1.sname,count(course_id),avg(score)
from student as t1 left join score as t2
on t1.sid = t2.student_id
group by student_id;
8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
select t1.sname,t3.cname,t2.score
from student as t1 inner join
(
select course_id,student_id,score
from score
where score
in (
(select max(score) from score where student_id = 2),
(select min(score) from score where student_id = 2)
)
) as t2 inner join course as t3
on t1.sid = t2.student_id and t2.course_id = t3.cid
;
9、查询姓“李”的老师的个数和所带班级数；
select count(t1.tname) as tea_num,count(t2.cid) as cls_num
from teacher t1 inner join teach2cls t2
on t1.tid = t2.tid
where t1.tname like '李%';
10、查询班级数小于5的年级id和年级名；
select t1.grade_id,t2.gname
from class t1 inner join class_grade t2
on t1.grade_id = t2.gid
group by t1.grade_id
having count(grade_id) < 5;
11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；
select class.cid,class.caption,class_grade.gid,
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,class_grade
where class.grade_id=class_grade.gid;
12、查询学过“张三”老师2门课以上的同学的学号、姓名；
select sid,sname
from student
where sid in
(
select student_id
from score
where course_id
in (select t2.cid
from teacher t1 inner join course t2
on t1.tid = t2.teacher_id
where t1.tname = '张三')
group by student_id
having count(course_id) > 2
);
13、查询教授课程超过2门的老师的id和姓名；
select t1.tid,t1.tname
from teacher t1 inner join course t2
on t1.tid = t2.teacher_id
group by teacher_id
having count(teacher_id) > 2;
14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
select t1.sid,t1.sname
from student as t1 inner join
(
select student_id,course_id
from score
where course_id =1 or course_id =2
) as t2
on t1.sid = t2.student_id
group by student_id
having count(t2.student_id) = 2;
15、查询没有带过高年级的老师id和姓名；
select tid,tname
from teacher
where tid in
(
select distinct tid
from teach2cls
where cid in
(
select cid
from class
group by grade_id
having grade_id not in (5,6)
)
);
16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
select sid,sname
from student
where sid =
(
select student_id
from score
where course_id in
(
select t1.cid
from course t1 inner join teacher t2
on t1.teacher_id = t2.tid
where t2.tname = '张三'
)
group by student_id
having count(course_id) = 2
);
17、查询带过超过2个班级的老师的id和姓名；
select tid,tname
from teacher
where tid in(
select tid from teach2cls
group by tid
having count(cid)>2
);
18、查询课程编号“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和姓名；
select tid,tname
from teacher
where tid =
(
select tid
from teach2cls
group by tid
order by count(cid) desc
limit 1
);
20、查询有课程成绩小于60分的同学的学号、姓名；
select t2.sid,t2.sname
from score t1 inner join student t2
on t1.student_id = t2.sid
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”的同学所学相同的同学的学号和姓名；
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”同学所选课程中任意一门课的其他同学学号和姓名；
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”号同学学习的课程完全相同的其他同学的学号和姓名；
select sid,sname
from student
where sid in
(
select t1.student_id
from score t1 inner join
(
select student_id,course_id
from score
where student_id = 2
) as t2
on t1.course_id = t2.course_id
and t1.student_id != 2
group by t1.student_id
having count(t1.course_id) =
(
select count(course_id)
from score
where student_id = 2
)
);
25、删除学习“张三”老师课的score表记录；
delete from score
where course_id in
(
select t2.cid
from teacher t1 inner join course t2
on t1.tid = t2.teacher_id
where t1.tname = '张三'
);
26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
insert intoscore(student_id, course_id, score)
values
(
(
select sid
from student
where sid not in
(
select s.student_id
from score as s
where s.course_id = 2
)
order by sid desc
limit 0,1
),
2,
(
select avg(s.score)
from score as s
where s.course_id = 2
)
);
27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select t3.student_id as '学生ID',
(select t1.score
from score t1 left join course t2
on t1.course_id = t2.cid
where t2.cname = "语文" and t1.student_id=t3.student_id
) as '语文',
(
select t1.score
from score t1 left join course t2
on t1.course_id = t2.cid
where t2.cname = "数学" and t1.student_id=t3.student_id
) as '数学',
(
select t1.score
from score t1 left join course t2
on t1.course_id = t2.cid
where t2.cname = "英语" and t1.student_id=t3.student_id
) as '英语',
count(t3.course_id) as '有效课程数',
avg(t3.score) as '有效平均分'
from score as t3
group by student_id
order by '有效平均分' desc;
28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id as 课程ID,max(score) as 最高分,min(score) as 最低分
from score
group by course_id;
29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id as 课程ID,avg(score) as 平均成绩,concat(100*count(case when score>=60 then 1 else null end)/count(score),"%") as 及格率
from score
group by course_id
order by avg(score) asc, count(case when score>=60 then 1 else null end)/count(score) desc;
30、课程平均分从高到低显示（现实任课老师）；
select t2.course_id,3.tname,t1.cname,t2.avg_score
from course t1 inner join
(
select course_id,avg(score) as avg_score
from score
group by course_id
order by avg_score desc
) as t2 inner join teacher t3
on t1.cid = t2.course_id and t3.tid = t1.teacher_id
order by t2.avg_score desc;
31、查询各科成绩前三名的记录(不考虑成绩并列情况)
select t1.sid,t1.course_id,t1.score,t2.first_score,t2.second_score
from score t1 left join
(
select sid,
(
select s2.score
from score as s2
where s2.course_id = s1.course_id
order by s2.score desc
limit 0,1
) as first_score,
(
select s2.score
from score as s2
where s2.course_id = s1.course_id
order by s2.score desc limit 3,1
) as second_score
from score as s1
) as t2
on t1.sid = t2.sid
where t1.score <= t2.first_score and t1.score >= t2.second_score;
32、查询每门课程被选修的学生数；
select t1.cname,t2.stu_num
from course t1 inner join
(
select course_id,count(student_id) as stu_num
from score
group by course_id
) as t2
on t1.cid = t2.course_id;
33、查询选修了2门以上课程的全部学生的学号和姓名；
select sid,sname
from student
where sid in
(
select student_id
from score
group by student_id
having count(course_id)>2
);
34、查询男生、女生的人数，按倒序排列；
select gender,count(sid) as count_student
from student
group by gender
order by count_student desc;
35、查询姓“张”的学生名单；
select sid,sname,gender,class.caption
from student inner join class on student.class_id = class.cid
where sname like '张%';
36、查询同名同姓学生名单，并统计同名人数；
select sid,sname,count(sname) as count_sname
from student
group by sname
having count(sname)>1;
37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select t2.course_id,3.tname,t1.cname,t2.avg_score
from course t1 inner join
(
select course_id,avg(score) as avg_score
from score
group by course_id
order by avg_score desc
) as t2 inner join teacher t3
on t1.cid = t2.course_id and t3.tid = t1.teacher_id
order by t2.avg_score asc,t2.course_id desc;
38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select t1.sid,t1.sname,t2.score
from student t1
inner join (
select score.student_id,score.score
from score
inner join course on score.course_id=course.cid
where cname='数学'
and score.score<60
)as t2
on t1.sid=t2.student_id;
39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
select t1.sid,t1.sname,t2.score
from student t1
inner join(
select score.student_id,score.score from score
inner join course on score.course_id=course.cid
where cid=3 and score.score > 80
)as t2
on t1.sid = t2.student_id;
40、求选修了课程的学生人数
select t1.cname,t2.stu_num
from course t1 inner join
(
select course_id,count(student_id) as stu_num
from score
group by course_id
) as t2
on t1.cid = t2.course_id;
41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
select t3.sname,t4.score
from student t3 inner join score t4
on t3.sid = t4.student_id
where score in
(
(
select min(score)
from score
where course_id in
(
select cid
from course t1 left join teacher t2
on t1.teacher_id = t2.tid
where t2.tname='张三'
)
),
(
select max(score)
from score
where course_id in
(
select cid
from course t1 left join teacher t2
on t1.teacher_id = t2.tid
where t2.tname='张三'
)
)
);
42、查询各个课程及相应的选修人数；
select t1.cname,t2.stu_num
from course t1 inner join
(
select course_id,count(student_id) as stu_num
from score
group by course_id
) as t2
on t1.cid = t2.course_id;
43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select distinct s1.course_id,s2.course_id,s1.score,s2.score
from score as s1, score as s2
where s1.score = s2.score and s1.course_id != s2.course_id;
44、查询每门课程成绩最好的前两名学生id和姓名；
select sid,sname
from student
where sid in
(
select t1.sid
from score t1 left join
(
select sid,
(
select s2.score
from score as s2
where s2.course_id = s1.course_id
order by s2.score desc
limit 0,1
) as first_score,
(
select s2.score
from score as s2
where s2.course_id = s1.course_id
order by s2.score desc limit 1,1
) as second_score
from score as s1
) as t2
on t1.sid = t2.sid
where t1.score <= t2.first_score and t1.score >= t2.second_score
);
45、检索至少选修两门课程的学生学号；
select t1.student_id
from score t1 inner join student t2
on t1.student_id = t2.sid
group by t1.student_id
having count(t1.course_id)>=2;
46、查询没有学生选修的课程的课程号和课程名；
select cid,cname
from course
where cid not in
(
select course_id
from score
group by course_id
);
47、查询没带过任何班级的老师id和姓名；
select tid,tname
from teacher
where tid not in
(
select tid
from teach2cls
group by tid
);
48、查询有两门以上课程超过80分的学生id及其平均成绩；
select student_id,avg(score) as avg_score
from score
where student_id in
(
select student_id
from score
where score>80
group by student_id
having count(score.course_id)>2
)
group by student_id;
49、检索“3”课程分数小于60，按分数降序排列的同学学号；
select student_id,score
from score
where score<60
and course_id = 3
order by score desc;
50、删除编号为“2”的同学的“1”课程的成绩；
delete score
from score where
student_id=2 and course_id=1;
51、查询同时选修了物理课和生物课的学生id和姓名；
select sid,sname
from student
where sid in
(
select student_id
from score
where course_id in
(
select cid
from course
where cname in('物理','生物')
)
group by student_id
having count(course_id)=2
);
扫一扫
400
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
