Django的orm的 数据库查询语法大全

Django的orm的高级操作

查询语法大全:

  1. 比较运算符
# id > 3
res = models.UserInfo.objects.filter(id__gt=3)
# id >= 3
res = models.UserInfo.objects.filter(id__gte=3)
# id < 3
res = models.UserInfo.objects.filter(id__lt=3)
# id <= 3
res = models.UserInfo.objects.filter(id__lte=3)
# id != 3
res = models.UserInfo.objects.exclude(id=3)
# id = 2 and name = "zekai"
res = models.UserInfo.objects.filter(id=2,name="zekai")


# or
from django.db.models import Q
res = models.UserInfo.objects.filter(Q(id__gt=3|Q(name='zekai')))

2 . 身份运算符 in, not in

in
res = models.UserInfo.objects.filter(id__in=[2,3,4])

not in
res = models.UserInfo.objects.exclude(id__in=[2,3,4])

3 .between...and

res = models.UserInfo.objects.filter(id__range=[1,2,3,4])

4 .like

# 以'ze'开头
res = models.UserInfo.objects.filter(name__startswith='ze')

# g: 全局 global  i:忽略大小写 ignore    以ze开头,且忽略大小写
res = models.UserInfo.objects.filter(name__istartswith='ze')


# 以'ze'结尾
res = models.UserInfo.objects.filter(name__endswith='ze')

res = models.UserInfo.objects.filter(name__iendswith='ze')

# 包含ze的
res = models.UserInfo.objects.filter(name__contains='ze')
res = models.UserInfo.objects.filter(name__icontains='ze')


5 .count

res = models.UserInfo.objects.filter(id__gt=3).count()

6 .order by

# 数据以id升序
res = models.UserInfo.objects.all().order_by('id')


# 数据以id降序
res = models.UserInfo.objects.all().order_by('-id')

# 数据以id降序,id相同的话 以age升序
res = models.UserInfo.objects.all().order_by('-id','age')

7 . group by

 导入模块
from django.db.models import Avg, Sum, Max, Min, Count

# 
res = models.UserInfo.objects.values("name").annotate(s=Sum('age'))


res = models.UserInfo.objects.values("name").annotate(s=Sum('age')).filter(s__gt=50)

8 .limit

# 从第10行开始取,取到20行
all()就是一个列表。因此[10:20]就是一个切片
res = models.UserInfo.objects.all()[10:20]
9. last
# 取最后一行数据
res = models.UserInfo.objects.last()
10. only
# 仅取name这列数据,(id默认会被取进去)
res = models.UserInfo.objects.only('name')
11. defer
# 排除name这列数据,其他列的数据都会取进去,(id默认会被取进去)
res = models.UserInfo.objects.defer('name')
12. F(批量操作列的数据)
from django.db.models import F
# 对UserInfo表中age列的所有数据 加 1
res = models.UserInfo.objects.update(age=F('age')+1)

原生的SQL

1 .使用extra

'''
解释:结果集修改器,一种提供额外查询参数的机制
说明:依赖model模型
'''
用在where后:
    Book.objects.filter(publisher_id="1").extra(where=["title='python学习1'"])    

用在select后  
    Book.objects.filter(publisher_id="1").extra(select={"count":"select count(*) from hello_book"})

2 .使用raw方法

'''
解释:执行原始sql并返回模型
说明:依赖model多用于查询
'''
# 用法:
book = Book.objects.raw("select * from hello_book")
for item in book:
    print(item.title)

3 . 执行自定义SQL

'''
解释:利用游标执行
导入:from django.db import connection
说明:不依赖model
'''

# 用法:

from django.db import connection
cursor = connection.cursor()
#插入
cursor.execute("insert into hello_author(name) values('xiaol')")
#更新
cursor.execute("update hello_author set name='xiaol' where id=1")
#删除
cursor.execute("delete from hello_author where name='xiaol'")
#查询
cursor.execute("select * from hello_author")
#返回一行
raw = cursor.fetchone()
print(raw)
#返回所有
cursor.fetchall()
posted @ 2019-08-18 14:33  『侠客行』  阅读(1067)  评论(0编辑  收藏  举报