Django ORM 练习题及答案

框架(架构)  /  管理员 发布于 7年前   214

1.modles中表结构

#出版社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 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)  #创建外键,关联Publisher  Publisher = models.ForeignKey(to='Publisher')  def __str__(self):    return "<Book object: {} {}>".format(self.id, self.title#作者class Author(models.Model):  name = models.CharField(max_length =32)  age = models.IntegerField()  phone = models.CharField(max_length=11)  #创建多对多关联  books = models.ManyToManyField(to='Book')  def __str__(self):    return "<Author object: {} {}>".format(self.id, self.name)

2.题目

"""查找所有书名里包含金老板的书查找出版日期是2018年的书查找出版日期是2017年的书名查找价格大于10元的书查找价格大于10元的书名和价格查找memo字段是空的书查找在北京的出版社查找名字以沙河开头的出版社查找“沙河出版社”出版的所有书籍查找每个出版社出版价格最高的书籍价格查找每个出版社的名字以及出的书籍数量查找作者名字里面带“小”字的作者查找年龄大于30岁的作者查找手机号是155开头的作者查找手机号是155开头的作者的姓名和年龄查找每个作者写的价格最高的书籍价格查找每个作者的姓名以及出的书籍数量查找书名是“跟金老板学开车”的书的出版社查找书名是“跟金老板学开车”的书的出版社所在的城市查找书名是“跟金老板学开车”的书的出版社的名称查找书名是“跟金老板学开车”的书的出版社出版的其他书籍的名字和价格查找书名是“跟金老板学开车”的书的所有作者查找书名是“跟金老板学开车”的书的作者的年龄查找书名是“跟金老板学开车”的书的作者的手机号码查找书名是“跟金老板学开车”的书的作者们的姓名以及出版的所有书籍名称和价钱"""题目

3.测试数据

-- ------------------------------ Records of app01_author-- ----------------------------INSERT INTO `app01_author` VALUES ('1', '金老板', '18', '15512351234');INSERT INTO `app01_author` VALUES ('2', '小哪吒', '20', '15312341234');INSERT INTO `app01_author` VALUES ('3', 'Alex', '73', '15512341234'); -- ------------------------------ Records of app01_publisher-- ----------------------------INSERT INTO `app01_publisher` VALUES ('1', '沙河出版社', '北京');INSERT INTO `app01_publisher` VALUES ('2', '西二旗出版社', '北京');INSERT INTO `app01_publisher` VALUES ('3', '张江出版社', '上海');INSERT INTO `app01_publisher` VALUES ('4', '沙河出版社', '上海'); -- ------------------------------ Records of app01_book-- ----------------------------INSERT INTO `app01_book` VALUES ('1', '跟金老板学开车', '2018-08-03', '12.90', null, '1');INSERT INTO `app01_book` VALUES ('2', '跟金老板学开潜艇', '2017-08-10', '9.99', null, '1');INSERT INTO `app01_book` VALUES ('3', '跟老男孩学思想', '2018-09-03', '39.99', null, '2');INSERT INTO `app01_book` VALUES ('4', '跟egon学喊麦', '2018-06-12', '0.99', null, '4'); -- ------------------------------ Records of app01_book_author-- ----------------------------INSERT INTO `app01_book_author` VALUES ('3', '1', '1');INSERT INTO `app01_book_author` VALUES ('4', '2', '1');INSERT INTO `app01_book_author` VALUES ('5', '1', '2');INSERT INTO `app01_book_author` VALUES ('2', '2', '2');INSERT INTO `app01_book_author` VALUES ('6', '3', '3');INSERT INTO `app01_book_author` VALUES ('7', '3', '4');测试数据

4.答案

