1.创建测试数据
# 创建本次作业的数据库
create database mysql_homework;
# 首先创建老师表
use mysql_homework;
create table teacher(
tid int primary key auto_increment,
tname varchar(30)
);
insert into teacher(tname) values
('张三'),
('李四'),
('王五')
;
# 首先创建学生表
create table student(
sid int primary key auto_increment,
sname varchar(30),
gender enum("男","女") not null default "男"
);
insert into student(sname) values
('乔丹'),
('艾弗森'),
('科比')
;
# 创建年级表
create table class_grade(
gid int primary key auto_increment,
gname varchar(30)
);
insert into class_grade(gname) values
('一年级'),
('二年级'),
('三年级')
;
# 创建班级表
create table class(
cid int primary key auto_increment,
caption varchar(20),
grade_id int,
foreign key(grade_id) references class_grade(gid)
on delete cascade
on update cascade
)engine=innodb;
insert into class(caption,grade_id) values
('一年级一班',1),
('二年级一班',2),
('三年级二班',3)
;
# 创建课程表
create table course(
cid int primary key auto_increment,
cname varchar(20),
teacher_id int,
foreign key(teacher_id) references teacher(tid)
on delete cascade
on update cascade
)engine=innodb;
insert into course(cname,teacher_id) values
('生物',1),
('体育',1),
('物理',2)
;
# 创建班级任职表
create table teach2cls(
tcid int not null unique 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,
primary key(tid,cid)
);
insert into teach2cls(tid,cid) values
(1,1),
(1,2),
(2,1),
(3,2)
;
# 创建成绩表
create table score(
sid int not null unique 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,
primary key(student_id,course_id)
);
insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(2,2,99)
;
2.查询学生总人数
select count(*) as '学生总数' from student sid;
3.查询“生物”课程和“物理”课程成绩都及格的学生id和姓名;
select sid as '学号',sname as '姓名' from student
where sid in (
select student_id
from score
where score>60 and course_id in
(
select cid from course
where cname in ('生物','物理')
)
group by student_id
having count(student_id)= 2
) ;
4.查询每个年级的班级数,取出班级数最多的前三个年级;
select class_grade.gname as'年级',count(caption) as '班级数'
from class left join class_grade on grade_id = gid
group by grade_id
order by count(caption) desc
limit 3;
5.查询平均成绩最高和最低的学生的id和姓名以及平均成绩;
select t3.student_id as "学号", t4.sname as "姓名", t3.avg_score as "平均成绩"
from (
select *
from (
select student_id, avg(score) as avg_score
from score
group by student_id
order by avg(score) desc
limit 1
)as t1
union
select *
from (
select student_id,avg(score)
from score
group by student_id
order by avg(score)
limit 1
)as t2
)as t3
inner join student as t4
on t3.student_id = t4.sid
;
6.查询每个年级的学生人数;
select gname as '年级', num as '总人数' from (select class.grade_id as cg_id,count(sid) as num from student
left join class on class_id = cid
group by class.grade_id)as t2
left join class_grade on t2.cg_id = class_grade.gid;
7.查询每位学生的学号,姓名,选课数,平均成绩;
select student.sid as '学号',student.sname as '姓名',count(score.course_id) as '选课数',avg(score.score) as '平均分' from score
left join student on
score.student_id = student.sid
group by student_id;
8.查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数;
select sname as '姓名',cname as '课程名',score as '分数'
from (select sname,course_id,score from score left join
student
on score.student_id = student.sid
where student.sid = 2) as t3 left join course
on t3.course_id= course.cid
where t3.score in ((select max(score) from t3),
(select min(score) from t3));
9.查询姓“李”的老师的个数和所带班级数;
select count(teacher.tid) as '李老师人数', GROUP_CONCAT(teach2cls.cid) as '班级数' from teacher
right join teach2cls
on teacher.tid = teach2cls.tid
where teacher.tname like '李%'
group by teacher.tid;
10.查询班级数小于5的年级id和年级名;
select gid ,class_grade.gname as'年级'
from class left join class_grade on grade_id = gid
group by grade_id
having count(caption) <5;
11.查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级),示例结果如下;
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门课以上的同学的学号、姓名;
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和姓名;
select teacher.tid, teacher.tname as '姓名'
from course
left join teacher
on course.teacher_id = teacher.tid
group by teacher_id
having count(cid) > 2;
14.查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;
select student.sid as '学号', student.sname as '姓名' from score
left join student
on score.student_id = student.sid
where course_id in (1,2)
group by student_id
having count(course_id) =2;
15.查询没有带过高年级的老师id和姓名;
select teach2cls.tid,teacher.tname from teach2cls
left join teacher
on teach2cls.tid = teacher.tid
where cid not in
(
select cid from class
where grade_id > 4
)
group by tid;
16.查询学过“张三”老师所教的所有课的同学的学号、姓名;
select score.student_id, student.sname from score
left join student
on score.student_id = student.sid
where score.course_id in
(
select cid from course
where teacher_id =
(
select tid from teacher
where tname = '张三'
)
)
group by student_id;
17.查询带过超过2个班级的老师的id和姓名;
select teach2cls.tid,teacher.tname from teach2cls
left join teacher
on teach2cls.tid = teacher.tid
group by tid
having count(cid)> 2;
18.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;
select student.sid,student.sname from student
where student.sid in
(
select t1.student_id from
(
select student_id,score from score
where course_id = 1
group by student_id
)as t1,
(
select student_id,score from score
where course_id = 2
group by student_id
)as t2
where t1.student_id = t2.student_id
and t2.score<t1.score
);
19.查询所带班级数最多的老师id和姓名;
select teacher.tid, teacher.tname from teacher
where teacher.tid =
(
select teach2cls.tid from teach2cls
group by teach2cls.tid
order by count(cid) desc
limit 1
);
20.查询有课程成绩小于60分的同学的学号、姓名;
select student.sid,student.sname from student
where student.sid in
(
select distinct score.student_id from score
where score.score < 60
);
21.查询没有学全所有课的同学的学号、姓名;
select concat('学号:',student.sid,'>>>姓名:',student.sname) as '没有学全所有课的同学'from student
where student.sid in
(
select distinct score.student_id from score
group by student_id
having count(course_id) <
(
select count(cid) from course
)
);
22.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;
select concat('学号:',student.sid,' 姓名:',student.sname)
as '至少有一门课与学号为“1”的同学所学相同的同学'
from student
where student.sid in
(
select distinct score.student_id from score
where score.course_id in
(
select course_id from score
where student_id = 1
)
);
23.查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名;
select concat('学号:',student.sid,' 姓名:',student.sname)
as '至少学过学号为“1”同学所选课程中任意一门课的其他同学'
from student
where student.sid in
(
select distinct score.student_id from score
where score.course_id in
(
select course_id from score
where student_id = 1
)
having student_id != 1
);
24.查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名;
select concat('学号:',student.sid,' 姓名:',student.sname)
as '和“2”号同学学习的课程完全相同的其他同学'
from student
where student.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 course
where teacher_id = (
select tid from teacher
where tname = '张三'
)
);
26.向score表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“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 *,(case when 数学 is null then 0 else 1 end)+
(case when 英语 is null then 0 else 1 end)+
(case when 语文 is null then 0 else 1 end)
as 有效成绩数,
(语文+数学+英语)/(
(case when 数学 is null then 0 else 1 end)+
(case when 英语 is null then 0 else 1 end)+
(case when 语文 is null then 0 else 1 end)
) as 有效平均分
from
(
SELECT student_id AS 学生ID,
(SELECT score FROM score LEFT JOIN course on score.course_id=course.cid
WHERE course.cname="语文" AND student_id=s1.student_id) AS 语文,
(SELECT score FROM score LEFT JOIN course on score.course_id=course.cid
WHERE course.cname="数学" AND student_id=s1.student_id) AS 数学,
(SELECT score FROM score LEFT JOIN course on score.course_id=course.cid
WHERE course.cname="英语" AND student_id=s1.student_id) AS 英语
FROM score as s1 GROUP BY student_id
)as t2;
28.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
select course_id,max(score) as max_score,min(score) as min_score
from score
group by course_id;
29.按各科平均成绩从低到高和及格率的百分数从高到低顺序;
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.课程平均分从高到低显示(现实任课老师);
select course.cname, avg(score) as'平均分' from course
right join score
on course.cid = score.course_id
group by score.course_id
order by avg(score) desc;
31.查询各科成绩前三名的记录(不考虑成绩并列情况)
SELECT t1.student_id,t1.course_id,t1.score
FROM score as t1
WHERE
(SELECT COUNT(course_id)
FROM score
WHERE t1.course_id= course_id AND t1.score<score)<3
ORDER BY t1.course_id,score DESC;
32.查询每门课程被选修的学生数;
select course.cname as '课程名', t1.sum as '学生数' from course,
(select course_id,count(student_id) as sum from score
group by course_id
)as t1
where course.cid = t1.course_id;
33.查询选修了2门以上课程的全部学生的学号和姓名;
select student.sid,student.sname from student
where student.sid in (
select 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,count(sid)as'同名人数' from student
group by sname
having count(sid)>1;
37.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
select course.cname, avg(score) as'平均分' from course
right join score
on course.cid = score.course_id
group by score.course_id
order by avg(score), course.cid desc;
38.查询课程名称为“数学”,且分数低于60的学生姓名和分数;
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分以上的学生的学号和姓名;
select student.sname,score.score from score
left join student
on score.student_id = student.sid
where score.course_id = 3
and score.score>80;
40.求选修了课程的学生人数
select course_id,count(student_id) as '学生人数' from score
group by course_id;
41.查询选修“王五”老师所授课程的学生中,成绩最高和最低的学生姓名及其成绩;
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.查询各个课程及相应的选修人数;
select course.cname, count(student_id) as'选修人数' from score
left join course
on score.course_id = course.cid
group by course_id;
43.查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
select score.student_id,score.course_id,score.score from score,(select score,student_id from score
group by score,student_id
having count(course_id)>1)as t1
where score.score =t1.score and score.student_id =t1.student_id;
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 from score
group by student_id
having count(course_id)>1;
46.查询没有学生选修的课程的课程号和课程名;
select cid,cname from course
where cid not in
(
select distinct course_id from score
);
47.查询没带过任何班级的老师id和姓名;
select tid,tname from teacher
where tid not in
(
select distinct tid from teach2cls
);
48.查询有两门以上课程超过80分的学生id及其平均成绩;
select student_id,avg(score)
as '平均成绩' from score
where score >80
group by student_id
having count(course_id)>1;
49.检索“3”课程分数小于60,按分数降序排列的同学学号;
select student_id from score
where course_id =3 and score <60
order by score desc;
50.删除编号为“2”的同学的“1”课程的成绩;
delete from score
where student_id =2 and course_id=1;
51.查询同时选修了物理课和生物课的学生id和姓名;
select sid as '学号',sname as '姓名' 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(student_id)= 2
) ;
52.查询学员信息,封装成视图
create view vstudent
as
select
gname,caption,sid,sname,gender
from
student u
inner join
class c
on
c.cid=u.class_id
inner join
class_grade g
on
g.gid=c.grade_id
53.查询每个老师不同带班数量,及其所带各班的总平均成绩
select
tname,count(distinct l.tcid) classCount,avg(score) avgscore
from
score s
right join
student u
on
s.student_id=u.sid
right join
class c
on
u.class_id=c.cid
inner join
teach2cls l
on
c.cid=l.cid
right join
teacher t
on
l.tid=t.tid
group by
tname;
54.查询各班不同科目平均成绩的最大差值
select
caption,max(avgscore)-min(avgscore)
from
(
select
caption,cname,avg(score) avgscore
from
score s
inner join
course r
on
r.cid=s.course_id
right join
student u
on
s.student_id=u.sid
right join
class c
on
c.cid=u.class_id
where
cname is not null
group by
caption,cname
having
avgscore is not null
)t
group by
caption;
55.查询每个年级,每个班男女生的人数
select
gname,caption,gender,count(sid)
from
student u
right join
class c
on
u.class_id=c.cid
right join
class_grade g
on
g.gid=c.grade_id
group by
gname,caption,gender
56.查总页码
select
ceil(count(1)/5) total
from
student