有关sql语句中过滤与排序的实例教程，学习下使用where字句实现对数据的过滤、多条件连接、比较符、日期和字符串的处理、order by 排序等，需要的朋友参考下。
一、使用where字句实现对数据的过滤
用法：select *|{[distinct] column|expression [alias],...}
from table
[where condition(s)];
二、多条件连接
and同时满足
or 满足其中一个
三、比较符
> 大于
>＝ 大于等于
< 小于
<＝ 小于等于
<> 不等于
！= 不等于
is null 是否为空值
is not null 不为空值
not 条件为false时返回true
between x and y 大于等于x，小于等于y
in （集合） 在这个集合中，或者讲在这个列表中
not in（集合） 不在这个集合中，或者讲不在这个列表中
like 使用like操作符来执行有效搜索字符串数值的通配符搜索
％ 通配任意字符
_ 能配单个字符
四、日期和字符串的处理
字符串和日期值使用单引号标记嵌入
字符数值是大小写有关，而日期数值是格式化的。
oracle默认显示的日期格式为：dd－mon－rr
可以用alter session set nls_date_format='yyyy-mm-dd';修改日期格式
永久性修改可以用：
alter system set nls_date_format='yyyy-mm-dd' scope=spfile;
日期相加减：
日期＋（－）数字 返回一个往前或往后的天数的日期
＋往后 －往前
日期－日期 得到两个日期之间相差的天数
五、order by 排序
默认的排序方式：升序
控制排序方式： asc升序
desc 降序
六、有关过滤与排序sql语句的例子
复制代码 代码示例:
/*使用where条件查询*/
sql> select * from scott.emp where sal > 2000
2 ;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 jones manager 7839 02-apr-81 297520
7698 blake manager 7839 01-may-81 285030
7782 clark manager 7839 09-jun-81 245010
7788 scott analyst 7566 19-apr-87 300020
7839 king president17-nov-81 500010
7902 ford analyst 7566 03-dec-81 300020
/*多条件连接*/
sql> select * from scott.emp where deptno = 20 and sal > 2000;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 jones manager 7839 02-apr-81 297520
7788 scott analyst 7566 19-apr-87 300020
7902 ford analyst 7566 03-dec-81 300020
/*错误的写法*/
sql> select * from scott.emp where 1000<=sal<=2000;
select * from scott.emp where 1000<=sal<=2000
*
error at line 1:
ora-00933: sql command not properly ended
/*正确的写法*/
sql> select * from scott.emp where sal >= 1000 and sal <= 2000;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 allen salesman7698 20-feb-81 1600300 30
7521 ward salesman7698 22-feb-81 1250500 30
7654 martin salesman7698 28-sep-81 1250 1400 30
7844 turner salesman7698 08-sep-81 1500 0 30
7876 adams clerk 7788 23-may-87 110020
7934 miller clerk 7782 23-jan-82 130010
/*between的用法*/
sql> select * from scott.emp where sal between 1000 and 2000;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 allen salesman7698 20-feb-81 1600300 30
7521 ward salesman7698 22-feb-81 1250500 30
7654 martin salesman7698 28-sep-81 1250 1400 30
7844 turner salesman7698 08-sep-81 1500 0 30
7876 adams clerk 7788 23-may-87 110020
7934 miller clerk 7782 23-jan-82 130010
/*in的用法，以下两条语句等同*/
sql> select * from scott.emp where sal = 3000 or sal = 5000;
sql> select * from scott.emp where sal in (3000,5000);
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 scott analyst 7566 19-apr-87 300020
7839 king president17-nov-81 500010
7902 ford analyst 7566 03-dec-81 300020
sql> select * from scott.emp where sal = 3000 or sal = 5000;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 scott analyst 7566 19-apr-87 300020
7839 king president17-nov-81 500010
7902 ford analyst 7566 03-dec-81 300020
--not in的用法
sql> select * from scott.emp where sal not in (3000,5000);
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 smith clerk 7902 17-dec-8080020
7499 allen salesman7698 20-feb-81 1600300 30
7521 ward salesman7698 22-feb-81 1250500 30
7566 jones manager 7839 02-apr-81 297520
7654 martin salesman7698 28-sep-81 1250 1400 30
7698 blake manager 7839 01-may-81 285030
7782 clark manager 7839 09-jun-81 245010
7844 turner salesman7698 08-sep-81 1500 0 30
7876 adams clerk 7788 23-may-87 110020
7900 james clerk 7698 03-dec-8195030
7934 miller clerk 7782 23-jan-82 130010
/*like、%、_ 运算符的用法*/
sql> select * from scott.emp where ename like 'a%';
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 allen salesman7698 20-feb-81 1600300 30
7876 adams clerk 7788 23-may-87 110020
sql> select * from scott.emp where ename like '_l%';
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 allen salesman7698 20-feb-81 1600300 30
7698 blake manager 7839 01-may-81 285030
7782 clark manager 7839 09-jun-81 245010
--日期格式：未修改前
sql> select sysdate from dual;
sysdate
---------
28-mar-10
/*日期格式的修改，仅对当前会话有效*/
sql> alter session set nls_date_format = 'yyyy-mm-dd';
session altered.
--查看修改后的日期格式
sql> select sysdate from dual;
sysdate
----------
2010-03-28
--永久性修改，将其修改写到参数文件中。
alter system set nls_date_format = 'yyyy_mm_dd' scope = spfile;
--日期的加减
-- 日期＋（－）数字 返回往前或往后的天数日期
-- ＋往前，－往后
-- 日期－ 日期
sql> select sysdate + 10 from dual;
sysdate+10
----------
2010-04-07
sql> select sysdate - 10 from dual;
sysdate-10
----------
2010-03-18
--日期相减(为相差的天数)
sql> select empno,ename,sysdate,sysdate - hiredate from scott.emp;
empno ename sysdatesysdate-hiredate
---------- ---------- ---------- ----------------
7369 smith 2010-03-28 10693.4346
7499 allen 2010-03-28 10628.4346
7521 ward 2010-03-28 10626.4346
7566 jones 2010-03-28 10587.4346
7654 martin 2010-03-28 10408.4346
7698 blake 2010-03-28 10558.4346
7782 clark 2010-03-28 10519.4346
7788 scott 2010-03-28 8379.43456
7839 king 2010-03-28 10358.4346
7844 turner 2010-03-28 10428.4346
7876 adams 2010-03-28 8345.43456
--使用null值过滤
sql> select * from scott.emp where comm is null;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 smith clerk 7902 1980-12-1780020
7566 jones manager 7839 1981-04-02 297520
7698 blake manager 7839 1981-05-01 285030
7782 clark manager 7839 1981-06-09 245010
7788 scott analyst 7566 1987-04-19 300020
7839 king president1981-11-17 500010
7876 adams clerk 7788 1987-05-23 110020
7900 james clerk 7698 1981-12-0395030
7902 ford analyst 7566 1981-12-03 300020
7934 miller clerk 7782 1982-01-23 130010
/*order by排序（默认为升序）*/
sql> select * from scott.emp where sal > 2000 order by sal;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7782 clark manager 7839 1981-06-09 245010
7698 blake manager 7839 1981-05-01 285030
7566 jones manager 7839 1981-04-02 297520
7902 ford analyst 7566 1981-12-03 300020
7788 scott analyst 7566 1987-04-19 300020
7839 king president1981-11-17 500010
--多字段排序
sql> select * from scott.emp where sal > 2000 order by sal desc ,ename asc;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7839 king president1981-11-17 500010
7902 ford analyst 7566 1981-12-03 300020
7788 scott analyst 7566 1987-04-19 300020
7566 jones manager 7839 1981-04-02 297520
7698 blake manager 7839 1981-05-01 285030
7782 clark manager 7839 1981-06-09 245010
--按第个字段降序排列，order by 4 desc
sql> select empno,ename,job,sal from scott.emp where sal > 2000 order by 4 desc ;
empno ename job sal
---------- ---------- --------- ----------
7839 king president 5000
7902 ford analyst 3000
7788 scott analyst 3000
7566 jones manager 2975
7698 blake manager 2850
7782 clark manager 2450
