057.Python前端Django模型ORM多表查询

一基于对象的查询

1.1 一对多查询

设计路由

from django.contrib import admin
from django.urls import path
from app01 import views
urlpatterns = [
    path('admin/', admin.site.urls),
    path('add_book/',views.add_book),
    path('query_book/',views.query_book),
]

视图函数

查询主键为1的书籍出版社所在的城市

mysql> select * from app01_book;

+----+--------------+--------+------------+------------+
| id | title        | price  | pub_date   | publish_id |
+----+--------------+--------+------------+------------+
|  1 | 独孤九剑     | 130.00 | 2007-10-03 |       NULL |
|  2 | 吸星大法     | 110.00 | 2017-10-03 |          1 |
+----+--------------+--------+------------+------------+

重新把null改为1

mysql> update app01_book set publish_id=1 where id=1;

mysql> select * from app01_book;

+----+--------------+--------+------------+------------+
| id | title        | price  | pub_date   | publish_id |
+----+--------------+--------+------------+------------+
|  1 | 独孤九剑     | 130.00 | 2007-10-03 |          1 |
|  2 | 吸星大法     | 110.00 | 2017-10-03 |          1 |
+----+--------------+--------+------------+------------+

正向查询

book--------------------------------------------->publish：按字段

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim app01/views.py

def query_book(request):
    book_obj = models.Book.objects.filter(pk=1).first()     #拿到pk=1的书的对象
    print(book_obj.publish.city)                            #正向获取，publish表的数据
    return HttpResponse("查询成功")

访问http://127.0.0.1:8000/query_book/

查看输出结果

反向获取

按小写表名加下划线set

def query_book(request):
    #book_obj = models.Book.objects.filter(pk=1).first()
    #print(book_obj.publish.city)
    #查询华山出版社所有的书籍名
    pub_obj = models.Publish.objects.filter(name="华山出版社").first()
    for book in pub_obj.book_set.all():
        print(book.title)

    return HttpResponse("查询成功")

查看输出结果

1.2 一对一查询

正向查询

def query_book(request):
    #查询令狐冲电话
    author_obj = models.Author.objects.filter(name="令狐冲").first()
    print(author_obj.au_detail.tel)
    return HttpResponse("查询成功")

访问http://127.0.0.1:8000/query_book/

结果

[06/Apr/2020 13:48:28] "GET /query_book/ HTTP/1.1" 200 12
13432335433

反向查询

在这里不用适应_set，是因为前面是一对多，这里是一对一，就不需要使用

def query_book(request):
    #book_obj = models.Book.objects.filter(pk=1).first()
    #print(book_obj.publish.city)
    #查询华山出版社所有的书籍名
    #pub_obj = models.Publish.objects.filter(name="华山出版社").first()
    #for book in pub_obj.book_set.all():
    #   print(book.title)
    #查询令狐冲电话
    #author_obj = models.Author.objects.filter(name="令狐冲").first()
    #print(author_obj.au_detail.tel)
    #查询所有住在黑木崖的作者姓名
    au_detail_list = models.AuthorDetail.objects.filter(addr="黑木崖")
    for au in au_detail_list:
        print(au)
        print(au.author.name)
    return HttpResponse("查询成功")

访问http://127.0.0.1:8000/query_book/

结果

AuthorDetail object (2)
任我行
AuthorDetail object (3)
任盈盈
[06/Apr/2020 13:55:46] "GET /query_book/ HTTP/1.1" 200 12

1.3 多对多关系查询

mysql> select * from app01_book_authors;

+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         9 |
|  5 |       2 |         8 |
+----+---------+-----------+

mysql> select * from app01_author;

+----+-----------+-----+--------------+
| id | name      | age | au_detail_id |
+----+-----------+-----+--------------+
|  7 | 令狐冲    |  25 |            1 |
|  8 | 任我行    |  58 |            2 |
|  9 | 任盈盈    |  23 |            3 |
+----+-----------+-----+--------------+

