Oracle中的特殊判式

Oracle中的特殊判式

除了逻辑运算之外，Oracle提供了一些特殊判式。这些判式可以用来生成更加复杂和灵活的查询条件。本节将着重介绍以下几种判式。

Between: 取值范围

In: 集合成员测试

Like: 模式匹配

Is null: 空值判断

All,somne,any:数量判断。

Exists：存在性判断

 

between——范围测试

between判式，用于判断某个值是否在另外两个值之间。这些值可以为数值型、字符串和日期型。使用betwwen判式来获得ID号在1-5之间的员工信息。

select * from t_employees where employee_id between 1 and 5;

betwwen判式同样可以应用于字符串和日期型。字符串是按照字母表的顺序进行比较，而日期型是按照日期的先后顺序进行比较。

select * from t_employees where 'b' between 'b' and 'c';

select * from t_employees where 'b' between 'bc' and 'c';

注意与说明：between判式与>=、<=的组合是等价关系。但是，效率上要比后者差。

 

 

in——集合成员测试

in用于判断某个值是否一个集合的成员。

select * from t_employees where status in('NEW', 'ACT');

值得注意的是，in判式中的集合的成员的数据类型可以不一致，例如，select * from t_employees where status in('NEW', 'ACT', sysdate, 1)中的数据类型包含了字符串、日期型和数值型。

 

 

like——模式匹配

like判式的最大特点在于，可以使用通配符。其通常的应用场景为处理模糊查询。

select * from t_employees where employee_name like '钟%';

如果要求字符串中含有原义字符"%"，例如，含有百分比的字符串。那么，like判式应写作：like '钟\%' escape '\'。Oracle会首先解释escape关键字，并将其后的字符"\"解释为转义字符。那么在"钟\%"中的"%"不再表示通配符，而是表示原义字符"%"。

"_"（下划线）是可用于like判式的另一个通配符，该通配符表示一个任意的字符。

 

 

is null——空值判断

在逻辑判断中，对于列值为空的判断，不能使用=或者<>。oracle对与空值的判断提供了专门的判式——is null。例如，为了获取表t_employees中员工信息不全的记录，可以利用如下所示的查询语句。

select * from t_employees

where employee_id is null

or employee_name is null

or work_years is null

or status is null;

 

 

exists——存在性判断

in判式用于判断表的列值是否存在于列表（集合）中。而exists判式则可用于判断查询结果集合是否为空。例如，为了查询出表t_employees所存储的员工信息中，哪些员工存在于工资表中，即可利用exists判式。

select * from t_employees e

where exists(select * from t_salary where employee_id = e.employee_id);

 

 

all，some，any——数量判断

all，some和any判式的作用对象为记录集合。all表示，记录集中的所有记录，some表示其中的一些记录，any判式则表示其中的任意记录。例如，在员工工资表t_salary中，为了查找高于id为4和5的工资信息，即可使用all判式。

select * from t_salary where employee_id = 4 or employee_id = 5;

select * from t_salary where salary > all(select distinct salary from t_salary where employee_id = 4 or employee_id = 5);

select * from t_salary where salary > some(select distinct salary from t_salary where employee_id = 4 or employee_id = 5);

此时的some判式实际相当于逻辑运算中的or运算，即salary>6000 or salary>7000。此时，使用any判式，将返回同样的结果。

 

 

Some,any,all的用法和区别

select ename,sal
From emp
Where sal > any(select sal from emp where deptno = 10);
从emp表中选出大于部门10中最小工资的的人的姓名和工资。
小结：any是大于最小，小于最大。（当然此处使用some也可以，但any语义更为直接）

 

 

select ename,sal
From emp
Where sal = some(select sal from emp where deptno = 30) and deptno not in (select deptno from emp where deptno = 30);
从emp表中选出不是30号部门，但工资和30号部门任何一个人的工资相等的人的姓名和工资。
小结：等于任意一个。

 

select ename,sal
From emp
Where sal > all(select sal from emp where deptno = 20);
从emp表中选出工资大于20号部门最大工资的人的姓名及工资。
小结：大于最大，小于最小。