Django的ORM查询

1.简单查询

models.Classes.objects.all()   # 相当于select * from classes;

返回的结果是列表里套对象

models.Classes.objects.values()  # 相当于 select * from classes
models.Classes.objects.values('name','age')  # 相当于 select name,age from classes,可指定查询的字段

返回的结果是列表里套字典的形式

models.Classes.objects.values_list()   # 相当于 select * from classes
models.Classes.objects.values_list('name','age')   # 相当于 select name,age from classes,可指定查询的字段

返回的结果是列表里套元组的形式

2.distinct

models.Classes.objects.values('name').distinct()  # select distinct name from classes

models.Classes.objects.values('name','age').distinct()  # 将（name，age）作为一组过滤

3.where

models.Classes.objects.filter(id=2)  # select * from classes where id = 2

models.Classes.objects.filter(id__gt=2)  # select * from classes where id > 2

models.Classes.objects.filter(id__gte=2)  # select * from classes where id >= 2

models.Classes.objects.filter(id__lt=2)  # select * from classes where id < 2

models.Classes.objects.filter(id__lte=2)  # select * from classes where id <= 2

models.Classes.objects.exclude(id=2)  # select * from classes where id != 2

models.Classes.objects.values().filter(id__lte=2,name='nick')  # select * from classes where id <= 2 and name ='nick'

4.in和not in

models.Classes.objects.filter(id__in=[2,3])  # select * from classes where id in (2,3)

models.Classes.objects.exclude(id__in=[2,3])  # select * from classes where id not in (2,3)

5.between...and...

models.Classes.objects.filter(id__range=[1,3])  # select * from classe where id between 1 and 3

6.like

models.Classes.objects.filter(name__endswith='on')  # select * from classes where name like "%on"

models.Classes.objects.filter(name__iendswith='on')  # select * from classes where name like "%on" 匹配时忽略大小写

models.Classes.objects.filter(name__startswith='aaa')  # select * from classes where name like "aaa%"

models.Classes.objects.filter(name__istartswith='bbb')  # select * from classes where name like "bbb%" 匹配时忽略大小写

models.Classes.objects.filter(name__contains='ccc')  # select * from classes where name like "%ccc%"

models.Classes.objects.filter(name__icontains='ccc')  # select * from classes where name like "%ccc%" 匹配时忽略大小写

7.正则表达式

models.Classes.objects.filter(name__regex='\w+')  # 正则表达式匹配

models.Classes.objects.filter(name__iregex='\w+')  # 正则表达式匹配，忽略大小写

8.count

models.Classes.objects.count()  # select count(*) from classes

models.Classes.objects.filter(id__gt=2).count()  # select count(*) from classes where id > 2

9.order by

models.Classes.objects.values().order_by('age')  # select * from classes order by age 升序

models.Classes.objects.values().order_by('-age')  # select * from classes order by age desc 降序

models.Classes.objects.values().order_by('age','-id')  # select * from classes order by age asc,id desc 先按age升序排，age相同的按id降序排

10.group by

from django.db.models import Count,Min,Max,Sum,Avg  
res = models.Classes.objects.values('name').annotate(s=Sum('age'))  # select name,sum(age) as s from classes group by name; 通过名字分组，在统计每组的年龄之和
res = models.Classes.objects.values('name').annotate(s=Sum('age')).filter(s__gt=30) # 相当于select name,sum(age) as s from classes group by name having(s>30);

11.limit

models.Classes.objects.values()[1:3]  # 通过切片获取查询数 select * from classes limit 1,2

12.last：取最后一个，first：取第一个

models.Classes.objects.last()  # 返回的是最后一个对象

models.Classes.objects.first()  # 返回的是第一个对象

13.only：取指定的字段，返回的是列表套对象

models.Classes.objects.only('name')  # 只取name字段，但也会查id字段

14.defer：取排除指定的字段后的所有字段，返回的是列表套对象

models.Classes.objects.defer('name')  # 不查name字段。select id, age from classes。指定为id时无效，还是会显示id字段

15.or

from django.db.models import Q
models.Classes.objects.values().filter(Q(id__gte=3)|Q(name='nick'))  # select * from classes where id>=3 or name='nick'
models.Classes.objects.values().filter((Q(id__gte=3)|Q(name='nick'))&Q(age=19))  # select * from classes where (id>=3 or name='nick') and age=19

16.F

from django.db.models import F 
models.Classes.objects.update(age=F('age')+1)  # 将每个人的年龄加1

17.原生sql

from django.db import connection,connections
cursor = connection.cursor()  
# 相当于connections['default'].cursor(),这里的default是settings.py文件中设置的数据库，也可以连接其他的数据库如connections['mymysql'].cursor()
cursor.execute('''select * from app01_classes where id > %s''',[1])
res = cursor.fetchall()  # 返回结果是列表套元组的方式
print(res)