Django框架学习——9—(查询操作、聚合函数、F表达式和Q表达式)

1、查询操作

date

针对某些date或者datetime类型的字段。可以指定date的范围。并且这个时间过滤，还可以使用链式调用。

date_test = Common.objects.filter(test_date__date=datetime(year=2018,month=12,day=19))

print(date_test.query)
print(date_test)

翻译成SQL语句:
SELECT `user_common`.`id`, `user_common`.`content`, `user_common`.`pid`, `user_common`.`test_date` FROM `user_common` WHERE DATE(`user_common`.`test_date`) = 2018-12-19

year

根据年份进行查找。

articles = Article.objects.filter(pub_date__year=2018)
articles = Article.objects.filter(pub_date__year__gte=2017)
以上的代码在翻译成SQL语句为如下：

select ... where pub_date between '2018-01-01' and '2018-12-31';
select ... where pub_date >= '2017-01-01';

time

根据时间进行查找。

articles = Article.objects.filter(pub_date__time=time(hour=15,minute=21,second=10))
以上的代码是获取每一天中15点21分10秒发表的所有文章。

# 查询10秒到11秒之间的
start_date = time(hour=17,minute=21,second=10)
end_date = time(hour=17,minute=21,second=11)
date_test = Common.objects.filter(test_date__time__range = (start_date,end_date))

实例如下：

def find_data1(request):
    # date字段
    # article = Article.objects.filter(creat_time__date=datetime(year=2020, month=7, day=1))
    # article = Article.objects.filter(creat_time__date='2020-07-01')
    # print(article)
    # Mysql原生语句,Mysql中没有时区的概念
    # print(article.query)
    
    # year字段
    article = Article.objects.filter(creat_time__year=2020)
    # 查询2019年之后的数据
    article = Article.objects.filter(creat_time__year__gt=2019)
    
    article = Article.objects.filter(creat_time__date__gt=datetime(year=2020, month=7, day=1))

    return HttpResponse("find_data")

2、聚合函数

如果你用原生SQL，则可以使用聚合函数来提取数据。比如提取某个商品销售的数量，那么可以使用Count，如果想要知道商品销售的平均价格，那么可以使用Avg。
聚合函数是通过aggregate方法来实现的。

先写模型文件

from django.db import models


class Author(models.Model):
    """作者模型"""
    name = models.CharField(max_length=100)
    age = models.IntegerField()
    email = models.EmailField()

    class Meta:
        db_table = 'author'         # 表名


class Publisher(models.Model):
    """出版社模型"""
    name = models.CharField(max_length=300)

    class Meta:
        db_table = 'publisher'


class Book(models.Model):
    """图书模型"""
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.FloatField()                                 # 注意下面一个价格字段，定价
    rating = models.FloatField()      # 评分
    # 外键
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

    class Meta:
        db_table = 'book'


class BookOrder(models.Model):
    """图书订单模型"""
    book = models.ForeignKey("Book", on_delete=models.CASCADE)
    price = models.FloatField()                                  # 与上面的价格不同，图书的定价和售价不一样

    class Meta:
        db_table = 'book_order'        

数据库中导入测试数据：

聚合函数的使用

1.Avg：求平均值。比如想要获取所有图书的价格平均值。那么可以使用以下代码实现。

from django.db.models import Avg
from django.db import connection
result = Book.objects.aggregate(Avg('price'))
print(connection.queries)       # 打印SQL语句
print(result)

以上的打印结果是：

{"price__avg":23.0}
其中price__avg的结构是根据field__avg规则构成的。如果想要修改默认的名字，那么可以将Avg赋值给一个关键字参数。

result = Book.objects.aggregate(my_avg=Avg('price'))
print(result)
那么以上的结果打印为：

{"my_avg":23}

实例如下：

from django.shortcuts import render
from django.http import HttpResponse
from .models import Book
from django.db.models import Avg
from django.db import connection


