05--基于ORM的关于数据库的单表操作 跨表操作 聚合查询 分组查询 F查询 Q查询

Django测试环境搭建:#

使用该方式不需要频繁的走web请求测试数据库操作语句：#

import os
import sys

if __name__ == "__main__":
# django在启动的时候 就会往全局的大字典中设置一个键值对  值是暴露给用户的配置文件的路径字符串
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day54.settings")

    import django
    django.setup()
    from app01 import models

单表13条数据库操作语句：#

"""
    pk会自动查找到当前数据的主键字段
"""
# <1> all(): 查询所有结果
# <2> filter(**kwargs): 它包含了与所给筛选条件相匹配的对象
# <3> get(**kwargs): 返回与所给筛选条件相匹配的对象，返回结果有且只有一个，如果符合筛选条件的对象超过一个或者没有都会抛出错误。

# <4> exclude(**kwargs): 它包含了与所给筛选条件不匹配的对象
    models.Book.objects.exclude(pk=1)   # 只要pk不是1的数据全部查询出来

# <5> values(*field): 返回一个ValueQuerySet——一个特殊的QuerySet，运行后得到的并不是一系列model的实例化对象，而是一个可迭代的字典序列
models.Book.objects.values('title','price')  #  列表套字典

# <6> values_list(*field): 它与values()非常相似，它返回的是一个元组序列，values返回的是一个字典序列
models.Book.objects.values_list('title','price')  #  列表套元组

# <7> order_by(*field): 对查询结果排序
models.Book.objects.order_by('price')   # 默认是升序
models.Book.objects.order_by('-price')  # 加负号就是降序

# <8> reverse(): 对查询结果反向排序，请注意reverse()通常只能在具有已定义顺序的QuerySet上调用(在model类的Meta中指定ordering或调用order_by()方法)。
models.Book.objects.order_by('price').reverse()  # 前面要先有排序才能反向

# <9> distinct(): 从返回结果中剔除重复纪录
models.Book.objects.values('title','price','create_time').distinct()
# 要完全一致的数据才可以去重

# <10> count(): 返回数据库中匹配查询(QuerySet)的对象数量。
models.Book.objects.count()

# <11> first(): 返回第一条记录
models.Book.objects.all().first()
# <12> last(): 返回最后一条记录
models.Book.objects.all().last()

# <13> exists(): 如果QuerySet包含数据，就返回True，否则返回False
models.Book.objects.filter(pk=1000).exists()

"""
返回QuerySet对象的方法有:
    all()、filter()、exclude()、order_by()、reverse()、distinct()

特殊的QuerySet：
values()       返回一个可迭代的字典列表
values_list() 返回一个可迭代的元祖列表

返回具体对象的：
get()、first()、last()

返回布尔值的方法有：
exists()

返回数字的方法有
count()
"""

常用的双下划线查询：#

"""单表操作的双下滑查询"""
# 查询价格大于200的书籍
res = models.Book.objects.filter(price__gt=200)

# 查询价格小于200的书籍
res = models.Book.objects.filter(price__lt=200)

# 查询价格大于等于200.22的书籍
res = models.Book.objects.filter(price__gte=200.22)

# 查询价格小于等于200.22的书籍
res = models.Book.objects.filter(price__lte=200.22)

# 查询价格要么是200,要么是300,要么是666.66
res = models.Book.objects.filter(price__in=[200,300,666.66])

# 查询价格在200到800之间的
res = models.Book.objects.filter(price__range=(200,800))  # 两边都包含

# 查询书籍名字中包含p的
res = models.Book.objects.filter(title__contains='p')

# 查询书籍是以  ...开头的  ...结尾
res = models.Book.objects.filter(title__startswith='三')
res1 = models.Book.objects.filter(title__endswith='p')
    
# 查询出版日期是2017的年(******)        
res=models.Book.objects.filter(create_time__year='2017')

在使用ORM进行数据库操作时，在django终端展示原生sql语句：#

settings.py中配置：

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}

建表：#

表结构:#

from django.db import models

# Create your models here.
class Book(models.Model):
    title = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=8,decimal_places=2)
    publish_date = models.DateField(auto_now_add=True)

    # 库存数
    kucun = models.IntegerField(null=True)
    # 卖出数
    maichu = models.IntegerField(null=True)

    publish = models.ForeignKey(to='Publish')  # 默认是跟publish的主键字段做的一对多外键关联
    authors = models.ManyToManyField(to='Author')
    # 虚拟字段      1.自动创建第三张表    2.帮助orm跨表查询

    def __str__(self):
        return self.title

class Publish(models.Model):
    name = models.CharField(max_length=32)
    addr = models.CharField(max_length=32)
    # email = models.EmailField()  # 就是varchar(254)

    def __str__(self):
        return self.name

