第四模块-mysql作业

 

二、操作表

1、自行创建测试数据；

2、查询学生总人数；

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；

4、查询每个年级的班级数，取出班级数最多的前三个年级；

5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；

6、查询每个年级的学生人数；

7、查询每位学生的学号，姓名，选课数，平均成绩；

8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；

9、查询姓“李”的老师的个数和所带班级数；

10、查询班级数小于5的年级id和年级名；

11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果 如下；

12、查询学过“张三”老师2门课以上的同学的学号、姓名；

13、查询教授课程超过2门的老师的id和姓名；

14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；

15、查询没有带过高年级的老师id和姓名；

16、查询学过“张三”老师所教的所有课的同学的学号、姓名；

17、查询带过超过2个班级的老师的id和姓名；

18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

19、查询所带班级数最多的老师id和姓名；

20、查询有课程成绩小于60分的同学的学号、姓名；

21、查询没有学全所有课的同学的学号、姓名；

22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；

24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；

25、删除学习“张三”老师课的score表记录；

26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课 程的平均成绩；

27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文, 数学,英语,课程数和平均分；

28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

30、课程平均分从高到低显示（现实任课老师）；

31、查询各科成绩前三名的记录(不考虑成绩并列情况) ；

32、查询每门课程被选修的学生数；

33、查询选修了2门以上课程的全部学生的学号和姓名；

34、查询男生、女生的人数，按倒序排列；

35、查询姓“张”的学生名单；

36、查询同名同姓学生名单，并统计同名人数；

37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；

40、求选修了课程的学生人数

41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

42、查询各个课程及相应的选修人数；

43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

44、查询每门课程成绩最好的前两名学生id和姓名；

45、检索至少选修两门课程的学生学号；

46、查询没有学生选修的课程的课程号和课程名；

47、查询没带过任何班级的老师id和姓名；

48、查询有两门以上课程超过80分的学生id及其平均成绩；

49、检索“3”课程分数小于60，按分数降序排列的同学学号；

50、删除编号为“2”的同学的“1”课程的成绩；

51、查询同时选修了物理课和生物课的学生id和姓名；

create table class(
cid int primary key auto_increment,
caption char(8),
grade_id int
);

insert into class(caption,grade_id) values
('一年一班',1),
('二年一班',2),
('三年一班',3)
;

create table student(
sid int primary key auto_increment,
sname varchar(8),
gender enum('男','女'),
class_id int
);


insert into student(sname,gender,class_id) values
('乔丹','女',1),
('艾弗森','女',1),
('科比','男',2)
;

create table teacher(
tid int primary key auto_increment,
tname varchar(8)
);

insert into teacher(tname) values
('张三'),('李四'),('王五');

create table score(
sid int primary key auto_increment,
student_id int not null,
course_id int not null,
score int not null
);

insert into score(student_id,course_id,score) values
(1,1,60),(1,2,59),(2,2,99);

create table class_grade(
gid int primary key auto_increment,
gname varchar(8)
);

insert into class_grade(gname) values
('一年级'),('二年级'),('三年级');

create table teach2cls(
tcid int primary key auto_increment,
tid int,
cid int
);

insert into teach2cls(tid,cid) values
(1,1),(1,2),(2,1),(3,2);

create table course(
cid int primary key auto_increment,
cname char(6),
teacher_id int
);

insert into course(cname,teacher_id) values
('生物',1),('体育',1),('物理',2);

 

 2、查询学生总人数；

select count(sid) as 学生总数 from 作业.student;

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
select 
  sid,sname
  from student
  where sid in
(select 
  student_id   #得到物理、生物都及格得人得id
from score 
  where course_id in
 (select 
  cid
  from 
  course
  where 
  cname ='生物' or cname = '物理')  # 得到所有课程id
and score >= 60
 group by student_id 
 having max(course_id)=3 
 and min(course_id) =1 
);

4、查询每个年级的班级数，取出班级数最多的前三个年级；
select
  gname  
from 
  class_grade
  group by gname
  order by gid desc
limit 3;

5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
select 
sid,sname,avg_sc
from student 
inner join 
(select 
  student_id,avg(score) as avg_sc
  from score
  group by student_id
  having avg(score) ) as t1      #将score中得到的id与avg_score重新定义成一张表