mysql> insert into app01_book_authors (book_id,author_id) values (1,7);

mysql> select * from app01_book_authors;

+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  7 |       1 |         7 |
|  1 |       1 |         9 |
|  5 |       2 |         8 |
+----+---------+-----------+

views文件

def query_book(request):
    #book_obj = models.Book.objects.filter(pk=1).first()
    #print(book_obj.publish.city)
    #查询华山出版社所有的书籍名
    #pub_obj = models.Publish.objects.filter(name="华山出版社").first()
    #for book in pub_obj.book_set.all():
    #   print(book.title)
    #查询令狐冲电话
    #author_obj = models.Author.objects.filter(name="令狐冲").first()
    #print(author_obj.au_detail.tel)
    #查询所有住在黑木崖的作者姓名
    # au_detail_list = models.AuthorDetail.objects.filter(addr="黑木崖")
    #for au in au_detail_list:
    #    print(au)
    #    print(au.author.name)
    #查出独孤九剑的所有作者和电话
    book_obj = models.Book.objects.filter(title="独孤九剑").first()
    for au in book_obj.authors.all():
        print(au.name,au.au_detail.tel)
    return HttpResponse("查询成功")

访问http://127.0.0.1:8000/query_book/

结果

令狐冲 13432335433
任盈盈 13878934322
[06/Apr/2020 14:21:34] "GET /query_book/ HTTP/1.1" 200 12

方法二

def query_book(request):
    #book_obj = models.Book.objects.filter(pk=1).first()
    #print(book_obj.publish.city)
    #查询华山出版社所有的书籍名
    #pub_obj = models.Publish.objects.filter(name="华山出版社").first()
    #for book in pub_obj.book_set.all():
    #   print(book.title)
    #查询令狐冲电话
    #author_obj = models.Author.objects.filter(name="令狐冲").first()
    #print(author_obj.au_detail.tel)
    #查询所有住在黑木崖的作者姓名
    # au_detail_list = models.AuthorDetail.objects.filter(addr="黑木崖")
    #for au in au_detail_list:
    #    print(au)
    #    print(au.author.name)
    #查出独孤九剑的所有作者和电话
    #book_obj = models.Book.objects.filter(title="独孤九剑").first()
    #for au in book_obj.authors.all():
    #   print(au.name,au.au_detail.tel)
    #查询令狐冲所有的书籍名称
    au_obj = models.Author.objects.filter(name="令狐冲").first()
    for book in au_obj.book_set.all():
        print(book.title)

    return HttpResponse("查询成功")

反向查询

访问http://127.0.0.1:8000/query_book/

结果

独孤九剑
[06/Apr/2020 14:26:24] "GET /query_book/ HTTP/1.1" 200 12

回到顶部

二基于双下划线的跨表查询

Django 还提供了一种直观而高效的方式在查询(lookups)中表示关联关系，它能自动确认 SQL JOIN 联系。要做跨关系查询，就使用两个下划线来链接模型(model)间关联字段的名称，直到最终链接到你想要的model 为止。

2.1 一对多

正向查询按字段,反向查询按表名小写用来告诉ORM引擎join哪张表

views视图文件

def query_book(request):
    #book_obj = models.Book.objects.filter(pk=1).first()
    #print(book_obj.publish.city)
    #查询华山出版社所有的书籍名
    #pub_obj = models.Publish.objects.filter(name="华山出版社").first()
    #for book in pub_obj.book_set.all():
    #   print(book.title)
    #查询令狐冲电话
    #author_obj = models.Author.objects.filter(name="令狐冲").first()
    #print(author_obj.au_detail.tel)
    #查询所有住在黑木崖的作者姓名
    # au_detail_list = models.AuthorDetail.objects.filter(addr="黑木崖")
    #for au in au_detail_list:
    #    print(au)
    #    print(au.author.name)
    #查出独孤九剑的所有作者和电话
    #book_obj = models.Book.objects.filter(title="独孤九剑").first()
    #for au in book_obj.authors.all():
    #   print(au.name,au.au_detail.tel)
    #查询令狐冲所有的书籍名称
    #au_obj = models.Author.objects.filter(name="令狐冲").first()
    #for book in au_obj.book_set.all():
    #    print(book.title)
    #基于双下划线查询
    #一对多，查询华山出版社出版过的所有书籍和价格
    books = models.Publish.objects.filter(name="华山出版社").values("book__title","book__price")
    print(books)
    #反向
    books = models.Book.objects.filter(publish__name="华山出版社").values("title","price")
    print(books)
    return HttpResponse("查询成功")

