1.常用模型字段类型及字段参数
https://docs.djangoproject.com/en/2.1/ref/models/fields/#field-types
常用的字段类型：
1. IntegerField : 整型，映射到数据库中的int类型。 2. CharField: 字符类型，映射到数据库中的varchar类型，通过max_length指定最大长度。 3. TextField: 文本类型，映射到数据库中的text类型。 4. BooleanField: 布尔类型，映射到数据库中的tinyint类型，在使用的时候，传递True/False进去。如果要可以为空，则用NullBooleanField。 5. DateField: 日期类型，没有时间。映射到数据库中是date类型， 在使用的时候，可以设置DateField.auto_now每次保存对象时，自动设置该字段为当前时间。设置DateField.auto_now_add当对象第一次被创建时自动设置当前时间。 6. DateTimeField: 日期时间类型。映射到数据库中的是datetime类型， 在使用的时候，传递datetime.datetime()进去。.
7. AutoField :Int 类型，用于自定义主键的创建，并且具有自增长的属性，再加上primary=True。就是主键。一般情况下不会自定义，系统会自动创建主键
2.field的常用参数
官方文档：https://docs.djangoproject.com/en/2.1/ref/models/fields/#field-options
primary_key: 指定是否为主键。
unique: 指定是否唯一。
null: 指定是否为空，默认为False。
blank: 等于True时form表单验证时可以为空，默认为False。
default: 设置默认值。
DateField.auto_now: 每次修改都会将当前时间更新进去，只有调用，QuerySet.update方法将不会调用。这个参数只是Date和DateTime以及TimModel.save()方法才会调用e类才有的。
DateField.auto_now_add: 第一次添加进去，都会将当前时间设置进去。以后修改，不会修改这个值
常用查询（一）
通过模型类上的管理器来构造QuerySet。
- 模型类上的管理器是啥？
模型类.objects
- QuerySet 表示数据库中对象的集合。通过过滤器查找的结果和范围
等同于select 语句。 惰性的
案例：
获取单个（）
- first() 获取第一条 返回的是对象，立即返回
Student.objects.first()
- last() 获取最后一条 返回的也是一个对象
res = Student.objects.last()
思考：排序规则？ 默认通过主键。通过模型中的_meta 设置
- get(**kwargs) 根据给定的条件，获取一个对象，如果有多个对象符合，报错
Student.objects.get(name='litao')
- all() 获取所有记录 返回的是queryset
Student.objects.create(name='蒸发贵') In [10]: res = Student.objects.all() In [11]: print(res.query) #打印语句 SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student`
- filter(**kwargs) 根据给定的条件，获取一个过滤后的queryset，多个条件和sql使用and连接一样。
In [27]: Student.objects.filter(age=0,sex=1) Out[27]: <QuerySet [<Student: 1-心蓝-0>, <Student: 2-litao-0>, <Student: 3-魏明凯-0>, <Student: 4-董浩-0>, <Student: 5-蒸发贵-0>]> In [28]: res = Student.objects.filter(age=0,sex=1) In [29]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE (`teacher_student`.`age` = 0 AND `teacher_student`.`sex` = 1)
- exclude(**kwargs) 跟filter使用方法一致，作用相反，它是排除。可以传递多个参数，使用and连接
In [30]: res = Student.objects.exclude(sex=0) In [31]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE NOT (`teacher_student`.`sex` = 0) #多个参数： In [32]: res = Student.objects.exclude(sex=0,age=1) In [33]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE NOT (`teacher_student`.`age` = 1 AND `teacher_student`.`sex` = 0)
常用查询（二）
- 多条件的OR连接 用到Q对象，django.db.models.Q
In [34]: from django.db.models import Q In [35]: res = Student.objects.filter(Q(age=0)|Q(age=1)) #查询age=1或者age=0的学生 In [36]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE (`teacher_student`.`age` = 0 OR `teacher_student`.`age` = 1)
- values(*fields) 返回一个queryset，返回一个字典列表，而不是数据对象。（指定限制字段的作用）可以写多个参数字段，还可以加入field过滤条件
In [38]: res = Student.objects.values('name')
In [39]: print(res.query) SELECT `teacher_student`.`name` FROM `teacher_student` In [40]: res Out[40]: <QuerySet [{'name': '心蓝'}, {'name': 'litao'}, {'name': '魏明凯'}, {'name': '董浩'}, {'name': '蒸发贵'}]> In [41]: res[0] Out[41]: {'name': '心蓝'} In [42]: res[0]['name'] Out[42]: '心蓝' In [45]: res = Student.objects.values('name','age').filter(age=0) In [46]: print(res.query) SELECT `teacher_student`.`name`, `teacher_student`.`age` FROM `teacher_student` WHERE `teacher_student`.`age` = 0
- only(*fiels) 和value差不多，但是返回querySet ，对象列表，注意only一定包含主键字段
In [48]: res = Student.objects.only('name','age').filter(age=0) In [49]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age` FROM `teacher_student` WHERE `teacher_student`.`age` = 0 In [50]: res[0] Out[50]: <Student: 1-心蓝-0> In [51]: res[0].sex Out[51]: 1
- defer(*fields) 与only用法一样，返回一个QuerySet，作用和only相反。指定排除fields中的字段
In [53]: res = Student.objects.defer('c_time','e_time').filter(age=0) In [54]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone` FROM `teacher_student` WHERE `teacher_student`.`age` = 0 In [55]: res[0] Out[55]: <Student: 1-心蓝-0> In [56]: res[0].id Out[56]: 1
- order_by(*fields) 根据给定的字段来排序 默认是顺序，字段名前加上 ‘-’代表反序。order_by中可以有多个条件。条件顺序无所谓，order_by与only顺序无所谓。
In [60]: res = Student.objects.order_by('c_time').only('name') In [62]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name` FROM `teacher_student` ORDER BY `teacher_student`.`c_time` ASC In [63]: res = Student.objects.order_by('-c_time').only('name') In [64]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name` FROM `teacher_student` ORDER BY `teacher_student`.`c_time` DESC
2.order_by asc desc 的使用场景
In [1]: from teacher.models import Student In [2]: Student.objects.all() Out[2]: <QuerySet [<Student: 1-心蓝-0>, <Student: 2-litao-0>, <Student: 3-魏明凯-0>, <Student: 4-董浩-0>, <Student: 5-蒸发贵-0>, <Student: 6-叶丽-0>, <Student: 7-xiaoli-0>, <Student: 8<Student: 9-如花-0>]> In [3]: Student.objects.order_by('name') Out[3]: <QuerySet [<Student: 2-litao-0>, <Student: 7-xiaoli-0>, <Student: 8-zhuyin-0>, <Student: 6-叶丽-0>, <Student: 9-如花-0>, <Student: 1-心蓝-0>, <Student: 4-董浩-0>, <Student: 5-蒸Student: 3-魏明凯-0>]>
2.1.实例：按照名字的大写进行排序
In [4]: from django.db.models.functions import Lower In [5]: res = Student.objects.order_by(Lower('name')) In [6]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` ORDER BY LOWER(`teacher_student`.`name`) ASC
2.2 按照名字的大写进行排序，倒叙：
In [7]: res = Student.objects.order_by(Lower('name').desc()) In [8]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` ORDER BY LOWER(`teacher_student`.`name`) DESC
- 切片 和python的列表切片用法相似，不支持负索引，数据量大时不用步长
*** 切片过后，不再支持，附加过滤条件与排序
In [68]: res = Student.objects.all()[:5] In [69]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` LIMIT 5 In [70]: res = Student.objects.all()[2:3] In [71]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` LIMIT 1 OFFSET 2
常用查询条件
- 支持 filter，exclude， get
- exact：准确，使用两个下划线（__）
In [79]: Student.objects.get(id__exact=1) Out[79]: <Student: 1-心蓝-0> In [80]: Student.objects.filter(id__exact=1) Out[80]: <QuerySet [<Student: 1-心蓝-0>]> In [81]: res = Student.objects.filter(id__exact=1) In [82]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`id` = 1 In [83]: res = Student.objects.filter(id=1) In [84]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`id` = 1
- iexact：不区分大小写
In [85]: res = Student.objects.filter(name = 'LiTao') In [86]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` = LiTao In [87]: res = Student.objects.filter(name__iexact = 'LiTao') In [88]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE LiTao
- contains：包含(sql语句中有BINARY为区分大小写)
In [89]: res = Student.objects.filter(name__contains = 'LiTao')
In [90]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE BINARY %LiTao%
- icontains：不区分大小写，包含
In [91]: res = Student.objects.filter(name__icontains = 'LiTao') In [92]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE %LiTao%
In [93]: res
Out[93]: <QuerySet [<Student: 2-litao-0>]>
- in：给定一个可迭代对象，列表，元组，queryset
In [96]: res = Student.objects.filter(name__in=['心蓝','litao','aaaa']) In [97]: res Out[97]: <QuerySet [<Student: 1-心蓝-0>, <Student: 2-litao-0>]> In [98]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` IN (心蓝, litao, aaaa)
- range：范围
In [99]: res = Student.objects.filter(age__range=[18,20]) In [100]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` BETWEEN 18 AND 20
- gt：大于
In [101]: res = Student.objects.filter(age__gt=10) In [102]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` > 10
- gte：大于等于
In [103]: res = Student.objects.filter(age__gte=10) In [104]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` >= 10
- lt：小于
In [105]: res = Student.objects.filter(age__lt=10) In [106]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` < 10
- lte：小于等于
In [107]: res = Student.objects.filter(age__lte=10) In [108]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`age` <= 10
- startswith ：大小写敏感，以什么开头
In [109]: res = Student.objects.filter(name__startswith = '新') In [110]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE BINARY 新%
- istartswith：大小写不敏感，以什么开头
In [112]: res = Student.objects.filter(name__istartswith = 'L') In [113]: res Out[113]: <QuerySet [<Student: 2-litao-0>]> In [114]: print(res.query) SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE L%
- endswith
- iendswith
- isnull True False 对应 IS NULL IS NOT NULL
In [116]: res = Student.objects.filter(qq__isnull=True) In [117]: res Out[117]: <QuerySet [<Student: 1-心蓝-0>, <Student: 2-litao-0>, <Student: 3-魏明凯-0>, <Student: 4-董浩-0>, <Student: 5-蒸发贵-0>]>
- 聚合
from django.db.models import Count, Avg, Max, Min, Sum #导入统计数量，平均值，最大值，最小值，求和
count 为Count的快捷方式
In [121]: Student.objects.all().count() Out[121]: 5 In [122]: Student.objects.filter(name__isnull=True).count() Out[122]: 0 In [123]: Student.objects.filter(name__isnull=False).count() Out[123]: 5
通过queryset的aggregate方法
Student.objects.aggregate(age_avg=Avg('age')) # 计算平均年龄
In [126]: Student.objects.aggregate(age_avg=Avg('age')) #avg_age为自定义的字段名，为字典的key Out[126]: {'age_avg': 0.0} In [129]: Student.objects.filter(age__gte=0).aggregate(age_avg=Avg('age')) Out[129]: {'age_avg': 0.0} In [130]: Student.objects.filter(age__gte=0).aggregate(age_Max=Max('age')) Out[130]: {'age_Max': 0} In [131]: Student.objects.filter(age__gte=0).aggregate(age_Sum=Sum('age')) Out[131]: {'age_Sum': 0}
- count
- 平均值 Avg
- 分组，聚合
结合 Values,annotate 和聚合方法一起实现
查询男生有几个，女生有几个
In [137]: Student.objects.values('sex').annotate(num=Count('sex')) #num为聚合后的字段名称 Out[137]: <QuerySet [{'sex': 1, 'num': 5}, {'sex': 0, 'num': 4}]> In [138]: res= Student.objects.values('sex').annotate(num=Count('sex')) In [139]: print(res.query) SELECT `teacher_student`.`sex`, COUNT(`teacher_student`.`sex`) AS `num` FROM `teacher_student` GROUP BY `teacher_student`.`sex` ORDER BY NULL