八、Django的orm之多表操作（二）
一、查询（重点）
- 基于对象的跨表查询 -- 类似于子查询
正向查询和反向查询
关系属性（字段）写在哪个表里面，从当前类（表）的数据去查询它关联类（表）的数据叫做正向查询，反之叫做反向查询
-
一对一查询
-
正向查询
# 1.查询jiege的地址
author_obj = models.Author.objects.get(name='jiege')
print(author_obj.authorDetail.addr)# 或者用filter这种查，但是要加.first()变成models对象，否则报错 author_obj1 = models.Author.objects.filter(name='jiege').first() print(author_obj1.authorDetail.addr) # 正向 author_obj.authorDetail 也就是 对象.关联属性名称 -
反向查询
# 2.查询 1996-02-14 是谁的生日
authordetail_obj = models.AuthorDetail.objects.get(birthday='1996-02-14')
print(authordetail_obj.author.name)# 反向 authordetail_obj.author 也就是 对象.小写的另一个表名（类名） -
总结
Author表 一对一关联 AuthorDetail表正向查询:Authorobj.authorDetail,对象.关联属性名称 Author------------------------------------------------->AuthorDetail <------------------------------------------------- 反向查询:AuthorDetailobj.author ,对象.小写类名
-
-
一对多的查询
注：关系字段在哪个表，哪个表就是“多“，被关联的表是”一“-
正向查询
# 1.查询 回村的诱惑 这本书是哪个出版社出版的
book_obj = models.Book.objects.get(title='回村的诱惑')
print(book_obj.publish.name)# 正向 book_obj.publish 也就是 对象名.关联属性名称 -
反向查询
# 2.查询 外交出版社 都出版了哪些书
publish_obj = models.Publish.objects.get(name='外交出版社')
print(publish_obj.book_set.all()) # <QuerySet [<Book: yuhao的回忆录>, <Book: 杰哥你真猛>]>
print(publish_obj.book_set.all().filter(price__gt=500)) # <QuerySet [<Book: 杰哥你真猛>]># 反向 publish_obj.book_set 也就是 对象.表名小写_set # 因为一个出版社可以对应很多书，所以用 book_set # 因为结果返回一个queryset对象，可以继续加 .方法 -
总结
Book表 一对多关联 Publish表正向查询:book_obj.publish,对象.关联属性名称 Book -------------------------------------------------> Publish <------------------------------------------------- 反向查询:publish_obj.book_set.all(),对象.表名小写_set
-
-
多对多的查询
-
正向查询
# 1.查询 yuhao的回忆录 这本书的作者都有谁
book_obj = models.Book.objects.get(title='yuhao的回忆录')
print(book_obj.authors.all()) # <QuerySet [<Author: jiege>, <Author: yuhao>, <Author: liangdao>]># 正向 book_obj.authors.all() 就是 对象.属性 -
反向查询
# 2.查询 liangdao 都写过哪些书
author_obj = models.Author.objects.get(name='liangdao')
print(author_obj.book_set.all()) # <QuerySet [<Book: yuhao的回忆录>, <Book: 装13是如何炼成的2>]># 反向 book_obj.author_obj.book_set.all() 就是 对象.表名小写_set -
总结
Book表 多对多关联 Author表正向查询:book_obj.authors.all(),对象.关联属性名称 Book -------------------------------------------------> Author <------------------------------------------------- 反向查询:author_obj.book_set.all(),对象.表名小写_set
-
- 基于双下划线的跨表查询 -- 类似连表join
正向查询和反向查询
-
一对一
-
查询 jiege 的地址
# 方式1：正向查询
obj = models.Author.objects.filter(name='jiege').values('authorDetail__addr')
print(obj) # <QuerySet [{'authorDetail__addr': '天空之城'}]># 方式2：反向查询 obj1 = models.AuthorDetail.objects.filter(author__name='jiege').values('addr') print(obj1) # <QuerySet [{'addr': '天空之城'}]> -
哪个作者的生日是 2019 - 07 - 19
# 方式1：正向查询
obj = models.Author.objects.filter(authorDetail__birthday='2019-07-19').values('name')
print(obj) # <QuerySet [{'name': 'liangge'}]># 方式2：反向查询 obj1 = models.AuthorDetail.objects.filter(birthday='2019-07-19').values('author__name') print(obj1) # <QuerySet [{'author__name': 'liangge'}]>
-
-
一对多
-
查询一下 装13是如何炼成的 这本书的出版社是哪个
# 方式1：正向查询
obj = models.Book.objects.filter(title='装13是如何炼成的').values('publish__name')
print(obj) # <QuerySet [{'publish__name': '膨胀出版社'}]># 方式2：反向查询 obj1 = models.Publish.objects.filter(book__title='装13是如何炼成的').values('name') print(obj1) # <QuerySet [{'name': '膨胀出版社'}]> -
膨胀出版社 出版了哪些书
# 方式1：正向查询
obj = models.Book.objects.filter(publish__name='膨胀出版社').values('title')
print(obj)
# <QuerySet [{'title': '装13是如何炼成的'}, {'title': '回村的诱惑'}, {'title': '装13是如何炼成的2'}, {'title': '杰哥诱惑'}]># 方式2：反向查询 obj1 = models.Publish.objects.filter(name='膨胀出版社').values('book__title') print(obj1) # <QuerySet [{'book__title': '装13是如何炼成的'}, {'book__title': '回村的诱惑'}, {'book__title': '装13是如何炼成的2'}, {'book__title': '杰哥诱惑'}]>
-
-
多对多
-
杰哥诱惑 这本书是谁写的
# 方式1：正向查询
obj = models.Book.objects.filter(title='杰哥诱惑').values('authors__name')
print(obj) # <QuerySet [{'authors__name': 'yuhao'}]># 方式2：反向查询 obj1 = models.Author.objects.filter(book__title='杰哥诱惑').values('name') print(obj1) # <QuerySet [{'name': 'yuhao'}]> -
yuhao 都写了哪些书
# 方式1：正向查询
obj = models.Book.objects.filter(authors__name='yuhao').values('title')
print(obj)
# <QuerySet [{'title': '装13是如何炼成的'}, {'title': 'yuhao的回忆录'}, {'title': '装13是如何炼成的2'}, {'title': '杰哥诱惑'}]># 方式2：反向查询 obj1 = models.Author.objects.filter(name='yuhao').values('book__title') print(obj1) # <QuerySet [{'book__title': '装13是如何炼成的'}, {'book__title': 'yuhao的回忆录'}, {'book__title': '装13是如何炼成的2'}, {'book__title': '杰哥诱惑'}]>
-
-
进阶
-
装13出版社 出版的书的名称以及作者的名字
# 关联了三张表，Book、Author、publish方式一： obj = models.Publish.objects.filter(name='装13出版社').values('book__title','book__authors__name') print(obj) # <QuerySet [{'book__title': '回娘家的诱惑', 'book__authors__name': 'jiege'}, {'book__title': '回娘家的诱惑', 'book__authors__name': 'yuhao'}]> 方式二： obj1 = models.Book.objects.filter(publish__name='装13出版社').values('title','authors__name') print(obj1) # <QuerySet [{'title': '回娘家的诱惑', 'authors__name': 'jiege'}, {'title': '回娘家的诱惑', 'authors__name': 'yuhao'}]> 方式三： obj2 = models.Author.objects.filter(book__publish__name='装13出版社').values('book__title','name') print(obj2) # <QuerySet [{'book__title': '回娘家的诱惑', 'name': 'jiege'}, {'book__title': '回娘家的诱惑', 'name': 'yuhao'}]>原生的sql语句是这样的：
SELECT
app01_book.title,
app01_author.name
FROM
app01_publish
INNER JOIN app01_book ON app01_publish.nid = app01_book.publish_id
INNER JOIN app01_book_authors ON app01_book.nid = app01_book_authors.book_id
INNER JOIN app01_author ON app01_author.nid = app01_book_authors.author_id
WHERE
app01_publish.name = '装13出版社';
使用Navicat工具： -
手机号以4开头的作者出版过的所有书籍名称以及出版社名称
# 关联了四张表，Book、Author、publish、AuthorDetail# 方式一 obj = models.AuthorDetail.objects.filter(telephone__startswith='4').values('author__book__title','author__book__publish__name') print(obj) # 方式二 obj1 = models.Book.objects.filter(authors__authorDetail__telephone__startswith='4').values('title','publish__name') print(obj1) # 方式三 obj2 = models.Publish.objects.filter(book__authors__authorDetail__telephone__startswith='4').values('book__title','name') print(obj2) # 方式四 obj3 = models.Author.objects.filter(authorDetail__telephone__startswith='4').values('book__title','book__publish__name') print(obj3)
-
3.related_name
反向查询时，如果定义了related_name ，则用related_name替换 表名，
注意：，用在外键的创建 ForeignKey 中的一个参数，只会影响反向查询
例如：
# 在创建Book表的时候
class Book(models.Model):
......
publish=models.ForeignKey(to="Publish", to_field = "nid", on_delete = models.CASCADE,related_name='xx')
......
# 因为定义了related_name='xx'，所以
# 在正向查询时，不会影响什么
# 在反向查询时，就不会用小写的表名了，而是必须用'xx',否则会报错
比如查询 装13是如何炼成的 这本书的出版社的名字
正向查询：
obj = models.Book.objects.filter(title='装13是如何炼成的').values('publish__name')
print(obj)
反向查询：
# 没加related_name='xx'
obj1 = models.Publish.objects.filter(book__title='装13是如何炼成的').values('name')
print(obj1)
# 加入了related_name='xx'
obj1 = models.Publish.objects.filter(xx__title='装13是如何炼成的').values('name')
print(obj1)
二、聚合查询
- 聚合
计算所有图书的平均价格、最高价格
from django.db.models import Avg,Max,Min,Count
obj = models.Book.objects.all().aggregate(a=Avg('price'),m=Max('price'))
print(obj) # {'a': 411.998571, 'm': Decimal('998.00')}
注意点：
- aggregate()是QuerySet 的一个终止子句,得到的是个字典
- 字典通过键取值，得到的是一个数字，可直接用于计算
print(obj['m'] - 2) #
- 分组
annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。
ret = models.Publish.objects.annotate(a=Avg('book__price')).values('a')
print(ret)
# <QuerySet [{'a': 449.2475}, {'a': 188.0}, {'a': 449.5}]>
ret1 = models.Book.objects.values('publish_id').annotate(a=Avg('price'))
print(ret1)
# <QuerySet [{'publish_id': 1, 'a': 449.2475}, {'publish_id': 2, 'a': 188.0}, {'publish_id': 3, 'a': 449.5}]>
ret2 = models.Emp.objects.values('dep_id','name').annotate(a=Count(1))
# 这里如果你写了其他字段，那么只有这两个字段重复，才算一组，合并到一起来统计个数
注意点：
- annotate里面必须写个聚合函数，不然没有意义，
- 并且必须有个别名=，别名随便写，但是必须有，
- 用哪个字段分组，values里面就写哪个字段,
- annotate其实就是对分组结果的统计，统计你需要什么。
六、F查询、Q查询
- F查询
针对自己单表中字段的比较和处理，有三种功能
比如在Book表中新建两个字段，一个收藏数（keep_num），一个评论数（comment_num）
需要先引入：from django.db.models import F
-
自己单表中字段的比较
# 查询Book自己这张单表中 收藏数 大于 评论数 的书
ret = models.Book.objects.filter(keep_num__gt=F('comment_num'))
print(ret)
# <QuerySet [<Book: 装13是如何炼成的>, <Book: yuhao的回忆录>, <Book: 装13是如何炼成的2>]> -
F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作
# 查询Book表中 收藏数 大于 评论数的2倍 的书
ret = models.Book.objects.filter(keep_num__gt=F('comment_num')*2)
print(ret)
# <QuerySet [<Book: 装13是如何炼成的>, <Book: 装13是如何炼成的2>]> -
修改、批量的修改操作也可以使用F函数
# 给所有书的价格都减少20
ret = models.Book.objects.all().update(price=F('price')-20)
print(ret)
. -
Q查询
因为查询的时候，.filter()等方法中的关键字查询都是 and 的关系，表示不了 or 的关系 ，此时， Q查询就会派上用场
Q 对象可以使用&(与) 、|（或）、~（非） 操作符组合起来。
当一个操作符在两个 Q对象 上使用时，它产生一个新的 Q对象。
先引入：from django.db.models import Q
-
使用&、|、~
ret = models.Book.objects.filter(Q(title='装13是如何炼成的')|Q(price__lt=100))
print(ret)# 等同于： where title='装13是如何炼成的' or price<100; -
优先级问题（非>与>或）,嵌套可解决
# 此时因为and的优先级高于or，所以先找价格小于100并且评论数大于100的 或者出版日期的年份是2019的书
ret = models.Book.objects.filter(Q(publishDate__year=2019)|Q(price__lt=100)&Q(comment_num__gt=100))
print(ret)# 因为加入了Q()嵌套，所以是查询出版日期的年份是2019或者价格小于100的 并且 评论数大于100的书 ret = models.Book.objects.filter(Q(Q(publishDate__year=2019)|Q(price__lt=100))&Q(comment_num__gt=100)) -
混合使用 Q 对象和关键字参数
所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。
ret = models.Book.objects.filter(Q(publishDate__year=2019)&Q(price__lt=100),title__icontains='yuhao')
print(ret) -
综合练习
-
查询每个作者的姓名以及出版的书的最高价格
ret = models.Author.objects.annotate(m=Max('book__price')).values('name','m')
print(ret) -
查询作者id大于2作者的姓名以及出版的书的平均价格
ret = models.Author.objects.filter(nid__gt=2).annotate(a=Avg('book__price')).values('name','a')
print(ret) -
查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
ret = models.Author.objects.filter(Q(nid__gt=2)|Q(age__gte=20),sex='female').annotate(m=Max('book__price')).values('name','m')
print(ret) -
查询每个作者出版的书的最高价格 的平均值
ret = models.Author.objects.annotate(m=Max('book__price')).values('m').aggregate(b=Avg('m'))
print(ret) -
每个作者出版的所有书的价格以及最高价格的那本书的名称
ret = models.Author.objects.annotate(m=Max('book__price')).values('book__price','book__title')
print(ret)
# orm无法解决这个问题，此时查出来的数据不正确（因为书的名字分组只取到了第一个，而不是最大的那个名字）在原生sql中： # sql_mode = only_full_group_by 设置这个模式 select * from (SELECT app01_author.id,app01_author.name,max(app01_book.price) as m FROM app01_author INNER JOIN app01_book_authors on app01_author.id=app01_book_authors.author_id INNER JOIN app01_book on app01_book_authors.book_id = app01_book.nid GROUP BY app01_author.id,app01_author.name) as t1 INNER JOIN app01_book_authors on t1.id=app01_book_authors.author_id INNER JOIN app01_book on app01_book.nid=app01_book_authors.book_id where t1.m=app01_book.price; # sql_mode != onlu_full_group_by SELECT app01_author.id,app01_book.title,app01_book.price FROM app01_author INNER JOIN app01_book_authors on app01_author.id=app01_book_authors.author_id INNER JOIN app01_book on app01_book_authors.book_id = app01_book.nid ORDER BY app01_book.price desc -
orm执行原生sql语句（了解）
Django 提供两种方法使用原始SQL进行查询：一种是使用raw()方法，进行原始SQL查询并返回模型实例；另一种是完全避开模型层，直接执行自定义的SQL语句。
-
执行原生查询
注：raw()语法查询必须包含主键
# 方式一：
models.Publish.objects.raw('原生sql')
models.Publish.objects.raw('select * from app01_pubblish')# 方式二： from django.db import connection cursor = connection.cursor() cursor.excute('原生sql',[1,]) cursor.fetchall() -
展示sql的
models.Book.objects.filter(good__gt=F('comment')*2)
from django.db import connection
print(connection.queries)
事务四大特性：一致性、持久性、隔离性、原子性
提交事务：start--执行修改---submit提交，rollback回滚取消
