Mysql语句练习,班级查找,学生查找

如感兴趣请搜索下载:在主页的资源中:Mysql作业压缩文件

1、查询所有的课程的名称以及对应的任课老师姓名

select cname,tname from course inner join teacher on teacher_id=tid;

2、查询学生表中男女生各有多少人

select gender,count(sid) from student group by gender='女' having count(sid);

3、查询物理成绩等于100的学生的姓名

select sname from student where student.sid in(select student_id from score inner join course on course_id=cid and cname='物理'and num=100);

第一步:取出student_id号

select student_id from score inner join course on course_id=cid and cname='物理'and num=100;

第二步:对比student_id号

select sname from student where student.sid in(select student_id from score inner join course on course_id=cid and cname='物理'and num=100);

4、查询平均成绩大于八十分的同学的姓名和平均成绩

第一步:以名字分组并且得到平均分数 >80的名字和平均成绩

select sname,avg(num) from student inner join score on student_id =student.sid group by sname having avg(num)>80;

5、查询所有学生的学号，姓名，选课数，总成绩

select student.sid,sname,count(course_id),sum(num) from student inner join score on student_id=student.sid group by student.sid ;

6、 查询姓李老师的个数

select tname from teacher WHERE tname like '李%' GROUP BY tname;

7、 查询没有报李平老师课的学生姓名

第一步:得到李平老师教的课程id

select cid from course inner join teacher on teacher_id = tid where tname = '李平老师'

第二步:得到没有报李平老师课的学生姓名

select student.sname from student where sname not in(select sname from student  inner join score on student.sid =score.student_id WHERE course_id NOT in (select cid from course inner join teacher on teacher_id = tid where tname = '李平老师'))

8、 查询物理课程比生物课程高的学生的学号

第一步:得到两个课程的学号和成绩

select student_id,num from score WHERE course_id =(select cid from course where cname='物理');
select student_id,num from score WHERE course_id =(select cid from course where cname='生物');

第二步:对比两个的分数高低:(加上as t1/t2)

select t1.student_id FROM (select student_id,num from score WHERE course_id =(select cid from course where cname='物理'))as t1 inner join 
(select student_id,num from score WHERE course_id =(select cid from course where cname='生物'))as t2 on t1.student_id=t2.student_id WHERE t1.num>t2.num;

9、 查询没有同时选修物理课程和体育课程的学生姓名

第一步:得到同时选了两门课的同学id号

select t1.student_id FROM (select student_id from score WHERE course_id =(select cid from course where cname='物理'))as t1 inner join 
(select student_id from score WHERE course_id =(select cid from course where cname='体育'))as t2 on t1.student_id=t2.student_id ;

第二步:对比id号不存在的就是没有同时选择的.

select sname from student WHERE sid not in ( select t1.student_id FROM (select student_id from score WHERE course_id =(select cid from course where cname='物理'))as t1 inner join 
(select student_id from score WHERE course_id =(select cid from course where cname='体育'))as t2 on t1.student_id=t2.student_id );

10、查询挂科超过两门(包括两门)的学生姓名和班级

第一步:先得到连续挂科两门的学生id

select student_id from score where num<60 HAVING (count(num<60)>=2);

第二步:得到名字和班级id

select sname,class_id from student where student.sid in (select student_id from score where num<60 HAVING (count(num<60)>=2));

第三步:得到班级名称:

select sname,caption from (select sname,class_id from student where student.sid in (select student_id from score where num<60 HAVING (count(num<60)>=2))) as b left join class on class.cid = b.class_id;

11 、查询选修了所有课程的学生姓名

第一步:得到选修课程的课程数量:

select count(cid) from course;

第二步:分组下筛选同id下的学生有多少个class_id

select sname from student inner join score on student_id=student.sid GROUP BY student_id having count(class_id)=(select count(cid) from course);

12、查询李平老师教的课程的所有成绩记录

第一步:得到李平老师教的那个课程id号

select tid from teacher where tname='李平老师';

第二步:得到李平老师所教的课程id号:

select cid from course where teacher_id=(select tid from teacher where tname='李平老师');

第三步:得到选择此课程的学生id: 需要得到成绩

select num from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));

13、查询全部学生都选修了的课程号和课程名

第一步:得到全部学生的数量:

select count(sid) from student;

第二步:分组查看选课数量是不是等于学生数量,得到课程id:

select course_id from student inner join score on student.sid = student_id group by course_id having count(course_id)=(select count(sid) from student);

第三步:得到课程名称:

select cname from course where cid=(select course_id from student inner join score on student.sid = student_id group by course_id having count(course_id)=(select count(sid) from student));

14、查询每门课程被选修的次数

select course_id,count(course_id) from student inner join score on student.sid = student_id group by course_id ;

15、查询之选修了一门课程的学生姓名和学号

第一步:得到成绩表中corese_id数量为一的学生学号:

select student_id from score group by student_id having count(course_id)=1;

第二步:通过id得到学生名字和id号:

select sname,sid from student where sid in (select student_id from score group by student_id having count(course_id)=1);

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

select distinct num from score order by num desc;

17、查询平均成绩大于85的学生姓名和平均成绩

select sname,avg(num) from score inner join student on student.sid=student_id group by sname having avg(num)>85;

18、查询生物成绩不及格的学生姓名和对应生物分数

第一步:得到生物课程的id号:

select cid from course where cname='生物';

第二步:得到生物课程分数低于60分的学生id和分数:

select * from student where student.sid in b.student_id (select student_id,num from score where course_id=(select cid from course where cname='生物') having num<60)as b;

第三步:得到学生名字:

select sname,b.num from student inner join ((select student_id,num from score where course_id=(select cid from course where cname='生物') having num<60))as b on student.sid=b.student_id ;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

第一步:得到李平老师教的那个课程id号

select tid from teacher where tname='李平老师';

第二步:得到李平老师所教的课程id号:

select cid from course where teacher_id=(select tid from teacher where tname='李平老师');

第三步:得到学生id: 得到平均成绩(平均成绩最高的学生姓名)

select sname from student where sid =(select student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师')) group by student_id order by  avg(num) desc limit 1);

20、查询每门课程成绩最好的前两名学生姓名

第一步:得到课程id:

select cid from course ;

第二步: 按照课程id分组:

21、查询不同课程但成绩相同的学号，课程号，成绩

第一步: 不同课程:但是成绩相同

的学号课程号与成绩

22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；

第一步得到李平老师教的课程id

select cid from course where teacher_id=(select tid from teacher where tname='李平老师');

第二步得到有学李平老师课程的学生id

select * from score inner join student on student.sid=student_id where course_id in(select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));

第三步:得到学生姓名和课程名称:

select sname,cname from course right join (select course_id,sname from score right join (select sid,sname from student where sid not in (select distinct student_id from score  where course_id in(select cid from course where teacher_id=(select tid from teacher where tname='李平老师'))))as f on student_id=f.sid)as t on course.cid=t.course_id;

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

第一步:得到学号唯一的同学所选修的课程id

select course_id from score where student_id=1;  1 2 4 

第二步:得到学生学号和名字:

select student_id,sname from student right join (select distinct student_id  from score where course_id in  (select course_id from score where student_id=1)) as f on student.sid=f.student_id;

24、任课最多的老师中学生单科成绩最高的学生姓名

题目