Django ORM查询详解 - 数据库操作与优化完整指南

from myapp.models import MyModel

# 查询（Read）
MyModel.objects.all()              # 查询所有记录
MyModel.objects.filter(field=value)  # 条件查询
MyModel.objects.get(field=value)     # 获取单个对象

# 创建（Create）
MyModel.objects.create(field=value)

# 更新（Update）
MyModel.objects.filter(field=value).update(field=new_value)

# 删除（Delete）
MyModel.objects.filter(field=value).delete()

from myapp.models import Article

# 创建
article = Article.objects.create(
    title='Django教程',
    content='Django是Python Web框架',
    status='published'
)
print(f"创建: {article.title}, ID: {article.id}")

# 查询所有
articles = Article.objects.all()
print(f"总数: {articles.count()}")

# 条件查询
published = Article.objects.filter(status='published')
print(f"已发布: {published.count()}")

# 获取单个
article = Article.objects.get(id=1)
print(f"获取: {article.title}")

# 更新（批量）
Article.objects.filter(id=1).update(views=100)

# 更新（实例）
article.views += 1
article.save()

# 删除
Article.objects.filter(status='draft').delete()

from myapp.models import Article
from django.db.models import Q, F

# Q对象：复杂OR查询
articles = Article.objects.filter(
    Q(title__contains='Python') | Q(title__contains='Django')
)
print(f"Python或Django: {articles.count()}")

# Q对象组合AND和OR
articles = Article.objects.filter(
    Q(status='published') & (Q(title__icontains='python') | Q(title__icontains='django'))
)

# F对象：引用字段进行更新
from django.db.models import F
Article.objects.update(views=F('views') + 1)

# F对象比较两个字段
Article.objects.filter(published_date__gt=F('created_date'))

from django.db.models import Count, Avg, Max, Min
from myapp.models import Article, Category

# 聚合：对整个表进行统计
stats = Article.objects.aggregate(
    avg_views=Avg('views'),
    max_views=Max('views'),
    min_views=Min('views'),
    total=Count('id'),
)
print(f"统计: {stats}")
# 输出: {'avg_views': 150.5, 'max_views': 1000, 'min_views': 10, 'total': 200}

# 注解：为每个分类添加文章数量
categories = Category.objects.annotate(
    article_count=Count('article')
)
for cat in categories:
    print(f"  {cat.name}: {cat.article_count}篇")

# 排序 + 注解：找出文章最多的分类
top_category = Category.objects.annotate(
    article_count=Count('article')
).order_by('-article_count').first()
print(f"最多文章的分类: {top_category.name} ({top_category.article_count}篇)")

from myapp.models import Article, Category, Tag

# 正向查询（通过外键）
article = Article.objects.first()
print(f"分类: {article.category.name}")  # 会触发额外查询（N+1问题）

# select_related：优化外键查询（使用SQL JOIN，一次查询）
articles = Article.objects.select_related('category').all()
for article in articles:
    print(f"  {article.title} - {article.category.name}")  # 无额外查询

# prefetch_related：优化多对多查询（两次独立查询，Python端关联）
articles = Article.objects.prefetch_related('tags').all()
for article in articles:
    tags = [t.name for t in article.tags.all()]
    print(f"  {article.title}: {tags}")

# 跨关系查询（双下划线语法）
articles = Article.objects.filter(category__name='Python')
print(f"Python分类文章: {articles.count()}")