Django的orm的 数据库查询语法大全
Django的orm的高级操作
查询语法大全:
- 比较运算符
# 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()
![微信](https://www.cnblogs.com/images/cnblogs_com/bladecheng/1492360/o_weinxin.png)
![QQ](https://www.cnblogs.com/images/cnblogs_com/bladecheng/1492360/o_qrcode_1561712740560.jpg)
个性签名:独学而无友,则孤陋而寡闻!
如果觉得这篇文章对你有小小的帮助的话,记得点个“关注”哦,博主在此感谢!还可以扫码添加好友,交流编程上的问题哦!
万水千山总是情,点赞再走行不行!哈哈哈(っ•̀ω•́)っ✎⁾⁾!