Django-orm数据库查询语句

Django-orm数据库查询语句

    #### orm查询用法大全

    ### 数据库表
    class UserInfo(models.Model):
        ### 主键自增id不用写， 默认会加上
        name = models.CharField(max_length=32, null=True)
        age = models.IntegerField(null=True)
        ut = models.ForeignKey("UserType", null=True, related_name='users')
    
    ### 1. 字段名过滤
    # res = models.UserInfo.objects.filter(name='zekai') ### where name='zekai'
    res = models.UserInfo.objects.filter(id__gt=3)
    res = models.UserInfo.objects.filter(id__gte=3)
    res = models.UserInfo.objects.filter(id__lte=3)
    res = models.UserInfo.objects.filter(id__lt=3)
    res = models.UserInfo.objects.exclude(id=4)

    res = models.UserInfo.objects.filter(id=2, name='zekai') ### where id=2 and name='zekai'
    ### 缺一个 or

    ####2. in not in
    res = models.UserInfo.objects.filter(id__in=[2,4,5]) ## where id in (2,4,5)
    res = models.UserInfo.objects.exclude(id__in=[1,2]) ### where id not in (1,2)

    ####3. between...and
    ### where id between 4 and 8   [4,8]
    res = models.UserInfo.objects.filter(id__range=[4,8])

    ####4. like
    ##### where name like 'like%'
    ##### g:全局 global  i: ignore (忽略大小写)
    res = models.UserInfo.objects.filter(name__startswith="ze")
    res = models.UserInfo.objects.filter(name__istartswith="zekai")

    ##### where name like '%kk'
    res = models.UserInfo.objects.filter(name__endswith="kkk")
    res = models.UserInfo.objects.filter(name__iendswith="jjj")

    ##### where name like '%hhh%'
    res = models.UserInfo.objects.filter(name__contains='hhh')
    res = models.UserInfo.objects.filter(name__icontains='ggg')

    res = models.UserInfo.objects.filter(name__regex="^zekai$")


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

    ### 6.order by
    ##### order by id desc, age asc;
    #### - :降序
    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("name").annotate(s=Sum('age'))
    print(res.query)

    ### 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]
    # print(res)


    ##### 9. last
    res = models.UserInfo.objects.last()

    ##### 10. only
    res = models.UserInfo.objects.only('name')
    print(res)

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


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

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


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


    #### 15.distinct
    # models.UserInfo.objects.values("name", 'age').distinct('name')

    # print(res.query)  ### 查看上述代码生成的sql语句