class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    author_detail = models.OneToOneField(to='AuthorDetail')

    def __str__(self):
        return self.name


class AuthorDetail(models.Model):
    phone = models.BigIntegerField()
    addr = models.CharField(max_length=64)
    """
    models.py中的模型类__str__方法 必须返回一个字符串形式数据!!!
    
    """
    def __str__(self):
        return self.addr

添加外键:#

#  一对多关联
publish = models.ForeignKey(to='Publish')

#  多对多关联
authors = models.ManyToManyField(to='Author')

#  一对一关联：
author_detail = models.OneToOneField(to='AuthorDetail')

一对多字段的增删改查：#

#  增：

# 方式一：publish_id传数字
models.Book.objects.create(title='三国演义',price=189.99,publish_id=1)

# 方式二：publish直接传出版社对象
# publish_obj = models.Publish.objects.filter(pk=2).first()
# models.Book.objects.create(title='红楼梦',price=999.99,publish=publish_obj)

#  改：

# 方式一:传数字的
models.Book.objects.filter(pk=1).update(publish_id=3)

# 方式二：传对象的
publish_obj = models.Publish.objects.filter(pk=2).first()
models.Book.objects.filter(pk=1).update(publish=publish_obj)

# 删：
models.Publish.objects.filter(pk=2).delete()   #   默认都是级联更新 级联删除

多对多字段的增删改查：#

#增：
# 给主键为1的书籍添加两个作者

#方式一：传数字
book_obj = models.Book.objects.filter(pk=1).first()
# 对象点击多对多虚拟字段 会直接跨到多对多的第三张表
print(book_obj.authors) 
book_obj.authors.add(1)
book_obj.authors.add(2,3)

# 方式二：传对象
author_obj = models.Author.objects.filter(pk=1).first()
author_obj1 = models.Author.objects.filter(pk=2).first()
author_obj2 = models.Author.objects.filter(pk=3).first()
book_obj.authors.add(author_obj)
book_obj.authors.add(author_obj1,author_obj2)

"""
add()：是给书籍添加作者 括号内既可以传数字也可以传对象并且支持一次性传多个,逗号隔开即可
"""

#改：
"""
set()括号内 需要传一个可迭代对象,可迭代对象中 可以是多个数字组合
也可以是多个对象组合,但是不要混合使用。
"""
# 将主键为1的书籍对象 作者修改为2,3

方式一：传数字
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.authors.set([2,])
book_obj.authors.set([2,3])

方式二：传对象
author_obj = models.Author.objects.filter(pk=1).first()
author_obj1 = models.Author.objects.filter(pk=2).first()
author_obj2 = models.Author.objects.filter(pk=3).first()
book_obj.authors.set([author_obj,])
book_obj.authors.set([author_obj, author_obj1, author_obj2])

#删:
"""
remove()括号内既可以传数字 也可以传对象,并且支持传对个 逗号隔开即可
"""

# 方式一:传数字
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.authors.remove(3)
book_obj.authors.remove(1,2)

# 方式二:传对象
author_obj = models.Author.objects.filter(pk=1).first()
author_obj1 = models.Author.objects.filter(pk=2).first()
author_obj2 = models.Author.objects.filter(pk=3).first()
book_obj.authors.remove(author_obj)
book_obj.authors.remove(author_obj1,author_obj2)

#清空操作：
# 将某本书跟作者的关系全部清空
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.authors.clear() # 清空当前书籍与作者的所有关系

跨表查询：#

正向与反向的概念：#

# 正向：有关联字段的一方——>没有关联字段的一方，反之则是方向。

查询规则：#

"""
正向查询按外键字段
反向查询按表名小写

基于对象的跨表查询
(子查询:将一张表的查询结果,当做另外一个查询语句的条件)

当你反向查询的结果是多个的时候,就需要加_set,否则直接表名小写即可
"""

　　# 1.查询书籍id是1 的出版社名称
    book_obj = models.Book.objects.filter(pk=1).first()
    print(book_obj.publish.name)
    print(book_obj.publish.addr)

    # 2.查询书籍id是2 的作者姓名
    book_obj = models.Book.objects.filter(pk=2).first()
    print(book_obj.authors)  # app01.Author.None
    print(book_obj.authors.all())
    res = book_obj.authors.all()
    for r in res:
        print(r.name)

    # 3.查询作者是jason的家庭住址
    author_obj = models.Author.objects.filter(name='jason').first()
    print(author_obj.author_detail.addr)

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

    # 5.查询作者是jason的写过的所有的书籍
    author_obj = models.Author.objects.filter(name='jason').first()
    print(author_obj.book_set)  # app01.Book.None
    print(author_obj.book_set.all())

    # 6.查询电话号码是130的作者姓名
    author_detail_obj = models.AuthorDetail.objects.filter(phone=130).first()
    print(author_detail_obj.author.name)
    print(author_detail_obj.author.age)

