1 from django.shortcuts import render,HttpResponse  2   3 # Create your views here.  4   5   6 from .models import *  7 def query(request):  8   9  10     #######################基于对象的跨表查询(子查询)####################### 11     # ''' 12     # 一对多： 13     #      正向查询按字段 14     # Book--------------->Publish 15     #     <--------------- 16     #      反向查询按表名小写_set 17     # ''' 18     # #(1) 查询id为2的书籍对应出版社的邮箱 19     # obj=Book.objects.filter(nid=2).first() 20     # print(obj.publish.email) 21     # ret=obj.publish.email 22     # # select publihs_id from Book where nid=2 23     # # select   email    from  Publish where nid=（select publihs_id from Book where nid=2） 24     # 25     # #(2) 橘子出版社出版过的所有的书籍的名字 26     # # obj=Publish.objects.filter(name="橘子出版社").first() 27     # # ret=obj.book_set.all().values("title") 28     # 29     # ''' 30     #    多对多： 31     #         正向查询按字段 32     #    Book--------------->Author 33     #        <--------------- 34     #         反向查询按表名小写_set 35     #         反向查询按xxx 36     # ''' 37     # 38     # # 查询围城所有作者的名字 39     # obj=Book.objects.filter(title="围城").first() 40     # ret=obj.authors.all().values("name") 41     # print(ret) 42     # 43     # # 查询alex出版的书籍个数 44     # obj=Author.objects.filter(name="alex").first() 45     # ret=obj.book_set.all().count() 46     # print(ret) 47     # 48     # ''' 49     #        一对一： 50     #             正向查询按字段 51     #        Book--------------->Author 52     #            <--------------- 53     #             反向查询按表名小写 54     # ''' 55     # 56     # # 查询alex的手机号 57     # obj=Author.objects.filter(name="alex").first() 58     # ret=obj.authorDetail.telephone 59     # print(ret) 60     # 61     # # 住在烟台的作者的名字 62     # 63     # obj_list=AuthorDetail.objects.filter(addr="烟台") 64     # for obj in obj_list: 65     #     print(obj.author.name) 66  67     #######################基于queryset和__的跨表查询(join查询)####################### 68     ''' 69     正向查询安字段 70     反向查询安表名 71     ''' 72     # (1)查询id为2的书籍对应出版社的邮箱 73     # ret=Book.objects.filter(price=100).values("publish__email") 74     # print(ret)# [{publish__email:456},{publish__email:456},{publish__email:123}] 75     ''' 76     Book.objects.filter(price=100).values("title","publish__email"): 77     queryset=Book.objects.filter(price=100) 78     temp=[] 79     for obj in queryset: 80         temp.append({ 81             "title":obj.title, 82             "publish__email":obj.publish__email 83         }) 84          85     temp 86      87     ''' 88  89     # # (2)橘子出版社出版过的所有的书籍的名字 90     # ret=Publish.objects.filter(name="橘子出版社").values("book__title") 91     # print(ret) 92     # # (3)查询围城所有作者的名字 93     # ret=Book.objects.filter(title="围城").values("authors__name") 94     # print(ret) 95     # # (4)查询alex出版的书籍个数 96     # Author.objects.filter(name="alex").values("book__title").count() 97     # # (5)查询alex的手机号 98     # ret=Author.objects.filter(name="alex").values("authorDetail__telephone") 99     # print(ret)100     # # (6)住在烟台的作者的名字101     # ret=AuthorDetail.objects.filter(addr="烟台").values("author__name")102     # print(ret)103 104 105     #####################分组查询##########################106     from django.db.models import Count,Avg107     # # 每一个出版社的名字以及对应出版书籍个数108     # ret=Publish.objects.all().annotate(book_count=Count("book__title")).values("name","book_count")109     # print(ret)110     #111     # # 查询每一个作者的名字以及对应书籍的平均价格112     # ret=Author.objects.all().annotate(books_avg=Avg("book__price")).values("name","books_avg")113     # print(ret)114     #115     #116     # # 查询每一本书 的名字以及作者的个数117     # ret=Book.objects.all().annotate(c=Count("authors")).values("title","c")118     # print(ret)119 120 121 122     # select * from Publish group by id123     # select 哪个字段就按哪个字段group_by124     ret=Book.objects.values("title").annotate(c=Count("*"))125     print(ret)126     # SELECT "app01_book"."title", COUNT(*) AS "c" FROM "app01_book" GROUP BY "app01_book"."title"127 128     return HttpResponse("OK")

 

1 from django.db import models 2  3 # Create your models here. 4  5  6  7 class AuthorDetail(models.Model): 8     nid = models.AutoField(primary_key=True) 9     birthday = models.DateField()10     telephone = models.BigIntegerField()11     addr = models.CharField(max_length=64)12 13 class Author(models.Model):14     nid = models.AutoField(primary_key=True)15     name = models.CharField(max_length=32)16     age = models.IntegerField()17     # 与AuthorDetail建立一对一的关系18     authorDetail = models.OneToOneField(to="AuthorDetail")19 20 class Publish(models.Model):21     nid = models.AutoField(primary_key=True)22     name = models.CharField(max_length=32)23     city = models.CharField(max_length=32)24     email = models.EmailField()25 26 27 class Book(models.Model):28     nid = models.AutoField(primary_key=True)29     title = models.CharField(max_length=32)30     publishDate = models.DateField()31     price = models.DecimalField(max_digits=5, decimal_places=2)32     keepNum = models.IntegerField()33     commentNum = models.IntegerField()34 35     # 与Publish建立一对多的关系,外键字段建立在多的一方36     publish = models.ForeignKey(to="Publish", to_field="nid",)37 38     # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表39     authors = models.ManyToManyField(to='Author')