F表达式作用

生成sql语句时，不是真正的去数据库中查询数据。而是动态获取传给F表达式的字段的值。

举个栗子

还是ORM聚合函数里的sql模型

#所有图书售价加10元
from django.db.models import F
Book.objects.update(price = F("price") + 10)
print(connection.queries[-1])
>>>UPDATE `book` SET `price` = (`book`.`price` + 10)

#2、获取名字和备注一样的图书
books = Book.objects.filter(name = F("remark"))
print(connection.queries[-1])
>>>SELECT `book`.`id`, `book`.`name`, `book`.`remark`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id` FROM `book` WHERE `book`.`name` = (`book`.`remark`)

使用F表达式可以减少重复查询，示例

books = Book.objects.annotate(author_name = F("author__name"))
print(books)
print(connection.queries[-1])
>>>SELECT `book`.`id`, `book`.`name`, `book`.`remark`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `author`.`name` AS `author_name` FROM `book` INNER JOIN `author` ON (`book`.`author_id` = `author`.`id`) LIMIT 21

通过外键__字段的方式，新增了一列，只要一条sql搞定。
如果通过下面的方式，每次循环都会根据id关联查找name，产生一条sql，效率极低：

books = Book.objects.all()
for book in books:
    print(book.author.name)
>>>SELECT `book`.`id`, `book`.`name`, `book`.`remark`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `author`.`name` AS `author_name` FROM `book` INNER JOIN `author` ON (`book`.`author_id` = `author`.`id`) LIMIT 21

Q表达式作用

filter只能实现多个条件与，Q表达式可以实现与或非

#filter示例，多条件与。获取价格大于100，并且评分在4.85分以上图书
books = Book.objects.filter(price__gte=100,rating__gte=4.85)
print(books)
print(connection.queries[-1])
>>>SELECT @@SQL_AUTO_IS_NULL'}, {'time': '0.000', 'sql': 'SELECT VERSION()'}, {'time': '0.001', 'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`remark`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id` FROM `book` WHERE (`book`.`price` >= 100.0e0 AND `book`.`rating` >= 4.85e0)

#Q示例，多条件与。获取价格大于100，并且评分在4.85分以上图书
books = Book.objects.filter(Q(price__gte=100) & Q(rating__gte=4.85))
print(books)
print(connection.queries[-1])
>>>SELECT `book`.`id`, `book`.`name`, `book`.`remark`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id` FROM `book` WHERE (`book`.`price` >= 100.0e0 AND `book`.`rating` >= 4.85e0)

#Q示例，多条件或。获取价格大于100，或者评分大于4.85的图书
books = Book.objects.filter(Q(price__gte=100) | Q(rating__gte=4.85))
print(books)
print(connection.queries[-1])
>>>SELECT `book`.`id`, `book`.`name`, `book`.`remark`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id` FROM `book` WHERE (`book`.`price` >= 100.0e0 OR `book`.`rating` >= 4.85e0)

#Q示例，非操作。获取价格大于100，并且评分不大于等于4.85的图书
books = Book.objects.filter(Q(price__gte=100) & ~Q(rating__gte=4.85))
print(books)
print(connection.queries[-1])
>>>SELECT `book`.`id`, `book`.`name`, `book`.`remark`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id` FROM `book` WHERE (`book`.`price` >= 100.0e0 AND NOT (`book`.`rating` >= 4.85e0))