6.Model层:多表操作

1.创建模型

模型建立如下：

from django.db import models

# Create your models here.
class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name=models.CharField( max_length=32)
    age=models.IntegerField()
    # 与AuthorDetail建立一对一的关系
  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=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()


class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    title = models.CharField( max_length=32)
    publish_date=models.DateField(null=True)
    price=models.DecimalField(max_digits=5,decimal_places=2)
    # 与Publish建立一对多的关系,外键字段建立在多的一方
    publish=models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE)
    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
    authors=models.ManyToManyField(to='Author',)

关系型字段	对应关系	注意
ForeignKey	多对一	Django 会在字段名上添加 “_id” 来创建数据库中的列名
ManyToManyField	多对多	Django 创建了一个中间连接表来表示多对多的关系(可以使用 [db_table]选项手动提供连接表的名称)
OneToOneField	一对一

2.添加表纪录

操作前先简单的录入一些数据：

1.my_model_book表

INSERT INTO `orm`.`my_model_book`(`id`, `title`, `pub_date`, `price`, `publish_id`) VALUES (1, '红楼梦', '2021-12-12', 300.00, 3);
INSERT INTO `orm`.`my_model_book`(`id`, `title`, `pub_date`, `price`, `publish_id`) VALUES (2, '三国演义', '2020-10-10', 300.00, 4);
INSERT INTO `orm`.`my_model_book`(`id`, `title`, `pub_date`, `price`, `publish_id`) VALUES (3, '水浒传', '2021-02-21', 200.00, 3);
INSERT INTO `orm`.`my_model_book`(`id`, `title`, `pub_date`, `price`, `publish_id`) VALUES (4, '西游记', '2021-02-21', 100.00, 4);

2.my_model_publish表:

INSERT INTO `orm`.`my_model_publish`(`id`, `name`, `city`, `email`) VALUES (3, '人民出版社', '背景', 'beijingchubanshe@qq.com');
INSERT INTO `orm`.`my_model_publish`(`id`, `name`, `city`, `email`) VALUES (4, '湖南出版社', '长沙', 'hunanchubanshe@qq.com');

3.my_model_author表:

INSERT INTO `orm`.`my_model_author`(`id`, `name`, `age`, `authorDetail_id`) VALUES (7, 'Jason1', 16, 1);
INSERT INTO `orm`.`my_model_author`(`id`, `name`, `age`, `authorDetail_id`) VALUES (8, 'Jason2', 17, 2);

4.my_model_authordetail表

INSERT INTO `orm`.`my_model_authordetail`(`id`, `birthday`, `telephone`, `addr`) VALUES (1, '2021-02-18', 1, 'a');
INSERT INTO `orm`.`my_model_authordetail`(`id`, `birthday`, `telephone`, `addr`) VALUES (2, '2021-02-19', 2, 'b');

一对多

方式一:

publish_obj=Publish.objects.get(nid=1)
book_obj=Book.objects.create(title="雷雨",publishDate="2012-12-12",price=100,publish=publish_obj)

方式二:

book_obj=Book.objects.create(title="雷雨",publishDate="2012-12-12",price=100,publish_id=1)　   　

**核心：**book_obj.publish 与 book_obj.publish_id 是什么？

1	2	3
book_obj.publish	<class ‘my_model.models.Publish’>	出版社名称
book_obj.publish_id	<class ‘int’>	出版社id

多对多

#1.获取书籍对象
book_obj=Book.objects.create(title="追风筝的人",price=200,publishDate="2012-11-12",publish_id=1)

#2.获取作者对象 
yuan=Author.objects.filter(id=1).first() 
egon=Author.objects.filter(id=2).first() 

#3.绑定多对多关系,即向关系表book_authors中添加纪录,为书籍绑定的做作者对象
book_obj.authors.add(yuan,egon)       
book_obj.authors.add(*[])

核心: book_obj.authors.all() 是什么？

1	2
book_obj.authors.all()	<QuerySet [<Author: yuan>, <Author: egon>]>

**注意:**多对多关系其它常用 API：

book_obj.authors.remove()   # 将某个特定的对象从被关联对象集合中去除 book_obj.authors.remove(*[])
book_obj.authors.clear()    #清空被关联对象集合
book_obj.authors.set()      #先清空再设置　　

3.基于对象的跨表查询

1	定义	如何查询
正向	A(关联属性在A中)_—>B	正向查询按照字段
反向	B---->A(关联属性在A中)	反向查询按照表名小写_set

注意:

一对一查询反向是按照表名小写即可,并不要加_set

一对多查询

Book(书籍)和Publish(出版社)

正向查询

#查询西游记的出版社
a = Book.objects.get(title="西游记")
print(a.publish)

反向查询

#查询人民出版社出版了那些书
b = Publish.objects.get(name="人民出版社")
print(b.book_set.all()) 
for book_obj in b:
    print(book_obj.title)

一对一查询

Author(作者) 与 AuthorDetail(作者详情)

正向查询

#查出名字为Jason1的家庭地址
a = Author.objects.get(name="Jason1")
print(a.authorDetail.addr)

反向查询

# 查询所有住址为b的作者的姓名
b = AuthorDetail.objects.get(addr="a")
print(b.author.name)

注意:

一对一查询反向是按照表名小写即可,并不要加_set

多对多查询

Author 与 Book

正向查询

# 西游记所有作者的名字以及手机号
book_obj=Book.objects.filter(title="西游记").first()
authors=book_obj.authors.all()
for author_obj in authors:
     print(author_obj.name,author_obj.authorDetail.telephone)

反向查询

# 查询Jason1出过的所有书籍的名字
author_obj=Author.objects.get()
book_list=author_obj.book_set.all()
 for book_obj in book_list:
        print(book_obj.title)

注意：

你可以通过在 ForeignKey() 和 ManyToManyField 的定义中设置 related_name 的值来覆写 表名小写_set 的名称。例如，如果 Article model 中做一下更改：

publish = ForeignKey(Book, related_name='bookList')

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

# 查询 人民出版社出版过的所有书籍
publish=Publish.objects.get()
book_list=publish.bookList.all()  # 与人民出版社关联的所有书籍对象集合

4.基于双下划线的跨表查询

Django 还提供了一种直观而高效的方式在查询,使用两个下划线来链接模型间关联字段的名称,它能自动确认 SQL JOIN 联系

正向	反向
字段	表名

一对多查询

问题:查询人民出版社出版过的所有书籍的名字与价格(一对多)

方式一

a = Book.objects.filter(publish__name="人民出版社").values("title", "price")
print(a)

方式二

a=Publish.objects.filter(name="人民出版社").values_list("book__title", "book__price")
print(a)

注意:

	类型	实例
values	元祖	<QuerySet [(‘红楼梦’, Decimal(‘300.00’)), (‘水浒传’, Decimal(‘200.00’))]>
values_list	字典	<QuerySet [{‘book__title’: ‘红楼梦’, ‘book__price’: Decimal(‘300.00’)}, {‘book__title’: ‘水浒传’, ‘book__price’: Decimal(‘200.00’)}]>

多对多查询

问题:　查询Jason1出过的所有书籍的名字(多对多)

方式一

a=Book.objects.filter(authors__name="Jason1").values("title")
print(a)

方式二

a = Author.objects.filter(name="Jason1").values("book__title")
print(a)

一对一查询

问题:查询Jason1的手机号

方式一

a=Author.objects.filter(name="Jason1").values("authorDetail__telephone")
print(a)

方式二

a=AuthorDetail.objects.filter(author__name="Jason1").values("telephone")
print(a)

连续跨表查询

问题一:查询人民出版社出版过的所有书籍的名字以及作者的姓名

方式一

a = Publish.objects.filter(name="人民出版社").values("book__title", "book__authors__name")

方式二

a=Author.objects.filter(book__publish__name="人民出版社").values("book__title","name")

方式三

a=Book.objects.filter(publish__name="人民出版社").values("title","authors__name")

问题二:地址以a开头的作者出版过的所有书籍名称以及出版社名称

方式一

a = Book.objects.filter(authors__authorDetail__addr__startswith="a").values("title","publish_name")

方式二

 a = Author.objects.filter(authorDetail__addr__startswith="a").values("book__title", "book__publish__name")

注意:

如果定义了 related_name ,则用 related_name 替换表名,

publish = ForeignKey(Blog, related_name='bookList')

问题: 查询人民出版社出版过的所有书籍的名字与价格(一对多)

#不再按表名:book,而是related_name:bookList
queryResult=Publish.objects.filter().values_list("bookList__title","bookList__price") 

4.聚合与分组

聚合

aggregate(*args, **kwargs)

问题:查询所有书籍的平均价格

from django.db.models import Avg
ret=Book.objects.all().aggregate(avg_price=Avg('price'))

注意:

1.除了Avg还有Max, Min,Sum

