一，表关系的练习测试
请创建如下表关系，并建立相关约束
image.png
一，创建表结构数据：
创建的话肯定先创建没有关联的表，老师，课程（关联老师），年级，班级（关联年级），学生（关联班级），
班级任职表 （关联老师，课堂）
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
);
查询班级信息，包括班级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 <+60;
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.txt
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 班级名称 年级 年级级别
一年一班 一年级 低
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 <+60;
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
);
