ORM之SQL查询、跨表查询

目录

• ORM执行SQL语句
• 神奇的双下划线查询
• ORM外键字段的创建
• 外键字段相关操作
• 多对多三种创建方式
• ORM跨表查询
• 基于对象的跨表查询
• 基于上下划线的跨表查询
• 进阶操作

ORM执行SQL语句

有时候ORM的操作效率可能偏低 我们是可以自己编写SQL的

方式一：
	models.User.objects.raw('select * from app01_user;')
方式二：
	from django.db import connection
    cursor = connection.cursor()
    cursor.execute('select name from app01_user;')
    print(cursor.fetchall())

神奇的双下划线查询

'''
只要还是queryset对象就可以无限制的点queryset对象的方法
queryset.filter().values().filter().values_list().filter()...
'''
# 查询年龄大于18的用户数据
    res = models.User.objects.filter(age__gt=18) #queryset[用户对象...]
# 查询年龄小于38的用户数据
    res = models.User.objects.filter(age__lt=38) #queryset[用户对象...]
# 大于等于  小于等于
    res = models.User.objects.filter(age__gte=18)#queryset[用户对象...]
    res = models.User.objects.filter(age__lte=38)#queryset[用户对象...]
# 查询年龄是18或者28或者38的数据
    res = models.User.objects.filter(age__in=(18,28,38))#queryset[用户对象...]
# 查询年龄在18到38范围之内的数据
    res = models.User.objects.filter(age__range=(18,38))#queryset[用户对象...]
# 查询名字中含有字母j的数据
    res = models.User.objects.filter(name__contains='j')  #区分大小写
    res = models.User.objects.filter(name__icontains='j')  # 不区分大小写
# 查询注册年份是2021的数据
    res = models.User.objects.filter(register_time__year=2021)#queryset[用户对象...]
    print(res)

ORM外键字段的创建

'''
复习MySQL外键关系
	一对多
		外键字段建在多的一方
	多对多
		外键字段统一建在第三张关系表
	一对一
		建在任何一方都可以 但是建议建在查询频率较高的表中
ps：关系的判断可以采用换位思考原则 熟练之后可以瞬间判断
'''
1.创建基础表（书籍表、出版社表、作者表、作者详情表）
2.确定外键关系
	一对多 ORM与MySQL一致 外键字段建在多的一方
	多对多 ORM比MySQL有更多变化
		1.外键字段可以直接建在某张表中（查询频率较高的）
			内部会自动帮你创建第三张关系表
		2.自己创建第三张关系表并创建外键字段
	一对一	ORM与MySQL一致 外键字段建在查询较高的一方
3.ORM创建
	针对一对多和一对一同步到表中之后会自动加_id的后缀
	publish = models.ForeignKey(to='Publish',on_delete=models.CASCADE)
author_detail=models.OneToOneField(to='AuthorDetail',on_delete=models.CASCADE)
	针对多对多 不会在表中有展示 而是创建第三张表
	authors = models.ManyToManyField(to='Author')

多表准备及数据手动添加

models.py：
class Book(models.Model):
    """图书表"""
    title = models.CharField(max_length=32,verbose_name='书名')
    price = models.DecimalField(max_digits=8,decimal_places=2,verbose_name='价格')
    publish_time = models.DateTimeField(auto_now_add=True,verbose_name='出版日期')

    # 创建书籍与出版社的一对多外键字段
    publish = models.ForeignKey(to='Publish',on_delete=models.CASCADE)
    '''django1.x版本外键字段默认都是级联更新删除 django2.x及以上需要自己申明 on_delete=models.xxx'''
    # 创建书籍与作者的多对多外键字段
    authors = models.ManyToManyField(to='Author')
    '''多对多字段为虚拟字段 用于告诉ORM自动创建第三张表'''

    def __str__(self):
        return f'数据对象:{self.title}'


class Publish(models.Model):
    """出版社表"""
    name = models.CharField(max_length=32,verbose_name='名称')
    address = models.CharField(max_length=64,verbose_name='地址')

    def __str__(self):
        return f'出版社对象:{self.name}'

class Author(models.Model):
    """作者表"""
    name = models.CharField(max_length=32,verbose_name='姓名')
    age = models.IntegerField(verbose_name='年龄')
    # 创建作者与作者详情的一对一外键字段
    author_detail = models.OneToOneField(to='AuthorDetail', on_delete=models.CASCADE)

    def __str__(self):
        return f'作者对象：{self.name}'

