第六十天 正反向、聚合、分组、F与Q、ORM查询

昨日内容

查询常见关键字
1.filter()
2.all()
3.get()
4.first()
5.last()
6.values()
7.values_list()
8.order_by()
9.distinct()
10.exclude()
11.exists()
12.count()
13.reverse()
14.raw()

神奇的双下划线查询
__gt
__gte
__lt
__lte
__in
__range
__contains
__icontains
__year
__month
__day

一、今日内容概要

正反向查询进阶操作
聚合查询
分组查询
F与Q查询
ORM查询优化
ORM常见字段类型
ORM常见字段参数
ORM事务操作
多对多三种创建方式详解

二、正反向查询进阶操作

1.查询主键为1的书籍对应的出版社名称及书名
2.查询主键为3的书籍对应的作者姓名及书名
3.查询jason的作者的电话号码和地址
4.查询南方出版社出版的书籍名称和价格
5.查询jason写过的书的名称和日期
6.查询电话是110的作者姓名和年龄
7.查询主键为1的书籍对应的作者电话号码
"""
一般来说：查询主键为1的书籍对应的出版社名称及书名会先去书籍表中查找书籍，但是我们这里规定优先不使用书籍表
"""

1.查询主键为1的书籍对应的出版社名称及书名(出版社查书为反向)
res = models.Publish.objects.filter(book__pk=1).values('name','book__title')
"""
.filter(book__pk=1)筛选条件为书的表中主键为1的字段
总结来说意思就是：查出版社，出版了书籍主键为1的出版社
"""

2.查询主键为3的书籍对应的作者姓名及书名
res = models.Author.objects.filter(book__pk=3).values('name', 'book__title')

3.查询jason的作者的电话号码和地址
res = models.AuthorDetail.objects.filter(author__name='jason').values('addr', 'phone')

4.查询南方出版社出版的书籍名称和价格
res = models.Book.objects.filter(publish__name='南方出版社').values('price', 'title')

5.查询jason写过的书的名称和日期
res = models.Book.objects.filter(author__name='jason').values('title', 'publish_time')

6.查询电话是110的作者姓名和年龄
res = models.Author.objects.filter(author_detail__phone=110).values('name', 'age')

7.查询主键为1的书籍对应的作者电话号码
查询方法之一：将作者作为主要要查询的字段
res = models.Author.objects.filter(book__pk=1).values('author_detail__phone')
查询方法之二：将作者详情表作为主要要查询的字段（重要）
res = models.AuthorDetail.objects.filter(author__book__pk=1).values('phone')
# 从作者详情表跳到作者表，再利用作者表的主键调到书籍表中，再查找书籍表中主键为1的书籍

三、聚合查询

聚合函数:max、min、sum、avg、count
'''聚合查询'''
from django.db.models import Max, Min, Sum, Avg, Count
没有分组之前如果单纯的时候聚合函数 需要关键字aggregate
aggregate:合计;总计
res = models.Book.objects.aggregate(Max('price'), Min('price'), Sum('price'), Avg('price'), Count('pk'))
print(res)

截至目前为止django我们所使用到的模块

from django.shortcuts import render, HttpResponse, redirect  # 来自小白必备三板斧
from django.db.models import Max, Min, Sum, Avg, Count  # 聚合查询
from django.urls import path, re_path  # re_path正则匹配
from django.http import JsonResponse  # 视图函数返回值

mysql分组查询知识回顾

show variables like '%mode%';
"""
分组有一个特性 默认只能够直接获取分组的字段 其他字段需要使用方法
我们也可以忽略掉该特性 将sql_mode中only_full_group_by配置移除即可
"""
其中有个sql_mode的限制 ONLY_FULL_GROUP_BY
set global sql_mode = 'STRICT_TRANS_TABLES'

四、分组查询

 示例1:统计每一本书的作者个数
res = models.Book.objects.annotate(book_num=Count('author__pk')).values('title', 'book_num')
print(res)
"""
1.假如我们没有进行上面的设置values('title', 'res')就无法进行显示，因为分组原来只能显示被分组的字段
2.这里可以理解为，要是想取用字段，就得使用 '字段名'
3.这里的按记录进行分组并不同于mysql中的按字段进行分组，当然这里也有按字段分组的方法
"""
1.按照整条数据分组
models.Book.objects.annotate()  按照一条条书籍记录分组
2.按照表中某个字段分组()
models.Book.objects.values('title').annotate()  按照annotate之前values括号中指定的字段分组

