Django 08.14

正想查询

# 查询所有用户类型
# res =models.UserInfo.objects.all()
# for obj in res:
#     print(obj.username,obj.age,obj.ut.title)

#反向查询:
# 查询所有用户的类型
# res=models.UserType.objects.all()
# for obj in res:
#     #表名小写_set
#     # print(obj.title,obj.userinfo_set.all())
#     #releate name的方式
#     #ut = models.ForeignKey("UserType",null=True,related_name="users")
#     print(obj.title,obj.users.all())

# ORM用法大全
# 1.字段名过滤
# sql="SELECT * FROM test_userinfo WHERE id > 3"
# res = models.UserInfo.objects.filter(id__gt=3)
# sql="SELECT * FROM test_userinfo WHERE id >= 3"
# res = models.UserInfo.objects.filter(id__gte=3)
# sql="SELECT * FROM test_userinfo WHERE id < 3"
# res = models.UserInfo.objects.filter(id__lt=3)
# sql="SELECT * FROM test_userinfo WHERE id <= 3"
# res = models.UserInfo.objects.filter(id__lte=3)
# sql = "SELECT * FROM test_userinfo WHERE NOT (id = 4)"
# res = models.UserInfo.objects.exclude(id=4)
#sql = "SELECT * FROM test_userinfo WHERE id = 2 and name = 'zekai'"
# res = models.UserInfo.objects.filter(id=2,username="zekai")

# 2.in |not in
#sql = "SELECT * FROM test_userinfo WHERE  id IN(2, 4, 5)'"
# res = models.UserInfo.objects.filter(id__in=[2,4,5])
#sql = "SELECT * FROM test_userinfo WHERE  NOT id IN(2, 4, 5)'"
# res = models.UserInfo.objects.exclude(id__in=[1,2])

# 3.between...and...
#sql = "SELECT * FROM test_userinfo WHERE id BETWEEN 4 AND 8'"
# res = models.UserInfo.objects.filter(id__range=[4,8])

#4.like
## where username like "ze%"
# res = models.UserInfo.objects.filter(username__startswith="ze")
# res = models.UserInfo.objects.filter(username__istartswith="zekai")

## where username like "%kk"
# res = models.UserInfo.objects.filter(username__endswith="kkk")
# res = models.UserInfo.objects.filter(username__iendswith="jjj")

# where username like "%hh%"
# res = models.UserInfo.objects.filter(username__contains="hhh")
# res = models.UserInfo.objects.filter(username__icontains="hhh")

#可插入re查询
# res = models.UserInfo.objects.filter(username__regex="^zekai$")

#5.count
## select count(*) from userinfo where id>3
## select count(id) from userinfo where id>3
# models.UserInfo.objects.filter(id__gt=3).count()


#6.order by
# order by id desc,age asc;
#SELECT *` FROM `test_userinfo` ORDER BY `test_userinfo`.`id` DESC
# res = models.UserInfo.objects.all().order_by("-id","age")

#7. group by
## select id,sum(age) as s, username from userinfo group by username
from django.db.models import Count,Min,Max,Sum
# res = models.UserInfo.objects.values("id","username").annotate(s=Sum('age'))

## select id,sum(age) as s, username from userinfo group by username having S>50;
# res = models.UserInfo.objects.values("name").annotate(s=Sum("age")).filter(s__gt=50)

#8,limit
## limit 0,10分页
# res = models.UserInfo.objects.all()[1:4]

#9.last
# res = models.UserInfo.objects.last()
# print(res)
# 10.only
# res = models.UserInfo.objects.only("username")
# print(res)

#11.defer
# res = models.UserInfo.objects.defer("id")
# print(res)

#12. or
## Q
from django.db.models import Q
# res = models.UserInfo.objects.filter(Q(Q(id__gt=3)|Q(username='zekai')) & Q(age=23))

#13. F
from django.db.models import F
# models.UserInfo.objects.update(age=F('age')+1)

# 14.原生的sql 类似mysql
# from django.db import connection,connections
# cursor = connection.cursor() #cursor = connections['default'].cursor()
# cursor.execute("""select * from UserInfo where id=%s""")
# row = cursor.fetchone()
# print(row)

# print(res.query) #查看上述代码的sql语句
return HttpResponse("ok")