Oracle中的数据过滤方法

比眉伴天荒 2024-03-22 09:42 49阅读 0赞

**目录**

1、where子句操作符--比较运算符

2、逻辑运算符

2.1AND操作符

2.2OR操作符

2.3not

3、通配符过滤-模糊查询like

LIKE操作符

4、转义符的使用

Ⅰ.单引号转义 --双写

Ⅱ.q'\{\}'或者q'\[\]' --括号内所见即所得

Ⅲ.模糊查询中的转义(对%\_的转义)

--------------------

> **首先明白为什么要过滤数据？**

在我们生活工作的实际应用当中，遇到的数据通常是非常庞大的，有时候我们只需要特定的一部分数据，而不是查询出全部的数据。此时我们就需要想办法解决这个问题，通过筛选、加条件等限定查询的方法去更好实现。因此需要数据过滤，过滤数据就可以提高我们数据查询的效率，减少查询的时间和资源消耗。

Oracle数据过滤的方法主要有两种：

1.  WHERE子句过滤：通过在SELECT语句中使用WHERE子句来筛选出需要的数据。WHERE子句可以使用比较运算符、逻辑运算符和通配符等来过滤数据。
2.  子查询过滤：通过在SELECT语句中嵌入子查询来筛选出需要的数据。子查询可以在FROM子句、SELECT子句和WHERE子句中使用，可以使用比较运算符、逻辑运算符和通配符等来过滤数据。子查询可以嵌套多层，但要注意性能问题。

在这里先讲解第一种数据过滤的办法！！！

使用where子句要清楚限定查询的语法：

select 列...
    from 表
    where 条件

\--条件一般表示为 "字段 操作符 值"

\--条件可以包含的操作符

在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。

WHERE 子句在表名（FROM 子句）之后给出。

比如：

SELECT prod_name, prod_price 
    FROM Products 
    WHERE prod_price = 3.49;

分析：

这条语句从 products 表中检索两个列，但不返回所有行，只返回 prod\_price 值为 3.49 的行。

## 1、where子句操作符--比较运算符 ##

支持下列所有操作符:

![2b1029d51f8245aab54ad8cd00313602.png][]

比如：

--查询工资大于2000的员工姓名和工资
 select ename,sal
 from emp
 where sal>2000;
 
 --查询1981-1-1之前入职的员工信息
 select *
 from emp
 where hiredate<date'1981-1-1';
 
 --查询名字是KING的员工信息
 select *
 from emp
 where ename='KING';

**空 is null / is not null**

在Oracle数据库中，NULL表示缺少值或未知值。NULL不等于0或空字符串，它是一个特殊的值，表示该字段没有值或未知值。在Oracle中，NULL可以用于任何数据类型。NULL的处理方式有一些特殊规定。例如，任何与NULL进行比较的结果都是未知的，因为NULL本身就是未知的。如果我们需要判断一个字段是否为NULL，可以使用IS NULL或IS NOT NULL运算符。

比如：

--查询没有经理的员工信息
    select *
    from emp
    where mgr is null;

>  **注意：**
> 
> in 里面有null 不影响结果
> 
> not in 里有null 结果为空
> 
> not in 要注意去空

**同时呢对NULL有以下几种描述：**

·空不占存储，空不等于0，也不等于空格，也不全等于''--空字符串

·对一行值进行计算时,比如单列函数/四则运算,空和任何值运算都是空

·对一列值进行计算时,比如聚合函数/分析函数,空不参与计算

·可以用nvl/NVL2/COALESCE处理空值

nvl(列,0) nvl2(列,列,0) coalesce(列,0)

·排序时 空最大

·在greatest和least中 求最大 空最大 求最小 空最小

## 2、逻辑运算符 ##

可以使用组合where子句，所有 WHERE 子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，SQL 是允许给出多个 WHERE 子句条件。这些子句有两种使用方式，即以 AND 子句或 OR 子句的方式使用。

### 2.1AND操作符 ###

and 并且 两个条件都满足；

要通过不止一个列进行过滤，可以使用 AND 操作符给 WHERE 子句附加条件。

比如：

--查询工资大于2000并且是30部门的员工信息
    select *
    from emp
    where sal>2000 and deptno=30;

这条 SELECT 语句中的 WHERE 子句包含两个条件，用 AND关键字联结在一起。实际是可以增加多个过滤条件，每个条件间都要使用 AND 关键字。

### 2.2OR操作符 ###

or 或者 两个条件满足其一；

OR 操作符与 AND 操作符正好相反，它指示 DBMS 检索匹配任一条件的行。事实上，许多 DBMS 在 OR WHERE 子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

比如：

--查询工资大于2000或者是30部门的员工信息
    select *
    from emp
    where sal>2000 or deptno=30;

OR WHERE 子句中使用的关键字，用来表示检索匹配任一给定条件的行。

### 2.3not ###

not 非/取反 不满足;

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为 NOT 从不单独使用（它总是与其他操作符一起使用），所以它的语法与其他操作符有所不同。NOT 关键字可以用在要过滤的列前，而不仅是在其后。

> not 取反

and 取反是 or; or 取反是 and;

比如：

--查询工资不大于2000的员工信息
 select *
 from emp
 where sal<=2000;
 
 select *
 from emp
 where not sal>2000;

NOT WHERE 子句中用来否定其后条件的关键字。

## 3、通配符过滤-模糊查询like ##

> #### LIKE操作符 ####

通配符（wildcard）用来匹配值的一部分的特殊字符。搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件。

在Oracle中，模糊查询是指在查询数据时，可以使用通配符来匹配不完全准确的数据。通配符是一些特殊的字符，可以用来代替其他字符或字符串，以便更灵活地搜索数据。