class AuthorDetail(models.Model):
    """作者详情表"""
    phone = models.BigIntegerField(verbose_name='手机号')
    address = models.CharField(max_length=64,verbose_name='家庭住址')

    def __str__(self):
        return f'详情对象:{self.address}'

执行数据库迁移命令：
python38 manage.py  makemigrations
python38 manage.py migrate

手动补数据：
app01_book:

app01_publish:

app01_author:

app01_authordetail:

外键字段相关操作

# 针对一对多 插入数据可以直接填写表中的实际字段
    models.Book.objects.create(title='三国演义',price=444.22,publish_id=1)
    models.Book.objects.create(title='人性的弱点',price=666.33,publish_id=2)
    # 针对一对多 插入数据也可以填写表中的类中字段名
    publish_obj = models.Publish.objects.filter(pk=1).first()
    models.Book.objects.create(title='水浒传',price=555.33,publish=publish_obj)
	#publish是Book类中的虚拟字段，也等同于book表里面的publish_id字段，就是一对多外键字段，publishi接收对应的数据对象
    '''一对一与一对多 一致'''
    #既可以传数据也可以传对象

实操案例
 # 针对一对多 插入数据可以直接填写表中的实际字段
    models.Book.objects.create(title='三国演义',price=444.22,publish_id=1)
    models.Book.objects.create(title='人性的弱点',price=666.33,publish_id=2)
    # 针对一对多 插入数据也可以填写表中的类中字段名
    publish_obj = models.Publish.objects.filter(pk=1).first()
    models.Book.objects.create(title='水浒传',price=555.33,publish=publish_obj)
    '''一对一与一对多 一致'''
    #既可以传数据也可以传对象

    # 针对多对多关系绑定
	针对ORM第三方表，需要拿到多对多表所在外键字段的数据对象，通过该对象可以对多对多第三方表添加数据
	 book_obj.authors==等价于到第三张表authors
	 book_obj.authors.add(2)给当前这本书绑定ID为2的作者
    book_obj = models.Book.objects.filter(pk=1).first() #数据对象
    book_obj.authors.add(2)  # 在第三张关系表中给当前书籍绑定作者
    book_obj.authors.add(3,4) #给当前这本书绑定ID为3和4的作者
    book_obj=models.Book.objects.filter(pk=5).first()
    author_obj1 = models.Author.objects.filter(pk=3).first()
    author_obj2 = models.Author.objects.filter(pk=4).first()
    book_obj.authors.add(author_obj1)# 给当前这本书绑定作者1对象
    book_obj.authors.add(author_obj1,author_obj2) # 给当前这本书绑定作者1和2对象
    book_obj = models.Book.objects.filter(pk=1).first()
    book_obj.authors.set((2,5))  # 修改关系，支持元组
    book_obj.authors.set([3,4])  # 修改关系，支持列表
    author_obj1 = models.Author.objects.filter(pk=2).first()
    author_obj2= models.Author.objects.filter(pk=3).first()
    book_obj = models.Book.objects.filter(pk=2).first()
    book_obj.authors.set((author_obj1,))# 给当前这本书修改为作者1的对象
    book_obj.authors.set([author_obj1,author_obj2]) # 给当前这本书修改作者1和2对象
    book_obj.authors.remove(2) # 把当前这本书移除作者ID为2
    book_obj.authors.remove(2,3)
    book_obj.authors.remove(author_obj1)
    book_obj.authors.remove(author_obj1,author_obj2)
    book_obj.authors.clear()

    add()\remove()  多个位置参数(数字 对象)
    set()           可迭代对象(元组 列表)数字 对象
    clear()         清空当前数据对象的关系

多对多表关系绑定如下：

多对多三种创建方式

1.全自动创建
class Book(models.Model):
    title = models.CharField(max_length=32)
    authors = models.ManyToManyField(to='Author')
class Author(models.Model):
    name = models.CharField(max_length=32)
  优势:自动创建第三张表 并且提供了add、remove、set、clear四种操作
  劣势:第三张表无法创建更多的字段 扩展性较差

2.纯手动创建
class Book(models.Model):
    title = models.CharField(max_length=32)
    class Author(models.Model):
    name = models.CharField(max_length=32)
class Book2Author(models.Model):
     book = models.ForeignKey(to='Book')
     author = models.ForeignKey(to='Author')
     others = models.CharField(max_length=32)
     join_time = models.DateField(auto_now_add=True)
  优势:第三张表完全由自己创建 扩展性强
  劣势:编写繁琐 并且不再支持add、remove、set、clear以及正反向概念

3.半自动创建
class Book(models.Model):
    title = models.CharField(max_length=32)
    authors = models.ManyToManyField(to='Author',through='Book2Author',      									through_fields=('book','author'))
