sql作业

import pymysql  # 导入模块

connect = pymysql.connect(host="127.0.0.1",
                          port=3306,
                          user="root",
                          password="200830",
                          database="王企鹅群",
                          charset="utf8mb4")

cursor = connect.cursor()  # 创建游标对象


# 1、查询所有的课程的名称以及对应的任课老师姓名
def check_all_teacher():
    sql = '''select course.cname, teacher.tname from
            course right join teacher on course.teacher_id = teacher.tid;'''
    cursor.execute(sql)


# 2、查询学生表中男女生各多少人
def count_of_gender():
    sql = """select gender,count(1) as "人数" from student group by gender;"""
    cursor.execute(sql)


# 3、查询物理成绩等于100的学生姓名
def full_marks_of_physical():
    sql = """select student.sname,score.num from student
            inner join score on student.sid=score.student_id
            where score.num=100;"""
    cursor.execute(sql)


# 4、查询平均成绩大于八十分的同学的姓名和平均成绩
def the_avg_of_80():
    sql = """select student.sname,avg(score.num) as "平均分"  from student
            inner join score on student.sid=score.student_id
             group by student_id
             having avg(score.num)>80;"""
    cursor.execute(sql)


# 5、查询所有学生的学号，姓名，选课数，总成绩
def Overall_result_of_all():
    sql = """select student.sid, student.sname,count(score.course_id) as course_cunt,
    sum(score.num) as sum_score from student left join score on student.sid=score.student_id
    group by score.student_id;"""
    cursor.execute(sql)


# 6、 查询姓李老师的个数
def name_of_lee():
    sql = """select * from teacher where tname like '李%';"""
    cursor.execute(sql)


# 7、 查询没有报李平老师课的学生姓名
def student_not_in_teacher_lee():
    sql = """select distinct student.sname from student inner join
    score on student.sid=score.student_id where score.course_id not in
    (2);"""
    cursor.execute(sql)


# 8、 查询物理课程比生物课程高的学生的学号
def check_studentid_pmtb():
    sql = """select c1.student_id from (select * from score where course_id=1)c1
     inner join
     (select * from score where course_id=2)c2
     on c1.student_id=c2.student_id
     where c1.num>c2.num;"""
    cursor.execute(sql)


# 9、 查询没有同时选修物理课程和体育课程的学生姓名
def both_none_of_pp():
    sql = """select sname, score.student_id from student inner join
     score on  score.student_id=student.sid
     where course_id in (2,3)
     group by student_id
     having count(student_id)=1;"""
    cursor.execute(sql)


# 10、查询挂科超过两门(包括两门)的学生姓名和班级
def mt_two_hang_section():
    sql = """select sname,class.caption from student inner join
    class on class_id=cid where sid in
    ( select student_id from score where num <60 group by student_id having count(student_id)>1);"""
    cursor.execute(sql)


# 11 、查询选修了所有课程的学生姓名
def all_select_student():
    sql = """select sname from student where sid in
    (select student_id from score
    group by student_id
    having count(course_id)=4);"""
    cursor.execute(sql)
    
    
# 12、查询李平老师教的课程的所有成绩记录
def all_about_lp():
    sql = """select * from score where course_id in
    (select cid from course inner join teacher on course.teacher_id = teacher.tid
    where teacher.tid=2);"""
    cursor.execute(sql)

# 13、查询全部学生都选修了的课程号和课程名
def all_course_selected():
    sql = """select * from course where cid in (select course_id from score group by student_id
    having count(course_id)=4);"""
    cursor.execute(sql)

# 14、查询每门课程被选修的次数
def count_of_course():
    sql = """select course.cid, course.cname, count(score.course_id) as count from course
     inner join score on course.cid = score.course_id group by
     score.course_id;"""
    cursor.execute(sql)

# 15、查询之选修了一门课程的学生姓名和学号
def one_course_ss():
    sql = """select student.sname, student.gender, count(score.course_id) as count from student
    inner join score on student.sid = score.student_id
    group by score.student_id
    having count(score.course_id)=1;"""
    cursor.execute(sql)

# 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
def score_of_all_student():
    sql = """select score.course_id,student.sname, student.gender, score.num from student
    inner join score on student.sid = score.student_id
    group by score.sid
    order by score.num desc;"""
    cursor.execute(sql)
    
# 17、查询平均成绩大于85的学生姓名和平均成绩
def avg_score_mtef():
    sql = """select score.course_id,student.sname, student.gender, avg(score.num) as avg from student
    inner join score on student.sid = score.student_id
    group by score.student_id
    having avg(score.num)>85;"""
    cursor.execute(sql)

# 18、查询生物成绩不及格的学生姓名和对应生物分数
def failed_in_biological():
    sql = """select student.sname, student.gender, score.num from student
    inner join score on student.sid = score.student_id
    where score.course_id=1
    group by score.student_id
    having num<60;"""
    cursor.execute(sql)
    

# 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
def the_best_student_oflp():
    sql = """select sid,student_id, avg(num) as avg from score where course_id in
    (select cid from course inner join teacher on course.teacher_id = teacher.tid
    where teacher.tid=2)
    group by score.course_id
    order by avg(score.num) desc
    limit 1;"""
    cursor.execute(sql)
    
    
# 20、查询每门课程成绩最好的前两名学生姓名
def best_2_student():
    sql = """select sname, new1.course, new1.num from student inner join
    (select course.cname as course, score.student_id as ssid,score.num as num from course
    inner join score on course.cid = score.course_id
    order by score.course_id, score.num desc) new1
    on student.sid = new1.ssid
    """
    cursor.execute(sql)

# 21、查询不同课程但成绩相同的学号，课程号，成绩
def both_score_not_course():
    sql = """select  score.course_id, score.num, student.sname,score.student_id
    from score inner join student
    on student.sid = student_id
    group by score.course_id
    having count(score.num)>1
    order by score.course_id,score.num desc;
    """

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

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

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