【postgresql 基础入门】带过滤条件的查询,where子句中的操作符介绍,案例展示,索引失效的大坑就在这里

【postgresql 基础入门】带过滤条件的查询,where子句中的操作符介绍,案例展示,索引失效的大坑就在这里

查询数据-过滤数据

专栏内容

开源贡献

  • toadb开源库

个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.

系列文章

  • 入门准备
  • postgrersql基础架构
  • 快速使用
  • 初始化集群
  • 数据库服务管理
  • psql客户端使用
  • pgAdmin图形化客户端
  • 数据库的使用
  • 创建数据库
  • 数据库操作
  • 表的使用
  • 表的创建
  • 表的操作
  • 数据查询
  • 数据查询
  • 多表联合查询
  • 数据操作
  • 插入数据的方式

前言


postgresql 数据库是一款通用的关系型数据,在开源数据库中能与商业数据媲美,在业界也越来越流行。

因为是开源数据库,不仅公开源码,还有很多使用案例,好用的插件,所以它的慢慢变成了数据库的先驱和标准,通过postgresql可以很好从使用到原理,彻底搞懂;

如果是学习编程,也可以学到丰富的编程知识,数据结构,编程技巧,它里面还有很多精妙的架构设计,分层思想,可以灵活定制的思想。

本专栏主要介绍postgresql 入门使用,数据库维护管理,通过这些使用来了解数据库原理,慢慢了解postgresql是什么样的数据库,能做那些事情,以及如何做好服务,最关键的是这些知识都是面试的必备项。

概述


在使用SQL查询数据时,一般不会查询全部数据,而是使用条件或者很多条件的组合来缩小结果集的范围,甚至精准查询到想要的数据。本文将介绍postgresql数据库中如何筛选过滤数据,如何设置条件,以及有那些操作符可以使用。

where 子句介绍


一般的查询SQL结构如下

SELECT ... FROM ... WHERE ... ORDER BY... ; 

一般把每个关键字,如SELECT,叫做一个子句,如select子句,from 子句,where子句,orderby子句等等,如果要对数据行进行过滤和筛选,就要用到where子句。

在postgresql 中,子句的执行顺序是
from子句->where子句->select子句-> orderby子句

按照实际执行动作,就是先扫描表,然后按条件进行过滤,再进行投影运算,最后对结果集进行排序。

这就会存在select子句中的列的别名,在where子句中是不能使用的,相反from子句和where子句中表的别名,在select子句中是可以使用的。

where子句,它的内容是一个个条件表达式,最终计算结果是一个布尔值,也就是’true’,'false’或者不确定;

也就是说当条件值为’true’时,数据表中的行才会被选入结果集当中,当不会真时就会被过滤掉。

操作符


在条件表达式中,要使用一些操作符,比如比较运算符,逻辑运算符等,才能组成表达式,我们常用的操作符有以下:

  • 比较运算符
运算符 描述
= 相等
<>或者!= 不相等
> 大于
>= 大于等于
< 小于
<= 小于等于

比较运算符有些类似于其它编程语言中的比较运算符,用于两个列、列与常量或者常量与常量的比较。

  • 逻辑运算符
运算符 描述
AND 逻辑与
OR 逻辑或
NOT 逻辑非,取反
  • 集合操作
运算符 描述
BETWEEN ... AND ... 在 AND前后指定的范围内则返回true
IN 在指定的集合中时返回true
  • 其它运算符
运算符 描述
LIKE 模式配匹,通配符有%,_
IS NULL 值为空时返回true

案例实践


下面我们一起来实践一下吧,先准备数据。

-- create table 
create table student(sid int primary key,sname varchar,sage int,ssex char,ctime timestamp);

-- insert data
insert into student values(1,'lilei',18,'m','2023/9/20'),(2,'liming',19,'m','2022/8/20'),(3,'zhanghua',20,'f','2021/8/29'),(4,'guodong',21,'f','2020/10/1');

再创建索引,用于后面案例分析

create index idx_sname ON student (sname);
create index idx_ssex on student (ssex );