Oracle中使用的通配符包括：

1. %：代表任意长度的字符，可以匹配0个或多个字符。

2. \_：代表一个字符，可以匹配任意一个字符。

使用通配符进行模糊查询可以帮助我们更快地查找数据，尤其是当我们只知道部分信息或者不确定具体的值时。例如，我们可以使用以下语句查询所有以字母“a”开头的员工姓名：

SELECT * FROM employees WHERE name LIKE 'a%';

这条语句将返回所有名字以字母“a”开头的员工记录。如果我们不确定员工名字的具体拼写，可以使用以下语句进行模糊查询：

SELECT * FROM employees WHERE name LIKE '%smi%';

这条语句将返回所有名字中包含“smi”的员工记录，例如“Smith”、“Williams”等。

比如：

--查询名字是四个字符的员工信息
    select *
    from emp
    where ename like '____';
    
    --查询员工姓名J开头S结尾的员工信息
    select *
    from emp
    where ename like 'J%S';
    
    --查询J开头名字总长度为五的员工信息
    select *
    from emp
    where ename like 'J____';
    
    --查询员工姓名不包含A也不包含S的员工信息
    select *
    from emp
    where ename not like '%A%' and ename not like '%S%';

> 注：使用通配符的技巧
> 
>  不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
> 
>  在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
> 
>  仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

***另外我们需要特别注意：***

## **4、转义符的使用** ##

如果我们需要在模糊查询中使用通配符本身，而不是作为通配符进行匹配，就需要使用转义字符。转义字符是一个特殊的字符，在通配符前面加上转义字符就可以将其转义为普通字符。在Oracle中，使用反斜杠“\\”作为转义字符。下面就具体举例几种情况：

### Ⅰ.单引号转义 --双写 ###

select '''''abc'''''
    from dual;

### Ⅱ.q'\{\}'或者q'\[\]' --括号内所见即所得 ###

select q'{'}'
    from dual;

比如：

--在万能表中查询 A said :"B said 'let's go!'" (两种方法)
    select q'{A said :"B said 'let's go!'"}'
    from dual;
    
    select 'A said :"B said ''let''s go!''"'
    from dual;

### Ⅲ.模糊查询中的转义(对%\_的转义) ###

escape

语法

select 列

from 表

where 列 like '匹配的内容' escape '转义符'(自定义一般用\\);

注：一个转义符只能转义一个字符

比如：

--查询A表中name包含_的行
    select *
    from A
    where name like '%\_%' escape '\';
    
    --查询A表中name列 % 开头的行
    select *
    from A
    where name like '\%%' escape '\';
    
    --查询A表中name列 %_ 开头的行
    select *
    from A
    where name like '\%\_%' escape '\';

使用转义字符的目的是避免通配符被误解为普通字符，从而导致查询结果不准确。在实际应用中，如果我们需要在模糊查询中使用通配符本身，就需要注意使用转义字符进行转义。

除了以上讲述where子句三大类型的数据过滤方法外，我们实际的限定查询where子句还有以下的几种情况！

Ⅰ.between 小值 and 大值

包含两端值 相当于 >=小值 and <=大值

比如：

--查询1987年上半年入职的员工信息
    select *
    from emp
    where hiredate between date'1987-1-1' and date'1987-6-30';
    
    --查询工资不在500-1500，也不再3000-5000的员工信息
    select *
    from emp
    where not(sal between 500 and 1500 or sal between 3000 and 5000);
    
    select *
    from emp
    where not sal between 500 and 1500 and sal not between 3000 and 5000;

Ⅱ.in (值1,值2...)

相当于 =值1 or =值2 or ...

比如：

--查询职位不是MANAGER,CLERK的员工信息
    select *
    from emp
    where job not in ('MANAGER','CLERK');

Ⅲ.算数运算符 + - \* / ()

比如;

--查询员工编号>(员工编号-5000之后乘以工资的五百分之一)的员工信息
    select *
    from emp
    where empno > (empno-5000)*sal/500;

Ⅳ.连接符 ||

多个字段连接成一个字段，这个新的字段为字符型。连接符（||）用于将两个字符串连接成一个字符串。连接符常用于在查询语句中将多个列或常量连接成一个字符串列。

比如：

--查询20部门奖金为空的员工信息
    select deptno||comm,deptno,comm
    from emp
    where deptno||comm=20;

***最后对where子句中的操作符优先级做个总结！！！***

<table style="width:500px;"> 操作符优先级总结 
 <tbody> 
 <tr> 
 <td> * / 优先于 + - ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 同级从左到右依次执行 </td> 
 </tr> 
 <tr> 
 <td>and 和 or 的优先级：and 优先于 or</td> 
 </tr> 
 <tr> 
 <td> ()、*/、+-||、比较符、IS NULL IN LIKE 、BETWEEN AND 、NOT、and、or 迷糊了用小括号改变优先级 </td> 
 </tr> 
 <tr> 
 <td> 算数运算符{ + - * / () }和连接符{ ||}的优先级最高 同级从左到右 or的优先级最低 </td> 
 </tr> 
 <tr> 
 <td> 列举优先级顺序 1.() 2.乘除 3.加减,连接符 4.比较符 5.is null，in 6.between and 7.not 8.and 9.or </td> 
 </tr> 
 </tbody> 
</table>

到此，数据过滤的第一种方法大致就描述完了，我也会尽快总结讲述第二种的过滤方法。如果本篇有什么错误或遗漏希望可以在评论区留言，特别感谢您的查看！！！

[2b1029d51f8245aab54ad8cd00313602.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/d688160b94aa4a59bbe18df2df8c51d1.png