数据库数据查询

星哥玩云

发布于 2022-09-14 17:26:52

8400

发布于 2022-09-14 17:26:52

文章被收录于专栏：开源部署

一、数据查询名词解析

查询集从数据库中获取数据的集合
原始查询集调用get_queryset得到的查询集称为原始查询集，不作为数据的提取
数据查询集在原始查询集上或数据查询集上调用过滤器得到数据查询集
过滤器基于过滤器函数的参数进行条件过滤 SQL的角度来说，查询集等价于select，过滤器等价于where、limit子句过滤器就是一个函数根据所给的参数限制返回的查询集可以使用链式语法规则（User.objects.all().filter().filter()）
惰性执行
- 创建查询集不会操作数据库，直到调用数据时才会与数据库进行交互
- 访问数据库与if合用迭代列化求值

二、返回查询集的过滤器

all()

作用：获取全部数据

格式：类名.objects.all()

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">allView</span><span class="hljs-params">(req)</span>:</span>
  u = User.objects.all()
  print(u) 
  <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

filter()

作用：过滤掉符合条件的数据

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">filterView</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.filter(sex=<span class="hljs-keyword">True</span>,username__contains=<span class="hljs-string">'1'</span>)
    u = User.objects.filter(sex=<span class="hljs-keyword">True</span>).filter(username__contains=<span class="hljs-string">'1'</span>)
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

exclude()

作用：过滤掉符合条件的数据

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">excludeView</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.exclude(sex=<span class="hljs-keyword">True</span>,username__contains=<span class="hljs-string">'l'</span>)
    u = User.objects.exclude(sex=<span class="hljs-keyword">True</span>).exclude(username__contains=<span class="hljs-string">'1'</span>)
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

order_by()

作用：根据字段进行排序

格式

order_by(‘field’) 升序
order_by(’-field’) 降序

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">orderByView</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.filter().order_by(<span class="hljs-string">'-id'</span>)
    u = User.objects.order_by(<span class="hljs-string">'-id'</span>)
    u = User.objects.order_by(<span class="hljs-string">'id'</span>)
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

values()

作用：返回一个列表每条数据是一个字典

格式

values() 返回所有字段和值
values(field1,field2…) 只返回给定字段的值

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">valuesView</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.values() <span class="hljs-comment">#默认返回所有</span>
    u = User.objects.values(<span class="hljs-string">'id'</span>,<span class="hljs-string">'age'</span>) <span class="hljs-comment">#只返回id和age字段的值</span>
    print(u) <span class="hljs-comment">#[{'username':'张三','age':18}]</span>
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u}

extra()

作用：实现别名操作

格式

extra()
extra(select={new:ord})

res = User.objects.all().extra(select={<span class="hljs-string">'name'</span>:<span class="hljs-string">'username'</span>})  <span class="hljs-comment"># 将username字段起别名为 name</span>
print(res[<span class="hljs-number">0</span>].name)
print(res[<span class="hljs-number">0</span>].username)

三、返回单个值的过滤器

get()

作用：返回单个满足条件的对象

格式：类名.objects.get(属性=值)

注意

如果未找到会引发“模型类.DoesNotExist”异常
如果多条被返回，会引发“模型类.MultipleObjectsReturned”异常

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">myGet</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.get(pk=<span class="hljs-number">1</span>)
    print(u)
    u = User.objects.get(id=<span class="hljs-number">1</span>)
    print(u)
    <span class="hljs-comment">#报错 原因：只能返回一个值 但是匹配到了多个 MultipleObjectsReturned</span>
    u = User.objects.get(age=<span class="hljs-number">43</span>) 
    <span class="hljs-comment">#报错 DoesNotExist 匹配失败</span>
    u = User.objects.get(id=<span class="hljs-number">20</span>) 
    print(u)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'get'</span>)

count()

作用：返回当前查询集中的数据条数

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">countView</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment"># 查询数据所有条数</span>
    u = User.objects.count()  
    <span class="hljs-comment"># 筛选性别为True的数据条数</span>
    u = User.objects.filter(sex=<span class="hljs-keyword">True</span>).count()  
    print(u)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'返回数据的条数'</span>)

first()

作用：返回查询集中的第一个对象

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">firstView</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment">#第一条</span>
    u = User.objects.order_by(<span class="hljs-string">'id'</span>).first() 
    print(u)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'first'</span>)

last()

作用：返回查询集中的最后一个对象

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">lastView</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment">#最后一条</span>
    u = User.objects.order_by(<span class="hljs-string">'id'</span>).last() 
    print(u)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'last'</span>)

exists()

作用：判断筛选条件结果是否存在

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">existsView</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment"># 查询年龄为400的数据是否存在</span>
    u = User.objects.filter(age=<span class="hljs-number">400</span>).exists()  
    print(u)
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'exists'</span>)

四、字段查询

概述

实现where子句，作为filter、exclude、get等参数

语法

属性名__比较运算符=值_
对于外键需要使用“外加属性_id”表示外键
%的使用 like语句中使用了%，匹配数据中的%与，在过滤器中可以直接使用% filter(title__contains=’%’) where title like ‘%%%’

比较运算符

exact

作用：表示判等，大小写敏感，如果“比较运算符”没有写，就表示判等

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">exactView</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.filter(username__exact=<span class="hljs-string">'LUCKY'</span>)
    u = User.objects.filter(username__exact=<span class="hljs-string">'lucky'</span>)
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

contains