on
student.sid = t1.student_id
;

6、查询每个年级的学生人数；

select
gname,count(gid)
from
(select 
cid,caption,grade_id,sname,class_id
from 
class
left join 
student
on 
cid=class_id) as t1 # 得到班级与学生得关联表
right join
class_grade
on
class_grade.gid = t1.grade_id  #得到年级与班级，学生得关联表
group by gname;


7、查询每位学生的学号，姓名，选课数，平均成绩；
select 
student.sid,sname as 姓名,count(course_id)as 选课数,avg(score) as 平均成绩
from 
student
left join
score
on student.sid = score.student_id
group by sname;

8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
select
sid,sname,cname,score
from
(select
student.sid,sname,course_id,score
from
student 
right join 
score 
on student.sid = score.student_id) as t1 
inner join
course
on
t1.course_id =course.cid
where sid =2
group by sname
having max(score) and min(score);


9、查询姓“李”的老师的个数和所带班级数；
select
count(tid)as 李老师,count(cid) as class_num
from
(select
teacher.tid,tname,cid
from
teach2cls
inner join 
teacher 
on
teacher.tid =teach2cls.tid)as t1
where tname like '李%'
group by tname;


10、查询班级数小于5的年级id和年级名；
select
gid,gname
from
(select
gid,gname,caption
from
class_grade
inner join
class
on
class_grade.gid = class.grade_id)as t1
group by gname
having count(caption)<5
order by  gid asc;


_____11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果
如下；
select 
    class.cid,class.caption,class_grade.gid,
    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,
          class_grade
     where
          class.grade_id=class_grade.gid;



12、查询学过“张三”老师2门课以上的同学的学号、姓名；
select
sid,sname
from 
student
where sid in(
select
student_id
from
score
inner join
(select
tname,cname,cid
from 
teacher
right join 
course
on tid = teacher_id) as t1  #得到tname,cname,cid表
on t1.cid = score.course_id   #得到老师，学生id，课程表
where tname ='张三'
group by student_id
having count(cname)>=2);


13、查询教授课程超过2门的老师的id和姓名；
select
tid,tname
from
teacher
left join
course
on
teacher.tid=course.teacher_id
group by tname
having count(cname) >=2;


_____14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
select 
sid,sname
from 
student
where sid in
(select student_id from score where course_id = 1 and student_id in 
(select student_id from score where course_id =2) );   # 学生id在课程1的名单中，学生id在课程2的名单中



15、查询没有带过高年级的老师id和姓名；
select
  tid,tname
  from
  teacher
  where tid in(
    select
    distinct
    tid 
    from
    teach2cls
    left join
      (select 
      gid,gname,cid
      from 
      class_grade
      left join
      class
      on
      gid = grade_id)as t1   # 班级年级表
      on teach2cls.cid = t1.cid    #班级年级老师id表
      where gid<5);


16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
select
sid,sname
from
student
where sid in
(select
student_id
from
score
where course_id in  # 张三老师所教学生的id
(select
cid
from
teacher
right join
course
on
tid=teacher_id
where tname ='张三') );  # 张三老师课程id


17、查询带过超过2个班级的老师的id和姓名；
select 
tid,tname
from teacher
where tid in(
select
tid
from
teach2cls
group by cid
having count(tid));


_____18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select sid,sname from student where(
(select score.score from score where score.student_id = student.sid and course_id = 2)
<
(select score.score from score where score.student_id = student.sid and course_id = 1)
);


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 sid 
from score
where 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 student_id 
from score
where course_id in( 
select course_id from score    #找到id=1的所有课程，然后找到在选此课程的学生id
where student_id =1))

