查询数据-过滤数据

​专栏内容：

• postgresql内核源码分析
• 手写数据库toadb
• 并发编程

  ​开源贡献：

  • toadb开源库

    个人主页：我的主页

    管理社区：开源数据库

    座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

系列文章

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

  文章目录

  • 查询数据-过滤数据
  • 系列文章
  • 前言
  • 概述
  • where 子句介绍
  • 操作符
  • 案例实践
  • • 字符串匹配
    • 范围比较
    • 总结
    • 结尾

      前言

      ---

      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.com

                如有错误或者疏漏欢迎指出，互相学习。

                注：未经同意，不得转载！

                 
                 网站设计专业网站建设公司  销售网站  建设企业网站的重要性  建设网站的企业  国外平面设计网站  恩施网