Oracle限定查询

--限定查询
 
 select COUNT(*) from emp;
 
 select * from emp where sal>1500;
 
 select * from emp where sal<=2000;
 
 select * from emp where ename='SMITH';
 
 select * from emp where job='CLERK';
 
 select * from emp where job<>'CLERK';
  
 select * from emp where job!='CLERK';
 
 select * from emp where sal>=1500 and sal<=3000;
 
 select * from emp where job='SALESMAN' and sal>1200;
 
 select * from emp where (deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK');
 
 select * from emp where job!='CLERK' and sal>2000;
 
 select * from emp where job<>'CLERK' and sal>2000;
 
 select * from emp where not(job='CLERK' or sal<=2000);
 
 select * from emp where sal between 1500 and 3000;
 
 select * from emp where hiredate between '01-1月-1981' and '31-12月-1981';
 
 select * from emp where comm is not null;
 
 select * from emp where comm is null;
 
 select * from emp where comm is null and sal >2000;
 
 select * from emp where comm is null or comm<100;
 
 select DISTINCT job from emp where comm is not null;
 
 select * from emp where empno=7369 or empno=7788 or empno=7566;
 
 select * from emp where empno in(7369,7788,7566);
 
 select * from emp where empno not in(7369,7788,7566); 
 
 select * from emp where ename like 'S%';
 
 select * from emp where ename like '_M%';
 
 select * from emp where ename like '%F%';
 
 select * from emp where ename like '______%';
 
 select * from emp where sal like '%1%' or hiredate like '%81%';
 
 select * 
 from emp 
 where empno like '%%' or ename like '%%' or job like '%%' or hiredate like '%%' or  sal like '%%' or comm like '%%';
 
 select * 
 from emp 
 where ((deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK')
 or(job not in('MANAGER','CLERK')and sal>2000))
 and (ename like '%S%' or ename like '%K%');
 
 select * from emp order by sal desc;
 
 select empno,ename,sal,job from emp order by 3 desc;
 
 select * from emp order by sal;
 
 select * from emp order by sal asc;
 
 select * from emp where job='CLERK' order by sal;
 
 select * from emp order by sal desc, hiredate asc;