Python实战项目：Django Models模型体会数据库的优化

Django Models模型体会数据库的优化

数据库的优化一直是面试中遇到的实际问题，如何进行数据库的优化不能只体现在概念上。向优秀一点的框架学习数据库语句上的优化也是很好的学习方法。

用Django Models来写数据库查询的语句，然后转成数据库查询语句，体会Django在数据库语句优化方面做了哪些处理。

一、Django进行sql语句优化的modes模型代码如下。

from django.db import models
class Student(models.Model):
    """学生表"""
        name = models.CharField(max_length=100)
        gender = models.SmallIntegerField()

        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)
        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'

二、查询平均成绩大于60分的同学的id和平均成绩；

这个查询语句的主语是同学，表就是student,查平均成绩可以用Avg函数统计出来，算平均数的时候带条件，这里可以用annotate进行聚合。

注意，Student表中没有成绩,但是与之关联的Score表中是有成绩的,两表建立关联后，Student表中就有小写socre,Score表中也有小写的student,访问score表中的分数字段就需再添加两个下划线。####代码如下。

models.Student.objects.annotate(c=Avg("score__number")).filter(c__gt=60).values("id","c")

将上一句代码转成sql语句的代码是。

m=models.Student.objects.annotate(c=Avg("score__number")).filter(c__gt=60).values("id","c")
m.query.__str__()

执行上面的代码，就会转成sql语句。

'SELECT `student`.`id`, `student`.`name`, AVG(`score`.`number`) AS `c` FROM `student` LEFT OUTER JOIN `score` ON (`student`.`id` =
 `score`.`student_id`) GROUP BY `student`.`id` HAVING AVG(`score`.`number`) > 60.0 ORDER BY NULL'

可以看到，带条件的聚合用到的是Having子句，annotate实现了Student表按Student.id进行分组聚合。注意，annotate只能按照Student.id进行分组聚和，这是因为一般id都是表格索引的原因。再则会把表格中所有的字段罗列出来。不是用星号（*）来实现的。

三、查询所有同学的id、姓名、选课的数量、总成绩

写django的models查询语句，首先看主语,然后对学生表进行查找，所有同学的id,name都可以直接获得，选课和总成绩就需要按学生进行分组聚和annotate，把每个学生的选课和总成绩都能选择出来，用Count计数统计和Sum求和统计的方法进行聚和。

Django models语句写法如下。

Student.objecs.annotate(s=Sum(score__number),c=Count(score__course)).values(‘id’,’name’,’c’,’s’)

上面的语句annotate括号里面可以聚和两次，将上一句代码转成sql语句的代码是。

m=Student.objecs.annotate(s=Sum(score__number),c=Count(score__course)).values(‘id’,’name’,’c’,’s’)
m.query.__str__()

执行上面的代码，就会转成sql语句。

'SELECT `student`.`id`, `student`.`name`, SUM(`score`.`number`) AS `s`, COUNT(`score`.`course_id`) AS `c` FROM `student` LEFT OUTER JOIN `score` ON (`student`.`id` = `score`.`student_id`) GROUP BY `student`.`id` ORDER BY NULL'

从转化成的sql语句上看，django对这种多表询使用了外连接来连接个表，还是按照Student表中的id进行的分组。

四、查询姓“李”的老师的个数；

写django的models查询语句，这条语句比较直接，直接对Teacher表用filer方法过滤，把Teacher表中name属性开头为“李”的老师选出即可。

Django models语句写法如下。

Teacher.objects.filter(name__startswith=”李”).count()

上面的语句starts_with表示的是某个字段的开头是否为一个特定的值，将上一句代码转成sql语句的代码是。

m=Teacher.objects.filter(name__startswith=”李”).count()
m.query.__str__()

执行上面的代码，就会转成sql语句。

'SELECT `teacher`.`id`, `teacher`.`name` FROM `teacher` WHERE `teacher`.`name` LIKE BINARY 李%' 李_

从转化成的sql语句上看，django models语句中 startswith对应的就是sql语句like这种模糊查询，单表查询数据库直接用where子句。

四、查询没学过“李老师”课的同学的id、姓名；；

