django的模型层(二)
django的模型层(二)
一 创建模型
from django.db import models # Create your models here. class Author(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() # 一对一 authordetail = models.OneToOneField(to="AuthorDetail", to_field="nid", on_delete=models.CASCADE) def __str__(self): return self.name # 作者详情表 class AuthorDetail(models.Model): nid = models.AutoField(primary_key=True) birthday = models.DateField() telephone = models.BigIntegerField() addr = models.CharField(max_length=64) # 出版社表 class Publish(models.Model): nid = models.AutoField(primary_key=True) name = models.CharField(max_length=32) city = models.CharField(max_length=32) email = models.EmailField() def __str__(self): return self.name class Book(models.Model): nid = models.AutoField(primary_key=True) title = models.CharField(max_length=32) publishDate = models.DateField() price = models.DecimalField(max_digits=5, decimal_places=2) # 一对多关系 publish = models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE) ''' publish_id INT , FOREIGN KEY (publish_id) REFERENCES publish(id) ''' # 多对多 authors = models.ManyToManyField(to="Author") ''' CREATE TABLE book_authors( id INT PRIMARY KEY auto_increment , book_id INT , author_id INT , FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES author(id) ) ''' # class Book2Author(models.Model): # # nid = models.AutoField(primary_key=True) # book=models.ForeignKey(to="Book") # author=models.ForeignKey(to="Author") def __str__(self): return self.title
二 添加表记录
1 一对多
# 方式 1 book_obj = Book.objects.create(title="水浒传",publishDate="2018-01-01",price="100",publish_id=1) print(book_obj.title) # 水浒传 # 方式2 pub_obj = Publish.objects.filter(nid=1).first() # print(pub_obj) book_obj = Book.objects.create(title="三国演义1", price=100, publishDate="2012-12-12", publish=pub_obj) print(book_obj.title) print(book_obj.price) print(book_obj.publishDate) print(book_obj.publish) #与这本书籍关联的出版社对象 print(book_obj.publish.name) print(book_obj.publish.email) print(book_obj.publish_id) # 查询水浒传的出版社对应的邮箱 book_obj = Book.objects.filter(title="水浒传").first() print(book_obj.publish.email)
2 多对多
book_obj=Book.objects.create(title="gogo",price=100,publishDate="2012-12-12",publish_id=1) egon=Author.objects.get(name="egon") alex=Author.objects.get(name="alex") # #绑定多对多关系的API book_obj.authors.add(egon,alex) # book_obj.authors.add(1,2,3) book_obj.authors.add(*[1,2,3]) # # # # #解除多对多关系 # book=Book.objects.filter(nid=4).first() book.authors.remove(2) #book.authors.remove(*[1,2]) book.authors.clear()
三 基于对象的跨表查询
基于对象的跨表查询(子查询)
1 一对多
# 一对多查询的正向查询： 查询水浒传这本书的出版社名字 book_obj = Book.objects.filter(title="水浒传").first() print(book_obj.publish.name) # 对应sql: #select publish_id from Book where title="水浒传" #select name from Publish where id=1 # 一对多 反向查询 查询人民出版社出版过的书籍名称 pub_obj = Publish.objects.filter(name="人民出版社").first() book_list = pub_obj.book_set.all() print(book_list)
正向查询 A--------->B 反向查询 B<---------A =============================== 一对多查询 正向查询： 按字段 反向查询： 表名小写_set.all() book_obj.publish Book(关联属性：publish) ---------------------> Publish <--------------------- publish_obj.book_set.all()
2 多对多
#多对多正向查询： 查询水浒传这本书作者名字 book_obj = Book.objects.filter(title="水浒传").last() author_list = book_obj.authors.all() for author in author_list: print(author.name) # 多对多反向查询 查询alex出版过的书籍 alex_obj = Author.objects.filter(name="alex").last() book_list = alex_obj.book_set.all() # print(book_list) for book in book_list: print(book.title)
正向查询 A--------->B 反向查询 B<---------A =============================== 多对多查询： 正向查询： 按字段 反向查询： 表名小写_set.all() book_obj.anthors.all() Book(关联属性：publish)对象 ---------------------> Author <--------------------- author_obj.book_set.all() #quertset
3 一对一
# 一对一查询的正向查询 : 查询alex的手机号 alex=Author.objects.filter(name="alex").first() print(alex.authordetail.telephone) # 一对一查询的反向查询 : 查询手机号为110的作者的名字和年龄 ad=AuthorDetail.objects.filter(telephone="123").first() print(ad.author.name) # print(ad.author.age)
一对一查询： 正向查询： 按字段 反向查询： 表名小写 author.authordetail Author(关联属性：publish)对象 ---------------------> AuthorDetail <--------------------- authordetail_obj.author
四 基于双下划线的跨表查询
SQL join
1 一对多
正向查询： 按字段
反向查询： 表名小写
''' 正向查询按字段,反向查询按表名小写用来告诉ORM引擎join哪张表 ''' ''' sql 语句 SELECT app01_publish.name from app01_book INNER JOIN app01_publish on app01_book.publish_id = app01_publish.nid where app01_book.title="水浒传"; '''
查询水浒传这本书的出版社的名字
# 方式一 book_obj = Book.objects.filter(title="水浒传").values("publish__name") print(book_obj) # <QuerySet [{'publish__name': '人民出版社'}]> # 方式二 ret = Publish.objects.filter(book__title="水浒传").values("name") print(ret) # <QuerySet [{'name': '人民出版社'}]>
2 多对多
SELECT app01_author.name from app01_book INNER JOIN app01_book_authors on app01_book.nid = app01_book_authors.book_id INNER JOIN app01_author on app01_book_authors.author_id = app01_author.nid where app01_book.title="三国演义";
查询三国演义这本书的所有作者的名字
# 方式一 # 需求: 通过Book表join与其关联的Author表,属于正向查询:按字段authors通知ORM引擎join book_authors与author ret = Book.objects.filter(title="三国演义").values("authors__name") print(ret) # 方式二 # 需求: 通过Author表join与其关联的Book表,属于反向查询:按表名小写book通知ORM引擎join book_authors与book表 ret = Author.objects.filter(book__title="三国演义").values("name") print(ret) # < QuerySet[{'name': 'alex'}, {'name': 'egon'}] >
3 一对一
查询alex的手机号
# 方式一 # 需求: 通过Author表join与其关联的AuthorDetail表,属于正向查询:按字段authordetail通知ORM引擎join Authordetail表 ret = Author.objects.filter(name="alex").values("authordetail__telephone") print(ret ) # <QuerySet [{'authordetail__telephone': 123}]> # 方式二 # 需求: 通过AuthorDetail表join与其关联的Author表,属于反向查询:按表名小写author通知ORM引擎join Author表 ret = AuthorDetail.objects.filter(author__name="alex").values("telephone") print(ret) # <QuerySet [{'telephone': 123}]>
4 进阶练习
手机号以112开头的作者出版过的所有书籍名称以及书籍出版社名称
SELECT app01_book.title, app01_publish. NAME FROM app01_book INNER JOIN app01_book_authors ON app01_book.nid = app01_book_authors.book_id INNER JOIN app01_author ON app01_book_authors.author_id = app01_author.nid INNER JOIN app01_authordetail ON app01_author.authordetail_id = app01_authordetail.nid INNER JOIN app01_publish ON app01_book.publish_id = app01_publish.nid WHERE app01_authordetail.telephone = 112;
# 方式一 # 需求: 通过Book表join AuthorDetail表, Book与AuthorDetail无关联,所以必需连续跨表 ret =Book.objects.filter(authors__authordetail__telephone="112").values("title","publish__name") print(ret) # 方式二 ret = Author.objects.filter(authordetail__telephone="112").values("book__title","book__publish__name") print(ret)
五 聚合与分组查询
聚合 aggregate:返回值是一个字典,不再是queryset
1. 聚合查询
查询所有书籍的平均价格
from django.db.models import Avg,Max,Min,Count # select count(price) from app01__book ret = Book.objects.all()..aggregate(price_agv = Avg("price"))
print(ret)
2. 分组查询-- 单表查询
① 查询每一个部门的名称以及员工的平均薪水
from django.db.models import Avg, Max, Min, Count ret = Emp.objects.values("dep").annotate(salary_avg = Avg("salary")).values("dep","salary_avg") print(ret) # <QuerySet [{'dep': '销售部', 'salary_avg': 10000.0}, {'dep': '人事部', 'salary_avg': 3333.0}]> # 单表分组查询的ORM语法： 单表模型.objects.values("group by 的字段").annotate(聚合函数的统计字段)
② 查询每一个省份的名称以及员工数
ret = Emp.objects.values("province").annotate(c = Count("id")).values("province","c") print(ret )
# <QuerySet [{'province': '山东', 'c': 1}, {'province': '河北', 'c': 1}]>
③ 补充知识点
ret = Emp.objects.all() print(ret) # select * from emp ret = Emp.objects.values("name") print(ret) # select name from emp; # Emp.objects.all().annotate(avg_salary=Avg("salary")) # SELECT avg(salary) from app01_emp GROUP BY id # 没有意义 单表
2. 分组查询-- 多表查询
① 查询每一个出版社的名称以及出版的书籍个数
SELECT app01_publish. NAME, count(app01_book.title) FROM app01_book INNER JOIN app01_publish ON app01_book.publish_id = app01_publish.nid
from django.db.models import Avg, Max, Min, Count # ret = Publish.objects.values("nid").annotate(c=Count("book__title")) ret = Publish.objects.values("nid").annotate(c=Count("book__title")).values("name", "c") print(ret)
② 查询每一个作者的名字以及出版过的书籍的最高价格
SELECT app01_author. NAME, MAX(app01_book.price) FROM app01_author INNER JOIN app01_authordetail ON app01_author.nid = app01_authordetail.nid INNER JOIN app01_book_authors ON app01_author.nid = app01_book_authors.author_id INNER JOIN app01_book ON app01_book_authors.book_id = app01_book.nid GROUP BY app01_author.nid
ret = Author.objects.values("pk").annotate(max_price=Max("book__price")).values("name","max_price") print(ret) # <QuerySet [{'name': 'alex', 'max_price': Decimal('300.00')}, {'name': 'egon', 'max_price': Decimal('300.00')}]>
③ 查询每一个书籍的名称以及对应的作者个数
SELECT DISTINCT `app01_book`.`title`, COUNT(`app01_author`.`name`) AS `c` FROM `app01_book` INNER JOIN `app01_book_authors` ON ( `app01_book`.`nid` = `app01_book_authors`.`book_id` ) INNER JOIN `app01_author` ON ( `app01_book_authors`.`author_id` = `app01_author`.`nid` ) GROUP BY `app01_book`.`nid`
ret = Book.objects.values("pk").values(c=Count("authors__name")).values("title","c").distinct() print(ret) # <QuerySet [{'title': '水浒传', 'c': 2}, {'title': '三国演义', 'c': 2}]>
posted on 2019-11-11 18:00 augustyang 阅读(168) 评论(0) 编辑 收藏 举报
