02、Oracle过滤和排序数据_oracle 过滤器-优快云博客

本文链接：https://blog.youkuaiyun.com/omaidb/article/details/130083689

Oracle过滤和排序数据

1.1 【where 子句基本语法】
1.2 【字符串和日期】
1.3 【比较操作符】
1.4 【逻辑运算符】
1.5 【优先级规则】
1.6 【order by 子句：排序】
1.7 【升序 asc】
1.8 【降序 desc】
1.9 【按照列的别名排序】
1.10 【按照列序号排序：位置排序】
1.11 【多个列排序：混合排序】
1.12 【排序中的 null 值】
1.13 【order by 的部分限制】

1.1 【where 子句基本语法】

where 子句中的条件有许多形式，只有表中符合这些条件的行才会返回。

where 子句中不可以使用列的别名。
如果条件列是数字型的，可以直接写；
如果条件列是字符型的，需要使用单引号；
如果条件列是日期型的，要注意日期格式，单引号。
需求：查询 10号部门的员工姓名

-- 查询 10 号部门的员工姓名  
SCOTT@orcl>select deptno,ename from emp where deptno=10;

1.2 【字符串和日期】

字符串和日期要用单引号引起来。
字符串大小写敏感。（区分大小写）

SCOTT@orcl>select * from emp where job='clerk';

SCOTT@orcl>select * from emp where job='CLERK';

日期值对于日期的格式敏感。
缺省的日期值格式： DD-MON-RR需求：查询入职日期是 1980-12-17 的员工信息 .

【补充知识】转换函数 to_date （备注： hiredate 是 date 类型）

SCOTT@orcl>select * from emp where hiredate='17-DEC-80';

在这里插入图片描述

