ORM-分组查询、F/Q查询

一、 F查询和Q查询

F 针对自己单表中字段的比较和处理

Q 可以和&、|、~搭配使用

# 评论数大于点赞数
 obj = models.Book.objects.filter(comment__gt=F('good'))
 print(obj)

# 评论数是大于点赞数的1.5倍
 obj = models.Book.objects.filter(comment__gt=F('good')*1.5)
 print(obj)

# 修改价格
 models.Book.objects.all().update(
     b_price=F('b_price')+1
)

# 查询年龄大于50岁的男性作者
obj = models.Author.objects.filter(a_sex='male', a_age__gt=50)
print(obj)
obj = models.Author.objects.filter(Q(a_sex='female')&Q(a_age__lte=45))
print(obj)

二、分组查询

1、obj = xxx. annotate(yyy). values('zzz')————————>以xxx的主键为分组依据

2、obj = xxx. values('aaa')annotate(yyy). values('zzz')————————>以aaa为分组依据

#查询每个出版社出版书籍的平均价格
obj = models.Book.objects.values('publish_id').annotate(a=Avg('b_price')).values('a')
print(obj)

#查询每个出版社的名字及其对应出版书籍的最高价格
obj = models.Publisher.objects.values('p_name').annotate(a=Max('book__b_price')).values('p_name', 'a')
print(obj)


# 查询每一个出版社的id以及其出版的书对应的平均价格
obj = models.Publisher.objects.annotate(a=Avg('book__b_price')).values('p_id', 'a')
print(obj)

# 查询每本书的id以及对对应的作者的最大年龄
obj = models.Book.objects.annotate(a=Max('authors__a_age')).values('b_id', 'a')
print(obj)

# 查询每个作者的姓名及出版的书的最高价格
obj = models.Author.objects.annotate(a=Max('book__b_price')).values('a_name', 'a')
print(obj)

# 查询作者id大于3作者的姓名以及出版社的平均价格
obj = models.Author.objects.filter(a_id__gt=3).annotate(a=Avg('book__b_price')).values('a_name', 'a')
print(obj)
# 查询作者id大于2或者作者年龄大于20岁的女作者的姓名及出版的书的最高价格
obj = models.Author.objects.filter(Q(a_id__gt=20) | Q(a_age__gt=40), a_sex='male').annotate(a=Max('book__b_price')).values('a_name', 'a')
print(obj)

# 查询每个作者出版的书的最高价格的平均值
obj = models.Author.objects.annotate(a=Max('book__b_price')).values('a').aggregate(b=Avg('a'))
print(obj)

# 每个作者出版的所有书的价格以及最高价格的那本书的名称
'''不能用ORM分组，因为ORM分组存在局限性，哪里出错具体看一下代码'''
'''
SELECT MAX(`app01_book`.`b_price`) AS `a` FROM `app01_author` LEFT OUTER JOIN `app01_book_authors` 
ON (`app01_author`.`a_id` = `app01_book_authors`.`author_id`) LEFT OUTER JOIN `app01_book` 
ON (`app01_book_authors`.`book_id` = `app01_book`.`b_id`) 
GROUP BY `app01_author`.`a_name` ORDER BY NULL LIMIT 21; args=()
'''
# 正确
obj = models.Author.objects.values('a_name').annotate(a=Max('book__b_price')).values('a')
print(obj)

'''
SELECT `app01_book`.`b_title`, MAX(`app01_book`.`b_price`) AS `a` 
FROM `app01_author` LEFT OUTER JOIN `app01_book_authors` 
ON (`app01_author`.`a_id` = `app01_book_authors`.`author_id`) 
LEFT OUTER JOIN `app01_book` ON (`app01_book_authors`.`book_id` = `app01_book`.`b_id`) 
GROUP BY `app01_author`.`a_name`, `app01_book`.`b_title` ORDER BY NULL LIMIT 21; args=()

'''
# 出错，因为以作者名分组，只能取作者名（分组依据）和a（筛选出的值），其余的值不能正确的筛选出，默认取那个组的第一条记录
obj = models.Author.objects.values('a_name').annotate(a=Max('book__b_price')).values('book__b_title', 'a')
print(obj)

'''解决办法，用元素sql语句写'''
SELECT app01_author.a_name, MAX(app01_book.b_price) FROM (app01_author INNER JOIN app01_book_authors ON app01_author.a_id=app01_book_authors.author_id
INNER JOIN app01_book ON app01_book_authors.book_id=app01_book.b_id) GROUP BY app01_author.a_name;  #没问题


SELECT app01_author.a_name, MAX(app01_book.b_price), app01_book.b_title FROM (app01_author INNER JOIN app01_book_authors ON app01_author.a_id=app01_book_authors.author_id
INNER JOIN app01_book ON app01_book_authors.book_id=app01_book.b_id) GROUP BY app01_author.a_name;  #报错

#该题目正确sql语句(方法一)
'''注意要设置'''
SELECT tt.aa, app01_book.b_price FROM(SELECT app01_author.a_id as aa, MAX(app01_book.b_price) as bb FROM app01_author INNER JOIN app01_book_authors ON app01_author.a_id=app01_book_authors.author_id
INNER JOIN app01_book ON app01_book.b_id=app01_book_authors.book_id GROUP BY app01_author.a_id) as tt
INNER JOIN app01_book_authors ON app01_book_authors.author_id=tt.aa
INNER JOIN app01_book ON app01_book_authors.book_id=app01_book.b_id
WHERE tt.bb=app01_book.b_price

#该题目正确sql语句(方法二，利用排序，分组后只能取上面的那一行，那把我们通过排序将想要的那一行放在上面)
'''注意sql_mode此时不能设置成 ONLY_FULL_GROUP_BY'''
SELECT * FROM (SELECT app01_author.a_id, app01_book.b_price, app01_book.b_title FROM app01_author INNER JOIN app01_book_authors ON app01_author.a_id=app01_book_authors.author_id
INNER JOIN app01_book ON app01_book.b_id=app01_book_authors.book_id ORDER BY app01_book.b_price desc) as tt
GROUP BY a_id

三、 sql_mode设置

1. 查询sql_mode的语句：select @@sql_mode
2. 查询全局sql_mode的语句：select @@global.sql_mode
3. 设置零时sql_mode的语句：set sql_mode='ONLY_FULL_GROUP_BY'(这是零时会话，只针对于当前会话客户端有效，一关闭或其它客户端没用)
4. 设置全局sql_mode的语句: set global sql_mode='ONLY_FULL_GROUP_BY'(这是永久会话，对所有客户端都有用，但一旦mysql服务端已重启，就失效)
5. 最终解决办法：先设置global sql_mode，然后在配置文件中修改sql_mode。

四、 在orm中执行原生sql语句

Django 提供两种方法使用原始SQL进行查询：

1. 使用raw()方法，进行原始SQL查询并返回模型实例；(raw()语法查询必须包含主键)

2. 完全避开模型层，直接执行自定义的SQL语句。

   from django.db import connection, connections
   cursor = connection.cursor()  # cursor = connections['default'].cursor()
   cursor.execute("""SELECT * from auth_user where id = %s""", [1])
   ret = cursor.fetchone()