Django-模型层(2)
多表操作
口决：正向操作按字段，反向操作按表名小写
创建模型
- 作者模型：姓名和年龄
- 作者详情模型：把作者详性放到详情表，包含生日，手机号，家庭住址等。作者详情模型与作者模型为一对一关系(one-to-one)
- 出版商模型：出版商有名称，所在城市及email
- 书藉模型：书藉有书名和出版日期，书藉模型与作者模型为多对多关系(many-to-many)，出版商模型与书藉模型为一对多关系(one-to-many)
模型建立如下：
from django.db import models # Create your models here. class Book(models.Model): title = models.CharField(max_length=32) pub_date = models.DateField() price = models.DecimalField(max_digits=5, decimal_places=2) publish = models.ForeignKey(to="Publish", to_field="id", on_delete=models.CASCADE, null=True) authors = models.ManyToManyField(to="Author", db_table="book2authors") def __str__(self): return self.title class Meta: db_table = "book" class Publish(models.Model): name = models.CharField(max_length=32) city = models.CharField(max_length=32) email = models.CharField(max_length=32) def __str__(self): return self.name class Meta: db_table = "publish" class Author(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() ad = models.OneToOneField("AuthorDetail", null=True, on_delete=models.CASCADE) def __str__(self): return self.name class Meta: db_table = "author" class AuthorDetail(models.Model): birthday = models.DateField() telephone = models.BigIntegerField() addr = models.CharField(max_length=64) def __str__(self): return str(self.telephone) class Meta: db_table = "authorDetail"
注意事项：
- 对于外键字段，Django会在字段名上添加"_id"来创建数据库的列名
- 外键字段ForeignKey有一个null=True的设置(它允许外键接受空值NULL) , 你可以赋给它空值None
增
一对多
关联字段在创建表时字段名为字段_id
先添加一的表：
publish = models.Publish.objects.create(name="苹果出版社", city="深圳",email="1234@163.com")
再添加多的表：
方式一： book = models.Book.objects.create(title="Python"， pub_date="2012-12-12", price=122, publish_id=1) 方式二： book = models.Book.objects.create(title="Python", pub_date="2012-12-12", price=122, publish=publish) #book.publish为book书藉的出版社对象
一对一
与一对多一样，先添加一的表，再添加带关联字段的表
关联字段在创建表时字段名为字段_id
多对多
- 绑定多对多的关系，无非是在关系表创建记录
- 第三张表中字段名为表名_id
# 正向(从具有关联属性的表添加) # Linux这本书绑定两个作者：Tom, Linda # 方式一： linux = models.Book.objects.filter(title="Linux").first() tom = models.Author.objects.filter(name="Tom").first() linda = models.Author.objects.filter(name="Linda").first() linux.authors.add(tom, linda) # 方式二： linux = models.Book.objects.filter(title="Linux").first() linux.authors.add(1, 2)/linux.authors.add(*[1, 2]) # 反向(从不具有关联属性的表添加) # 给Ethan绑定一本书藉：PhP ethan = models.Author.objects.filter(name="Ethan").first() php = models.Book.objects.filter(title="PhP").first() ethan.book_set.add(php)
注：添加表记录可以通过插件(如navicat)完成
查
基于对象的跨表查询
实质为子查询，即以上一次的查询结果作为下一次的查询条件
一对多
正向查询:按字段book.publish ----------------------------> Book对象 <---------------------------- Publish 对象 反向查询:按表名小写_set.all()
例： 正向:查询Linux这本书藉的出版社的地址 book = models.Book.objects.filter(title="Linux").first() print(book.publish.city) 反向:查询苹果出版社出版的所有书藉 publish = models.Publish.objects.filter(name="苹果出版社").first() print(publish.book_set.all())
多对多
正向查询:按字段book.authors.all() ---------------------------------> Book对象 <--------------------------------- Author 对象 反向查询:按表名小写_set.all()
例: 正向:查询Linux书藉的所有作者 linux = models.Book.objects.filter(title="Linux").first() print(linux.authors.all()) 反向:查询tom出版过的所有书藉 tom = models.Author.objects.filter(name="Tom").first() print(tom.book_set.all())
一对一
正向查询:按字段book.ad ---------------------------------> Author 对象 <--------------------------------- AuthorDetail 对象 反向查询:按表名小写
例: 正向:查询Tom的地址 tom = models.Author.objects.filter(name="Tom").first() print(tom.ad.addr) 反向:查询地址为南京的作者的名字 ad = models.AuthorDetail.objects.filter(addr="南京").first() print(ad.author.name)
基于双下划线的跨表查询
Django还提供了一种直观而高效的方式在查询(lookups)中表示关联关系，它能自动确认SQL JOIN联系。要做跨关系查询，就使用两个下划线来链接模型(model)间关联字段的名称。直到最终链接到你想要的model为止。
实质为join查询。
"""正向查询按字段,反向查询按表名小写"""
# <1> 查询Linux这本书藉的出版社的地址(一对多) # 正向查询 addr = models.Book.objects.filter(title="Linux").values("publish__city") # 反向查询 addr = models.Publish.objects.filter(book__title="Linux").values("city") # <2> 查询Linux书藉的所有作者名字(多对多) # 正向查询
print(models.Book.objects.filter(title="Linux").values("authors__name")) # 反向查询
print(models.Author.objects.filter(book__title="Linux").values("name"))
# <3> 查询Ethan的手机号(一对一) # 正向查询 ret = models.Author.objects.filter(name="Ethan").values("ad__telephone") # 反向查询 ret = models.AuthorDetail.objects.filter(author__name="Ethan").values("telephone")
连续跨表
# <1> 查询人民出版社出版过的所有书藉的名字以及作者的姓名 # 正向查询 ret = models.Book.objects.filter(publish__name="教育出版社").values("title", "authors__name") # 反向查询 ret = models.Publish.objects.filter(name="人民出版社").values_list("book__title", "book__authors__name")
# <2> 手机号以13开头的作者出版过的所有书籍名称以及出版社名称 # 方式一： ret = models.Book.objects.filter(authors__ad__telephone__startswith="13").values("title", "publish__name")) # 方式二： ret = models.Author.objects.filter(ad__telephone__startswith="13").values("book__title", "book__publish__name")
related_name
反向查询时，如果定义了related_name，则用related_name替换表名，如：
# models.py class Book(models.Model): publish = models.ForeignKey(to="Author", to_field="id", on_delete=models.CASCADE, null=True, related_name="booklist")
# 查询人民出版社出版过的所有书籍的名字与价格(一对多) # 反向查询 不再按表名:book,而是related_name:bookList ret =models.Publish.objects.filter(name="人民出版社").values_list("bookList__title","bookList__price")
聚合查询与分组查询
聚合查询
方法：aggregate(*args, **kwargs)
导入聚合函数：
from django.db.models import Avg,Max,Min,Sum,Count
调用者：QuerySet对象
返回值：包含一些键值对的字典。键的名称如果没有指定，则按照字段和聚合函数的名称自动生成，也可自行指定
# 计算所有图书的平均价格 from django.db.models import Avg # 自动生成键名称 models.objects.aggregate(Avg("price")) # {'price_avg': 34.55} # 自行指定键名称 models.objects.aggregate(average_price = Avg("price")) # {'average_price': 34.55}
可以向aggregate()添加多个参数，即生成多个聚合
from django.db.models import Avg, Max, Min models.Book.objects.aggregate(Avg('price'), Max('price'), Min('price')) # {'price__avg': 34.55, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}
分组查询
本质：将关联表join成一张表，再安单表的思路进行分组查询
KEY: 1. 选连表(基于双下划线查询就是连表)，再Group by，最后显示字段 2. values().annotate()，此时values()就是group by
方法：annotate()
调用者：QuerySet对象
返回值：QuerySet对象
###################################－－单表分组查询－－####################################################### 查询每一个部门名称以及对应的员工数 emp: id name age salary dep 1 alex 12 2000 销售部 2 egon 22 3000 人事部 3 wen 22 5000 人事部 sql语句: select dep,Count(*) from emp group by dep; ORM: emp.objects.values("dep").annotate(c=Count("id") ###################################－－多表分组查询－－########################### 多表分组查询： 查询每一个部门名称以及对应的员工数 emp: id name age salary dep_id 1 alex 12 2000 1 2 egon 22 3000 2 3 wen 22 5000 2 dep id name 1 销售部 2 人事部 emp－dep: id name age salary dep_id id name 1 alex 12 2000 1 1 销售部 2 egon 22 3000 2 2 人事部 3 wen 22 5000 2 2 人事部 class Emp(models.Model): name=models.CharField(max_length=32) age=models.IntegerField() salary=models.DecimalField(max_digits=8,decimal_places=2) dep=models.CharField(max_length=32) province=models.CharField(max_length=32) sql语句: select dep.name,Count(*) from emp left join dep on emp.dep_id=dep.id group by dep.id ORM: dep.objetcs.values("id").annotate(c=Count("emp")).values("name","c")
# objects.values().annotate()与objects.all().annotate()区别 dep.objects.values("id").annotate(c=Count("price")) # 得到的是QuerySet集合，集合里元素为id和c键值对的字典 dep.objects.all().annotate(c=Count("price")) # 得到的是QuerySet集合，集合里元素为dep对象，dep对象在原有基础上多了个c字段
查询练习
# <1> 统计每一个出版社的最便宜的书 # <2> 统计每一本书的作者个数 # <3> 统计每一本以Py开头的书籍的作者个数 # <4> 统计不止一个作者的图书 # <5> 根据一本图书作者数量的多少对查询集QuerySet进行排序 # <6> 查询各个作者出的书的总价格
# <1> 统计每一个出版社的最便宜的书 ret = models.Publish.objects.annotate(MinPrice=Min("book__price")).values_list("name", "MinPrice") # <2> 统计每一本书的作者个数 ret = models.Book.objects.annotate(authorsNum=Count("authors__name")) # <3> 统计每一本以Py开头的书籍的作者个数 ret = models.Book.objects.filter(title__startswith="Py").annotate(num_authors=Count("authors")) # <4> 统计不止一个作者的图书 ret = models.Book.objects.annotate(num_authors=Count("authors")).filter(num_authors__gt=1) # <5> 根据一本图书作者数量的多少对查询集QuerySet进行排序 ret = models.Book.objects.annotate(num_authors=Count("authors")).order_by("num_authors") # <6> 查询各个作者出的书的总价格 ret = models.Author.objects.annotate(SumPrice=Sum("book__price")).values_list("name", "SumPrice")
F查询与Q查询
F查询
在上面的所有例子中，构造的过滤器都只是将字段值与某个常量做比较
Django提供F()的实例可以在查询中引用字段，来比较同一个model实例中两个不同的字段的值
F的导入：
from django.db.models import F
from django.db.models import F # 查询评论数大于收藏数的书籍 ret = models.Book.objects.filter(commentNum__lt=F("keepNum"))
Django支持F()对象之间以及F()对象和常数之间的加减乘除和取模的操作
# 查询评论数大于收藏数2倍的书籍 ret = models.Book.objects.filter(commentNum__lt=F("keepNum")*2)
修改操作也可以使用F函数
# 将每一本书的价格提高30元 ret = models.Book.objects.all().update(price=F("price") + 30)
Q查询
filter()等方法中的关键字参数查询都是一起进行"and"的。如果需要执行更复杂的查询(例如OR 语句)，则使用Q对象
Q查询支持查询条件的and，or， not操作，分别对应操作符&，|，~
Q的引入：
from django.db.models import Q
Q对象可以使用&和|操作符组合起来。当一个操作符在两个Q对象上使用时，它产生一个新的Q对象
# 查询价格大于300或者名称以"p"开头的书籍 ret = models.Book.objects.filter(Q(price__gt=300)|Q(title__startswith="p"))
可以组合&和|操作符以及使用括号进行分组来编写任意复杂的Q对象。同时，Q对象可以使用~操作符取反，这允许组合正常的查询和取反(NOT)查询:
# 查询由Ethan编写的且不是在2017年出版的书籍名称 ret = models.Book.objects.filter(Q(authors__name="Ethan") & ~Q(pub_date__year=2017)).values_list("title")
查询函数可以混合使用Q对象和关键字参数。所有提供给查询函数的参数(关键字参数或Q对象)都将"AND"在一起。但是，如果出现Q对象,它必须位于所有关键字参数的前面
# 查询由2016或2017年出版的名称中含有"python"(不区分大小写)的书籍 ret = models.Book.objects.filter(Q(pub_date__year=2016) | Q(pub_date__year=2017), title__icontains="python")
当有多个操作符时，属于同一关系外层还需套一层Q
# 查询价格大于300或者不是2019年1月出版的书籍 ret = models.Book.objects.filter(Q(price__gt=300) | ~Q(Q(pub_date__year=2019) & Q(pub_date__month=1)))
删
# create, add, remove, clear, set适用于如下管理器： """ 1. 一对多：如Publish_obj.book_set 2. 多对多：如Book_obj.authors, Author_obj.book_set """
remove
# 删除Linux这本书的一个作者Tom linux = models.Book.objects.filter(title="Linux").first() tom = models.Author.objects.filter(name="Tom").first() linux.authors.remove(tom)/tom.book_set.remove(linux) # 删除第三张表Tom与Linux记录
clear
# 删除Linux这本书的所有作者 linux = models.Book.objects.filter(title="Linux").first() linux.authors.clear()
set
相当于clear + add。set括号内参数为list
# 删除Linux这本书的所有作者，并添加Ethan为作者 # 方法一： linux = models.Book.objects.filter(title="Linux").first() linux.authors.set([3,]) # 方法二： linux = models.Book.objects.filter(title="Linux").first() ethan = models.Author.objects.filter(name="Ethan").first() linux.authors.set([ethan,])
练习题
# 1、 自行创建测试数据； # 2、 查询学生总人数； # 3、 查询“生物”课程和“物理”课程成绩都及格的学生id和姓名； # 4、 查询每个年级的班级数，取出班级数最多的前三个年级； # 5、 查询平均成绩最高的学生的id和姓名以及平均成绩； # 6、 查询每个年级的学生人数； # 7、 查询每位学生的学号，姓名, 平均成绩； # 8、 查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名及分数； # 9、 查询姓“李”的老师的个数和所带班级数； # 10、查询班级数小于5的年级id和年级名； # 11、查询教过课程超过2门的老师的id和姓名； # 12、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名； # 13、查询所带班级数最多的老师id和姓名； # 14、查询有课程成绩小于60分的同学的学号、姓名； # 15、查询男生、女生的人数，按倒序排列； # 16、查询各个课程及相应的选修人数； # 17、查询同时选修了物理课和生物课的学生id和姓名； # 18、检索“3”课程分数小于60，按分数降序排列的同学学号； # 19、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； # 20、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
# 1、 自行创建测试数据； # 2、 查询学生总人数； ret = models.Student.objects.aggregate(studentNum=Count("*")) # 3、 查询“生物”课程和“物理”课程成绩都及格的学生id和姓名； ret = models.Score.objects.filter(Q(course__cname="物理")|Q(course__cname="生物"), score__gt=59).values("student").annotate(c=Count("*")).filter(c__gt=1).values("student__sid", "student__sname") # 4、 查询每个年级的班级数，取出班级数最多的前三个年级； ret = models.Cls.objects.values("grade_id").annotate(c=Count("cid")).order_by("-c").values("grade__gname")[:3] # 5、 查询平均成绩最高的学生的id和姓名以及平均成绩； ret = models.Score.objects.values("student_id").annotate(avg=Avg("score")).order_by("-avg").values("student__sid", "student__sname", "avg").first() # 6、 查询每个年级的学生人数； ret = models.Student.objects.values("cls__grade").annotate(c=Count("cls__grade")).values("cls__grade__gname", "c") # 7、 查询每位学生的学号，姓名, 平均成绩； ret = models.Score.objects.values("student_id").annotate(avg=Avg("score")).values("student__sid", "student__sname", "avg") # 8、 查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名及分数； ret = models.Score.objects.filter(student_id=2).order_by("-score").values("student__sname", "course__cname", "score").first() # 9、 查询姓“李”的老师的个数和所带班级数； ret = models.Teacher.objects.filter(tname__startswith="李").annotate(c=Count("tid")).values("c", "classes") # 10、查询班级数小于5的年级id和年级名； ret = models.Class_Grade.objects.values("gid").annotate(c=Count("cls__grade_id")).filter(c__lt=5).values("gid", "gname") # 11、查询教过课程超过2门的老师的id和姓名； ret = models.Course.objects.values("teacher_id").annotate(c=Count("*")).filter(c__gt=1).values("teacher__tid", "teacher__tname") # 12、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名； ret = models.Score.objects.values("student_id").annotate(c=Count("*")).filter().values("student__sid", "student__sname") # 13、查询所带班级数最多的老师id和姓名；***** ret = models.Teacher.objects.annotate(c=Count("classes__cid")).order_by("-c").values("tid", "classes__teacher__tname")[0] # 14、查询有课程成绩小于60分的同学的学号、姓名； ret = models.Score.objects.values("student").annotate(min=Min("score")).filter(score__lt=60).values("student__sid", "student__sname") # 15、查询男生、女生的人数，按倒序排列； ret = models.Student.objects.values("gender").annotate(c=Count("sid")).order_by("-c") # 16、查询各个课程及相应的选修人数； ret = models.Score.objects.values("course").annotate(c=Count("*")).values("course__cname","c") # 17、查询同时选修了物理课和生物课的学生id和姓名； ret = models.Score.objects.filter(Q(course__cname="物理")|Q(course__cname="生物")).values("student").annotate(c=Count("*")).filter(c__gt=1).values("student__sid", "student__sname") # 18、检索“3”课程分数小于60，按分数降序排列的同学学号； ret = models.Score.objects.filter(score__lt=60, course_id="3").order_by("-score").values("student__sid") # 19、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； ret = models.Score.objects.values("course").annotate(c=Avg("score")).order_by("c", "-course__cid").values("course__cname", "c") # 20、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； ret = models.Score.objects.values("course").annotate(max=Max("score"), min=Min("score")).values("course__cid", "max", "min")
表关系：
