ORM中基于对象查询与基于queryset查询

感谢老男孩~  一步一步走下去

前面是视图函数 后面是表结构models.py 

from django.shortcuts import render, HttpResponse
from django.http import JsonResponse

# Create your views here.
from app01 import models


def query(request):
    #########基于对象查询(子查询)###########

    # 一对多  book(publish forkey) ---> publish 正向查询
    # 正向查询 按字段
    # 反向查询 表名_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() #对象
    # print(publish_obj)
    # for obj in publish_obj.book_set.all():
    #     print(obj.title)

    #           按字段(author.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)

    # 查询家在北京的作者名字 (反向查询)
    # obj = models.AuthorDetail.objects.filter(addr="北京").first()
    # print(obj.author.name)

    print("分割线:", "*" * 100)

    # 如果addr为北京的人很多  执行了若干次的一对一执行
    # obj_list = models.AuthorDetail.objects.filter(addr="北京").all()
    # for first in obj_list:
    #     print(first.author.name)
    """
    查询python这本书籍的出版社的邮箱 models.book.objects.filter(title="python").first().publish.email
    第一步: SELECT "app01_book"."nid", "app01_book"."title", "app01_book"."publishDate", "app01_book"."price
", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."title" = 'python' ORDER BY "app01_boo
k"."nid" ASC LIMIT 1; args=('python',)
    第二步:SELECT "app01_publish"."nid", "app01_publish"."name", "app01_publish"."city", "app01_publish"."e
mail" FROM "app01_publish" WHERE "app01_publish"."nid" = 1; args=(1,)
    即: #若干条子查询 select语句
        select publish_id from Book where title = "python"
        select email from Publish where nid = 1
    
    查询alex手机号 models.author.objects.filter(name="alex").first().authorDetail.telephone
    select authorDetail_id from author where name = "alex"
    select telephone from authordetail where nid = 1
    
    """

    #########基于queryset和__查询 (join)###########
    # 正向查询:按字段__
    # 反向查询:表名小写__
    """
    查询python 这本书籍的出版社的邮箱 
    select publish.email from Book left join Publish on book.publish_id = publish.nid where book.title = "python"
    """
    # 查询python 这本书籍的出版社的邮箱 (正向查询)
    ret = models.Book.objects.filter(title="python").values("publish__email")
    #方式2
    models.Publish.objects.filter(book__title="python").values("email")
    print(ret.query)  # ret.query属性 就不需要在setging设置全局了
    """
    SELECT "app01_publish"."email" FROM "app01_book" INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "ap
p01_book"."title" = python

    """

    #查询苹果出版社的书籍名称
    #方式1
    book = models.Publish.objects.filter(name="天空出版社").values("book__title")
    print(book.query)
    #方式2
    book = models.Book.objects.filter(publish__name="天空出版社").values("title")
    print(book.query)
    """
    SELECT "app01_book"."title" FROM "app01_publish" 
    LEFT OUTER JOIN "app01_book" ON ("app01_publish"."nid" = "app01_book"."publish_id") 
    WHERE "app01_publish"."name" = 天空出版社
    
    SELECT "app01_book"."title" FROM "app01_publish" 
    LEFT OUTER JOIN "app01_book" ON ("app01_book"."publish_id" = "app01_publish"."nid") 
    WHERE "app01_publish"."name" = 天空出版社   
    
    """

    #查询alex的手机号
    alex = models.Author.objects.filter(name="alex").values("authorDetail__telephone")
    print(alex)
    alex  = list(alex)
    print(alex)
    tel = models.AuthorDetail.objects.filter(author__name="alex").values("telephone")
    print(tel)

    #查询手机号以151开头的作者出版过的书籍名称以及对应的出版社名称  (五张表)
    book_ret = models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values("title","publish__name")
    print(book_ret)
    publish_ret = models.Publish.objects.filter(book__authors__authorDetail__telephone__startswith="151").values("book__title","name")
    print(publish_ret)
    """
    SELECT "app01_book"."titl
e", "app01_publish"."name" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."nid" = "app01_book_authors"."book_id") INNER
JOIN "app01_author" ON ("app01_book_authors"."author_id" = "app01_author"."nid") INNER JOIN "app01_authordetail" ON ("app01_author"."authorD
etail_id" = "app01_authordetail"."nid") INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "app01_autho
rdetail"."telephone" LIKE 151% ESCAPE '\'
    """
    # test查询手机号以151开头的作者出版过的书籍名称以及对应的出版社名称  (五张表)
    book = models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values("title","publish__name")
    publish = models.Publish.objects.filter(book__authors__authorDetail__telephone__startswith="151").values("book__title","name")
    print("this is test",book,publish)

    ###############################聚合与分组#####################################
    #查询所有书籍的价格和
    from django.db.models import Sum, Count, Avg
    ret = models.Book.objects.all().aggregate(price_sum = Sum("price"))
    print(ret) #ret为字典 {'price_sum': Decimal('710.00')}

    #查询所有作者的平均年龄
    ret = models.Author.objects.all().aggregate(age_avg = Avg("age"))
    print(ret)

    #统计有多少个作者
    ret = models.Author.objects.all().aggregate(num = Count("nid"))
    print(ret)

    #分组
    """
    sql分组
        emp表
        id       name     age     dep
        1        alex      21        技术部
        2        egon      23        技术部
        3        wenzhou   25        运营部
        4        jc      22          运营部
    
    查询每一个部门的人数
    sql:
        select Count(id) from emp group by dep
    orm:
        models.emp.objects.values("dep").annotate(c=Count("id"))
        
        
        emp表
    id       name     age     dep_id
    1        alex      21        1
    2        egon      23        2
    3        wenzhou   25        2
    
        dep表
    id      name
     1      技术部
     2      运营部
     
    查询每一个部门名称以及对应人数
    sql:
        select * from emp inner join dep on emp.dep_id = dep.id
            id       name     age     dep_id  dep.id dep.name
            1        alex      21        1      1    技术部
            2        egon      23        2      2    运营部
            3        wenzhou   25        2      2    运营部
        select dep.name, Count(*) from emp inner join dep on emp.dep_id = dep.id group by dep.id, dep.name 
    
    ORM:
        关键点:
                1.queryset数据可以调用annotate()
                2.annotate进行分组统计，按照前面的select的字段进行group by
                3.annotate()返回值依然是queryset对象， 增加了分组统计的键值对
        models.dep.objects.values("name").annotate(c=Count("emp__name")).values("name","c")
        models.emp.objects.values("dep__name").annotate(c=Count("name")).values("dep__name", "c")
        select dep.name, Count(emp.name) as c from dep inner join emp on .. group by dep.name
            
        select ___ from ___ inner join ___ on ... group by ___ 
    """
    ret = models.Publish.objects.values("name") #SELECT "app01_publish"."name" FROM "app01_publish"
    print(ret.query)

    from django.db.models import Max
    #查询每一个作者的名字以及出版过的书籍最高价格
    ret = models.Author.objects.values("name").annotate(max_price = Max("book__price")).values("name","max_price")
    print(ret)

    """
    select author.name, Max(book.price) as max_price from author inner join book_authors on ...
                          inner join book   on ...
    group by author.name
    """

    #查询每一个出版社出版过的书籍的平均价格
    ret = models.Publish.objects.values("name").annotate(avg_price = Avg("book__price")).values("name","avg_price")
    print(ret)

    #查询每一本书籍的作者个数
    ret = models.Book.objects.values("title").annotate(author_count = Count("authors__name")).values("title","author_count")  #或者将authors__name换成authors
    print(ret)

    #查询每一个分类的名称以及对应的文章数
    # models.Category.objects.all().annotate(c = Count("article__title")).values("title","c")

    #统计不止2个作者的图书名称: 作者数大于两个  难点
    #先统计每个本书籍的作者个数
    ret = models.Book.objects.values("title").annotate(count=Count("authors__name")).values("title","count")
    print(ret)

    """
    select book.title, Count(author.name) as c  from book inner join book-authors on ..
                        inner join author on ..
    group by book.id having c > 2
    """
    ret = models.Book.objects.values("title").annotate(count=Count("authors__name")).filter(count__gt=1).values("title","count")
    print(ret)
    #统计作者为2的图书名称
    ret = models.Book.objects.values("title").annotate(count=Count("authors__name")).filter(count=2).values("title","count")
    print(ret)

    #统计出版的每一本价格大于100的书籍
    ret = models.Book.objects.values("title").filter(price__gt=100).values("title","price")
    print(ret)

    #统计每一个出版出版的书籍及价格
    ret = models.Publish.objects.values("name").annotate(count=Count("book__title")).values("name","book__title")
    print(ret)

    #统计每一个出版社出版的书籍个数
    ret = models.Publish.objects.values("name").annotate(count=Count("book__title")).values("name","count")
    print(ret)

    #统计每一个出版社出版大于100的书籍名称
    ret = models.Publish.objects.values("name").annotate().filter(book__price__gt=100).values("name","book__title","book__price")
    print(ret)
    return HttpResponse("ok")

 

 

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=models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE)
    authors=models.ManyToManyField(to='Author',)  #多对多关联字段

 

