Python-Django 模型层-多表查询
多表操作
基于对象的跨表查询(多次查询)
一对一：
-正向查询按字段
-反向查询按表名小写
一对多：
-正向查询按字段（正向查询一定会查出一个来）
-反向查询按表名小写_set.all()(返回结果是queryset对象)
多对多：
-正向查询按字段.all()（正向查询一定会查出多个来）
-反向查询按表名小写_set.all()(返回结果是queryset对象)
基于双下划线的跨表查询
-在filter和values中都可以做连表操作（也就是都可以写 __）
-正向查询按字段
-反向查询按表名小写
无论以谁做基表，没有效率之分
1 查询地址为上海的作者出版的书
# res=Author_det.objects.filter(address='上海').values('author__book__name')
# print(res)
# 2 两种方式
# 查找所有书名里包含红楼的书
# res=Book.objects.filter(name__icontains='eg')
# print(res)
# 查找出版日期是2017年的书
# res=Book.objects.filter(pub_date__year='2017')
# print(res)
# 查找出版日期是2017年的书名
# res = Book.objects.filter(pub_date__year='2017').values('name')
# print(res)
# books = Book.objects.filter(pub_date__year='2017')
# for i in books:
# print(i)
# 查找价格大于10元的书
# res = Book.objects.filter(price__gt=10)
# print(res)
# 查找价格大于10元的书名和价格
# res = Book.objects.filter(price__gt=10).values('name','price')
# print(res)
# books = Book.objects.filter(price__gt=10)
# for i in books:
# print(i.name,i.price)
# 查找在北京的出版社
# res = Publisher.objects.filter(addr='北京')
# print(res)
# 查找名字以沙河开头的出版社
# res = Publisher.objects.filter(name__startswith='沙河')
# print(res)
# 查找作者名字里面带“小”字的作者
# res = Author_sim.objects.filter(name__icontains='小')
# print(res)
# 查找年龄大于30岁的作者
# res = Author_sim.objects.filter(age__gt=30)
# print(res)
# 查找手机号是155开头的作者
# res = Author_sim.objects.filter(author_det__address__startswith='23')
# print(res)
# 查找手机号是155开头的作者的姓名和年龄
# res = Author_sim.objects.filter(author_det__address__startswith='23').values('name','age')
# print(res)
# authors = Author_sim.objects.filter(author_det__address__startswith='23')
# for i in authors:
# print(i.name,i.age)
# 查找书名是“红楼梦”的书的出版社
# res = Book.objects.filter(name='红楼梦').values('publisher__name')
# print(res)
# res = Book.objects.filter(name='红楼梦')
# for i in res:
# print(i.publisher)
# 查找书名是“红楼梦”的书的出版社所在的城市
# res = Book.objects.filter(name='红楼梦').values('publisher__addr')
# print(res)
# book = Book.objects.filter(name='红楼梦')
# publisher=Publisher.objects.filter(book=book)
# for i in publisher:
# print(i.addr)
# 查找书名是“红楼梦”的书的出版社的名称
# res = Book.objects.filter(name='红楼梦').values('publisher__name')
# print(res)
# book = Book.objects.filter(name='红楼梦')
# publisher=Publisher.objects.filter(book=book)
# for i in publisher:
# print(i.name)
# 查找书名是“红楼梦”的书的所有作者
# res = Book.objects.filter(name='红楼梦').values('authors__name')
# print(res)
# book = Book.objects.filter(name='红楼梦')
# authors=Author_sim.objects.filter(book=book)
# for i in authors:
# print(i.name)
# 查找书名是“红楼梦”的书的作者的年龄
# res = Book.objects.filter(name='红楼梦').values('authors__age')
# print(res)
# book = Book.objects.filter(name='红楼梦')
# authors=Author_sim.objects.filter(book=book)
# for i in authors:
# print(i.age)
# 查找书名是“红楼梦”的书的作者的手机号码
# res = Book.objects.filter(name='红楼梦').values('authors__author_det__address')
# print(res)
# book = Book.objects.filter(name='红楼梦')
# authors = Author_sim.objects.filter(book=book)
# for i in authors:
# authors_det = Author_det.objects.filter(author_id=i.id)
# for i in authors_det:
# print(i.address)
# 查找书名是“红楼梦”的书的作者的地址
# res = Book.objects.filter(name='红楼梦').values('authors__author_det__address')
# print(res)
# book = Book.objects.filter(name='红楼梦')
# author_sim = Author_sim.objects.filter(book=book)
# for i in author_sim:
# author_det = Author_det.objects.filter(id=i.id)
# for i in author_det:
# print(i.address)
# 查找书名是“红楼梦”的书的作者的邮箱
# res = Book.objects.filter(name='红楼梦').values('authors__age')
# print(res)
# book = Book.objects.filter(name='红楼梦')
# author = Author_sim.objects.filter(book=book)
# for i in author:
# print(i.age)
TTL
基于对象的跨表查询
-子查询,多条查询
-正向:关联字段在当前表中
-反向:关联不在当前表中
-正向查询:按字段
-反向查询:(具体反向查询按什么字段?默认按这种情况,不建议你修改,related_query_name,写在关联字段上)
-一对一:按表名小写
-一对多:按表名小写_set.all()
-多对多:按表名小写_set.all()
-问题:book.authors.author_detail 这个是错误的
-book.authors.author_detail.all().author_detail 这个是错误的
authors=book.authors.all().
for author in authors:
author.author_detail
-重点:基于对象的跨表查询,必须用表模型的对象查询,不能用queryset对象或者其它的对象
基于双下划线的跨表查找
-正向:按字段
-反向:按表名小写(默认情况下,可以用:related_name来修改,写在关联字段上)
-filter中可以跨表,values中也可以跨表
-练习:查找书名是“红楼梦”的书的出版社的名称
-基于双下划线
-ret=models.Book.objects.filter(name='红楼梦').values('publish__name')
-ret=models.Publish.objects.filter(book__name='红楼梦').values('name')
