一、表关系
年级表：class_grade
create table class_grade(
gid int primary key auto_increment,
gname varchar(20) not null
);
insert into class_grade(gname) values
('一年级'),
('二年级'),
('三年级');
班级表：class
create table class(
cid int primary key auto_increment,
caption varchar(30) not null,
grade_id int not null,
constraint class_name foreign key(grade_id)
references class_grade(gid)
on delete cascade
on update cascade
);
insert into class(caption,grade_id) values
('一年一班',1),
('二年一班',2),
('三年二班',3);
学生表：student
create table student(
sid int primary key auto_increment,
sname varchar(20) not null,
gender enum('女','男'),
class_id int not null,
constraint student_name foreign key(class_id)
references class(cid)
on delete cascade
on update cascade
);
insert into student(sname,gender,class_id) values
('乔丹','女',1),
('艾弗森','女',1),
('科比','女',2);
老师表：teacher
create table teacher(
tid int primary key auto_increment,
tname varchar(30) not null
);
insert into teacher(tname) values
('张三'),
('李四'),
('王五');
课程表：course
create table course(
cid int primary key auto_increment,
cname varchar(30) not null,
teacher_id int not null,
constraint teacher_name foreign key(teacher_id)
references teacher(tid)
on delete cascade
on update cascade
);
insert into course(cname,teacher_id) values
('生物',1),
('体育',1),
('物理',2);
成绩表：score
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(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(2,2,99);
班级任职表：teach2cls
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(tid,cid) values
(1,1),
(1,2),
(2,1),
(3,2);
2、查询学生总人数
select count(sname) 总人数 from student;
3、查询’生物‘课程和’物理‘课程成绩都及格的学生id和姓名
select
sid,
sname
from
student
inner join
(
select
student_id
from
score
where
course_id in (
select
cid
from
course
where
cname in ('生物','物理')
) and score >= 60
) as t1 on t1.student_id = student.sid;
4、查询每个年级的班级数，取出班级数最多的前三个年级;
select
class_grade.gid,
class_grade.gname
from
class_grade
inner join
(
select
grade_id,
count(cid)
from
class
group by
grade_id
order by
grade_id desc
limit 3
) as t1 on t1.grade_id = class_grade.gid;
5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩;
select
student.sid,
student.sname,
avg_score
from
student
inner join(
select
student_id,
avg(score) as avg_score
from
score
group by
student_id
having avg(score) in
(
(
select
avg(score) as max_score
from
score
group by
student_id
order by
avg(score) desc
limit 1
),
(
select
avg(score) as min_score
from
score
group by
student_id
order by
avg(score)
limit 1
)
)
) as t1 on t1.student_id = student.sid;
6、查询每个年级的学生人数
select
class_grade.gname,
count_cid
from
class_grade
inner join
(
select
grade_id,
count(cid) as count_cid
from
class,student
where
class.cid = class_id
group by grade_id
) as t1 on t1.grade_id = class_grade.gid;
7、查询每位学生的学号、姓名、选课数、平均成绩;
select
student.sid,
student.sname,
course_count,
avg_score
from
student
left join
(
select
student_id,
count(course_id) as course_count,
avg(score) as avg_score
from
score
group by
student_id
) as t1 on t1.student_id = student.sid;
8、查询学生编号为‘2’的学生的姓名，该学生成绩最高的课程名、成绩最低的课程名及分数;
select
student.sname,
student.sid,
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、查询‘李’的老师的个数和所带班级数;
select
count(teacher.tname) as '李%个数',
count(teach2cls.cid) as '班级数量'
from teacher left join teach2cls on teach2cls.tid = teacher.tid
where teacher.tname like '李%';
10、查询班级数小于5年级的id和年级名;
select
gid,
gname,
count(cid)
from
class_grade
inner join class on gid = grade_id
group by
gid
having
count(cid) < 5
11、查询班级信息，包括班级id、班级名称、年级、年级级别（12
为低年级，34为中年级，56为高年级）
select
class.cid as '班级id',
class.caption as '班级名称',
class_grade.gid 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.gid=class.grade_id;
12、查询学过“张三”老师2门课以上的同学的学号、姓名；
select
sid,
sname
from
student
where sid in
(
select
student_id
from
score
left join course on 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和姓名;
select
tid,
tname
from
teacher
inner join
(
select
teacher_id,
count(cid)
from
course
group by
teacher_id
having count(cid) >=2
) as t1 on t1.teacher_id = teacher.tid;
14、查询学过编号‘1’课程和编号2课程的同学的学号、姓名;
select
sid,
sname
from
student
where sid in
(
select
course_id
from
score
group by
course_id
having
course_id in (1,2)
)
15、查询没有带过高年级的老师id和姓名;
select
tid,
tname
from
teacher
where tid in
(
select
tid
from
teach2cls
where tid in
(select
cid
from
class
where grade_id in (5,6)));
16、查询学过'张三'老师所教的所有课的同学的学号、姓名;
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_id = teacher.tid
where teacher.tname ='张三'));
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
) as t1
inner join
(
select
student_id,
score
from
score
where
course_id =1
) as t2 on t1.student_id = t2.student_id
where 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
sid,
sname
from
student
where sid in (
select
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”的同学所学相同的同学的学号和姓名；
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));
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)
having
student_id !=1
);
24、查询和‘2’号同学学习的课程完全相同的其它同学的学号和姓名
select
sid,
sname
from
student
where sid in
(
select
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表记录;
delete
from
score
where course_id in (
select
cid
from
course
where course.teacher_id = (
select
tid
from
teacher
where teacher.tname = '张三'));
26、向score表中插入一些记录，这些记录要求符合以下条件：
1、没有上过编号‘2’课程的同学学号
2、插入’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,语文,数学,英语,有效课程数,有效平均分；
select sc.student_id as 学生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 生物,
(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 体育,
(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 物理,
count(sc.course_id) as '有效课程数',avg(sc.score) as '有效平均分'
from score as sc group by sc.student_id order by avg(sc.score);
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 '平均成绩',
sum(CASE WHEN score > 60 then 1 ELSE 0 END)/COUNT(1)*100 as '及格率'
from score
GROUP BY course_id ORDER BY '平均成绩' ASC,'及格率' desc;
30、课程平均分从高到低显示（现实任课老师）；
SELECT score.course_id as '课程ID',avg(score) as '平均分' from score
inner join course on score.course_id = course.cid
GROUP BY course_id
ORDER BY avg(score) DESC
31、查询各科成绩前三名的记录(不考虑成绩并列情况)
SELECT score.sid,score.course_id,score.score,t1.first_score,t1.sencond_score,t1.third_score from score LEFT JOIN
(SELECT sid,
(select score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 0,1) as first_score,
(select score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 1,1) as sencond_score,
(select score from score as s2 where s2.course_id=s1.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.sencond_score,t1.third_score)
32、查询每门课程被选修的学生数；
SELECT score.course_id as '课程ID',count(student_id) as '学生数' from score
GROUP BY course_id
33、查询选修了2门以上课程的全部学生的学号和姓名；
SELECT student.sid,student.sname from student WHERE sid in (
SELECT score.student_id from score
GROUP BY student_id
HAVING count(course_id) >=2);
34、查询男生、女生的人数，按倒序排列；
select gender,count(sid) as sum from student
group by gender
order by sum desc
35、查询姓“张”的学生名单；
SELECT sname from student WHERE sname like '张%'
36、查询同名同姓学生名单，并统计同名人数；
SELECT sname as '名字',count(sname) as '同名人数' from student
GROUP BY sname
HAVING count(sname) >1
37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select score.course_id,avg(score) as avg from score
INNER JOIN course on course.cid = course_id
GROUP BY course_id
ORDER BY avg ASC,course_id DESC;
38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
SELECT sid,sname from student
where sid in (
SELECT score.student_id from score
INNER JOIN course on course.cid = score.course_id
WHERE course.cname = '体育' and score.score <60);
39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
SELECT sid,sname from student
WHERE sid in (
SELECT score.student_id from score
INNER JOIN course on course.cid = course_id
WHERE course_id = 3 and score.score > 80);
40、求选修了课程的学生人数
select course_id as '课程ID',count(student_id) as '学生人数' from score group by course_id;
41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
SELECT student.sname,max(score),min(score) from score
INNER 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 = '王五'))
GROUP BY student_id
ORDER BY max(score) DESC,MIN(score) ASC
LIMIT 2
42、查询各个课程及相应的选修人数；
SELECT score.course_id as 'ID',course.cname as '课程',count(student_id) as '人数' from score
LEFT JOIN course on score.course_id = course.cid
GROUP BY 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 score.sid,score.course_id,score.score,t1.first_score,t1.sencond_score from score LEFT JOIN
(SELECT sid,
(select score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 0,1) as first_score,
(select score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 1,1) as sencond_score
from score as s1)as t1 on score.sid = t1.sid
WHERE score.score <= t1.first_score and score.score >= t1.sencond_score
45、检索至少选修两门课程的学生学号；
SELECT score.student_id as '学生ID',count(course_id) as '课程ID' from score
GROUP BY student_id
HAVING count(course_id)>=2
46、查询没有学生选修的课程的课程号和课程名；
SELECT cid,cname from course
WHERE cid not IN(
SELECT score.course_id from score
GROUP BY score.course_id)
47、查询没带过任何班级的老师id和姓名；
SELECT tid,tname from teacher
WHERE tid not in(
SELECT teach2cls.cid from teach2cls
GROUP BY teach2cls.cid)
48、查询有两门以上课程超过80分的学生id及其平均成绩；
SELECT score.student_id as '学生ID',avg(score) as '平均成绩' from score
WHERE score > 30
GROUP BY student_id
HAVING count(course_id) >=2
49、检索“3”课程分数小于60，按分数降序排列的同学学号；
select score.student_id from score
WHERE score < 60 and course_id = 3
ORDER BY score DESC
50、删除编号为“2”的同学的“1”课程的成绩
delete from score where score.student_id = 2 and score.course_id = 1;
51、查询同时选修了物理课和生物课的学生id和姓名；
SELECT sid,sname from student
WHERE sid in(
SELECT score.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)