写django的models查询语句，主语是同学，还是找同学的id和name。学生表中没有老师，但是学生表里有成绩，成绩表里有课程，课程表里有老师,这样的多表关系就可以找到老师，而django的models用双下划线（__）可以找到有关系的表。

Django models语句写法如下。

Student.objects.exclude(score__course__teacher__name=李老师).values(id,name)

上面的语句scorecourseteacher__name，就是一级一级的关系表中去找最后的教师表，再找教师表的name字段，将上一句代码转成sql语句的代码是。

m=Student.objects.exclude(score__course__teacher__name=李老师).values(id,name)
m.query.__str__()

执行上面的代码，就会转成sql语句。

'SELECT `student`.`id`, `student`.`name` FROM `student` WHERE NOT (`student`.`id` IN (SELECT U1.`student_id` FROM `score` U1 INNER JOIN `course` U2 ON (U1.`course_id` = U2.`id`) INNER JOIN `teacher` U3 ON (U2.`teacher_id` = U3.`id`) WHERE U3.`name` = 李老师))'

从转化成的sql语句上看，django models语句中用了内连接来连接这几个关系表，最后用where子句定位到了name="李老师"。

五、查询学过课程id为1和2的所有同学的id、姓名

这里要查询id==1与 id==2，主语是同学Student，对于Django Models来说，就可以把Student类写在前面，后面跟上objects，如果id=1和id=2两个条件都满足，用filer是不符实际的。代码下。

Student.objects.filter(score__course__id=1,score__course__id=2).values()

上面代码中filter后面条件是与的关系，如果写的同一字段，即代表当前字段即等于1又等于2,filter认为是矛盾的，不可能发生的。把这句代码换成sql语句。代码如下。

m=Student.objects.filter(score__course__id=1,score__course__id=2).values()
m.__query__()

执行上面的代码，就会转成sql语句。

SELECT student.id, student.name FROM student INNER JOIN score ON (student.id = score.student_id) WHERE (score.course_id = 1 AND score.course_id = 2)

从语句上看，要即满足id=1又要满足id=2,最终一定输出为空。

可以用Q查询来改写。语句如下。

Student.objects.filter(Q(score__course__id=1)&Q(score_course__id=2))

将上一句代码转成sql语句的代码是。

m=Student.objects.filter(Q(score__course__id=1)&Q(score_course__id=2))
m.query.__str__()

执行上面的代码，就会转成sql语句。

SELECT student.id, student.name FROM student INNER JOIN score ON (student.id = score.student_id) WHERE (score.course_id = 1 OR score.course_id = 2)

这里是或查询，不提倡。OR会带来全表查询,因此可以改写。

Student.objects.filter(score__course__id__in=[1,2]))

将上一句代码转成sql语句的代码是。

m=Student.objects.filter(score__course__id__in=[1,2]))
m.query.__str__()

执行上面的代码，就会转成sql语句。

SELECT student.id, student.name FROM student INNER JOIN score ON (student.id = score.student_id) WHERE score.course_id IN (1, 2)

上面语句中，where子句中使用in方法来实现查询，不会出现全表检索。

六、查询学过“黄老师”所教的“所有课”的同学的id、姓名

写django的models查询语句，主语是修黄老师课的同学，需要知道黄老师有几门课,这个可以用Select 子查询来完成，select查询中可以Count方法聚合完成黄老师课程的统计。

思路可以分为两步：

(1)黄老师教课数量

Curse.objects.filter(teacher__name=”黄老师”).count()

(2)学生修课做分组统计，修黄老师课

Student.objects.annotate(c=Count(“scorecourse”,filter=Q(score__course__teacher__name=”黄老师”))).filter(c_gte=Course.objects.filter(teacher_name=”黄老师”).count()).values(“id”,”name”)

注意上式中统计的时候,annotate统计函数参数中带了filter条件，同时filter条件后跟的是Q查询语句。annotate分组聚和后把就学生到底修了多少黄老师的课程统计了出来，接着filter过滤，过滤条件中实现统计黄老师有多少课程，如果两者相等，即为学生修了黄老师的所有课程。

将上一句代码转成sql语句的代码是。

m=Student.objects.annotate(c=Count(“scorecourse”,filter=Q(score__course__teacher__name=”黄老师”))).filter(c__gte=Course.objects.filter(teacher__name=”黄老师”).count()).values(“id”,”name”)
m.query.__str__()

