- 51-表关系之一对多
- 52-表关系之多对多
- 53-表关系之一对一
- 54-数据库表关系之关联字段与外键约束
- 55-数据库表关系之sql创建关联表
- 56-ORM生成关联表模型
- 57-多表操作之一对多添加记录
- 58-多表操作之多对多添加记录
- 59-基于对象跨表查询简介
- 60-基于对象跨表查询之一对多
- 61-基于对象跨表查询之多对多
- 62-基于对象跨表查询之一对一
- 63-基于对象跨表查询之sql语句
- 64-基于双下划线的跨表查询之一对多1
- 65-基于双下划线的跨表查询之一对多2
- 66-基于双下划线的跨表查询之多对多1
- 67-基于双下滑线的跨表查询之多对多2
- 68-基于双下滑线的跨表查询之一对一
- 69-基于双下划线的跨表查询之连续跨表1
- 70-基于双下划线的跨表查询之连续跨表2
- 71-聚合查询
- 72-单表下的分组查询1
- 73-单表下的分组查询2
- 74-多表下的分组查询1
- 75-多表下的分组查询2
- 76-多表下的分组查询3
- 77-多表下的分组查询4
- 78-多表下的分组查询5
- 79-多表下的分组查询6
- 80-F查询和Q查询
- 81-多表操作的章节作业布置-基于多表的图书管理系统
- 82-基于多表的图书管理系统添加功能1
- 83-基于多表的图书管理系统添加功能2
- 84-基于多表的图书管理系统查看功能
- 85-基于多表的图书管理系统编辑功能1
- 86-基于多表的图书管理系统编辑功能2
- 87-基于多表的图书管理系统删除功能
51-表关系之一对多；
1、表关系之“一对多”；
即一张表中的一条记录，可对应另一张表中的多条记录；
2、为什么要使用多表？
减少冗余数据，提升查询效率；
3、总结;
1)一旦确认表关系是一对多后，在多对应的表中创建"关联字段"；
52-表关系之多对多
1、表关系之多对多；
即两张表的关系为“双向的一对多或双向的多对一”；
2、总结；
1）一但确定表关系是“多对多”：创建第三张“关系表”，一般为3个字段；id、a_id、b_id；
53-表关系之一对一
1、表关系之“一对一”；
即本来两张表的字段分布在“两张表”中，彼此一 一对应，但有一个“关联字段”，关联字段的约束为unique；
2、总结；
1）关联字段写在那张表呢？哪张表都可以；
2）一旦确定关联关系是“一对一”：在两张表的任意一张表中建立“关联字段”+unique约束信息；
54-数据库表关系之关联字段与外键约束
1、一般建立完关联字段后，加上约束信息，比如外键约束；
2、但有的情况下，可以使用“逻辑意义上”的外键，即不加外键约束，但从业务代码的角度进行外键引用的使用；
具体情况，具体分析，各有利弊；
55-数据库表关系之sql创建关联表
56-ORM生成关系表模型
1、Publish、Book、Author、AuthorDetail、Book2Author表关系初识；
实例：我们来假定下面这些概念，字段和关系
1、作者模型：一个作者有姓名和年龄；
2、作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息，作者详情模型和作者模型之间是一对一的关系（one-to-one）；
3、出版商模型：出版商有名称，所在城市以及email；
4、书籍模型： 书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系(many-to-many)，一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系；(one-to-many)。
2、创建models.py模型文件；
from django.db import models # Create your models here. """ Book--------Publish,一对多的关系； AuthorDetail--------Author，一对一的关系，关联字段建立在哪里都行； Book2Author,多对多关系； """ #作者详情表； class AuthorDetail(models.Model): nid = models.AutoField(primary_key=True) birthday = models.DateField() telephone = models.BigIntegerField() addr = models.CharField(max_length=64)
#作者表； class Author(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() authordetail = models.OneToOneField(to="AuthorDetail",to_field="nid",on_delete=models.CASCADE) #出版社表； 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) """ 数据库迁移的时候，会翻译成SQL语句中的这两句话； publish_id INT, FOREIGN KEY (publish_id) REFERENCES publish(id) """ #一对多的关系； authors = models.ManyToManyField(to="Author") """ #CREATE TABLE book2author( CREATE TABLE book_authors( id INT PRIMAEY KEY auto_increment, book_id INT, author_id INT, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGB KEY (autor_id) REFERENCES author(id) ); """ #ORM中绑定多对多关系,不建议这么去创建，虽说可以以类的方式去创建，但不建议这么做； # class Book2Author(models.Model): # nid = models.AutoField(primary_key=True) # book = models.ForeignKey(to="Book") # author = models.ForeignKey(to="Author")
3、使用Pycharm自带sqllite客户端工具进行连接查看；
小结：
- 表的名称
myapp_modelName，算了，我还是说人话吧，应用名_模型名称，比如app01_Book是根据模型中的元数据自动生成的，也可以覆写为别的名称； id字段是自动添加的，可指定为nid或其他；- 对于外键字段，Django 会在关联字段名上添加"_id" 来创建数据库中的列名，所以我们的关联字段不再写成book_id的形式，直接为book，否则会出现book_id_id的尴尬形式；
- 这个例子中的
CREATE TABLESQL 语句使用PostgreSQL 语法格式，要注意的是Django会根据settings.py文件中指定的数据库类型来使用相应的SQL语句，默认为sqllite3，可指定为MySQL； - 定义好模型之后，你需要告诉Django 使用这些模型。我们要做的就是修改配置文件中的INSTALL_APPSZ中设置，在其中添加
models.py所在应用的名称，比如app01； - 外键字段 ForeignKey有一个null=True的设置(它允许外键接收空值 NULL)，我们可以赋给它空值 None ；
-
在Django2.0之后，出现OneToOneField、ForeignKey要在后面添加on_delete=models.CASCADE属性，否则报错；
57-多表操作之一对多添加记录
1、views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def add(request): pub = Publish.objects.create(name="人民出版社",email="123@qq.com",city="北京") #方式1： #为Book表绑定出版社"Book-------Publish； book_obj = Book.objects.create(title="红楼梦",price=100,publishDate="2012-12-12",publish_id=1) print(book_obj.title) #方式2： pub_obj = Publish.objects.filter(nid=1).first() book_obj= Book.objects.create(title="三国演义",price=100,publishDate="2018-08-18",publish=pub_obj) print(book_obj.title) print(book_obj.price) print(book_obj.publishDate) print(book_obj.publish)#与这本书关联的出版社对象； print(book_obj.publish.name)# print(book_obj.publish.email)# print(book_obj.publish_id) #查询西游记的出版社的邮箱地址是什么？ book_obj=Book.objects.filter(title="西游记").first() print("查询西游记的出版社的邮箱地址是什么？",book_obj.publish.email) return HttpResponse("OK")
58-多表操作之一对多添加纪录
1、一对多添加记录；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def add(request): pub = Publish.objects.create(name="人民出版社",email="123@qq.com",city="北京") #方式1： #为Book表绑定出版社"Book-------Publish； # book_obj = Book.objects.create(title="红楼梦",price=100,publishDate="2012-12-12",publish_id=1) # print(book_obj.title) #方式2： # pub_obj = Publish.objects.filter(nid=1).first() # book_obj= Book.objects.create(title="三国演义",price=100,publishDate="2018-08-18",publish=pub_obj) # print(book_obj.title) # print(book_obj.price) # print(book_obj.publishDate) # print(book_obj.publish)#与这本书关联的出版社对象； # print(book_obj.publish.name)# # print(book_obj.publish.email)# # print(book_obj.publish_id) #查询西游记的出版社的邮箱地址是什么？ # book_obj=Book.objects.filter(title="西游记").first() # print("查询西游记的出版社的邮箱地址是什么？",book_obj.publish.email) ##########################################多对多的关系################################################################## book_obj = Book.objects.create(title="金梅",price=100,publishDate="2012-12-12",publish_id=1) cxz = Author.objects.get(name="cxz") ljp = Author.objects.get(name="ljp") #绑定多对多关系的API； book_obj.authors.add(cxz,ljp) #book_obj.authors.add(1,2,3) #book_obj.authors.add(*[1,2,3]) #解除多对多关系的API； book = Book.objects.filter(nid=2).first() #book.authors.remove(2) #book.authors.clear() print(book.authors.all()) #查询主键为4的书籍的所有作者的名字； ret = book.authors.all().values("name") print(ret) return HttpResponse("OK")
models.py;
from django.db import models # Create your models here. from django.db import models """ Book--------Publish,一对多的关系； AuthorDetail--------Author，一对一的关系，关联字段建立在哪里都行； Book2Author,多对多关系； """ #作者详情表； class AuthorDetail(models.Model): nid = models.AutoField(primary_key=True) birthday = models.DateField() telephone = models.BigIntegerField() addr = models.CharField(max_length=64)
#作者表； class Author(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() authordetail = models.OneToOneField(to="AuthorDetail",to_field="nid",on_delete=models.CASCADE) def __str__(self): return self.name #出版社表； 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() def __str__(self): return self.name #书籍表； 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) def __str__(self): return self.title """ 数据库迁移的时候，会翻译这两句话； publish_id INT, FOREIGN KEY (publish_id) REFERENCES publish(id) """ #一对多的关系； authors = models.ManyToManyField(to="Author") """ #CREATE TABLE book2author( CREATE TABLE book_authors( id INT PRIMAEY KEY auto_increment, book_id INT, author_id INT, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGB KEY (autor_id) REFERENCES author(id) ); """ #绑定多对多关系,不建议这么去创建； # class Book2Author(models.Model): # nid = models.AutoField(primary_key=True) # book = models.ForeignKey(to="Book") # author = models.ForeignKey(to="Author")
操作日志；
59-基于对象跨表查询简介
1、基于对象的跨表查询简介；
60-基于对象跨表查询之一对多
1、子查询和john查询；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): """ 跨表查询： 1、基于对象的查询； 2、基于双下划线的查询； 3、聚合与分组查询； 4、F与Q查询； :param request: :return: """ #1、基于对象的跨表查询； #-----------------基于对象的跨表查询(子查询)-----------------------# #一对多的正向查询；查询《西游记》这本书的出版社的名字 book_obj = Book.objects.filter(title = "西游记").first() print(book_obj.publish)#与这本书关联的出版社对象； print(book_obj.publish.name) #一对多的反向查询；查询出版社出版过的书籍的名称; publish = Publish.objects.filter(name="人民出版社").first() ret = publish.book_set.all() print(ret) return HttpResponse("OK") """ 关联属性在A表中： 正向查询:A--------->B； 反向查询:B--------->A； 关联属性在B表中： 正向查询:B--------->A； 反向查询:A--------->B； 1、一对多查询 正向查询:按照字段； 反向查询：表名小写_set.all()； book_obj.publish Book(关联属性:publish)--------------->Publish """
操作日志：
Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. [19/Aug/2018 11:46:13] "GET /query/ HTTP/1.1" 200 2 人民出版社 (0.000) SELECT "app01_book"."nid", "app01_book"."title", "app01_book"."publishDate", "app01_book"."price", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."title" = '西游记' ORDER BY "app01_book"."nid" ASC LIMIT 1; args=('西游记',) 人民出版社 (0.000) SELECT "app01_publish"."nid", "app01_publish"."name", "app01_publish"."city", "app01_publish"."email" FROM "app01_publish" WHERE "app01_publish"."nid" = 1; args=(1,) <QuerySet [<Book: 红楼梦>, <Book: 西游记>, <Book: 三国演义>, <Book: 金梅>]> (0.000) SELECT "app01_publish"."nid", "app01_publish"."name", "app01_publish"."city", "app01_publish"."email" FROM "app01_publish" WHERE "app01_publish"."name" = '人民出版社' ORDER BY "app01_publish"."nid" ASC LIMIT 1; args=('人民出版社',) (0.001) SELECT "app01_book"."nid", "app01_book"."title", "app01_book"."publishDate", "app01_book"."price", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."publish_id" = 1 LIMIT 21; args=(1,) [19/Aug/2018 11:46:55] "GET /query/ HTTP/1.1" 200 2 Performing system checks...
61-基于对象跨表查询之多对多
1、基于对象的跨表查询之多对多；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def add(request): """ 绑定关系的视图； :param request: :return: """ pub = Publish.objects.create(name="人民出版社",email="123@qq.com",city="北京") #方式1： #为Book表绑定出版社"Book-------Publish # book_obj = Book.objects.create(title="红楼梦",price=100,publishDate="2012-12-12",publish_id=1) # print(book_obj.title) #方式2： # pub_obj = Publish.objects.filter(nid=1).first() # book_obj= Book.objects.create(title="三国演义",price=100,publishDate="2018-08-18",publish=pub_obj) # print(book_obj.title) # print(book_obj.price) # print(book_obj.publishDate) # print(book_obj.publish)#与这本书关联的出版社对象； # print(book_obj.publish.name)# # print(book_obj.publish.email)# # print(book_obj.publish_id) #查询西游记的出版社的邮箱地址是什么？ # book_obj=Book.objects.filter(title="西游记").first() # print("查询西游记的出版社的邮箱地址是什么？",book_obj.publish.email) ##########################################多对多的关系################################################################## book_obj = Book.objects.create(title="金梅",price=100,publishDate="2012-12-12",publish_id=1) cxz = Author.objects.get(name="cxz") ljp = Author.objects.get(name="ljp") #绑定多对多关系的API book_obj.authors.add(cxz,ljp) #book_obj.authors.add(1,2,3) #book_obj.authors.add(*[1,2,3]) #解除多对多关系的API book = Book.objects.filter(nid=2).first() #book.authors.remove(2) # book.authors.clear() print(book.authors.all()) #查询主键为4的书籍的所有作者的名字； ret = book.authors.all().values("name") print(ret) return HttpResponse("OK") def query(request): """ 跨表查询： 1、基于对象的查询； 2、基于双下划线的查询； 3、聚合与分组查询； 4、F与Q查询； :param request: :return: """ ##1、基于对象的跨表查询； #------------------------------------------------------基于对象的跨表查询(子查询)-------------------------------------# #一对多的正向查询；查询《西游记》这本书的出版社的名字 # book_obj = Book.objects.filter(title = "西游记").first() # print(book_obj.publish)#与这本书关联的出版社对象； # print(book_obj.publish.name) # #一对多的反向查询；查询出版社出版过的书籍的名称; # publish = Publish.objects.filter(name="人民出版社").first() # ret = publish.book_set.all() # print(ret) #多对多查询的正向查询：查询《金梅》这本书的作者的名字； book_obj = Book.objects.filter(title="金梅").first() author_list = book_obj.authors.all()#Queryset对象； for author in author_list: print(author.name) #多对多反向查询；查询cxz出版过的所有书籍名称； cxz = Author.objects.filter(name="cxz").first() book_list = cxz.book_set.all() for book in book_list: print(book.title) return HttpResponse("OK") """ 关联属性在A表中： 正向查询:A--------->B； 反向查询:B--------->A； 关联属性在B表中： 正向查询:B--------->A； 反向查询:A--------->B； 1、一对多查询 正向查询:按照字段； 反向查询：表名小写_set.all()； book_obj.publish Book(关联属性:publish)--------------->Publish对象; <---------------- publish_obj.book_set.all()#Queryset 2、多对多查询 正向查询:按照字段； 反向查询：表名小写_set.all()； book_obj.authors.all() Book(关联属性:authors)--------------->Author对象 <---------------- author_obj.book_set.all()#Quertset对象； """
62-基于对象跨表查询之一对一
1、Author与AuthorDetail表存在一对一关系；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def add(request): """ 绑定关系的视图； :param request: :return: """ pub = Publish.objects.create(name="人民出版社",email="123@qq.com",city="北京") #方式1： #为Book表绑定出版社"Book-------Publish # book_obj = Book.objects.create(title="红楼梦",price=100,publishDate="2012-12-12",publish_id=1) # print(book_obj.title) #方式2： # pub_obj = Publish.objects.filter(nid=1).first() # book_obj= Book.objects.create(title="三国演义",price=100,publishDate="2018-08-18",publish=pub_obj) # print(book_obj.title) # print(book_obj.price) # print(book_obj.publishDate) # print(book_obj.publish)#与这本书关联的出版社对象； # print(book_obj.publish.name)# # print(book_obj.publish.email)# # print(book_obj.publish_id) #查询西游记的出版社的邮箱地址是什么？ # book_obj=Book.objects.filter(title="西游记").first() # print("查询西游记的出版社的邮箱地址是什么？",book_obj.publish.email) ##########################################多对多的关系################################################################## book_obj = Book.objects.create(title="金瓶",price=100,publishDate="2012-12-12",publish_id=1) cxz = Author.objects.get(name="cxz") ljp = Author.objects.get(name="ljp") #绑定多对多关系的API book_obj.authors.add(cxz,ljp) #book_obj.authors.add(1,2,3) #book_obj.authors.add(*[1,2,3]) #解除多对多关系的API book = Book.objects.filter(nid=2).first() #book.authors.remove(2) # book.authors.clear() print(book.authors.all()) #查询主键为4的书籍的所有作者的名字； ret = book.authors.all().values("name") print(ret) return HttpResponse("OK") def query(request): """ 跨表查询： 1、基于对象的查询； 2、基于双下划线的查询； 3、聚合与分组查询； 4、F与Q查询； :param request: :return: """ ##1、基于对象的跨表查询； #------------------------------------------------------基于对象的跨表查询(子查询)-------------------------------------# #一对多的正向查询；查询《西游记》这本书的出版社的名字 # book_obj = Book.objects.filter(title = "西游记").first() # print(book_obj.publish)#与这本书关联的出版社对象； # print(book_obj.publish.name) # #一对多的反向查询；查询出版社出版过的书籍的名称; # publish = Publish.objects.filter(name="人民出版社").first() # ret = publish.book_set.all() # print(ret) #多对多查询的正向查询：查询《金梅》这本书的作者的名字； book_obj = Book.objects.filter(title="金梅").first() author_list = book_obj.authors.all()#Queryset对象； for author in author_list: print(author.name) #多对多反向查询；查询cxz出版过的所有书籍名称； cxz = Author.objects.filter(name="cxz").first() book_list = cxz.book_set.all() for book in book_list: print(book.title) #一对一的正向查询:查询cxz的手机号 cxz = Author.objects.filter(name="cxz").first() print(cxz.authordetail.telephone) # 一对一的反向查询:查询手机号为138的作者的名字和年龄； ad = AuthorDetail.objects.filter(telephone="138").first() print(ad.author.name) print(ad.author.age) return HttpResponse("OK") """ 关联属性在A表中： 正向查询:A--------->B； 反向查询:B--------->A； 关联属性在B表中： 正向查询:B--------->A； 反向查询:A--------->B； 1、一对多查询 正向查询:按照字段； 反向查询：表名小写_set.all()； book_obj.publish Book(关联属性:publish)--------------->Publish对象; <---------------- publish_obj.book_set.all()#Queryset 2、多对多查询 正向查询:按照字段； 反向查询：表名小写_set.all()； book_obj.authors.all() Book(关联属性:authors)--------------->Author对象 <---------------- author_obj.book_set.all()#Quertset对象； 3、一对一查询 正向查询：按照字段 反向查询：表名小写 book_obj.authors.all() Author(关联属性：authordetail)对象 ------------------------->AuthorDetail <------------------------ authordetail.author #Queryset """
63-基于对象跨表查询之sql语句
64-基于双下划线的跨表查询之一对多1
1、基于上下滑线的跨表查询之一对多1；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------基于双下划线的跨表查询(join查询)---------------------------------------------------# #一对多查询的正向查询：查询金梅这本书的出版社的名字； ret = Book.objects.filter(title="金梅").values("publish__name") print(ret)#<QuerySet [{'publish__name': '人民出版社'}]> return HttpResponse("OK") """ 基于双下划线的跨表查询(join查询) 正向查询按照字段，反向查询按表名小写；用来告诉ORM引擎join哪张表 """
65-基于双下划线的跨表查询之一对多2
1、基于双下划线的跨表查询之一对多2；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------基于双下划线的跨表查询(join查询)---------------------------------------------------# #一对多查询的正向查询：查询金梅这本书的出版社的名字； #方式1: ret1 = Book.objects.filter(title="金梅").values("publish__name") print(ret1)#<QuerySet [{'publish__name': '人民出版社'}]> #方式2： ret2 = Publish.objects.filter(book__title="金梅").values("name") print(ret2) return HttpResponse("OK") """ 基于双下划线的跨表查询(join查询) 正向查询按照字段，反向查询按表名小写；用来告诉ORM引擎join哪张表 """
SQL语句；
--查询金瓶这本书的出版社的名字 select app01_publish.name from app01_book inner join app01_publish on app01_book.publish_id = app01_publish.nid where app01_book.title = "金梅"
66-基于双下划线的跨表查询之多对多1
1、基于双下划线的跨表查询之多对多1；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------基于双下划线的跨表查询(join查询)---------------------------------------------------# #一对多查询的正向查询：查询金梅这本书的出版社的名字； #方式1: ret1 = Book.objects.filter(title="金梅").values("publish__name") print(ret1)#<QuerySet [{'publish__name': '人民出版社'}]> #方式2： ret2 = Publish.objects.filter(book__title="金梅").values("name") print(ret2) #多对多查询：查询金梅这本书的所有作者的名字； #方式3： ret3 = Book.objects.filter(title="金梅").values("authors__name") print(ret3) return HttpResponse("OK") """ 基于双下划线的跨表查询(join查询) 正向查询按照字段，反向查询按表名小写；用来告诉ORM引擎join哪张表 """
SQL语句；
--需求：查询金梅这本书的所有作者的名字； select app01_author.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 where app01_book.title="金梅"
67-基于双下滑线的跨表查询之多对多2
1、基于双下划线的跨表查询之多对多2；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------基于双下划线的跨表查询(join查询)---------------------------------------------------# #一对多查询的正向查询：查询金梅这本书的出版社的名字； #方式1: ret1 = Book.objects.filter(title="金梅").values("publish__name") print(ret1)#<QuerySet [{'publish__name': '人民出版社'}]> #方式2： ret2 = Publish.objects.filter(book__title="金梅").values("name") print(ret2) #多对多查询： #查询金梅这本书的所有作者的名字； #需求：通过Book表join与其关联的Author表；属于正向查询，按照字段authors通知ORM引擎join book_authors与author #方式3： ret3 = Book.objects.filter(title="金梅").values("authors__name") print(ret3) #方式4： #需求：通过Author表join与其关联的Book表: ret4 = Author.objects.filter(book__title="金梅").values("name") print(ret4) return HttpResponse("OK") """ 基于双下划线的跨表查询(join查询) 正向查询按照字段，反向查询按表名小写；用来告诉ORM引擎join哪张表 """
操作日志：
System check identified no issues (0 silenced). (0.001) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.001) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() August 19, 2018 - 14:56:38 Django version 2.1, using settings 'ORM2.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. <QuerySet [{'publish__name': '人民出版社'}]> (0.000) SELECT "app01_publish"."name" FROM "app01_book" INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) <QuerySet [{'name': '人民出版社'}]> <QuerySet [{'authors__name': 'cxz'}]> <QuerySet [{'name': 'cxz'}]> (0.000) SELECT "app01_publish"."name" FROM "app01_publish" INNER JOIN "app01_book" ON ("app01_publish"."nid" = "app01_book"."publish_id") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) (0.000) SELECT "app01_author"."name" FROM "app01_book" LEFT OUTER JOIN "app01_book_authors" ON ("app01_book"."nid" = "app01_book_authors"."book_id") LEFT OUTER JOIN "app01_author" ON ("app01_book_authors"."author_id" = "app01_author"."nid") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) (0.000) SELECT "app01_author"."name" FROM "app01_author" INNER JOIN "app01_book_authors" ON ("app01_author"."nid" = "app01_book_authors"."author_id") INNER JOIN "app01_book" ON ("app01_book_authors"."book_id" = "app01_book"."nid") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) [19/Aug/2018 15:04:56] "GET /query/ HTTP/1.1" 200 2
68-基于双下滑线的跨表查询之一对一
1、基于双下滑线的跨表查询之一对一；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------基于双下划线的跨表查询(join查询)---------------------------------------------------# #一对多查询的正向查询：查询金梅这本书的出版社的名字； #方式1: ret1 = Book.objects.filter(title="金梅").values("publish__name") print(ret1)#<QuerySet [{'publish__name': '人民出版社'}]> #方式2： ret2 = Publish.objects.filter(book__title="金梅").values("name") print(ret2) #多对多查询： #查询金梅这本书的所有作者的名字； #需求：通过Book表join与其关联的Author表；属于正向查询，按照字段authors通知ORM引擎join book_authors与author #方式4： #方式3： ret3 = Book.objects.filter(title="金梅").values("authors__name") print(ret3) #需求：通过Author表join与其关联的Book表: ret4 = Author.objects.filter(book__title="金梅").values("name") print(ret4) #一对一的查询：查询cxz的手机号 #方式5：通过Author表与其关联的AuthorDetail表；属于正向查询，按照字段authordetail通知ORM引擎join AuthorDetail ret5 = Author.objects.filter(name="cxz").values("authordetail__telephone") print(ret5)#<QuerySet [{'authordetail__telephone': 138}]> #方式6：通过AuthorDetail表与其关联的Author表；属于反向查询，按照表名小写author通知ORM引擎join Author ret6 = AuthorDetail.objects.filter(author__name="cxz").values("telephone") print(ret6) return HttpResponse("OK") """ System check identified no issues (0 silenced). (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() August 19, 2018 - 15:12:42 Django version 2.1, using settings 'ORM2.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. <QuerySet [{'publish__name': '人民出版社'}]> (0.001) SELECT "app01_publish"."name" FROM "app01_book" INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."nid") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) <QuerySet [{'name': '人民出版社'}]> <QuerySet [{'authors__name': 'cxz'}]> <QuerySet [{'name': 'cxz'}]> <QuerySet [{'authordetail__telephone': 138}]> <QuerySet [{'telephone': 138}]> (0.000) SELECT "app01_publish"."name" FROM "app01_publish" INNER JOIN "app01_book" ON ("app01_publish"."nid" = "app01_book"."publish_id") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) (0.000) SELECT "app01_author"."name" FROM "app01_book" LEFT OUTER JOIN "app01_book_authors" ON ("app01_book"."nid" = "app01_book_authors"."book_id") LEFT OUTER JOIN "app01_author" ON ("app01_book_authors"."author_id" = "app01_author"."nid") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) (0.001) SELECT "app01_author"."name" FROM "app01_author" INNER JOIN "app01_book_authors" ON ("app01_author"."nid" = "app01_book_authors"."author_id") INNER JOIN "app01_book" ON ("app01_book_authors"."book_id" = "app01_book"."nid") WHERE "app01_book"."title" = '金梅' LIMIT 21; args=('金梅',) (0.000) SELECT "app01_authordetail"."telephone" FROM "app01_author" INNER JOIN "app01_authordetail" ON ("app01_author"."authordetail_id" = "app01_authordetail"."nid") WHERE "app01_author"."name" = 'cxz' LIMIT 21; args=('cxz',) (0.000) SELECT "app01_authordetail"."telephone" FROM "app01_authordetail" INNER JOIN "app01_author" ON ("app01_authordetail"."nid" = "app01_author"."authordetail_id") WHERE "app01_author"."name" = 'cxz' LIMIT 21; args=('cxz',) [19/Aug/2018 15:12:50] "GET /query/ HTTP/1.1" 200 2 """ """ 基于双下划线的跨表查询(join查询) 正向查询按照字段，反向查询按表名小写；用来告诉ORM引擎join哪张表 """
69-基于双下划线的跨表查询之连续跨表1
# 进阶练习: # 练习: 手机号以110开头的作者出版过的所有书籍名称以及书籍出版社名称 # 方式1: # 需求: 通过Book表join AuthorDetail表, Book与AuthorDetail无关联,所以必需连续跨表； # ret=Book.objects.filter(authors__authordetail__telephone__startswith="110").values("title","publish__name") # print(ret) # # # 方式2: # ret=Author.objects.filter(authordetail__telephone__startswith="110").values("book__title","book__publish__name") # print(ret)
70-基于双下划线的跨表查询之连续跨表2
71-聚合查询
1、ORM下的聚合查询；
- Avg()；
- Count()；
- Max()；
- Min()；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print(ret)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} return HttpResponse("查询成功！") """ """
操作日志：
"D:\Program\PyCharm 2018.1.4\bin\runnerw.exe" C:\Users\TQTL911\PycharmProjects\ORM2\venv\Scripts\python.exe C:/Users/TQTL911/PycharmProjects/ORM2/manage.py runserver 8000 Performing system checks... System check identified no issues (0 silenced). (0.002) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() August 19, 2018 - 17:07:15 Django version 2.1, using settings 'ORM2.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. (0.000) SELECT AVG("app01_book"."price") AS "avg_price", CAST(MAX("app01_book"."price") AS NUMERIC) AS "max_price", CAST(MIN("app01_book"."price") AS NUMERIC) AS "Min_price" FROM "app01_book"; args=() {'avg_price': 100.0, 'max_price': Decimal('100'), 'Min_price': Decimal('100')} [19/Aug/2018 17:07:16] "GET /query/ HTTP/1.1" 200 15 Not Found: /favicon.ico [19/Aug/2018 17:07:16] "GET /favicon.ico HTTP/1.1" 404 2076 {'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} (0.000) SELECT AVG("app01_book"."price") AS "avg_price", CAST(MAX("app01_book"."price") AS NUMERIC) AS "max_price", CAST(MIN("app01_book"."price") AS NUMERIC) AS "Min_price" FROM "app01_book"; args=() [19/Aug/2018 17:07:49] "GET /query/ HTTP/1.1" 200 15 Performing system checks... System check identified no issues (0 silenced). (0.001) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() August 19, 2018 - 17:08:20 Django version 2.1, using settings 'ORM2.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. Performing system checks... System check identified no issues (0 silenced). August 19, 2018 - 17:25:28 Django version 2.1, using settings 'ORM2.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=()
72-单表下的分组查询1
1、单表下的分组查询1；
README.md文件；
#####################################--单表的分组查询--####################################################### 单表的分组查询： #查询每一个部门名称以及对应的员工数； emp: id name age salary dep 1 alex 12 2000 销售部 2 egon 22 3000 人事部 3 wen 22 5000 人事部 SQL语句： select dep,Count(*) from emp group by dep; 思考：如使使用Django自带的ORM进行分组查询？
models.py,不能忘记执行迁移数据库命令；
from django.db import models # Create your models here. class Emp(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() salary = models.DecimalField(max_digits=8,decimal_places=2) dep = models.CharField(max_length=32) province =models.CharField(max_length=32)
1）执行迁移数据库命令；
Microsoft Windows [版本 10.0.17134.1] (c) 2018 Microsoft Corporation。保留所有权利。 (venv) C:\Users\TQTL911\PycharmProjects\ORM2>python manage.py makemigrations (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() Migrations for 'app01': app01\migrations\0002_emp.py - Create model Emp (venv) C:\Users\TQTL911\PycharmProjects\ORM2>python manage.py migrate (0.001) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() Operations to perform: Apply all migrations: admin, app01, auth, contenttypes, sessions Running migrations: (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) PRAGMA foreign_keys = OFF; args=None (0.000) BEGIN; args=None CREATE TABLE "app01_emp" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(32) NOT NULL, "age" integer NOT NULL, "salary" decimal NOT NULL, "dep" varchar(32) NOT NULL, "province" varchar(32) NOT NULL); (params None) (0.002) CREATE TABLE "app01_emp" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(32) NOT NULL, "age" integer NOT NULL, "salary" decimal NOT NULL, "dep" varchar(32) NOT NULL, "province" varc har(32) NOT NULL); args=None (0.000) PRAGMA foreign_keys = ON; args=None (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.000) BEGIN; args=None (0.001) INSERT INTO "django_migrations" ("app", "name", "applied") VALUES ('app01', '0002_emp', '2018-08-19 10:12:31.599829'); args=['app01', '0002_emp', '2018-08-19 10:12:31.599829'] Applying app01.0002_emp... OK (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.001) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'admin' AND "django_content_ type"."model" = 'logentry'); args=('admin', 'logentry') (0.000) SELECT "auth_permission"."content_type_id", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "auth_permission"."content_type_id" IN (1) ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC; args=(1,) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE "django_content_type"."app_label" = 'admin'; args=('admin',) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'auth' AND "django_content_t ype"."model" = 'permission'); args=('auth', 'permission') (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'auth' AND "django_content_t ype"."model" = 'group'); args=('auth', 'group') (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'auth' AND "django_content_t ype"."model" = 'user'); args=('auth', 'user') (0.000) SELECT "auth_permission"."content_type_id", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "auth_permission"."content_type_id" IN (2, 3, 4) ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC; args=(2, 3, 4) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE "django_content_type"."app_label" = 'auth'; args=('auth',) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'contenttypes' AND "django_c ontent_type"."model" = 'contenttype'); args=('contenttypes', 'contenttype') (0.000) SELECT "auth_permission"."content_type_id", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "auth_permission"."content_type_id" IN (5) ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC; args=(5,) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE "django_content_type"."app_label" = 'contenttypes'; args=('conten ttypes',) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'sessions' AND "django_conte nt_type"."model" = 'session'); args=('sessions', 'session') (0.000) SELECT "auth_permission"."content_type_id", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "auth_permission"."content_type_id" IN (6) ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC; args=(6,) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE "django_content_type"."app_label" = 'sessions'; args=('sessions', ) (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'app01' AND "django_content_ type"."model" = 'author'); args=('app01', 'author') (0.001) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'app01' AND "django_content_ type"."model" = 'authordetail'); args=('app01', 'authordetail') (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'app01' AND "django_content_ type"."model" = 'book'); args=('app01', 'book') (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'app01' AND "django_content_ type"."model" = 'publish'); args=('app01', 'publish') (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'app01' AND "django_content_ type"."model" = 'emp'); args=('app01', 'emp') (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE ("django_content_type"."app_label" = 'app01' AND "django_content_ type"."model" = 'emp'); args=('app01', 'emp') (0.000) BEGIN; args=None (0.001) INSERT INTO "django_content_type" ("app_label", "model") VALUES ('app01', 'emp'); args=['app01', 'emp'] (0.001) SELECT "auth_permission"."content_type_id", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "auth_permission"."content_type_id" IN (7, 8, 9, 10, 11) ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC; args=(7, 8, 9, 10, 11) (0.000) BEGIN; args=None (0.001) INSERT INTO "auth_permission" ("name", "content_type_id", "codename") SELECT 'Can add emp', 11, 'add_emp' UNION ALL SELECT 'Can change emp', 11, 'change_emp' UNION ALL SELECT 'Can delete emp', 11, ' delete_emp' UNION ALL SELECT 'Can view emp', 11, 'view_emp'; args=('Can add emp', 11, 'add_emp', 'Can change emp', 11, 'change_emp', 'Can delete emp', 11, 'delete_emp', 'Can view emp', 11, 'view_emp') (0.000) SELECT "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" FROM "django_content_type" WHERE "django_content_type"."app_label" = 'app01'; args=('app01',) (venv) C:\Users\TQTL911\PycharmProjects\ORM2>
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3) return HttpResponse("查询成功！") """ """
操作日志：
System check identified no issues (0 silenced). August 19, 2018 - 18:25:45 Django version 2.1, using settings 'ORM2.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. (0.000) SELECT name, type FROM sqlite_master WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' ORDER BY name; args=None (0.001) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=() (0.001) SELECT AVG("app01_book"."price") AS "avg_price", CAST(MAX("app01_book"."price") AS NUMERIC) AS "max_price", CAST(MIN("app01_book"."price") AS NUMERIC) AS "Min_price" FROM "app01_book"; args=() 查询所有书籍的平均价格； {'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} (0.000) SELECT "app01_emp"."dep", AVG("app01_emp"."salary") AS "avg_salary" FROM "app01_emp" GROUP BY "app01_emp"."dep" LIMIT 21; args=() 查询每一个部门的名称以及员工的平均薪水: <QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> (0.000) SELECT "app01_emp"."province", COUNT("app01_emp"."id") AS "c" FROM "app01_emp" GROUP BY "app01_emp"."province" LIMIT 21; args=() 查询每一个身份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> [19/Aug/2018 18:25:49] "GET /query/ HTTP/1.1" 200 15
73-单表下的分组查询2
1、单表下的分组查询2；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3)#查询每一个省份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> #补充知识点： ret4 = Emp.objects.all() print(ret4)#select * from emp; ret5 = Emp.objects.all().values("name") print(ret5)#select name from emp; return HttpResponse("查询成功！") """ 小结： 1、在单表分组下，按照主键分组，是没有意义的！ """
74-多表下的分组查询1
75-多表下的分组查询2
1、多表下的分组查询2；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3)#查询每一个省份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> #补充知识点： ret4 = Emp.objects.all() print(ret4)#select * from emp; ret5 = Emp.objects.all().values("name") print(ret5)#select name from emp; #2 查询每一个出版社的名称以及出版的书籍的个数； ret6 = Publish.objects.values("nid").annotate(c = Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret6)#查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'nid': 1, 'c': 4}, {'nid': 2, 'c': 0}]> ret7 = Publish.objects.values("name").annotate(c=Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret7) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}]> ret8 = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c") print("查询每一个出版社的名称以及出版的书籍的个数", ret8) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}, {'name': '人民出版社', 'c': 0}]> return HttpResponse("查询成功！") """ 1 查询每一个出版社的书籍的个数 Book.objects.values("publish_id").annotate(Count("id")) 2 查询每一个出版社的名称以及出版的书籍的个数 Join SQL:select * from Book inner join Publish on book.publish_id = publish.id id title date price publish_id publish.id publish.name publish.addr publish.email 1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 3 三国演义 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 4 金瓶 2012-12-12 101 1 1 南京民出版社 南京 345@163.com 分组查询的SQL语句： select publish.name,Count("title") from Book inner join Publish on book.publish_id = publish.id group by publish.id; 思考：如何使用Django 的ORM进行书写查询语句，进行跨表分组查询； 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) 小结： 1、在单表分组下，按照主键分组，是没有意义的！ 1、在多表分组下，按照主键分组，是有意义的！ """
76-多表下的分组查询3
1、多表下的分组查询3；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3)#查询每一个省份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> #补充知识点： ret4 = Emp.objects.all() print(ret4)#select * from emp; ret5 = Emp.objects.all().values("name") print(ret5)#select name from emp; #2 查询每一个出版社的名称以及出版的书籍的个数 ret6 = Publish.objects.values("nid").annotate(c = Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret6)#查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'nid': 1, 'c': 4}, {'nid': 2, 'c': 0}]> ret7 = Publish.objects.values("name").annotate(c=Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret7) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}]> ret8 = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c") print("查询每一个出版社的名称以及出版的书籍的个数", ret8) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}, {'name': '人民出版社', 'c': 0}]> #查询每个作者的名字以及出版过的书籍的最高价格； ret9 = Author.objects.values("pk").annotate(max_price = Max("book__price")).values("name","max_price") print("查询每个作者的名字以及出版过的书籍的最高价格；",ret9)#查询每个作者的名字以及出版过的书籍的最高价格； <QuerySet [{'name': 'alex', 'max_price': Decimal('301')}, {'name': 'egon', 'max_price': Decimal('301')}]> return HttpResponse("查询成功！") """ 1 查询每一个出版社的书籍的个数 Book.objects.values("publish_id").annotate(Count("id")) 2 查询每一个出版社的名称以及出版的书籍的个数 Join SQL:select * from Book inner join Publish on book.publish_id = publish.id id title date price publish_id publish.id publish.name publish.addr publish.email 1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 3 三国演义 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 4 金瓶 2012-12-12 101 1 1 南京民出版社 南京 345@163.com 分组查询的SQL语句： select publish.name,Count("title") from Book inner join Publish on book.publish_id = publish.id group by publish.id; 思考：如何使用Django 的ORM进行书写查询语句，进行跨表分组查询； 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) 跨表的分组查询模型： 每个后面表的模型.objects.values("pk).annotate(聚合函数(关联表__统计字段)).values("xxx","xxx") 小结： 1、在单表分组下，按照主键分组，是没有意义的！ 1、在多表分组下，按照主键分组，是有意义的！ """
原生SQL语句：
--查询每个作者的名字以及出版过的书籍的最高价格； SELECT MAX(app01_book.price),app01_author.name FROM app01_book INNER JOIN app01_book_authors ON app01_book.nid = app01_book_authors.book_id INNER JOIN app01_author ON app01_author.nid=app01_book_authors.author_id
77-多表下的分组查询4
1、多表下的分组查询4；
理解的时候建议使用原生SQL模拟，查询的时候，使用ORM；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3)#查询每一个省份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> #补充知识点： ret4 = Emp.objects.all() print(ret4)#select * from emp; ret5 = Emp.objects.all().values("name") print(ret5)#select name from emp; #示例2 查询每一个出版社的名称以及出版的书籍的个数 ret6 = Publish.objects.values("nid").annotate(c = Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret6)#查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'nid': 1, 'c': 4}, {'nid': 2, 'c': 0}]> ret7 = Publish.objects.values("name").annotate(c=Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret7) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}]> ret8 = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c") print("查询每一个出版社的名称以及出版的书籍的个数", ret8) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}, {'name': '人民出版社', 'c': 0}]> #查询每个作者的名字以及出版过的书籍的最高价格； ret9 = Author.objects.values("pk").annotate(max_price = Max("book__price")).values("name","max_price") print("查询每个作者的名字以及出版过的书籍的最高价格；",ret9)#查询每个作者的名字以及出版过的书籍的最高价格； <QuerySet [{'name': 'alex', 'max_price': Decimal('301')}, {'name': 'egon', 'max_price': Decimal('301')}]> #示例3：查询每一个书籍的名称以及对应的作者的个数； ret10 = Book.objects.values("pk").annotate(c = Count("authors__name")).values("title","c")#<QuerySet [{'title': '红楼梦', 'c': 0}, {'title': '西游记', 'c': 0}, {'title': '三国演绎', 'c': 2}, {'title': '金梅', 'c': 2}]> print(ret10) return HttpResponse("查询成功！") """ 1 查询每一个出版社的书籍的个数 Book.objects.values("publish_id").annotate(Count("id")) 2 查询每一个出版社的名称以及出版的书籍的个数 Join SQL:select * from Book inner join Publish on book.publish_id = publish.id id title date price publish_id publish.id publish.name publish.addr publish.email 1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 3 三国演义 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 4 金瓶 2012-12-12 101 1 1 南京民出版社 南京 345@163.com 分组查询的SQL语句： select publish.name,Count("title") from Book inner join Publish on book.publish_id = publish.id group by publish.id; 思考：如何使用Django 的ORM进行书写查询语句，进行跨表分组查询； 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) 跨表的分组查询模型： 每个后面表的模型.objects.values("pk).annotate(聚合函数(关联表__统计字段)).values("xxx","xxx") 小结： 1、在单表分组下，按照主键分组，是没有意义的！ 1、在多表分组下，按照主键分组，是有意义的！ """
原生SQL；
--#示例3：查询每一个书籍的名称以及对应的作者的个数； select app01_book.title,COUNT(app01_author.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 group by app01_book.nid
78-多表下的分组查询5
1、多表下的分组查询5；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3)#查询每一个省份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> #补充知识点： ret4 = Emp.objects.all() print(ret4)#select * from emp; ret5 = Emp.objects.all().values("name") print(ret5)#select name from emp; #示例2 查询每一个出版社的名称以及出版的书籍的个数 ret6 = Publish.objects.values("nid").annotate(c = Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret6)#查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'nid': 1, 'c': 4}, {'nid': 2, 'c': 0}]> ret7 = Publish.objects.values("name").annotate(c=Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret7) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}]> ret8 = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c") print("查询每一个出版社的名称以及出版的书籍的个数", ret8) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}, {'name': '人民出版社', 'c': 0}]> #查询每个作者的名字以及出版过的书籍的最高价格； ret9 = Author.objects.values("pk").annotate(max_price = Max("book__price")).values("name","max_price") print("查询每个作者的名字以及出版过的书籍的最高价格；",ret9)#查询每个作者的名字以及出版过的书籍的最高价格； <QuerySet [{'name': 'alex', 'max_price': Decimal('301')}, {'name': 'egon', 'max_price': Decimal('301')}]> #示例3：查询每一个书籍的名称以及对应的作者的个数； ret10 = Book.objects.values("pk").annotate(c = Count("authors__name")).values("title","c")#<QuerySet [{'title': '红楼梦', 'c': 0}, {'title': '西游记', 'c': 0}, {'title': '三国演绎', 'c': 2}, {'title': '金梅', 'c': 2}]> print(ret10) #------------------跨表分组查询的另外一种玩法------------------------# #示例1：查询每一个出版社的名称以及出版的书籍的个数； ret11 = Publish.objects.values("nid").annotate(c = Count("book__title")).values("name","email") print(ret11)#<QuerySet [{'name': '人民出版社', 'email': '123@qq.com'}, {'name': '南京出版社', 'email': '345@163.com'}]> ret12 = Publish.objects.all().annotate(c = Count("book__title")).values("name","c","city") print(ret12)#<QuerySet [{'name': '人民出版社', 'city': '北京', 'c': 3}, {'name': '南京出版社', 'city': '南京', 'c': 1}]> return HttpResponse("查询成功！") """ 1 查询每一个出版社的书籍的个数 Book.objects.values("publish_id").annotate(Count("id")) 2 查询每一个出版社的名称以及出版的书籍的个数 Join SQL:select * from Book inner join Publish on book.publish_id = publish.id id title date price publish_id publish.id publish.name publish.addr publish.email 1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 3 三国演义 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 4 金瓶 2012-12-12 101 1 1 南京民出版社 南京 345@163.com 分组查询的SQL语句： select publish.name,Count("title") from Book inner join Publish on book.publish_id = publish.id group by publish.id; 思考：如何使用Django 的ORM进行书写查询语句，进行跨表分组查询； 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) 跨表的分组查询模型： 每个后面的表模型.objects.values("pk).annotate(聚合函数(关联表__统计字段)).values("xxx","xxx") 每个后面的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的素有字段以及统计字段") 小结： 1、在单表分组下，按照主键分组，是没有意义的！ 1、在多表分组下，按照主键分组，是有意义的！ """
79-多表下的分组查询6
1、多表下的分组查询6；
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3)#查询每一个省份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> #补充知识点： ret4 = Emp.objects.all() print(ret4)#select * from emp; ret5 = Emp.objects.all().values("name") print(ret5)#select name from emp; #示例2 查询每一个出版社的名称以及出版的书籍的个数 ret6 = Publish.objects.values("nid").annotate(c = Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret6)#查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'nid': 1, 'c': 4}, {'nid': 2, 'c': 0}]> ret7 = Publish.objects.values("name").annotate(c=Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret7) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}]> ret8 = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c") print("查询每一个出版社的名称以及出版的书籍的个数", ret8) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}, {'name': '人民出版社', 'c': 0}]> #查询每个作者的名字以及出版过的书籍的最高价格； ret9 = Author.objects.values("pk").annotate(max_price = Max("book__price")).values("name","max_price") print("查询每个作者的名字以及出版过的书籍的最高价格；",ret9)#查询每个作者的名字以及出版过的书籍的最高价格； <QuerySet [{'name': 'alex', 'max_price': Decimal('301')}, {'name': 'egon', 'max_price': Decimal('301')}]> #示例3：查询每一个书籍的名称以及对应的作者的个数； ret10 = Book.objects.values("pk").annotate(c = Count("authors__name")).values("title","c")#<QuerySet [{'title': '红楼梦', 'c': 0}, {'title': '西游记', 'c': 0}, {'title': '三国演绎', 'c': 2}, {'title': '金梅', 'c': 2}]> print(ret10) #------------------------------------------------跨表分组查询的另外一种玩法-----------------------------------------# #示例1：查询每一个出版社的名称以及出版的书籍的个数； ret11 = Publish.objects.values("nid").annotate(c = Count("book__title")).values("name","email") print(ret11)#<QuerySet [{'name': '人民出版社', 'email': '123@qq.com'}, {'name': '南京出版社', 'email': '345@163.com'}]> ret12 = Publish.objects.all().annotate(c = Count("book__title")).values("name","c","city") print(ret12)#<QuerySet [{'name': '人民出版社', 'city': '北京', 'c': 3}, {'name': '南京出版社', 'city': '南京', 'c': 1}]> #--------------------------------------------练习题------------------------------------------------# #统计每一个以py开头的书籍的作者的个数 ret13 = Book.objects.filter(title__startswith="py").values("pk").annotate(c = Count("authors__name")).values("title","c") print(ret13)#<QuerySet []> ret14 = Book.objects.values("pk").annotate(c = Count("authors__name")).filter(c__gt = 1).values("title","c") print(ret14)#<QuerySet [{'title': '三国演绎', 'c': 2}, {'title': '金梅', 'c': 2}]> return HttpResponse("查询成功！") """ 1 查询每一个出版社的书籍的个数 Book.objects.values("publish_id").annotate(Count("id")) 2 查询每一个出版社的名称以及出版的书籍的个数 Join SQL:select * from Book inner join Publish on book.publish_id = publish.id id title date price publish_id publish.id publish.name publish.addr publish.email 1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 3 三国演义 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 4 金瓶 2012-12-12 101 1 1 南京民出版社 南京 345@163.com 分组查询的SQL语句： select publish.name,Count("title") from Book inner join Publish on book.publish_id = publish.id group by publish.id; 思考：如何使用Django 的ORM进行书写查询语句，进行跨表分组查询； 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) 跨表的分组查询模型： 每个后面的表模型.objects.values("pk).annotate(聚合函数(关联表__统计字段)).values("xxx","xxx") 每个后面的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的素有字段以及统计字段") 小结： 1、在单表分组下，按照主键分组，是没有意义的！ 1、在多表分组下，按照主键分组，是有意义的！ """
分组查询的总结；
A-B 关联属性在A表中 正向查询: A------>B 反向查询: B------>A 基于对象的跨表查询(子查询) # 一对多查询 正向查询:按字段 反向查询:表名小写_set.all() book_obj.publish Book(关联属性:publish)对象 --------------> Publish对象 <-------------- publish_obj.book_set.all() # queryset # 多对多查询 正向查询:按字段 反向查询:表名小写_set.all() book_obj.authors.all() Book(关联属性:authors)对象 ------------------------> Author对象 <------------------------ author_obj.book_set.all() # queryset # 一对一查询 正向查询:按字段 反向查询:表名小写 author.authordetail Author(关联属性:authordetail)对象 ------------------------>AuthorDetail对象 <------------------------ authordetail.author 基于双下划线的跨表查询(join查询) key:正向查询按字段,反向查询按表名小写 单表的分组查询: 查询每一个部门名称以及对应的员工数 emp: id name age salary dep 1 alex 12 2000 销售部 2 egon 22 3000 人事部 3 wen 22 5000 人事部 sql : select Count(id) from emp group by dep; 思考:如何用ORM语法进行分组查询? # 单表分组查询的ORM语法: 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) 在单表分组下,按着主键进行group by是没有任何意义的. 跨表的分组查询: Book表 id title date price publish_id 1 红楼梦 2012-12-12 101 1 2 西游记 2012-12-12 101 1 3 三国演绎 2012-12-12 101 1 4 金梅 2012-12-12 301 2 Publish表 id name addr email 1 人民出版社 北京 123@qq.com 2 南京出版社 南京 345@163.com 1 查询每一个出版社出版的书籍个数 Book.objects.values("publish_id").annotate(Count("id")) 2 示例1 查询每一个出版社的名称以及出版的书籍个数 join sql : select * from Book inner join Publish on book.publish_id=publish.id id title date price publish_id publish.id publish.name publish.addr publish.email 1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 3 三国演绎 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 4 金梅 2012-12-12 301 2 2 南京出版社 南京 345@163.com 分组查询sql: select publish.name,Count("title") from Book inner join Publish on book.publish_id=publish.id group by publish.id,publish.name,publish.addr,publish.email 思考:如何用ORM语法进行跨表分组查询 ret=Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c") print(ret) 3 示例2 查询每一个作者的名字以及出版过的书籍的最高价格 ret=Author.objects.values("pk").annotate(max_price=Max("book__price")).values("name","max_price") 4 示例3 查询每一个书籍的名称以及对应的作者个数 ret=Book.objects.values("pk").annotate(c=Count("authors__name")).values("title","c") print(ret) 5 总结: # 总结 跨表的分组查询的模型: # 每一个后的表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段") # 每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
80-F查询和Q查询
1、models.py中，class新增字段，建议添加default=0；
from django.db import models # Create your models here. from django.db import models """ Book--------Publish,一对多的关系； AuthorDetail--------Author，一对一的关系，关联字段建立在哪里都行； Book2Author,多对多关系； """ #作者详情表； class AuthorDetail(models.Model): nid = models.AutoField(primary_key=True) birthday = models.DateField() telephone = models.BigIntegerField() addr = models.CharField(max_length=64) #作者表； class Author(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() authordetail = models.OneToOneField(to="AuthorDetail",to_field="nid",on_delete=models.CASCADE) def __str__(self): return self.name #出版社表； 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() def __str__(self): return self.name
#xxx表； class Emp(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() salary = models.DecimalField(max_digits=8,decimal_places=2) dep = models.CharField(max_length=32) province =models.CharField(max_length=32) #书籍表； 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)
#新增字段； read_num = models.IntegerField(default=0) comment_num = models.IntegerField(default=0) #一对多的关系； publish =models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE) def __str__(self): return self.title """ 数据库迁移的时候，会翻译这两句话； publish_id INT, FOREIGN KEY (publish_id) REFERENCES publish(id) """ #一对多的关系 authors = models.ManyToManyField(to="Author") """ #CREATE TABLE book2author( CREATE TABLE book_authors( id INT PRIMAEY KEY auto_increment, book_id INT, author_id INT, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGB KEY (autor_id) REFERENCES author(id) ); """ #绑定多对多关系,不这么去创建； # class Book2Author(models.Model): # nid = models.AutoField(primary_key=True) # book = models.ForeignKey(to="Book") # author = models.ForeignKey(to="Author")
再次重新执行数据库迁移操作语句：
Python manage.py makemigrations
python manage.py migrate
2、F和Q查询的使用场景；
from django.shortcuts import render,HttpResponse # Create your views here. from app01.models import * def query(request): #---------------------------------------聚合与分组查询------------------------------------------------------------------# #-------------------------------------->聚合，aggregate：返回值是一个字段，不再是queryset #查询所有书籍的平均价格； from django.db.models import Avg,Count,Max,Min ret1 = Book.objects.all().aggregate(avg_price = Avg("price"),max_price = Max("price"),Min_price = Min("price")) print("查询所有书籍的平均价格；",ret1)#{'avg_price': 71.0, 'max_price': Decimal('100'), 'Min_price': Decimal('38')} #----------------------------------------------分组查询annotate,返回值queryset---------------------------------------# #示例1： #单表的分组查询： #查询每一个部门的名称以及员工的平均薪水！ #SQL语句:select dep,Avg(salary) from emp group by dep; #单表分组查询的ORM语法：单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) ret2 = Emp.objects.values("dep").annotate(avg_salary = Avg("salary")) print("查询每一个部门的名称以及员工的平均薪水:",ret2)#<QuerySet [{'dep': '保安部', 'avg_salary': 5000.0}, {'dep': '教学部', 'avg_salary': 51000.0}]> #示例2：查询每一个省份的名称，以及对应的员工数； ret3 = Emp.objects.values("province").annotate(c = Count("id")) print("查询每一个省份的名称，以及对应的员工数；",ret3)#查询每一个省份的名称，以及对应的员工数； <QuerySet [{'province': '山东省', 'c': 2}, {'province': '河北省', 'c': 1}]> #补充知识点： ret4 = Emp.objects.all() print(ret4)#select * from emp; ret5 = Emp.objects.all().values("name") print(ret5)#select name from emp; #示例2 查询每一个出版社的名称以及出版的书籍的个数 ret6 = Publish.objects.values("nid").annotate(c = Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret6)#查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'nid': 1, 'c': 4}, {'nid': 2, 'c': 0}]> ret7 = Publish.objects.values("name").annotate(c=Count("book__title")) print("查询每一个出版社的名称以及出版的书籍的个数",ret7) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}]> ret8 = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c") print("查询每一个出版社的名称以及出版的书籍的个数", ret8) # 查询每一个出版社的名称以及出版的书籍的个数 <QuerySet [{'name': '人民出版社', 'c': 4}, {'name': '人民出版社', 'c': 0}]> #查询每个作者的名字以及出版过的书籍的最高价格； ret9 = Author.objects.values("pk").annotate(max_price = Max("book__price")).values("name","max_price") print("查询每个作者的名字以及出版过的书籍的最高价格；",ret9)#查询每个作者的名字以及出版过的书籍的最高价格； <QuerySet [{'name': 'alex', 'max_price': Decimal('301')}, {'name': 'egon', 'max_price': Decimal('301')}]> #示例3：查询每一个书籍的名称以及对应的作者的个数； ret10 = Book.objects.values("pk").annotate(c = Count("authors__name")).values("title","c")#<QuerySet [{'title': '红楼梦', 'c': 0}, {'title': '西游记', 'c': 0}, {'title': '三国演绎', 'c': 2}, {'title': '金梅', 'c': 2}]> print(ret10) #------------------------------------------------跨表分组查询的另外一种玩法-----------------------------------------# #示例1：查询每一个出版社的名称以及出版的书籍的个数； ret11 = Publish.objects.values("nid").annotate(c = Count("book__title")).values("name","email") print(ret11)#<QuerySet [{'name': '人民出版社', 'email': '123@qq.com'}, {'name': '南京出版社', 'email': '345@163.com'}]> ret12 = Publish.objects.all().annotate(c = Count("book__title")).values("name","c","city") print(ret12)#<QuerySet [{'name': '人民出版社', 'city': '北京', 'c': 3}, {'name': '南京出版社', 'city': '南京', 'c': 1}]> #--------------------------------------------练习题------------------------------------------------# #统计每一个以py开头的书籍的作者的个数 ret13 = Book.objects.filter(title__startswith="py").values("pk").annotate(c = Count("authors__name")).values("title","c") print(ret13)#<QuerySet []> ret14 = Book.objects.values("pk").annotate(c = Count("authors__name")).filter(c__gt = 1).values("title","c") print(ret14)#<QuerySet [{'title': '三国演绎', 'c': 2}, {'title': '金梅', 'c': 2}]> #---------------------------------------F查询和Q查询------------------------------------------------------# from django.db.models import F,Q ret15 = Book.objects.filter(comment_num__gt=F("read_num")) print(ret15) ret16 = Book.objects.all().update(price=F("price")+2) print("图书价格上涨2元每本",ret16) ret17 = Book.objects.filter(Q(title = "红楼梦")|Q(price = 100)) #ret17 = Book.objects.filter(Q(title = "红楼梦")&Q(price = 100)) #ret17 = Book.objects.filter(~Q(title = "红楼梦")&~Q(price = 100)) ret17 = Book.objects.filter(Q(title = "红楼梦")|Q(price = 100)) #先放置Q，再放置其他字段，注意先后顺序 ret17 = Book.objects.filter(Q(title = "红楼梦")|Q(price = 100),comment_num=100) print(ret17) return HttpResponse("查询成功！") """ 1 查询每一个出版社的书籍的个数 Book.objects.values("publish_id").annotate(Count("id")) 2 查询每一个出版社的名称以及出版的书籍的个数 Join SQL:select * from Book inner join Publish on book.publish_id = publish.id id title date price publish_id publish.id publish.name publish.addr publish.email 1 红楼梦 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 2 西游记 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 3 三国演义 2012-12-12 101 1 1 人民出版社 北京 123@qq.com 4 金瓶 2012-12-12 101 1 1 南京民出版社 南京 345@163.com 分组查询的SQL语句： select publish.name,Count("title") from Book inner join Publish on book.publish_id = publish.id group by publish.id; 思考：如何使用Django 的ORM进行书写查询语句，进行跨表分组查询； 单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段")) 跨表的分组查询模型： 每个后面的表模型.objects.values("pk).annotate(聚合函数(关联表__统计字段)).values("xxx","xxx") 每个后面的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的素有字段以及统计字段") 小结： 1、在单表分组下，按照主键分组，是没有意义的！ 1、在多表分组下，按照主键分组，是有意义的！ """
81-多表操作的章节作业布置-基于多表的图书管理系统
1、项目介绍；
82-基于多表的图书管理系统添加功能1
1、项目操作步骤：https://www.processon.com/view/link/5b7a420ee4b0f8477db9c3b0
settings.py;
""" Django settings for bookms_02 project. Generated by 'django-admin startproject' using Django 2.1. For more information on this file, see https://docs.djangoproject.com/en/2.1/topics/settings/ For the full list of settings and their values, see https://docs.djangoproject.com/en/2.1/ref/settings/ """ import os # Build paths inside the project like this: os.path.join(BASE_DIR, ...) BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) # Quick-start development settings - unsuitable for production # See https://docs.djangoproject.com/en/2.1/howto/deployment/checklist/ # SECURITY WARNING: keep the secret key used in production secret! SECRET_KEY = '0e$((-gvw%926f7$^17cln1n+#k2*7g^3aectpb^r!z157*vn&' # SECURITY WARNING: don't run with debug turned on in production! DEBUG = True ALLOWED_HOSTS = [] # Application definition INSTALLED_APPS = [ 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'book.apps.BookConfig', ] MIDDLEWARE = [ 'django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware', ] ROOT_URLCONF = 'bookms_02.urls' TEMPLATES = [ { 'BACKEND': 'django.template.backends.django.DjangoTemplates', 'DIRS': [os.path.join(BASE_DIR, 'templates')] , 'APP_DIRS': True, 'OPTIONS': { 'context_processors': [ 'django.template.context_processors.debug', 'django.template.context_processors.request', 'django.contrib.auth.context_processors.auth', 'django.contrib.messages.context_processors.messages', ], }, }, ] WSGI_APPLICATION = 'bookms_02.wsgi.application' # Database # https://docs.djangoproject.com/en/2.1/ref/settings/#databases DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlite3', 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'), } } # Password validation # https://docs.djangoproject.com/en/2.1/ref/settings/#auth-password-validators AUTH_PASSWORD_VALIDATORS = [ { 'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator', }, { 'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator', }, { 'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator', }, { 'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator', }, ] # Internationalization # https://docs.djangoproject.com/en/2.1/topics/i18n/ LANGUAGE_CODE = 'en-us' TIME_ZONE = 'UTC' USE_I18N = True USE_L10N = True USE_TZ = True # Static files (CSS, JavaScript, Images) # https://docs.djangoproject.com/en/2.1/howto/static-files/ STATIC_URL = '/static/' STATICFILES_DIRS = [ os.path.join(BASE_DIR,"static") ]
urls.py;
"""bookms_02 URL Configuration The `urlpatterns` list routes URLs to views. For more information please see: https://docs.djangoproject.com/en/2.1/topics/http/urls/ Examples: Function views 1. Add an import: from my_app import views 2. Add a URL to urlpatterns: path('', views.home, name='home') Class-based views 1. Add an import: from other_app.views import Home 2. Add a URL to urlpatterns: path('', Home.as_view(), name='home') Including another URLconf 1. Import the include() function: from django.urls import include, path 2. Add a URL to urlpatterns: path('blog/', include('blog.urls')) """ from django.contrib import admin from django.urls import path from book import views urlpatterns = [ path('admin/', admin.site.urls), path('book/add', views.add_book), ]
views.py;
from django.shortcuts import render # Create your views here. from .models import Publish,Author #添加书籍； def add_book(request): publish_list = Publish.objects.all() author_list = Publish.objects.all() return render(request,"addbook.html",{"author_list":author_list,"publish_list":publish_list})
models.py;
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() #出版社； 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)#999.99 #与Publish表建立"一对多"的关系，外键字段建立在多的一方； publish = models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE) #与Author表建立多对多的关系，ManyToManyField可以建立两个模型中的任意一个，自动创建第三张表表； authors = models.ManyToManyField(to="Author",)
addbook.html;
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>添加书籍</title> <!-- Bootstrap --> <link rel="stylesheet" type="text/css" href="/static/bootstrap-3.3.7/dist/css/bootstrap.css"> <!-- Bootstrap --> <!--<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">--> </head> <body> <h3>添加书籍</h3> <div class="container"> <div class="row"> <div class="col-md-6 col-md-offset-3"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="">名称</label> <input type="text" name="title" class="form-control"> </div> <div class="form-group"> <label for="">价格</label> <input type="text" name="price" class="form-control"> </div> <div class="form-group"> <label for="">出版日期</label> <input type="text" name="pub_date" class="form-control"> </div> <div class="form-group"> <label for="" >出版社</label > <select name="publish" id="" class="form-control"> {% for publish in publish_list %} <option value="{{ publish.pk }}">{{ publish.name }}</option> {% endfor %} </select> </div> <div class="form-group"> <label for="">作者</label> <select name="authors" type="text" id="" multiple class="form-control"> {% for author in author_list %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endfor %} </select> </div> <input type="submit" class="btn btn-default"> </form> </div> </div> </div> </body> </html>
83-基于多表的图书管理系统添加功能2
views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from .models import Publish,Author,Book #添加书籍； def add_book(request): if request.method == "POST": title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors_id_list = request.POST.getlist("authors_id_list") #向数据库写入数据； book_obj = Book.objects.create(title=title,price=price,publishDate=pub_date,publish_id=publish_id) #绑定多对多关系； book_obj.authors.add(*authors_id_list) #print(authors_id_list) return HttpResponse("Sucess!") publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"addbook.html",{"author_list":author_list,"publish_list":publish_list})
addbook.html;
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>添加书籍</title> <!-- Bootstrap --> <link rel="stylesheet" type="text/css" href="/static/bootstrap-3.3.7/dist/css/bootstrap.css"> <!-- Bootstrap --> <!--<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">--> </head> <body> <h3>添加书籍</h3> <div class="container"> <div class="row"> <div class="col-md-6 col-md-offset-3"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="">名称</label> <input type="text" name="title" class="form-control"> </div> <div class="form-group"> <label for="">价格</label> <input type="text" name="price" class="form-control"> </div> <div class="form-group"> <label for="">出版日期</label> <input type="date" name="pub_date" class="form-control"> </div> <div class="form-group"> <label for="" >出版社</label > <select name="publish_id" id="" class="form-control"> {% for publish in publish_list %} <option value="{{ publish.pk }}">{{ publish.name }}</option> {% endfor %} </select> </div> <div class="form-group"> <label for="">作者</label> <select name="authors_id_list" type="text" id="" multiple class="form-control"> {% for author in author_list %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endfor %} </select> </div> <input type="submit" class="btn btn-default"> </form> </div> </div> </div> </body> </html>
84-基于多表的图书管理系统查看功能
1、urls.py中添加books；
"""bookms_02 URL Configuration The `urlpatterns` list routes URLs to views. For more information please see: https://docs.djangoproject.com/en/2.1/topics/http/urls/ Examples: Function views 1. Add an import: from my_app import views 2. Add a URL to urlpatterns: path('', views.home, name='home') Class-based views 1. Add an import: from other_app.views import Home 2. Add a URL to urlpatterns: path('', Home.as_view(), name='home') Including another URLconf 1. Import the include() function: from django.urls import include, path 2. Add a URL to urlpatterns: path('blog/', include('blog.urls')) """ from django.contrib import admin from django.urls import path,re_path from book import views urlpatterns = [ path('admin/', admin.site.urls), path('book/add', views.add_book), re_path('books/', views.books), ]
2、views.py中添加books视图函数；
from django.shortcuts import render,HttpResponse # Create your views here. from .models import Publish,Author,Book #添加书籍； def add_book(request): if request.method == "POST": title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors_id_list = request.POST.getlist("authors_id_list") #向数据库写入数据； book_obj = Book.objects.create(title=title,price=price,publishDate=pub_date,publish_id=publish_id) #绑定多对多关系； book_obj.authors.add(*authors_id_list) #print(authors_id_list) return HttpResponse("Sucess!") publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"addbook.html",{"author_list":author_list,"publish_list":publish_list}) def books(request): book_list = Book.objects.all() return render(request,"books.html",{"book_list":book_list})
3、templates下新增books.html页面；
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>查看书籍</title> <!-- Bootstrap --> <link rel="stylesheet" type="text/css" href="/static/bootstrap-3.3.7/dist/css/bootstrap.css"> <!-- Bootstrap --> <!--<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">--> </head> <body> <h3>添加书籍</h3> <div class="container"> <div class="row"> <div class="col-md-6 col-md-offset-3"> <table class="table table-bordered table-hover table-striped"> <thead> <tr> <th>编号</th> <th>书籍名称</th> <th>书籍价格</th> <th>出版日期</th> <th>出版社</th> <th>作者</th> </tr> </thead> <tbody> {% for book in book_list %} <tr> <td>{{ forloop.counter }}</td> <td>{{ book.title }}</td> <td>{{ book.price }}</td> <td>{{ book.publishDate|date:"Y-m-d" }}</td> <td>{{ book.publish.name }}</td> <td> {% for author in book.authors.all %} {% if forloop.last %} <span>{{ author.name }}</span> {% else %} <span>{{ author.name }}</span>, {% endif %} {% endfor %} </td> </tr> {% endfor %} </tbody> </table> </div> </div> </div> </body> </html>
4、查看页面显示效果；
85-基于多表的图书管理系统编辑功能1
1、editbook.html;
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>编辑书籍</title> <!-- Bootstrap --> <link rel="stylesheet" type="text/css" href="/static/bootstrap-3.3.7/dist/css/bootstrap.css"> <!-- Bootstrap --> <!--<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">--> </head> <body> <h3>编辑书籍</h3> <div class="container"> <div class="row"> <div class="col-md-6 col-md-offset-3"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="">名称</label> <input type="text" name="title" class="form-control" value="{{ edit_book_obj.title }}"> </div> <div class="form-group"> <label for="">价格</label> <input type="text" name="price" class="form-control" value="{{ edit_book_obj.price }}"> </div> <div class="form-group"> <label for="">出版日期</label> <input type="date" name="pub_date" class="form-control" value="{{ edit_book_obj.publishDate|date:"Y-m-d" }}"> </div> <div class="form-group"> <label for="" >出版社</label > <select name="publish_id" id="" class="form-control" > {% for publish in publish_list %} {% if edit_book_obj.publish == publish %} <option value="{{ publish.pk }}" selected>{{ publish.name }}</option> {% else %} <option value="{{ publish.pk }}" >{{ publish.name }}</option> {% endif %} {% endfor %} </select> </div> <div class="form-group"> <label for="">作者</label> <select name="authors_id_list" type="text" id="" multiple class="form-control"> {% for author in author_list %} {% if author in edit_book_obj.authors.all %} <option selected value="{{ author.pk }}">{{ author.name }}</option> {% else %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endif %} {% endfor %} </select> </div> <input type="submit" class="btn btn-default"> </form> </div> </div> </div> </body> </html>
2、views.py;
from django.shortcuts import render,HttpResponse # Create your views here. from .models import Publish,Author,Book #添加书籍； def add_book(request): if request.method == "POST": title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors_id_list = request.POST.getlist("authors_id_list") #向数据库写入数据； book_obj = Book.objects.create(title=title,price=price,publishDate=pub_date,publish_id=publish_id) #绑定多对多关系； book_obj.authors.add(*authors_id_list) #print(authors_id_list) return HttpResponse("Sucess!") publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"addbook.html",{"author_list":author_list,"publish_list":publish_list}) def books(request): book_list = Book.objects.all() return render(request,"books.html",{"book_list":book_list}) #编辑图书 def change_book(request,edit_book_id): edit_book_obj = Book.objects.filter(pk=edit_book_id).first() publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"editbook.html",{"edit_book_obj":edit_book_obj,"publish_list":publish_list,"author_list":author_list})
86-基于多表的图书管理系统编辑功能2
1、编辑功能时候，set()方法的引入；
views.py;
from django.shortcuts import render,HttpResponse,redirect # Create your views here. from .models import Publish,Author,Book #添加书籍； def add_book(request): if request.method == "POST": title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors_id_list = request.POST.getlist("authors_id_list") #向数据库写入数据； book_obj = Book.objects.create(title=title,price=price,publishDate=pub_date,publish_id=publish_id) #绑定多对多关系； book_obj.authors.add(*authors_id_list) #print(authors_id_list) return HttpResponse("Sucess!") publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"addbook.html",{"author_list":author_list,"publish_list":publish_list}) def books(request): book_list = Book.objects.all() return render(request,"books.html",{"book_list":book_list}) #编辑图书 def change_book(request,edit_book_id): edit_book_obj = Book.objects.filter(pk=edit_book_id).first() if request.method == "POST": title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors_id_list = request.POST.getlist("authors_id_list") # 向数据库更新写入数据； """ 1、先做更新内容操作； 2、清空原有的值； 3、重新进行写入新的值； """ Book.objects.filter(pk=edit_book_id).update(title=title,price = price,publishDate=pub_date,publish_id=publish_id) # edit_book_obj.authors.clear() # edit_book_obj.authors.add(*authors_id_list) #等价于以上两句，先清空再设置； edit_book_obj.authors.set(authors_id_list) #编辑完成后，点击提交按钮，redirect至books页面； return redirect("/books/") publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"editbook.html",{"edit_book_obj":edit_book_obj,"publish_list":publish_list,"author_list":author_list})
2、编辑完成后，redirect()重定向页面的引入；
return redirect("/books/")
87-基于多表的图书管理系统删除功能
urls.py;
"""bookms_02 URL Configuration The `urlpatterns` list routes URLs to views. For more information please see: https://docs.djangoproject.com/en/2.1/topics/http/urls/ Examples: Function views 1. Add an import: from my_app import views 2. Add a URL to urlpatterns: path('', views.home, name='home') Class-based views 1. Add an import: from other_app.views import Home 2. Add a URL to urlpatterns: path('', Home.as_view(), name='home') Including another URLconf 1. Import the include() function: from django.urls import include, path 2. Add a URL to urlpatterns: path('blog/', include('blog.urls')) """ from django.contrib import admin from django.urls import path,re_path from book import views urlpatterns = [ path('admin/', admin.site.urls), re_path('books/add/$', views.add_book), re_path('books/$', views.books), re_path('books/(\d+)/change/$', views.change_book), re_path('books/(\d+)/delete/$', views.delete_book), ]
views.py;
from django.shortcuts import render,HttpResponse,redirect # Create your views here. from .models import Publish,Author,Book #添加书籍； def add_book(request): if request.method == "POST": title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors_id_list = request.POST.getlist("authors_id_list") #向数据库写入数据； book_obj = Book.objects.create(title=title,price=price,publishDate=pub_date,publish_id=publish_id) #绑定多对多关系； book_obj.authors.add(*authors_id_list) #print(authors_id_list) return redirect("/books/") publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"addbook.html",{"author_list":author_list,"publish_list":publish_list}) def books(request): book_list = Book.objects.all() return render(request,"books.html",{"book_list":book_list}) #编辑图书 def change_book(request,edit_book_id): edit_book_obj = Book.objects.filter(pk=edit_book_id).first() if request.method == "POST": title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors_id_list = request.POST.getlist("authors_id_list") # 向数据库更新写入数据； """ 1、先做更新内容操作； 2、清空原有的值； 3、重新进行写入新的值； """ Book.objects.filter(pk=edit_book_id).update(title=title,price = price,publishDate=pub_date,publish_id=publish_id) # edit_book_obj.authors.clear() # edit_book_obj.authors.add(*authors_id_list) #等价于以上两句，先清空再设置； edit_book_obj.authors.set(authors_id_list) #编辑完成后，点击提交按钮，redirect至books页面； return redirect("/books/") publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"editbook.html",{"edit_book_obj":edit_book_obj,"publish_list":publish_list,"author_list":author_list}) def delete_book(request,delete_book_id): Book.objects.filter(pk=delete_book_id).delete() return redirect("/books/")
books.html;
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>查看书籍</title> <!-- Bootstrap --> <link rel="stylesheet" type="text/css" href="/static/bootstrap-3.3.7/dist/css/bootstrap.css"> <!-- Bootstrap --> <!--<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">--> </head> <body> <h3>添加书籍</h3> <div class="container"> <div class="row"> <div class="col-md-8 col-md-offset-2"> <a href="/books/add/" class="btn btn-primary">添加书籍</a> <table class="table table-bordered table-hover table-striped"> <thead> <tr> <th>编号</th> <th>书籍名称</th> <th>书籍价格</th> <th>出版日期</th> <th>出版社</th> <th>作者</th> <th>操作</th> </tr> </thead> <tbody> {% for book in book_list %} <tr> <td>{{ forloop.counter }}</td> <td>{{ book.title }}</td> <td>{{ book.price }}</td> <td>{{ book.publishDate|date:"Y-m-d" }}</td> <td>{{ book.publish.name }}</td> <td> {% for author in book.authors.all %} {% if forloop.last %} <span>{{ author.name }}</span> {% else %} <span>{{ author.name }}</span>, {% endif %} {% endfor %} </td> <td> <a href="books/{{ book.pk }}/change/" class="btn btn-warning">编辑</a> <a href="books/{{ book.pk }}/delete/" class="btn btn-info">删除</a> </td> </tr> {% endfor %} </tbody> </table> </div> </div> </div> </body> </html>
editbooks.html;
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>编辑书籍</title> <!-- Bootstrap --> <link rel="stylesheet" type="text/css" href="/static/bootstrap-3.3.7/dist/css/bootstrap.css"> <!-- Bootstrap --> <!--<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">--> </head> <body> <h3>编辑书籍</h3> <div class="container"> <div class="row"> <div class="col-md-8 col-md-offset-2"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="">名称</label> <input type="text" name="title" class="form-control" value="{{ edit_book_obj.title }}"> </div> <div class="form-group"> <label for="">价格</label> <input type="text" name="price" class="form-control" value="{{ edit_book_obj.price }}"> </div> <div class="form-group"> <label for="">出版日期</label> <input type="date" name="pub_date" class="form-control" value="{{ edit_book_obj.publishDate|date:"Y-m-d" }}"> </div> <div class="form-group"> <label for="" >出版社</label > <select name="publish_id" id="" class="form-control" > {% for publish in publish_list %} {% if edit_book_obj.publish == publish %} <option value="{{ publish.pk }}" selected>{{ publish.name }}</option> {% else %} <option value="{{ publish.pk }}" >{{ publish.name }}</option> {% endif %} {% endfor %} </select> </div> <div class="form-group"> <label for="">作者</label> <select name="authors_id_list" type="text" id="" multiple class="form-control"> {% for author in author_list %} {% if author in edit_book_obj.authors.all %} <option selected value="{{ author.pk }}">{{ author.name }}</option> {% else %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endif %} {% endfor %} </select> </div> <input type="submit" class="btn btn-default"> </form> </div> </div> </div> </body> </html>
addbook.html;
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>添加书籍</title> <!-- Bootstrap --> <link rel="stylesheet" type="text/css" href="/static/bootstrap-3.3.7/dist/css/bootstrap.css"> <!-- Bootstrap --> <!--<link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">--> </head> <body> <h3>添加书籍</h3> <div class="container"> <div class="row"> <div class="col-md-8 col-md-offset-2"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="">名称</label> <input type="text" name="title" class="form-control"> </div> <div class="form-group"> <label for="">价格</label> <input type="text" name="price" class="form-control"> </div> <div class="form-group"> <label for="">出版日期</label> <input type="date" name="pub_date" class="form-control"> </div> <div class="form-group"> <label for="" >出版社</label > <select name="publish_id" id="" class="form-control"> {% for publish in publish_list %} <option value="{{ publish.pk }}">{{ publish.name }}</option> {% endfor %} </select> </div> <div class="form-group"> <label for="">作者</label> <select name="authors_id_list" type="text" id="" multiple class="form-control"> {% for author in author_list %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endfor %} </select> </div> <input type="submit" class="btn btn-default"> </form> </div> </div> </div> </body> </html>
models.py;
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() #出版社； 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)#999.99 #与Publish表建立"一对多"的关系，外键字段建立在多的一方； publish = models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE) #与Author表建立多对多的关系，ManyToManyField可以建立两个模型中的任意一个，自动创建第三张表表； authors = models.ManyToManyField(to="Author",)
