class Book(models.Model):
    name = models.CharField(max_length=60, verbose_name='圖書(shū)名')
    # 浮點(diǎn)數(shù)字段，max_digits表示數(shù)字共8位， decimal_place表示小數(shù)點(diǎn)占2位
    price = models.DecimalField(max_digits=8, decimal_places=2, verbose_name='圖書(shū)價(jià)格')
    inventory_num = models.IntegerField(verbose_name='庫(kù)存數(shù)量')
    sell_num = models.IntegerField(verbose_name='賣(mài)出數(shù)量')
    # 一對(duì)多關(guān)系外鍵字段創(chuàng)建使用 ForeigenKey(to='表名')，默認(rèn)關(guān)聯(lián)主鍵字段, db_constraint=Flase表示不建立實(shí)質(zhì)的外鍵關(guān)系，只是邏輯上的關(guān)系
    publish = models.ForeignKey(to='Publish', on_delete=models.DO_NOTHING, db_constraint=False, verbose_name='外鍵關(guān)聯(lián)出版社')
    # 多對(duì)多關(guān)系，使用ManyToManyField(to='表名')，author是一個(gè)虛擬的字段，主要是用來(lái)告訴ORM，書(shū)籍和作者是多對(duì)多的關(guān)系，而且ORM會(huì)自動(dòng)創(chuàng)建多對(duì)多關(guān)系的第三張
    author = models.ManyToManyField(to='Author', on_delete=models.DO_NOTHING， db_constraint=False, verbose_name='外鍵關(guān)聯(lián)作者')


class Publish(models.Model):
    name = models.CharField(max_length=12, verbose_name='出版社名稱')


class Author(models.Model):
    name = models.CharField(max_length=10, verbose_name='作者名稱')
    # 一對(duì)一關(guān)系使用OneToOneField(to='表名')
    author_detail = models.OneToOneField(to='AuthorDetail', on_delete=models.DO_NOTHING, db_constraint=False, verbose_name='外間關(guān)聯(lián)作者詳情')


class AuthorDetail(models.Model):
    age = models.IntegerField(verbose_name='年齡')
    phone = models.CharField(max_length=11, verbose_name='手機(jī)號(hào)')

# 全自動(dòng)創(chuàng)建 - ManyToManyField，一般這種方式可以滿足需求
'''
使用全自動(dòng)創(chuàng)建多對(duì)多關(guān)系的優(yōu)點(diǎn)就是無(wú)需手動(dòng)創(chuàng)建第三張表，非常方便，django ORM直接提供操作第三張表關(guān)系的方法
缺點(diǎn)就是無(wú)法擴(kuò)展第三張關(guān)系表
'''
class Book(models.Model):
 name = models.CharField(max_length=32)
    authors = models.ManyToManyField(to='Author')
    
class Author(models.Model):
    name = models.CharField(max_length=32)
    
    
    
# 純手動(dòng)創(chuàng)建 -  ForeignKey手動(dòng)創(chuàng)建第三張表
'''
第三張表完全取決于手動(dòng)的擴(kuò)展，但是需要寫(xiě)的代碼較多，而且無(wú)法使用ORM提供的簡(jiǎn)單方法
'''
class Book(models.Model):
    name = models.CharField(max_length=32)
    
class Author(models.Model):
 name = models.CharField(max_length=32)
 
class Book2Author(models.Model):
    book_id = models.ForeignKey(to='Book')
    author_id = models.ForeignKey(to='Author')
    
# 半自動(dòng)創(chuàng)建，通過(guò)ManyToManyField的參數(shù)控制第三張表
class Book(models.Model):
    name = models.CharField(max_length=32)
    authors = models.ManyToManyField(
        to='Author', # 告訴ORM不需要自動(dòng)幫忙創(chuàng)建第三張關(guān)系表
        through='Book2Author', # 告訴ORM第三張關(guān)系表對(duì)應(yīng)的表的外鍵字段
        through_fields=('book','author')  # 通過(guò)哪兩個(gè)字段關(guān)聯(lián)表，當(dāng)前在哪張表，就先寫(xiě)哪個(gè)表的關(guān)聯(lián)字段
                                     )
class Author(models.Model):
    name = models.CharField(max_length=32) 
    
class Book2Author(models.Model):
    book = models.ForeignKey(to='Book')
    author = models.ForeignKey(to='Author')

# 方式1：通過(guò)實(shí)際字段
book_obj = models.Book.objects.create(name='哈利波特', price=10.2, publish_id=1)

# 方式2，先獲取出版社對(duì)象，再將書(shū)籍和出版社通過(guò)出版社對(duì)象進(jìn)行關(guān)聯(lián)
publis_obj = models.Publish.objects.filter(pk=1).first()
book_obj = models.Book.objects.create(name='哈利波特', price=10.2, publish=publis_obj)

