Django版本:
>>> django.VERSION
(4, 1, 0, 'final', 0)
PS:基于前几章的进度进行修改
django.db.models
引入Avg,Max,Min,Count,Sum
,注意需要首字母大写from django.db.models import Avg,Max,Min,Count,Sum
Avg:计算平均值
Max:最大值
Min:最小值
Count:统计出现的次数
Sum:和
aggregate()
是QuerySet的一个终止子句,生成的一个汇总值,相当于count()
函数。需要注意的是,使用aggregate()
后,数据类型就变成了字典类型,不能再使用QuerySet数据类型的一些API了Max
和Min
属性名称+__聚合函数名称
,值是计算出来的聚合值,如果要自定义返回字典的键的名称,可以起别名,例如:aggregate(别名 = 聚合函数名("属性名称"))
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.aggregate(Avg("price"))
print(res,type(res))
return HttpResponse("ok")
127.0.0.1:8000/add_book
进行测试,观察终端输出,可以看到输出数据类型是字典
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.aggregate(c=Count("id"),max=Max("price"),min=Min("price"))
print(res,type(res))
return HttpResponse("ok")
django.db.models
引入Avg,Max,Min,Count,Sum
,注意需要首字母大写from django.db.models import Avg,Max,Min,Count,Sum
分组后使用
values
取值,返回值是QuerySet数据类型里面的一个个字典如果使用
Values_list
取值,那么返回值则是QuerySet数据类型里的一个个元组
Mysql中的limit
就相当于ORM中QuerySet数据类型的切片
注意:
annotate里面存放聚合函数,其中:
values或values_list放在annotate前面
:前面的values或values_list声明以什么字段分组,是字典还是元组,而annotate执行分组values或values_list放在annotate后面
:annotate表示直接以当前表的pk(主键)执行分组,后面的values或values_list表示查询哪些字段,==(也就是输出什么字段)==并且要将annotate里面的聚合函数起别名,在values或values_list中写它的别名
- models.py
# -*- coding: utf-8 -*-
from django.db import models
class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=5,decimal_places=2)
pub_date = models.DateField()
publish = models.ForeignKey("Publish",on_delete=models.CASCADE) #多对一
authors = models.ManyToManyField("Author") #多对多
class Publish(models.Model):
name = models.CharField(max_length=32)
city = models.CharField(max_length=64)
email = models.EmailField()
class Author(models.Model):
name = models.CharField(max_length=32)
age = models.SmallIntegerField()
au_detail = models.OneToOneField("AuthorDetail",on_delete=models.CASCADE) #一对一
class AuthorDetail(models.Model):
gender_choices = (
(0,"女"),
(1,"男"),
(2,"保密"),
)
gender = models.SmallIntegerField(choices=gender_choices)
tel = models.CharField(max_length=32)
addr = models.CharField(max_length=64)
birthday = models.DateField()
class Emp(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
salary = models.DecimalField(max_digits=8, decimal_places=2)
dep = models.CharField(max_length=32)
province = models.CharField(max_length=32)
class Emps(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
salary = models.DecimalField(max_digits=8, decimal_places=2)
dep = models.ForeignKey("Dep", on_delete=models.CASCADE)
province = models.CharField(max_length=32)
class Dep(models.Model):
title = models.CharField(max_length=32)
(test) PS F:\django\app1> python .\manage.py makemigrations app1_model
Migrations for 'app1_model':
app1_model\migrations\0003_dep_emp_emps_remove_book_authors_remove_book_publish_and_more.py
- Create model Dep
- Create model Emp
- Create model Emps
- Remove field authors from book
- Remove field publish from book
- Delete model Author
- Delete model AuthorDetail
- Delete model Book
- Delete model Publish
(test) PS F:\django\app1> python .\manage.py migrate app1_model
Operations to perform:
Apply all migrations: app1_model
Running migrations:
Applying app1_model.0003_dep_emp_emps_remove_book_authors_remove_book_publish_and_more... OK
INSERT INTO app1_model_emp (id,name,age,salary,dep,province) VALUES ('1','令狐冲','24','6000.00','销售部','河南');
INSERT INTO app1_model_emp (id,name,age,salary,dep,province) VALUES ('2','任盈盈','18','8000.00','关公部','广东');
INSERT INTO app1_model_emp (id,name,age,salary,dep,province) VALUES ('3','任我行','56','10000.00','销售部','广东');
INSERT INTO app1_model_emp (id,name,age,salary,dep,province) VALUES ('4','岳灵珊','19','6000.00','关公部','河南');
INSERT INTO app1_model_emp (id,name,age,salary,dep,province) VALUES ('5','小龙女','20','8000.00','关公部','河北');
INSERT INTO app1_model_dep (id,title) VALUES ('1','销售部');
INSERT INTO app1_model_dep (id,title) VALUES ('2','关公部');
INSERT INTO app1_model_emps (id,name,age,salary,province,dep_id) VALUES ('2','令狐冲','24','8000.00','河南','1');
INSERT INTO app1_model_emps (id,name,age,salary,province,dep_id) VALUES ('3','任盈盈','18','9000.00','广东','2');
INSERT INTO app1_model_emps (id,name,age,salary,province,dep_id) VALUES ('4','任我行','57','10000.00','广东','1');
INSERT INTO app1_model_emps (id,name,age,salary,province,dep_id) VALUES ('5','岳灵珊','19','6000.00','河南','2');
INSERT INTO app1_model_emps (id,name,age,salary,province,dep_id) VALUES ('6','小龙女','20','8000.00','河北','2');
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Publish.objects.values("name").annotate(in_price = Min("book__price"))
print(res)
return HttpResponse("ok")
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.annotate(c = Count("authors__name")).values("title","c")
print(res)
return HttpResponse("ok")
Python
开头的书籍的作者个数# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.filter(title__startswith="Python").annotate(c = Count("authors__name")).values("title","c")
print(res)
return HttpResponse("ok")
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.annotate(c = Count("authors__name")).filter(c__gt=0).values("title","c")
print(res)
return HttpResponse("ok")
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.annotate(c = Count("authors__name")).order_by("-c").values("title","c")
print(res)
return HttpResponse("ok")
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Author.objects.annotate(all=Sum("book__price")).values("name","all")
print(res)
return HttpResponse("ok")
F()
的实例可以在查询中引用字段,来比较同一个model实例中两个不同字段的值,之前的过滤器都只是将字段值与某个常量作比较,如果想要对两个字段的值进行比较,就需要用到F()
django.db.models
引入F:from django.db.models import Avg,Max,Min,Count,Sum,F
语法:F("字段名称")
F()
动态获取对象字段的值,可以进行运算,Django支持F()
对象之间以及F()
对象和常数之间的加减乘除和取余的操作,修改操作即update也可以使用F()
函数
查询工资大于年龄的人
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum,F
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
book = models.Emp.objects.filter(salary__gt=F("age")).values("name","age")
print(book)
return HttpResponse("ok")
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum,F
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.update(price=F("price")+100)
print(res)
return HttpResponse("ok")
django.db.models
中引入Q:from django.db.models import Avg,Max,Min,Count,Sum,F,Q
Q(条件判断)
,例如Q(title__startswith="Python")
之前构造的过滤器里的多个条件的关系都是and
,如果需要执行更复杂的查询,例如or
语句,就可以使用Q()
Q对象可以使用&,|,~
,即与、或、非等操作符进行组合,优先级从高到低为~ & |
,可以混合使用Q对象和关键字参数,Q对象和关键字参数是使用and
拼在一起的,即把,
看成与
的关系,但是Q对象必须位于所有关键字参数的前面
查询价格大于350或者名称以Python
开头的书籍和名称和价格
# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum,F,Q
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.filter(Q(price__gt=350)|Q(title__startswith="Python")).values("title","price")
print(res)
return HttpResponse("ok")
M教程
结尾或者不是2011年11月的书籍# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum,F,Q
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.filter(Q(title__endswith="M教程")|~Q(Q(pub_date__year=2011) & Q(pub_date__month=11))).values("title","pub_date")
print(res)
return HttpResponse("ok")
教程
的# -*- coding: utf-8 -*-
from django.shortcuts import render,HttpResponse
from app1_model import models
from django.db.models import Avg,Max,Min,Count,Sum,F,Q
def add_book(request):
#books = models.Book.objects.create(title="Python",price=500,publish="Python出版社",pub_date="1970-12-10")
res = models.Book.objects.filter(Q(pub_date__year=2011) & Q(pub_date__month=11),Q(title__contains="教程")).values("title","pub_date")
print(res)
return HttpResponse("ok")