1. 关联表之间建议建立外键,但是可以取消关联关系(db_constraint=False) 2. 关联表之间的晚间字段建议采用对应类名的全小写 3. 采用关联表的主键或对象均能进行操作 ''' 书籍: Book: id name price publish_date publish author(多对多关联字段) 出版社: Publish:id name address 作者: Author : id name author_detail 作者详情: AuthorDetail : id age telephone info '''
创建数据表(Models)
# 一对多：出版社（一） 书籍（多，外键在多的一方，依赖于出版社） # 一对一：作者详情（一） 作者（一，外键在任意一方均可，一旦外键放在作者中，作者依赖于作者详情） # 多对多：作者（多）书籍（多）建立关系表（存放两个表的外键信息 => 将建表转化为关系对应字段） # Book书籍：id name price publish_date publish(publish_id) class Book(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=20) price = models.DecimalField(max_digits=5, decimal_places=2) publish_date = models.DateField() publish = models.ForeignKey(to='Publish', to_field='id') # 多对多关系字段，该字段不会再book表中形成字段，是用来创建关系表的 author = models.ManyToManyField(to='Author') # Author作者：id name class Author(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=20) # author_detail = models.ForeignKey(to='AuthorDetail', to_field='id', unique=True) author_detail = models.OneToOneField(to='AuthorDetail', to_field='id') # AuthorDetail作者详情: id age telephone info class AuthorDetail(models.Model): id = models.AutoField(primary_key=True) age = models.IntegerField() telephone = models.IntegerField() # 存大文本 info = models.TextField() # Publish出版社：id name address class Publish(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=20) address = models.CharField(max_length=64)
一对多关系
# 规则 1. 关系中多的依赖于一的 2. Django 1.X版本中外键关联默认由级联删除 Django 2.X版本中外键需要手动设置级联删除(on_delete=models.CASCADE) ************************************************************ # 增 # 先有出版社,才有书籍 Publish.objects.create(name='xxx出版额社', address='SH') # 外键为关联对象 Book.objectes.create(name='python', price=88.88, publish_data='2018-08-08', publish=publish) id = Publish.objects.create(name="小女孩出版社", address="宇宙中心").id # 外键字段为关联对象主键 Book.objects.create(name='灭霸Linux', price=120.00, publish_date='2015-8-8', publish_id=id) # 删除出版社，默认有级联删除，出版社出版的数据全会被删除 Publish.objects.first().delete() # 书籍的出版社修改必须为已存在的出版社 publish = Publish.objects.create(name="ssss出版社", address="御花园") Book.objects.filter(pk=1).update(publish=publish) # pk代表主键
一对一关系
规则
1. 通过外键所在表决定依赖关系
# 操作规则同一对多关系，有外键的表 依赖于 另一张表
# 增：遵循操作顺序
author_detail = AuthorDetail.objects.create(age=8, telephone=13860357890, info="真的帅")
Author.objects.create(name='Alan', author_detail=author_detail)
# 删：拥有级联删除
AuthorDetail.objects.first().delete()
# 改：一般不考虑该关联字段
多对多关系
# 规则 1. 多对多关系存在表关系,关系表建议采用ManyToManyField字段处理 2. 需要手动创建关系表时, 字段中明确through与through_field值 ************************************************** 通过关系表字段存在的类的对象获取关系表 book.author # 增：为书籍添加作者的主键或对象们 book.author.add(*args) # 删：删除书籍已有作者的主键或对象们 book.author.remove(*args) # 改：清空并添加作者的主键或对象 | 设置作者的主键或对象形式的列表 book.author.clear() book.author.add(*args) book.author.set([*args])
跨表查询规则:
1. 正向逆向概念：从存放外键的表到关系表称之为正向跨表查询，反之称之为逆向查询 2. 正向查询通过外键属性名进行跨表查询 3. 逆向查询通过关联表对应类名小写进行跨表查询
基于对象的跨表查询
在跨表查询的规则上，跨表查询的结果为多条数据时需要在字段后添加_set # 一对一 author = Author.objects.first() # 查询得到作者对象 author_detail = author.author_detail # 基于对象跨表获取作者详情对象，正向通过字段名 author_detail author = author_detail.author # 基于对象跨表获取作者对象，逆向通过表名小写 author # 一对多 book = Book.objects.first() # 查询得到书籍对象 publish = book.publish # 获取出版社对象，正向通过字段名 publish book_list = publish.book_set.all() # 获取书籍对象们，逆向通过表名小写 book，多条数据添加_set # 多对多 book = Book.objects.first() # 查询得到书籍对象 author_list = book.author # 获取作者对象们，正向通过字段名 author author = Author.objects.first() book_list = author.book_set # 获取书籍对象们，逆向通过表名小写 book，多条数据添加_set ''' 多级跨表 # 案例一：某作者出版的第一本书的出版社名字 # 作者 Author ->book表(逆向查询, book_set)-> 第一本书first() ->出版社 publish-> name author.book_set.first().publish.name '''
基于下划线的跨表查询
满足跨表查询规则 filter方法与values方法支持__查询规则 # 案例 # 两表关联：查询所有小于18岁作者的名字与实际年龄 author_dic = Author.objects.filter(author_detail_age_lt=18).values('name','author_detail_age') # 多表关联：查询出版社在上海的出版过的所有书的 作者姓名、作者电话、具体出版社名 的相关信息 info_dic = Book.objects.filter(publish_address_contains='上海').values('author_name', 'author_author_detail_telephoone', 'publish_name')
# 直接导入django可以查看并用于django包相关模块,但无法使用django创建的项目中的包 import django import os os.environ.setdefault("DJANGO_SETTINGS_MODULE", "duobiaochaxun.settings") django.setup() from app.models import Book, AuthorDetail, Author, Publish # 一对多关系(publish和book book依赖于publish) # 增加 publish = Publish.objects.create(name='老男孩出版社', address='上海浦东').id publish1 = Publish.objects.create(name='小女孩出版社', address='上海虹桥').id publish2 = Publish.objects.create(name='金沙江出版社', address='北京朝阳').id Book.objects.create(name='西游记', price='33.33', publish_date='2018-01-01', publish_id=publish2) Book.objects.create(name='东游记', price='44.44', publish_date='2018-02-02', publish_id=publish2) Book.objects.create(name='爱丽莎', price='55.55', publish_date='2018-03-03', publish_id=publish1) Book.objects.create(name='顾杜友', price='66.66', publish_date='2018-04-04', publish_id=publish1) Book.objects.create(name='拍死你', price='77.77', publish_date='2018-05-05', publish_id=publish) Book.objects.create(name='牛尼斯', price='88.88', publish_date='2018-06-06', publish_id=publish) 一对一关系 Author依赖于AuthorDetail detail = AuthorDetail.objects.create(age=20, telephone=13333334444, info='Owen简介') detail1 = AuthorDetail.objects.create(age=19, telephone=14356789900, info='Zero简介') detail2 = AuthorDetail.objects.create(age=20, telephone=16678906677, info='Egon简介') detail3 = AuthorDetail.objects.create(age=21, telephone=18900123456, info='Lxx简介') detail4 = AuthorDetail.objects.create(age=17, telephone=16875435678, info='Yhh简介') Author.objects.create(name='Owen', author_detail=detail) Author.objects.create(name='Zero', author_detail=detail1) Author.objects.create(name='Egon', author_detail=detail2) Author.objects.create(name='Lxx', author_detail=detail3) Author.objects.create(name='Yhh', author_detail=detail4) 多对多关系 b1 = Book.objects.first() b2 = Book.objects.all()[1] b3 = Book.objects.all()[2] b4 = Book.objects.all()[3] b5 = Book.objects.all()[4] b6 = Book.objects.last() a1 = Author.objects.first() a2 = Author.objects.all()[1] a3 = Author.objects.all()[2] a4 = Author.objects.all()[3] a5 = Author.objects.last() b1.author.add(a1, a2) b2.author.add(a1, a2, a5) b3.author.add(a4) b4.author.add(a4) b5.author.add(a1, a3, a4) b6.author.add(a4, a5) # 地址在北京的出版社出版的书名 # 价格超过50元的书籍的出版社名 books = Book.objects.filter(publish__address__contains='北京') for book in books: print(book.name) publish = Publish.objects.filter(book__price__gt=50).distinct() for p in publish: print(p.name) # 获取所有名字里包含字母o(不区分大小写)作者的电话号码 # 年龄是20岁作者的作者名 authors = AuthorDetail.objects.filter(author__name__icontains='o') for author in authors: print(author.telephone) author = Author.objects.filter(author_detail__age=20) for age in author: print(age.name) # -- 获取第三位作者出版过的书的书名 # -- 获取最后一本书作者们的简介 author = Author.objects.all()[2] for book in author.book_set.all(): print(book.name) authors = Book.objects.last().author.all() for author in authors: print(author.author_detail.info) # 获取第一个出版社出版的最近一次出版的书的作者们的详情 authors = Publish.objects.first().book_set.all().order_by('publish_date').last().author.all() for author in authors: print(author.author_detail.info) # 1. 找到第一个出版社 2.找到最后一本书 3.找到作者们 authors = Publish.objects.first().book_set.all().order_by('publish_date').last().author.all() for author in authors: print(author.name) # -- 获取地址在上海的出版社名，与出版过的书名 # -- 获取2018年出版的书名、书的价格与出版社名 publish = Publish.objects.filter(address__contains='上海').values('name','book__name') print(publish) books = Book.objects.filter(publish_date__year=2018).values('name', 'price', 'publish__name') print(books) # -- 获取年龄小于20岁作者的名字、年龄、电话与简介 print(Author.objects.filter(author_detail__age__lt=20).values('name', 'author_detail__age', 'author_detail__info')) # -- 获取名字中包含e(不区分大小写)的作者出版过的书的书名与价格(需要去重) # -- 获取书籍价格不超过50元的作者名与作者电话(需要去重) n_pr = Author.objects.filter(name__icontains='e').values('name','book__name', 'book__price').distinct() print(n_pr) # name_tep = Book.objects.filter(price__lte=50).values('author__name', 'author__author_detail__telephone').distinct() print(name_tep) # -- 获取在老男孩出版过书的年龄最大的作者的作者名、年龄、电话与简介 info = Author.objects.filter(book__publish__name__contains='老男孩')\ .values('name', 'author_detail__age', 'author_detail__telephone', 'author_detail__info', 'book__publish__name')\ .order_by('author_detail__age').last() print(info)