from django.shortcuts import render, HttpResponse
from django.http import JsonResponse

# Create your views here.
from app01 import models


def query(request):
    #########基于对象查询(子查询)###########

    # 一对多  book(publish forkey) ---> publish 正向查询
    # 正向查询 按字段
    # 反向查询 表名_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() #对象
    # print(publish_obj)
    # for obj in publish_obj.book_set.all():
    #     print(obj.title)

    #           按字段(author.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)

    # 查询家在北京的作者名字 (反向查询)
    # obj = models.AuthorDetail.objects.filter(addr="北京").first()
    # print(obj.author.name)

print("分割线:", "*" * 100)

    # 如果addr为北京的人很多  执行了若干次的一对一执行
    # obj_list = models.AuthorDetail.objects.filter(addr="北京").all()
    # for first in obj_list:
    #     print(first.author.name)
"""
    查询python这本书籍的出版社的邮箱 models.book.objects.filter(title="python").first().publish.email
    第一步: SELECT "app01_book"."nid", "app01_book"."title", "app01_book"."publishDate", "app01_book"."price
", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."title" = 'python' ORDER BY "app01_boo
k"."nid" ASC LIMIT 1; args=('python',)
    第二步:SELECT "app01_publish"."nid", "app01_publish"."name", "app01_publish"."city", "app01_publish"."e
mail" FROM "app01_publish" WHERE "app01_publish"."nid" = 1; args=(1,)
    即: #若干条子查询 select语句
        select publish_id from Book where title = "python"
        select email from Publish where nid = 1
    
    查询alex手机号 models.author.objects.filter(name="alex").first().authorDetail.telephone
    select authorDetail_id from author where name = "alex"
    select telephone from authordetail where nid = 1
    
    """

