Django 数据库 ORM模型 应用练习案例

先看数据库的表：
学生表 student ； 成绩表 score； 教师表 teacher；课程表 course；
表数据结构： 数据下载地址
（可下载后自行导入自己mysql库进行操作，无法下载的话，请评论区留下邮箱，我发给你）
student

score

course

teacher

django models.py中的映射关系

from django.db import models


class Student(models.Model):
    name = models.CharField(max_length=100)
    gender = models.SmallIntegerField()
    def __str__(self):
        return self.name
    class Meta:
        db_table = 'student'


class Course(models.Model):
    name = models.CharField(max_length=100)
    teacher = models.ForeignKey("Teacher",on_delete=models.SET_NULL,null=True)
    def __str__(self):
        return self.name
    class Meta:
        db_table = 'course'


class Score(models.Model):
    student = models.ForeignKey("Student",on_delete=models.CASCADE)
    course = models.ForeignKey("Course",on_delete=models.CASCADE)
    number = models.FloatField()

    class Meta:
        db_table = 'score'


class Teacher(models.Model):
    name = models.CharField(max_length=100)

    class Meta:
        db_table = 'teacher'

操作函数解决实际场景的问题：

from django.shortcuts import render
from django.http import HttpResponse
from .models import Student,Score,Course,Teacher
from django.db.models import Avg, Count, Sum, Q
from django.db import connection

def index(request):
    #查询平均成绩大于60分的同学的id和平均成绩；
    students = Student.objects.annotate(score_avg=Avg('score__number')).filter(score_avg__gt=60).values('id','score_avg')
    for student in students:
        print(student)
    return HttpResponse('success')

def index2(request):
    # 2. 查询所有同学的id、姓名、选课的数量、总成绩；
    students =  Student.objects.annotate(counts=Count('score'),sums=Sum('score__number')).values('id','name','counts','sums')
    for student in students:
        print(student)
    return HttpResponse('index2')

def index3(request):
    #查询姓李的老师的数量
    counts = Teacher.objects.filter(name__startswith='李').count()
    print(counts)
    print(connection.queries[-1])
    return HttpResponse('index3')

def index4(request):
    #查询没选李老师课程的同学都学了哪些课
    students = Student.objects.exclude(score__course__teacher__name='李老师').values('name','score__course__name')
    for student in students:
        print(student)
    print(connection.queries[-1])
    print('分割线'+'*'*20)
    #查询哪位同学没选李老师的课程
    students2 = Student.objects.exclude(score__course__teacher__name='李老师').values('id','name').distinct()
    for student in students2:
        print(student)
    print(connection.queries[-1])
    return HttpResponse('index4')

def index6(request):
    #查询同时学过课程3,4 所有同学的id、姓名
    students = Student.objects.filter(score__course__in=[3,4]).annotate(counts=Count('score')).values('id','name','counts').filter(counts=2)
    #用values来筛选可以控制annotate的聚合字段，此处聚合字段只有id (name和id效果一样，无需重复)
    for student in students:
        print(student)
    print(connection.queries[-1])
    return HttpResponse('index6')

def index7(request):
    # 查询学过“黄老师”所教的“所有课”的同学的id、姓名；
    students = Student.objects.annotate(counts=Count('score__course',filter=Q(score__course__teacher__name='黄老师')))\
    .filter(counts=Course.objects.filter(teacher__name='黄老师').count()).values('id','name') 
    #这里的Count()里面带的filter=Q() 相当于sql中的countif
    for student in students:
        print(student)
    return HttpResponse('index7')
#index7的打印结果： 
{'id': 1, 'name': '张三'}
{'id': 2, 'name': '李四'}
{'id': 3, 'name': '王五'}
{'id': 4, 'name': '赵六'}