示例2:按照publish字段对图书表中的图书进行分组
res = models.Book.objects.values('publish_id').annotate(book_num=Count('pk')).values('publish_id','book_num')

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

示例4：统计不止一个作者的图书
res = models.Book.objects.annotate(author_num=Count('author__pk')).filter(author_num__gt=1).values('title','author_num')
print(res)
# 这道题的重点在于利用filter(author_num__gt=1)进行筛选出大于1的数!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

示例5：查询各个作者出的书的总价格
res = models.Author.objects.annotate(total_price=Sum('book__price')).values('name', 'total_price')
print(res)

五、F查询

# 首先我们想在原有的书籍表的基础上加上库存和卖出字段，先去修改书籍列
class Book(models.Model):
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=8, decimal_places=2)
    publish_time = models.DateTimeField(auto_now=True)

    publish = models.ForeignKey(to='Publish', on_delete=models.CASCADE)
    author = models.ManyToManyField(to='Author')

    storage_num = models.IntegerField(verbose_name='库存数')
    sale_num = models.IntegerField(verbose_name='卖出数')

    def __str__(self):
        return '书籍对象:%s' % self.title
# 解决方法
1.允许字段的值为空
2.直接进行默认值的设置
3.在makemigrations命令中选1进行默认值的填入
storage_num = models.IntegerField(verbose_name='库存数', null=True)
sale_num = models.IntegerField(verbose_name='卖出数', default=1000)


'''F查询:查询条件不是自定义的而是来自于表中其他字段!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'''
from django.db.models import F

需求1.查询库存数大于卖出数的书籍
我们首先想到的是利用fliter中的storage_num__gt
res = models.Book.objects.filter(storage_num__gt=sale_num)
这里就直接报错了，思考如何使用其他的字段引入F查询
res = models.Book.objects.filter(storage_num__gt=F('sale_num'))
# 注意因为这里用F调用字段了，所以得加引号

需求2.将所有书籍的价格上涨1000块
res = models.Book.objects.update(price=F('price')+1000)
# F可以对数字类型的字段直接使用加减法

需求3.将所有书籍名称加上爆款后缀
from django.db.models import F
from django.db.models.functions import Concat
from django.db.models import Value
res = models.Book.objects.update(title=Concat(F('title'), Value('爆款')))
"""
F虽然可以直接调用自己的字段，但是想要进行文本的修改还需要
from django.db.models.functions import Concat    Concat用于连接文本，类似于上面的+
from django.db.models import Value  Value用于接收文本
"""

六、Q查询

'''Q查询:可以改变filter括号内多个条件之间的逻辑运算符'''
我们在使用filter进行查询的时候，默认条件之间是and，我们这里想要条件之间的关系为or或者其他
from django.db.models import Q
res = models.Book.objects.filter(price=41000, publish_id=2)  # 等同于and
res = models.Book.objects.filter(Q(price=41000), Q(publish_id=2))  # 等同于and
res = models.Book.objects.filter(Q(price=41000) | Q(publish_id=2))  # 等同于or
res = models.Book.objects.filter(~Q(price=41000) | Q(publish_id=2))  # ~等同于not

'''Q查询:还可以将查询条件的字段改为字符串形式'''
res = models.Book.objects.filter(price=41000)
# 这里这个方法很重要注意上面查询的方式使用的的就不是字符串，我们的目标是使用res = models.Book.objects.filter('price'=41000)进行查询
先创建一个Q对象，并且向对象里面添加条件,之后就可以利用对象进行查找
from django.db.models import Q
q_obj = Q()
q_obj.connector = 'or'  # 这里默认为and可以自行修改为or
q_obj.children.append(('price', 41000))
q_obj.children.append(('pk', 1))
q_obj.children.append(('publish_id', 3))
res = models.Book.objects.filter(q_obj)
"""
这个功能和反射一样很重要的！！！！！！！！！！！！！！！
因为是使用字符串，所以所有变量名都得是字符串
记忆方法，在chlidren后面进行append添加条件，整个条件由很多元组组成，注意后面的条件为元组！！！！！！！！
connector 连接器的意思
"""

七、ORM查询优化（only，defer，select_related, perfetch_related）

django orm默认都是惰性查询
	当orm的语句在后续的代码中真正需要使用的时候才会执行
django orm自带limit分页
	减轻数据库端以及服务端的压力
# 注意一定要尽量减少数据库的查询次数，次数太多数据库会炸的
在setting文件的末尾加上可以看见mysql查询码的东n
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}
——————————————————————————————————————————————————
res = models.Book.objects.all()  # 在没有print的时候你会发现数据库是没有被查询的
print(res)  # 直到运行到这里数据库才被查询


