MySQL 学习记录- 数据行查询40道练习题（错题！） Day18

本文链接：https://blog.csdn.net/yhy779169750/article/details/106290972

本文记录了40道MySQL数据行查询练习题，涵盖连表操作、子查询、平均成绩计算、排名筛选等多种场景。通过解决这些题目，深入理解如何查询不同科目成绩、比较课程成绩、查找相同课程的学生等复杂问题。

摘要由CSDN通过智能技术生成

1、自行创建测试数据

create table class(
cid int not null auto_increment primary key,
caption char(4))engine=innodb auto_increment=1 default charset=utf8;

create table student(
sid int not null auto_increment primary key,
sname varchar(5),
gender char(1),
class_id int,
constraint fk_student_class foreign key(class_id) references class(cid)
)engine=innodb auto_increment=1 default charset=utf8;

create table teacher(
tid int not null auto_increment primary key,
tname varchar(5))engine=innodb auto_increment=1 default charset=utf8;

create table course(
cid int not null auto_increment primary key,
cname varchar(5),
teacher_id int,
constraint fk_course_teacher foreign key(teacher_id) references teacher(tid)
)engine=innodb auto_increment=1 default charset=utf8;

create table score(
sid int not null auto_increment primary key,
student_id int,
course_id int,
number int,
constraint fk_score_student foreign key(student_id) references student(sid),
constraint fk_score_course foreign key(course_id) references course(cid)
)engine=innodb auto_increment=1 default charset=utf8;

insert into class(caption) values('高一2班'),('高二1班'),('高二2班'),('高三1班'),('高三2班'),('高三3班')；
insert into student(sname,gender,class_id) values('张一','男',1),('张二','女',2),('张三','男',3),('张四','女',4),('张五',