作用：包含，大小写敏感

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">containsView</span><span class="hljs-params">(req)</span>:</span>
    u = User.userobj.filter(username__contains=<span class="hljs-string">'LUCKY'</span>)
    u = User.userobj.filter(username__contains=<span class="hljs-string">'lucky'</span>)
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

startswith、endswith

作用：以value开头或结尾，大小写敏感

<span class="hljs-comment">#以...作为开头和结尾</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">startendView</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.filter(username__startswith=<span class="hljs-string">'a'</span>)
    u = User.objects.filter(username__startswith=<span class="hljs-string">'A'</span>)
    u = User.objects.filter(username__endswith=<span class="hljs-string">'a'</span>)
    u = User.objects.filter(username__endswith=<span class="hljs-string">'A'</span>)
    u = User.objects.filter(username__startswith=<span class="hljs-string">'5'</span>,username__endswith=<span class="hljs-string">'5'</span>)
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

iexact、icontains、istartswith、iendswith

作用：前面加个i，表示不区分大小写

null

作用：查询数据是否为空

<span class="hljs-comment">#查询为空的</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">null</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment">#查询为null的数据</span>
    u = User.objects.filter(username__isnull=<span class="hljs-keyword">True</span>)
    u = User.objects.filter(username=<span class="hljs-keyword">None</span>)
    <span class="hljs-comment">#查询不为null的数据</span>
    u = User.objects.exclude(username__isnull=<span class="hljs-keyword">True</span>)
    u = User.objects.filter(username__isnull=<span class="hljs-keyword">False</span>)
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

作用：是否包含范围内

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">In</span><span class="hljs-params">(req)</span>:</span>
    <span class="hljs-comment">#在...范围内</span>
    u = User.objects.filter(id__in=[<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>,<span class="hljs-number">4</span>])  <span class="hljs-comment"># 查询id为 1 2 3 4的数据</span>
    u = User.objects.filter(pk__in=[<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>,<span class="hljs-number">4</span>])  <span class="hljs-comment"># 查询主键为 1 2 3 4的数据</span>
    <span class="hljs-comment">#不在...范围内</span>
    u = User.objects.exclude(pk__in=[<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>,<span class="hljs-number">4</span>])  <span class="hljs-comment"># 查询主键不为 1 2 3 4的数据</span>
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:u})

比较运算符

__gt 大于
__gte 大于等于
__lt 小于
__lte 小于等于

示例

<span class="hljs-comment">#比较运算符</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">compare</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.filter(id__gt=<span class="hljs-number">2</span>)  <span class="hljs-comment"># 查询id大于2的数据</span>
    u = User.objects.filter(id__gte=<span class="hljs-number">2</span>)  <span class="hljs-comment"># 查询id大于等于2的数据</span>
    u = User.objects.filter(id__lt=<span class="hljs-number">5</span>)  <span class="hljs-comment"># 查询id小于5的数据</span>
    u = User.objects.filter(id__lte=<span class="hljs-number">5</span>)  <span class="hljs-comment"># 查询id小于等于5的数据</span>
    u = User.objects.filter(id__exact=<span class="hljs-number">5</span>)  <span class="hljs-comment"># 查询id等于的数据</span>
    u = User.objects.exclude(id__exact=<span class="hljs-number">5</span>)  <span class="hljs-comment"># 查询id不等于的数据</span>
    <span class="hljs-keyword">return</span> render(req,<span class="hljs-string">'show.html'</span>,{<span class="hljs-string">'data'</span>:a})

五、聚合函数

导入

from django.db.models import Avg,Max,Min,Sum,Count

aggregate()

返回聚合的值

方法

Avg 平均数
Max 最大
Min 最小
Sum 求和
Count 统计

示例

<span class="hljs-keyword">from</span> django.db.models <span class="hljs-keyword">import</span> Avg,Count,Sum,Max,Min
<span class="hljs-comment">#聚合函数</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">jhView</span><span class="hljs-params">(req)</span>:</span>
    u = User.objects.aggregate(Avg(<span class="hljs-string">'age'</span>))  <span class="hljs-comment"># 求平均年龄</span>
    u = User.objects.aggregate(Sum(<span class="hljs-string">'age'</span>))  <span class="hljs-comment"># 求年龄总和</span>
    u = User.objects.aggregate(Max(<span class="hljs-string">'age'</span>))  <span class="hljs-comment"># 求最大年龄</span>
    u = User.objects.aggregate(Min(<span class="hljs-string">'age'</span>))  <span class="hljs-comment"># 求最小年龄</span>
    u = User.objects.aggregate(Count(<span class="hljs-string">'age'</span>))  <span class="hljs-comment"># 求年龄数据条数</span>
    <span class="hljs-keyword">return</span> HttpResponse(<span class="hljs-string">'值为{}'</span>.format(u))

六、限制查询机制

原理

查询集是一个列表，可以使用下标的方式进行限制，等同于mysql中的limit和offset子句

注意

下标不能为负数

格式

[下标]
[头下标:尾下标]
[:尾下标]
[头下标:]
[::步长]

示例

u = User.objects.all()[<span class="hljs-number">0</span>]  <span class="hljs-comment"># 获取索引为0的数据</span>
u = User.objects.all()[:<span class="hljs-number">2</span>]  <span class="hljs-comment"># 获取前俩条数据</span>
u = User.objects.all()[<span class="hljs-number">0</span>:]  <span class="hljs-comment"># 获取所有数据</span>
u = User.objects.all()[::<span class="hljs-number">2</span>]  <span class="hljs-comment"># 获取步长为2数据</span>

异常