ORM操作：双划线查询、多表查询，聚合查询、分组查询、F查询、Q查询

Djago admin使用

1. 后台管理，方便我们快速的录入书籍
2. 使用方法：
第一步：在admin.py 中把要使用的表注册
from app01 import models
      admin.site.register(models.Book)
      admin.site.register(models.Publish)
      admin.site.register(models.Author)
      admin.site.register(models.AuthorDetail)

第二步：创建个超级管理员
      python3 manage.py createsuperuser 
            输入用户名，输入密码

第三步：登录
     http://127.0.0.1:8000/admin/
```python
#图书管理系统相关表设计
```python
 5个表
- 书籍表：id、书名、价格、出版日期
- 作者表：id、名字、年龄
- 作者详情表：id、电话、地址
- 出版社表：id、出版社名、地址、邮箱
- 书籍和作者表（多对多关系）

一对一的关系，关联字段可以写在任意一方
一对多的关系，关联字段写在多的一方
多对多的关系，必须建立第三张表（orm中，可以用一个字段表示，这个字段可以写在任意一方）

class Book(models.Model):
    title = models.CharField(verbose_name='书名', max_length=32)
    price = models.DecimalField(verbose_name='价格', max_digits=8, decimal_places=2)
    publish_time = models.DateField(verbose_name='出版日期',auto_now_add=True)
    # 一对多 外键字段建在多的一方
    publish = models.ForeignKey(to='Publish')
    # 多对多 外键字段推荐建在查询频率较高的表中
    authors = models.ManyToManyField(to='Author')


class Publish(models.Model):
    title = models.CharField(verbose_name='名称',max_length=32)
    addr = models.CharField(verbose_name='地址',max_length=128)
    email = models.EmailField(verbose_name='邮箱')


class Author(models.Model):
    name = models.CharField(verbose_name='姓名',max_length=32)
    age = models.IntegerField(verbose_name='年龄')
    # 一对一 外键字段推荐建在查询频率较高的表中
    author_detail = models.OneToOneField(to='AuthorDetail')


class AuthorDetail(models.Model):
    phone = models.BigIntegerField(verbose_name='电话')
    addr = models.CharField(verbose_name='地址',max_length=32)
    
"""
作者详情表
作者表
出版社表

书籍表和关系表通过orm实现
"""
# django 2.0版本需要加no_delete参数

on_delete=None,        # 删除关联表中的数据时,当前表与其关联的field的行为


on_delete=models.CASCADE,        # 删除关联数据,与之关联也删除


on_delete=models.DO_NOTHING,  # 删除关联数据,什么也不做


on_delete=models.PROTECT,         # 删除关联数据,引发错误ProtectedError


# models.ForeignKey('关联表', on_delete=models.SET_NULL, blank=True, null=True)
on_delete=models.SET_NULL,        # 删除关联数据,与之关联的值设置为null（前提FK字段需要设置为可空,一对一同理）


# models.ForeignKey('关联表', on_delete=models.SET_DEFAULT, default='默认值')
on_delete=models.SET_DEFAULT,  # 删除关联数据,与之关联的值设置为默认值（前提FK字段需要设置默认值,一对一同理）

ORM双划线查询(范围查询)

# 在查询字段加上下面的语法
__gt #大于
 models.Books.objects.filter(price__gt=700)

__lt  #小于
models.Books.objects.filter(price__lt=700)

__gte #大于等于
models.Books.objects.filter(price__gte=700)

__lte  #小于等于
models.Books.objects.filter(price__lte=700)

__in  #连接...要么
models.Books.objects.filter(price__in=[666.66,999.66,10000])

#python对数字不是很敏感 精确度不高  很多时候我们会采取字符串存储数字类型

__range #多少之间
models.Books.objects.filter(price__range=(500,800))

__contains #包含的字符串
models.Books.objects.filter(title__contains='s')
models.Books.objects.filter(title__icontains='s')  # 区分大小写

__year #按年查询
models.Books.objects.filter(publish_time__year=2021)

__month #按月查询
models.Books.objects.filter(publish_time__month=3)

ORM之多表查询

跨表查询理论

正向
    外键字段在谁那儿，谁查另外的人就是正向
反向
    没有外键字段