如果有重值，可以最后加上distinct()进行去重。

m=Student.objects.annotate(c=Count(“scorecourse”,filter=Q(score__course__teacher__name=”黄老师”))).filter(c__gte=Course.objects.filter(teacher__name=”黄老师”).count()).values(“id”,”name”).distinct()
m.query.__str__()

执行上面的代码，就会转成sql语句。

SELECT student.id, student.name FROM student LEFT OUTER JOIN score ON (student.id = score.student_id) LEFT OUTER JOIN course ON (score.course_id = course.id) LEFT OUTER JOIN teacher ON (course.teacher_id = teacher.id) GROUP BY student.id HAVING COUNT(CASE WHEN (teacher.name = 黄老师) THEN score.course_id ELSE NULL END) >= 2 ORDER BY NULL

七、查询所有课程成绩小于60分的同学的id和姓名

写django的models查询语句，主语是学生表Student，输出的内容属于哪一个表，基本主语就放在哪个表；先统计学生所学的课程数，再统计学生<60的课程数， 相等，就认为所有课程的成绩小于60；分组annotate需要做两个分组：选课的数量、总成绩。可以这样写annotate代码。

annotate(Count(“score”),sum=(“score__num”))

在最终的models语句中，做了两个统计

(1)第一个统计，每一个学生学的课程总数 c=Count(“score__course”)

(2)第一个统计，每一个学生不及格的课程总数

使用了F查询，发生在等号两个字段左右时候，左边是字段，右边还是字段，需要F查询，语句中需要把统计的两个值判断是否相等，相等即为所有成绩小于60分的同学。

