爱站程序员基地[Django框架之ORM操作：多表查询，聚合查询、分组查询、F查询、Q查询、ORM查询优化]
5个表- 书籍表：id、书名、价格、出版日期- 作者表：id、名字、年龄- 作者详情表：id、电话、地址- 出版社表：id、出版社名、地址、邮箱- 书籍和作者表（多对多关系）一对一的关系，关联字段可以写在任意一方一对多的关系，关联字段写在多的一方多对多的关系，必须建立第三张表（orm中，可以用一个字段表示，这个字段可以写在任意一方）
创建表
# models.pyfrom django.db import modelsclass Book(models.Model):id = models.AutoField(primary_key=True) # 自增，主键title = models.CharField(verbose_name=\'书名\', max_length=32)price = models.DecimalField(verbose_name=\'价格\', max_digits=8, decimal_places=2)publish_date = models.DateField(verbose_name=\'出版日期\',auto_now_add=True) # 年月日类型# 阅读数# reat_num=models.IntegerField(default=0)# 评论数# commit_num=models.IntegerField(default=0)# 建议加引号，也可以不加引号#models.CASCADE：级联删除，设为默认值，设为空，设为指定的值，不做处理# 2.x以后必须加on_dekete，否则报错# publish = models.ForeignKey(to=Publish,to_field=\'id\',on_delete=models.CASCADE)# 一对多 外键字段建在多的一方publish = models.ForeignKey(to=\'Publish\',to_field=\'id\',on_delete=models.CASCADE)# 多对多 必须建立第三张表（orm中，可以用一个字段表示，在数据库中，根本没有这个字段，# orm用来查中介模型询，映射成一个表了,如果我不这么写，则需要手动建立第三张表，）authors = models.ManyToManyField(to=\'Author\')def __str__(self):return self.titleclass Publish(models.Model):id = models.AutoField(primary_key=True)title = models.CharField(verbose_name=\'名称\',max_length=32)addr = models.CharField(verbose_name=\'地址\',max_length=128)email = models.EmailField(verbose_name=\'邮箱\') # 本质是varchar类型class Author(models.Model):id = models.AutoField(primary_key=True)name = models.CharField(verbose_name=\'姓名\',max_length=32)age = models.IntegerField(verbose_name=\'年龄\')# 一对一 外键字段推荐建在查询频率较高的表中author_detail = models.OneToOneField(to=\'AuthorDetail\',to_field=\'id\',unique=True,on_delete=models.CASCADE)class AuthorDetail(models.Model):id = models.AutoField(primary_key=True)phone = models.BigIntegerField(verbose_name=\'电话\')addr = models.CharField(verbose_name=\'地址\',max_length=32)
ORM之多表查询
跨表查询理论
1 基于对象的跨表查- 子查询，多次查询2 基于双下划线的跨表查- 多表连接查询正向：外键字段在谁那儿，谁查另外的人就是正向反向：没有外键字段# 就是判断你是否有关联的外键字段"""正向查询按外键字段反向查询按表名小写加_set查询的对象可能有多个的情况查询的对象只有一个的情况不需要加正向查询按外键字段.all()反向查询按表名小写_set.all()"""
基于对象的跨表查询操作
# 连表操作子查询"""1.先查询出一个对象2.基于对象点正反向字段"""import osif __name__ == \'__main__\':import django # 安装了django模块，就可以importdjango.setup() # 使用环境变量中的配置文件，跑djangofrom app01 import models# 正向查询：按外键字段1.查询为人三会书籍对应的出版社名称# 先拿到书籍对象book_obj = models.Book.objects.filter(title=\'为人三会\').first()# 用书籍对象拿到对应的出版社res = book_obj.publishprint(res.title)2.查询修心三不对应的作者# 先拿到书籍对象book_obj = models.Book.objects.filter(title=\'修心三不\').first()# 用书籍对象拿到对应的作者# res = book_obj.authors # app01.Author.Noneres = book_obj.authors.all() # 注意 需要加all()print(res)3.查询geng的地址# 先拿到作者对象author_obj = models.Author.objects.filter(name=\'geng\').first()# 通过作者对象拿到对应的作者详情res = author_obj.author_detailprint(res.addr)# 反向查询：表名小写加_set4.查询东方出版社出版过的书籍# 先拿到出版社对象publish_obj = models.Publish.objects.filter(title=\'东方出版社\').first()# res = publish_obj.book_set # app01.Book.None# 通过出版社对象拿到对应的书籍res = publish_obj.book_set.all() # 需要加all()print(res)5.查询yang写过的书籍# 先拿到作者对象author_obj = models.Author.objects.filter(name=\'yang\').first()# res = author_obj.book_set # app01.Book.None# 通过作者对象拿到对应的书籍res = author_obj.book_set.all() # 需要加all()print(res)6.查询电话是139的作者姓名# 先拿到作者详情对象author_detail_obj = models.AuthorDetail.objects.filter(phone=139).first()# 通过作者详情拿到对应的作者res = author_detail_obj.author # 单个查询不需要加_setprint(res.name,res.age) # 可以通过作者详情点出作者的名字，年龄
基于双下划线的跨表查询
# 基于双下划线的正向查询1.查询口才三绝书籍对应的出版社名称res = models.Book.objects.filter(title=\'口才三绝\').values(\'publish__title\')print(res.query)2.查询修心三不对应的作者名字和年龄res = models.Book.objects.filter(title=\'修心三不\').values(\'authors__name\',\'authors__age\')print(res)3.查询geng的地址res = models.Author.objects.filter(name=\'geng\').values(\'author_detail__addr\')print(res)# 基于双下划线的反向查询1.查询为人三会书籍对应的出版社名称# 拿出版社对象拿出版了为人三会的出版社res = models.Publish.objects.filter(book__title=\'为人三会\').values(\'title\')print(res)2.查询口才三绝对应的作者名字和年龄res = models.Author.objects.filter(book__title=\'口才三绝\').values(\'name\',\'age\')print(res)3.查询yang的地址res = models.AuthorDetail.objects.filter(author__name=\'yang\').values(\'addr\')print(res)# 查询主键为1的书籍的出版社所在的城市book=models.Book.objects.filter(pk=1).first()print(book.publish.city)res=models.Book.objects.filter(pk=1).values(\'publish__addr\')print(res)res=models.Publish.objects.filter(book__id=1).values(\'addr\')print(res)# 查询所有住址在北京的作者的姓名res=models.Author.objects.filter(author_detail__addr=\'北京\').values(\'name\')print(res)res=models.AuthorDatail.objects.filter(addr=\'北京\').values(\'author__name\')print(res)# 查询egon出过的所有书籍的名字res=models.Author.objects.filter(name=\'egon\').values(\'book__title\')print(res)res=models.Book.objects.filter(authors__name=\'egon\').values(\'title\')print(res)# 查询为人三会对应的作者的电话和地址res = models.Book.objects.filter(title=\'为人三会\').values(\'authors__author_detail__phone\',\'authors__author_detail__addr\')print(res)# 查询人民出版社出版过的所有书籍的名字以及作者的姓名和地址res=models.Book.objects.filter(publish__title=\'北京出版社\')\\.values(\'publish__title\',\'title\',\'authors__name\',\'authors__author_detail__addr\')res=models.Publish.objects.filter(title=\'北京出版社\').values(\'title\',\'book__title\',\'book__authors__name\',\'book__authors__author_detail__addr\')res=models.Author.objects.filter(book__publish__title=\'北京出版社\').values(\'book__publish__title\',\'book__title\',\'name\',\'author_detail__addr\')res=models.AuthorDatail.objects.filter(author__book__publish__name=\'北京出版社\').values(\'author__book__publish__title\',\'author__book__title\',\'author__name\',\'addr\')print(res)
聚合查询
聚合函数，Sum，Max，Min，Count，Avg总数，最大值，最小值，数量，平均值把聚合结果字段重命名res=models.Book.objects.all().aggregate(aaa=Sum(\'price\'))
关键字：aggregate()
aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句提供它。
# 聚合查询from django.db.models import Sum,Avg,Max,Min,Count# 计算所有图书的平均价格res=models.Book.objects.all().aggregate(Avg(\'price\'))print(res)# 计算所有图书的最高价格res=models.Book.objects.all().aggregate(Max(\'price\'))print(res)# 计算所有图书的总价格res=models.Book.objects.all().aggregate(Sum(\'price\'))print(res)# egon出版图书的总价格res = models.Book.objects.filter(authors__name=\'egon\').aggregate(Sum(\'price\'))print(res)# 北京的出版社出版的书的最高价格res = models.Book.objects.filter(publish__addr=\'北京\').aggregate(Sum(\'price\'))print(res)# 计算所有图书的总价格res=models.Book.objects.all().aggregate(book_sum=Sum(\'price\'),book_avg=Avg(\'price\'))print(res)
分组查询
关键字：annotate（）
annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数，所以使用前要先从 django.db.models 引入 Avg,Max,Min,Count,Sum（首字母大写））。
返回值：
- 分组后，用 values 取值，则返回值是 QuerySet 数据类型里面为一个个字典；
- 分组后，用 values_list 取值，则返回值是 QuerySet 数据类型里面为一个个元组。
MySQL 中的 limit 相当于 ORM 中的 QuerySet 数据类型的切片。
注意：
annotate （）里面放聚合函数。
- **values **或者 values_list 放在 annotate 前面：values 或者 values_list 是声明以什么字段分组，annotate 执行分组。
- **values 或者 values_list 放在 annotate 后面： **annotate****表示直接以当前表的pk执行分组，values 或者 values_list 表示查询哪些字段， 并且要将 annotate 里的聚合函数起别名，在 values 或者 values_list 里写其别名。
- filter放在 annotate 前面：表示where条件
- filter放在annotate后面：表示having
总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。
import osos.environ.setdefault("DJANGO_SETTINGS_MODULE", "day53.settings")if __name__ == \'__main__\':import djangodjango.setup()from app01 import models# 查询每一个出版社id，以及出书平均价格(单表)# 原生sql# select publish_id,avg(price) from book group by publish_id;# orm实现\'\'\'标准 annotate() 内写聚合函数values在前，表示group by 的字段values在后，表示取字段filter在前，表示where条件filter在后，表示having\'\'\'from django.db.models import Avg,Count,Max# 查询每一个出版社id，以及出书平均价格(单表)res = models.Book.objects.all().values(\'publish_id\').annotate(price_ave=Avg(\'price\')).values(\'publish_id\',\'price_ave\')print(res)# 查询出版社id大于1的出版社id，以及出书平均价格res = models.Book.objects.values(\'publish_id\').filter(publish_id__gt=1).annotate(price_ave=Avg(\'price\')).values(\'publish_id\',\'price_ave\')print(res)# 查询出版社id大于1的出版社id，以及出书平均价格大于30的res = models.Book.objects.values(\'publish_id\').filter(publish_id__gt=1).annotate(price_ave=Avg(\'price\')).filter(price_ave__gt=60).values(\'publish_id\',\'price_ave\')print(res)# 查询每一个出版社出版的名称和书籍个数(连表)# 联表的话最好以group by的表作为基表res = models.Publish.objects.values(\'nid\').annotate(book_count=Count(\'book__nid\')).values(\'name\',\'book_count\')# 简写成，如果基表是group by的表，就可以不写valuesres=models.Publish.objects.annotate(book_count=Count(\'book\')).values(\'name\',\'book_count\')# 以book为基表res = models.Book.objects.values(\'publish__nid\').annotate(book_count=Count(\'nid\')).values(\'publish__name\',\'book_count\')print(res)# 查询每个作者的名字，以及出版过书籍的最高价格(建议使用分组的表作为基表)# 多对多如果不以分组表作为基表，可能会出数据问题res = models.Author.objects.annotate(price_max=Max(\'book__price\')).values(\'name\',\'price_max\')res = models.Book.objects.values(\'authors__nid\').annotate(price_max=Max(\'price\')).values(\'authors__name\',\'price_max\')print(res)# 查询每一个书籍的名称，以及对应的作者个数res=models.Book.objects.annotate(count=Count(\'authors\')).values(\'name\',\'count\')print(res)# 统计不止一个作者的图书ret = models.Author.objects.values(\'book__id\').annotate(count=Count(\'id\')).filter(count__gt=1).values(\'book__name\', \'count\')print(ret)# 统计价格数大于10元，作者的图书ret = models.Book.objects.values(\'pk\').filter(price__gt=10).annotate(count=Count(\'authors__id\')).values(\'name\',\'count\')print(ret)#统计价格数大于10元，作者个数大于1的图书res = models.Book.objects.filter(price__gt=10).annotate(count=Count(\'authors\')).filter(count__gt=1).values(\'name\',\'price\',\'count\')print(res)
F查询
在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？
Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。
F查询当查询条件的左右两边数据都来自于表中则可以使用F查询F查询甚至还可以统一修改表中字段数据
针对字符串稍微复杂一点需要再导入两个模块from django.db.models import F,Valuefrom django.db.models.functions import Concat
# F 查询,取出某个字段对应的值from django.db.models import F# 查询评论数大于阅读数的书籍res=models.Book.objects.filter(commit_num__gt=F(\'read_num\'))print(res)# 把所有图书价格+1res=models.Book.objects.all().update(price=F(\'price\')+1)print(res) # 影响的行数# 把egon出版的所有图书价格加10res = models.Book.objects.filter(authors__name=\'egon\').update(price=F(\'price\')+1)print(res)# 将所有书的后面加上爆款两个字from django.db.models.functions import Concatfrom django.db.models import Value# res = models.Book.objects.update(title = Concat(F(\'title\'),Value(\'爆款\')))
Q查询
filter()等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。
Q查询1.filter默认只支持and连接查询条件如果需要修改则需要使用Q导入模块:from django.db.models import Qfilter(Q(),Q()) # andfilter(Q()|Q()) # orfilter(~Q(),~Q()) # not2.查询条件还可以由变量名模式改为字符串形式q = Q()q.connector=\’or\’q.children.append((\’字段名\’,\’条件\’))res = models.Book.objects.filter(q)
# 1、 Q查询：构造出 与 & 或 | 非 ~from django.db.models import Q# 查询名字叫红楼梦或者价格大于100的书res=models.Book.objects.filter(Q(name=\'红楼梦\')|Q(price__gt=100))res=models.Book.objects.filter(Q(name=\'红楼梦\')& Q(price__gt=100))# 查询名字不是红楼梦的书res=models.Book.objects.filter(~Q(name=\'红楼梦\'))# 查询名字不是红楼梦，并且价格大于100的书# res = models.Book.objects.filter(~Q(name=\'红楼梦\'),price__gt=\'100\')res = models.Book.objects.filter(~Q(name=\'红楼梦\')&Q(price__gt=\'100\'))print(res)2、进阶操作：# condition = input(\'请输入你需要按照什么字段查询数据>>>:\')+\'__contains\'# data = input(\'请输入你需要查询的数据名称>>>:\')# q = Q()# q.children.append((condition,data))# # print(q,type(q))# res = models.Book.objects.filter(q)# print(res)
大练习：
# 查找出版日期是2017年的书res = models.Book.objects.filter(publish_date__year=2017)print(res)# 查找出版日期是2017年的书名res = models.Book.objects.filter(publish_date__year=2017).values(\'title\')print(res)# 查找价格大于10元的书res = models.Book.objects.filter(price__gt=10)print(res)# 查找价格大于10元的书名和价格res = models.Book.objects.filter(price__gt=10).values(\'title\',\'price\')print(res)# 查找在北京的出版社res = models.Publish.objects.filter(addr=\'北京\')print(res)# 查找年龄大于30岁的作者res = models.Author.objects.filter(age__gt=30)print(res)# 查找手机号是155开头的作者res = models.Author.objects.filter(author_detail__phone=155)print(res)# 查找手机号是155开头的作者的姓名和年龄res = models.Author.objects.filter(author_detail__phone=155).values(\'name\',\'age\')print(res)# 查找书名是“红楼梦”的书的出版社book_obj = models.Book.objects.filter(title=\'红楼梦\').first()res = book_obj.publishprint(res)# 查找书名是“红楼梦”的书的出版社所在的城市res = models.Book.objects.filter(title=\'红楼梦\').values(\'publish__addr\')print(res)# 查找书名是“红楼梦”的书的出版社的名称res = models.Book.objects.filter(title=\'红楼梦\').values(\'publish__name\')print(res)# 查找书名是“红楼梦”的书的所有作者res = models.Book.objects.filter(title=\'红楼梦\').values(\'authors__name\')print(res)# 查找书名是“红楼梦”的书的作者的年龄res = models.Author.objects.filter(book__title=\'红楼梦\').values(\'age\')print(res)# 查找书名是“红楼梦”的书的作者的手机号码res = models.Book.objects.filter(title=\'红楼梦\').values(\'authors__author_detail__phone\')print(res)res = models.AuthorDetail.objects.filter(author__book__title=\'红楼梦\').values(\'phone\')print(res)# 查找书名是“红楼梦”的书的作者的地址res = models.Book.objects.filter(title=\'红楼梦\').values(\'authors__author_detail__addr\')print(res)res = models.AuthorDetail.objects.filter(author__book__title=\'红楼梦\').values(\'addr\')print(res)# 查找书名是“红楼梦”的书的出版社的邮箱res = models.Publish.objects.filter(book__title=\'红楼梦\').values(\'email\')print(res)# 基于双下划线，查询红楼梦这本书出版社的名字res = models.Book.objects.filter(title=\'红楼梦\').values(\'publish__name\')print(res)res = models.Publish.objects.filter(book__title=\'红楼梦\').values(\'name\')print(res)# 基于双下划线，查询egon的手机号res = models.AuthorDetail.objects.filter(author__name=\'egon\').values(\'phone\')print(res)res = models.Author.objects.filter(name=\'egon\').values(\'author_detail__phone\')print(res)# 基于双下划线， 查询手机号以133开头的作者出版过的书籍名称以及书籍出版社名称res = models.AuthorDetail.objects.filter(phone=133).values(\'author__book__title\',\'author__book__publish__name\')print(res)# 基于双下划线，查询城市是北京的出版社出版过的所有书籍res = models.Publish.objects.filter(addr=\'北京\').values(\'book__title\')print(res)# 查询地址内有北京的作者出版所有书籍的平均价格from django.db.models import Sum,Avg,Max,Min,Count,F,Qres = models.Book.objects.filter(authors__author_detail__addr=\'北京\').aggregate(Avg(\'price\'))print(res)# 把egon出版的所有图书价格加10res = models.Book.objects.filter(authors__name=\'egon\').update(price=F(\'price\')+1)print(res)# 查询名字叫红楼梦或价格大于50的书res = models.Book.objects.filter(Q(title=\'红楼梦\')|Q(price__gt=50))print(res)# 查询名字叫红楼梦和价格大于100 或者 id大于2res = models.Book.objects.filter(Q(title=\'红楼梦\') & Q(price__gt=100) | Q(pk__gt=2))print(res)# 查询名字不是红楼梦，并且价格大于100的书res = models.Book.objects.filter(~Q(title=\'红楼梦\'), price__gt=100)print(res)# 手机号以139开头的作者出版过的所有书籍名称以及出版社名称res = models.Author.objects.filter(author_detail__phone=139).values(\'book__title\',\'book__publish__name\')print(res)
原生sql
from app01 import modelsres=models.Author.objects.all()for author in res:print(author.sex)print(author.get_sex_display())# 使用原生sqlres=models.Author.objects.raw(\'select * from app01_author where id>1\')for author in res:print(author.name)res = models.Author.objects.raw(\'select * from app01_book where id>1\')for book in res:print(book.price)# 执行原生sql，跟对象类型无关了，查出什么字段，可以直接使用该字段
choices参数
创建用户表性别两到三种状态学历也是有限个在职状态也是有限个婚姻也是有限个..."""针对某个字段可以列举完全的情况 一般都是使用choices参数"""class Server(models.Model):host = models.CharField(max_length=32)status_choices = ((1,\'在线\'),(2,\'待上线\'),(3,\'宕机\'),(4,\'待上架\'))status = models.IntegerField(choices=status_choices)desc_choices = ((\'哈哈\',\'哈哈哈哈哈哈\'),(\'呵呵\',\'呵呵呵呵呵呵\'),(\'嘿嘿\',\'嘿嘿嘿嘿嘿嘿\'),(\'嘻嘻\',\'嘻嘻嘻嘻嘻嘻\'),)desc = models.CharField(max_length=32,choices=desc_choices)获取对应关系.get_字段名_display()
ORM查询优化
1、only与deferonly会把括号内字段对应的值，封装到查询返回的对象中，通过对象点括号字段，不需要再走数据库查询，直接返回结果，一旦你点了不是括号内的字段 就会频繁的去走数据库查询
# 惰性查询用不到的数据即使写了orm语句也不会执行1.only与deferres = models.Book.objects.values(\'title\') # 列表套字典res1 = models.Book.objects.only(\'title\') # 列表套对象# print(res1)for i in res1:# print(i.title)print(i.price)"""only括号内写什么字段生成的对象就含有对应的属性 在查找该属性的时候不再走数据库查询但是一旦查找括号内没有的字段属性 则每次都会走数据库查询"""# res1 = models.Book.objects.defer(\'title\')# # print(res1) # 列表套对象# for i in res1:# # print(i.title)# print(i.title)"""defer与only刚好相反生成的对象就不含有对应的属性 在查找该属性的时候需要每次走数据库查询但是一旦查找括号内没有的字段属性 则不需要走数据库查询总结：和 only相反，defer会将括号内的字段排除之外将其他字段对应的值， 直接封装到返回给你的对象中， 点其他字段 不需要再走数据库查询，一旦你点了括号内的字段就会有多少值，就会查询几次
2、select_related与prefech_related
"""# res = models.Book.objects.filter(pk=3).first()# print(res.publish.title)# res = models.Book.objects.select_related(\'publish\')# for i in res:# print(i.publish.title)"""select_related相当于连表操作先将models后面的表和括号内外键字段关联的表连接起来之后一次性把所有的数据封装到数据对象中总结：先将models后面的表和括号内外键字段关联的表连接起来之后一次性把所有的数据封装到数据对象中得到的结果是QuerySet列表套对象每个对象以models后面的类的具体对象为主可以用对象句点符models后面类的字段属性。如果要得到被关联表属性，需要对象句点符外键字段先拿到被关联表的对象，然后句点符被关联表的字段属性取值"""res = models.Book.objects.prefetch_related(\'publish\')for i in res:print(i.publish.title)"""prefetch_related相当于子查询先查询models后面的表的所有的数据然后将括号内关联的表的数据全部查询出来之后整合到一起"""
