多表操作
一、创建模型
- 实例：我们来假定下面这些概念，字段和关系
- 作者模型：一个作者有姓名和年龄。
- 作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息。作者详情模型和作者模型之间是一对一的关系（one-to-one）
- 出版商模型：出版商有名称，所在城市以及email。
- 书籍模型：
- 书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系(many-to-many);
- 一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系(one-to-many)。
模型建立如下：
from django.db import models # Create your models here. # 作者表
class Author(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=)
age = models.IntegerField() # 声明一对一关联字段
authordetail = models.OneToOneField(to="AuthorDetail", to_field="nid", on_delete=models.CASCADE) # 作者详情表
class AuthorDetail(models.Model):
nid = models.AutoField(primary_key=True)
birthday = models.DateField()
telephone = models.BigIntegerField()
addr = models.CharField(max_length=) # 出版社表
class Publish(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=)
city = models.CharField(max_length=)
email = models.EmailField() # 书籍表
class Book(models.Model):
nid = models.AutoField(primary_key=True)
title = models.CharField(max_length=)
publishDate = models.DateField()
price = models.DecimalField(max_digits=, decimal_places=) # 一对多关系
publish = models.ForeignKey(to="Publish", to_field="nid", on_delete=models.CASCADE)
'''
publish_id INT
FOREIGN KEY (publish_id) REFERENCES publish(id)
''' # 多对多关系
authors = models.ManyToManyField(to="Author")
'''
CREATE TABLE book_authors(
id INT PRIMARY KEY auto_increment,
book_id INT,
author_id INT,
FOREIGN KEY (book_id) REFERENCES book(id),
FOREIGN KEY (author_id) REFERENCES author(id)
)
''' # 多对多表也可以直接用class创建
# class Book2Author(models.Model):
# nid = models.AutoField(primary_key=True)
# book = models.ForeignKey(to="Book", on_delete=models.CASCADE)
# author = models.ForeignKey(to="Author", on_delete=models.CASCADE)
生成表如下：
注意事项：
- 表的名称myapp_modelName，是根据 模型中的元数据自动生成的，也可以覆写为别的名称
- id 字段是自动添加的
- 对于外键字段，Django 会在字段名上添加"_id" 来创建数据库中的列名
- 这个例子中的CREATE TABLE SQL 语句使用PostgreSQL 语法格式，要注意的是Django 会根据settings 中指定的数据库类型来使用相应的SQL 语句。
- 定义好模型之后，你需要告诉Django _使用_这些模型。你要做的就是修改配置文件中的INSTALL_APPSZ中设置，在其中添加models.py所在应用的名称。
- 外键字段 ForeignKey 有一个 null=True 的设置(它允许外键接受空值 NULL)，你可以赋给它空值 None
二、添加表字段
# 插入一条出版社记录
# pub = Publish.objects.create(name="人民出版社", email="123@qq.com", city="北京")
'''
######################绑定一对多的关系##############################################
# 方式一
# 为book表绑定出版社： book（多） ---- publish（一）
book_obj = Book.objects.create(title="红楼梦", price=, publishDate="2012-12-12", publish_id=)
print(book_obj.title) # 方式二
pub_obj = Publish.objects.filter(name="人民出版社").first()
book_obj = Book.objects.create(title="西游记", price=, publishDate="2012-12-12", publish=pub_obj)
print(book_obj.publish) # 与这本书关联的出版社对象
print(book_obj.publish.name)
print(book_obj.publish.email) # 查询西游记的出版社对应的邮箱
book_obj = Book.objects.filter(title="西游记").first()
print(book_obj.publish.email)
''' '''
######################绑定多对多的关系##############################################
# 添加一个书对象（Book表的一条记录）
# book_obj = Book.objects.create(title="金瓶mei", price=, publishDate="2012-12-12", publish_id=) # 获取作者对象
egon = Author.objects.get(name="egon")
alex = Author.objects.get(name="alex") # 绑定多对多关系的API
# book_obj.authors.add(egon, alex) #直接传入对象
# book_obj.authors.add(, ) # 传入作者id
# book_obj.authors.add(*[, ]) # 传入作者列表 # 解除多对多关系的API
# book_obj = Book.objects.filter(nid=).first()
# book_obj.authors.remove(alex)
# book_obj.authors.remove()
# book_obj.authors.remove(*[, ]) # book.authors.clear() # 查询主键为4的书籍所有的作者名字
book_obj = Book.objects.filter(nid=).first()
book_obj.authors.add(alex, egon)
print(book_obj.authors.all())
ret = book_obj.authors.all().values("name")
print(ret) # 关键点
一 book_obj.publish <====> Publish.objects.filter(id=book_obj.publish_id).first() 二 book_obj.authors.all()
关键点：book.authors.all() # 与这本书关联的作者合集 book.id=
book_authors
id book_id author_ID author
id name
alex
egon book_obj.authors.all() -------> [alex,egon]
'''
三、基于对象的跨表查询（子查询）
"""
跨表查询：
.基于对象查询 -- 子查询
.基于双下划线查询 -- 链表查询 join
.聚合和分组查询
.F 与 Q查询
:param request:
:return:
"""
'''
A-B
关联属性在A表中 正向查询：A----->B
反向查询：B----->A # 一对多查询
正向查询：按字段
反向查询：表名小写_set.all() book_obj.publish
Book(关联属性:publish)对象 ----------------》 publish对象
《----------------
publish_obj.book_set.all() - querysetd对象 # 多对多查询
正向查询：按字段+ .all()
反向查询：表名小写_set.all() book_obj.authors.all()
Book(关联属性:authors)对象 ----------------》 publish对象
《----------------
author_obj.book_set.all() - queryset 对象 # 一对一查询
正向查询：按字段
反向查询：表名小写 book_obj.authors
Author(关联属性:authordetail)对象 ----------------》 AuthorDetail对象
《----------------
author_obj.book - book 对象 '''
# -------------------------基于对象的跨表查询(子查询)----------------------- # 一对多查询的正向查询：查询金瓶mei这本书的出版社名字
# book_obj = Book.objects.filter(title="金瓶mei").first()
# book_obj.publish # 与这本书关联的出版社对象
# print(book_obj.publish.name)
# 对应的sql：
# select name from publish where id = (select publish_id where title = "金瓶mei") # 一对多查询的反向查询：查询人民出版社出版过的书籍名称
# publish_obj = Publish.objects.filter(name="人民出版社").first()
# print(publish_obj.book_set.all()) # 多对多查询的正向查询：查询金瓶mei这本书的所有作者名字
# book_obj = Book.objects.filter(title="金瓶mei").first()
# author_list = book_obj.authors.all()
# for author in author_list:
# print(author.name) # 多对多查询的反向查询：查询alex出版过的所有书籍名称
# author_obj = Author.objects.filter(name="alex").first()
# book_list = author_obj.book_set.all()
# for book in book_list:
# print(book.title) # 一对一查询的正向查询：查询alex的手机号
# author_obj = Author.objects.filter(name="alex").first()
# print(author_obj.authordetail.telephone) # 一对一查询的反向查询：查询手机号为110的作者的名字和年龄
# ad = AuthorDetail.objects.filter(telephone=).first()
# print(ad.author.name)
# print(ad.author.age)
四、基于双下划线的跨表查询（join）
Django还提供了一种直观而高效的方式在查询（lookups）中表示关联关系，它能自动确认SQL JOIN联系。
要做跨关系查询，就使用两个下划线来链接模型（model）间关联字段的名称，知道最终链接到你想要的model为止。
关键点：正向查询按字段，反向查询按表名小写。
# -------------------------基于双下划线的跨表查询(JOIN表)-----------------------
'''
正向查询按字段，反向查询按表名小写用来告诉ORM引擎join那张表
'''
# 一对多查询：查询金瓶mei这本书出版社的名字
# 方式1 以book为基表：
# ret=Book.objects.filter(title="金瓶mei").values("publish__name")
# print(ret) # <QuerySet [{'publish__name': '南京出版社'}]>
# 方式2 以publish为基表：
# ret = Publish.objects.filter(book__title="金瓶mei").values("name")
# print(ret)
# 多对多查询：查询金瓶mei这本书的所有作者的名字
# 方式1：
# 需求：通过Book表join与其关联的Author表，属于正向查询：按字段authors通知ORM引擎join book_authors与author表
# ret = Book.objects.filter(title="金瓶mei").values("authors__name")
# print(ret)
# 方式2：
# 需求：通过Author表join与其关联的Book表，属于反向查询：按表名小写book通知ORM引擎jion book_authors与book表
# ret = Author.objects.filter(book__title="金瓶mei").values("name")
# print(ret)
# 一对一查询：查询alex的手机号
# 方式一：
# 需求：通过Author表join与其关联的AuthorDetail表，属于正向查询：按字段名authordetail通知ORM引擎join authordetail表
# ret = Author.objects.filter(name="alex").values("authordetail__telephone")
# print(ret)
# 方式二：
# 需求：通过AuthorDetail表join与其关联的Author表，属于反向查询
# ret = AuthorDetail.objects.filter(author__name="alex").values("telephone")
# print(ret)
五、聚合查询与分组查询
聚合 aggregate（*args， **kwargs）
分组 annotate()
# -------------------------聚合与分组查询---------------------------
# ------------------------->聚合 aggregate:返回值是一个字典,不再是queryset
# 查询所有书籍的平均价格
# ret = Book.objects.all().aggregate(avg_price=Avg("price"), max_price=Max("price"))
# print(ret)
# ------------------------->分组查询 annotate ,返回值依然是queryset
# ------------------------->单表分组查询:
# 示例一
# 查询每一个部门的名称以及员工的平均薪水
# select dep, avg(salary) from emp group by dep
# ret = Emp.objects.values("dep").annotate(avg_salary=AVG(salary))
# print(ret) # <QuerySet [{'avg_salary': 5000.0, 'dep': '保安部'}, {'avg_salary': 51000.0, 'dep': '教学部'}]>
# 单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数（"统计字段"）)
# 示例二
# 查询每一个省份的名称以及员工数
# ret = Emp.objects.values("province").annotate(c = Count(id))
# print(ret) # <QuerySet [{'province': '山东省', 'c': }, {'province': '河北省', 'c': }]>
'''
# 补充知识点：
ret = Emp.objects.all()
print(ret) # select * from emp
ret = Emp.objects.values("name")
print(ret) # select name from emp
Emp.objects.all().annotate(avg_salary=Avg("salary"))
select AVG(salary) from emp ;
'''
# ------------------------->多表分组查询:
# 示例1 查询每一个出版社的名称以及出版的书籍个数
# ret = Publish.objects.values('pk').annotate(c=Count("book__title"))
# print(ret) <QuerySet [{'nid': , 'c': }, {'nid': , 'c': }]>
# ret = Publish.objects.values("pk").annotate(c=Count("book__title")).values("name", "c")
# print(ret) <QuerySet [{'name': '人民出版社', 'c': }, {'name': '南京出版社', 'c': }]>
# 示例2 查询每一个作者的名字以及出版过的书籍的最高价格
# ret = Author.objects.values("pk").annotate(m_price=Max("book__price")).values("name", "m_price")
# print(ret) <QuerySet [{'name': 'alex', 'm_price': Decimal('120.00')}, {'name': 'egon', 'm_price': Decimal('120.00')}]>
# 总结 跨表的查询模型：
# 每一个后表的模型.objects.values("pk").annotate(聚合函数（关联表__统计字段）)
# 示例3 查询每一个书籍的名称以及对应的作者个数
# ret = Book.objects.values("pk").annotate(c=Count("authors__name")).values("title", "c")
# print(ret) <QuerySet [{'title': '金瓶mei', 'c': }, {'title': '鹿鼎记', 'c': }, {'title': '红楼梦', 'c': }, {'title': '西游记', 'c': }]>
#################### 跨表分组查询的另一种玩法 ####################
# 示例1 查询每一个出版社的名称以及出版的书籍个数
# ret=Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","email","c")
# ret=Publish.objects.all().annotate(c=Count("book__title")).values("name","c","city")
# ret=Publish.objects.annotate(c=Count("book__title")).values("name","c","city")
# print(ret)
六、F查询与Q查询
F查询
作用：
- 比较model中的两个不同字段的值
- F()对象之间或者F()对象与常数的加减乘除操作
Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。
# 查询评论数大于收藏数的书籍 from django.db.models import F
Book.objects.filter(commnetNum__lt=F('keepNum'))
Django支持F()对象之间以及F()对象和常数之间的加减乘除和取模的操作。
# 查询评论数大于收藏数2倍的书籍
Book.objects.filter(commnetNum__lt=F('keepNum')*)
Q查询
作用：
- 逻辑查询 | , &,~
filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。
from django.db.models import Q
Q(title__startswith='Py')
Q 对象可以使用& 和| 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象。
bookList=Book.objects.filter(Q(authors__name="yuan")|Q(authors__name="egon"))
等同于下面的SQL WHERE 子句：
WHERE name ="yuan" OR name ="egon"
你可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询：
bookList=Book.objects.filter(Q(authors__name="yuan") & ~Q(publishDate__year=)).values_list("title")
查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。例如：
bookList=Book.objects.filter(Q(publishDate__year=) | Q(publishDate__year=),
title__icontains="python"
)