访问http://127.0.0.1:8000/query_book/

结果

<QuerySet [{'book__title': '独孤九剑', 'book__price': Decimal('130.00')}, {'book__title': '吸星大法', 'book__price': Decimal('110.00')}]>
<QuerySet [{'title': '独孤九剑', 'price': Decimal('130.00')}, {'title': '吸星大法', 'price': Decimal('110.00')}]>
[07/Apr/2020 06:39:31] "GET /query_book/ HTTP/1.1" 200 12

2.2 多对多查询

views视图文件

def query_book(request):
    #book_obj = models.Book.objects.filter(pk=1).first()
    #print(book_obj.publish.city)
    #查询华山出版社所有的书籍名
    #pub_obj = models.Publish.objects.filter(name="华山出版社").first()
    #for book in pub_obj.book_set.all():
    #   print(book.title)
    #查询令狐冲电话
    #author_obj = models.Author.objects.filter(name="令狐冲").first()
    #print(author_obj.au_detail.tel)
    #查询所有住在黑木崖的作者姓名
    # au_detail_list = models.AuthorDetail.objects.filter(addr="黑木崖")
    #for au in au_detail_list:
    #    print(au)
    #    print(au.author.name)
    #查出独孤九剑的所有作者和电话
    #book_obj = models.Book.objects.filter(title="独孤九剑").first()
    #for au in book_obj.authors.all():
    #   print(au.name,au.au_detail.tel)
    #查询令狐冲所有的书籍名称
    #au_obj = models.Author.objects.filter(name="令狐冲").first()
    #for book in au_obj.book_set.all():
    #    print(book.title)
    #基于双下划线查询
    #一对多，查询华山出版社出版过的所有书籍和价格
    #books = models.Publish.objects.filter(name="华山出版社").values("book__title","book__price")
    #print(books)
    #反向
    #books = models.Book.objects.filter(publish__name="华山出版社").values("title","price")
    #print(books)
    #多对多,查出令狐冲出国所有的书籍的名字
    books = models.Book.objects.filter(authors__name="令狐冲").values("title")
    print(books)
    books = models.Author.objects.filter(name="令狐冲").values("book__title")
    print(books)
    return HttpResponse("查询成功")

访问http://127.0.0.1:8000/query_book/

结果

<QuerySet [{'title': '独孤九剑'}]>
<QuerySet [{'book__title': '独孤九剑'}]>

回到顶部

三聚合查询和分组查询

3.1 聚合查询aggregate

aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句指定它

views视图文件

def query_book(request):
    #聚合查询
    from django.db.models  import Avg,Max,Min,Count,Sum
    books = models.Book.objects.aggregate(Avg("price"))
    print(books)

访问http://127.0.0.1:8000/query_book/

结果

{'price__avg': Decimal('120.000000')}
[07/Apr/2020 09:25:27] "GET /query_book/ HTTP/1.1" 200 12

如果你希望生成不止一个聚合，你可以向aggregate() 子句中添加另一个参数。所以，如果你也想知道所有图书价格的最大值和最小值，可以这样查询：

views视图文件

def query_book(request):
    #聚合查询
    from django.db.models  import Avg,Max,Min,Count,Sum
    books = models.Book.objects.aggregate(Avg("price"),Max("price"),Min("price"))
    print(books)

访问http://127.0.0.1:8000/query_book/

结果

