DOM操作

  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,Avg
107     # # 每一个出版社的名字以及对应出版书籍个数
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 id
123     # select 哪个字段就按哪个字段group_by
124     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')