23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
select
sid,sname
from 
student
where sid !=1 and sid in(
select student_id 
from score
where course_id in(
select course_id from score
where 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  #将2号学生所选课程重新拟表
where score.student_id !=2
and score.course_id = t1.course_id）



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,score) values( 
select sid from student where sid not in(
select student_id from score where course_id !=2)),(
select avg(score) from score where course_id =2 
group by student_id);



_________27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生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 = score.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,max(score),min(score)
from score
group by course_id;



______29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id,avg(score),
sum(case 
        when score.score >=60 
        then 1 else 0
        end) /count(score) as pass_rate   # 统计出所有>=60/单科所有学生数
from score
group by course_id
order by avg(score) asc,pass_rate desc;



30、课程平均分从高到低显示（现实任课老师）；
select t1.tname,avg(score)
 from
 (select
 tid,tname,cid,cname
 from
 teacher
 left join
 course
 on tid = teacher_id)as t1,score
 where t1.cid in (select score.course_id from score group by score.course_id)
 order by avg(score);


______31、查询各科成绩前三名的记录(不考虑成绩并列情况) ；
select
*
from
(select
cid,cname,score
from course
left join 
score
on course.cid = score.course_id) as t1 # t1,t2表：各科成绩明细
where 
(select count(cid) 
from 
(select cid,cname,score from course left join  score on course.cid = score.course_id) as t2
 where t1.cname = t2.cname and t1.score>t2.score)<=3  #得到科目名一样，科目分数前三位


32、查询每门课程被选修的学生数；
select 
cname,count(student_id)
from course
left join 
score
on cid = course_id
group by cname ;



33、查询选修了2门以上课程的全部学生的学号和姓名；
select 
sid,sname
from student
where sid in(
select 
student_id
from
score 
group  by course_id
having count(student_id)>=2);


34、查询男生、女生的人数，按倒序排列；
select
count(
case
when gender='女' 
then 1 
end) as '女生',
count(
case
when gender='男' 
then 0
end) as '男生' 
from
student
;



35、查询姓“乔”的学生名单；
select
sid,sname,gender
from student
where sname like '乔%';




36、查询同名同姓学生名单，并统计同名人数；
select
*
from student t1
where student.sname = t1.sname 
and 
student.sid in (
select sid from student where student.sname = t1.sname)


37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select 
cid,cname,avg(score)
from course
left join
score
on cid = course_id 
group by cname
order by avg(score) asc,cid desc;



38、查询课程名称为“体育”，且分数低于60的学生姓名和分数；
select 
student.sid,sname,score
from student
inner join 
score
on student.sid = student_id
where course_id = (select cid from course where cname = '体育') and score<60;



39、查询课程编号为“2”且课程成绩在80分以上的学生的学号和姓名；
select
sid,sname
from
student
where sid in(
select student_id from score where course_id =2 and score >80);




40、求选修了课程的学生人数
select course_id,count(student_id) as count_student 
from score
group by course_id;



41、查询选修“张三”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
select 
student.sid,sname,score,course_id
from 
student
inner join
score
on student.sid =student_id
where course_id in
(select cid from course where teacher_id =
(select tid from teacher where tname = "张三"))   #得到老师id——得到老师授课id


42、查询各个课程及相应的选修人数；

select course_id,count(student_id) as count_student 
from score
group by course_id;

43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select * from
(select 
student.sid,course_id,score
from 
student
inner join
score
on student.sid = student_id)as t1
where t1.course_id !=t1.course_id and t1.score = t1.score;


44、查询每门课程成绩最好的前两名学生id和姓名；
select
*
from (
select
course_id,student_id,sname,score
from score
inner join 
student
on student.sid = student_id) as t
where student_id in (select student_id 
from score as t1
where (
select count(sid) from score where score=t1.score and score>t1.score )<=2);  #挑选出单科成绩最好的两名同学id


45、检索至少选修两门课程的学生学号；
select student_id 
from score
group by student_id
having count(course_id)>=2;



46、查询没有学生选修的课程的课程号和课程名；
select cid,cname
from course 
where cid not in(
select course_id from score group by course_id);



47、查询没带过任何班级的老师id和姓名；
select tid,tname
from teacher
where tid not in(
select t1.tid from teach2cls as t1
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 student_id,score from score
where score<60
and course_id = 3
order by score.score desc;



50、删除编号为“2”的同学的“1”课程的成绩；
delete score from score where student_id=2 and course_id=1

51、查询同时选修了体育课和生物课的学生id和姓名；
select sid,sname
from student
where sid in 
(select student_id from score where course_id = (
select cid from course where cname ='生物') )    #得到选修生物客学生id
and sid in 
(select student_id from score where course_id = (
select cid from course where cname ='体育'));     #得到所有选择体育客的学生id