class Author(models.Model):
    name = models.CharField(max_length=32)
class Book2Author(models.Model):
     book = models.ForeignKey(to='Book', on_delete=models.CASCADE)
     author = models.ForeignKey(to='Author', on_delete=models.CASCADE)
     others = models.CharField(max_length=32)
     join_time = models.DateField(auto_now_add=True)
  优势:第三张表完全由自己创建 扩展性强 正反向概念依然清晰可用
  劣势:编写繁琐不再支持add、remove、set、clear

ORM跨表查询

"""
复习MySQL跨表查询的思路
	子查询
		分步操作：将一条SQL语句用括号括起来当做另外一条SQL语句的条件
	连表操作
		先整合多张表之后基于单表查询即可
			inner join	内连接
			left join	左连接
			right join	右连接
"""
正反向查询的概念（重要）
	正向查询：由外键字段所在的表数据查询关联的表数据  正向
	反向查询：没有外键字段的表数据查询关联的表数据   反向
ps：正反向的核心就看外键字段在不在当前数据所在的表中

ORM跨表查询的口诀（重要）
	正向查询按外键字段
	反向查询按表名小写

基于对象的跨表查询

#1.查询主键为1的书籍对应的出版社名称
    # 先根据条件获取数据对象
    book_obj = models.Book.objects.filter(pk=1).first()#结果为数据对象
    # 在判断正反向的概念 由书查出版社 外键字段在书所在的表中 所以是正向查询
    print(book_obj.publish.name)

#2.查询主键为4的 书籍对应作者姓名
    # 先根据条件获取数据对象
    book_obj = models.Book.objects.filter(pk=4).first()
    # 再判断正反向的概念 由书查作者 外键字段在书所在的表中 所以是正向查询
    print(book_obj.authors)  # app01.Author.None
    print(book_obj.authors.all()) #querset[对象1,对象2]
    print(book_obj.authors.all().values('name'))
#3.查询jason的电话号码
    author_obj = models.Author.objects.filter(name='jason').first()
    print(author_obj.author_detail.phone)
#4.查询北方出版社出版过的书籍
    publish_obj = models.Publish.objects.filter(name='北方出版社').first()
    print(publish_obj.book_set.all().values('title'))
#5.查询jason写过的书籍
    author_obj=models.Author.objects.filter(name='jason').first()
    print(author_obj.book_set.all().values('title'))
#6.查询电话号码是110的作者姓名
    authordetail_obj = models.AuthorDetail.objects.filter(phone=110).first()
    print(authordetail_obj.author.name)

基于上下划线的跨表查询

'''基于双下划线的跨表查询'''
    # 1.查询主键为1的书籍对应的出版社名称
    res = models.Book.objects.filter(pk=1).values('publish__name','title')
    # 2.查询主键为4的书籍对应的作者姓名
    res = models.Book.objects.filter(pk=4).values('authors__name','title')
    # 3.查询jason的电话号码
    res = models.Author.objects.filter(name='jason').values('author_detail__phone','name')
    # 4.查询北方出版社出版的书籍名称和价格
    res=models.Publish.objects.filter(name='北方出版社').values('book__title','book__price')
    # 5.查询jason写过的书籍名称
    res = models.Author.objects.filter(name='jason').values('book__title')
    # 6.查询电话号码是110的作者姓名
    res=models.AuthorDetail.objects.filter(phone=110).values('author__name')
    print(res)

进阶操作

    #1.查询主键为1的书籍对应的出版社名称
    res = models.Publish.objects.filter(book__pk=1).values('name')
    #2.查询主键为4的书籍对应的作者姓名
    res=models.Author.objects.filter(book__pk=4).values('name','book__title')
    #3.查询jason的电话号码
    res = models.AuthorDetail.objects.filter(author__name='jason').values('phone')
    #4.查询北方出版社出版过的书籍名称和价格
    res = models.Book.objects.filter(publish__name='北方出版社').values('title','price')
    #5.查询jason写过的书籍名称
    res = models.Book.objects.filter(authors__name='jason').values('title')
    #6.查询电话号码是110的作者姓名
    res = models.Author.objects.filter(author_detail__phone=110).values('name')
    #7.查询主键为4的书籍对应的作者的电话号码(三张表查询)
    res = models.Book.objects.filter(pk=4).values('authors__author_detail__phone')
    res = models.AuthorDetail.objects.filter(author__book__pk=4).values('phone')
    res = models.Author.objects.filter(book__pk='4').values('author_detail__phone')