#########基于queryset和__查询 (join)###########
    # 正向查询:按字段__
    # 反向查询:表名小写__
"""
    查询python 这本书籍的出版社的邮箱 
    select publish.email from Book left join Publish on book.publish_id = publish.nid where book.title = "python"
    """
# 查询python 这本书籍的出版社的邮箱 (正向查询)
ret = models.Book.objects.filter(title="python").values("publish__email")
    #方式2
models.Publish.objects.filter(book__title="python").values("email")
    print(ret.query)  # ret.query属性 就不需要在setging设置全局了
"""
    SELECT "app01_publish"."email" FROM "app01_book" INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "ap
p01_book"."title" = python

    """

#查询苹果出版社的书籍名称
    #方式1
book = models.Publish.objects.filter(name="苹果出版社").values("book__title")
    print(book.query)
    #方式2
book = models.Book.objects.filter(publish__name="苹果出版社").values("title")
    print(book.query)
    """
    SELECT "app01_book"."title" FROM "app01_publish" 
    LEFT OUTER JOIN "app01_book" ON ("app01_publish"."nid" = "app01_book"."publish_id") 
    WHERE "app01_publish"."name" = 苹果出版社
    
    SELECT "app01_book"."title" FROM "app01_publish" 
    LEFT OUTER JOIN "app01_book" ON ("app01_book"."publish_id" = "app01_publish"."nid") 
    WHERE "app01_publish"."name" = 苹果出版社   
    
    """

#查询alex的手机号
alex = models.Author.objects.filter(name="alex").values("authorDetail__telephone")
    print(alex)
    alex  = list(alex)
    print(alex)
    tel = models.AuthorDetail.objects.filter(author__name="alex").values("telephone")
    print(tel)

    #查询手机号以151开头的作者出版过的书籍名称以及对应的出版社名称  (五张表)
book_ret = models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values("title","publish__name")
    print(book_ret)
    publish_ret = models.Publish.objects.filter(book__authors__authorDetail__telephone__startswith="151").values("book__title","name")
    print(publish_ret)
    """
    SELECT "app01_book"."titl
e", "app01_publish"."name" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."nid" = "app01_book_authors"."book_id") INNER
JOIN "app01_author" ON ("app01_book_authors"."author_id" = "app01_author"."nid") INNER JOIN "app01_authordetail" ON ("app01_author"."authorD
etail_id" = "app01_authordetail"."nid") INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "app01_autho
rdetail"."telephone" LIKE 151% ESCAPE '\'
    """
