1.ORM介绍
2.准备工作
2.1 创建表(以学生信息表为例)
2.1.1 确认实体和实体属性
学生:姓名,年龄, 所在班级
班级:班级名称,任课老师
教师:姓名
课程:课程名称,授课老师
成绩:学生, 课程,成绩
2.1.2 确认实体之间的关系
班级表----学生表:一对多
班级表----教师表:多对多(由ORM自动创建第三张表关联)
课程表----教师表:一对多
学生表----课程表:多对多(由成绩表关联,需手动创建,s_id与co_id为联合主键)
2.1.3创建类生成数据表
ORM与关系型数据库的对应关系
关系型数据库 | ORM |
---|---|
表 | 类 |
字段 | 类属性 |
表记录 | 类实例化对象 |
2.1.4 配置显示ORM转SQL语句
在settings.py添加
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console':{
'level':'DEBUG',
'class':'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'propagate': True,
'level':'DEBUG',
},
}
}
2.2 创建数据库表
在app的models.py中添加
from django.db import models
# Create your models here.
class Student(models.Model):
name = models.CharField(max_length=32, verbose_name="姓名")
age = models.IntegerField(verbose_name="年龄")
classes = models.ForeignKey("Class", verbose_name="班级", on_delete=models.CASCADE)
class Meta():
db_table = "s_student" #自定义表名
def __str__(self):
return self.name
class Class(models.Model):
name = models.CharField(max_length=32, verbose_name="班级")
teacher = models.ManyToManyField("Teacher", verbose_name="教师")
class Meta():
db_table = "s_class"
def __str__(self):
return self.name
class Teacher(models.Model):
name = models.CharField(max_length=32, verbose_name="姓名")
course = models.ForeignKey("Course", verbose_name="课程", on_delete=models.CASCADE) # 级联删除(删除对象时,会连带删除外键关联的对象)
class Meta():
db_table = "s_teacher"
def __str__(self):
return self.name
class Course(models.Model):
name = models.CharField(max_length=32, verbose_name="课程")
class Meta():
db_table = "s_course"
def __str__(self):
return self.name
class Achievement(models.Model):
student = models.ForeignKey("Student", verbose_name="学生", on_delete=models.CASCADE)
course = models.ForeignKey("Course", verbose_name="课程", on_delete=models.CASCADE)
source = models.DecimalField(max_digits=5, decimal_places=2, verbose_name="成绩")
class Meta():
db_table = "s_achievement"
unique_together = ("student", "course") # 联合唯一约束
生成表
python manage.py makemigrations # 生成表结构变化文件
python manage.py migrate # 根据变化文件,创建表
3.ORM添加
打开python终端,导入模块
python manage.py shell
# student_info 为app名,根据自己创建的app自行修改
from student_info import models
3.1 添加数据(单表)
# 1:先实例化对象,再保存
student_obj = models.Teacher(name="connor") # 创建对象
student_obj.save() # 插入数据库
# INSERT INTO "s_teacher" ("name") VALUES ('connor');
# 2:直接创建
models.Teacher.objects.create(name="allen")
# INSERT INTO "s_teacher" ("name") VALUES ('allen');
3.2 添加数据(一对多)
# 1. 传入外键id
models.Teacher.objects.create(name='dell', course_id=2)
# INSERT INTO "s_teacher" ("name", "course_id") VALUES ('dell', 2);
# 1. 传入外键对象
course_obj = models.Course.objects.filter(name='美术').first()
#SELECT "s_course"."id", "s_course"."name" FROM "s_course" WHERE "s_course"."name" = '美术' ORDER BY "s_course"."id" ASC LIMIT 1;
models.Teacher.objects.create(name='bob', course=course_obj)
# INSERT INTO "s_teacher" ("name", "course_id") VALUES ('bob', 2);
# 3. 使用set创建
course_obj.teacher_set.create(name='ven')
# INSERT INTO "s_teacher" ("name", "course_id") VALUES ('ven', 2);
3.3 添加数据(多对多)
使用add函数对第三张表进行绑定(只适用于系统生成的多对多表)
# 1. 传入对象
class_1506 = models.Class.objects.create(name="1506")
# INSERT INTO "s_class" ("name") VALUES ('1506'); args=['1506']
connor = models.Teacher.objects.filter(name='connor').first()
# SELECT "s_teacher"."id", "s_teacher"."name" FROM "s_teacher" WHERE "s_teacher"."name" = 'connor' ORDER BY "s_teacher"."id" ASC LIMIT 1;
allen = models.Teacher.objects.filter(name='allen').first()
# SELECT "s_teacher"."id", "s_teacher"."name" FROM "s_teacher" WHERE "s_teacher"."name" = 'allen' ORDER BY "s_teacher"."id" ASC LIMIT 1;
class_1506.teacher.add(allen, connor)
# SELECT "s_class_teacher"."teacher_id" FROM "s_class_teacher" WHERE ("s_class_teacher"."class_id" = 1 AND "s_class_teacher"."teacher_id" IN (1, 2));
# INSERT INTO "s_class_teacher" ("class_id", "teacher_id") SELECT 1, 1 UNION ALL SELECT 1, 2;
# 2. 传入id
class_1507 = models.Class.objects.create(name="1507")
# INSERT INTO "s_class" ("name") VALUES ('1507');
class_1507.teacher.add(3, 4)
# SELECT "s_class_teacher"."teacher_id" FROM "s_class_teacher" WHERE ("s_class_teacher"."class_id" = 2 AND "s_class_teacher"."teacher_id" IN (3, 4));
#INSERT INTO "s_class_teacher" ("class_id", "teacher_id") SELECT 2, 3 UNION ALL SELECT 2, 4;
3.4 自定义多对多表插入
# 1.创建时传入对象
models.Student.objects.create(name="小名", age='12', classes_id='1')
xiaoming = models.Student.objects.filter(name='小名').first()
co_01 = models.Course.objects.filter(name='数学').first()
models.Achievement.objects.create(source=60, course=co_01, student=xiaoming)
# INSERT INTO "s_achievement" ("student_id", "course_id", "source") VALUES (1, 1, '60.00');
# 2.创建时传入id
models.Course.objects.create(name='物理', teacher_id='4')
models.Achievement.objects.create(source=80, course_id=3, student_id=1)
# INSERT INTO "s_achievement" ("student_id", "course_id", "source") VALUES (1, 3, '80.00');
# 3.使用set创建
xiaoming.achievement_set.create(source=90, course_id="2")
# INSERT INTO "s_achievement" ("student_id", "course_id", "source") VALUES (1, 2, '90.00');
4. ORM查询
查询API
方法 | 说明 |
---|---|
all() | 查询所有结果 |
filter(**kwargs) | 查询所有符合筛选条件的对象,多个值是搜索条件为and,如果想用or或非需要使用Q查询 |
get(**kwargs) | 查询符合筛选条件的对象,但返回值只能有一个,超过一个则报错(get() returned more than one) |
exclude(**kwargs) | 返回 筛选条件不符合的数据(反选) |
order_by(*field) | 对结果进行排序 |
reverse() | 对返回结果反选 |
count() | 计数 |
first() | 返回第一条数据 |
last() | 返回最后一条数据 |
exists() | 判断查询数据是否为空,有数据返回True,空则返回False |
values(*field) | 返回一个QuerySet,列表中每条数据为一个字典 |
values_list(*field) | 返回一个QuerySet,列表中每条数据为一个元组 |
distinct() | 对结果去重 |
4.1 单表查询
# 1. all
# 查询所有数据
models.Student.objects.all()
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" LIMIT 21;
# 2. filter
# 查询所有符合筛选条件的对象
models.Student.objects.filter(name='张三')
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."name" = '张三' LIMIT 21;
# 3. get
# 查询符合筛选条件的对象,但返回值只能有一个,超过一个则报错(get() returned more than one)
models.Student.objects.get(age=12)
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."age" = 12;
# 4. exclude
# 返回 筛选条件不符合的数据(反选)
# 如果有多个参数,参数之间为且的关系
models.Student.objects.exclude(classes_id=2)
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE NOT ("s_student"."classes_id" = 2)
# 5. order_by
# 对结果进行排序
models.Student.objects.order_by("classes_id", "age")
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" ORDER BY "s_student"."classes_id" ASC, "s_student"."age" ASC LIMIT 21;
# 6. reverse order_by 为sql 中为ASC reverse sql为DESC
# 对结果进行排序
models.Student.objects.order_by("pk").reverse()
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" ORDER BY "s_student"."id" DESC LIMIT 21;
# 7. count
# 计数
models.Student.objects.filter(classes_id=2).count()
# SELECT COUNT(*) AS "__count" FROM "s_student" WHERE "s_student"."classes_id" = 2;
# 8. first
# 返回第一条数据
models.Student.objects.first()
#SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" ORDER BY "s_student"."id" ASC LIMIT 1;
# 9. last
# 返回最后一条数据
models.Student.objects.last()
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" ORDER BY "s_student"."id" DESC LIMIT 1;
# 10. exists
# 判断查询数据是否为空,有数据返回True,空则返回False
models.Student.objects.filter(age=12).exists()
# SELECT (1) AS "a" FROM "s_student" WHERE "s_student"."age" = 12 LIMIT 1;
# 11. values,QuerySet中的元素为字典
# 返回一个QuerySet,列表中每条数据为一个字典
models.Student.objects.values()
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" LIMIT 21;
# 12. values_list, QuerySet中的元素为元组
# 返回一个QuerySet,列表中每条数据为一个元组
models.Student.objects.values_list()
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" LIMIT 21;
# 13. distinct
# 对结果去重
models.Student.objects.filter(classes_id=1).values("classes_id").distinct()
# SELECT DISTINCT "s_student"."classes_id" FROM "s_student" WHERE "s_student"."classes_id" = 1 LIMIT 21;
4.2 一对多查询
# 正向查找,通过字段查找
# 查找connor老师教的课程
connor = models.Teacher.objects.filter(name='connor').first()
connor.course.name
# SELECT "s_course"."id", "s_course"."name" FROM "s_course" WHERE "s_course"."id" = 1;
# '数学'
# 反向查找,通过表名查找
# 查找教美术的老师
co_obj = models.Course.objects.filter(name='美术').first()
co_obj.teacher_set.all()
# <QuerySet [<Teacher: allen>, <Teacher: dell>, <Teacher: bob>, <Teacher: ven>]>
# 双下划线查询
# 查找connor老师教的课程(与正向查找做对比)
models.Course.objects.filter(teacher__name="connor").values('name')
#查找教美术的老师(与反向查找做对比)
models.Teacher.objects.filter(course__name='美术')
# <QuerySet [{'name': 'allen'}, {'name': 'dell'}, {'name': 'bob'}, {'name': 'ven'}]>
4.2 多对多查询
# 正向查找,通过模型表中的字段查询
# 查找 1506班所有老师
cl_1506 = models.Class.objects.filter(name='1506').first()
cl_1506.teacher.all()
# 反向查找,通过表明查询
# 查找connor老师教的班级
connor.class_set.all()
# 双下划线方法查询
# 查找1506班所有老师
models.Class.objects.filter(name='1506').values('teacher__name')
# SELECT "s_teacher"."name" FROM "s_class" LEFT OUTER JOIN "s_class_teacher" ON ("s_class"."id" = "s_class_teacher"."class_id") LEFT OUTER JOIN "s_teacher" ON ("s_class_teacher"."teacher_id" = "s_teacher"."id") WHERE "s_class"."name" = '1506' LIMIT 21;
# 查找connor老师教的班级
models.Teacher.objects.filter(name='connor').values('class__name')
# SELECT "s_class"."name" FROM "s_teacher" LEFT OUTER JOIN "s_class_teacher" ON ("s_teacher"."id" = "s_class_teacher"."teacher_id") LEFT OUTER JOIN "s_class" ON ("s_class_teacher"."class_id" = "s_class"."id") WHERE "s_teacher"."name" = 'connor' LIMIT 21;
4.3 自定义多对多查询
自定义多对多实际上是双向一对多,所以只有反向查询
# 1. 查询所有学生的数学成绩
co1_obj = models.Course.objects.filter(name='数学').first()
co1_obj.achievement_set.all().values("source")
# 2 查询二哈同学所有学科的成绩
erha = models.Student.objects.filter(name='二哈').first()
erha.achievement_set.all().values('source')
# 3 双下划线查询
# 查询所有学生的数学成绩
models.Course.objects.filter(name='数学').values('achievement__student__name', 'achievement__source')
# SELECT "s_student"."name", "s_achievement"."source" FROM "s_course" LEFT OUTER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") LEFT OUTER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") WHERE "s_course"."name" = '数学' LIMIT 21;
#
# 查询二哈同学所有学科的成绩
models.Student.objects.filter(name='二哈').values('achievement__course__name','achievement__source')
# SELECT "s_course"."name", "s_achievement"."source" FROM "s_student" LEFT OUTER JOIN "s_achievement" ON ("s_student"."id" = "s_achievement"."student_id") LEFT OUTER JOIN "s_course" ON ("s_achievement"."course_id" = "s_course"."id") WHERE "s_student"."name" = '二哈' LIMIT 21;
4.5 聚合查询
聚合查询是使用aggreate(*args,**kwargs)方法,对QuerySet进行计算
使用前需导入
from django.db.models import Sum, Avg, Max, Min,Count
4.5.1 Count(求总个数)
单表
# 求学生总数
# pk 即主键ID
models.Student.objects.aggregate(s_num=Count('pk'))
# SELECT COUNT("s_student"."id") AS "s_num" FROM "s_student";
一对多
# 求1506班学生总和
# 1. LEFT JOIN
models.Class.objects.filter(name='1506').aggregate(Count('student__pk'))
# SELECT COUNT("s_student"."id") AS "student__pk__count" FROM "s_class" LEFT OUTER JOIN "s_student" ON ("s_class"."id" = "s_student"."classes_id") WHERE "s_class"."name" = '1506';
# 2. INNER JOIN
models.Student.objects.filter(classes__name='1506').aggregate(Count('pk'))
# SELECT COUNT("s_student"."id") AS "pk__count" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE "s_class"."name" = '1506';
多对多(与一对多类似)
ORM表达式不会涉及第三张表
# 查询1506班的老师总数
# 1. LEFT JOIN
models.Class.objects.filter(name='1506').aggregate(t_count = Count('teacher__pk'))
# SELECT COUNT("s_class_teacher"."teacher_id") AS "t_count" FROM "s_class" LEFT OUTER JOIN "s_class_teacher" ON ("s_class"."id" = "s_class_teacher"."class_id") WHERE "s_class"."name" = '1506';
# 2. INNER JOIN
models.Teacher.objects.filter(class__name='1506').aggregate(t_count = Count('pk'))
# SELECT COUNT("s_teacher"."id") AS "t_count" FROM "s_teacher" INNER JOIN "s_class_teacher" ON ("s_teacher"."id" = "s_class_teacher"."teacher_id") INNER JOIN "s_class" ON ("s_class_teacher"."class_id" = "s_class"."id") WHERE "s_class"."name" = '1506';
自定义多对多
ORM表达式会涉及第三张表
# 查询二哈同学考试学科总数
# 当Count中字段为id时可以省略
models.Student.objects.filter(name='二哈').aggregate(co_count = Count('achievement'))
# SELECT COUNT("s_achievement"."id") AS "co_count" FROM "s_student" LEFT OUTER JOIN "s_achievement" ON ("s_student"."id" = "s_achievement"."student_id") WHERE "s_student"."name" = '二哈';
4.5.2 SUM(求和)
# 查询二哈同学的总成绩
models.Student.objects.filter(name='二哈').aggregate(co_sum = Sum('achievement__source'))
# SELECT CAST(SUM("s_achievement"."source") AS NUMERIC) AS "co_sum" FROM "s_student" LEFT OUTER JOIN "s_achievement" ON ("s_student"."id" = "s_achievement"."student_id") WHERE "s_student"."name" = '二哈';
4.5.3 AVG(平均值)
# 求1506班物理平均成绩
models.Course.objects.filter(name='物理',achievement__student__classes__name='1506').aggregate(Avg('achievement__source'))
# SELECT CAST(AVG("s_achievement"."source") AS NUMERIC) AS "achievement__source__avg" FROM "s_course" INNER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_class"."name" = '1506' AND "s_course"."name" = '物理');
4.5.4 MAX(最大值)
# 求1506班物理成绩最高是多少
models.Course.objects.filter(name='物理',achievement__student__classes__name='1506').aggregate(Max('achievement__source'))
# SELECT CAST(MAX("s_achievement"."source") AS NUMERIC) AS "achievement__source__max" FROM "s_course" INNER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_class"."name" = '1506' AND "s_course"."name" = '物理');
4.5.5 MIN(最小值)
# 求1506班物理成绩最低是多少
models.Course.objects.filter(name='物理',achievement__student__classes__name='1506').aggregate(Min('achievement__source'))
4.6 分组查询
models.表.objects.values(‘分组字段’).annotate(统计字段)
# 查看每门学科的最低分
models.Course.objects.values('name').annotate(Min('achievement__source'))
# SELECT "s_course"."name", CAST(MIN("s_achievement"."source") AS NUMERIC) AS "achievement__source__min" FROM "s_course" LEFT OUTER JOIN "s_achievement" ON ("s_course"."id" = "s_achievement"."course_id") GROUP BY "s_course"."name" LIMIT 21;
4.7 双下方法
方法 | 说明 |
---|---|
field__lt | 小于 |
field__ gt | 大于 |
field__ lte | 小于等于 |
field__ gte | 大于等于 |
field__ in | [1,2,4]在列表中 |
field__ range | (2,6)在范围内 |
field__contains | 获取字段中包含指定字符串的数据 |
field__icontains | 获取字段中包含指定字符串的数据,大小写不敏感 |
field__startswith | 获取字段中以指定字符串开头的数据 |
field__istartswith | 获取字段中以指定字符串开头的数据,大小写不敏感 |
field__endswith | 获取字段中以指定字符串结尾的数据 |
field__iendswith | 获取字段中以指定字符串结尾的数据,大小写不敏感 |
4.7.1 数值型字段
# 1. 小于
# 查询小于15岁的学生
models.Student.objects.filter(age__lt=15)
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."age" < 15 LIMIT 21;
# 2. 大于
# 查询大于15岁的学生
models.Student.objects.filter(age__gt=15)
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."age" > 15 LIMIT 21;
# 3. 小于等于
# 查询小于等于15岁的学生
models.Student.objects.filter(age__lte=15)
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."age" <= 15 LIMIT 21;
# 4. 大于等于
# 查询大于等于15岁的学生
models.Student.objects.filter(age__gte=15)
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."age" >= 15 LIMIT 21;
# 5. 范围
# 查询大于15 小于等于18的学生
models.Student.objects.filter(age__gte=15, age__lt=18)
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE ("s_student"."age" >= 15 AND "s_student"."age" < 18) LIMIT 21;
# in
models.Student.objects.filter(age__in=[12,15])
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."age" IN (12, 15) LIMIT 21;
# range 两端都取
models.Student.objects.filter(age__range=(15, 20))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" WHERE "s_student"."age" BETWEEN 15 AND 20 LIMIT 21;
4.7.2 字符串字段
# 包含字段
models.Teacher.objects.filter(name__contains='c')
# SELECT "s_teacher"."id", "s_teacher"."name", "s_teacher"."course_id" FROM "s_teacher" WHERE "s_teacher"."name" LIKE '%c%' ESCAPE '\' LIMIT 21;
# 包含字段,大小写不敏感
models.Teacher.objects.filter(name__icontains='c')
# 以某字符串开头
models.Teacher.objects.filter(name__startswith='con')
# 以某字符串开头,大小写不敏感
models.Teacher.objects.filter(name__istartswith='con')
# 以某字段结束
models.Teacher.objects.filter(name__endswith='nor')
# 以某字段结束,大小写不敏感
models.Teacher.objects.filter(name__iendswith='nor')
4.8 F查询
对查询结果进行引用,用于两个不同字段的对比
导入模块
from django.db.models import F
# F()对象之间,F()与常数之间可以进行加减乘除和取模操作
# 查询分数比年龄*2还低的学生
models.Achievement.objects.filter(student__age__gt=F('source')*2).values('student__name', 'student__age', 'source')
# SELECT "s_student"."name", "s_student"."age", "s_achievement"."source" FROM "s_achievement" INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") WHERE "s_student"."age" > ("s_achievement"."source") LIMIT 21;
4.9 Q查询
filter()方法默认是and,并没有提供or和not方法,无法使用无法使用复杂的查询方法,可以使用Q方法封装字段进行复杂的查询,可以组合使用 &(and), |(or),~(not)操作符对Q对象进行操作
导入模块
from django.db import Q
4.9.1 AND
# 查询年龄15岁,并且在1506班上课的同学
models.Student.objects.filter(Q(age=15) & Q(classes__name='1508'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_student"."age" = 15 AND "s_class"."name" = '1508') LIMIT 21;
4.9.2 OR
# 查询年龄12岁或在1508班上课的同学
models.Student.objects.filter(Q(age=12) | Q(classes__name='1508'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_student"."age" = 12 OR "s_class"."name" = '1508') LIMIT 21;
4.9.3 NOT
# 查找1506班以外的同学
models.Student.objects.filter(~Q(classes__name='1506'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE NOT ("s_class"."name" = '1506') LIMIT 21;
4.9.4 混合使用
优先级 NOT >AND>OR
# 查找年龄大于25岁或者年龄等于12岁并且不在1506班的学生
models.Student.objects.filter(Q(age__gt=25) | Q(age=12) & ~Q(classes__name='1506'))
# SELECT "s_student"."id", "s_student"."name", "s_student"."age", "s_student"."classes_id" FROM "s_student" INNER JOIN "s_class" ON ("s_student"."classes_id" = "s_class"."id") WHERE ("s_student"."age" > 25 OR ("s_student"."age" = 12 AND NOT ("s_class"."name" = '1506'))) LIMIT 21;
5 更新
5.1 模型类修改(update)
# 修改二哈同学的物理成绩为50分
models.Achievement.objects.filter(student__name='二哈', course__name='物理').update(source=50)
# UPDATE "s_achievement" SET "source" = '50.00' WHERE "s_achievement"."id" IN (SELECT U0."id" FROM "s_achievement" U0 INNER JOIN "s_course" U1 ON (U0."course_id" = U1."id") INNER JOIN "s_student" U2 ON (U0."student_id" = U2."id") WHERE (U1."name" = '物理' AND U2."name" = '二哈'));
5.2 修改模型类对象,然后保存(save)
# 修改二哈同学的物理成绩为70分
erha_obj = models.Achievement.objects.filter(student__name='二哈', course__name='物理').first()
# SELECT "s_achievement"."id", "s_achievement"."student_id", "s_achievement"."course_id", "s_achievement"."source" FROM "s_achievement" INNER JOIN "s_course" ON ("s_achievement"."course_id" = "s_course"."id") INNER JOIN "s_student" ON ("s_achievement"."student_id" = "s_student"."id") WHERE ("s_course"."name" = '物理' AND "s_student"."name" = '二哈') ORDER BY "s_achievement"."id" ASC LIMIT 1;
erha_obj.source = '70
erha_obj.save()
# UPDATE "s_achievement" SET "student_id" = 2, "course_id" = 3, "source" = '70.00' WHERE "s_achievement"."id" = 6; args=(2, 3, '70.00', 6)
6 删除
Django 删除对象时,会模仿 SQL 约束 ON DELETE CASCADE 的行为,也就是说删除时会删除关联外键对应的对象,如果不想要删除关联对象,需要在模型类对象中修改on_delete=models.SET_NULL
# 删除tom老师
models.Teacher.objects.filter(name='tom').delete()
# SELECT "s_teacher"."id", "s_teacher"."name", "s_teacher"."course_id" FROM "s_teacher" WHERE "s_teacher"."name" = 'tom'; args=('tom',)
# BEGIN; args=None
# DELETE FROM "s_class_teacher" WHERE "s_class_teacher"."teacher_id" IN (3); args=(3,)
# DELETE FROM "s_teacher" WHERE "s_teacher"."id" IN (3); args=(3,)
# 返回值:(3, {'student_info.Class_teacher': 2, 'student_info.Teacher': 1})