SCOTT@orcl>select * from emp where hiredate=to_date('
1980-12-17','yyyy-mm-dd');

1.2.1 【数据库默认日期格式】

注意用户为sysdba

SYS@orcl>select property_name,property_value
  2  from database_properties
  3  where property_name='NLS_DATE_FORMAT';

1.2.2 【当前会话默认日期格式】

注意用户为**scott**

SCOTT@orcl>select * from v$nls_parameters
  2  where parameter='NLS_DATE_FORMAT';

1.2.3 【修改当前会话日期格式】

SCOTT@orcl>alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';
SCOTT@orcl>select sysdate from dual;

退出 sqlplus 重新登录后恢复默认

1.3 【比较操作符】

SCOTT@orcl>select * from emp where deptno = 10;
SCOTT@orcl>select * from emp where deptno <>10;
SCOTT@orcl>select * from emp where deptno!=10;

1.3.1 【空值的不等于比较】

【注意】包含空值的列进行比较，要对空值进行处理。
否则进行比较空值默认为无限大
需求：查询工资大于奖金的员工

-- 查询工资大于奖金的员工 
SCOTT@orcl>select * from emp where sal>comm;

SCOTT@orcl>select * from emp where sal>nvl(comm,0);

1.3.2 【where 子句中使用 rownum 伪列】

因为 ROWNUM 是对结果集加的一个伪列：即先查到结果集之后再加上去的一个列
(说明：先要有结果集)。通俗的说 rownum 是对符合条件结果的序列号。它总是从 1 开始排起的。
所以你选出的结果不可能没有 1，而有其他大于 1 的值。where 子句中使用 rownum 伪列进行限定时，使用<或<=，使用>不返回行。

SCOTT@orcl>select rownum,deptno,dname from dept; 
SCOTT@orcl>select rownum,deptno,dname from dept where rownum=2;
SCOTT@orcl>select rownum,deptno,dname from dept where rownum>2;
SCOTT@orcl>select rownum,deptno,dname from dept where rownum<=2;

rownum< 和 and 并用时，是在另一个条件 and 基础上的 rownum<
也就是说先执行 and 后面的条件，返回满足条件的数据，然后再进行 rownum<

SCOTT@orcl>select rownum,ename,deptno from emp;

SCOTT@orcl>select rownum,ename,deptno from emp where rownum<2;

SCOTT@orcl>select rownum,ename,deptno from emp where deptno=10;

SCOTT@orcl>select rownum,ename,deptno from emp where rownum<2
  2  and deptno=10;

1.3.3 【范围】 (between and)

需求：查询工资在 **800~1000 **范围内的员工。

-- 查询工资在 800~1000 范围内的员工。 
SCOTT@orcl>select * from emp where sal between 800 and 1000;
SCOTT@orcl>select * from emp where sal between 1000 and 800;

【注意】 **between and **同时包括上下限边界，小值写在前；否则不返回结果。

**between and **也可以处理字符、日期类型。
between ‘a’ and ‘c’ 时，包含’c’ 不包含’ca’、 **‘cb’**等等以 **c **字母开头的。【注意】日期格式，否则报错

SCOTT@orcl>select * from emp where hiredate between '09-08-21' and '01-02-14';

SCOTT@orcl>select * from emp where hiredate between '09-JUN-81' and '23-JAN-82'

SCOTT@orcl>select * from emp where ename between 'A' and 'C';

1.3.4 【列举 in】

可以使用 IN 操作符来检查一个值是否在值的列表中。

SCOTT@orcl>select empno,ename,sal,mgr from
  2  emp where mgr in(7902,7566,7788);

SCOTT@orcl>select empno,ename,sal,mgr from emp
  2  where mgr=7902 or mgr=7566 or mgr=7788;

下面查询使用 NOT IN 检索那些未被 IN 检索出来的行：

SCOTT@orcl>select * from emp where comm in(300,500); 
SCOTT@orcl>select * from emp where comm not in (300,500);

【注意】如果列表中包含空值，那么 NOT IN 返回 false。下面这个查询可以说明这
一点，它不返回任何行，因为列表中包含空值：

SCOTT@orcl>select * from emp where comm not in (300,500,null);

1.3.5 【模糊查询 like】

需求：查询姓名中包含 **MI **的。

SCOTT@orcl>select * from emp where ename like '%MI%';

1.3.5.1【escape 转义】

创建测试表、修改数据

SCOTT@orcl>create table emp1 as select * from emp;
SCOTT@orcl>update emp1 set ename='OCM_DBA' where empno=7788;
SCOTT@orcl>commit;

-- 查询
SCOTT@orcl>select * from emp1;

SCOTT@orcl>select * from emp1 where ename like '%_%';

【说明】
**_代表任一字符
转义符（\ **、 $）后的通配符失去通配功能，变为普通字符

SCOTT@orcl>select * from emp1 where ename like '%\_%' escape '\';

SCOTT@orcl>select * from emp1 where ename like '%$_%' escape '$';

1.3.6 【空值比较 is null is not null】

空值和空值不能用**= **来判断例如：未知数 x 和未知数 y ，都是未知值，不能用=
需求：查询奖金是空值（不是空值）的员工信息

-- 查询奖金是空值（不是空值） 的员工信息 
SCOTT@orcl>select * from emp where comm is null;

SCOTT@orcl>select * from emp where comm is not null;

1.4 【逻辑运算符】

实现多个条件的组合，逻辑操作符根据逻辑条件来限定行

1.4.1 【AND】

需求：查询工资大于等于 **1100 **并且工作是 **CLERK **的员工信息

-- 查询工资大于等于 1100 并且工作是 CLERK 的员工信息 
SCOTT@orcl>select * from emp
  2  where sal >=1000  and  job='CLERK';

需求：查询工资在 **800~1000 **范围内的员工。

-- 查询工资在 800~1000 范围内的员工。 
SCOTT@orcl>select * from emp where sal>=800 and sal <=1000;

-- 扩展
SCOTT@orcl>select * from emp where sal between 800 and 1000;

1.4.2 【OR】

需求：查询工资大于 **1100 **或者工作是 **CLERK **的员工信息

-- 查询工资大于 1100 或者工作是 CLERK 的员工信息
SCOTT@orcl>select * from emp where sal >=1100 or job='CLERK';

需求：查询经理号是 **7902 **或者 **7566 **或者 **7788 **的员工

-- 查询经理号是 7902 或者 7566 或者 7788 的员工 
SCOTT@orcl>select empno,ename,sal,mgr from emp
  2  where mgr=7902 or mgr=7566 or mgr=7788;

1.4.3 【NOT】

需求：查询工作不是 CLERK、 MANAGER、 **ANALYST **的员工

-- 查询工作不是 CLERK、 MANAGER、 ANALYST 的员工 
SCOTT@orcl>select ename,job from emp
  2  where job not in ('CLERK','MANAGER','ANALYST');

SCOTT@orcl>select ename,job from emp 
  2  where job!='CLERK' and job!='MANAGER' and job!=upper('analyst');

1.5 【优先级规则】

需求：查询工作是 **SALESMAN **或者 **PRESIDENT **的员工中，工资大于 **1500 **的员工。

-- 查询工作是 SALESMAN 或者 PRESIDENT 的员工中，工资大于 1500 的员工。
SCOTT@orcl>select ename,job,sal from emp
  2  where job='SALESMAN' or job ='PRESIDENT' and sal>1500;

结果中出现 sal 小于 1500 的员工，不符合要求。

SCOTT@orcl>select ename,job,sal from emp
  2  where (job='SALESMAN' or job='PRESIDENT') and sal>1500;

SCOTT@orcl>select ename,job,sal from emp
  2  where job in('SALESMAN','PRESIDENT') and sal>1500;

1.6 【order by 子句：排序】

【order by 子句】
desc 降序， asc 升序。
order by 列名， order by 别名， order by 列序号， order by 表达式

如果有 where 子句， order by 子句跟在 where 子句后面。**order by **子句是 **select **语句中最后一个子句。

1.7 【升序 asc】

默认是 **asc **升序：
数字升序：由低到高
日期升序：由早到晚
字符升序：按字母表顺序(区分大小写)

SCOTT@orcl>select * from emp order by sal;

1.8 【降序 desc】

SCOTT@orcl>select * from emp order by sal desc;

1.9 【按照列的别名排序】

SCOTT@orcl>select ename,sal salary from emp order by salary;

如果列别名用双引号定义， **order by **排序时注意格式

SCOTT@orcl>select ename,sal "salary" from emp order by salary;
SCOTT@orcl>select ename,sal "salary" from emp order by "salary";

1.10 【按照列序号排序：位置排序】

列序号：列在 **select **子句中出现的位置

SCOTT@orcl>select empno,ename,deptno from emp order by 3,1;

1.11 【多个列排序：混合排序】

SCOTT@orcl>select * from emp order by deptno,sal desc;

1.12 【排序中的 null 值】

包含 null 值的列排序时，默认把 null 值看做无限大:
在升序排序时， nulls last
在降序排序时， nulls first

SCOTT@orcl>select empno,ename,sal,comm from emp order by comm;

1.13 【order by 的部分限制】

order by 后面不能跟 LOB、 LONG、 LONG ROW 类型的列。
select 语句中有 distinct 关键字， order by 后面的列必须出现在 select 子句中。
order by 根据字符排序时，区分大小写