[07/Apr/2020 09:58:04] "GET /query_book/ HTTP/1.1" 200 12
{'price__avg': Decimal('120.000000'), 'price__max': Decimal('130.00'), 'price__min': Decimal('110.00')}

3.2 分组查询-单表操作

首先建一个表的模型

from django.db import models

# Create your models here.
class Book(models.Model):
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    pub_date = models.DateField()
    publish = models.ForeignKey("Publish", on_delete=models.CASCADE,null=True)
    authors = models.ManyToManyField("Author")


class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=64)
    email = models.EmailField()


class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.SmallIntegerField()
    au_detail = models.OneToOneField("AuthorDetail", on_delete=models.CASCADE)


class AuthorDetail(models.Model):
    gender_choices = (
        (0, "女"),
        (1, "男"),
        (2, "保密"),
    )
    gender = models.SmallIntegerField(choices=gender_choices)
    tel = models.CharField(max_length=32)
    addr = models.CharField(max_length=64)
    birthday = models.DateField()
#新加部分
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)

执行迁移操作

root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py makemigrations
Migrations for 'app01':
  app01/migrations/0003_emp.py
    - Create model Emp
root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, app01, auth, contenttypes, sessions
Running migrations:
  Applying app01.0003_emp... OK

向Emp表中插入数据

mysql> select * from app01_emp;
mysql> INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('1', '令狐冲', '24', '6000.00', '销售部', '河南');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('2', '任盈盈', '18', '8000.00', '公关部', '广东');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO  `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('3', '任我行', '56', '10000.00', '销售部', '广东');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('4', '岳灵珊', '19', '6000.00', '公关部', '河南');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO  `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('5', '小龙女', '20', '8000.00', '公关部', '河北');
Query OK, 1 row affected (0.00 sec)

查看数据

查询操作：

mysql> select dep, count(1) from app01_emp group by dep;

mysql> select dep, avg(salary) from app01_emp GROUP BY dep;

使用orm

views视图文件

def query_book(request):
    from django.db.models  import Avg,Max,Min,Count,Sum
    #单表分组查询
    dep = models.Emp.objects.values('dep').annotate(c=Count('id'))
    print(dep)
    sa = models.Emp.objects.values('dep').annotate(a=Avg('salary'))
    print(sa)
    return HttpResponse("查询成功")

访问http://127.0.0.1:8000/query_book/

结果

<QuerySet [{'dep': '销售部', 'c': 2}, {'dep': '公关部', 'c': 3}]>
<QuerySet [{'dep': '销售部', 'a': Decimal('8000.000000')}, {'dep': '公关部', 'a': Decimal('7333.333333')}]>
[07/Apr/2020 10:53:14] "GET /query_book/ HTTP/1.1" 200 12

3.3 分组操作-多表查询

表结构

class Emps(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8, decimal_places=2)
    dep = models.ForeignKey("Dep", on_delete=models.CASCADE)
    province = models.CharField(max_length=32)


class Dep(models.Model):
    title = models.CharField(max_length=32)

执行数据库迁移操作

root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py makemigrations
Migrations for 'app01':
  app01/migrations/0004_dep_emps.py
    - Create model Dep
    - Create model Emps
root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, app01, auth, contenttypes, sessions
Running migrations:
  Applying app01.0004_dep_emps... OK

插入dep表数据

mysql> INSERT INTO `app01_dep` (`id`, `title`) VALUES ('1', '销售部');
mysql> INSERT INTO `app01_dep` (`id`, `title`) VALUES ('2', '公关部');

插入emps表数据

mysql> INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('2', '令狐冲', '24', '8000.00', '河南', '1');
mysql> INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('3', '任盈盈', '18', '9000.00', '广东', '2');
mysql> INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('4', '任我行', '57', '10000.00', '广东', '1');
mysql> INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('5', '岳灵珊', '19', '6000.00', '河南', '2');
mysql> INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('6', '小龙女', '20', '8000.00', '河北', '2');

查看数据

多表分组查询

sq语句查询

