django ORM 查询关系
2019-05-15 本文已影响0人
dongshangtong
模型关系：
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建立一对一的关系
authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)
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()
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建立一对多的关系,外键字段建立在多的一方
publish = models.ForeignKey(to="Publish", to_field="nid", on_delete=models.CASCADE)
# 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
authors = models.ManyToManyField(to='Author', )
1.查询python这本书籍的出版社的邮箱
# 正向查询按字段
v = models.Book.objects.filter(title="python教程").first()
print(v.publish.email)
# beijing@163.com
- 反向查询按 表名小写_set.all()
# 海南出版社出版的书籍名称
# filter 和 get 区别, filter 查出来是集合，所以经常跟first(), get 查出来是对象。
v = models.Publish.objects.filter(name='海南出版社').first()
# v = models.Publish.objects.get(name='海南出版社')
for book in v.book_set.all():
print(book.title)
# golang教程
#nodejs 教程
3.查询golang作者的年龄
# 按字段（authors.all()）
# 多对多 book -----------------------> author
# <----------------
# book_set.all()
ath = models.Book.objects.filter(title="golang教程").first()
for auther in ath.authors.all():
print(auther.name, auther.age)
# 小明 10
# 莉君 12
4.查询莉君出版过的书籍名称
ath = models.Author.objects.filter(name='莉君').first()
for book in ath.book_set.all():
print(book.title)
# golang教程
# nodejs 教程
- 查询莉君的手机号
ath = models.Author.objects.filter(name='莉君').first()
print(ath.authorDetail.telephone)
# 18938641414
- 查询家在深圳的作者名字
dais = models.AuthorDetail.objects.filter(addr='深圳市')
for ath in dais:
print(ath.author.name)
# 莉君
- 北京出版社出版的书籍名称
ret1 = models.Publish.objects.filter(name="成都出版社").values('book__title')
print(ret1.query)
'''
SELECT `app01_book`.`title` FROM `app01_publish`
LEFT OUTER JOIN `app01_book`
ON (`app01_publish`.`nid` = `app01_book`.`publish_id`) WHERE `app01_publish`.`name` = 成都出版社
'''
# 方式2：
ret2 = models.Book.objects.filter(publish__name="成都出版社").values("title")
print(ret2.query)
"""
SELECT `app01_book`.`title` FROM `app01_book`
INNER JOIN `app01_publish`
ON (`app01_book`.`publish_id` = `app01_publish`.`nid`) WHERE `app01_publish`.`name` = 成都出版社
"""
7.查询手机号以188开头的作者出版过的书籍名称以及书籍对应的出版社名称
# ret3 = models.Book.objects.filter(authors__authorDetail__telephone__startswith="188").values('title', 'publish__name')
#
# print(ret3.query)
"""
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` LIKE BINARY 188%
"""
8.查询每一作者的名字以及出版过的书籍最高价格
# 正向查询
ret = models.Author.objects.values('name').annotate(Max('book__price'))
print(ret.query)
"""
SELECT `app01_author`.`name`, MAX(`app01_book`.`price`) AS `book__price__max` FROM `app01_author`
LEFT OUTER JOIN `app01_book_authors` ON (`app01_author`.`nid` = `app01_book_authors`.`author_id`)
LEFT OUTER JOIN `app01_book` ON (`app01_book_authors`.`book_id` = `app01_book`.`nid`)
GROUP BY `app01_author`.`name` ORDER BY NULL
"""
# 反向查询
ret = models.Book.objects.values('authors__name').annotate(Max('price'))
print(ret.query)
"""
SELECT `app01_author`.`name`, MAX(`app01_book`.`price`) AS `price__max` FROM `app01_book`
LEFT OUTER JOIN `app01_book_authors` ON (`app01_book`.`nid` = `app01_book_authors`.`book_id`)
LEFT OUTER JOIN `app01_author` ON (`app01_book_authors`.`author_id` = `app01_author`.`nid`)
GROUP BY `app01_author`.`name` ORDER BY NULL
"""
9.查询一个出版社的出版过的书籍的平均价格
ret= models.Publish.objects.values("name").annotate(avg_price=Avg("book__price"))
print(ret)
- 查询每一本书籍的作者个数
ret4=models.Book.objects.values('title').annotate(co=Count("authors__name")).values("title", "co")
print(ret4)
- 统计不止一个作者的图书名称: 意思是书籍有两个以上的作者
ret = models.Book.objects.annotate(c=Count("authors__name")).filter(c__gt=1).values("title", 'c')
print(ret)
"""
SELECT `app01_book`.`title`, COUNT(`app01_author`.`name`) AS `c` FROM `app01_book`
LEFT OUTER JOIN `app01_book_authors` ON (`app01_book`.`nid` = `app01_book_authors`.`book_id`)
LEFT OUTER JOIN `app01_author` ON (`app01_book_authors`.`author_id` = `app01_author`.`nid`)
GROUP BY `app01_book`.`nid` HAVING COUNT(`app01_author`.`name`) > 1 ORDER BY NULL
"""
