Django再次練習orm語句

標籤：style   end   contain   core   choices   pre   obj   ber   cas   

請配合https://www.cnblogs.com/Guishuzhe/p/9524790.html使用

models.py

from django.db import models


class ClassTable(models.Model):
    """班級"""
    id = models.AutoField(primary_key=True)
    caption = models.CharField(max_length=32)

    def __str__(self):
        return self.caption


class Student(models.Model):
    """學生"""
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, verbose_name="學生名字")
    gender_choices = ((1, "男"), (2, "女"))
    gender = models.IntegerField(choices=gender_choices, verbose_name="性別")
    classb = models.ForeignKey(to="ClassTable", verbose_name="班級")

    def __str__(self):
        return self.name


class Teacher(models.Model):
    """老師"""
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, verbose_name="老師名字")
    students = models.ManyToManyField(to="Student")
    classb = models.ManyToManyField(to="ClassTable")

    def __str__(self):
        return self.name


class Course(models.Model):
    """課程表"""
    id = models.AutoField(primary_key=True)
    cname = models.CharField(max_length=32, verbose_name="課程名稱")
    teacher = models.ForeignKey(to="Teacher", verbose_name="老師", on_delete=models.CASCADE)

    def __str__(self):
        return self.cname


class Score(models.Model):
    """成績表"""
    id = models.AutoField(primary_key=True)
    number = models.IntegerField()
    student = models.ForeignKey(to="Student", verbose_name="學生")
    course = models.ForeignKey(to="Course", verbose_name="課程")

    def __str__(self):
        return self.number

xx.py

from app01 import models
from django.db.models import Avg, Sum, Max, Min, Count, F, Q, Value
from django.db.models.functions import Concat

# 查詢體育課的成績
# val = models.Course.objects.values("score__number").filter(score__id=2)
# # print(val)
# 查詢學產生績高於80的人數
# val = models.Course.objects.values("score__student").filter(score__number__gt=80)
# print(val)
# 查詢生物課程的學產生績
# val = models.Student.objects.filter(score__course__cname="生物").values("score__number")
# val = models.Course.objects.filter(id=1).values("score__number")
# 查詢物理課程的學產生績
# val = models.Course.objects.filter(id=3).values("teacher__students").values("score__number")
# 查詢生物/物理課學生的平均成績
# val = models.Course.objects.filter(id=1).values("teacher__students").aggregate(Avg("score__number"))
# val = models.Course.objects.filter(id=3).values("teacher__students").aggregate(Avg("score__number"))
# 分別查詢所有課程平均成績和最大最小成績
# val = models.Course.objects.all().aggregate(Avg("score__number"), Max("score__number"), Min("score__number"))
# 查詢每個部門的平均成績
# val = models.Course.objects.values("cname").annotate(avg=Avg("score__number")).values("cname", "avg")
# 按課程查詢每個課程的最大分數和最小分數
# val = models.Course.objects.values("cname").annotate(max=Max("score__number"), min=Min("score__number")).values("cname", "max", "min")
# 查詢老師下面的所有學生的平均成績
# val = models.Teacher.objects.values("students").annotate(avg=Avg("students__score__number")).values("students", "avg")
# 查詢每個班級的平均分數
# val = models.ClassTable.objects.values("caption").annotate(avg=Avg("student__score__number")).values("caption", "avg")
# 查詢班級學生名字叫鋼蛋或者小王八的班級
# val = models.ClassTable.objects.filter(Q(student__name="鋼蛋")|Q(student__name="小王八"))
# 查詢班級上名字叫小五並且老師不是波多的班級
# val = models.ClassTable.objects.filter(Q(student__name="小五") & ~Q(teacher__name="波多")).values_list("caption")
# 查詢課程是生物或課程名字帶物，教師名字到蒼的老師("哈哈")
# val = models.Teacher.objects.filter(Q(course__cname="生物") | Q(course__cname__contains="物"), name__contains="蒼")
# 把所有班級後面都加上(陽光小學)
# val = models.ClassTable.objects.all().update(caption=Concat(F("caption"), Value("("), Value("陽光小學"), Value(")")))
# 吧id=2的成績提高22(支援+ - * /)
val = models.Score.objects.filter(id=2).update(number=F("number")+22)
print(val)

Django再次練習orm語句