import osos.environ.setdefault("DJANGO_SETTINGS_MODULE", "ORMHomework.settings")import djangodjango.setup()from app01 import modelsfrom django.db.models import Max, Min, Sum, Avg, Count# 查找所有书名里包含金老板的书ret1= models.Book.objects.filter(title__contains='金老板')# print (ret1)#查找出版日期是2018年的书ret2 = models.Book.objects.filter(publish_date__year=2018)# print (ret2)#查找出版日期是2017年的书名ret3 = models.Book.objects.filter(publish_date__year=2017).values('title')# print (ret3)#查找价格大于10元的书ret4 =models.Book.objects.filter(price__gt=10)# print(ret4)#查找价格大于10元的书名和价格ret5 = models.Book.objects.filter(price__gt=10).values('title','price')# print(ret5)# 查找memo字段是空的书ret6 = models.Book.objects.filter(memo__isnull=True)# print(ret6)#-----------------------------------------------------------------------------# 查找在北京的出版社ret7 = models.Publisher.objects.filter(city='北京')# print(ret7)# 查找名字以沙河开头的出版社ret8 = models.Publisher.objects.filter(name__startswith='沙河')# print(ret8)#查找“沙河出版社”出版的所有书籍ret9 =models.Book.objects.filter(Publisher__name='沙河出版社')# print(ret9)# 查找每个出版社出版价格最高的书籍价格# ret10 = models.Publisher.objects.all().annotate(max=Max('book__price')).values('name','max')# ret10 = models.Publisher.objects.annotate(max=Max('book_price')).values()# for i in ret10 :#   print(i)# 查找每个出版社的名字以及出的书籍数量ret11 = models.Publisher.objects.annotate(count=Count('book__title')).values('name','count')# for i in ret11:#   print(i)#---------------------------------------------------------------------------------------------------------#查找作者名字里面带“小”字的作者ret12 = models.Author.objects.filter(name__contains='小')# print(ret12)#查找年龄大于30岁的作者ret13 = models.Author.objects.filter(age__gt=30)# print(ret13)#查找手机号是155开头的作者ret14 = models.Author.objects.filter(phone__startswith=155)# print(ret14)#查找手机号是155开头的作者的姓名和年龄ret15 = models.Author.objects.filter(phone__startswith=155).values('name','age')# print(ret15)#查找每个作者写的价格最高的书籍价格# ret16 = models.Author.objects.annotate(max=Max('books__price')).values('name','max')ret16= models.Book.objects.values('author').annotate(max=Max('price')).values('author','max')# for i in ret16:#   print(i)#查找每个作者的姓名以及出的书籍数量# ret17 = models.Author.objects.all().annotate(count=Count('books__title')).values('name','count')# for i in ret17 :#   print(i)#-------------------------------------------------------------------------------------------------------#查找书名是“跟金老板学开车”的书的出版社ret18 = models.Publisher.objects.filter(book__title='跟金老板学开车')# print (ret18)#查找书名是“跟金老板学开车”的书的出版社所在的城市ret19 = models.Publisher.objects.filter(book__title='跟金老板学开车').values('city')# print(ret19)#查找书名是“跟金老板学开车”的书的出版社的名称ret20 = models.Publisher.objects.filter(book__title='跟金老板学开车').values('name')# print(ret20)#查找书名是“跟金老板学开车”的书的出版社出版的其他书籍的名字和价格pub_obj = models.Publisher.objects.get(book__title='跟金老板学开车')ret21= pub_obj.book_set.all().exclude(title='跟金老板学开车').values('title','price')print(ret21)#查找书名是“跟金老板学开车”的书的所有作者# ret22 = models.Author.objects.filter(books__title='跟金老板学开车')# print(ret22)#查找书名是“跟金老板学开车”的书的作者的年龄# ret23 = models.Author.objects.filter(books__title='跟金老板学开车').values('name','age')# print(ret23)#查找书名是“跟金老板学开车”的书的作者的手机号码# ret24 = models.Author.objects.filter(books__title='跟金老板学开车').values('name','phone')# print(ret24)#查找书名是“跟金老板学开车”的书的作者们的姓名以及出版的所有书籍名称和价钱# ret25= models.Author.objects.filter(books__title='跟金老板学开车')# print(ret25)# for i in ret25:#   print(i.books.all().values('title','price'))## ret = models.Book.objects.aggregate(Max('price'))# print(ret)ret25 = models.Author.objects.values('name','books__title','books__price').filter(books__title='跟金老板学开车')print(ret25)答案

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。