windows,pycharm,python，PyMySQL( 三 ) _索引

4查询男生、女生人数
select gender,count(1) from student group by gender;
5查询同名同姓学生名单，并统计同名人数
select sname,count(1) from student group by sname having count(1)>1;
6查询“三年级一班”的所有学生信息
select*fromstudentleft join class on student.class_id = class.cidwhere class.caption='三年级一班';
7查询每个班级的班级名称、班级人数
selectclass.caption,count(1)fromstudentleft join class on student.class_id = calss.cidgroup by class.caption;
8查询成绩小于60分的同学的学号、姓名、成绩、课程名称
select student.sid,student.sname,score.number,course.cnamefromscoreleft join student on score.student_id=student.sidleft join cource on score.course_id=course.cidwhere number<60;
9查询选修了“生物课”的所有学生ID、学生姓名、成绩
selectstudent.sid,student.sname,score.numberfromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidwherecourse.cname='生物';

文章插图
10查询选修了“生物课”且成绩低于60分的所有学生ID、学生姓名、成绩

selectstudent.sid,student.sname,score.numberfromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidwherecourse.cname='生物' and score.number<60;

11查询所有同学的学号、姓名、选课数、总成绩

selectstudent_id,student.sname,count(1),sum(number)fromscoreleft join student on score.student_id=student.sidgroup by student_id;

12查询各科选修学生的人数
selectcoourse_id,course.cname,count(1)fromscoreleft join course on course.cid=score.course_idgroup by course_id;
13查询各科成绩的总分、最高分、最低分，显示:课程ID、课程名称、总分、最高分、最低分

selectcourse_id,course.cname,sum(number),max(number),min(number)fromscoreleft join course on score.course_id=course.cidgroup by course_id;

14查询各科成绩的平均分，显示：课程id、课程名称、课程平均分
selectcourse_id,course.cname,avg(num)fromscoreleft join course on score.course_id=course.cidgroup by course_id;
15查询各科成绩的平均分，显示：课程id、课程名称、课程平均分(按照从大到小排队)

selectcourse_id,course.cname,avg(num) as Afromscoreleft join course on score.course_id=course.cidgroup by course_idorder by A desc;

16 查询各科成绩平均分和及格率，显示：课程id、课程名称、平均分、及格率

selectcourse_id,course.cname,avg(num),sum(case when score.num>60 then 1 else 0 end)/count(1)*100 as percentfromscoreleft join course on score.course_id=course.cidgroup by course_id

17查询平均成绩大于60的所有学生的学号，平均成绩
selectstudent_id,avg(number)fromscoregroup bystudent_id having avg(num)>60
18查询平均成绩大于85的所有学生的学号、平均成绩、姓名