多表查询

多表查询

关注
发信
关注(28)粉丝(399)

Django ORM多表查询练习

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())  #

    # 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('查询成功')

　　

05-02 21:41
Powered by LMLPHP ©2024 多表查询 0.066102