from app01 import models
def query(request):
# #####################基于对象查询(子查询)##############################
# 按字段(publish)
# 一对多 book -----------------> publish
# <----------------
# book_set.all()
# 正向查询按字段:
# 查询python这本书籍的出版社的邮箱
# python=models.Book.objects.filter(title="python").first()
# print(python.publish.email)
# 反向查询按 表名小写_set.all()
# 苹果出版社出版的书籍名称
# publish_obj=models.Publish.objects.filter(name="苹果出版社").first()
# for obj in publish_obj.book_set.all():
# print(obj.title)
# 按字段(authors.all())
# 多对多 book -----------------------> author
# <----------------
# book_set.all()
# 查询python作者的年龄
# python = models.Book.objects.filter(title="python").first()
# for author in python.authors.all():
# print(author.name ,author.age)
# 查询alex出版过的书籍名称
# alex=models.Author.objects.filter(name="alex").first()
# for book in alex.book_set.all():
# print(book.title)
# 按字段 authorDetail
# 多对多 author -----------------------> authordetail
# <----------------
# 按表名 author
#查询alex的手机号
# alex=models.Author.objects.filter(name='alex').first()
# print(alex.authorDetail.telephone)
# 查询家在山东的作者名字
# ad_list=models.AuthorDetail.objects.filter(addr="shandong")
#
# for ad in ad_list:
# print(ad.author.name)
'''
对应sql:
select publish_id from Book where title="python"
select email from Publish where nid = 1
'''
# #####################基于queryset和__查询(join查询)############################
# 正向查询:按字段 反向查询:表名小写
# 查询python这本书籍的出版社的邮箱
# ret=models.Book.objects.filter(title="python").values("publish__email")
# print(ret.query)
'''
select publish.email from Book
left join Publish on book.publish_id=publish.nid
where book.title="python"
'''
# 苹果出版社出版的书籍名称
# 方式1:
ret1=models.Publish.objects.filter(name="苹果出版社").values("book__title")
print("111111111====>",ret1.query)
#方式2:
ret2=models.Book.objects.filter(publish__name="苹果出版社").values("title")
print("2222222222====>", ret2.query)
#查询alex的手机号
# 方式1:
ret=models.Author.objects.filter(name="alex").values("authorDetail__telephone")
# 方式2:
models.AuthorDetail.objects.filter(author__name="alex").values("telephone")
# 查询手机号以151开头的作者出版过的书籍名称以及书籍对应的出版社名称
ret=models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values('title',"publish__name")
print(ret.query)
###########################################聚合################################# #查询所有书籍的价格和 from django.db.models import Sum,Count,Avg,Max,Min ret=models.Book.objects.all().aggregate(price_sum=Sum("price")) print(ret) #{'price_sum': Decimal('154.00')} #查询所有作者的平均年龄 ret=models.Author.objects.all().aggregate(age_avg=Avg('age')) print(ret) #--------------------------------分组---------------# ''' sql分组 emp id name age dep 1 alex 22 保安部 2 egon 33 保安部 1 zlp 18 保洁部 #统计每个部门多少人 sql: select Count(id) from emp group by dep orm: models.emp.objects.values('dep').annotate(c=Count("id")) ########################################################## emp id name age dep 1 alex 22 1 2 egon 33 2 1 zlp 18 1 dep id name 1 保安部 1 保洁部 查询每一个部门 名称以及对于的人数 id name age dep id name 1 alex 22 1 1 保安部 2 egon 33 2 1 保洁部 1 zlp 18 1 1 保安部
sql: select name Count(*) from emp inner join emp.dep.id=dep.id group by dep.id dep.name orm: 关键点: 1.queryset对象.annotate() 2.annotate进行分组统计,按前面select字段进行group by 3.annotate()返回值依然是queryset对象,增加了分组统计后的键值对 models.dep.objects.values("name").annotate(c=Count(emp__name)).values("name","c") ''' #查询每一个作者的名字及出版过的书籍最高价格 from django.db.models import Sum,Count,Avg,Max,Min ret= models.Author.objects.values("name").annotate(max_price=Max("book__price")).values("name","max_price") print(ret) #<QuerySet [{'name': '张艳娟', 'max_price': Decimal('22.00')}] #查询每个出版社出版过的书籍平均价格 ret=models.Publish.objects.values("name").annotate(avg_price=Avg("book__price")).values("name","avg_price") print(ret) #查询没一本书籍的作者个数 ret=models.Book.objects.values("title").annotate(c=Count("authors__name")).values("title",'c') print(ret) #查询每一个分类的名称以及对于的文章数 # models.Category.objects.all().annotate(c=Count("article__title")).values("title",'c') #统计不止一个图书名称: ret = models.Book.objects.annotate(c=Count("authors__name")).filter(c__gte=1).values("title","c") print(ret)
****************************************************************************************************************************
from django.db import models
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",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)
publishDate=models.DateField()
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',)