def avg_func(request):
    # 获取所有图书的价格平均值,  聚合函数需要采用aggregate,而不是get、filter
    # book = Book.objects.aggregate(Avg('price'))
    book = Book.objects.aggregate(avg__price=Avg('price'))       # 第二种写法
	# id大于等于2的价格平均值,先筛选id>=2，再计算平均值
    book = Book.objects.filter(id__gt=2).aggregate(avg__price=Avg('price'))     

    print(book)                                                  # {'price__avg': 97.25}
    print(type(book))                                            # <class 'dict'>,无法使用query属性
    # print(book.query)                                            # 'dict' object has no attribute 'query'
    print(connection.queries)                                    # 会输出所有的SQL原生语句
    '''
    [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
    {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},
    {'sql': 'SELECT AVG(`book`.`price`) AS `price__avg` FROM `book`', 'time': '0.001'}]
    '''
    print(connection.queries[-1])                                # 列表中最后一个才是执行的SQL语句
    return HttpResponse("取平均值")

aggregate和annotate的区别

• 1·aggregate：返回使用聚合函数后的字段和值。
• 2.annotate：在原来模型字段的基础之上添加一个使用了聚合函数的字段，并且在使用聚合函数的时候，会使用当前这个模型的主键进行分组（group by）。

def agg_ann_func(request):
    # 每一本图书的销售平均价格,annotate是先分组再执行其他的条件
    books = Book.objects.annotate(avg=Avg('bookorder__price'))
    '''
    <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>, <Book: Book object (4)>]>
    三国演义 98.0
    水浒传 97.0
    西游记 None
	红楼梦 None
    [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.001'},
    {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},
    {'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`,
    AVG(`book_order`.`price`) AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`)
    GROUP BY `book`.`id` ORDER BY NULL  LIMIT 21', 'time': '0.014'},
    {'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, AVG(`book_order`.`price`)
    AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`)
    GROUP BY `book`.`id` ORDER BY NULL', 'time': '0.001'}]
    '''
    
    # {'avg': 91.0}  所有值的平均,aggregate返回使用聚合函数后的字段和值
    # books = Book.objects.aggregate(avg=Avg('bookorder__price'))      # bookorder是BookOrder的模型反向引用
    '''
       [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
       {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},
       {'sql': 'SELECT AVG(`book_order`.`price`)
       AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON
       (`book`.`id` = `book_order`.`book_id`)', 'time': '0.023'}]
    '''
    print(books)
 
    for i in books:
        print(i.name, i.avg)
    print(connection.queries)
    
    return HttpResponse("agg_ann_func")

注意：annotate是先分组再执行其他的条件，aggregate返回使用聚合函数后的字段和值

2.Count：获取指定的对象的个数。

from django.db.models import Count
result = Book.objects.aggregate(book_num=Count('id'))
以上的result将返回Book表中总共有多少本图书。

Count类中，还有另外一个参数叫做distinct，默认是等于False，如果是等于True，那么将去掉那些重复的值。

比如要获取作者表中所有的不重复的邮箱总共有多少个。
from djang.db.models import Count
result = Author.objects.aggregate(count=Count('email',distinct=True))

# 统计每本图书的销量
result = Book.objects.annotate(book_nums=Count("bookorder"))
for book in result:
    print("%s/%s"%(book.name,book.book_nums))

3.Max和Min：获取指定对象的最大值和最小值。比如想要获取Author表中，最大的年龄和最小的年龄分别是多少。

from django.db.models import Max,Min
result = Author.objects.aggregate(Max('age'),Min('age'))
如果最大的年龄是88,最小的年龄是18。那么以上的result将为：
{"age__max":88,"age__min":18}

# 统计每本售卖图书的最大值和最小值
request = Book.objects.annotate(max=Max("bookorder__price"),min=Min("bookorder__price"))
print(request)

4.Sum：求指定对象的总和。比如要求图书的销售总额。

from djang.db.models import Sum

result = Book.objects.aggregate(total=Sum("price"))

# 每一本图书的销售总额
result = Book.objects.annotate(total=Sum("bookorder__price"))

