#作业库
create database db8 charset utf8;
#年级表
create table class_grade(
gid int not null primary key auto_increment,
gname varchar(20) not null unique
);
#班级表
create table class(
cid int primary key auto_increment,
caption varchar(20) not null,
grade_id int not null,
foreign key(grade_id) references class_grade(gid)
);
#学生表
create table student(
sid int primary key auto_increment,
sname varchar(20) not null,
gender enum("女",'男') not null,
class_id int not null,
foreign key(class_id) references class(cid)
);
#老师表
create table teacher(
tid int primary key auto_increment,
tname varchar(20) not null
);
#课程表
create table course(
cid int primary key auto_increment,
cname varchar(20) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
);
#成绩表
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 unique not null 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
);
#插入数据
insert into class_grade(gname) values #4个年级
('一年级'),
('二年级'),
('三年级'),
('四年级');
insert into class(caption,grade_id) values #9个
('一年一班',1),
('一年二班',1),
('一年三班',1),
('二年一班',2),
('二年二班',2),
('三年一班',3),
('三年二班',3),
('四年一班',4),
('四年二班',4);
insert into student(sname,gender,class_id) values #12个学生
('Jane','女',1),
('Rose','女',1),
('Jack','男',2),
('Alice','女',2),
('Alex','男',3),
('Drigon','男',4),
('Lily','女',5),
('Lucy','女',6),
("Jone",'男',6),
('紫霞','女',7),
('张尊宝','男',8),
('高圆圆','女',9);
insert into teacher(tname) values #4个老师
('曹显'),
('王浩'),
('王五'),
('赵坤');
insert into course(cname,teacher_id) values #6门课程
('生物',1),
('物理',2),
('化学',3),
('语文',3),
('数学',4),
('地理',2);
#12个学生，6门课程
insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(2,4,60),
(2,5,59),
(2,6,33),
(3,1,59),
(3,5,28),
(4,4,100),
(4,6,90),
(5,4,88),
(6,5,100),
(6,6,60),
(7,3,57),
(7,5,60),
(8,2,61),
(8,4,59),
(9,1,60),
(9,2,61),
(9,3,21),
(10,5,68),
(11,1,89),
(12,3,100);
insert into teach2cls(tid,cid) values #4个老师 9个班级
(1,1),
(1,2),
(1,3),
(1,7),
(2,4),
(2,8),
(2,7),
(2,5),
(3,9),
(3,3),
(3,5),
(3,2),
(4,8),
(4,4),
(4,6),
(4,1);
# 4个老师 9个班级
insert into teach2cls(tid,cid) values
(1,1),
(1,2),
(1,3),
(1,7),
(2,4),
(2,8),
(2,7),
(2,5),
(3,9),
(3,3),
(3,5),
(3,2),
(4,8),
(4,4),
(4,6),
(4,1);
二、操作表
1、自行创建测试数据；
2、查询学生总人数；
select count(sid) 学生总人数 from student;
3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
select sid 学生ID,sname 姓名 from student
where
sid in (
select student_id from score inner join
course on score.course_id = course.cid
where cname in (
"生物",
"物理")
and score.score >= 60
group by score.student_id
having count(course_id) =2
);
4、查询每个年级的班级数，取出班级数最多的前三个年级；
select gname 年级 from class_grade inner join(
select grade_id from class
group by grade_id
order by count(caption) desc
limit 3
) t1 on t1.grade_id = class_grade.gid;
5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
select student.sid,student.sname,t1.avg_score
from student inner join(
select student_id ,avg(score) avg_score from score
group by student_id
having student_id in (
(select student_id from score
group by student_id
order by avg(score) desc
limit 1),
(select student_id from score
group by student_id
order by avg(score) asc
limit 1)
)) t1 on t1.student_id = student.sid;
6、查询每个年级的学生人数；
select class_grade.gid,gname,t1.count_sid
from class_grade inner join(
select grade_id, count(sid) count_sid
from class inner join student
on class.cid = student.class_id
group by grade_id
) t1 on t1.grade_id = class_grade.gid;
7、查询每位学生的学号，姓名，选课数，平均成绩；
select
student_id,sname,t1.count_course,t1.avg_score
from student inner join(
select
student_id,count(course_id) count_course,avg(score) avg_score
from score
group by student_id
) t1 on student.sid = t1.student_id;
8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
select
student.sid,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)
) ) t1
inner join student on t1.student_id = student.sid
inner join course on course.cid = t1.course_id;
9、查询姓“李”的老师的个数和所带班级数；
select
count(teacher.tname) 姓李的老师个数,
count(teach2cls.cid) 带班级数
from teacher left join teach2cls
on teacher.tid = teach2cls.tid
where teacher.tname like '李%';
10、查询班级数小于5的年级id和年级名；
select
gid 年级id,
gname 年级名,
count(cid)
from class inner join class_grade
on class.grade_id = class_grade.gid
group by gid
having count(cid) <5;
11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果
如下；
select
class.cid '班级id',
class.caption '班级名称',
class_grade.gname 年级,
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 inner join class_grade
on class.grade_id = class_grade.gid;
12、查询学过“张三”老师2门课以上的同学的学号、姓名；
select
student.sid '学号',
student.sname '姓名'
from
student
inner join(
select
student_id
from score
where score.course_id in (
select course.cid from course
where teacher_id in(
select tid from teacher
where tname = '张三'
)
) group by student_id
having count(course_id) >2
) as t1 on student.sid = t1.student_id;
13、查询教授课程超过2门的老师的id和姓名；
select
tid '老师id',
tname '姓名'
from teacher
where tid in (
select
teacher_id
from course
group by teacher_id
having count(cid) >2
);
14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
select
sid '学号',
sname '姓名'
from student
where sid in
(
select distinct student_id
from score where course_id in (1,2)
);
15、查询没有带过高年级的老师id和姓名；
select
tid '老师id',
tname '姓名'
from
teacher
where tid in (
select distinct
teach2cls.tid
from teach2cls inner join class on
teach2cls.cid = class.cid
where class.grade_id not in (5,6)
);
16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
select
sid '学号',
sname '姓名'
from
student
where sid in (
select distinct student_id from
score where course_id in(
select cid from course inner join teacher
on course.teacher_id = teacher.tid
where tname = '张三'
)
);
17、查询带过超过2个班级的老师的id和姓名；
select
tid 'id',
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
t2.student_id
from (select * from score
where course_id = 2) t2
inner join(
select * from score
where course_id = 1
) t1 on t2.student_id = t1.student_id
where t2.score < t1.score
)
19、查询所带班级数最多的老师id和姓名；
select
tid 'id',
tname '姓名' #3、取得结果
from
teacher
where tid in (
select tid from teach2cls cls #2、根据数值取出并列的老师ID
group by tid
having count(cid) = (
select count(cid) from teach2cls #1、求出带班级数最多的数值
group by tid
order by count(cid) desc
limit 1
)
);
20、查询有课程成绩小于60分的同学的学号、姓名；
select
sid '学号',
sname '姓名'
from
student where sid in (
select distinct student_id from score
where score.score < 60
);
21、查询没有学全所有课的同学的学号、姓名；
select
sid '学号',
sname '姓名'
from
student where sid 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 distinct
student_id
from
score where course_id in (
select course_id from score
where student_id = 1 group by course_id
)
);
23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
select
sid '学号',
sname '姓名'
from
student where sid in(
select distinct 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 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表记录；
delete
from
score where course_id in(
select cid from teacher,course
where teacher_id = tid and tname = "张三"
)
26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
insert into score(student_id,course_id,score)
select t1.sid,2,t2.avg_score 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_score from score
where course_id=2
) as t2;
27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,
数学,英语,课程数和平均分；
select
sc.student_id 学生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) 课程数,
avg(sc.score) 平均分
from score as sc
group by
sc.student_id
order by
avg(sc.score) asc;
28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select
course_id 课程ID,
max(score) 最高分,
min(score) 最低分
from
score
group by course_id;
29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select
course_id,
avg(score) '平均成绩',
(sum(case when score >= 60 then 1 else 0 end) / count(score)) *100 '及格率'
from
score
group by
course_id
order by
avg(score) asc,
'及格率' desc;
30、课程平均分从高到低显示（现实任课老师）；
select
t1.course_id '课程ID',
t1.avg_score '平均分',
teacher.tname '老师'
from course,teacher,
(
select course_id,avg(score) as avg_score from score
group by course_id
order by avg_score desc
) as t1
where
course.cid = t1.course_id
and course.teacher_id = teacher.tid
order by
t1.avg_score desc;
31、查询各科成绩前三名的记录(不考虑成绩并列情况) ；
select
score.sid,
score.student_id '学生ID',
score.course_id '课程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、查询每门课程被选修的学生数；
select course.cid '课程ID',
ifnull(t1.count_students,0) as '选修学生数'
from course left join (
select course_id,count(student_id) as count_students
from score
group by
course_id
) as t1 on course.cid = t1.course_id;
#第二种
select course_id,count(student_id) from score group by 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 '张%';
#第二种方法
select * from student where sname like "张%";
36、查询同名同姓学生名单，并统计同名人数；
SELECT sname as '名字',count(sname) as '同名人数' from student
GROUP BY sname
HAVING count(sname) >1
select sname,count(sname) from student group by sname
having count(sname) >1
37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select cid,cname,avg(score.score) as avg_score from course
inner join score on course.cid = score.course_id
group by cid
having avg(score.score)
order by avg(score.score),
course_id desc;
38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select student.sid,student.sname,t1.score from student
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 t1 on student.sid=t1.student_id;
select student.sid,student.sname,t1.score from student
right join
(select student_id,score from score where course_id in(
select cid from course where cname = '数学'
) having score < 60) t1 on t1.student_id = student.sid;
39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
select student.sid,student.sname,t1.score from student
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 t1 on student.sid = t1.student_id;
select sid, sname from student where sid in(
select student_id from score where course_id = 3 and score > 80
)
40、求选修了课程的学生人数
select course_id,count(student_id) as count_student
from score
group by course_id;
41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
select student.sid,student.sname,t2.course_id,t2.score,t2.max_score,t2.min_score from student
inner join (
select score.student_id,score.course_id,score.score,t1.max_score,t1.min_score
from score,
(
select course_id,max(score) as max_score,min(score) as min_score
from score
where course_id in (
select cid from course
inner join teacher on course.teacher_id = teacher.tid
where teacher.tname = '王五'
)
group by course_id
) as t1
where score.course_id = t1.course_id
and score.score in(
max_score,
min_score
)
)as t2 on student.sid = t2.student_id;
#第二种方法
select t1.max_sname,t1.t1.max_score ,
t2.min_sname,t2.t2.min_score
from (
select t1.student_id,sname max_sname,t1.max_score from student inner join (
select student_id,score max_score from score where course_id in
(select cid from teacher,course
where teacher.tid = course.teacher_id
and tname = '王五'
) order by score desc
limit 1 ) t1 on student.sid = t1.student_id) t1,
(
select t2.student_id,sname min_sname,t2.min_score from student inner join (
select student_id,score min_score from score where course_id in
(select cid from teacher,course
where teacher.tid = course.teacher_id
and tname = '王五'
) order by score asc
limit 1 ) t2 on student.sid = t2.student_id
)t2
42、查询各个课程及相应的选修人数；
select course.cid,course.cname,count(student_id) as count_student from course
inner join score on course.cid = score.course_id
group by course.cid
having count(student_id);
43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select distinct s1.student_id,s2.student_id,
s1.course_id as s1_course_id,
s2.course_id as 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、检索至少选修两门课程的学生学号；
select student_id,student.sname from score inner join student on score.student_id = student.sid
group by student_id
having count(course_id)>=2;
46、查询没有学生选修的课程的课程号和课程名；
select course.cid,course.cname from course
where course.cid not in (
select course_id from score
group by course_id
);
47、查询没带过任何班级的老师id和姓名；
select teacher.tid,teacher.tname from teacher
where teacher.tid not in(
select tid from teach2cls
group by tid
);
48、查询有两门以上课程超过80分的学生id及其平均成绩；
select score.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 score.student_id,score.score from score
where score<60
and course_id = 3
order by score.score desc;
50、删除编号为“2”的同学的“1”课程的成绩；
delete from score where sid=(
select t1.sid from (
select sid from score
where student_id = 2 and course_id = 1
)as t1
);
51、查询同时选修了物理课和生物课的学生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
);