# 就是判断你是否有关联的外键字段

"""
正向查询按外键字段
反向查询按表名小写
            _set
"""
all()   #查询不止一个的时候加上all()

基于对象的跨表查询操作

# 子查询
"""
1.先查询出一个对象
2.基于对象点正反向字段
3.基于对象的跨表查
    -子查询，多次查询
"""

反向查询表明小写加_set
    查询的对象可能有多个的情况
查询的对象只有一个的情况不需要加
基于对象的跨表查询
    1.查询聊斋书籍对应的出版社名称
    book_obj = models.Book.objects.filter(title='聊斋').first()
    res = book_obj.publish
    print(res.title)

    2.查询神雕侠侣对应的作者
    book_obj = models.Book.objects.filter(title='神雕侠侣').first()
    # res = book_obj.authors  # app01.Author.None
    res = book_obj.authors.all()
    print(res)


    3.查询jason的地址
    author_obj = models.Author.objects.filter(name='jason').first()
    res = author_obj.author_detail
    print(res.phone,res.addr)

    4.查询东方出版社出版过的书籍
    publish_obj = models.Publish.objects.filter(title='东方出版社').first()
    # res = publish_obj.book_set  # app01.Book.None
    res = publish_obj.book_set.all()  # app01.Book.None
    print(res)

    5.查询jason写过的书
    author_obj = models.Author.objects.filter(name='jason').first()
    # res = author_obj.book_set  # app01.Book.None
    res = author_obj.book_set.all()
    print(res)

    6.查询电话是110的作者姓名
    author_detail_obj = models.AuthorDetail.objects.filter(phone=110).first()
    res = author_detail_obj.author
    print(res.name,res.age)

基于双下划线的跨表查询

基于双下划线的跨表查
    -多表连接查询
# 1.查询聊斋书籍对应的出版社名称
res = models.Book.objects.filter(title='聊斋').values('publish__title')
print(res.query)
# 2.查询神雕侠侣对应的作者名字和年龄
res = models.Book.objects.filter(title='神雕侠侣').values('authors__name','authors__age')
print(res)
# 3.查询jason的地址
res = models.Author.objects.filter(name='jason').values('author_detail__addr')
print(res)

# 1.查询聊斋书籍对应的出版社名称
res = models.Publish.objects.filter(book__title='聊斋').values('title')
print(res)
# 2.查询神雕侠侣对应的作者名字和年龄
res = models.Author.objects.filter(book__title='神雕侠侣').values('name','age')
print(res)
# 3.查询jason的地址
res = models.AuthorDetail.objects.filter(author__name='jason').values('addr')
print(res)

# 查询神雕侠侣对应的作者的电话和地址
res = models.Book.objects.filter(title='神雕侠侣').values('authors__author_detail__phone','authors__author_detail__addr')
print(res)

聚合查询

聚合函数，sum，max，min，count，avg
       总数，最大值，最小值，数量，平均值
把聚合结果字段重命名
res=models.Book.objects.all().aggregate(aaa=Sum('price'))

# tests.py
from django.db.models import Sum,Avg,Max,Min,Count
# 计算所有图书的平均价格
Countres=models.Book.objects.all().aggregate(Avg('price'))
print(res)
# 计算所有图书的最高价格
res=models.Book.objects.all().aggregate(Max('price'))
print(res)
# 计算所有图书的总价格
res=models.Book.objects.all().aggregate(Sum('price'))
print(res)
# 计算所有图书的总价格
res=models.Book.objects.all().aggregate(book_sum=Sum('price'),book_avg=Avg('price'))
print(res)
# 测试出版图书的总价格
res = models.Book.objects.filter(authors__name='测试').values('publish__book').aggregate(Sum('price'))
print(res)
# 北京出版社书的最高价格
res1 = models.Publish.objects.filter(name='北京出版社').values('book').aggregate(Max('book__price'))
print(res1)

aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句提供它。

分组查询

annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数，所以使用前要先从 django.db.models 引入 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 里写其别名。

• filter放在 annotate 前面：表示where条件

• filter放在annotate后面：表示having
  总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。

