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
makefile实例select cid from course inner join teacher on teacher_id = tid where tname = '李平⽼师'
第⼆步：得到没有报李平⽼师课的学⽣姓名mysql面试题学生表
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 c
ourse 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、查询全部学⽣都选修了的课程号和课程名
第⼀步：得到全部学⽣的数量:
shell里面的if语句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、查询之选修了⼀门课程的学⽣姓名和学号
gzip是前端开启还是后端第⼀步：得到成绩表中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 havi
ng 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='李平⽼师'));
java银行项目一般做什么第三步：得到学⽣姓名和课程名称:
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 23、查询所有选修了学号为1的同学选修过的⼀门或者多门课程的同学学号和姓名；
第⼀步：得到学号唯⼀的同学所选修的课程id
cms系统网站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、任课最多的⽼师中学⽣单科成绩最⾼的学⽣姓名
题⽬
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论