表关系设计,增删改查
1.一对一,一对多,多对多表关系设计
在models.py中创建关系表.
from django.db import models # Create your models here. # 作者表 class Author(models.Model): name = models.CharField( max_length=32) age = models.IntegerField() # django2.x版本必须手动指定on_delete=models.CASCADE级联模式 # au = models.OneToOneField(to="AuthorDetail", to_field="id", on_delete=models.CASCADE) # au = models.OneToOneField("AuthorDetail") # au = models.IntegerField() au = models.OneToOneField("AuthorDetail", db_constraint=False) # db_constraint=False取消foreign key的强制约束效果,还可以继续使用orm的提供的属性或者方法来操作关系记录 # foreign key + unique # 属性是OneToOneField或者ForeignKey,那么生成的对应字段是 属性名称_id #作者详细信息表 class AuthorDetail(models.Model): birthday=models.DateField() telephone=models.BigIntegerField() addr=models.CharField(max_length=64) # 出版社表 class Publish(models.Model): name=models.CharField( max_length=32) city=models.CharField( max_length=32) #书籍表 class Book(models.Model): title = models.CharField( max_length=32) publishDate=models.DateField() price=models.DecimalField(max_digits=5,decimal_places=2) # publishs=models.ForeignKey(to="Publish",to_field="id",on_delete=models.CASCADE) publishs=models.ForeignKey("Publish") authors = models.ManyToManyField('Author') # class authortobook(models.Model): # book_id = models.ForeignKey('Book') # author_id = models.ForeignKey('Author')
2.记录的增删改查操作
增:
# 增 # 一对一关系的添加 # 先创建作者详细信息表记录 # ret = models.AuthorDetail.objects.create( # birthday='2000-12-12', # telephone='122', # addr='惠州', # ) # # # models.Author.objects.create( # name='元涛', # age=18, # # au_id=ret.id, #如果用的是属性名称_id,那么值为关联记录的id值 # au=ret, #如果写属性名称来添加关系数据,那么值为关联记录的模型类对象 # ) # 一对多 # pub_obj = models.Publish.objects.get(id=1) # models.Book.objects.create( # title='白洁2', # price=10, # publishDate='1980-07-07', # # publishs=pub_obj, #如果写属性名称来添加关系数据,那么值为关联记录的模型类对象 # publishs_id=2, #如果用的是属性名称_id,那么值为关联记录的id值 # # ) # 多对多 # book_obj = models.Book.objects.get(title='金鳞岂是池中物') # author1 = models.Author.objects.get(id=1) # author2 = models.Author.objects.get(id=2) # book_obj.authors.add(author1,author2) # book_obj.authors.add(1, 2)
3.删除和更新
# 删除 # 外键关联到这条作者记录的都会被删除(级联模式下) # 一对一 # models.Author.objects.get(id=1).delete() # models.AuthorDetail.objects.get(id=2).delete() # 一对多 # models.Book.objects.get(id=1).delete() # models.Publish.objects.get(id=2).delete() # 多对多删除 book_obj = models.Book.objects.get(id=6) # book_obj = models.Book.objects.filter(id=5)[0] # book_obj.authors.remove(1) # 4 1 删除第三张表中id为4 并且作者id为1的记录 # book_obj.authors.clear() # 清空 第三张表中的书籍id为5的所有记录 # book_obj.authors.remove(1, 4) #删除多条 # book_obj.authors.remove(*[1, 4]) #删除多条 # 修改 # 一对一 # models.Author.objects.filter(id=3).update( # age=38, # # au_id=5, # # au=models.AuthorDetail.objects.get(id=5), # ) # 一对多 # models.Book.objects.filter(id=4).update( # title='白洁1', # publishs=models.Publish.objects.get(id=2), # publishs_id=2 # ) # 多对多 obj = models.Book.objects.get(id=5) obj.authors.set(['1','3']) #clear + add 跟新,先清空book_id为5的第三张表里的记录,再添加5 1和5 3记录
4.基于对象的跨表查询
# 基于对象的跨表查询 # 一对一的 # 正向查询(关系属性在哪个表里面,通过这个表的数据去查询另外一张表的数据,就是正向查询) # 正向查询靠属性,反向查询靠表名小写 # 查询一下闻哥这个作者的手机号 # obj = models.Author.objects.get(name='闻哥') # obj.au #这就找到了关联的详细信息表里面的那个记录对象 # print(obj.au.telephone) # 查询手机号为555的作者姓名 # obj = models.AuthorDetail.objects.get(telephone='555') # obj.author #这就找到了关联的作者表表里面的那个记录对象 # print(obj.author.name) # 一对多 # 正向查询 # 查询白洁1这本书是哪个出版社出版的 # obj = models.Book.objects.get(title='白洁1') # obj.publishs #找到了关联的出版社记录 # print(obj.publishs.name) # 查询闻哥出版社出版了哪些书 # 反向查询 # 反向查询在一对多的关系是,使用 表名小写_set # obj = models.Publish.objects.get(name='闻哥出版社') # obj.book_set.filter() #类似于objects控制器 # books = obj.book_set.all() # for book in books: # print(book.title) # 多对多 # 查询一下白洁2这本书的作者是谁 # 正向查询 # obj = models.Book.objects.filter(title='白洁2').first() # obj.authors.all() #类似objects控制器 # objs = obj.authors.all() # for i in objs: # print(i.name) # 查询一下何导写了哪些书 # 反向查询 # obj = models.Author.objects.get(name='何导') # objs = obj.book_set.all() # for i in objs: # print(i.title)
5.基于双下划线的跨表查询
#####基于双下划线的跨表查询 -- mysql连表查询 # select app01_authordetail.telephone from app01_author inner join app01_authordetail on app01_author.au_id = app01_authordetail.id; # select app01_authordetail.telephone from app01_authordetail inner join app01_author on app01_author.au_id = app01_authordetail.id; # 正向查询靠属性, 反向查询靠表名小写 # 一对一的 # 查询一下闻哥这个作者的手机号 # ret = models.Author.objects.filter(name='闻哥').values('au__telephone') # <QuerySet [{'au__telephone': '222'}]> # 反向查询 # ret = models.AuthorDetail.objects.filter(author__name='闻哥').values('telephone') # print(ret) #<QuerySet [{'telephone': '222'}]> # 一对多的 # 查询白洁1这本书是哪个出版社出版的 # ret = models.Book.objects.filter(title='白洁1').values('publishs__name') # <QuerySet [{'publishs__name': '闻哥出版社'}]> # print(ret) # ret = models.Publish.objects.filter(book__title='白洁1').values('name') # print(ret) #<QuerySet [{'name': '闻哥出版社'}]> # 多对多 # 查询一下白洁2这本书的作者是谁 # ret = models.Book.objects.filter(title='白洁2').values('authors__name') # <QuerySet [{'authors__name': '闻哥'}, {'authors__name': '何导'}]> # print(ret) # ret = models.Author.objects.filter(book__title='白洁2').values('name') # print(ret) #<QuerySet [{'name': '何导'}, {'name': '闻哥'}]>
6.聚合查询
# 聚合查询 # 查询所有书籍的平均价格 # ret = models.Book.objects.all().aggregate(Avg('price')) # ret = models.Book.objects.aggregate(Max('price'),Avg('price')) # {'price__max': Decimal('19.00')} 字典类型数据, # ret = models.Book.objects.aggregate(m=Max('price'), a=Avg('price')) # # print(ret)
7.分组查询
# 分组查询 -- group by # 查询一下每个出版社出版书的平均价格 # 默认是用Publish的id字段值作为分组依据,自动会找book表里面的publishs_id去分组 # ret = models.Publish.objects.annotate(a=Avg('book__price')).values('a','name','city') # select t1.name,t1.city,avg(t2.price) from app01_publish as t1 inner join app01_book as t2 on t1.id = t2.publishs_id group by t1.id;(原生sql语句) #<QuerySet [{'name': '33期桔色成人出版社', 'city': '沙河', 'a': 12.5}, {'name': '超哥出版社', 'city': '松兰花', 'a': 14.0}, {'name': '牡丹花出版社', 'city': '洛阳', 'a': 13.75}]> # ret = models.Book.objects.values('publishs_id').annotate(a=Avg('price')) # select avg(price) from app01_book group by publishs_id; # print(ret) # 每个作者出版书的最高价格 # 别忘了: 1.检查数据正确性 2.剔除无用数据 # ret = models.Author.objects.annotate(m=Max('book__price')).values('name','m') # <QuerySet [{'name': '苑昊', 'm': None}, {'name': '何导', 'm': Decimal('17.00')}, {'name': '超哥', 'm': Decimal('19.00')}]> # print(ret) ret = models.Book.objects.values('authors__id').annotate(m=Max('price')) # <QuerySet [{'authors__id': '苑昊', 'm': 123}, # 注意:一定要起别名a= #结果为Publish模型类对象,对象中有本表字典数据 # id name city a # print(ret)