import os

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day53.settings")
if __name__ == '__main__':
    import django
    django.setup()
    from app01 import models
    # 查询每一个出版社id，以及出书平均价格(单表)
    # 原生sql
    # select publish_id,avg(price) from book group by publish_id;
    # orm实现
    '''
    把同一类归为一组，然后使用聚合函数操作
    如果是多表，把连个表连起来，再分组，再聚合
    取的字段必须是分组字段或者聚合函数的字段
    标准 annotate() 内写聚合函数
    values在前，表示group by 的字段
    values在后，表示取字段
    filter在前，表示where条件
    filter在后，表示having
    '''
from django.db.models import Avg,Count,Max

res = models.Book.objects.all().\
values('publish_id').\
annotate(price_ave=Avg('price')).values('publish_id','price_ave')
print(res)

# 查询出版社id大于1的出版社id，以及出书平均价格
res=models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(price_ave=Avg('price')).values('publish_id','price_ave')
print(res)

# 查询出版社id大于1的出版社id，以及出书平均价格大于30的
res=models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(price_ave=Avg('price')).filter(price_ave__gt=60).values('publish_id','price_ave')
print(res)

#查询每一个出版社出版的名称和书籍个数(连表)
# 联表的话最好以group by的表作为基表
res=models.Publish.objects.values('nid').annotate(book_count=Count('book__nid')).values('name','book_count')
# 简写成，如果基表是group by的表，就可以不写values
res=models.Publish.objects.annotate(book_count=Count('book')).values('name','book_count')

# 以book为基表
res=models.Book.objects.values('publish__nid').annotate(book_count=Count('nid')).values('publish__name','book_count')
print(res)

#查询每个作者的名字，以及出版过书籍的最高价格(建议使用分组的表作为基表)
# 多对多如果不以分组表作为基表，可能会出数据问题
res=models.Author.objects.annotate(price_max=Max('book__price')).values('name','price_max')
res=models.Book.objects.values('authors__nid').annotate(price_max=Max('price')).values('authors__name','price_max')
print(res)

#查询每一个书籍的名称，以及对应的作者个数
res=models.Book.objects.annotate(count=Count('authors')).values('name','count')
print(res)

##统计不止一个作者的图书
## 统计价格数大于10元，作者的图书
##统计价格数大于10元，作者个数大于1的图书   
res=models.Book.objects.filter(price__gt=10).annotate(count=Count('authors')).filter(count__gt=1).values('name','price','count')
print(res)

F查询

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

# F 查询,取出某个字段对应的值
    from django.db.models import F
  # 查询评论数大于阅读数的书籍
    res=models.Book.objects.filter(commit_num__gt=F('read_num'))
    print(res)

  # 把所有图书价格+1
    res=models.Book.objects.all().update(price=F('price')+1)
    print(res) # 影响的行数
    
  # 把mzk出版的所有图书价格加10
    res = models.Book.objects.filter(authors__name='mzk').update(price=F('price')+1)
    print(res)

Q查询

filter()等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如ORM语句），你可以使用Q对象。

# Q查询：构造出与&    或|   ^非~
from django.db.models import Q

# 查询名字叫红楼梦或者价格大于100的书
res=models.Book.objects.filter(name='红楼梦',price__gt=100)
res=models.Book.objects.filter(Q(name='红楼梦')|Q(price__gt=100))
res=models.Book.objects.filter(Q(name='红楼梦')& Q(price__gt=100))

# 查询名字不是红楼梦的书

res=models.Book.objects.filter(~Q(name='红楼梦'))
#查询名字不是红楼梦，并且价格大于100的书
res = models.Book.objects.filter(~Q(name='红楼梦'),price__gt='100')
res = models.Book.objects.filter(~Q(name='红楼梦')&Q(price__gt='100'))
print(res)

查询练习

查询练习

