ORM多表查询
创建表结构：
from django.db import models # 创建表结构 # Create your models here. class Class_grade(models.Model): """年级表""" gid = models.AutoField(primary_key=True) gname = models.CharField(max_length=32) class Teacher(models.Model): """老师表""" tid = models.AutoField(primary_key=True) tname = models.CharField(max_length=32) class Classtb(models.Model): """班级表""" cid = models.AutoField(primary_key=True) caption = models.CharField(max_length=32) grade = models.ForeignKey(to="Class_grade", on_delete=models.CASCADE) # 多对多 teachers = models.ManyToManyField(to="Teacher") class Student(models.Model): """学生表""" sid = models.AutoField(primary_key=True) sname = models.CharField(max_length=32) gender = models.CharField(max_length=32) classtb = models.ForeignKey(to="Classtb",on_delete=models.CASCADE) class Course(models.Model): """课程表""" cname = models.CharField(max_length=32) teacher = models.ForeignKey(to="Teacher",on_delete=models.CASCADE) class Score(models.Model): """成绩表""" student = models.ForeignKey(to="Student",on_delete=models.CASCADE) course = models.ForeignKey(to="Course",on_delete=models.CASCADE) score = models.IntegerField() #整数
添加数据：
def addclass(request): Student.objects.create(sname="乔丹",gender="女",classtb_id=1) Student.objects.create(sname="艾弗森",gender="女",classtb_id=1) Student.objects.create(sname="科比",gender="男",classtb_id=2) Student.objects.create(sname="清风徐来",gender="男",classtb_id=3) Course.objects.create(cname="生物",teacher_id=1) Course.objects.create(cname="体育",teacher_id=1) Course.objects.create(cname="物理",teacher_id=2) c1=Classtb.objects.create(caption="一年一班",grade_id=1) c2=Classtb.objects.create(caption="二年一班",grade_id=2) c3=Classtb.objects.create(caption="三年二班",grade_id=3) Score.objects.create(student_id=1,course_id=1,score=60) Score.objects.create(student_id=1,course_id=2,score=59) Score.objects.create(student_id=2,course_id=2,score=99) #多对多添加表记录 c1.teachers.add(*[1, 2]) c2.teachers.add(*[1, 3]) c3.teachers.add(2) return HttpResponse("ok")
多表查询练习：
from django.shortcuts import render,redirect,HttpResponse from django.urls import reverse from app01.models import Class_grade,Teacher,Classtb,Student,Course,Score from django.db.models import Avg, Max, Sum, Min, Count,Q,F # orm表的查询练习 def check(request): """多表查询练习""" # 2、查询学生总人数；Count print(Student.objects.all().aggregate(students_c=Count(1))) #{'students_c': 5} print(Student.objects.count()) # 5 # 3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名； print(Score.objects.filter(Q(course__cname='生物')|Q(course__cname='物理'),score__gte=60).values("student_id","student__sname").annotate(c_course=Count("course__cname")).filter(c_course=2)) # < QuerySet[{'student_id': 1, 'student__sname': '乔丹', 'c_course': 2}] > # 4、查询每个年级的班级数，取出班级数最多的前三个年级； print(Classtb.objects.values("grade__pk").annotate(c=Count(1)).order_by("-c").values("grade__gname","c")[0:3]) # """<QuerySet [{'grade__gname': '二年级', 'c': 3}, {'grade__gname': '三年级', 'c': 2}, {'grade__gname': '一年级', 'c': 2}]>""" # 5、查询平均成绩最高的学生的id和姓名以及平均成绩； print(Score.objects.values('student__sid',"student__sname").annotate(a=Avg("score")).order_by("a").last()) # {'student_id': 2, 'student__sname': '艾弗森', 'a': 82.0} # 6、查询每个年级的学生人数； print(Classtb.objects.values("grade__gname").annotate(c_students=Count("student__sid"))) # < QuerySet[{'grade__gname': '一年级', 'c_students': 3}, {'grade__gname': '二年级', 'c_students': 1}, {'grade__gname': '三年级','c_students': 1}] > # 7、查询每位学生的学号，姓名, 平均成绩； print(Score.objects.values("student_id","student__sname").annotate(avg=Avg("score"))) # 8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名及分数； print(Score.objects.filter(student_id=2).values("student__sname","course__cname","score").order_by("score").last()) # {'student__sname': '艾弗森', 'course__cname': '体育', 'score': 99} # 9、查询姓“李”的老师的个数和所带班级数； tname_li = Teacher.objects.filter(tname__startswith='李') print(tname_li.count()) # 姓李的老师个数1 print(tname_li.values('tname', 'classtb__pk').count()) # 其所带班级数3 # 10、查询班级数小于3的年级id和年级名； print(Class_grade.objects.values("gname").annotate(class_c=Count("classtb__pk")).filter(class_c__lt=3).values("gid",'gname'))
# 11、查询教过课程超过2门的老师的id和姓名； print(Course.objects.values("teacher__tname").annotate(course_c=Count("cname")).filter(course_c__gt=2).values('teacher__tid','teacher__tname')) # <QuerySet []> # 12、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名； print(Score.objects.filter(Q(course__id=1)|Q(course__id=2)).values("student__sid").annotate(course_c=Count("course")).values("student__sid","student__sname").filter(course_c=2)) # < QuerySet[{'student__sid': 5, 'student__sname': '清风徐来'}] > # 13、查询所带班级数最多的老师id和姓名； print(Classtb.objects.values("teachers__tid","teachers__tname").annotate(class_c=Count("cid")).order_by("class_c").last()) ## < QuerySet['teachers__tid': 2, 'teachers__tname': '李四','class_c': 3}]> ## { # 14、查询有课程成绩小于60分的同学的学号、姓名； print(Score.objects.filter(score__lt=60).values("student__sid","student__sname").distinct()) #从返回结果中剔除重复记录distinct() # < QuerySet[{'student__sid': 4, 'student__sname': '大仙'}, {'student__sid': 5, 'student__sname': '清风徐来'}] > # 15、查询男生、女生的人数，按倒序排列； print(Student.objects.values("gender").annotate(students_c=Count(1)).order_by("-students_c")) # <QuerySet [{'gender': '女', 'students_c': 3}, {'gender': '男', 'students_c': 2}]> # 16、 查询各个课程及相应的选修人数； print(Score.objects.values("course__cname").annotate(students_c=Count("student__pk"))) # 17、 查询同时选修了物理课和生物课的学生id和姓名； print(Score.objects.filter(Q(course__cname="物理")|Q(course__cname="生物")).values("student__sid","student__sname").annotate(course_c=Count("course_id")).filter(course_c=2)) # 18、 检索“3”课程分数小于60，按分数降序排列的同学学号； print(Score.objects.filter(course_id=3,score__lt=60).order_by("-score").values("student__sid")) # 19、 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 print(Score.objects.values("course_id").annotate(avg=Avg("score")).order_by("avg","-course_id")) # 20、 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； print(Score.objects.values("course_id").annotate(max_s=Max("score"),min_s=Min("score"))) return HttpResponse("查询成功")
DjangoORM执行原生SQL
# Django执行原生SQL from django.db import connection def seleck(request): cursor = connection.cursor() cursor.execute('select * from app01_teacher') raw = cursor.fetchone() # 返回结果行游标直读向前，读取一条 raw2 = cursor.fetchall() # 从游标位置起，读取所有 print(raw) # (1, '张三') print(raw2) # ((2, '李四'), (3, '王二'), (4, '刘六'), (5, '麻子')) return HttpResponse('查询成功')