查询每一个部门名称以及对应的员工数
mysql> select app01_dep.title,count(app01_emps.id) from app01_emps LEFT JOIN app01_dep ON  app01_emps.dep_id = app01_dep.id GROUP BY app01_emps.dep_id;
+-----------+----------------------+
| title     | count(app01_emps.id) |
+-----------+----------------------+
| 销售部    |                    2 |
| 公关部    |                    3 |
+-----------+----------------------+
查询每一个部门名称以及对应的员工的平均工资
mysql> select app01_dep.title,avg(app01_emps.salary) FROM app01_emps LEFT JOIN app01_dep ON app01_emps.dep_id = app01_dep.id GROUP BY app01_emps.dep_id;
+-----------+------------------------+
| title     | avg(app01_emps.salary) |
+-----------+------------------------+
| 销售部    |            9000.000000 |
| 公关部    |            7666.666667 |
+-----------+------------------------+

使用orm

views视图文件

def query_book(request):      
    from django.db.models  import Avg,Max,Min,Count,Sum
    #查询每一个部门名称以及对应的员工数
    dep_count = models.Emps.objects.values("dep__title").annotate(c=Count("id"))
    print(dep_count)
    #查询每一个部门名称以及对应的员工的平均工资
    sa = models.Emps.objects.values("dep__title").annotate(a=Avg("salary"))
    print(sa)
    return HttpResponse("查询成功")

结果

<QuerySet [{'dep__title': '销售部', 'c': 2}, {'dep__title': '公关部', 'c': 3}]>
<QuerySet [{'dep__title': '销售部', 'a': Decimal('9000.000000')}, {'dep__title': '公关部', 'a': Decimal('7666.666667')}]>
[07/Apr/2020 11:43:21] "GET /query_book/ HTTP/1.1" 200 12

统计每一个出版社价格最便宜的书

sql语句

mysql> select name,min(app01_book.price) from app01_publish LEFT JOIN app01_book on app01_book.publish_id = app01_publish.id GROUP BY app01_publish.name;

统计每一本书的作者个数

mysql> select title,count(NAME) from app01_book LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id LEFT JOIN app01_author ON app01_book_authors.author_id = app01_author.id GROUP BY app01_book.id;

统计每一本以“吸”开头的书籍的作者个数

mysql> select title,count(NAME) FROM app01_book LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id LEFT JOIN app01_author ON app01_book

统计不止一个作者的图书名称

SELECT
    title,
    count(NAME) AS num
FROM
    app01_book
LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id
LEFT JOIN app01_author ON app01_author.id = app01_book_authors.author_id
GROUP BY
    app01_book.id
HAVING
    num > 1;

根据一本图书作者数量的多少对查询集QuerySet进行排序

mysql> SELECT
    ->     title,
    ->     count(author_id) AS num
    -> FROM
    ->     app01_book
    -> LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id
    -> LEFT JOIN app01_author ON app01_author.id = app01_book_authors.author_id
    -> GROUP BY
    ->     app01_book.id
    -> ORDER BY
    ->     num;

查询各个作者出的书的总价格

mysql> SELECT
    ->     NAME,
    ->     sum(price)
    -> FROM
    ->     app01_author
    -> LEFT JOIN app01_book_authors ON app01_author.id = app01_book_authors.author_id
    -> LEFT JOIN app01_book ON app01_book.id = app01_book_authors.book_id
    -> GROUP BY
    ->     app01_author.id;

使用orm

views视图文件

