13-字段的 查询

# 过滤器查询 非过滤器查询  关系运算符  集合运算符  字符串操作  日期查询  原生sql

1. 基础查询

    # 1. all 过滤器 查询所有数据
    users = User.objects.all()

    # 2. 获取一条数据,从结果集中取一条
    data= users.first()

    # 3. filter 过滤器 约等于 where
    # select * from user where uid=5
    users = User.objects.filter(uid=5)

    # 过滤器可以串联调用
    # select * from user where uid>5
    users = User.objects.filter(uid__gt=5).filter(uid__lt=50)

    # 4. 排序
    # 按username升序排列
    users = User.objects.order_by('username')

    # 限制结果集-不支持负下标:切片,取前两条
    users = User.objects.order_by('uid')[:2]

    # 5. 指定字段: 得到的是[{},{}]
    users = User.objects.all().values("username")

    # 6. 去重 distinct: 需要先排序
    users = User.objects.all().values("password").order_by().distinct()[:10]
    # print(users)

    # 7. 反序
    users = User.objects.order_by('uid').reverse()

2. 非过滤器方法

    # 非过滤器方法
    # 1. get: 只能返回一条记录
    # 如果记录不存在: DoesNotExist:
    # 如果多于一条记录: MultipleObjectsReturned:
    # user=User.objects.get(uid__gt=1)
    user=User.objects.get(uid=10)

    # 2. first: 返回第一个模型对象
    user = User.objects.first()

    # 3. last: 最后一条记录
    user = User.objects.last()

    # 4. count: 结果集中的记录数: 必须是QuerySet才能调用couont
    user = User.objects.filter(uid__lt=10).count()

    # 5. exists: 判断结果集是否为空,返回True / False
    user = User.objects.all().exists()

3. 查询条件的写法

    # 1. 关系运算符
      '''
      >=  uid__gte=2      uid >=2
      >   uid__gt=2       uid>2
      <   uid__lt=2       uid<2
      <=  uid__lte=2      uid<=2
      ==  uid = 2         uid==2  判等
      :param request:
      :return:
      '''
    # filter中多个条件做逻辑与链接
    # data = User.objects.filter(uid__lt=20)
    data = User.objects.filter(uid__lt=20,uid__gte=10)

    # 2. in 集合运算
    data = User.objects.filter(uid__in=[9, 12, 50])

    # 3. is null 判断空
    data = User.objects.filter(sex__isnull=True)

    # 4. 字符串操作
    # startswith 以...开头
    data = User.objects.filter(username__startswith="张")

    # endswith 以...结尾
    data = User.objects.filter(username__endswith="1")

    # contains 包含
    data = User.objects.filter(username__contains="1")

    # regex 正则匹配
    data=User.objects.filter(username__regex=r'3$')

    # 5. 日期查询
    data = User.objects.filter(regtime__year=2023) # 年
    data = User.objects.filter(regtime='2023-03-11')  # 详细日期

    # 6. 统计查询
    from django.db.models import Max, Min, Avg, Sum, Count
    # select max(uid) from user
    uid = User.objects.aggregate(Max("uid")) # 查询最大的uid值

    # 7. 分组: 分组前先排序
    # select sex,count(uid) from user group by sex
    data = User.objects.values("sex").order_by().annotate(Count('uid'))
    # select sex,count(uid) from user group by sex having sex=1
    data = User.objects.values("sex").order_by().annotate(Count('uid')).filter(sex=1)

4. Q对象和F对象

# Q对象: 构造 逻辑或,逻辑非; &(and), |(or), ~(not)
   # 查询uid>30或者sex=1
    from django.db.models import Q, F
    data = User.objects.filter(Q(uid__gt=30) | Q(sex=1))  # |逻辑或
    data = User.objects.filter(~Q(uid__gt=30))  # ~逻辑非,不能处理null
  
# F对象: 用于比较表中两个字段
   data = User.objects.filter(uid=F("sex"))  # 把sex看成User的一个列名

5. 原生sql

# 1. 不带聚合函数
    # 原生sql: raw ,可以执行任何sql语句和user无关,
    data = User.objects.raw("select * from bb")

    tem = input("用户名:")
    # asdasd' or '1  {}:会发生sql注入
    users = User.objects.raw("select * from user where username='{}'".format(tem))

    # 防止sql注入,使用%号的占位符
    users = User.objects.raw("select * from user where username=%s", ["'sdsdsd' or '1'"])
  
# 2. 带聚合函数(自定义sql)
    # 创建.py文件
    from django.db import connection
    def query(sql, *args):
        # with 语句相当于cursor = connection.cursor() 和 cursor.close()
        with connection.cursor() as cursor:
            cursor.execute(sql, args)
            # return cursor.fetchall() # 返回查询结果

            columns = [col[0] for col in cursor.description]
            res = [dict(zip(columns, row)) for row in cursor.fetchall()]  # 返回列表[{},{}]
            return res

    
# 视图函数调用
     from App.tools import query
    # 不带参数
    data=query("select count(*) from user")
    # 带参数的
    data = query("select * from user where uid=%s", 10)
    print(data)