1.创建表结构
# 学生 class Student(models.Model): sid = models.AutoField(primary_key=True) sname = models.CharField(max_length=32) gender = models.CharField(max_length=10) classs = models.ForeignKey("Class", on_delete=models.CASCADE) # 班级 class Class(models.Model): cid = models.AutoField(primary_key=True) caption = models.CharField(max_length=32) grade = models.ForeignKey("Class_grade",on_delete=models.CASCADE) # 老师 class Teacher(models.Model): tid = models.AutoField(primary_key=True) tname = models.CharField(max_length=32) class_t = models.ManyToManyField(to="Class") # 课程表 class Course(models.Model): cid = models.AutoField(primary_key=True) cname = models.CharField(max_length=32) teacher = models.ForeignKey("Teacher",on_delete=models.CASCADE) # 年级 class Class_grade(models.Model): gid = models.AutoField(primary_key=True) gname = models.CharField(max_length=32) # 成绩表 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()
2.小小的练习
1、自行创建测试数据; 2、查询学生总人数; 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,最高分,最低分;
3.答案(别看!!!)
# 1、自行创建测试数据; # 2、查询学生总人数; # ret = Student.objects.all().aggregate(c = Count(1)) # 3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名; # ret2=Score.objects.filter(course__cname__in=["生物","物理"],score__gte=60).values("student__pk").annotate(c=Count("course")).filter(c=2) # print(ret) # 4、查询每个年级的班级数,取出班级数最多的前三个年级; # ret = Class.objects.values("grade_id").annotate(c = Count("grade_id")).order_by("c")[:3] # print(ret) # 5、查询平均成绩最高的学生的id和姓名以及平均成绩; # ret = Score.objects.values("student__sname","student__sid").annotate(avg_s=Avg("score")).order_by("-avg_s").first() # print(ret) # 6、查询每个年级的学生人数; # ret = Class_grade.objects.annotate(s = Count("class__student")).values("s") # print(ret) # 7、查询每位学生的学号,姓名, 平均成绩; # ret = Student.objects.values("sid","sname").annotate(Avg("score__score")) # print(ret) # 8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名及分数; # ret = Student.objects.filter(sid=2).values("sname","score__course__cname").annotate(max_s=Max("score__score")).order_by("-max_s").first() # print(ret) # 9、查询姓“李”的老师的个数和所带班级数; # ret = Teacher.objects.filter(tname__istartswith="李").values("tname").aggregate(t=Count("tname"),n = Count("class_t")) # print(ret) # 10、查询班级数小于5的年级id和年级名; # ret = Class_grade.objects.annotate(c=Count("class")).filter(c__lt=5).values("gid","gname") # print(ret) # 11、查询教过课程超过2门的老师的id和姓名; # ret = Teacher.objects.annotate(t = Count("course")).filter(t__gt=2).values("tid","tname") # print(ret) # 12、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名; # ret = Student.objects.filter(score__course__cid=1).filter(score__course__cid=2).values("pk", "sname") # print(ret) # 13、查询所带班级数最多的老师id和姓名; # ret = Teacher.objects.annotate(c = Count("class_t")).order_by("-c").values("c","tid","tname").first() # print(ret) # 14、查询有课程成绩小于60分的同学的学号、姓名; # ret = Student.objects.values("sid","sname").filter(score__score__lt=60) # print(ret) # 15、查询男生、女生的人数,按倒序排列; # ret = Student.objects.values("gender").annotate(g = Count(1)).order_by("-g") # print(ret) # 16、 查询各个课程及相应的选修人数; # ret = Score.objects.values("course_id").annotate(k = Count(1)) # print(ret) # 17、 查询同时选修了物理课和生物课的学生id和姓名; # 第三题 # 18、 检索“3”课程分数小于60,按分数降序排列的同学学号; # ret = Course.objects.filter(cid=3,score__score__lt=60).order_by("score__score").values("score__student_id") # print(ret) # 19、 查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列; # ret = Course.objects.annotate(avg_s = Avg("score__score")).values("avg_s").order_by("avg_s","-cid") # print(ret) # 20、 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分; # ret = Score.objects.values("course__cid").annotate(Max("score"),Min("score")) # print(ret)