rom django.shortcuts import render,HttpResponse
from app01 import models
# Create your views here.
def add_book(request):
    if request.method == "GET":
        return render(request,"add_book.html")
    else:
        #获取数据
        title = request.POST.get("title")
        publish = request.POST.get("publish")
        price = request.POST.get("price")
        pub_date = request.POST.get("pub_date")
        #方式一，通过传对象的方式，首先获取对象
        #pub_obj = models.Publish.objects.filter(name=publish).first()
        #book_obj = models.Book.objects.create(title=title,price=price,pub_date=pub_date,publish=pub_obj)
        #books = models.Book.objects.create(title=title, price=price, pub_date=pub_date, publish_id=pub_obj.pk)
        #多对多，方式一
        #book = models.Book.objects.filter(title="独孤九剑").first()
        #ling = models.Author.objects.filter(name="令狐冲").first()
        #ying = models.Author.objects.filter(name="任盈盈").first()
        #book.authors.add(ling, ying)
        #方式二
        #book = models.Book.objects.filter(title="吸星大法").first()
        #ling = models.Author.objects.filter(name='令狐冲').first()
        #ying = models.Author.objects.filter(name='任我行').first()
        #book.authors.add(ling.pk, ying.pk)
        #author_obj = models.Author.objects.filter(au_detail_id=1).first()
        #print(author_obj)
        #author_obj.book_set.clear()

        pub_obj = models.Publish.objects.get(pk=1)
        book_obj = models.Book.objects.get(pk=1)
        pub_obj.book_set.remove(book_obj)
    return HttpResponse("新增成功")
def query_book(request):
    #book_obj = models.Book.objects.filter(pk=1).first()
    #print(book_obj.publish.city)
    #查询华山出版社所有的书籍名
    #pub_obj = models.Publish.objects.filter(name="华山出版社").first()
    #for book in pub_obj.book_set.all():
    #   print(book.title)
    #查询令狐冲电话
    #author_obj = models.Author.objects.filter(name="令狐冲").first()
    #print(author_obj.au_detail.tel)
    #查询所有住在黑木崖的作者姓名
    # au_detail_list = models.AuthorDetail.objects.filter(addr="黑木崖")
    #for au in au_detail_list:
    #    print(au)
    #    print(au.author.name)
    #查出独孤九剑的所有作者和电话
    #book_obj = models.Book.objects.filter(title="独孤九剑").first()
    #for au in book_obj.authors.all():
    #   print(au.name,au.au_detail.tel)
    #查询令狐冲所有的书籍名称
    #au_obj = models.Author.objects.filter(name="令狐冲").first()
    #for book in au_obj.book_set.all():
    #    print(book.title)
    #基于双下划线查询
    #一对多，查询华山出版社出版过的所有书籍和价格
    #books = models.Publish.objects.filter(name="华山出版社").values("book__title","book__price")
    #print(books)
    #反向
    #books = models.Book.objects.filter(publish__name="华山出版社").values("title","price")
    #print(books)
    #多对多,查出令狐冲出国所有的书籍的名字
    #books = models.Book.objects.filter(authors__name="令狐冲").values("title")
    #print(books)
    #books = models.Author.objects.filter(name="令狐冲").values("book__title")
    #print(books)
    #聚合查询
    from django.db.models  import Avg,Max,Min,Count,Sum
    #books = models.Book.objects.aggregate(Avg("price"),Max("price"),Min("price"))
    #print(books)
    #单表分组查询
    #dep = models.Emp.objects.values('dep').annotate(c=Count('id'))
    #print(dep)
    #sa = models.Emp.objects.values('dep').annotate(a=Avg('salary'))
    #print(sa)
    #books = models.Publish.objects.values("name").annotate(min_price=Min("book__price"))
    #print(books)
    #查询每一个部门名称以及对应的员工数
    #dep_count = models.Emps.objects.values("dep__title").annotate(c=Count("id"))
    #print(dep_count)
    #查询每一个部门名称以及对应的员工的平均工资
    #sa = models.Emps.objects.values("dep__title").annotate(a=Avg("salary"))
    #print(sa)
    price = models.Publish.objects.values("name").annotate(min_price=Min("book__price"))
    print(price)
    count = models.Book.objects.annotate(num=Count('authors__name')).values("title", "num")
    print(count)
    author_count = models.Book.objects.filter(title__startswith="吸").annotate(num=Count('authors__name')).values("title", "num")
    print(author_count)
    books = models.Book.objects.annotate(num_author=Count("authors__name")).filter(num_author__gt=1).values("title")
    print(books)
    num = models.Book.objects.annotate(num_author=Count("authors__name")).order_by("num_author").values("title", "num_author")
    print(num)
    sum_price = models.Author.objects.annotate(total=Sum("book__price")).values('name', 'total')
    print(sum_price)
    return HttpResponse("查询成功")

