orm操作习题一

model表结构：

from django.db import models


# 书
class Book(models.Model):
    title = models.CharField(max_length=32)
    publish_date = models.DateField(auto_now_add=True)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    memo = models.TextField(null=True)
    # 创建外键，关联publish
    publisher = models.ForeignKey(to="Publisher")
    # 创建多对多关联author
    author = models.ManyToManyField(to="Author")

    def __str__(self):
        return "<Book object: {} {}>".format(self.id, self.title)


# 出版社
class Publisher(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)

    def __str__(self):
        return "<Publisher object: {} {}>".format(self.id, self.name)


# 作者
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    phone = models.CharField(max_length=11)

    def __str__(self):
        return "<Author object: {} {}>".format(self.id, self.name)

表数据显示：

book表：

作者表：

书与作者表关系：

出版社表数据：

 习题练习：

import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE","text67天.settings")
import django
django.setup()
from django.db.models import Avg,Min,Sum,Max,Count
from app01 import models

#1查找所有书名里包含金老板的书
# print(models.Book.objects.filter(title__contains="金老板"))

#2.查找出版日期是2018年的书
# print(models.Book.objects.filter(publish_date__year=2018))

#3.查找出版日期是2017年的书名
# print([ret.title for ret in models.Book.objects.filter(publish_date__year=2017)])

#4.查找价格大于10元的书
# print(models.Book.objects.filter(price__gt = 10))

#5查找价格大于10元的书名和价格
# print(models.Book.objects.filter(price__gt = 10).values("title","price"))

#6.查找memo字段是空的书
# print(models.Book.objects.filter(memo__isnull = True))

#7.查找在北京的出版社
# print(models.Publisher.objects.filter(city__in=["北京"]))

#8.查找名字以沙河开头的出版社
# print(models.Publisher.objects.filter(name__startswith="沙河"))

#9.查找“沙河出版社”出版的所有书籍
# print(models.Book.objects.filter(publisher__name ="沙河出版社"))

#10.查找每个出版社出版价格最高的书籍价格
# print(models.Publisher.objects.values("pk","name").annotate(Max("book__price")))

#11.查找每个出版社的名字以及出的书籍数量
# print(models.Publisher.objects.values_list("pk","name").annotate(Count("book__pk")))

#12.查找作者名字里面带“小”字的作者
# print(models.Author.objects.filter(name__contains="小"))

#13.查找年龄大于30岁的作者
# print(models.Author.objects.filter(age__gt = 30))

#14.查找手机号是155开头的作者
# print(models.Author.objects.filter(phone__startswith=155))

#15.查找手机号是155开头的作者的姓名和年龄
# print(models.Author.objects.filter(phone__startswith=155).values_list("name","age"))

# 16.查找每个作者写的价格最高的书籍价格
# print(models.Author.objects.values_list("pk").annotate(Max("book__price")))

#17.查找每个作者的姓名以及出的书籍数量
# print(models.Author.objects.values_list("name").annotate(Count("book__pk")))

# 18.查找书名是“跟金老板学开车”的书的出版社
# print(models.Book.objects.filter(title="跟金老板学开车").values_list("publisher__name"))

# 19.查找书名是“跟金老板学开车”的书的出版社所在的城市
# print(models.Book.objects.filter(title="跟金老板学开车").values_list("publisher__city"))

# 20.查找书名是“跟金老板学开车”的书的出版社的名称
# print(models.Book.objects.filter(title="跟金老板学开车").values_list("publisher__name"))

# 21.查找书名是“跟金老板学开车”的书的出版社出版的其他书籍的名字和价格

# print(models.Publisher.objects.filter(pk__in=models.Book.objects.filter(title='跟金老板学开车').values("publisher_id")).values("book__title","book__price"))
# print(models.Book.objects.filter(publisher__in= models.Book.objects.filter(title="跟金老板学开车").values("publisher_id")).values_list("title","price"))


# 22.查找书名是“跟金老板学开车”的书的所有作者
# print(models.Book.objects.filter(title="跟金老板学开车").values_list("author__name"))

#23. 查找书名是“跟金老板学开车”的书的作者的年龄
# print(models.Book.objects.filter(title="跟金老板学开车").values_list("author__age"))

# 24.查找书名是“跟金老板学开车”的书的作者的手机号码
# print(models.Book.objects.filter(title="跟金老板学开车").values_list("author__phone"))

# 25.查找书名是“跟金老板学开车”的书的作者们的姓名以及出版的所有书籍名称和价钱
# print(models.Book.objects.filter(author__in=models.Book.objects.filter(title="跟金老板学开车").values("author__id")).values_list("author__name","title","price"))