'''ORM基础操作'''
'''orm返回的两种数据类型： 1.QuerySet 2.object'''
'''1.QuerySet'''
# 支持链式编程，可以在all后面加方法
qs = models.Teacher.objects.all() #获取全部 Teacher数据qs,返回的是QuerySet
name = qs.values('name') #通过value字段进行过滤,获取我们希望获取到的字段
print(name) #返回QuerySet
count = qs.count() #获取qs的个数
print(count)
firstData = qs.first() #获取qs的第一个数据，等同于list[0]
print(firstData) #返回的是object
valueList = qs.values_list('name') #只获取数据中的value
print(valueList) #返回QuerySet
'''2.object'''
teacher = models.Teacher.objects.get(id=1) #返回的是object
print(teacher.name)
teacher = models.Teacher.objects.first() #只获取第一个数据obj
name = teacher.name #获取其中一个字段
teacher = models.Teacher.objects.raw('select * from teacher;') #执行原生sql qs
'''过滤 orm filter sql的where条件'''
teacher = models.Teacher.objects.filter(name__contains='吴') #过滤，模糊查询, ==sql中%
print(teacher)
teacher = models.Teacher.objects.filter(name__endswith='牛') #过滤，以什么结尾
print(teacher)
teacher = models.Teacher.objects.filter(name__startswith='牛') #过滤，以什么开始
print(teacher)
teacher = models.Teacher.objects.filter(name__in=['冯璐','牛牛']) #过滤，在什么范围内 ==sql的in条件
print(teacher)
teacher = models.Teacher.objects.filter(name__in=['冯璐','牛牛1']) #过滤，在什么范围内 ==sql的in条件
print(teacher)
teacher = models.Teacher.objects.filter(name_isnull=True) #过滤，为空
teacher = models.Teacher.objects.filter(id__gt=1) #过滤，大于
print(teacher)
teacher = models.Teacher.objects.filter(id__gte=1) #过滤，大于等于
print(teacher)
teacher = models.Teacher.objects.filter(id__lt=1) #过滤，小于
print(teacher)
teacher = models.Teacher.objects.filter(id__lte=1) #过滤，小于等于
print(teacher)
teacher = models.Teacher.objects.filter(id__range=[1,4]) #过滤，在1到4之间 between and，包括4
print(teacher)
'''排除，排除的方法和上面的filter是一样的'''
teacher = models.Teacher.objects.exclude(id__gt=2) #排除id大于2的 其他用法和filter相同
print(teacher)
teacher = models.Teacher.objects.exclude(name='牛牛')
print(teacher)
'''多条件查询'''
# and操作
teacher = models.Teacher.objects.filter(id=1,name='牛牛') #where id=1 and name='牛牛'
print(teacher)
# or操作
# 通过django中提供的q来实现负责的过滤
# 导入 from django.db.models import Q
teacher =models.Teacher.objects.filter(Q(name='大师兄')&Q(id=4)) #&相当于sql中的and
print(teacher)
teacher =models.Teacher.objects.filter(Q(name='大师兄')|Q(id=2)) #&相当于sql中的or
print(teacher)
# 在 Q前面可以通过~取反，代表name!=大师兄 and id=2
teacher =models.Teacher.objects.filter(~Q(name='大师兄')&Q(id=2))
print(teacher)
# 使用Q之外，还可以使用关键字参数，再次进行过滤，Q必须写在关键字参数的前面
teacher =models.Teacher.objects.filter(~Q(name='大师兄')&Q(id=3),name__contains='吴')
print(teacher)