对应六个结果

<QuerySet [{'name': '华山出版社', 'min_price': Decimal('110.00')}, {'name': '明教出版社', 'min_price': None}]>
<QuerySet [{'title': '独孤九剑', 'num': 2}, {'title': '吸星大法', 'num': 1}]>
<QuerySet [{'title': '吸星大法', 'num': 1}]>
<QuerySet [{'title': '独孤九剑'}]>
<QuerySet [{'title': '吸星大法', 'num_author': 1}, {'title': '独孤九剑', 'num_author': 2}]>
<QuerySet [{'name': '令狐冲', 'total': Decimal('130.00')}, {'name': '任盈盈', 'total': Decimal('130.00')}, {'name': '任我行', 'total': Decimal('110.00')}]>
[07/Apr/2020 12:09:26] "GET /query_book/ HTTP/1.1" 200 12

和使用sql语句对比，结果一样

回到顶部

四 F查询与Q查询

4.1 F查询

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

查询工资大于年龄的人

from django.db.models import F, Q
    #查询工资大于年龄的人
    person = models.Emp.objects.filter(salary__gt=F('age'))
    print(person)
    #查询工资小于两倍年龄值的人
    people = models.Emp.objects.filter(salary__lt=F("age") * 2)
    print(people)
    #修改操作也可以使用F函数,比如将每一本书的价格提高100元
    models.Book.objects.update(price=F('price') + 100)

查看数据库结果

4.2 Q查询

filter() 等方法中的关键字参数查询都是一起进行“AND” 的。如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。

from django.db.models import F,Q
    #查询工资大于年龄的人
    #person = models.Emp.objects.filter(salary__gt=F('age'))
    #print(person)
    #查询工资小于两倍年龄值的人
    #people = models.Emp.objects.filter(salary__lt=F("age") * 2)
    #print(people)
    #修改操作也可以使用F函数,比如将每一本书的价格提高100元
    #models.Book.objects.update(price=F('price') + 100)
    # 查询价格大于220或者名称以吸开头的书籍
    books = models.Book.objects.filter(Q(price__gt=220) | Q(title__startswith="吸"))
    print(books)
    # 查询价格大于220或者不是2007年10月份的书籍
    books = models.Book.objects.filter(Q(price__gt=220)|~Q(Q(pub_date__year=2007)&Q(pub_date__month=10)))
    print(books)
    return HttpResponse("查询成功")

得到结果

<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
[07/Apr/2020 12:37:58] "GET /query_book/ HTTP/1.1" 200 12

对照数据库，看结果

posted @ 2021-01-24 23:10 uplee 阅读(117) 评论(0) 收藏举报

刷新页面返回顶部

我的菜园子

057.Python前端Django模型ORM多表查询

一基于对象的查询

1.1 一对多查询

1.2 一对一查询

1.3 多对多关系查询

二基于双下划线的跨表查询

2.1 一对多

2.2 多对多查询

三聚合查询和分组查询

3.1 聚合查询aggregate

3.2 分组查询-单表操作

3.3 分组操作-多表查询

四 F查询与Q查询

4.1 F查询

4.2 Q查询

公告

我的菜园子

057.Python前端Django模型ORM多表查询

一 基于对象的查询

1.1 一对多查询

1.2 一对一查询

1.3 多对多关系查询

二 基于双下划线的跨表查询

2.1 一对多

2.2 多对多查询

三 聚合查询和分组查询

3.1 聚合查询aggregate

3.2 分组查询-单表操作

3.3 分组操作-多表查询

四 F查询与Q查询

4.1 F查询

4.2 Q查询

公告

一基于对象的查询

二基于双下划线的跨表查询

三聚合查询和分组查询