1、我们想取得全部字段的数据
from app01 import models
    res = models.Book.objects.all()
    for obj in res:
        print(obj.title)
		print(obj.price)
		print(obj.publish_time)
# 这里你会发现数据库仅仅被查询了一次


2、我们这里想要获取数据，且只获取'title','price'字段数据
res = models.Book.objects.values('title','price')
    for obj in res:
        print(obj.get('title'))  # 因为values的输出结果为列表套字典，所以得用get取值


3、我们想要直接能点出数据的方法(only)
res = models.Book.objects.only('title','price')
    for obj in res:
        print(obj.title)
        print(obj.price)
"""
1.only会将括号内填写的字段封装成一个个数据对象 对象在点击的时候不会再走数据库查询
2.但是对象也可以点击括号内没有的字段 只不过每次都会走数据库查询
3.所以千万得注意，当别人使用only写法的时候千万别查其他字段的数据，数据库会炸的！！！！！！！！！！！
"""

4、我们想要与上面only取反的数据集(defer)
res = models.Book.objects.defer('title','price')
    for obj in res:
        print(obj.publish_time)
"""
defer与only刚好相反
	数据对象点击括号内出现的字段 每次都会走数据库查询
	数据对象点击括号内没有的字典 不会走数据库查询
"""

5、我们想要进行跨表获取数据
res = models.Book.objects.all()
for obj in res:
	print(obj.publish.name)
"""
这里也是支持进行跨表查询的，这里是基于对象的查询
但是基于上面的这种方法，每查询一次就得访问一次数据库
"""

6、我们使用优化后的方法，这种方法可以自动将两表合并，之后利用合并后的表进行查询(select_related)
res = models.Book.objects.select_related('publish')
for obj in res:
	print(obj.publish.name)
"""
1.select_related将Book与publish进行并表操作
2.select_related括号内只能接收外键字段(一对多 一对一) 自动连表 得出的数据对象在点击表中数据的时候都不会再走数据库查询，注意多对多就无法查询了
3.还需要注意的是，括号里面只能填外键字段，在创建表格的时候book的foreignkey就是publish！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
"""

7、上面是连表查询，这里我们想要使用子查询
res = models.Book.objects.prefetch_related('publish')
for obj in res:
    print(obj.publish.name)
"""
prefetch_related底层其实是子查询 将查询之后的结果也一次性封装到数据对象中 用户在使用的时候是感觉不出来的
也就是下面两条sql语句
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`publish_time`, `app01_book`.`publish_id`, `app01_book`.`storage_num`, `app01_book`.`sale_num` FROM `app01_book`; args=(); alias=default
(0.001) SELECT `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`email` FROM `app01_publish` WHERE `app01_publish`.`id` IN (1, 2, 3); args=(1, 2, 3); alias=default
"""

8、select_related和prefetch_related本质上的差距也就是子查询与连表查询的区别
连表在两个表格数据过大的时候，子查询两句查询的速度优于连表
当两个表格数据量不大的时候，连表查询会更快

事务回顾

事务的最终目的就是想让几件事同时执行比如银行卡转账
事务的四大特性(重点)
ACID
A:原子性（Atomicity）
	多条SQL语句要么一起执行，要么一起失败
	一个事务是一个不可分割的整体 里面的操作要么都成立要么都不成立
C:一致性（Consistency）
	事务必须使数据库从一个一致性状态变到另外一个一致性状态与原子性相关联
I:隔离性（Isolation）
	并发编程中 多个事务之间是相互隔离的 不会彼此干扰 独立
D:持久性（Durability）(永久性)
	事务一旦提交 产生的结果应该是永久的 不可逆的

事务处理中有几个关键词汇会反复出现
事务(transaction) start transcation
回退(rollback)
提交(commit)
保留点(savepoint) 类似于设置检查点  # 很少用，可以回退到某个地方
# 在没有进行commit前其实数据都默认存储在内存当中

1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
'''
打个比方就是进行银行转账事务的时候，表格已经完成修改，但是没有使用commit进行确认
这个时候有别的事务来读取表格，读取到的是修改以后的数据，即使没有进行commit的确认
'''

2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
'''
与上面的不同之处在于，在未进行commit之前，不管事务来先后读取，读取到的内容都是一致的
'''

3.repeatable read(可重复读)		# MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行
'''
1.意思就是运行过程中发现数据被改了
2.InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
'''

