526互联

15-多表查询

发布时间 2023-03-28 18:40:39作者: 测试圈的彭于晏
# django必须定义外键,才能多表联查

1. 一对多

# 创建模型类 models.py
# ForeignKey 关联
class Book(models.Model):
    """图书表"""
    id = models.AutoField(primary_key=True)
    bname = models.CharField(max_length=200, blank=True, null=True)
    # ForeignKey第一个参数: 参照的模型名;
    # on_delete =models.CASCADE 级联删除
    # db_column: 表中字段名
    # related_name： 从出版社查询图书引用的名字
    pud = models.ForeignKey('Publisher', on_delete=models.CASCADE, related_name="books", db_column="pid", blank=True,
                            null=True) # 外键

    class Meta:
        db_table = "book"
        # managed=False # 不会对此模型执行数据库表创建或删除操作


class Publisher(models.Model):
    """出版社表"""
    pname = models.CharField(max_length=100, blank=True, null=True)

    class Meta:
        db_table = "publisher"


'''
数据库迁移指令
python manage.py makemigrations
python manage.py migrate
'''

# 1. 创建表数据
  # 创建一个出版社
    pub = Publisher(pname="计算机工业出版社")
    pub.save()

  # 创建图书
    book = Book(bname="蓝精灵")
    book.save() # 此时的pid是Null

# 2. 主键关联外键
  # 把图书的外键赋值 主键对象
    book = Book.objects.get(pk=1)
    pub = Publisher.objects.get(pk=1)
    book.pud = pub  # pub必须是出版社对象
    book.save()

  # 通过出版社管理图书(通过主键创建外键数据)
    pub = Publisher.objects.get(pk=3) # 获取出版者对象
    # 增加图书 books是模型外键中定义的related_name
    pub.books.create(bname="测试的自我修养")  # 通过对象管理图书

# 3. 级联删除
    pub = Publisher.objects.get(pk=3)
    pub.delete()

# 4. 更新
   # 生成主键对象, 调用外键,修改图书内容
    pub = Publisher.objects.get(pk=2)
    pub.books.update(bname="巴拉巴拉小魔仙")

   # 通过图书外键修改出版社主键
    book = Book.objects.get(pk=3)
    book.pud.pname="蓝翔技术学院"
    book.pud.save()
    print(book.pud)

# 5. 查询
   # 由出版社查询图书
    pub = Publisher.objects.get(pk=2)
    # pub.books 是一个查询管理器对象 objects
    print(pub.books.all())

   # 由图书查出版社
    book = Book.objects.get(pk=3)
    print(book.pud.pname)

# 6. 复杂查询:跨关系查
    pub = Publisher.objects.filter(books__bname="蓝精灵")
    pub = Publisher.objects.filter(books__id=3)
    print(pub[0].pname)

2. 一对一

# 创建模型, OneToOneField关联
class Student(models.Model):
    sno = models.CharField(max_length=6, primary_key=True)
    sname = models.CharField(max_length=100)
    ssex = models.CharField(max_length=2, blank=True, null=True)
    sage = models.IntegerField(blank=True, null=True)
    sclass = models.CharField(max_length=10, blank=True, null=True)

    class Meta:
        db_table = 'student'


class Archives(models.Model):
    idcard = models.CharField(max_length=18, unique=True)
    address = models.CharField(max_length=200, blank=True, null=True)
    # on_delete = models.CASCADE 级联删除,删除学生会连同档案一块删除
    student = models.OneToOneField('Student', on_delete=models.CASCADE, unique=True, related_name="archive")

    class Meta:
        db_table = 'archives'


'''
python manage.py makemigrations
python manage.py migrate
'''

# 操作,和一对多一致
# 1. 增加
   # 增加一个学生
    m = {"sno": "100567", "sname": "小蓝", "sage": 18,"ssex":"1","sclass":"吃"}
    student = Student(**m)
    student.save()

    # 增加一个档案, 外键需要关联学生对象
    stu = Student.objects.get(pk="10056")
    a = {"idcard": "123123", "address": "北京市丰台区"}
    arc = Archives(**a)
    arc.student = stu  # 学生对象必须已经保存到数据库,否则错误
    arc.save()

# 2. 查询
    student = Student.objects.get(pk="10056")
    print(student.sname)
    # 通过学生查档案
    print(student.archive)

    # 通过档案看学生信息
    arc = Archives.objects.get(pk=1)
    print(arc.student.sno)

    return HttpResponse("{success:成功}")

3. 多对多

# 一个买家对应多个商品,一个商品可以对应多个买家,买家和商品之间形成多对多关系;
# 多对多关系必然会生成一张中间表,买家-商品表,记录商品和买家的关系,该表包含商品主键和买家主键

# 手动创建中间表,关联两张表,将多对多变成一对多操作