models.Publish.objects.filter(pk=1).delete()

# 方式1
models.Book.objects.filter(pk=1).update(publish_id=1)

# 方式2
pub_obj = models.Publish.objects.filter(pk=2).first()
models.Book.objects.filter(pk=1).update(publish=pub_obj)

# 方式1，直接添加id值
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.add(1)  # 增加 1 1記錄，即id為1的書(shū)綁定id為1的作者
book_obj.author.add(2, 3)   # 增加兩條記錄，1 2 和 1 3

# 方式2，通過(guò)對(duì)象添加關(guān)系
book_obj = models.Book.objects.filter(pk=2).first()
author_obj1 = models.Author.objects.filter(pk=1).first()
author_obj2 = models.Author.objects.filter(pk=2).first()
author_obj3 = models.Author.objects.filter(pk=3).first()
book_obj.author.add(author_obj1)    # 增加1條記錄
book_obj.author.add(author_obj2, author_obj3) # 增加2條

# 方式1：直接刪除值
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.remove(2)  # 刪除book_id為1和author_id都為2的記錄
book_obj.authors.remove(1, 3)  # 刪除多條

# 方式2：通過(guò)對(duì)象刪除
author_obj1 = models.Author.objects.filter(pk=2).first()
author_obj2 = models.Author.objects.filter(pk=3).first()
book_obj.authors.remove(author_obj1, author_obj2)

# 方式1：直接通過(guò)值進(jìn)行修改
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.set([2])  # 將book_id為1對(duì)應(yīng)的author_id修改為2
book_obj.authors.set([1, 2])  # 將書(shū)的作者設(shè)置為id=1 和id=2的作者

# 方式2：通過(guò)對(duì)象進(jìn)行修改
author_obj2 = models.Author.objects.filter(pk=2).first()
author_obj3 = models.Author.objects.filter(pk=3).first()
book_obj.authors.set([author_obj2, author_obj3])

book_obj = models.Book.objects.filter(pk=1).first()
book_obj.author.clear()

# 一對(duì)多關(guān)系查詢：查詢書(shū)籍主鍵為1的書(shū)籍由哪個(gè)出版社出版
book_obj = models.Book.objects.filter(pk=1).first()
res = book_obj.publish  # Publish object (1)
print(res.name)

# 多對(duì)多關(guān)系查詢：查詢數(shù)據(jù)主鍵為1的作者
book_obj = models.Book.objects.filter(pk=1).first()
res = book_obj.author  # first.Author.None，說(shuō)明結(jié)果有多個(gè)
res_many = book_obj.author.all()  # <QuerySet [<Author: Author object (1)>, <Author: Author object (2)>]>
    
# 一對(duì)一關(guān)系查詢：查詢作者lili的年齡
author_obj = models.Author.objects.filter(name='lili').first()
res = author_obj.author_detail
print(res.phone)

# 一對(duì)多關(guān)系查詢，查詢出版社是東方出版社出版的書(shū)
publish_obj = models.Publish.objects.filter(name='東方').first()
res = publish_obj.book_set.all()  # <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>

# 多對(duì)多關(guān)系查詢，查詢作者是lili寫(xiě)過(guò)的書(shū)
author_obj = models.Author.objects.filter(name='lili').first()
res = author_obj.book_set  # first.Book.None，說(shuō)明有多個(gè)結(jié)果
res_many = author_obj.book_set.all()
print(res_many)  # <QuerySet [<Book: Book object (1)>]>

# 一對(duì)一關(guān)系查詢，查詢手機(jī)號(hào)是119的作者
author_detail_obj = models.AuthorDetail.objects.filter(phone='119').first()
res = author_detail_obj.author
print(res.name)

# 一對(duì)多關(guān)系查詢：查詢書(shū)籍主鍵為1的出版社名稱和書(shū)名
# 首先獲取書(shū)籍對(duì)象，書(shū)籍是查詢publish的基表，因此獲取書(shū)名直接values('names')即可，而出版社的名字是通過(guò)外鍵字段跳到出版社的表中農(nóng)，需要通過(guò)__找到需要的字段值
res = models.Book.objects.filter(pk=1).values('name', 'publish__name')  # <QuerySet [{'name': '哈利波特', 'publish__name': '東方'}]>

# 多對(duì)多關(guān)系查詢，查詢書(shū)籍主鍵為1的作者姓名
res = models.Book.objects.filter(pk=1).values('author__name')  # <QuerySet [{'author__name': 'lili'}, {'author__name': 'nana'}]>

