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')
浙公网安备 33010602011771号