Django模型系统二
Django模型系统(二)
一、常用的查询
每一个django模型类,都有一个默认的管理器,objects。
QuerySet表示数据库中对象的列表,它可以有0到多个过滤器,过滤器通过给定参数,缩小查询范围(filter)
QuerySet等同于select语句,过滤器是一个限制子句,比如where,Limit等。
常用参数如下:
1、all()
获取所有的。返回的是queryset
res = Student.objects.all()
SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`QQ`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student`
2、first()
获取第一条。返回的是对象
In [60]: res = Student.objects.first()
3、last()
获取最后一条。返回的是对象
In [65]: res = Student.objects.last()
4、get(**kwargs)
根据给定的条件获取一个对象,如果符合多个或没有就报错
res = Student.objects.get(name="李志荣",age=16)
5、filter(**kwargs)
根据参数提供的条件,获取一个过滤器的QuerySet,多个条件使用and连接关键字参数的形参必须是模型中的字段名
In [81]: res=Student.objects.filter(name="沈",age=28)
6、exclude(**kwargs)
用法和filter一样,作用相反,它是排除
In [83]: res=Student.objects.exclude(age=28)
7、order_by(*fields)
根据给定的字段排序
正向排序:In [85]: res=Student.objects.all().order_by('age')
反向排序:In [89]: res=Student.objects.all().order_by('-age')
8、切片
使用列表的切片语法操作query,除了不能用负索引,其他的都可以,它等价于Limit的offset子句
In [91]: res=Student.objects.all()[2:5]
In [93]: res=Student.objects.all().order_by('-age')[3:5]
9、values(*fields)
返回queryset,这个queryset返回的是一个字典列表,参数fields指定了select中我们想要限制查询的字段,返回的字典列表中,只会包含我们指定的字段,如果不指定,包含所有字段。
In [95]: res=Student.objects.values()
In [97]: res=Student.objects.values('name')
10、only(*fields)
返回一个queryset,跟values一样,区别在于这个queryset是对象列表,only一定包含主键。
In [99]: res=Student.objects.only('QQ')
In [101]: print(res.query)
SELECT `teacher_student`.`id`, `teacher_student`.`QQ` FROM `teacher_student`
11、defer(*fields)
用法与only相反,表示除了指定字段不要,其它字段都要
In [102]: res=Student.objects.defer('QQ')
In [104]: print(res.query)
SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student`
12、多条件OR连接
需要实现 OR 条件,需要用 Q 对象
In [108]: res = Student.objects.filter(Q(age=18)|Q(age=24)) 表示或者用(“|”)
In [110]: res = Student.objects.filter(Q(age=18),Q(age=24)) 表示and 用(“,”)
二、查询条件
用法(field__condition)是两个下划线表示
常用如下:
1、exact(精确匹配)
In [112]: res = Student.objects.filter(name__exact="小沈")
2、iexact(忽略大小写)
3、contains(包含)
In [114]: res = Student.objects.filter(name__contains="李")
4、icontains(忽略大小写)
5、in
In [116]: res = Student.objects.filter(age__in=[16,39])
In [118]: 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` FROM `teacher_student` WHERE `teacher_student`.`age` IN (16, 39)
6、gt(大于)
In [119]: res = Student.objects.filter(age__gt=24)
7、gte(大于等于)
In [121]: res = Student.objects.filter(age__gte=24)
In [123]: 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` FROM `teacher_student` WHERE `teacher_student`.`age` >= 24
8、Lt(小于)
In [124]: res = Student.objects.filter(age__lt=24)
In [126]: 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` FROM `teacher_student` WHERE `teacher_student`.`age` < 24
9、Lte(小于等于)
In [127]: res = Student.objects.filter(age__lte=24)
In [129]: 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` FROM `teacher_student` WHERE `teacher_student`.`age` <= 24
10、startswith(以什么开始)
In [130]: res = Student.objects.filter(name__startswith="李")
In [132]: 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` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE BINARY 李%
11、istartswith(忽略大小写)
12、endswith(以什么结尾)
In [134]: res = Student.objects.filter(name__endswith="毅")
In [136]: 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` FROM `teacher_student` WHERE `teacher_student`.`name` LIKE BINARY %毅
13、iendswith(忽略大小写)
14、range(范围区间)
In [138]: res = Student.objects.filter(age__range=[18,38])
In [140]: 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` FROM `teacher_student` WHERE `teacher_student`.`age` BETWEEN 18 AND 38
15、isnull
In [141]: res = Student.objects.filter(QQ__isnull=True)
In [143]: res = Student.objects.filter(QQ__isnull=False)
三、聚合函数
首先要导入 from django.db.models import Avg,Max,Min,Sum
1、Avg(平均值)
In [4]: res = Student.objects.aggregate(age_ave=Avg('age'))
In [5]: res
Out[5]: {'age_ave': 26.8333}
2、Max(最大值)
In [6]: res = Student.objects.aggregate(age_max=Max('age'))
In [7]: res
Out[7]: {'age_max': 39}
3、Min(最小值)
In [8]: res = Student.objects.aggregate(age_min=Min('age'))
In [9]: res
Out[9]: {'age_min': 16}
4、Sum(求和)
In [10]: res = Student.objects.aggregate(age_sum=Sum('age'))
In [11]: res
Out[11]: {'age_sum': 161}
四、分组
注:需要结合values和annotate和聚合方法
例:查询男、女生年龄平均多少岁
In [5]: res = Student.objects.values('sex').annotate(Avg('age'))
In [6]: res
Out[6]: <QuerySet [{'age__avg': 27.6667, 'sex': 1}, {'age__avg': 26.0, 'sex': 2}]>
五、表关系的实现
例:在models.py中写以下几个模型
class Student(models.Model):
name = models.CharField(verbose_name='姓名', max_length=20)
age = models.SmallIntegerField(verbose_name='年龄',null=True)
sex = models.SmallIntegerField(default=1) #表示默认就是1
QQ = models.CharField(max_length=20,null=True)
phone = models.CharField(max_length=20, default="")
c_time = models.DateTimeField(verbose_name='创建时间',auto_now_add=True)
e_time = models.DateTimeField(verbose_name='最后修改时间', auto_now=True)
grade = models.ForeignKey('Grade',on_delete=models.SET_NULL, null=True)
def __str__(self):
return self.name
class StudentDetail(models.Model):
student=models.OneToOneField('Student', on_delete=models.CASCADE)
card_num = models.CharField('身份证号码', max_length=20,null=True,unique=True)
college = models.CharField('毕业学院', max_length=20,default="无学校")
class Grade(models.Model):
name = models.CharField('班级名称', max_length=20)
num = models.CharField('班期', max_length=20)
class Course(models.Model):
name = models.CharField('课程名称',max_length=20)
students = models.ManyToManyField('Student', through='Enroll')
class Enroll(models.Model):
student = models.ForeignKey('Student', on_delete=models.CASCADE)
course = models.ForeignKey('Course', on_delete=models.CASCADE)
c_time = models.DateTimeField(auto_now_add=True)
paid = models.FloatField('付款', default=0)
------------------------------------------------------
一对一关系: StudentDetail类 与 Student类 建立为一对一关系,在 StudentDetail类 中写入
student = models.OneToOneField('Student', on_delete=models.CASCADE)
备注: 'Student'表示模型类名称; on_delete=models.CASCADE 表示学生删除了,学生信息也删除了
---------------------------------------------------------------
一对多关系:Grade类 与 Student类 建立为一对多关系,在Student类中写入
grade = models.ForeignKey('Grade', on_delete=models.SET_NULL, null=True)
备注: 'Grade'表示模型类名称; on_delete=models.SET_NULL 表示班级删除了,学生不删除了
-------------------------------------------------------
多对多关系:如果不需要额外字段的话,不用手动创建第三张表
如果需要额外字段的话,就要手动创建第三张表,要结合要用到 through = '第三张表名称'
第一种:Course类 与 Student类 建立为多对多关系,不需要额外字段,只要在Course类中写入
students = models.ManyToManyField('Student')
第二种:Course类 与 Student类 建立为多对多关系,要额外字段,所以还要手动创建Enroll类,在Course类中写入
students = models.ManyToManyField('Student', through='Enroll')
在 Enroll类中写入外键关链
student = models.ForeignKey('Student', on_delete=models.CASCADE)
course = models.ForeignKey('Course', on_delete=models.CASCADE)