模型层之多表
多表
Models：publish，author，authordetail，book 一对一表，不推荐foreign key 要自己加上unique 最好使用onetoone django 自动处理了unique 补充：models 建立多个文件 1 App中创建models目录 2 Init 里面导入 from . import models1 from . import test2 # from app01.models import models1 3 models目录下新建文件 4 makemigrations migrate 外键可以设置空
class Publish(models.Model): nid=models.AutoField(primary_key=True) name=models.CharField(max_length=32) addr=models.CharField(max_length=64) email=models.EmailField() 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') class AuthorDetail(models.Model): nid=models.AutoField(primary_key=True) phone=models.CharField(max_length=32) email=models.EmailField() class Book(models.Model): nid=models.AutoField(primary_key=True) name=models.CharField(max_length=32,null=True) price=models.DecimalField(max_digits=5,decimal_places=2) pub_date=models.DateField() # 阅读数 # reat_num=models.IntegerField(default=0) # 评论数 # commit_num=models.IntegerField(default=0) publish=models.ForeignKey(to='Publish',to_field='nid') authors=models.ManyToManyField(to='Author')
# 一对一新增 AuthorDetail Author # 先创建没有外键的数据，然后类的字段等于对象（方式一） # authordetail=AuthorDetail.objects.create(phone='43333333',email='667788@qq.com') # print(authordetail,type(authordetail)) #AuthorDetail object <class 'app01.models.AuthorDetail'> # author=Author.objects.create(name='小猴',age=12,authordetail=authordetail) # print(author) #Author object # 直接指名道姓给authordatil_id赋值 （方式二） # author=Author.objects.create(name='lqz',age=17,authordetail_id=4) # print(author) #Author object # 一对多增加 Publish Book # publish=Publish.objects.create(nid=1,name='南京出版社',addr='南京东路',email='30633@qq.com') # 不建议这么用 # publish=Publish.objects.create(name='北京出版社',addr='北京西路',email='30633@qq.com') # book=Book.objects.create(name='在人间',price=12.4,pub_date='2018-08-19',publish=publish) # print(book) #Book object # publish=Publish.objects.filter(name='北京出版社').first() # book=Book.objects.create(name='百年孤独',price=12.4,pub_date='2018-08-19',publish_id=publish.nid) # print(book) #Book object #多对多增加 Author Book # book=Book.objects.create(name='红楼',price=66,pub_date='2017-07-19',publish_id=2) # print(book.nid) # print(book.name) # print(book.price) # print(book.authors.all(),type(book.authors)) #QuerySet manager # 多对多，添加关联关系add,传对应表的(作者表的nid)id book.authors.add(1,2) # 没有返回值 # book=Book.objects.filter(name='红楼').first() # ret = book.authors.add(2,3) # ret = book.authors.add(*(2,3)) # print(ret) # add里面可以传对象，也可以传多个，以逗号分割，也可以*(作者对象，作者对象) # book=Book.objects.filter(name='在人间').first() # author=Author.objects.filter(name='egon').first() # ret = book.authors.add(author) # print(ret)
# 多对多 # remove 解除绑定关系,传author_id(既可以传对象，又可以传author_id,既可以传多个，又可以传一个) # book=Book.objects.filter(pk=2).first() # ret=book.authors.remove(author.id) # ret=book.authors.remove(3) # author=Author.objects.filter(pk=3).first() # ret=book.authors.remove(author) # ret=book.authors.remove(2,5) # ret=book.authors.remove(*(2,5)) # print(ret) #clear 一次性全部解除绑定关系 # book = Book.objects.filter(pk=2).first() # book.authors.clear() # set 用法跟上面的不太一样，参数，必须传可迭代对象,可以传id，也可以传对象 # book = Book.objects.filter(pk=2).first() # author=Author.objects.filter(pk=2).first() # book.authors.set([author]) # 先执行clear，在执行add # book = Book.objects.filter(pk=2).first() # ret=book.authors.all() # ret=book.authors # print(ret,type(ret))
# 基于对象的多表查询(子查询) # 一对一( # 查询egon的电话号码（正向查询 按字段） # egon=Author.objects.filter(name='egon').first() # print(egon) # authordetail=AuthorDetail.objects.filter(pk=egon.authordetail_id) # print(authordetail) # print(egon.authordetail) # print(egon.authordetail.phone,type(egon.authordetail)) # 查询电话号码是 182281212 的作者(反向查询 按表名小写) # authordetail=AuthorDetail.objects.filter(phone='182281212').first() # print(authordetail.author.name) ''' A表book(关联自动段) B表 publish # 正向查询 A--->B 关联字段再A，A去查询B表，这叫正向查询，按字段来查 # 反向查询 B--》A 关联字段再A，B去查询A表，这叫反向查询，按表明小写_set ''' # 一对多 # 查询红楼是那个出版社出版的 # 正向查询 # book=Book.objects.filter(name='红楼').first() # print(book.publish.name) # 反向查询 # 查询北京出版社出版的所有书 # publish=Publish.objects.filter(name='北京出版社').first() # # print(publish.book_set,type(publish.book_set)) # print(publish.book_set.all()) # 多对多 # 查询红楼这本书的所有作者（正向,按字段） # book=Book.objects.all().filter(name='红楼').first() # print(book.authors.all()) # 查询lqz出的所有书(反向查询，按表名小写_set) # lqz=Author.objects.filter(name='lqz').first() #SELECT `app01_author`.`nid`, `app01_author`.`name`, `app01_author`.`age`, `app01_author`.`authordetail_id` # FROM `app01_author` WHERE `app01_author`.`name` = 'lqz' ORDER BY `app01_author`.`nid` # print(lqz.book_set.all()) #SELECT `app01_book`.`nid`, `app01_book`.`name`, `app01_book`.`price`, `app01_book`.`pub_date`, `app01_book`.`publish_id` # FROM `app01_book` INNER JOIN `app01_book_authors` ON (`app01_book`.`nid` = `app01_book_authors`.`book_id`) # WHERE `app01_book_authors`.`author_id` = 3 ''' A表book(关联自动段) B表 publish # 正向查询 A--->B # 反向查询 B-->A 总结：一对一 正向：按字段 反向：按表名小写 一对多 正向：按字段 反向：按表名小写_set 多对多 正向：按字段 反向：按表名小写_set '''
# 基于双下划线的多表查询(连表查询） # 正向查询按字段, 反向查询按表名小写用来告诉ORM引擎join哪张表 # 一对多查询 # 查询北京版社出版过的所有书籍价格，名字(反向 按表名) # ret=Publish.objects.filter(name='北京出版社').values('book__price','book__name') # print(ret) # ret=Book.objects.filter(publish__name='北京出版社').values('price','name') # print(ret) # SELECT `app01_book`.`price`, `app01_book`.`name` # FROM `app01_publish` LEFT OUTER JOIN `app01_book` ON (`app01_publish`.`nid` = `app01_book`.`publish_id`) # WHERE `app01_publish`.`name` = '北京出版社' # SELECT `app01_book`.`price`, `app01_book`.`name` # FROM `app01_book` INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`nid`) # WHERE `app01_publish`.`name` = '北京出版社' # 多对多 # 查询lqz出过的所有书籍的名字(多对多) # 正向 # ret=Book.objects.filter(authors__name='lqz').values('name','price','authors__name','authors__authordetail__phone') # ret=Book.objects.filter(authors__name='lqz').values('name','price') # print(ret) # 反向 # ret=Author.objects.filter(name='lqz').values('book__name') # print(ret) # SELECT `app01_book`.`name`, `app01_book`.`price` 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_author`.`name` = 'lqz' # SELECT `app01_book`.`name` FROM `app01_author` # LEFT OUTER JOIN `app01_book_authors` ON (`app01_author`.`nid` = `app01_book_authors`.`author_id`) # LEFT OUTER JOIN `app01_book` ON (`app01_book_authors`.`book_id` = `app01_book`.`nid`) # WHERE `app01_author`.`name` = 'lqz' # 一对一 # 查询egon的手机号(正向) # ret=Author.objects.filter(name='egon').values('authordetail__phone') # print(ret) # 反向 按表名小写 # ret=AuthorDetail.objects.filter(author__name='egon').values('phone') # print(ret) # SELECT `app01_authordetail`.`phone` FROM `app01_author` # INNER JOIN `app01_authordetail` ON (`app01_author`.`authordetail_id` = `app01_authordetail`.`nid`) # WHERE `app01_author`.`name` = 'egon' # SELECT `app01_authordetail`.`phone` FROM `app01_authordetail` # INNER JOIN `app01_author` ON (`app01_authordetail`.`nid` = `app01_author`.`authordetail_id`) # WHERE `app01_author`.`name` = 'egon' ''' 总结：用__告诉orm，要连接那个表 一对一： 正向：按字段 反向：按表名小写 一对多： 正向：按字段 反向：按表名小写 多对多： 正向：按字段 反向：按表名小写 ''' # 手机号以18开头的作者出版过的所有书籍名称以及出版社名称 # ret=Book.objects.filter(authors__authordetail__phone__startswith='18').values('name','publish__name') # print(ret) # ret=AuthorDetail.objects.filter(phone__startswith='18').values('author__book__name','author__book__publish__name') # print(ret) # ret=Author.objects.filter(authordetail__phone__startswith='18').values('book__name','book__publish__name') # print(ret) # ret=Publish.objects.filter(book__authors__authordetail__phone__startswith='18').values('name','book__name') # print(ret) # SELECT `app01_publish`.`name`, `app01_book`.`name` FROM `app01_publish` # INNER JOIN `app01_book` ON (`app01_publish`.`nid` = `app01_book`.`publish_id`) # 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`) # INNER JOIN `app01_authordetail` ON (`app01_author`.`authordetail_id` = `app01_authordetail`.`nid`) # WHERE `app01_authordetail`.`phone` LIKE BINARY '18%'
# 练习: 查询人民出版社出版过的所有书籍的名字以及作者的姓名 # ret=Publish.objects.filter(name='人民出版社').values('book__name','book__authors__name') # ret=Book.objects.filter(publish__name='人民出版社').values('name','authors__name') # ret=Author.objects.filter(book__publish__name='人民出版社').values('book__name','name') # ret=AuthorDetail.objects.filter(author__book__publish__name='人民出版社').values('author__book__name','author__name') # print(ret) # SELECT `app01_book`.`name`, `app01_author`.`name` FROM `app01_authordetail` # INNER JOIN `app01_author` ON (`app01_authordetail`.`nid` = `app01_author`.`authordetail_id`) # 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`) # INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`nid`) # WHERE `app01_publish`.`name` = '人民出版社'
# 聚合 # 计算所有图书的平均价格 # from django.db.models import Avg, Count, Max, Min, Sum # ret=Book.objects.all().aggregate(c=Avg('price')) #可以重命名 # ret=Book.objects.all().aggregate(Avg('price')) # print(ret) # 计算所有图书总价 # ret=Book.objects.all().aggregate(s=Sum('price')) # print(ret) # 最大价格： # ret=Book.objects.all().aggregate(c=Max('price')) # print(ret) # 所有图书价格的最大值和最小值 返回结果是字典 # ret=Book.objects.all().aggregate(c_max=Max('price'),c_min=Min('price')) # print(ret) # SELECT AVG(`app01_book`.`price`) AS `c` FROM `app01_book` # SELECT SUM(`app01_book`.`price`) AS `s` FROM `app01_book` # SELECT MAX(`app01_book`.`price`) AS `c` FROM `app01_book` # SELECT MAX(`app01_book`.`price`) AS `c_max`, MIN(`app01_book`.`price`) AS `c_min` FROM `app01_book`
# 分组 1 注意数据库的严格模式 2 默认的分组依据是表的主键 from django.db.models import Count, Min, Max,Sum # 统计每一本书作者个数 # ret=Book.objects.all().annotate(author_num=Count('authors__nid')).values('name','author_num') # print(ret) # values 在这里代指group by 的字段 # ret=Book.objects.all().values('name').annotate(author_num=Count('authors__name')).values('name','author_num') # print(ret) # ret = Book.objects.all().annotate(author_num=Count('authors__nid')) #所有的字段都查询出来，queryset对象 # for book in ret: # print(book.name,":",book.author_num) # print(ret) # 统计每一个出版社的最便宜的书 # ret=Publish.objects.all().annotate(c=Min('book__price')).values('name','c') # print(ret) # SELECT `app01_publish`.`name`, MIN(`app01_book`.`price`) AS `c` FROM `app01_publish` # LEFT OUTER JOIN `app01_book` ON (`app01_publish`.`nid` = `app01_book`.`publish_id`) # GROUP BY `app01_publish`.`nid` # 统计每一本以红开头的书籍的作者个数： # ret=Book.objects.all().filter(name__startswith='红').annotate(c=Count('authors__nid')).values('name','c') # print(ret) # SELECT `app01_book`.`name`, COUNT(`app01_book_authors`.`author_id`) AS `c` FROM `app01_book` # LEFT OUTER JOIN `app01_book_authors` ON (`app01_book`.`nid` = `app01_book_authors`.`book_id`) # WHERE `app01_book`.`name` LIKE BINARY '红%' GROUP BY `app01_book`.`nid` #统计不止一个作者的图书：（作者数量大于一） # 注意：valu再annotate前，代表group by 的字段，不写value，默认以基表的主键做group by 在后代表我要select出来的字段 # filter在前，代指where的东西，在后，代表having 的东西 # ret = Book.objects.all().values('name').annotate(author_num=Count('authors__name')).filter(author_num__gt=1).values('name','author_num') # print(ret) # SELECT `app01_book`.`name`, COUNT(`app01_author`.`name`) AS `author_num` 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`) # GROUP BY `app01_book`.`name` # HAVING COUNT(`app01_author`.`name`) > 1 # 练习：查询各个作者出的书的总价格: # ret=Author.objects.all().annotate(c=Sum('book__price')).values('name','c') # print(ret) # 查询每个出版社的名称和书籍个数 # ret=Publish.objects.all().annotate(c=Count('book__nid')).values('name','c') # ret=Publish.objects.all().annotate(c=Count('book')).values('name','c') # print(ret)
# F和Q from django.db.models import F, Q # F F() 的实例可以在查询中引用字段 # 查询评论数大于阅读数的所有书 # ret=Book.objects.filter(commit_num__gt=F('reat_num')).values('name') # print(ret) # 把所有书的价格加1 # ret=Book.objects.all().update(price=F('price')+1) # print(ret) # UPDATE `app01_book` SET `price` = (`app01_book`.`price` + 1) # Q函数 # 名字叫在人间，或者price是13的数 | 或 & 和 # ret=Book.objects.all().filter(Q(name='在人间')|Q(price='13')) # ret=Book.objects.all().filter(~Q(name='在人间')| Q(price='13')) # print(ret) # SELECT `app01_book`.`nid`, `app01_book`.`name`, `app01_book`.`price`, `app01_book`.`pub_date`, `app01_book`.`reat_num`, `app01_book`.`commit_num`, `app01_book`.`publish_id` # FROM `app01_book` # WHERE (`app01_book`.`name` = '在人间' OR `app01_book`.`price` = 13) # (0.002) SELECT `app01_book`.`nid`, `app01_book`.`name`, `app01_book`.`price`, `app01_book`.`pub_date`, `app01_book`.`reat_num`, `app01_book`.`commit_num`, `app01_book`.`publish_id` FROM `app01_book` # WHERE (NOT (`app01_book`.`name` = '在人间' AND `app01_book`.`name` IS NOT NULL) OR `app01_book`.`price` = 13)
