###########1 聚合查询(聚合函数:最大,最小,和,平均,总个数)"""D:\Djangozuoye\zuoye1014"""django项目路径
from django.db.models import Avg,Max,Min,Count,Sum
#1 计算所有图书的平均价格
aggregate结束,已经不是queryset对象了
book=models.Book.objects.all().aggregate(Avg('price')){'price__avg':57.0}# 起别名
book=models.Book.objects.all().aggregate(avg=Avg('price')){'avg':57.0}#2 计算总图书数
book = models.Book.objects.all().aggregate(count=Count('id')){'count':10}# 3 计算最低价格的图书
book = models.Book.objects.all().aggregate(min=Min('price')){'min': Decimal('6.00')}# 4 计算最大价格图书
book = models.Book.objects.all().aggregate(max=Max('price')){'max': Decimal('120.00')}print(book)
二十、分组查询
####2 分组查询'''
查询每一个部门名称以及对应的员工数
book:
id name price publish
1 金品 11.2 1
2 西游 14.2 2
3 东游 16.2 2
4 北邮 19.2 3
'''# 示例一:查询每一个出版社id,以及出书平均价格
select publish_id,avg(price)from app01_book group by publish_id;
SELECT publish_id,AVG(price),GROUP_CONCAT(id) FROM app01_book GROUP BY publish_id;# annotate# annotate() 内写聚合函数# values在前表示group by的字段# values在后表示取某几个字段# filter在前表示where# filter在后表示havingfrom django.db.models import Avg, Count, Max, Min
# 查询每个出版社出版图书的平均价格和出版社名称
ret=models.Book.objects.values('publish_id').annotate(avg=Avg('price')).values('publish_name','avg')print(ret)<QuerySet [{'publish__name':'沙河出版社','avg':46.25},{'publish__name':'人民出版社','avg':52.333333},{'publish__name':'瑶池出版社','avg':76.0}]># 查询出版社id大于1的出版社id,以及出书平均价格
select publish_id,avg(price)from app01_book where publish_id>1 group by publish_id;
ret = models.Publish.objects.filter(id__gt=1).annotate(avg=Avg('book__price')).values('id','avg')
ret = models.Book.objects.filter(publish_id__gt=1).values('publish__id').annotate(avg=Avg('price')).values('publish__id','avg')
ret=models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).values('publish_id','avg')print(ret)<QuerySet [{'publish__id':2,'avg':52.333333},{'publish__id':3,'avg':76.0}]># 查询出版社id大于1的出版社id,以及出书平均价格大于60的
select publish_id,avg(price)as avg from app01_book where publish_id>1 group by publish_id HAVING avg>60;
ret = models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).filter(avg__gt=60).values('publish_id','avg')print(ret)<QuerySet [{'publish__id':3,'avg':76.0}]>## 查询每一个出版社出版的书籍个数# pk 代指主键
ret=models.Book.objects.get(pk=1)print(ret.name)
ret=models.Publish.objects.values('pk').annotate(count=Count('book__id')).values('name','count')print(ret)<QuerySet [{'name':'沙河出版社','count':4},{'name':'人民出版社','count':3},{'name':'瑶池出版社','count':3}]># 如果没有指定group by的字段,默认就用基表(Publish)主键字段作为group by的字段
ret=models.Publish.objects.annotate(count=Count('book__id')).values('name','count')print(ret)<QuerySet [{'name':'沙河出版社','count':4},{'name':'人民出版社','count':3},{'name':'瑶池出版社','count':3}]># 另一种方式实现
ret=models.Book.objects.values('publish_id').annotate(count=Count('id')).values('publish__name','count')print(ret)<QuerySet [{'name':'沙河出版社','count':4},{'name':'人民出版社','count':3},{'name':'瑶池出版社','count':3}]>#查询每个作者的名字,以及出版过书籍的最高价格(建议使用分组的表作为基表)# 如果不用分组的表作为基表,数据不完整可能会出现问题
ret=models.Author.objects.values('pk').annotate(max=Max('book__price')).values('name','max')
ret = models.Author.objects.annotate(max=Max('book__price')).values('name','max')
ret= models.Book.objects.values('authors__id').annotate(max=Max('price')).values('authors__name','max')print(ret)<QuerySet [{'name':'大','max': Decimal('120.00')},{'name':'中','max': Decimal('120.00')},{'name':'小','max': Decimal('120.00')}]>#查询每一个书籍的名称,以及对应的作者个数
ret=models.Book.objects.values('pk').annotate(count=Count('author__id')).values('name','count')
ret=models.Book.objects.annotate(count=Count('author__id')).values('name','count')
ret=models.Author.objects.values('book__id').annotate(count=Count('id')).values('book__name','count')#print(ret)<QuerySet [{'name':'红0','count':3},{'name':'红楼梦1','count':2},{'name':'红楼梦','count':2},{'name':'红楼梦','count':2},{'name':'红楼梦','count':1},{'name':'红楼梦','count':1},{'name':'红6','count':1},{'name':'红7','count':1},{'name':'红8','count':1},{'name':'红9','count':1}]>#统计不止一个作者的图书
ret=models.Book.objects.values('pk').annotate(count=Count('authors__id')).filter(count__gt=1).values('name','count')
ret = models.Author.objects.values('book__id').annotate(count=Count('id')).filter(count__gt=1).values('book__name','count')print(ret)<QuerySet [{'name':'红0','count':3},{'name':'红楼梦1','count':2},{'name':'红楼梦','count':2},{'name':'红楼梦','count':2}]># 上述结果可以去重
res = models.Book.objects.values('pk').annotate(count=Count('author')).filter(count__gt=1).values('name','count').distinct()<QuerySet [{'name':'红0','count':3},{'name':'红楼梦1','count':2},{'name':'红楼梦','count':2}]># 统计价格数大于10元,作者的图书
ret = models.Book.objects.values('pk').filter(price__gt=10).annotate(count=Count('authors__id')).values('name','count')print(ret)#统计价格数大于10元,作者个数大于1的图书
ret = models.Book.objects.values('pk').filter(price__gt=10).annotate(count=Count('authors__id')).filter(count__gt=1).values('name','count')print(ret)
二十一、F与Q查询
# F查询:取出数据库的某个字段的值# res = models.Book.objects.values(F('read_num')) 报错
res = models.Book.objects.values('name', read=F('read_num'))<QuerySet [{'name':'红0','read':1},{'name':'红楼梦1','read':534},{'name':'红d楼梦','read':534},{'name':'红楼梦','read':5344},{'name':'红楼梦','read':7675},{'name':'红楼梦','read':3},{'name':'红6','read':346},{'name':'红7','read':54},{'name':'红8','read':546},{'name':'红9','read':657}]># 把read_num都加1from django.db.models import F
ret=models.Book.objects.all().update(read_num=F('read_num')+1)print(ret)(0.056) UPDATE `app01_book` SET `read_num` =(`app01_book`.`read_num` +1); args=(1,)10#查询评论数大于阅读数的书籍
ret=models.Book.objects.all().filter(speak_num__gt=F('read_num')).values('name')<QuerySet [{'name':'红0'},{'name':'红楼梦1'},{'name':'红楼梦'},{'name':'红6'},{'name':'红7'}]>## 查询评论数大于阅读数2倍的书籍
ret=models.Book.objects.filter(speak_num__gt=F('read_num')*2)print(ret)<QuerySet [{'name':'红0'},{'name':'红楼梦1'},{'name':'红楼梦'},{'name':'红6'},{'name':'红7'}]># Q查询:制造 与或非的条件# Q查询:制造 与或非的条件# 查询名字叫红楼梦或者价格大于100的书from django.db.models import Q
res = models.Book.objects.filter(Q(name='红楼梦')| Q(price__gt=100)).values('name','price')<QuerySet [{'name':'红楼梦','price': Decimal('100.00')},{'name':'红楼梦','price': Decimal('120.00')},{'name':'红楼梦','price': Decimal('100.00')},{'name':'红楼梦','price': Decimal('120.00')}]># 查询名字叫红楼梦并且价格大于100的书
res = models.Book.objects.filter(Q(name__contains='红')& Q(price__lt=100)).values('name','price')print(res)<QuerySet [{'name':'红0','price': Decimal('50.00')},{'name':'红楼梦1','price': Decimal('50.00')},{'name':'红6','price': Decimal('6.00')},{'name':'红7','price': Decimal('7.00')},{'name':'红8','price': Decimal('8.00')},{'name':'红9','price': Decimal('9.00')}]># 查询名字不为红楼梦的书
res = models.Book.objects.filter(~Q(name='红楼梦')).values('name')print(res)<QuerySet [{'name':'红0'},{'name':'红楼梦1'},{'name':'红6'},{'name':'红7'},{'name':'红8'},{'name':'红9'}]># Q可以嵌套
res = models.Book.objects.filter((Q(name='红楼梦')& Q(price=100))| Q(id__lt=2)).values('id','name','price')print(ret)<QuerySet [{'id':1,'name':'红0','price': Decimal('50.00')},{'id':3,'name':'红楼梦','price': Decimal('100.00')},{'id':5,'name':'红楼梦','price': Decimal('100.00')}]>
二十二、原生SQL
# 原生sql(有些sql用orm写不出来)# 两种方案# 第一种:用的比较少from django.db import connection
#
cursor = connection.cursor()#
cursor.execute('select * from app01_book where id < %s',[2])## # row = cursor.fetchone()
row = cursor.fetchall()print(row)((1,'红0', Decimal('50.00'), datetime.datetime(2017,10,14,18,32,48,720483),1,2,3465),)# 第二种,用的多
books=models.Book.objects.raw('select * from app01_book where id <2')print(books)#RawQuerySet对象for book in books:print(book.id)print(book.name)1
红0
books=models.Book.objects.raw('select * from app01_publish where id < 3')for book in books:print(book.__dict__)print(book.id)print(book.name)print(book.addr)print(book.price){'_state':<django.db.models.base.ModelState object at 0x0000000003FF7190>,'id':1,'name':'沙河出版社','addr':'北京','phone':'0526-123'}1
沙河出版社
北京
50.00{'_state':<django.db.models.base.ModelState object at 0x0000000003FF72B0>,'id':2,'name':'人民出版社','addr':'上海','phone':'0527-456'}2
人民出版社
上海
50.00(0.012) select *from app01_publish where id<3; args=()(0.000) SELECT VERSION(); args=None(0.000) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` =1; args=(1,)(0.009) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` =2; args=(2,)
进程已结束,退出代码 0
authors = models.Author.objects.raw('SELECT app01_author.id,app01_author.name,app01_authordetail.sex FROM app01_author JOIN app01_authordetail ON app01_author.author_detail_id = app01_authordetail.id WHERE app01_authordetail.sex = 1')for author in authors:print(author.name)print(author.__dict__)
二十三、defer和only
# defer和only(查询优化相关)# 但是只能使用only指定的字段
books = models.Book.objects.all().only('name')# books = models.Book.objects.only('name')
book = books.first()# 取第一个值,也支持索引取值print(book.name)print(book.price)# 能出来,但是会再次去数据库查询
红050.00(0.014) SELECT `app01_book`.`id`, `app01_book`.`name` FROM `app01_book` ORDER BY `app01_book`.`id` ASC LIMIT 1; args=()(0.001) SELECT VERSION(); args=None(0.001) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` =1; args=(1,)# 可以使用defer指定之外的字段,指定的字段使用时需要再次查询,和only相反
books = models.Book.objects.defer('name')
book = books.first()print(book.name)# 查询改字段,会再去数据库查询print(book.price)print(book.id)print(book.read_num)print(book.speak_num)(0.075) SELECT `app01_book`.`id`, `app01_book`.`price`, `app01_book`.`publish_date`, `app01_book`.`publish_id`, `app01_book`.`read_num`, `app01_book`.`speak_num` FROM `app01_book` ORDER BY `app01_book`.`id` ASC LIMIT 1; args=()(0.001) SELECT VERSION(); args=None(0.002) SELECT `app01_book`.`id`, `app01_book`.`name` FROM `app01_book` WHERE `app01_book`.`id` =1; args=(1,)
红050.00123465
进程已结束,退出代码 0