Django model select的多种用法详解的多种用法详解
最近看到 大神 运维咖啡吧的 一篇文章,这里只做个备份,讲的是各种 django model 查询,发现不错,十分感谢该大神的无私奉献
《Django model update的各种用法介绍》文章介绍了Django model的各种update操作,这篇文章就是她的姊妹篇,详细介绍Django model select的用法,配以对应MySQL的查
询语句,理解起来更轻松。
基本操作基本操作
# 获取所有数据,对应SQL:select * from User
User.objects.all()
# 匹配,对应SQL:select * from User where name = '运维咖啡吧'
User.objects.filter(name='运维咖啡吧')
# 不匹配,对应SQL:select * from User where name != '运维咖啡吧'
User.objects.exclude(name='运维咖啡吧')
# 获取单条数据(有且仅有一条,id唯一),对应SQL:select * from User where id = 724
User.objects.get(id=123)
常用操作常用操作
# 获取总数,对应SQL:select count(1) from User
User.objects.count()
# 获取总数,对应SQL:select count(1) from User where name = '运维咖啡吧'
User.objects.filter(name='运维咖啡吧').count()
# 大于,>,对应SQL:select * from User where id > 724
User.objects.filter(id__gt=724)
# 大于等于,>=,对应SQL:select * from User where id >= 724
User.objects.filter(id__gte=724)
# 小于,<,对应SQL:select * from User where id < 724
User.objects.filter(id__lt=724)
# 小于等于,<=,对应SQL:select * from User where id <= 724
User.objects.filter(id__lte=724)
# 同时大于和小于, 1 < id < 10,对应SQL:select * from User where id > 1 and id < 10
User.objects.filter(id__gt=1, id__lt=10)
# 包含,in,对应SQL:select * from User where id in (11,22,33)
User.objects.filter(id__in=[11, 22, 33])
# 不包含,not in,对应SQL:select * from User where id not in (11,22,33)
User.objects.exclude(id__in=[11, 22, 33])
# 为空:isnull=True,对应SQL:select * from User where pub_date is null
User.objects.filter(pub_date__isnull=True)
# 不为空:isnull=False,对应SQL:select * from User where pub_date is not null
User.objects.filter(pub_date__isnull=True)
# 匹配,like,大小写敏感,对应SQL:select * from User where name like '%sre%',SQL中大小写不敏感
User.objects.filter(name__contains="sre")
# 匹配,like,大小写不敏感,对应SQL:select * from User where name like '%sre%',SQL中大小写不敏感
User.objects.filter(name__icontains="sre")
# 不匹配,大小写敏感,对应SQL:select * from User where name not like '%sre%',SQL中大小写不敏感
User.objects.exclude(name__contains="sre")
# 不匹配,大小写不敏感,对应SQL:select * from User where name not like '%sre%',SQL中大小写不敏感
User.objects.exclude(name__icontains="sre")
# 范围,between and,对应SQL:select * from User where id between 3 and 8
User.objects.filter(id__range=[3, 8])
# 以什么开头,大小写敏感,对应SQL:select * from User where name like 'sh%',SQL中大小写不敏感
User.objects.filter(name__startswith='sre')
# 以什么开头,大小写不敏感,对应SQL:select * from User where name like 'sh%',SQL中大小写不敏感
User.objects.filter(name__istartswith='sre')
# 以什么结尾,大小写敏感,对应SQL:select * from User where name like '%sre',SQL中大小写不敏感
User.objects.filter(name__endswith='sre')
# 以什么结尾,大小写不敏感,对应SQL:select * from User where name like '%sre',SQL中大小写不敏感
User.objects.filter(name__iendswith='sre')
# 排序,order by,正序,对应SQL:select * from User where name = '运维咖啡吧' order by id
User.objects.filter(name='运维咖啡吧').order_by('id')
# 多级排序,order by,先按name进行正序排列,如果name一致则再按照id倒叙排列
User.objects.filter(name='运维咖啡吧').order_by('name','-id')
# 排序,order by,倒序,对应SQL:select * from User where name = '运维咖啡吧' order by id desc
User.objects.filter(name='运维咖啡吧').order_by('-id')
进阶操作进阶操作
# limit,对应SQL:select * from User limit 3;
User.objects.all()[:3]
# limit,取第三条以后的数据,没有对应的SQL,类似的如:select * from User limit 3,10000000,从第3条开始取数据,取10000000条(10000000大于表中数据条数)
User.objects.all()[3:]
# offset,取出结果的第10-20条数据(不包含10,包含20),也没有对应SQL,参考上边的SQL写法
User.objects.all()[10:20]
# 分组,group by,对应SQL:select username,count(1) from User group by username;
from django.db.models import Count
User.objects.values_list('username').annotate(Count('id'))
# 去重distinct,对应SQL:select distinct(username) from User
User.objects.values('username').distinct().count()
# filter多列、查询多列,对应SQL:select username,fullname from accounts_user
User.objects.values_list('username', 'fullname')