• Django多表查询练习题


    #一 model表:

    from
    django.db import models # Create your models here. class Teacher(models.Model): tid=models.AutoField(primary_key=True) tname=models.CharField(max_length=32) classes=models.ManyToManyField("Klass") def __str__(self): return self.tname class Meta: db_table ="teacher" class Grade(models.Model): gid=models.AutoField(primary_key=True) gname=models.CharField(max_length=32) def __str__(self): return self.gname class Meta: db_table ="grade" class Klass(models.Model): kid=models.AutoField(primary_key=True) kname=models.CharField(max_length=32) grade=models.ForeignKey("Grade",on_delete=models.CASCADE) def __str__(self): return self.kname class Meta: db_table ="klass" class Student(models.Model): sid=models.AutoField(primary_key=True) sname=models.CharField(max_length=32) gender=models.IntegerField(choices=((0,""),(1,""))) cls=models.ForeignKey("Klass",on_delete=models.CASCADE) def __str__(self): return self.sname class Meta: db_table ="student" class Course(models.Model): cid=models.AutoField(primary_key=True) cname=models.CharField(max_length=32) teacher=models.ForeignKey("Teacher",on_delete=models.CASCADE) def __str__(self): return self.cname class Meta: db_table = "course" class Score(models.Model): sid=models.AutoField(primary_key=True) student=models.ForeignKey("Student",on_delete=models.CASCADE) course=models.ForeignKey("Course",on_delete=models.CASCADE) score=models.IntegerField() def __str__(self): return str(self.student)+str(self.course)+str(self.score) class Meta: db_table ="score" # class Meta: # unique_together = (("student","course"),)

    二.ER图

    三.查询练习题

    from django.shortcuts import render,HttpResponse
    
    # Create your views here.
    from django.db.models import Count,Avg,Min,Max,F,Q
    from .models import *
    def query(request):
        '''
    3、 查询“生物”课程和“物理”课程成绩都及格的学生id和姓名; # ?
    
    4、 查询每个年级的班级数,取出班级数最多的前三个年级;
    5、 查询平均成绩最高的学生的id和姓名以及平均成绩;
    6、 查询每个年级的学生人数;
    7、 查询每位学生的学号,姓名,平均成绩;
    8、 查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名及分数;
    9、 查询姓“李”的老师的个数和所带班级数;
    10、查询班级数小于5的年级id和年级名;
    11、查询教过课程超过2门的老师的id和姓名;
    12、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;
    13、查询所带班级数最多的老师id和姓名;
    14、查询有课程成绩小于60分的同学的学号、姓名;
    15、查询男生、女生的人数,按倒序排列;
    16、查询各个课程及相应的选修人数;
    17、查询同时选修了物理课和生物课的学生id和姓名;
    18、检索“3”课程分数小于60,按分数降序排列的同学学号;
    19、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
    20、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
    
        '''
        #2、 查询学生总人数;
        # select count(*) from student
        res = Student.objects.all().aggregate(c=Count("sid"))
        print("res",res) #  res {'c': 3}
    
        # #3、 查询“生物”课程和“物理”课程成绩都及格的学生id和姓名;(course表,score表,student表)
        # queryset = Student.objects.filter(score__course__cname="生物").filter(score__score__gt=60).values("sid","sname","score__score").distinct()
        # queryset2 = Student.objects.filter(score__course__cname="物理").filter(score__score__gt=60).values("sid","sname","score__score").distinct()
        queryset3=Score.objects.filter(score__gt=60).filter(course__cname="生物").values('student__sname','student__sid',"score","course__cname")
        queryset4=Score.objects.filter(score__gt=60).filter(course__cname="物理").values('student__sname','student__sid',"score","course__cname")
        # lst1 = [{'student__sname': '乔丹', 'student__sid': 1, 'score': 67, 'course__cname': '生物'}]
        # lst2 = [{'student__sname': '乔丹', 'student__sid': 1, 'score': 77, 'course__cname': '物理'},
        #         {'student__sname': '艾弗森', 'student__sid': 2, 'score': 88, 'course__cname': '物理'}]
    
        li1 = []
        for dic in queryset3:
            li1.append({dic["student__sname"]: dic["student__sid"]})
    
        li2 = []
        for dic2 in queryset4:
            li2.append({dic["student__sname"]: dic["student__sid"]})
    
        li3 = []
    
        for name in li1:
            if name in li2:
                li3.append(name)
        print(li3)
    
        queryset5 = Score.objects.filter(score__gte=60,course__cname__in=["生物","物理"]).values('student__sname','student__sid',"score","course__cname") #老师的,不对
    
        # print("queryset",queryset)
        # print("queryset2",queryset2)
        # print("queryset3",queryset3)
        # print("queryset4",queryset4)
        print("queryset5",queryset5)
        print()
    
        #4、 查询每个年级的班级数,取出班级数最多的前三个年级;
        q1 =Grade.objects.all().annotate(c=Count("klass")).values("gname","c").order_by('-c')[0:3]
        print("q1",q1)
    
        #5、 查询平均成绩最高的学生的id和姓名以及平均成绩;
        q5 =Student.objects.annotate(avg_score=Avg("score__score")).values('sname','avg_score').order_by("-avg_score")[0]
        print("q5",q5)
        #6、 查询每个年级的学生人数;
        q6= Grade.objects.annotate(c=Count("klass__student")).values("gname","c")
        print(q6)
    
        #7、 查询每位学生的学号,姓名,平均成绩;
        # q7 =Student.objects.annotate(avg_score=Avg("score__socre")).values("sid","sname","avg_score") #错误的写法
        q7 =Student.objects.values("sid", "sname").annotate(avg_score=Avg("score__score"))
        print("q7",q7)
    
        #8、 查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名及分数;
        # q8 =Student.objects.filter(sid=2).first().score_set.all().order_by("-score")[0] # 自己的思路?
        q8 = Student.objects.filter(sid=2).order_by("-score__score").values("sname","score__score","score__course__cname")[0]
    
        print("q8",q8)
        #9查询每一个姓“李”的老师所带班级数
        q9=Teacher.objects.filter(tname__startswith="").annotate(c=Count("classes")).values("tname","c")
        print(q9)
        #10、查询班级数小于5的年级id和年级名;
        q10=Grade.objects.annotate(c=Count("klass")).filter(c__lt=5).values("gid","gname")
        print(q10)
    
        #11、查询教过课程超过2门的老师的id和姓名;
        q11=Teacher.objects.annotate(c=Count("course")).filter(c__gt=2).values("tname","tid","c")
        print("q11",q11)
    
        #12、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;
        # q12=Student.objects.filter( Q(score__course__cid=1)&Q(score__course__cid=2)).values("sid","sname") # 自己想的,?
        ret = Student.objects.filter(score__course__cid=1).filter(score__course__cid=2).values("pk", "sname")
        # print("q12",q12)
        print(ret)
    
        #13、查询所带班级数最多的老师id和姓名;
        q13=Teacher.objects.annotate(c=Count("classes")).order_by('-c').values("tname","tid","c")[0]
        print(q13)
    
        # 14 查询有课程成绩小于60分的同学的学号、姓名;
        q14 =Student.objects.filter(score__score__lt=60).values("sid","sname","score__score").distinct()
        print(q14)
    
        #15、查询男生、女生的人数,按倒序排列; ?
        # q15_1 = Student.objects.filter(gender=0).count()
        # q15_2=Student.objects.filter(gender=1).count()
        # print("男",q15_1)
        # print("女",q15_2)
        q15 = Student.objects.values("gender").annotate(c=Count(1)).order_by("-c") #???
        print(q15)
    
        #16、查询各个课程及相应的选修人数;
        q16 = Course.objects.annotate(c=Count("score__student__id")).values("cname","c") #可以
        ret = Score.objects.values("course").annotate(c=Count(1)).values("course__cname", "c") # ???
        print("ret",ret)
        print("q16",q16)
    
        #17、查询同时选修了物理课和生物课的学生id和姓名;
        q17=Student.objects.filter(score__course__cname="物理").filter(score__course__cname="生物").values("sid","sname")
        print("q17",q17)
        ret = Student.objects.filter(score__course__cname__in=["物理", "生物"]).values("pk", "sname").distinct()#??? in 不是同时满足吧
        print("ret",ret)
        #18、检索“3”课程分数小于60,按分数降序排列的同学学号;
        q18=Course.objects.filter(cid=3).filter(score__score__lt=60).order_by("-score__score").values("score__student_id","score__student__sname")
        ret=Score.objects.filter(course_id=3, score__lt=60).order_by("-score").values("student_id")
        print("q18",q18)
        print(ret)
    
        #19、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
        q19=Course.objects.annotate(avg_score=Avg("score__score")).order_by("avg_score").order_by("-cid").values("cname","avg_score")#错误,不能分开排序
        q19=Course.objects.annotate(avg_score=Avg("score__score")).order_by("avg_score","-cid").values("cname","avg_score")#  正确
        print("q19",q19)
        ret = Score.objects.values("course").annotate(avg_score=Avg("score")).order_by("avg_score", "-course")# 正确
        print(ret)
    
        #20、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
        q20=Course.objects.annotate(M_score=Max("score__score"),m_score=Min("score__score")).values("cid","M_score","m_score")
        print(q20)
    
    
        return HttpResponse("OK")
  • 相关阅读:
    转 linux shell自定义函数(定义、返回值、变量作用域)介绍
    转 Shell调试篇
    WIN2012的桌面和开始菜单跑到什么地方去了
    转 awr自动收集脚本
    Troubleshooting Guide for ORA-12541 TNS: No Listener
    test
    向Linus学习,让代码具有good taste
    php 页面展示
    c++ list sort
    c++ word类型
  • 原文地址:https://www.cnblogs.com/knighterrant/p/10388977.html
Copyright © 2020-2023  润新知