八、事务操作

事务:ACID
事务隔离级别:脏读 幻读 不可重复读 ...
原生SQL: start transaction\rollback\commit\savepoint 

from django.db import transaction
try:
    with transaction.atomic():
        pass  # 多条ORM语句
except Exception as e:
        print(e)
"""
这段话没什么好解释的也就是开启了一个原子性的事务
下面的也就是异常捕获
剩下的操作自行了解即可
"""

九、模型层常见字段

AutoField()  int自增，必须填入primary_key=True
CharField()  默认对应的varchar，可以自己定义为char
IntegerField()
BigIntegerField()
DateField()
DateTimeField()
DecimalField()
EmailField()
BooleanField()
	传布尔值存数字0或1
TextField()
	存储大段文本
FileField()
	存储文件数据  自动找指定位置存储 字段存具体路径
	upload_to="" 上传默认路径
ForeignKey()
OneToOneField()
ManyToManyField()

ORM还支持自定义字段
class MyCharField(models.Field):
    def __init__(self, max_length, *args, **kwargs):
        self.max_length = max_length
        super().__init__(max_length=max_length, *args, **kwargs)

    def db_type(self, connection):
        return 'char(%s)' % self.max_length
info = MyCharField(max_length=32)

十、ORM常见字段参数

max_length
verboses_name   备注名称
auto_now
auto_now_add
null
default
max_digits
decimal_places
unique=True    字段是否唯一
db_index=True    为字段添加索引进行查询优化
choices
class UserInfo(models.Model):
	username = models.CharField(max_length=32)
	gender_choice = (
	(1, '男性'),
	(2, '女性'),
	(3, '其他'),
	)
	gender = models.IntegerField(choices=gender_choice)
# 进行数据获取
res = models.Userinfo.objects.filter(pk=1).first()
print(res.gender)  # 数字数据
print(res.get_gender_display())  # 定义的数据
"""
1.利用大元组套小元组来创建数据
2.在IntegerField中使用，将数据绑定给变量choices
3.基于点对象的查询一定要在后面加上.first()!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
4.res.get_gender_display()获取定义的数据
"""


to
to_field
related_name   正反向查询的时候起别名用的
on_delete
1、models.CASCADE
	级联操作，当主表中被连接的一条数据删除时，从表中所有与之关联的数据同时被删除
2、models.SET_NULL
	当主表中的一行数据删除时，从表中所有与之关联的数据的相关字段设置为null,此时注意定义外键时，这个字段必须可以允许为空
3、models.PROTECT
	当主表中的一行数据删除时，由于从表中相关字段是受保护的外键，所以都不允许删除
4、models.SET_DEFAULT
	当主表中的一行数据删除时，从表中所有相关的数据的关联字段设置为默认值，此时注意定义外键时，这个外键字段应该有一个默认值
5、models.SET()
	当主表中的一条数据删除时，从表中所有的关联数据字段设置为SET()中设置的值，与models.SET_DEFAULT相似，只不过此时从表中的相关字段不需要设置default参数
6、models.DO_NOTHING
	什么都不做，一切都看数据库级别的约束，注数据库级别的默认约束为RESTRICT,这个约束与django中models.PROTECT相似

十一、多对多三种创建方式

1.自动创建
	authors = models.ManyToManyField(to='Author')
	优点:第三张表自动创建
 	缺点:第三张表扩展性差
2.手动创建
class Book(models.Model):
	pass
class Author(models.Model):
	pass
class Book2Author(models.Model):
	book_id = models.ForeignKey(to="Book")
	author_id = models.ForeignKey(to="Author")
优点:第三张表扩展性强，自己想加字段就加不上去
缺点:无法使用正反向查询以及多对多四个方法
3.半自动创建
class Book(models.Model):
	authors = models.ManyToManyField(to='Author,
													through='Book2Author',
													through_fields=('book_id','author_id')
													')
class Author(models.Model):
	pass
class Book2Author(models.Model):
	book_id = models.ForeignKey(to="Book")
	author_id = models.ForeignKey(to="Author")
"""
1.先像第二步一样，自己手动创建第三张表以及其外键字段
2.在第一张表中建立和作者的多对多字段，并且说明是通过第三张表建立的
3.说明通过第三张表中的哪两个字段，注意因为外键字段建立在book表中，所以顺序为'book_id','author_id'
4.上面的顺序一定是外键字段在哪，哪个表就在前面！！！！！！！！！
"""
优点:扩展性强并且支持正反向查询
缺点:无法使用多对多四个方法