# 统计2019年，销售总额
result = BookOrder.objects.filter(create_time__year=2019).aggregate(total=Sum("price"))

实例如下：

from django.shortcuts import render
from django.http import HttpResponse
from .models import Book, Author, BookOrder
from django.db.models import Avg, Count, Max, Min, Sum
from django.db import connection


def avg_func(request):
    # 获取所有图书的价格平均值,  聚合函数需要采用aggregate,而不是get、filter
    # book = Book.objects.aggregate(Avg('price'))
    # book = Book.objects.aggregate(avg__price=Avg('price'))       # 第二种写法
    # id大于等于2的价格平均值,先筛选id>=2，再计算平均值
    book = Book.objects.filter(id__gt=2).aggregate(avg__price=Avg('price'))

    print(book)                                                  # {'price__avg': 97.25}
    print(type(book))                                            # <class 'dict'>,无法使用query属性
    # print(book.query)                                            # 'dict' object has no attribute 'query'
    print(connection.queries)                                    # 会输出所有的SQL原生语句
    '''
    [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
    {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},
    {'sql': 'SELECT AVG(`book`.`price`) AS `price__avg` FROM `book`', 'time': '0.001'}]
    '''
    print(connection.queries[-1])                                # 列表中最后一个才是执行的SQL语句
    return HttpResponse("取平均值")


def agg_ann_func(request):
    # 每一本图书的销售平均价格,annotate是先分组再执行其他的条件
    books = Book.objects.annotate(avg=Avg('bookorder__price'))
    '''
    <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>, <Book: Book object (4)>]>
    三国演义 98.0
    水浒传 97.0
    西游记 95.0
    红楼梦 99.0
    [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.001'},
    {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},
    {'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`,
    AVG(`book_order`.`price`) AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`)
    GROUP BY `book`.`id` ORDER BY NULL  LIMIT 21', 'time': '0.014'},
    {'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, AVG(`book_order`.`price`)
    AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`)
    GROUP BY `book`.`id` ORDER BY NULL', 'time': '0.001'}]
    '''
    
    # {'avg': 91.0}  所有值的平均,aggregate返回使用聚合函数后的字段和值
    # books = Book.objects.aggregate(avg=Avg('bookorder__price'))      # bookorder是BookOrder的模型反向引用
    '''
       [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
       {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},
       {'sql': 'SELECT AVG(`book_order`.`price`)
       AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON
       (`book`.`id` = `book_order`.`book_id`)', 'time': '0.023'}]
       '''
    print(books)
 
    for i in books:
        print(i.name, i.avg)
    print(connection.queries)
    
    return HttpResponse("agg_ann_func")


def count_func(request):
    # 查询一共多少图书
    # books = Book.objects.aggregate(book_num=Count('pk'))
    # books = Book.objects.aggregate(book_num=Count('id'))               # {'book_num': 4}
    # print(books)
    
    # 查询作者年龄不重复的,distinct=True去重复，默认是False
    # authors = Author.objects.aggregate(count=Count("age", distinct=True))
    # print(authors)                                                       # {'count': 4}
    
    # 每本图书的销量
    # books = Book.objects.annotate(book_num=Count("bookorder__id"))
    books = Book.objects.annotate(book_num=Count("bookorder"))         # 默认自动关联id字段
    for book in books:
        print(book.name, book.book_num)
    return HttpResponse('count_func')
    
    
def max_min_func(request):
    # 最大值最小值
    author = Author.objects.aggregate(Max('age'), Min('age'))          # {'age__max': 46, 'age__min': 28}
    print(author)
    
    # 每本图书销售价格最大最小值
    book = Book.objects.annotate(max=Max("bookorder__price"), min=Min("bookorder__price"))
    '''
    <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>, <Book: Book object (4)>]>
    '''
    print(book)
    
    for i in book:
        print(i.name, i.max, i.min)
        
    '''
    三国演义 95.0 85.0
    水浒传 94.0 93.0
    西游记 None None
    红楼梦 None None
    '''
    
    return HttpResponse("max_min_func")


