模型层-多表操作

1 创建模型

实例：我们来假定下面这些概念，字段和关系

作者模型：一个作者有姓名和年龄。

作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息。作者详情模型和作者模型之间是一对一的关系（一对一）

出版商模型：出版商有名称，所在城市以及电子邮件。

书籍模型： 书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系（many-to-many）;一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系（one-to-many）。

Book

   id    title   price  publish    

   1      php     100   人民出版社  
   2      python  200   老男孩出版社   
   3      go      100   人民出版社  
   4      java    300   人民出版社  


为了存储出版社的邮箱，地址，在第一个表后面加字段

Book

   id    title   price  publish    email    addr    
   1      php     100   人民出版社   111      北京
   2      python  200   老男孩出版社 222      上海
   3      go      100   人民出版社   111      北京
   4      java    300   人民出版社   111      北京
   
这样会有大量重复的数据，浪费空间

####################################################################################

一对多：一个出版社对应多本书（关联信息建在多的一方，也就是book表中）

Book

   id    title   price     publish_id   
   1      php     100         1
   2      python  200         1
   3      go      100         2  
   4      java    300         1


Publish

    id    name       email    addr    
     1    人民出版社   111      北京       
     2    沙河出版社   222      沙河


总结：一旦确定表关系是一对多：在多对应的表中创建关联字段（在多的表里创建关联字段）  ，publish_id


查询python这本书的出版社的邮箱(子查询)

select publish_id from Book where title=“python”
select email from Publish where id=1


####################################################################################


多对多：一本书有多个作者，一个作者出多本书

Book

   id    title   price     publish_id    
   1      php     100         1               
   2      python  200         1
   3      go      100         2  
   4      java    300         1



Author
    id  name  age   addr
    1    alex  34   beijing
    2    egon  55   nanjing



Book2Author

    id    book_id  author_id
     1       2         1
     2       2         2
     3       3         2

总结：一旦确定表关系是多对多：创建第三张关系表（创建中间表，中间表就三个字段，自己的id，书籍id和作者id） ：
          id    book_id   author_id


# lqz出版过的书籍名称(子查询)

select id from Author where name='lqz'

select book_id from Book2Author where author_id=1

select title from Book where id=book_id

####################################################################################


一对一：对作者详细信息的扩展（作者表和作者详情表）

Author
         id  name  age     ad_id(UNIQUE) 
         1    lqz  34        1     
         2    egon  55       2     


AuthorDetail

   id    addr      gender    tel   gf_name   author_id(UNIQUE)
    1   beijing    male      110   小花           1
    2   nanjing    male      911   杠娘           2


总结： 一旦确定是一对一的关系：在两张表中的任意一张表中建立关联字段＋Unique（推荐建在查询频率高的表中）


====================================


Publish  
Book
Author
AuthorDetail
Book2Author


CREATE TABLE publish(
                id INT PRIMARY KEY auto_increment ,
                name VARCHAR (20)
              );


CREATE TABLE book(
                id INT PRIMARY KEY auto_increment ,
                title VARCHAR (20),
                price DECIMAL (8,2),
                pub_date DATE ,
                publish_id INT ,
                FOREIGN KEY (publish_id) references publish(id)
              );


CREATE TABLE authordetail(
                id INT PRIMARY KEY auto_increment ,
                tel VARCHAR (20)
              );

CREATE TABLE author(
                id INT PRIMARY KEY auto_increment ,
                name VARCHAR (20),
                age INT,
                authordetail_id INT UNIQUE ,
                FOREIGN KEY (authordetail_id) references authordetail(id)
              );


CREATE TABLE book2author(
       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)
)

注意：关联字段与外键约束没有必然的联系（建关联字段是为了进行查询，建约束是为了不出现脏数据）

在Models创建如下模型：