查询一下所有数据

postgres=# select * from student;
 sid |  sname   | sage | ssex |        ctime
-----+----------+------+------+---------------------
   1 | lilei    |   18 | m    | 2023-09-20 00:00:00
   2 | liming   |   19 | m    | 2022-08-20 00:00:00
   3 | zhanghua |   20 | f    | 2021-08-29 00:00:00
   4 | guodong  |   21 | f    | 2020-10-01 00:00:00
(4 rows)

字符串匹配


先来看一下字符串的条件,因为它常常会用到,但是也常常会出现问题。

在我们对字符串进行模糊查找时,会用到like 和通配符的方式,通配符在postgresql中有两个:

  • 百分号 %, 匹配任意字符和长度
  • 下划线 _, 匹配任意的一个字符

如果字符串中包括以上通配符,需要使用转义字符’'进行转义;

查找li开头的字符串,如下:

postgres=# select * from student where sname like 'li%';
 sid | sname  | sage | ssex |        ctime
-----+--------+------+------+---------------------
   1 | lilei  |   18 | m    | 2023-09-20 00:00:00
   2 | liming |   19 | m    | 2022-08-20 00:00:00
(2 rows)

查找li开头的字符串,并且字符长度确定时,如下:

postgres=# select * from student where sname like 'li_';
 sid | sname | sage | ssex | ctime
-----+-------+------+------+-------
(0 rows)

postgres=# select * from student where sname like 'li___';
 sid | sname | sage | ssex |        ctime
-----+-------+------+------+---------------------
   1 | lilei |   18 | m    | 2023-09-20 00:00:00
(1 row)

注意:虽然模糊匹配非常好用,但是我们还是要尽量减少使用,因为它会让该列上的索引失效,如下所示:

postgres=# explain select * from student where sname like 'li___';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on student  (cost=0.00..121.05 rows=1 width=56)
   Filter: ((sname)::text ~~ 'li___'::text)
(2 rows)

postgres=# explain select * from student where sname like 'lilei';
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using idx_sname on student  (cost=0.13..8.15 rows=1 width=56)
   Index Cond: ((sname)::text = 'lilei'::text)
   Filter: ((sname)::text ~~ 'lilei'::text)
(3 rows)

当我们使用了通配符之后,查询计划变成了Seq scan,也就是顺序扫描,而不使用通配符时采用的是index scan也就是索引扫描。

在一些字符串作为键值的业务逻辑当中,如果发现业务性能下降时,可以按此方法进行排查,是否存在索引失效的问题。

范围比较


查找sage在17到20之间的数据,这个范围可以有两种写法:
一种使用比较运算符和逻辑运算符;

postgres=# select * from student where sage >= 17 and sage <=20;
 sid |  sname   | sage | ssex |        ctime
-----+----------+------+------+---------------------
   1 | lilei    |   18 | m    | 2023-09-20 00:00:00
   2 | liming   |   19 | m    | 2022-08-20 00:00:00
   3 | zhanghua |   20 | f    | 2021-08-29 00:00:00
(3 rows)

另一种使用between and运算符;

postgres=# select * from student where sage between 17 and 20;
 sid |  sname   | sage | ssex |        ctime
-----+----------+------+------+---------------------
   1 | lilei    |   18 | m    | 2023-09-20 00:00:00
   2 | liming   |   19 | m    | 2022-08-20 00:00:00
   3 | zhanghua |   20 | f    | 2021-08-29 00:00:00
(3 rows)

可以将多个条件使用 逻辑运算符连接起来,形成多条件过滤。

总结


通过where子句中的条件可以达到过滤查询的效果,其中使用不同的运算符组合可以形成多个条件表达式,同时在使用过程中避免索引失效的情况,尤其在大数据量下时,索引失效可能是灾难级的。

结尾

非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!

作者邮箱:study@senllang.onaliyun.***
如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!

转载请说明出处内容投诉
CSS教程_站长资源网 » 【postgresql 基础入门】带过滤条件的查询,where子句中的操作符介绍,案例展示,索引失效的大坑就在这里

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买