多表查询(跨表查询)
子查询:分步查询
链表查询:把多个有关系的表拼接成一个大表(虚拟表)
inner join
left join
right join
基于双下划线的查询
# 建表
class User(models.Model):
username = models.CharField(max_length=64)
password = models.CharField(max_length=64)
age = models.IntegerField()
register_time = models.DateTimeField()
# 增加数据
models.User.objects.create(username='kevin', password='123', age='40', register_time='2020-01-30')
models.User.objects.create(username='jerry', password='123', age='36', register_time='2021-11-30')
models.User.objects.create(username='tank', password='123', age='25', register_time='2020-01-10')
models.User.objects.create(username='mike', password='123', age='30', register_time='2020-01-01')
# 1 年龄大于35岁的数据
res = models.User.objects.filter(age__gt=35)
print(res)
# 2 年龄小于35岁的数据
res = models.User.objects.filter(age__lt=35)
print(res)
# 大于等于 小于等于
res = models.User.objects.filter(age__gte=35).all()
print(res)
# 年龄是18 或者 32 或者40
res = models.User.objects.filter(age__lte=35).all()
print(res)
# 年龄在18到40岁之间的 首尾都要
res = models.User.objects.filter(age__range=[18,40])
print(res)
# 查询出名字里面含有s的数据 模糊查询
res = models.User.objects.filter(username__contains='s')
print(res)
# 是否区分大小写 查询出名字里面含有p的数据 区分大小写
# mysql默认不区分大小写 __contains、__icontains、__startswith、__istartswith适用于其他关系型数据库
res = models.User.objects.filter(username__contains="p")
print(res)
# 查询出名字以p开头的记录
res = models.User.objects.filter(username__startswith='P')
print(res)
# 查询出注册时间是 2020 1月
res = models.User.objects.filter(register_time__year=2020, register_time__month=1).all()
print(res)
多表查询前期数据准备
# 图书表 ----出版社表----> 一对多 ----作者表-----> 多对多
class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=8, decimal_places=2)
publish_date = models.DateTimeField(auto_now_add=True)
publish = models.ForeignKey(to='Publish')
authors = models.ManyToManyField(to='Author')
# 出版社表
class Publish(models.Model):
name = models.CharField(max_length=64)
addr = models.CharField(max_length=64)
# 作者表 ------作者信息表------>一对一
class Author(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
author_detail = models.OneToOneField(to='AuthorDetail')
# 作者信息表
class AuthorDetail(models.Model):
phone = models.BigIntegerField()
addr = models.CharField(max_length=64)
# 添加数据---->图书
models.Book.objects.create(title='红楼梦', price=1000, publish_date='2010-10-10', publish_id=1)
models.Book.objects.create(title='西游记', price=1000, publish_date='2010-10-10', publish_id=2)
models.Book.objects.create(title='三国演义', price=1000, publish_date='2010-10-10', publish_id=3)
models.Book.objects.create(title='水浒传', price=1000, publish_date='2010-10-10', publish_id=4)
# 添加数据---->出版社
models.Publish.objects.create(name='上海出版社', addr='上海市')
models.Publish.objects.create(name='北京出版社', addr='北京市')
models.Publish.objects.create(name='南京出版社', addr='南京市')
# 作者表增加数据
models.Author.objects.create(name='kevin', age=35, author_detail_id=1)
models.Author.objects.create(name='jerry', age=40, author_detail_id=2)
models.Author.objects.create(name='tank', age=55, author_detail_id=3)
models.Author.objects.create(name='peter', age=45, author_detail_id=4)
models.Author.objects.create(name='jevin', age=22, author_detail_id=5)
models.Author.objects.create(name='kerry', age=18, author_detail_id=6)
# 作者详情表增加数据
models.AuthorDetail.objects.create(phone=12345, addr='上海市')
models.AuthorDetail.objects.create(phone=12121, addr='北京市')
models.AuthorDetail.objects.create(phone=12312, addr='南京市')
models.AuthorDetail.objects.create(phone=12300, addr='苏州市')
models.AuthorDetail.objects.create(phone=65412, addr='无锡市')
models.AuthorDetail.objects.create(phone=65432, addr='镇江市')
一对多外键增删改查
# 一对多外键增删改查
# 添加数据---->图书
models.Book.objects.create(title='红楼梦', price=1000, publish_date='2010-10-10', publish_id=1)
models.Book.objects.create(title='西游记', price=1000, publish_date='2010-10-10', publish_id=2)
models.Book.objects.create(title='三国演义', price=1000, publish_date='2010-10-10', publish_id=3)
models.Book.objects.create(title='水浒传', price=1000, publish_date='2010-10-10', publish_id=4)
# *****出版社
models.Publish.objects.create(name='上海出版社', addr='上海市')
models.Publish.objects.create(name='北京出版社', addr='北京市')
models.Publish.objects.create(name='南京出版社', addr='南京市')
# 方式一 -------> publish_id=值
models.Book.objects.create(title='论语', price=666.66, publish_date='2020-01-01', publish_id=1)
# 方式二 -------> publish=对象
publish_obj = models.Publish.objects.filter(pk=2).first()
models.Book.objects.create(title='三字经', price=666.66, publish_date='2020-01-01', publish=publish_obj)
# 删 --------> 级联删除 删除主表数据时,会自动删除与之相关联的表的数据
models.Publish.objects.filter(pk=1).delete()
# 改 方式一 值
models.Book.objects.filter(pk=3).update(publish_id=4)
# 方式二 对象
publish_obj = models.Publish.objects.filter(pk=2).first()
models.Book.objects.filter(pk=3).update(publish=publish_obj)
多对多外键增删改查
#多对多外键增删改查 图书------->作者
# 增加数据
book_obj = models.Book.objects.filter(pk=3).first()
book_obj.authors.add(3)
book_obj = models.Book.objects.filter(pk=4).first()
book_obj.authors.add(1,2)
book_obj = models.Book.objects.filter(pk=5).first()
book_obj.authors.add(1,5,6)
book_obj = models.Book.objects.filter(pk=6).first()
book_obj.authors.add(4)
book_obj = models.Book.objects.filter(pk=7).first()
book_obj.authors.add(4,6)
# 删除数据
# 删除图书西游记的作者tank
book_obj = models.Book.objects.filter(pk=3).first()
book_obj.authors.remove(1)
# 删除图书红楼梦的作者peter和kerry
# remove的括号内可以是一个或多个数字
book_obj = models.Book.objects.filter(pk=7).first()
book_obj.authors.remove(4,6)
# 修改数据
# 将图书水浒传(5)的作者修改为jerry和tank[2,3]
# 方式一
book_obj = models.Book.objects.filter(pk=5).first()
book_obj.authors.set([2,3])
# 方式二
book_obj = models.Book.objects.filter(pk=5).first()
author_obj1 = models.Author.objects.filter(pk=2).first()
author_obj2 = models.Author.objects.filter(pk=3).first()
book_obj.authors.set([author_obj1, author_obj2])
# 清空
# 清空图书三国演义与作者绑定的关系
book_obj = models.Book.objects.filter(pk=4).first()
book_obj.authors.clear()
clear的括号中不加任何的参数
# 修改操作就是先删除原有的数据,然后重新添加新的数据
正反向的概念
# 正向
# 反向
外键字段在我手上那么,我查你就是正向
外键字段如果不在手上,我查你就是反向
book >>>外键字段在书那儿(正向)>>> publish
publish >>>外键字段在书那儿(反向)>>>book
一对一和多对多正反向的判断也是如此
"""
正向查询按外键字段
反向查询按表名小写
_set
...
"""
多表查询之子查询(基于对象的跨表查询)
# 1.查询书籍主键为3的出版社 book ----正向---> publish 根据外键字段查
book_obj = models.Book.objects.filter(pk=3)
res = book_obj[0].publish
print(res.name)
print(res.addr)
# 2.查询书籍主键为5的作者 book ----正向---> author 根据外键字段查
book_obj = models.Book.objects.filter(pk=5).first()
res = book_obj.authors.all()
for i in res:
print(i.name)
# 3.查询作者peter的电话号码 author ----正向----> authordetail 根据外键字段查询
author_obj = models.Author.objects.filter(name='peter').first()
res = author_obj.author_detail
print(res.phone)
"""
在书写orm语句的时候跟写sql语句一样的
不要企图一次性将orm语句写完 如果比较复杂 就写一点看一点
正向什么时候需要加.all()
当你的结果可能有多个的时候就需要加.all()
如果是一个则直接拿到数据对象
book_obj.publish
book_obj.authors.all()
author_obj.author_detail
"""
# 4.查询出版社是北京出版社出版的书 publish -----反向----> book 根据表名小写_set查
publish_obj = models.Publish.objects.filter(name='北京出版社').first()
res = publish_obj.book_set.all()
for i in res:
print(i.title)
# 5.查询作者是tank写过的书 author -----反向-----> book 根据表名小写_set
author_obj = models.Author.objects.filter(name='tank').first()
res = author_obj.book_set.all()
for i in res:
print(i.title)
# 6.查询手机号是12300的作者姓名 authordetail ------反向-----> author 根据表名小写_set
authordetail_obj = models.AuthorDetail.objects.filter(phone=12300).first()
res = authordetail_obj.author
print(res.name)
'''
基于对象
反向查询
当查询的结果可以有多个的时候必须加上_set.all()
当查询的结果只有一个的时候不需要加_set.all()
'''