本文转载自https://blog.csdn.net/xiaogeldx/article/details/87927345
模型的类对应的是数据库中的表，模型的类属性对应表的字段
DateField.auto_now_add：第一次添加进去，都会将当前时间设置进去，以后修改，不会修改这个值
DateTimeField：日期时间类型，映射到数据库中的是datetime类型，在使用的时候，传递datetime.datetime()进去
int--------------------------IntegetField
varchar----------------------CharField
longtext----------------------TextField
date--------------------------DateField
datetime--------------DateTimeField
filter(**kwargs)根据给定的条件，获取符合条件的过滤后的QuerySet，多个条件时使用and连接
In [19]: res = Student.objects.filter(age=17,sex=0) #选出age=17 and sex=0的数据
In [21]: res
Out[21]: <QuerySet [<Student: na-17>]>exclude(**kwargs)根据给定的条件，获取不符合条件的过滤后的QuerySet，和filter使用方法一致，作用刚好相反，它是排除
In [24]: res = Student.objects.exclude(sex=0,age=16) #除了age=16 and sex=0的数据，剩下的都要
In [25]: res
Out[25]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>, <Student: long-19>, <Student: di-18>]> #因为没有符合条件的数据，所以都取多条件的or连接，用到Q对象，django.db.models.Q
In [27]: from django.db.models import Q
In [28]: Student.objects.filter(Q(sex=0)|Q(sex=1)) #获取性别是男或者是女
Out[28]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>, <Student: long-19>, <Student: di-18>]> #所有数据都符合values(*fields)指定字段（可以多个字段），返回一个QuerySet（QuerySet里是字典列表，而不是数据对象）,后面还可以加n个filter()
In [34]: res = Student.objects.values('name','sex').filter(age=17)
In [35]: res
Out[35]: <QuerySet [{'name': 'na', 'sex': 0}]>
In [36]: res = Student.objects.values('name','age').filter(age=17).filter(sex=0)
In [37]: res
Out[37]: <QuerySet [{'name': 'na', 'age': 17}]>
- only(*)返回QuerySet（里面是对象列表），一定包含主键字段，only用的比较多，可以指定很少的字段，高效
In [38]: res = Student.objects.only('name','age').filter(sex=0)
In [39]: res
Out[39]: <QuerySet [<Student: na-17>, <Student: di-18>]>
In [40]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name`, `students_student`.`age` FROM `students_student` WHERE `students_student`.`sex` = 0 #主键id
In [41]: res = Student.objects.only('name','age').filter(sex=0).filter(name='di')
In [42]: res
Out[42]: <QuerySet [<Student: di-18>]>
In [43]: res[0].c_time
Out[43]: datetime.datetime(2019, 2, 26, 0, 27, 28, 626041, tzinfo=<UTC>)defer(*fields)返回一个QuerySet，和only用法一样，作用相反，fields是指定排除的字段
In [44]: res = Student.objects.defer('c_time','age')
In [46]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name`, `students_student`.`sex`, `students_student`.`qq`, `students_student`.`phone`, `students_student`.`e_time` FROM `students_student`order_by(*fields)根据给定字段来排序
In [47]: res = Student.objects.order_by('c_time').only('name')
In [48]: res
Out[48]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>, <Student: long-19>, <Student: di-18>]>
In [49]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name` FROM `students_student` ORDER BY `students_student`.`c_time` ASCorder_by(*-fields)根据给定字段来倒序(和正序唯一区别是在字段名前加个负号）
In [52]: res = Student.objects.order_by('-c_time').only('name')
In [53]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name` FROM `students_student` ORDER BY `students_student`.`c_time` DESC
In [54]: res
Out[54]: <QuerySet [<Student: di-18>, <Student: long-19>, <Student: na-17>, <Student: xiaoge-16>]>切片，和Python列表切片用法相似，不支持负索引，数据量大时不用步长
切片后不再支持附加过滤条件与排序
In [55]: res = Student.objects.all()[:2]
In [56]: res
Out[56]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>]>
In [57]: res = Student.objects.all()[2:3]
In [58]: res
Out[58]: <QuerySet [<Student: long-19>]>
In [59]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name`, `students_student`.`age`, `students_student`.`sex`, `students_student`.`qq`, `students_student`.`phone`, `students_student`.`c_time`, `students_student`.`e_time` FROM `students_student` LIMIT 1 OFFSET 2
In [60]: res = Student.objects.all()[1:3]
In [61]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name`, `students_student`.`age`, `students_student`.`sex`, `students_student`.`qq`, `students_student`.`phone`, `students_student`.`c_time`, `students_student`.`e_time` FROM `students_student` LIMIT 2 OFFSET 1 *offset偏移坐标，偏移的位置
In [62]: res = Student.objects.all()[::1]
In [63]: res
Out[63]: [<Student: xiaoge-16>, <Student: na-17>, <Student: long-19>, <Student: di-18>]exact准确匹配（__exact）
In [65]: res = Student.objects.filter(id__exact=13)
In [66]: res
Out[66]: <QuerySet [<Student: di-18>]>iexact模糊匹配，不区分大小写(__iexact)
In [67]: res = Student.objects.filter(name__iexact='XIAOGE')
In [69]: res
Out[69]: <QuerySet [<Student: xiaoge-16>]>contains包含，在首尾亦可(__contains)
In [71]: res = Student.objects.filter(name__contains='ao')
In [72]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name`, `students_student`.`age`, `students_student`.`sex`, `students_student`.`qq`, `students_student`.`phone`, `students_student`.`c_time`, `students_student`.`e_time` FROM `students_student` WHERE `students_student`.`name` LIKE BINARY %ao%
In [73]: res
Out[73]: <QuerySet [<Student: xiaoge-16>]>
icontains不区分大小写的包含(__icontains)
In [74]: res = Student.objects.filter(name__icontains='ON')
In [75]: res
Out[75]: <QuerySet [<Student: long-19>]>in给定一个可迭代的对象（列表，元组，QuerySet）
In [76]: res = Student.objects.filter(name__in=['xiaoge','小哥','aaa'])
In [77]: res
Out[77]: <QuerySet [<Student: xiaoge-16>]>
In [78]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name`, `students_student`.`age`, `students_student`.`sex`, `students_student`.`qq`, `students_student`.`phone`, `students_student`.`c_time`, `students_student`.`e_time` FROM `students_student` WHERE `students_student`.`name` IN (xiaoge, 小哥, aaa)range范围
In [79]: res = Student.objects.filter(age__range=(16,19))
In [80]: res
Out[80]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>, <Student: long-19>, <Student: di-18>]>
In [81]: print(res.query)
SELECT `students_student`.`id`, `students_student`.`name`, `students_student`.`age`, `students_student`.`sex`, `students_student`.`qq`, `students_student`.`phone`, `students_student`.`c_time`, `students_student`.`e_time` FROM `students_student` WHERE `students_student`.`age` BETWEEN 16 AND 19gt大于，gte大于等于，lt小于，lte小于等于
In [82]: res = Student.objects.filter(age__gt=18)
In [83]: res
Out[83]: <QuerySet [<Student: long-19>]>
In [84]: res = Student.objects.filter(age__gte=18)
In [85]: res
Out[85]: <QuerySet [<Student: long-19>, <Student: di-18>]>
In [86]: res = Student.objects.filter(age__lte=18)
In [87]: res
Out[87]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>, <Student: di-18>]>
In [88]: res = Student.objects.filter(age__lt=18)
In [89]: res
Out[89]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>]>
endswith以...结尾，区分大小写，iendswith，不区分大小写
In [90]: res = Student.objects.filter(name__startswith='xi')
In [91]: res
Out[91]: <QuerySet [<Student: xiaoge-16>]>
In [92]: res = Student.objects.filter(name__endswith='na')
In [93]: res
Out[93]: <QuerySet [<Student: na-17>]>
In [94]: res = Student.objects.filter(name__iendswith='NG')
In [95]: res
Out[95]: <QuerySet [<Student: long-19>]>
isnull，值为True或False，对应MySQL中的IS NULL和IS NOT NULL，判断是否为空
In [97]: res = Student.objects.filter(name__isnull=True)
In [98]: res
Out[98]: <QuerySet []>
In [99]: res = Student.objects.filter(name__isnull=False)
In [100]: res
Out[100]: <QuerySet [<Student: xiaoge-16>, <Student: na-17>, <Student: long-19>, <Student: di-18>]>count统计数量，Avg平均值，Max最大值，Min最小值，Sum求和
In [102]: Student.objects.all().count()
Out[102]: 4
In [103]: Student.objects.filter(age__gt=16).count()
Out[103]: 3
In [104]: from django.db.models import Avg,Count,Max,Min,Sum
In [105]: Student.objects.aggregate(age_avg=Avg('age'))
Out[105]: {'age_avg': 17.5}
In [106]: Student.objects.filter(sex=1).aggregate(max_avg=Max('age'))
Out[106]: {'max_avg': 19}
In [107]: Student.objects.filter(sex=0).aggregate(age_min=Min('age'))
Out[107]: {'age_min': 17}
In [108]: Student.objects.filter(sex=0).aggregate(age_sum=Sum('age'))
Out[108]: {'age_sum': 35}
结合Values，annotate和聚合方法一起实现
#查询男生女生分别是多少个
In [109]: Student.objects.values('sex').annotate(num=Count('sex'))
Out[109]: <QuerySet [{'sex': 1, 'num': 2}, {'sex': 0, 'num': 2}]>
In [110]: Student.objects.values('sex').annotate(Count('sex'))
Out[110]: <QuerySet [{'sex': 1, 'sex__count': 2}, {'sex': 0, 'sex__count': 2}]>
原文：https://www.cnblogs.com/xiaogeldx/p/10436191.html