s=Count(“scorecourse”,filter=Q(scorenumber__lt=60)
Student.objects.annotate(c=Count(score__course),s=Count(score__course,filter=Q(score__number__lt=60))).filter(c=F(s)).values(id,name)

将上一句代码转成sql语句的代码是。

s=Count(“scorecourse”,filter=Q(scorenumber__lt=60)
Student.objects.annotate(c=Count(score__course),s=Count(score__course,filter=Q(score__number__lt=60))).filter(c=F(s)).values(id,name)
s.query.__str__()

执行上面的代码，就会转成sql语句。

SELECT student.id, student.name, COUNT(score.course_id) AS c, COUNT(CASE WHEN score.number < 60.0 THENscore.course_id ELSE NULL END) AS s FROM student LEFT OUTER JOIN score ON (student.id = score.student_id) GROUP BY student.id HAVING COUNT(score.course_id) = COUNT(CASE WHEN score.number < 60.0 THEN score.course_id ELSE NULL END) ORDER BY NULL

注意这里的sql语句中对成绩用CASE WHEN来判断是否是>60，这种处理可以学习，表之间的连接用了左外连接。

八、查询没有学全所有课的同学的id、姓名

写django的models查询语句，主语是同学Student的id，name，也就是输出同学的id,name。通过学生表Student去找Score成绩表，通过成绩Score表分组统计每个学生的Course选课个数。还需要把所有课程求出来，可以用子查询Course.objects.count()表示有多少课程，没有学全的意思是排除，排除可以用exclude方法，最后输出学生的id和name。代码如下。

Student.objects.annotate(c=Count('score__course')).exclude(c=Course.objects.count()).values('id','name')

将上一句代码转成sql语句的代码是。

s=Student.objects.annotate(c=Count('score__course')).exclude(c=Course.objects.count()).values('id','name')
s.query.__str__()

执行上面的代码，就会转成sql语句。

SELECT student.id, student.name FROM student LEFT OUTER JOIN score ON (student.id =score.student_id) GROUP BY student.id HAVING NOT (COUNT(score.course_id) = 6) ORDER BY NULL

这里的sql语句使用了左外连接，条件在Having子句中，exclude实际上就是Having子句带一个Not条件，Not条件中去统计学员课程的总数是不是等于总课数，总课数已直接转成数值6。

九、查询各科成绩的最高和最低分，以如下形式显示：课程ID，课程名称，最高分，最低分

写django的models查询语句，主语是课程，也就是输出课程id,内容，最高分，最低分，直接用annotate分组统计不同的课程的Max最高分，Min最低分即可。代码如下。

models.Course.objects.annotate(max=Max('score__number'),min=Min('score__number')).values('id','name','max','min')

将上一句代码转成sql语句的代码是。

s=Course.objects.annotate(max=Max('score__number'),min=Min('score__number')).values('id','name','max','min')
s.query.__str__()

执行上面的代码，就会转成sql语句。

SELECT course.id, course.name, MAX(score.number) AS max, MIN(score.number) AS min FROM course LEFT OUTER JOIN score ON (course.id = score.course_id) GROUP BY course.id ORDER BY NULL

十、查询每门课程的平均成绩，按照平均成绩进行排序

写django的models查询语句，主语是课程，输出是课程的平均成绩，直接用annotate分组统计不同的课程的Avg平均成绩，最后order_by按照平均成绩这个新的字段名称avg去排序。代码如下。

models.Course.objects.annotate(avg=Avg('score__number')).order_by('avg').values('id','name','avg')

将上一句代码转成sql语句的代码是。

s=Course.objects.annotate(avg=Avg('score__number')).order_by('avg').values('id','name','avg')
s.query.__str__()

执行上面的代码，就会转成sql语句。

SELECT course.id, course.name, AVG(score.number) AS avg FROM course LEFT OUTER JOIN score ON (course.id = score.course_id) GROUP BY course.id ORDER BY avg DESC

十一、 统计总共有多少女生，多少男生

这个问题如果两句话来解决，非常简单。

Student.objects.fiter(gender=1).count()
Student.objects.filter(gender=2).count()

但实际上需要用一句话，其实annotate可以同时进行若干统计项,利用这个特性可以一起统计男女。

models.Student.objects.aggregate(male=Count('name',filter=Q(gender=1)),female=Count('name',filter=Q(gender=2)))

因为这句直接aggregate全表聚和，只输出统计的数值，不能转化成sql语句。这样就得到统计出的男女个数。

可以这样理解，如果annotate判断每个人是男是女，aggregate作用所有的数据汇总

十二、将“黄老师”的每一门课程都在原来的基础之上加5分

利用django models模型把黄老师教的每一课的成绩找到，主语是成绩，所以是Score表为语句的开始，直接用条件过滤，把黄老师的课程找到，成绩表可以找到课程表，课程表可以找到教师表，利用表的关系，再定位name属性，找到黄老师，在成绩上加5分；成绩=成绩+5,左右两边又出现字段，可以用F查询。

models.Score.objects.filter(course__teacher__name='黄老师').update(number=F('number')+5)

这里实现成绩的更新，sql语句中用的是update实现，外联接各表，这在前面已经有所体会， 这里执地结果，省略sql语句。

十三、查询两门以上不及格的同学的id、姓名、以及不及格课程数

写django的models查询语句，主语是学生，输出姓名及不及格课程数，就把学生进行分组，统计学生中成绩<60的个数，再用filter过滤条件，过滤掉不及格成绩数>2的同学。也就是说，annotate统计后进行filter过滤。代码如下。

models.Student.objects.annotate(c=Count('scorecourse',filter=Q(scorenumberlt=60))).filter(c__gte=2).values('id','name','c')

这里省略sql语句的转化，可以自行转化后观察，了解django对sql语句的优化。

十四、查询每门课的选课人数；

写django的models查询语句，主语是课程，输出课程的选课人数，就把课程进行分组，统计每门课 程对应的学生数量Count，annotate完成对学生统计后输出即可。代码如下。

models.Course.objects.annotate(c=Count('score__student')).values('name','c')

这里省略sql语句的转化。

十五、annotate分组聚合和aggregate聚合的区别

1、annotate分组，以主语的id来分组，id为索引的，annotate默认就是以id来group by分组之后要有目的性，目的性求和，求平均数。理解：分组后进行了Count(),annotate只能接聚和函数。

2、Django aggregate只会出来一个值，只是汇总的值，输出一直是字典.{‘c’:5},想要数据库的字段，这个时候需要annotate，是会根据id进行分组。相当于在aggregate基础之上还可以对数据表进行操作,整表统计，或有条件的数据的汇总。