oracle支持的SQL操作符

根据优先级排序
一、算术操作符
+、-、*、/
select sal，comm，sal+nvl(comm,0) from emp;

[img]http://dl2.iteye.com/upload/attachment/0112/9378/8a08b8ac-379f-335c-9c50-6efe6d06adaf.png[/img]

二、连接操作符
1、union ：合并两个操作结果，去掉重复值
select sal,deptno from emp union select sal,deptno from emp where deptno=10;

[img]http://dl2.iteye.com/upload/attachment/0112/9380/66716071-8b1e-37e0-a468-aa6ab48de4a3.png[/img]

2、union all：合并两个操作结果，保留重复值
select sal,deptno from emp union all select sal ,deptno from emp where deptno=10;

[img]http://dl2.iteye.com/upload/attachment/0112/9382/aeb25477-b97d-32b1-a6b1-054a6393b508.png[/img]

3、intersect ：取两个结果集的交集
select sal ,deptno from emp intersect select sal,deptno from emp where deptno=10;

[img]http://dl2.iteye.com/upload/attachment/0112/9384/57f29c45-78a3-379d-abe8-73eac562523d.png[/img]

4、minus：取在第一个结果集中出现但在第二个结果集中没有出现过的数据
select sal ,deptno from emp minus select sal,deptno from emp where deptno=10;

[img]http://dl2.iteye.com/upload/attachment/0112/9386/b047974a-28a5-33c2-a87b-18f65a17f4cf.png[/img]


三、比较操作符
= ！= > < >= <=
in ：用来检索某列的值在某个列表中的数据行
select * from emp where empno in(7788,7800,7900);

[img]http://dl2.iteye.com/upload/attachment/0112/9390/39027f46-3a85-3e7f-af9d-335bf2fe5b1a.png[/img]

any :与一个列表中任意一个值比较
select ename,sal from emp where sal<any(3000,2850,2975);

[img]http://dl2.iteye.com/upload/attachment/0112/9392/0af66b9c-d239-3485-8e98-9f4c5568ad47.png[/img]

all ：与一个列表中所有值比较
select ename,sal from emp where sal<all(3000,2850,2975);

[img]http://dl2.iteye.com/upload/attachment/0112/9394/69812d47-a110-30d7-8349-7543279a96a7.png[/img]

between..and..：用来检索列值包含在指定区间内的数据行
select ename,sal from emp where sal between 1600 and 2975;

[img]http://dl2.iteye.com/upload/attachment/0112/9396/7d753f4a-ae76-3e31-9949-560efc6ec047.png[/img]

is null:用来判断是否为null
select * from emp where mgr is null;

[img]http://dl2.iteye.com/upload/attachment/0112/9398/6cc5ddd3-e683-31fa-be7d-ecb5fa98914d.png[/img]

like:模糊匹配
_ ：表示匹配指定位置的一个字符
% ： 表示匹配指定位置开始的一个或者多个字符
select * from emp where ename like 'B_A%';

[img]http://dl2.iteye.com/upload/attachment/0112/9400/d26fa657-8aee-3a6f-af37-2e0b73132f72.png[/img]

如果需要对_和%进行文本匹配，可以用escape选项标识这些字符
例如：'%\%%'escape '\'
四、逻辑操作符
not ：
and
or
1、查询工资大于1600且小于3000的信息：
select * from emp where sal>1600 and sal<3000;

[img]http://dl2.iteye.com/upload/attachment/0112/9402/2d228d5c-e910-3f9c-95bf-4086b79777e7.png[/img]/
2、查询部门为10和20的信息：
select * from emp where deptno=10 or deptno=20;

[img]http://dl2.iteye.com/upload/attachment/0112/9406/8d2e1a4b-d03e-3387-a8fa-57420d58baf1.png[/img]