from django.db import models

# Create your models here.

from django.db import models

# Create your models here.

class Author(models.Model):

    nid = models.AutoField(primary_key=True)

    name=models.CharField( max_length=32)

    age=models.IntegerField()

    # 与AuthorDetail建立一对一的关系

    authorDetail=models.OneToOneField(to="AuthorDetail",on_delete=models.CASCADE)

class AuthorDetail(models.Model):

    nid = models.AutoField(primary_key=True)

    birthday=models.DateField()

    telephone=models.BigIntegerField()

    addr=models.CharField( max_length=64)

class Publish(models.Model):

    nid = models.AutoField(primary_key=True)

    name=models.CharField( max_length=32)

    city=models.CharField( max_length=32)

    email=models.EmailField()

class Book(models.Model):

    nid = models.AutoField(primary_key=True)

    title = models.CharField( max_length=32)

    publishDate=models.DateField()

    price=models.DecimalField(max_digits=5,decimal_places=2)

    # 与Publish建立一对多的关系,外键字段建立在多的一方

    publish=models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE)

    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表

    authors=models.ManyToManyField(to='Author',)

models.py

from django.shortcuts import render,HttpResponse

# Create your views here.

from app01 import models

def query(request):

    # #####################基于对象查询(子查询)##############################

    #                按字段（publish）

    # 一对多   book  ----------------->  publish

    #               <----------------

    #                 book_set.all()

    # 正向查询按字段：

    # 查询python这本书籍的出版社的邮箱

    # python=models.Book.objects.filter(title="python").first()

    # print(python.publish.email)

    # 反向查询按     表名小写_set.all()

    # 苹果出版社出版的书籍名称

    # publish_obj=models.Publish.objects.filter(name="苹果出版社").first()

    # for obj in publish_obj.book_set.all():

    #     print(obj.title)

    #                按字段（authors.all()）

    # 多对多   book  ----------------------->  author

    #               <----------------

    #                  book_set.all()

    # 查询python作者的年龄

    # python = models.Book.objects.filter(title="python").first()

    # for author in python.authors.all():

    #     print(author.name ,author.age)

    # 查询alex出版过的书籍名称

    # alex=models.Author.objects.filter(name="alex").first()

    # for book in alex.book_set.all():

    #     print(book.title)

    #                  按字段 authorDetail

    # 多对多   author  ----------------------->  authordetail

    #                <----------------

    #                  按表名  author

    #查询alex的手机号

    # alex=models.Author.objects.filter(name='alex').first()

    # print(alex.authorDetail.telephone)

    # 查询家在山东的作者名字

    # ad_list=models.AuthorDetail.objects.filter(addr="shandong")

    #

    # for ad in ad_list:

    #     print(ad.author.name)

    '''

    对应sql:

       select publish_id from Book where title="python"

       select email from Publish where nid =   1

    '''

    # #####################基于queryset和__查询（join查询）############################

    # 正向查询：按字段  反向查询：表名小写

    # 查询python这本书籍的出版社的邮箱

    # ret=models.Book.objects.filter(title="python").values("publish__email")

    # print(ret.query)

    '''

    select publish.email from Book

    left join Publish on book.publish_id=publish.nid

    where book.title="python"

    '''

    # 苹果出版社出版的书籍名称

    # 方式1：

    ret1=models.Publish.objects.filter(name="苹果出版社").values("book__title")

    print("111111111====>",ret1.query)

    #方式2：

    ret2=models.Book.objects.filter(publish__name="苹果出版社").values("title")

    print("2222222222====>", ret2.query)

    #查询alex的手机号

    # 方式1：

    ret=models.Author.objects.filter(name="alex").values("authorDetail__telephone")

    # 方式2：

    models.AuthorDetail.objects.filter(author__name="alex").values("telephone")

    # 查询手机号以151开头的作者出版过的书籍名称以及书籍对应的出版社名称

    ret=models.Book.objects.filter(authors__authorDetail__telephone__startswith="").values('title',"publish__name")

    print(ret.query)

    return HttpResponse("OK")