# 一對(duì)一關(guān)系查詢，查詢lili的手機(jī)號(hào)和姓名
res = models.Author.objects.filter(name='lili').values('name', 'author_detail__phone').first()
print(res.get('name'), res.get('author_detail__phone'))

# 一對(duì)多關(guān)系查詢：查詢數(shù)據(jù)主鍵為1的出版社名稱和書(shū)的名字
res = models.Publish.objects.filter(book__id=1).values('name', 'book__name')  # <QuerySet [{'name': '東方', 'book__name': '哈利波特'}]>

# 多對(duì)多關(guān)系查詢：查詢書(shū)籍主鍵為1的作者姓名和書(shū)名
res = models.Author.objects.filter(book__id=1).values('name', 'book__name')  # <QuerySet [{'name': 'lili', 'book__name': '哈利波特'}, {'name': 'nana', 'book__name': '哈利波特'}]>

# 一對(duì)一關(guān)系查詢：查詢作者id是1作者的姓名和手機(jī)號(hào)
res = models.AuthorDetail.objects.filter(author__id=1).values('author__name', 'phone')  # <QuerySet [{'author__name': 'lili', 'phone': '119'}]>

# 綜合大查詢：查詢書(shū)籍主鍵是1的作者的手機(jī)號(hào)，首先獲取書(shū)籍對(duì)象，書(shū)籍關(guān)聯(lián)了作者表，作者表又關(guān)聯(lián)了作者詳情表 
res = models.Book.objects.filter(pk=1).values('author__author_detail__phone')  # <QuerySet [{'author__author_detail__phone': '119'}, {'author__author_detail__phone': '120'}]>

from django.db.models import Min,Max,Sum,Count,Avg
# 統(tǒng)計(jì)書(shū)的平均價(jià)格
res = models.Book.objects.aggregate(Avg('price'))
print(res)
# 可以將這些聚合函數(shù)同時(shí)使用
res = models.Book.objects.aggregate(Max('price'),Sum('price'),Count('pk'))
print(res)

from django.db.models import Sum, Max, Min, Avg, Count

# 1.統(tǒng)計(jì)每本書(shū)的作者個(gè)數(shù)
res = models.Book.objects.annotate(author_num=Count('author')).values('name', 'author_num')  # author_num是自己定義的字段用來(lái)存儲(chǔ)統(tǒng)計(jì)出來(lái)的每本書(shū)對(duì)應(yīng)的作者個(gè)數(shù),暫時(shí)存為表中的字段

# 2.統(tǒng)計(jì)每個(gè)出版社賣(mài)的最便宜的書(shū)的價(jià)格
res = models.Publish.objects.annotate(min_price=Min('book__price')).values('name', 'min_price')

# 3.統(tǒng)計(jì)不止一個(gè)作者的圖書(shū)
# 先按照?qǐng)D書(shū)分組，算出每本書(shū)的作者數(shù)量，再過(guò)濾出作者數(shù)量大于1的數(shù)據(jù)
res = models.Book.objects.annotate(author_num=Count('author')).filter(author_num__gt=1).values('name', 'author_num')

# 4.查詢每個(gè)作者出版書(shū)的總價(jià)格
res = models.Author.objects.annotate(sum_price=Sum('book__price')).values('name', 'sum_price')

from django.db.models import F

# 1.查詢賣(mài)出數(shù)量大于庫(kù)存數(shù)量的書(shū)籍
res = models.Book.objects.filter(sell_num__gt=F('inventory_num'))

# 將所有書(shū)籍的價(jià)格提升20元
res = models.Book.objects.update(price=F('price')+20)

# 將所有書(shū)的名稱后面加上爆款兩個(gè)字
from django.db.models.functions import Concat
from django.db.models import F, Value

models.Book.objects.update(name=Concat(F('name'),Value('爆款')))

import django.db.models import Q

# 查詢賣(mài)出數(shù)量大于100或者價(jià)格小于20的書(shū)籍
res = models.Book.objects.filter(~Q(sell_num__gt=100) | Q(price__lt=20))

# 先產(chǎn)生一個(gè)Q的實(shí)例
q = Q()
# 修改q的連接條件的關(guān)系
q.connector = 'or'
q.children.append(('sell_num__gt',100))
q.children.append(('price__lt',200))
res = models.Book.objects.filter(q)
# filter的條件是Q實(shí)例化產(chǎn)生的對(duì)象，每個(gè)條件默認(rèn)還是and關(guān)系，可以修改
print(res)

from django.db import transaction

try:
    with transaction.atomic():   # 在with代碼快內(nèi)書(shū)寫(xiě)的所有orm操作都屬于同一個(gè)事務(wù)
        ...
except Exception as e:
    print(r)
...