def sum_func(request):
    # 图书销售总额
    book = BookOrder.objects.aggregate(Sum("price"))             # {'price__sum': 455.0}
    print(book)
    
    # 每一本图书的销售总额,    先分组
    books = Book.objects.annotate(total=Sum("bookorder__price"))      # 注意外键关联字中是双下划线 _ _
    for book in books:
        print(book.name, book.total)
    
    # 统计每本图书2020年的销售总额
    books = Book.objects.filter(bookorder__price__year=2020).annotate(total=Sum("bookorder__price"))
    
    # 统计2020年的图书销售总额,creat_time是模型新添加字段
    book = BookOrder.objects.filter(creat_time__year=2020).aggregate(total=Sum("price"))
    return HttpResponse("sum_func")

3、F表达式和Q表达式

F表达式

F表达式是用来优化ORM操作数据库的。比如我们要将公司所有员工的薪水都增加1000元，如果按照正常的流程，应该是先从数据库中提取所有的员工工资到Python内存中，然后使用Python代码在员工工资的基础之上增加1000元，最后再保存到数据库中。这里面涉及的流程就是，首先从数据库中提取数据到Python内存中，然后在Python内存中做完运算，之后再保存到数据库中。

注意： 映射数据库前需要添加app名称到INSTALLED_APPS
定义模型：

from django.db import models


class User(models.Model):
    name = models.CharField(max_length=50)
    age = models.IntegerField()
    salary = models.FloatField()           # 薪水

实现功能：所有人薪水加1000

from django.shortcuts import render
from django.http import HttpResponse
from .models import User
from django.db.models import F
from django.db import connection
'''
# F表达式    所有人薪水加1000
1.提取所有员工的薪资
2.在原有基础上+1000
3.保存到数据库中
'''


def index(request):
    # users = User.objects.all()
    # for user in users:
    #     user.salary += 1000
    #     user.save()
    
    # F表达式
    user = User.objects.update(salary=F("salary")+1000)
    print(user)
    print(type(user))                                            # <class 'int'>
    print(connection.queries)                                    # 只要不是QuerySet类型数据，都需要用connection.queries表达原生SQL语句
    '''
    [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
    {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},
    {'sql': 'UPDATE `user_user` SET `salary` = (`user_user`.`salary` + 1000)', 'time': '0.042'}]
    '''
    return HttpResponse("index")

F表达式并不会马上从数据库中获取数据，而是在生成SQL语句的时候，动态的获取传给F表达式的值。

比如如果想要获取作者中，name和email相同的作者数据。如果不使用F表达式。

authors = Author.objects.all()
for author in authors:
    if author.name == author.email:
        print(author)
如果使用F表达式，那么一行代码就可以搞定。示例代码如下：

from django.db.models import F
authors = Author.objects.filter(name=F("email"))

Q表达式（与或非运算）

如果想要实现所有价格高于100元，并且评分达到9.0以上评分的图书。

books = Book.objects.filter(price__gte=100,rating__gte=9)

以上这个案例是一个并集查询，可以简单的通过传递多个条件进去来实现。
但是如果想要实现一些复杂的查询语句，比如要查询所有价格低于10元，或者是评分低于9分的图书。那就没有办法通过传递多个条件进去实现了。这时候就需要使用Q表达式来实现了。

from django.db.models import Q
# 或运算
books = Book.objects.filter(Q(price__lte=10) | Q(rating__lte=9))

以上是进行或运算，当然还可以进行其他的运算，比如有&和~（非）等。

from django.db.models import Q
# 获取id等于3的图书
books = Book.objects.filter(Q(id=3))
# 获取id等于3，或者名字中包含文字"传"的图书
books = Book.objects.filter(Q(id=3)|Q(name__contains="传"))
# 获取价格大于100，并且书名中包含"传"的图书
books = Book.objects.filter(Q(price__gte=100)&Q(name__contains="传"))
# 获取书名包含"传"，但是id不等于3的图书
books = Book.objects.filter(Q(name__contains='传') & ~Q(id=3))