# 查找出版日期是2021年的书
res = models.Book.objects.filter(publish_date__year=2021)
print(res)
# 查找出版日期是2021年的书名
res = models.Book.objects.filter(publish_date__year=2021).values('title')
print(res)
# 查找价格大于10元的书
res = models.Book.objects.filter(price__gt=10)
print(res)
# 查找价格大于10元的书名和价格
res = models.Book.objects.filter(price__gt=10).values('title','price')
print(res)
# 查找在北京的出版社
res = models.Publish.objects.filter(addr='北京')
print(res)
# 查找年龄大于30岁的作者
res = models.Author.objects.filter(age__gt=30)
print(res)
# 查找手机号是155开头的作者
res = models.Author.objects.filter(author_detail__phone=155)
print(res)
# 查找手机号是155开头的作者的姓名和年龄
res = models.Author.objects.filter(author_detail__phone=155).values('name','age')
print(res)
# 查找书名是“红楼梦”的书的出版社
book_obj = models.Book.objects.filter(title='红楼梦').first()
res = book_obj.publish
print(res)
# 查找书名是“红楼梦”的书的出版社所在的城市
res = models.Book.objects.filter(title='红楼梦').values('publish__addr')
print(res)
# 查找书名是“红楼梦”的书的出版社的名称
res = models.Book.objects.filter(title='红楼梦').values('publish__name')
print(res)
# 查找书名是“红楼梦”的书的所有作者
res = models.Book.objects.filter(title='红楼梦').values('authors__name')
print(res)
# 查找书名是“红楼梦”的书的作者的年龄
res = models.Author.objects.filter(book__title='红楼梦').values('age')
print(res)
# 查找书名是“红楼梦”的书的作者的手机号码
res = models.Book.objects.filter(title='红楼梦').values('authors__author_detail__phone')
print(res)
res = models.AuthorDetail.objects.filter(author__book__title='红楼梦').values('phone')
print(res)
# 查找书名是“红楼梦”的书的作者的地址
res = models.Book.objects.filter(title='红楼梦').values('authors__author_detail__addr')
print(res)
res = models.AuthorDetail.objects.filter(author__book__title='红楼梦').values('addr')
print(res)
# 查找书名是“红楼梦”的书的出版社的邮箱
res = models.Publish.objects.filter(book__title='红楼梦').values('email')
print(res)

# 基于双下划线，查询红楼梦这本书出版社的名字
res = models.Book.objects.filter(title='红楼梦').values('publish__name')
print(res)
res = models.Publish.objects.filter(book__title='红楼梦').values('name')
print(res)
# 基于双下划线，查询egon的手机号
res = models.AuthorDetail.objects.filter(author__name='egon').values('phone')
print(res)
res = models.Author.objects.filter(name='egon').values('author_detail__phone')
print(res)
# 基于双下划线， 查询手机号以133开头的作者出版过的书籍名称以及书籍出版社名称
res = models.AuthorDetail.objects.filter(phone=133).values('author__book__title','author__book__publish__name')
print(res)
# 基于双下划线，查询城市是北京的出版社出版过的所有书籍
res = models.Publish.objects.filter(addr='北京').values('book__title')
print(res)
# 查询地址内有北京的作者出版所有书籍的平均价格
from django.db.models import Sum,Avg,Max,Min,Count,F,Q
res = models.Book.objects.filter(authors__author_detail__addr='北京').aggregate(Avg('price'))
print(res)
# 把egon出版的所有图书价格加10
res = models.Book.objects.filter(authors__name='egon').update(price=F('price')+1)
print(res)
#  查询名字叫红楼梦或价格大于50的书
res = models.Book.objects.filter(Q(title='红楼梦')|Q(price__gt=50))
print(res)
# 查询名字叫红楼梦和价格大于100  或者 id大于2
res = models.Book.objects.filter(Q(title='红楼梦') & Q(price__gt=100) | Q(pk__gt=2))
print(res)
# 查询名字不是红楼梦，并且价格大于100的书
res = models.Book.objects.filter(~Q(title='红楼梦'), price__gt=100)
print(res)
# 手机号以139开头的作者出版过的所有书籍名称以及出版社名称
res = models.Author.objects.filter(author_detail__phone=139).values('book__title','book__publish__name')
print(res)

原生sql

 from app01 import models
    res=models.Author.objects.all()
    for author in res:
         print(author.sex)
         print(author.get_sex_display())

    # 使用原生sql
    res=models.Author.objects.raw('select * from app01_author where nid>1')
    for author in res:
        print(author.name)

    res = models.Author.objects.raw('select * from app01_book where nid>1')
    for book in res:
        print(book.price)
        
 # 执行原生sql，跟对象类型无关了，查出什么字段，可以直接使用该字段