08.Django框架之ORM操作：多表查询，聚合查询、分组查询、F查询、Q查询
1 后台管理，方便我们快速的录入书籍
2 使用方法：
第一步：在admin.py 中把要使用的表注册
from app01 import models
admin.site.register(models.Book)
admin.site.register(models.Author)
admin.site.register(models.AuthorDatail)
admin.site.register(models.Publish)
第二步：创建个超级管理员
python3 manage.py createsuperuser
输入用户名，输入密码
第三步：登录，录入书籍
http://127.0.0.1:8000/admin/
5个表 - 书籍表：id、书名、价格、出版日期 - 作者表：id、名字、年龄 - 作者详情表：id、电话、地址 - 出版社表：id、出版社名、地址、邮箱 - 书籍和作者表（多对多关系） 一对一的关系，关联字段可以写在任意一方 一对多的关系，关联字段写在多的一方 多对多的关系，必须建立第三张表（orm中，可以用一个字段表示，这个字段可以写在任意一方）
创建表
# models.py
from django.db import models
class 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.title
class 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)
1 基于对象的跨表查
- 子查询，多次查询
2 基于双下划线的跨表查
- 多表连接查询
正向：外键字段在谁那儿，谁查另外的人就是正向
反向：没有外键字段
# 就是判断你是否有关联的外键字段
"""
正向查询按外键字段
反向查询按表名小写加_set
查询的对象可能有多个的情况
查询的对象只有一个的情况不需要加
"""
# 连表操作子查询
"""
1.先查询出一个对象
2.基于对象点正反向字段
"""
import os
if __name__ == '__main__':
import django # 安装了django模块，就可以import
django.setup() # 使用环境变量中的配置文件，跑django
from app01 import models
# 正向查询：按外键字段
1.查询为人三会书籍对应的出版社名称
# 先拿到书籍对象
book_obj = models.Book.objects.filter(title='为人三会').first()
# 用书籍对象拿到对应的出版社
res = book_obj.publish
print(res.title)
2.查询修心三不对应的作者
# 先拿到书籍对象
book_obj = models.Book.objects.filter(title='修心三不').first()
# 用书籍对象拿到对应的作者
# res = book_obj.authors # app01.Author.None
res = book_obj.authors.all() # 注意 需要加all()
print(res)
3.查询geng的地址
# 先拿到作者对象
author_obj = models.Author.objects.filter(name='geng').first()
# 通过作者对象拿到对应的作者详情
res = author_obj.author_detail
print(res.addr)
# 反向查询：表名小写加_set
4.查询东方出版社出版过的书籍
# 先拿到出版社对象
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 # 单个查询不需要加_set
print(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()是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)
注意:
增删改查
1，增
-
一对多添加
-
# 一对多的添加 # 方式一:如果是这样直接指定publish_id字段去添加值，前提是你的主表里面必须有数据 # 主表：没有被关联的（因为book表是要依赖于publish这个表的）也就是publish表 # 子表：关联的表 models.Book.objects.create(title="追风筝的人",publishDdata="2015-5-8",price="111",publish_id=1) # 方式二:推荐 pub_obj = models.Publish.objects.filter(name="人民出版社")[0] print(pub_obj) models.Book.objects.create(title = "简爱",publishDdata="2000-6-6",price="222",publish=pub_obj) # 方式三：save pubObj= models.Publish.objects.get(name="人民出版社") #只有一个的时候用get,拿到的直接就是一个对象 bookObj = models.Book(title = "真正的勇士",publishDdata="2015-9-9",price="50",publish=pubObj) bookObj.save()
-
-
多对多添加的两种方式
-
# 多对多的添加的两种方式 # 方式一： # 先创建一本书： pub_obj=models.Publish.objects.filter(name="江南出版社").first() book_obj = models.Book.objects.create(title="醉玲珑",publishDdata="2015-4-10",price="222",publish=pub_obj) # #通过作者的名字django默认找到id haiyan_obj = models.Author.objects.filter(name="haiyan")[0] egon_obj = models.Author.objects.filter(name="egon")[0] xiaoxiao_obj = models.Author.objects.filter(name="xiaoxiao")[0] # 绑定多对多的关系、 book_obj.authorlist.add(haiyan_obj, egon_obj, xiaoxiao_obj) # 方式二=========,查出所有的作者 pub_obj = models.Publish.objects.filter(name="江南出版社").first() book_obj = models.Book.objects.create(title="醉玲珑", publishDdata="2015-4-10", price="222", publish=pub_obj) authers = models.Author.objects.all() # #绑定多对多关系 book_obj.authorlist.add(*authers)
-
2，删
-
remove：将某个特定的对象从被关联对象集合中去除，即解除关联关系
-
# 解除多对多的关系（remove） book_obj=models.Book.objects.filter(title="醉玲珑").last() #找到书对象 authers=models.Author.objects.filter(id__lt=3) #找到符合条件的作者对象 book_obj.authorlist.remove(*authers) #因为清除的是多条，得加个*
-
-
清除绑定：clear
-
清空被关联对象集合
-
# 清除关系方法(clear) book_obj= models.Book.objects.filter(title="红楼梦") for book_obj_item in book_obj:#把所有红楼梦的都给清空了 book_obj_item.authorlist.clear()
-
-
两者区别：
- remove：先将要清除的数据筛选出来，然后移除
- clear：不用查，直接就把数据都清空了。
- 各有应用场景
3，改
- 改和查一样
4，查
-
一对一查询记录
- 正向查询：按字段属性
- 反向查询：按表名，一对一用不到_set
-
一对多查询记录
- 正向查询：按字段属性
- 反向查询：按表名_set
-
多对多查询记录
- 正向查询：按字段属性
- 反向查询：按表名__set
-
related_name：别名
-
你可以通过在 ForeignKey() 和ManyToManyField的定义中设置 related_name 的值来覆写 FOO_set 的名称。例如，如果 Article model 中做一下更改： publish = ForeignKey(Blog, related_name='bookList')，那么接下来就会如我们看到这般：
# 查询 人民出版社出版过的所有书籍 publish=Publish.objects.get(name="人民出版社") book_list=publish.bookList.all() # 与人民出版社关联的所有书籍对象集合
-
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 os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day53.settings")
if __name__ == '__main__':
import django
django.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的表，就可以不写values
res=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 查询,取出某个字段对应的值
from django.db.models import F
# 查询评论数大于阅读数的书籍
res=models.Book.objects.filter(commit_num__gt=F('read_num'))
print(res)
# 把所有图书价格+1
res=models.Book.objects.all().update(price=F('price')+1)
print(res) # 影响的行数
# 把egon出版的所有图书价格加10
res = models.Book.objects.filter(authors__name='egon').update(price=F('price')+1)
print(res)
Q查询
filter()等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。
# 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)
# 查找出版日期是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.publish print(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,Q res = models.Book.objects.filter(authors__author_detail__addr='北京').aggregate(Avg('price')) print(res) # 把egon出版的所有图书价格加10 res = 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大于2 res = 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 models
res=models.Author.objects.all()
for author in res:
print(author.sex)
print(author.get_sex_display())
# 使用原生sql
res=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，跟对象类型无关了，查出什么字段，可以直接使用该字段
