django-ORM复习补充

建表

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

    # 与AuthorDetail建立一对一的关系
    ad = models.OneToOneField(to="AuthorDetail")

class AuthorDetail(models.Model):
    birthday = models.DateField()
    telephone = models.BigIntegerField()
    addr = models.CharField(max_length=64)


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


class Book(models.Model):

    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    keepNum = models.IntegerField()
    commentNum = models.IntegerField()

    # 与Publish建立一对多的关系,外键字段建立在多的一方
    publish = models.ForeignKey(to="Publish", to_field="nid")

    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
    authors = models.ManyToManyField(to='Author')

可以看到表结构中有三个关联字段

# 与AuthorDetail建立一对一的关系
ad = models.OneToOneField(to="AuthorDetail")
            
# 与Publish建立一对多的关系,外键字段建立在多的一方
publish = models.ForeignKey(to="Publish", to_field="nid")

# 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
authors = models.ManyToManyField(to='Author')

基于对象查询（子查询）

一对多：

正向查询按字段：publish
Book------------------------>Publish
<------------------------
反向查询按表名小写_set

多对多：
正向查询按字段：authors
Book------------------------>Author
<------------------------
反向查询按表名小写_set

一对一
正向查询按字段：ad
Author---------------------->AuthorDetail
<---------------------
反向查询按表名小写

示例：

#########################单表查询

# 1 查询id为1 的书籍的价格
# Book.objects.filter(id=1).values("price")

##========================================多表查询=======================


##############################基于对象的多表查询######################################
#################一对多

# 查询python这本书的出版社的城市和email
# book_obj=Book.objects.filter(title="python").first()
# print(book_obj.publish.email,book_obj.publish.city)
# 查询北京出版社出版社过得所有书籍名称
# publish_obj=Publish.objects.filter(name="南京出版社").first()
# book_list=publish_obj.book_set.all() # Queryset[obj,obj]
# for obj in book_list:
#     print(obj.title)

#################多对多

# 查询go书籍的作者名称和年龄
# book_obj=Book.objects.filter(title="go").first()
# author_list=book_obj.authors.all()
# for obj in author_list:
#     print(obj.name,obj.age)

# alex出版过得所有书籍的名称和价格
# author_obj=Author.objects.filter(name="alex").first()
# book_list=author_obj.book_set.all()
# for obj in book_list:
#     print(obj.title,obj.price)

#################一对一

# 查询alex的电话号码
# author_obj = Author.objects.filter(name="alex").first()
# print(author_obj.ad.telephone)
# # 查询电话号码以123开头的作者名称
# ad_obj=AuthorDetail.objects.filter(telephone__startswith=123).first()
# print(ad_obj.author.name)

基于Queryset查询（join查询）

正向查询按字段
反向查询按表名小写

##############################基于QuerySet和双下划线的多表查询############################

#################一对多
# 查询python这本书的出版社的城市和email
# ret=Book.objects.filter(title="python").values("publish__city","publish__email")
# print(ret)
'''
values("publish__city"):

book_list=Book.objects.filter(title="python")
temp=[]
for obj in book_list:
     d={}
     d["publish__city"]=obj.publish.city
     d["publish__email"]=obj.publish.email
     temp.append(d)

return temp  # <QuerySet [{'publish__city': '北京', 'publish__email': '123'}]>


'''
# 查询北京出版社出版社过得所有书籍名称

# ret=Publish.objects.all().values("book__title")
# #Book.objects.all()
# print(ret)
'''
publish_list=Publish.objects.filter(name="北京出版社")

temp=[]
for obj in publish_list:

    book_list=obj.book_set.all()
    for book in book_list:
         d={}
         d["book__title"]=book.title
         temp.append(d)
return temp

'''

#################多对多

# 查询go书籍的作者名称和年龄
ret=Book.objects.filter(title="go").values("authors__name","authors__age")
ret=Book.objects.filter(title="go").values_list("authors__name","authors__age")
print(ret)# [('alex', 33), ('egon', 38)]>

# alex出版过得所有书籍的名称和价格
#ret=Author.objects.filter(name="alex").values("book__title","book__price")

#################一对一

# 查询alex的电话号码
#ret=Author.objects.filter(name="alex").values("ad__telephone")
# 查询电话号码以123开头的作者名称
# ret=AuthorDetail.objects.filter(telephone__startswith=123).values("author__name")
# print(ret)

#====================================== 扩展查询====================

#查询python这本书的出版社的城市和email
ret=Book.objects.filter(title="python").values("publish__city")
ret=Publish.objects.filter(book__title="python").values("email","city")

#查询go书籍的作者名称和年龄
ret=Book.objects.filter(title="go").values("authors__name", "authors__age")
Author.objects.filter(book__title="go").values("name","age")

################################聚合和分组###############################################
################################聚合
# 查询所有书籍中最高的价格
# select Max("price") from book
ret=Book.objects.all().aggregate(m=Max("price"))
print(ret) # {"price__max":123]
################################分组

# 每一个出版社出版过得书籍的最高价格
ret=Publish.objects.all().annotate(m=Max("book__price")).values("name","m")
print(ret)
# 每一个作者出版过的书籍的个数
ret=Author.objects.all().annotate(c=Count("book")).values("name","c")