2.aggregate(聚合函数名(字段名)) 的返回值是一个键值对的字典,默认字段名_小写聚合函数名

分组

跨表分组查询本质就是***将关联表 join 成一张表***,再按单表的思路进行分组查询

表模型.objects.values("group by的字段 ").annotate(聚合函数(“统计字段”))

一对多查询

问题:查询一个出版社的名称以及出版的书籍

sql查询

select publish,name,Count('title') from Book inner join Publish on book.publish_id=publish.id group by publish.id

orm查询

Publish.objects.values("id").annotate(c=Count('book__title')).values("name","c")
<QuerySet [{'name': '人民出版社', 'c': 2}, {'name': '湖南出版社', 'c': 2}]>

一对一查询

问题: 查询每个书籍的名称以及对应的作者数

sql查询

SELECT my_model_book.title, COUNT(my_model_book_authors.author_id) AS author_count FROM my_model_book LEFT OUTER JOIN my_model_book_authors ON (my_model_book.id = my_model_book_authors.book_id) GROUP BY my_model_book.id 

orm查询

ret = Book.objects.values("id").annotate(author_count=Count("authors")).values("title", "author_count")
<QuerySet [{'title': '红楼梦', 'author_count': 2}, {'title': '三国演义', 'author_count': 2}, {'title': '水浒传', 'author_count': 2}, {'title': '西游记', 'author_count': 2}]>

多对多查询

问题:查询每一个作者的名字以及出版过书籍的最高价格

sql语句

select my_model_author,name,Max(my_model_book.price) 
from my_model_book inner join my_model_book_authors on my_model_book.id=my_model_book_authors.book_id 
inner join my_model_author on  my_model_author.id=my_model_book_authors.author_id

orm查询

方式一

ret=Book.objects.values("authors").annotate(book_price=Max("price")).values("authors__name","price")
#结果
<QuerySet [{'authors__name': 'Jason1', 'book_price': Decimal('300.00')}, {'authors__name': 'Jason2', 'book_price': Decimal('300.00')}]>

方式二

ret = Author.objects.values("id").annotate(book_price=Max("book__price")).values("name", "book_price")
#注意
book__price和book_price的区别

查询练习

(1) 练习：统计每一个出版社的最便宜的书

SELECT "app01_publish"."name", MIN("app01_book"."price")  AS "MinPrice" FROM "app01_publish" 
LEFT  JOIN "app01_book" ON ("app01_publish"."nid" = "app01_book"."publish_id") 
GROUP BY "app01_publish"."nid", "app01_publish"."name", "app01_publish"."city", "app01_publish"."email" 

Publish.objects.annotate(MinPrice=Min("book__price")).values_list("name","MinPrice")

(2) 练习：统计每一本书的作者个数

ret=Book.objects.values("id").annotate(authorsNum=Count('authors__name'))

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

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

(4) 统计不止一个作者的图书：

queryResult=Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

(5) 根据一本图书作者数量的多少对查询集 QuerySet 进行排序:

Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

(6) 查询各个作者出的书的总价格:

#按author表的所有字段 group by
queryResult=Author.objects.annotate(SumPrice=Sum("book__price")).values_list("name","SumPrice")
print(queryResult)

F 查询与 Q 查询

算术运算

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')*2)

问题:将每本书的价格提高30元

Book.objects.all().update(price=F("price")+30)　

Q 查询

逻辑运算

有时候我们需要执行or逻辑的条件查询，这时使用Q方法就可以了，它可以连接多个查询条件。Q对象前面加~可以表示否定。

from django.db.models import Q
#查询作者是jason1或者json2的书籍
bookList=Book.objects.filter(Q(authors__name="Jason1")|Q(authors__=="Jason2"))
#查询作者名字包含1,不在2017出版的书籍
bookList=Book.objects.filter(Q(authors__icontains='1') & ~Q(publishDate__year=2017)).values_list("tile")

注意:

查询函数可以混合使用 Q 对象和关键字参数。所有提供给查询函数的参数(关键字参数或 Q 对象)都将 "AND” 在一起.但是,如果出现 Q 对象,它必须位于所有关键字参数的前面.例如：

bookList=Book.objects.filter(Q(publishDate__year=2016) | Q(publishDate__year=2017),
                              title__icontains="python")

问题:

删除操作:

1.一对多:删除某本书籍相应的出版社是不是会被删除
2.一对一:删除作者同时删除作者详细信息,且多对多表中书籍和作者的关联记录也要被删除
3.多对多:这本书不是某个作者出的,删除多对多映射中作作者和书籍的关系