　　# 7.查询书籍id为1 的作者的电话号码
　　book_obj = models.Book.objects.filter(pk=1).first()
　　author_list = book_obj.authors.all()
　　for author_obj in author_list:
　　print(author_obj.author_detail.phone)

连表查询：#

"""
基于双下划线的跨表查询(连表操作):
    left join
    inner join
    right join
    union
"""

 

# 正向
    # 查询jason作者的手机号
    res = models.Author.objects.filter(name='jason').values('author_detail__phone','author_detail__addr')
    res1 = models.AuthorDetail.objects.filter(author__name='jason').values('phone')

    # 查询jason这个作者的年龄和手机号
    # 正向
    res = models.Author.objects.filter(name='jason').values('age','author_detail__phone')
    # 反向
    res1 = models.AuthorDetail.objects.filter(author__name='jason').values('phone','author__age')

    # 查询手机号是130的作者年龄
    # 正向
    res = models.AuthorDetail.objects.filter(phone=130).values('author__age')
    # 反向
    res1 = models.Author.objects.filter(author_detail__phone=130).values('age')

    # 查询书籍id是1 的作者的电话号码
    res = models.Book.objects.filter(pk=1).values('authors__author_detail__phone')
    res1 = models.Book.objects.filter(pk=1).values('外键字段1__外键字段2__外键字段3__普通字段')
　　 """只要表里面有外键字段 你就可以无限制跨多张表"""

    # 查询出版社为北方出版社的所有图书的名字和价格
    res = models.Publish.objects.filter(name='北方出版社').values('book__title','book__price')

    # 查询北方出版社出版的价格大于19的书
    res = models.Book.objects.filter(price__gt=19,publish__name='北方出版社').values('title','publish__name')

聚合查询：#

# 聚合查询(aggregate)
    # 1.统计所有书的总价格
    from django.db.models import Max,Min,Count,Avg,Sum
    
    res = models.Book.objects.aggregate(Sum('price'))
    res1 = models.Book.objects.aggregate(Avg('price'))
    res2 = models.Book.objects.aggregate(Count('price'))
    res3 = models.Book.objects.aggregate(Max('price'))
    res4 = models.Book.objects.aggregate(Min('price'))
    res5 = models.Book.objects.aggregate(Max('price'),Min('price'),Count('pk'),Avg('price'),Sum('price'))

分组查询：#

    # 统计每一本书的作者个数
    from django.db.models import Max, Min, Count, Avg, Sum
    res = models.Book.objects.annotate(author_num = Count('authors')).values('author_num','title')

    # 统计出每个出版社卖的最便宜的书的价格
    res = models.Publish.objects.annotate(mmp = Min('book__price')).values('name','mmp')

    # 统计不止一个作者的图书
    res = models.Book.objects.annotate(author_num=Count('authors')).filter(author_num__gt=1)

    """
    只要是queryset对象 就可以无限制的调用queryset对象的方法!!!
    最最常用的就是对一个已经filter过滤完的数据 再进行更细化的筛选
    
    """

    # 查询各个作者出的书的总价格
    res = models.Author.objects.annotate(sp=Sum('book__price')).values('name','sp')

F查询：#

    #F查询的本质就是从数据库中获取某个字段的值
    # 查询库存数大于卖出数的书籍
    """之前查询等号后面的条件都是我们认为输入的
        现在变成了需要从数据库中获取数据放在等号后面
    """
    from django.db.models import F
    res = models.Book.objects.filter(kucun__gt=F('maichu'))

    # 将书籍库存数全部增加1000
    models.Book.objects.update(kucun=F('kucun')+1000)

    # 把所有书名后面加上'新款'
    from django.db.models.functions import Concat
    from django.db.models import Value
    ret3 = models.Book.objects.update(title=Concat(F('title'), Value('新款')))
    models.Book.objects.update(title = F('title')+'新款')  # 不能这么写

Q查询：#

    # Q查询
    from django.db.models import Q
    # 查询书籍名称是三国演义或者价格是444.44
    res = models.Book.objects.filter(title='三国演义',price=444.44)  # filter只支持and关系
    res1 = models.Book.objects.filter(Q(title='三国演义'),Q(price=444))  # 如果用逗号 那么还是and关系
    res2 = models.Book.objects.filter(Q(title='三国演义')|Q(price=444))
    res3 = models.Book.objects.filter(~Q(title='三国演义')|Q(price=444))

Q查询进阶：#

# Q高级用法
    q = Q()
    q.connector = 'or'  # 修改查询条件的关系   默认是and
    q.children.append(('title__contains','三国演义'))  # 往列表中添加筛选条件
    q.children.append(('price__gt',444))  # 往列表中添加筛选条件
    res = models.Book.objects.filter(q)  # filter支持你直接传q对象  但是默认还是and关系