# test查询手机号以151开头的作者出版过的书籍名称以及对应的出版社名称  (五张表)
book = models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values("title","publish__name")
    publish = models.Publish.objects.filter(book__authors__authorDetail__telephone__startswith="151").values("book__title","name")
    print("this is test",book,publish)

    ###############################聚合与分组#####################################
    #查询所有书籍的价格和
from django.db.models import Sum, Count, Avg
    ret = models.Book.objects.all().aggregate(price_sum = Sum("price"))
    print(ret) #ret为字典 {'price_sum': Decimal('710.00')}

    #查询所有作者的平均年龄
ret = models.Author.objects.all().aggregate(age_avg = Avg("age"))
    print(ret)

    #统计有多少个作者
ret = models.Author.objects.all().aggregate(num = Count("nid"))
    print(ret)

    #分组
"""
    sql分组
        emp表
        id       name     age     dep
        1        alex      21        保安部
        2        egon      23        保安部
        3        wenzhou   25        保安部
        4        jc      22          保安部
    
    查询每一个部门的人数
    sql:
        select Count(id) from emp group by dep
    orm:
        models.emp.objects.values("dep").annotate(c=Count("id"))
        
        
        emp表
    id       name     age     dep_id
    1        alex      21        1
    2        egon      23        2
    3        wenzhou   25        2
    
        dep表
    id      name
     1      保安部
     2      保洁部
     
    查询每一个部门名称以及对应人数
    sql:
        select * from emp inner join dep on emp.dep_id = dep.id
            id       name     age     dep_id  dep.id dep.name
            1        alex      21        1      1    保安部
            2        egon      23        2      2    保洁部
            3        wenzhou   25        2      2    保洁部
        select dep.name, Count(*) from emp inner join dep on emp.dep_id = dep.id group by dep.id, dep.name 
    
    ORM:
        关键点:
                1.queryset数据可以调用annotate()
                2.annotate进行分组统计，按照前面的select的字段进行group by
                3.annotate()返回值依然是queryset对象， 增加了分组统计的键值对
        models.dep.objects.values("name").annotate(c=Count("emp__name")).values("name","c")
        models.emp.objects.values("dep__name").annotate(c=Count("name")).values("dep__name", "c")
        select dep.name, Count(emp.name) as c from dep inner join emp on .. group by dep.name
            
        select ___ from ___ inner join ___ on ... group by ___ 
    """
ret = models.Publish.objects.values("name") #SELECT "app01_publish"."name" FROM "app01_publish"
print(ret.query)

    from django.db.models import Max
    #查询每一个作者的名字以及出版过的书籍最高价格
ret = models.Author.objects.values("name").annotate(max_price = Max("book__price")).values("name","max_price")
    print(ret)

    """
    select author.name, Max(book.price) as max_price from author inner join book_authors on ...
                          inner join book   on ...
    group by author.name
    """

#查询每一个出版社出版过的书籍的平均价格
ret = models.Publish.objects.values("name").annotate(avg_price = Avg("book__price")).values("name","avg_price")
    print(ret)

    #查询每一本书籍的作者个数
ret = models.Book.objects.values("title").annotate(author_count = Count("authors__name")).values("title","author_count")  #或者将authors__name换成authors
print(ret)

    #查询每一个分类的名称以及对应的文章数
    # models.Category.objects.all().annotate(c = Count("article__title")).values("title","c")

    #统计不止2个作者的图书名称: 作者数大于两个  难点
    #先统计每个本书籍的作者个数
ret = models.Book.objects.values("title").annotate(count=Count("authors__name")).values("title","count")
    print(ret)

    """
    select book.title, Count(author.name) as c  from book inner join book-authors on ..
                        inner join author on ..
    group by book.id having c > 2
    """
ret = models.Book.objects.values("title").annotate(count=Count("authors__name")).filter(count__gt=1).values("title","count")
    print(ret)
    #统计作者为2的图书名称
ret = models.Book.objects.values("title").annotate(count=Count("authors__name")).filter(count=2).values("title","count")
    print(ret)

    #统计出版的每一本价格大于100的书籍
ret = models.Book.objects.values("title").filter(price__gt=100).values("title","price")
    print(ret)

    #统计每一个出版出版的书籍及价格
ret = models.Publish.objects.values("name").annotate(count=Count("book__title")).values("name","book__title")
    print(ret)

    #统计每一个出版社出版的书籍个数
ret = models.Publish.objects.values("name").annotate(count=Count("book__title")).values("name","count")
    print(ret)

    #统计每一个出版社出版大于100的书籍名称
ret = models.Publish.objects.values("name").annotate().filter(book__price__gt=100).values("name","book__title","book__price")
    print(ret)
    return HttpResponse("ok")