多对多关系通过 through 显性指定 中间表

通过 类名_set 反向查询

related_name 与 related_query_name 比较

https://www.cnblogs.com/zhufanyu/p/12361607.html

什么时候queryset 才被执行

  • 迭代
  • 切片
  • 序列化
  • list()
  • len()
  • bool()
  • repr()

filter(A,B) 与 filter(A).filter(B) 的区别

通过F ，与同一个表其他字段比较

aggregate 与 annotate区别

select_related()

  1. # 创建数据模型
  2. class Students(models.Model):
  3.     GENDER = (
  4.         ('1', '男'),
  5.         ('2', '女')
  6.     )
  7.     s_id = models.CharField(max_length=10, verbose_name="学号", primary_key=True)
  8.     name = models.CharField(max_length=100, verbose_name="姓名")
  9.     csrq = models.CharField(max_length=20, verbose_name="出生日期")
  10.     gender = models.CharField(choices=GENDER, verbose_name="性别", max_length=2)
  12.     class Meta:
  13.         db_table = 'student'
  15.     def __str__(self):
  16.         return self.name
  19. class Score(models.Model):
  20.     student = models.ForeignKey(to="Students", on_delete=models.CASCADE, db_constraint=False, to_field="s_id")
  21.     course = models.ForeignKey(to="Courses", on_delete=models.CASCADE, db_constraint=False, to_field="c_id")
  22.     score = models.IntegerField(verbose_name="成绩")
  24.     class Meta:
  25.         db_table = 'score'
  28. class Teachers(models.Model):
  29.     t_id = models.CharField(max_length=10, verbose_name="教师编号", primary_key=True)
  30.     name = models.CharField(max_length=100, verbose_name="教师名", null=True)
  32.     class Meta:
  33.         db_table = 'teacher'
  35.     def __str__(self):
  36.         return self.name
  38. class Courses(models.Model):
  39.     c_id = models.CharField(max_length=10, verbose_name="课程号", primary_key=True)
  40.     name = models.CharField(max_length=100, verbose_name="课程名称")
  41.     teacher = models.ForeignKey(to="Teachers", on_delete=models.CASCADE, db_constraint=False)
  42.     student = models.ManyToManyField(to="Students", through="Score")
  44.     class Meta:
  45.         db_table = 'course'
  47.     def __str__(self):
  48.         return self.name
  50.   # 创建表数据
  51. INSERT INTO "student" VALUES ('0001', '猴子', '1989-01-01', '1');
  52. INSERT INTO "student" VALUES ('0002', '猴子', '1990-12-21', '2');
  53. INSERT INTO "student" VALUES ('0003', '马云', '1991-12-21', '1');
  54. INSERT INTO "student" VALUES ('0004', '王思聪', '1990-05-20', '1');
  55. INSERT INTO "score" VALUES ('1', '80', '0001', '0001');
  56. INSERT INTO "score" VALUES ('2', '90', '0002', '0001');
  57. INSERT INTO "score" VALUES ('3', '99', '0003', '0001');
  58. INSERT INTO "score" VALUES ('4', '60', '0002', '0002');
  59. INSERT INTO "score" VALUES ('5', '80', '0003', '0002');
  60. INSERT INTO "score" VALUES ('6', '80', '0001', '0003');
  61. INSERT INTO "score" VALUES ('7', '80', '0002', '0003');
  62. INSERT INTO "score" VALUES ('8', '80', '0003', '0003');
  63. INSERT INTO "teacher" VALUES ('0001', '孟扎扎');
  64. INSERT INTO "teacher" VALUES ('0002', '马化腾');
  65. INSERT INTO "teacher" VALUES ('0003', NULL);
  66. INSERT INTO "teacher" VALUES ('0004', '');
  67. INSERT INTO "course" VALUES ('0001', '语文', '0002');
  68. INSERT INTO "course" VALUES ('0002', '数学', '0001');
  69. INSERT INTO "course" VALUES ('0003', '英语', '0003');
  71. # 执行案列
  72. # 查询姓“猴”的学生名单
  73. Students.objects.filter(name__contains='猴').values('s_id', 'name', 'csrq')
  74. # 查询姓“孟”老师的个数
  75. Teachers.objects.filter(name__startswith="孟").count()
  76. # 查询课程编号为“0002”的总成绩
  77. Score.objects.filter(course_id='0002').aggregate(s=Sum('score'))
  78. # 查询选了课程的学生人数
  79. Score.objects.values('student_id').distinct().count()
  80. # 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
  81. data = Score.objects.values('course_id').annotate(max_score=Max('score'), min_score=Min('score'))
  82. # 查询每门课程被选修的学生数
  83. Score.objects.values('course_id').annotate(student_cn=Count('student_id'))
  84. # 查询男生、女生人数
  85. Students.objects.values('gender').annotate(gender_cn=Count('gender'))
  86. # 查询平均成绩大于60分学生的学号和平均成绩
  87. Score.objects.values('student_id').annotate(avg_score=Avg('score')).filter(avg_score__gte=60)
  88. # 查询至少选修两门课程的学生学号
  89. Score.objects.values('student_id').annotate(course_cn=Count('course_id', distinct=True)).filter(course_cn__gt=2).values('student_id')
  90. # 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
  91. Score.objects.filter(course_id='0004', score__lt=60).values('student_id').order_by('-score')
  92. # 查询学生的总成绩并进行排名
  93. Score.objects.values('student_id').annotate(score_sum=Sum('score')).order_by('-score_sum')
  94. # 查询所有课程成绩小于60分学生的学号、姓名
  95. Score.objects.filter(score__lt=60).values('student__s_id', 'student__name')
  96. # 查询所有学生的学号、姓名、选课数、总成绩
  97. Score.objects.values('student').annotate(course_cn=Count('course', distinct=True), score_sum=Sum('score')).
  98.         values('student__s_id', 'student__name', 'course_cn', 'score_sum')
  99. # 查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩
  100. data = Score.objects.filter(course__c_id__in=Courses.objects.filter(teacher__name='孟扎扎').values('c_id'))
  101.         .values('course_id').annotate(score_max=Max('score'))