08 django模型系统（二）

1.常用模型字段类型及字段参数

#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的常用参数

官方文档：#field-

primary_key:  指定是否为主键。
unique:  指定是否唯一。
null:  指定是否为空，默认为False。
blank: 等于True时form表单验证时可以为空，默认为False。
default:  设置默认值。
DateField.auto_now:  每次修改都会将当前时间更新进去，只有调用，QuerySet.update方法将不会调用。这个参数只是Date和DateTime以及TimModel.save()方法才会调用e类才有的。
DateField.auto_now_add:  第一次添加进去，都会将当前时间设置进去。以后修改，不会修改这个值

常用查询（一）

通过模型类上的管理器来构造。

- 模型类上的管理器是啥？

模型类.

- 表示数据库中对象的集合。通过过滤器查找的结果和范围

等同于 语句。 惰性的

案例：

获取单个（）

- first() 获取第一条 返回的是对象，立即返回

Student.objects.first()

- last() 获取最后一条 返回的也是一个对象

res = Student.objects.last() 

思考：排序规则？ 默认通过主键。通过模型中的_meta 设置

- get(**) 根据给定的条件，获取一个对象，如果有多个对象符合，报错

Student.objects.get(name='litao') 

- all() 获取所有记录 返回的是

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`

- (**) 根据给定的条件，获取一个过滤后的，多个条件和sql使用and连接一样。

In [27]: Student.objects.filter(age=0,sex=1) 
Out[27]: , , , , ]>
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)

- (**) 跟使用方法一致，作用相反，它是排除。可以传递多个参数，使用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对象，.db..Q

In [34]: from django.db.models import QIn [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)

- (*) 返回一个，返回一个字典列表，而不是数据对象。（指定限制字段的作用）可以写多个参数字段，还可以加入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]: '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差不多，但是返回 ，对象列表，注意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]: In [51]: res[0].sex 
Out[51]: 1

- defer(*) 与only用法一样，返回一个，作用和only相反。指定排除中的字段

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` = 0In [55]: res[0] 
Out[55]: In [56]: res[0].id 
Out[56]: 1

- (*) 根据给定的字段来排序 默认是顺序，字段名前加上 ‘-’代表反序。中可以有多个条件。条件顺序无所谓，与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` ASCIn [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. asc desc 的使用场景

In [1]: from teacher.models import StudentIn [2]: Student.objects.all() 
Out[2]: , , , , , , , ]>In [3]: Student.objects.order_by('name') 
Out[3]: , , , , , , , ]>

2.1.实例：按照名字的大写进行排序

In [4]: from django.db.models.functions import LowerIn [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

- 切片 和的列表切片用法相似，不支持负索引，数据量大时不用步长

*** 切片过后，不再支持，附加过滤条件与排序

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 5In [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

常用查询条件

- 支持 ，， get

- exact：准确，使用两个下划线（__）

In [79]: Student.objects.get(id__exact=1) 
Out[79]: In [80]: Student.objects.filter(id__exact=1) 
Out[80]: ]>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` = 1In [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

- ：不区分大小写

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` = LiTaoIn [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

- ：包含(sql语句中有为区分大小写)

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%

- ：不区分大小写，包含

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]: ]>

- in：给定一个可迭代对象，列表，元组，

In [96]: res = Student.objects.filter(name__in=['心蓝','litao','aaaa'])In [97]: res 
Out[97]: , ]>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

- ：大小写敏感，以什么开头

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 新%

- ：大小写不敏感，以什么开头

In [112]: res = Student.objects.filter(name__istartswith = 'L')In [113]: res 
Out[113]: ]>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%

-

-

- True False 对应 IS NULL IS NOT NULL

In [116]: res = Student.objects.filter(qq__isnull=True)In [117]: res 
Out[117]: , , , , ]>

- 聚合

from django.db.models import Count, Avg, Max, Min, Sum  #导入统计数量，平均值，最大值，最小值，求和

count 为Count的快捷方式

In [121]: Student.objects.all().count() 
Out[121]: 5In [122]: Student.objects.filter(name__isnull=True).count() 
Out[122]: 0In [123]: Student.objects.filter(name__isnull=False).count() 
Out[123]: 5

通过的方法

..(=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

- 分组，聚合

结合 , 和聚合方法一起实现

查询男生有几个，女生有几个

In [137]: Student.objects.values('sex').annotate(num=Count('sex')) #num为聚合后的字段名称
Out[137]: '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