class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    publish_date = models.DateField()
    # 阅读数
    # reat_num=models.IntegerField(default=0)
    # 评论数
    # commit_num=models.IntegerField(default=0)

    publish = models.ForeignKey(to='Publish',to_field='nid',on_delete=models.CASCADE)
    #to 设置要关联的表
    #to_field 设置要关联的表的字段，默认不写，关联的就是另外一张表的主键字段
    #on_delete 当删除关联表中的数据时，当前表与其关联的行的行为
    #db_constraint 是否在数据库中创建外键约束，默认为True;生产环境一般设置为False，不强制关联
    authors = models.ManyToManyField(to='Author')
    #通过虚拟字段authors，告诉ORM创建第三方关系表，建在查询频率高的一方，不用设置级联删除
    def __str__(self):
        return self.name


class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    author_detail = models.OneToOneField(to='AuthorDatail',to_field='nid',unique=True,on_delete=models.CASCADE)


class AuthorDatail(models.Model):
    nid = models.AutoField(primary_key=True)
    telephone = models.BigIntegerField()
    birthday = models.DateField()
    addr = models.CharField(max_length=64)


class Publish(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()

注意事项：

• 表的名称，是根据 模型中的元数据自动生成的，也可以覆写为别的名称myapp_modelName
• id字段是自动添加的
• 对于外键字段，Django 会在字段名上添加 来创建数据库中的列名"_id"
• 这个例子中的 SQL 语句使用PostgreSQL 语法格式，要注意的是 Django 会根据设置中指定的数据库类型来使用相应的 SQL 语句。CREATE TABLE
• 定义好模型之后，你需要告诉Django _使用_这些模型。你要做的就是修改配置文件中的INSTALL_APPSZ中设置，在其中添加所在应用的名称。models.py
• 外键字段 ForeignKey 有一个 null=True 的设置（它允许外键接受空值 NULL），你可以赋给它空值 None 。

on_delete详细配置

1、models.CASCADE
    级联操作，当主表中被连接的一条数据删除时，从表中所有与之关联的数据同时被删除
2、models.SET_NULL
    当主表中的一行数据删除时，从表中所有与之关联的数据的相关字段设置为null,此时注意定义外键时，这个字段必须可以允许为空
3、models.PROTECT
    当主表中的一行数据删除时，由于从表中相关字段是受保护的外键，所以都不允许删除
4、models.SET_DEFAULT
    当主表中的一行数据删除时，从表中所有相关的数据的关联字段设置为默认值，此时注意定义外键时，这个外键字段应该有一个默认值
5、models.SET()
    当主表中的一条数据删除时，从表中所有的关联数据字段设置为SET()中设置的值，与models.SET_DEFAULT相似，只不过此时从表中的相关字段不需要设置default参数
6、models.DO_NOTHING
    什么都不做，一切都看数据库级别的约束，注数据库级别的默认约束为RESTRICT,这个约束与django中的models.PROTECT相似

 

2 添加表记录

2.1 一对多外键增删改

# 增
# 1  直接写实际字段 id （直接传被关联表的主键值）
models.Book.objects.create(title='论语',price=899.23,publish_id=1)
models.Book.objects.create(title='聊斋',price=444.23,publish_id=2)
models.Book.objects.create(title='老子',price=333.66,publish_id=1)
# 2  虚拟字段 对象 （先获取想关联的对象，传入对象，自动关联上对象的主键值）
publish_obj = models.Publish.objects.filter(pk=2).first()
models.Book.objects.create(title='红楼梦',price=666.23,publish=publish_obj)

# 删
models.Publish.objects.filter(pk=1).delete()  # 级联删除 出版社被删除了，相关联的书籍自动删除

# 修改
# 真实字段传外键值，把id是1这本书，外键字段改为2，即关联到出版社主键为2的对象
models.Book.objects.filter(pk=1).update(publish_id=2)
# 虚拟字段传对象，获取主键是1的出版社对象，把它塞给id是1这本书
publish_obj = models.Publish.objects.filter(pk=1).first()
models.Book.objects.filter(pk=1).update(publish=publish_obj)

2.2 多对多外键增删改

多对多就是在操作第三张关系表，多对多的增删改查就是对第三张表的增删改查。

问题是这张表不是我们自己建的，是通过虚拟外键字段让ORM帮你建的，你无法通过models查询到表的名字。

多对多关系的核心就是虚拟字段，Book类里面定义了authors外键字段，因此book_obj 能够访问到authors（对象查找属性），book_obj.authors 就相当于你已经到了第三张关系表了，可以对其进行操作。

# 如何给书籍添加作者？
book_obj = models.Book.objects.filter(pk=1).first()  # 先拿到想要操作的书籍对象
print(book_obj.authors)                              # 就类似于你已经到了第三张关系表了

# 方式一：add给第三张表添加数据，直接传数字，支持传多个
book_obj.authors.add(1)    # 给id为1的书籍绑定一个主键为1 的作者
book_obj.authors.add(2,3)  # 给id为1的书籍绑定主键为2和主键为3 的作者

# 方式二：add也可以传对象，支持传多个
author_obj1 = models.Author.objects.filter(pk=1).first()  # 拿到一个作者对象
author_obj2 = models.Author.objects.filter(pk=2).first()
author_obj3 = models.Author.objects.filter(pk=3).first()

book_obj.authors.add(author_obj1)
book_obj.authors.add(author_obj1,author_obj2)
   

# 删
# 方式一：remove删除第三张表里的数据，直接传数字，支持传多个
book_obj.authors.remove(2)
book_obj.authors.remove(1,3)

# 方式二：remove也可以传对象，支持传多个
author_obj1 = models.Author.objects.filter(pk=1).first()
author_obj2 = models.Author.objects.filter(pk=1).first()
author_obj3 = models.Author.objects.filter(pk=2).first()
book_obj.authors.remove(author_obj1)
book_obj.authors.remove(author_obj2,author_obj3)


# 修改 
# 方式一：set内必须是可迭代对象，直接传数字，支持传多个
book_obj.authors.set([1,2])  # 括号内必须给一个可迭代对象
book_obj.authors.set([3])    # 括号内必须给一个可迭代对象

# 方式二：set内也可以传对象，支持传多个
author_obj1 = models.Author.objects.filter(pk=2).first()
author_obj2 = models.Author.objects.filter(pk=3).first()
book_obj.authors.set([author_obj1,author_obj2])  # 括号内必须给一个可迭代对象
# set本质是先删除后增加，会删除原有的外键，再把列表里的外键或对象添加进去，如果原有外键在列表里则不会动


# 清空
# 不用传参，直接清空当前书籍以及所有与其绑定的作者
book_obj.authors.clear()

 

3 正反向的概念

外键字段在我手上，我查你就是正向，如书籍表查出版社表，book >>>外键字段在书那儿(正向)>>> publish；

外键字段不在我手上，我查你就是反向，如出版社查书籍表，publish >>>外键字段在书那儿(反向)>>>book

一对一和多对多正反向的判断也是如此。

口诀： 正向查询按字段、反向查询按表名小写。

---

 

4 多表查询

4.1 基于对象的跨表查询（子查询）

正向查询

# 1.查询书籍主键为1的出版社
# 先拿主键为1的书籍对象
book_obj = models.Book.objects.filter(pk=1).first()
# 正向查询按字段，书籍表里有publish外键字段，书籍与出版社是多对一，只能拿到一个出版社对象
res = book_obj.publish
print(res)
print(res.name)
print(res.addr)

# 2.查询书籍主键为2的作者
# 先拿主键为2的书籍对象
book_obj = models.Book.objects.filter(pk=2).first()
# 正向查询按字段，书籍表有authors外键字段，书籍与作者是多对多，可能拿都多个作者对象
res = book_obj.authors.all()  # <QuerySet [<Author: Author object>, <Author: Author object>]>
print(res)

# 3.查询作者jason的电话号码
# 先拿姓名为jason的作者对象
author_obj = models.Author.objects.filter(name='jason').first()
# 正向查询按字段，作者表里author_detail外键字段，作者与详情是一对一，只能拿到一个作者详情对象
res = author_obj.author_detail
print(res)
print(res.phone)

总结：正向查询按外键字段就能拿到对象，如果返回的对象可能有多个，则需要点all()

反向查询

# 4.查询出版社是东方出版社出版的书
# 先拿出版社对象
publish_obj = models.Publish.objects.filter(name='东方出版社').first()
# 反向查询按表名小写，一对多，可能拿到多个书籍对象
res = publish_obj.book_set.all()
print(res)

# 5.查询作者是jason写过的书
# 先拿作者对象
author_obj = models.Author.objects.filter(name='jason').first()
# 反向查询按表名小写，多对多，可能拿到多个书籍对象
res = author_obj.book_set.all()
print(res)

# 6.查询手机号是110的作者姓名
# 先拿作者详情对象
author_detail_obj = models.AuthorDetail.objects.filter(phone=110).first()
# 反向查询按表名小写，一对一，只能拿到一个作者对象
res = author_detail_obj.author
print(res.name)

"""
基于对象 反向查询的时候
当你的查询结果可以有多个的时候 就必须加_set.all()
当你的结果只有一个的时候 不需要加_set.all()
"""

可以通过在 ForeignKey 和 ManyToManyField的定义中设置 related_name 的值来覆写 FOO_set 的名称。反向操作时，使用的字段名，用于代替原反向查询时的’表名_set’。例如，如果 book model 中做一下更改：

class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    publish_date = models.DateField()
    publish = models.ForeignKey(to='Publish',related_name='bookList',on_delete=models.CASCADE)

那么接下来就会如我们看到这般:

# 查询 人民出版社出版过的所有书籍
publish_obj = models.Publish.objects.filter(name='人民出版社').first()
book_list = publish_obj.bookList.all()  # 与人民出版社关联的所有书籍对象集合

4.2 基于双下划线的跨表查询（连表查询）

Django 还提供了一种直观而高效的方式在查询（lookups）中表示关联关系，它能自动确认 SQL JOIN 联系。要做跨关系查询，就使用两个下划线来链接模型（model）间关联字段的名称，直到最终链接到你想要的model为止。

# 1.查询jason的手机号和作者姓名
# 正向查询按字段，通过author_detail字段相当于跨到作者详情表，双下划綫查询详情表数据
res = models.Author.objects.filter(name='jason').values('author_detail__phone','name')
# 反向查询按表名小写，先拿到名字是jason的作者详情对象，再values取值
res = models.AuthorDetail.objects.filter(author__name='jason').values('phone','author__name')


# 2.查询书籍主键为1的出版社名称和书的名称
# 正向
res = models.Book.objects.filter(pk=1).values('title','publish__name')
# 反向
res = models.Publish.objects.filter(book__id=1).values('name','book__title')


# 3.查询书籍主键为1的作者姓名
# 正向
res = models.Book.objects.filter(pk=1).values('authors__name')
# 反向
res = models.Author.objects.filter(book__id=1).values('name')


#4 查询书籍主键是1的作者的手机号
# book author authordetail 跨三张表
# 正向
res = models.Book.objects.filter(pk=1).values('authors__author_detail__phone')
# 反向
res = models.Author.objects.filter(book__pk=1).values('author_detail__phone')


"""
总结:
1、正向：filter直接筛选我的普通字段，values取值，外键字段 __查普通字段
2、反向：filter筛选 表名__我的普通字段， valuse取值，查的普通字段
你只要掌握了正反向的概念，以及双下划线，那么你就可以无限制的跨表
"""

 

5 聚合查询与分组查询

5.1 聚合查询

即聚合函数的使用，关键字aggregate，通常都是配合分组一起使用的

from app01 import models
from django.db.models import Max,Min,Sum,Count,Avg
    
# 1 所有书的平均价格
res = models.Book.objects.aggregate(Avg('price'))
print(res)

# 如果你想要为聚合值指定一个名称，可以向聚合子句提供它。
models.Book.objects.aggregate(average_price=Avg('price'))

# 2.上述方法一次性使用
res = models.Book.objects.aggregate(Max('price'),Min('price'),Sum('price'),Count('pk'),Avg('price'))
print(res)

5.2 分组查询

即group by的使用，关键字annotate， models后点什么，annotate就按什么分组。annotate（）为调用的中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。QuerySet

总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。

统计每一本书作者个数（按书籍分组）

from django.db.models import Avg, Max, Sum, Min, Max, Count

# 方式一 查出QuerySet对象，循环拿到book对象，再点属性取值
book_list = models.Book.objects.annotate(author_num=Count("authors"))
for book in book_list:
     print(book.title)
     print(book.author_num)
    

# 方式二 查出QuerySet对象，并直接取值
book_list = models.Book.objects.annotate(author_num=Count('authors__pk')).values('title', 'author_num')
print(book_list)

# author_num 先自定义一个字段名，用来存储统计出来的值
# 利用聚合函数，跨表查询正向，'authors__pk'， 这里ORM提供了简写，用'authors'就知道是统计作者个数
# values取值是列表套字典，values_list取值是列表套元组

统计每一个出版社的最便宜的书

# 原生SQL
SELECT publish.name, MIN(book.price) FROM publish INNER JOIN book ON publish_id=publish.id GROUP BY publish.id

from django.db.models import Avg, Max, Sum, Min, Max, Count

# 方式一
publishList = models.Publish.objects.annotate(min_price=Min("book__price"))
for publish_obj in publishList:
    print(publish_obj.name,publish_obj.min_price)
    

# 方式二
publishList = models.Publish.objects.annotate(min_price=Min('book__price')).values('name', 'min_price')

统计每一本以py开头的书籍的作者个数

queryResult = models.Book.objects.filter(title__startswith="Py").annotate(num_authors=Count('authors'))

统计不止一个作者的图书：（作者数量大于一） 

# 第一步：先按图书分组，求每一本书的作者个数
# 第二步：过滤出不止一个作者的图书

res = models.Book.objects.annotate(author_num=Count('authors')).filter(author_num__gt=1).values('title','author_num')
print(res)

只要你的orm语句得出的结果还是一个queryset对象，那么它就可以继续无限制的点queryset对象封装的方法。

查询每个作者出的书的总价格：

res = models.Author.objects.annotate(sum_price=Sum("book__price")).values("name", "sum_price")
print(res)

查询每个出版社的名称和出版的书籍个数:

res = models.Publish.objects.annotate(c=Count('book__name')).values('name','c')
print(res)

总结：

1、annotate本身表示group by的作用，前面找寻分组依据，内部放置显示可能用到的聚合函数，后面跟filter来增加限制条件，最后的values来表示分组后想要查找的字段值

2、values在前表示group by的字段，values在后表示取某几个字段

models.Book.objects.values('price').annotate()

# 在objects后紧跟values，就不按models后的表Book分组，而是按Book中的price字段分组

3、filter在前表示where，filter在后表示having

 

6 F查询与Q查询

6.1 F查询

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F（） 来做这样的比较。F（） 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

作用：能够帮助你直接获取到表中某个字段对应的数据。

from django.db.models import F
    
#1 查询卖出数大于库存数的书籍
# 筛选条件右边的数据之前是手动给一个值（常量），此题的要求是来自当前表中其他字段对应的值，需要用F
# F会拿出每一本书库存字段对应的值放在这里，一本一本进行比较
models.Book.objects.filter(maichu__gt=F('kucun'))

Django 支持 F（） 对象之间以及 F（） 对象和常数之间的加减乘除和取模的操作。

# 查询评论数大于收藏数2倍的书籍
models.Book.objects.filter(commnetNum__lt=F('keepNum')*2)

修改操作也可以使用F函数,比如将每一本书的价格提高30元： 

models.Book.objects.update(price=F('price')+30)

引申：

如果要修改char字段咋办(千万不能用上面对数值类型的操作！！！)？

如：把所有书名后面加上'爆款',(这个时候需要对字符串进行拼接Concat操作，并且要加上拼接值Value)

from django.db.models.functions import Concat
from django.db.models import Value

models.Book.objects.update(title=Concat(F('title'), Value('爆款')))
# models.Book.objects.update(title=F('title') + '爆款')  # 所有的名称会全部变成空白

Concat表示进行字符串的拼接操作，参数位置决定了拼接是在头部拼接还是尾部拼接，Value里面是要新增的拼接值。

 

6.2 Q查询

filter() 等方法中逗号隔开的条件是与的关系。 如果你需要执行更复杂的查询（例如OR语句），你可以使用Q对象。

作用：实现查询条件的“与或非”逻辑。

# 1.查询卖出数大于100或者价格小于600的书籍
# res = models.Book.objects.filter(maichu__gt=100,price__lt=600)  filter括号内多个参数是and关系
    
from django.db.models import Q
res = models.Book.objects.filter(Q(maichu__gt=100),Q(price__lt=600))   # Q包裹逗号分割 还是and关系
res = models.Book.objects.filter(Q(maichu__gt=100)|Q(price__lt=600))   # | or关系
res = models.Book.objects.filter(~Q(maichu__gt=100)|Q(price__lt=600))  # ~ not关系 取反


#2 Q的高阶用法  能够将查询条件的左边也变成字符串的形式
#应用：实现搜索功能，用户输入的字符串就直接能放在查询条件的左边
q = Q()
q.connector = 'or'  # 把查询关系改为or
q.children.append(('maichu__gt',100))    # 添加元祖，左边是字符形式的查询条件，右边是值
q.children.append(('price__lt',600))
res = models.Book.objects.filter(q)      # filter支持放入q对象，默认还是and关系
print(res)

 

7 多对多关系三种创建方式

方式一：全自动，利用orm自动帮我们创建第三张关系表

class Book(models.Model):
    name = models.CharField(max_length=32)
    authors = models.ManyToManyField(to='Author')

class Author(models.Model):
    name = models.CharField(max_length=32)
    
"""
优点:代码不需要你写 非常的方便 还支持orm提供操作第三张关系表的方法...add、remove、set、clear和正反向查询
不足之处:第三张关系表的扩展性极差(没有办法额外添加字段...)
"""

方式二：纯手动

class Book(models.Model):
    name = models.CharField(max_length=32)
    
class Author(models.Model):
    name = models.CharField(max_length=32)

class Book2Author(models.Model):
    book_id = models.ForeignKey(to='Book')
    author_id = models.ForeignKey(to='Author')
    
'''
    优点:第三张表完全取决于你自己进行额外的扩展
    不足之处:需要写的代码较多，不能够再使用orm提供的简单的方法
'''

方式三：半自动， 手动创建第三张关系表，但告诉ORM我们是多对多关系，你内部的操作让我用

class Book(models.Model):
    name = models.CharField(max_length=32)
    # 在Book表创建外键字段，但告诉ORM书和作者是通过我自己建的表关联，不需要你创建
    # 还要告诉ORM是通过哪两个字段建立关联的，因为第三张表可能有其他外键字段
    # through_fields参数括号内的顺序，在哪张表建的外键字段，就把那张表的外键字段放前面
    authors = models.ManyToManyField(to='Author',            
                                     through='Book2Author',
                                     through_fields=('book','author')
                                     )
    
class Author(models.Model):
    name = models.CharField(max_length=32)
    # books = models.ManyToManyField(to='Book',
    #                                  through='Book2Author',
    #                                  through_fields=('author','book')
    #                                  )
    
class Book2Author(models.Model):
    book = models.ForeignKey(to='Book')
    author = models.ForeignKey(to='Author')

"""
through_fields字段先后顺序
    判断的本质：
        通过第三张表查询对应的表 需要用到哪个字段就把哪个字段放前面
    你也可以简化判断
        当前表是谁 就把对应的关联字段放前面
                
半自动:可以使用orm的正反向查询 但是没法使用add,set,remove,clear这四个方法
"""

总结：你需要掌握的是全自动和半自动 为了扩展性更高 一般我们都会采用半自动(写代码要给自己留一条后路)。