Oracle的各种查询方法大全

1.查询全部，空值，去重复，大于/小于/等于/乘于/除于及赋别名与and用法
select * from student; //查询表所有数据
select distinct deptno,job from emp; //过滤查询，deptno,job相同的记录只出现一次
select ename,sal from emp where sal>=2000 and sal<=2500; //大于小于和and
select sal*12 "年工资", ename from emp; //乘于与别名
2.查询空值与处理空值
select * from student where birthday is [not] null; //查询空值
select sal*12+comm*12 “年工资”,ename from emp; //此法若comm值为空，两个相加值其中一个为空相加结果为空，所以不对
select sal*12+nvl(comm,0)*12 “年工资”,ename,comm from emp; //正确处理空值用nvl(comm,0)表如果comm字段的值为空，那么把该值设为0
3.查询中的like，in，%，_ ，百分号表任意多个，下划线为任意一个
select ename,sal from emp where ename like 'S%'; //查首字母为S的
select ename,sal from emp where like ename like '__O%'; //查第三个字母为O的
select * from emp where empno in (7844,234,456); //查in括号中有的

4.or与and，order by
select * from emp where (sal>500 or job='MANAGER') and ename like ‘J%’; //查工资大于500或工作为manager并且姓名第一个字母为J的
select * from emp order by deptno[ asc], hiredate desc; //按工资正排，日期倒排
select ename,(sal+nvl(comm,0))*12 [as] "年薪" from emp order by "年薪";  //别名
5.max,min,avg,sum,count用法
select max(sal),min(sal),agv(sal) from emp; //查最高，最低及平均工资

6.子查询
select ename,sal from emp where sal=(select max(sal) from emp); //查最高工资的
select * from emp where sal>(select avg(sal) from emp);//查高于平均工资的

7.把查询结果分组并限制分组后的结果，group by和having用法
select avg(sal),max(sal),deptno from emp group by deptno; //按部门分组
select avg(sal),max(sal),deptno,job from emp group by deptno,job; //按部门和工作分级
select  avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000; //按部门分组只显示平均工资大于2000的部门
select  avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal); //再按平均工资排序

8.多表查询，及多表查询加and,between,orderby
select a1.ename, a1.sal, a2.dname from emp a1, dept a2 where a1.deptno=a2.deptno; //查出姓名，工资，部门名，表名和别名间不能加as
select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a2.deptno=10; //加and
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal; //加between，salgrade为表名
select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno; //加order

9.自连接，子查询或嵌套查询，多行子查询加in，all，any
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and boss.ename='FORD';
select * from emp where deptno=(select deptno from emp where ename='SMITH'); //查询部门号等于smith所在部门号的所有人员
select * from emp where job in (select distinct job from emp where deptno=10);//多行加in
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30); //多行加all
select ename,sal deptno from emp where sal>any(select sal from emp where deptno=30); //多行加any
10.多行多列比较
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
11.与查询出的子表或内嵌视图的多表查询
select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2, (select deptno,avg(sal) [as] mysal from emp group by deptno) a1 where a1.deptno=a2.deptno and a2.sal>a1.mysal;
12.取前几条，后几条，第几到第几条记录，类似于top N
select a1.*,rownum rn from (select * from emp) a1 where rownum<=10; //输出前10条
select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6; //输出6到10条
13.统计记录数，用查询结果创建新表并录入查询结果
select count(*) from table;/select count(字段名) from table; //统计记录
create table myemp2 (id,name,sal) as select empno,ename,sal from emp; //用查询结果建新表
14.union,unionall,intersect,minus，取并，交，差，减
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER'; //合并两个查询结果去掉重复
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='MANAGER';//合并不去掉重复
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER';//取两个查询都有的
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER';//取前一查询有后一查询没有的
15.把查询的数据插入到另一个表，及用查询的数据更新表
create table kkk(myId number(4),myName varchar2(50),myDept number(5));//创建名为kkk的表，
insert into kkk(Myid,myName,myDept) select empno,ename,deptno from emp where deptno=10;//
update emp set job=(select job from emp where ename='SMITH'),sal=(select sal from emp where ename='SMITH')
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
16.在查询中使用字符函数， 以及双竖用法 ||
select lower(ename),sal  from emp; //字段转小写
select upper(ename),sal  from emp; //转大写
select *  from emp where length(ename)=5; //返回字段长度
select substr(ename,1,3) from emp; //切取字符部分
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; //首字母大写
select replace(ename,'A','a') from emp; //替换某字符
17.数据函数
select round(sal/30,1),sal from emp where ename='shunping'; //保留指定个小数，小后位四舍五入
select trunc(sal/30,1),comm from emp where ename='FORD'; //保留指定个小数，不四舍五入
select floor(comm),comm from emp where ename='shunping';  // floor(n)，返回小于或等于N的最近整数 ，向下取整数
select ceil(comm),comm from emp where ename='shunping'; //向上取整
18.亚元表测试取模
select mod(10,2) from dual; //取模
select abs(-13) from dual //返回绝对值
select sysdate from dual; //当前时间
select sys_context('userenv','db_name') from dual; //返回正在使用的的数据库
select sys_context('userenv','language') from dual; //返回正在使用的语言
select sys_context('userenv','session_user') from dual; //返回正在使用的用户
select sys_context('userenv','current_schema') from dual; //返回正使用的方案，用户名和方案名一样
19.日期函数的查询与运算
select * from emp where sysdate>add_months(hiredate,8); // add_months为加月份的函数
select ename,hiredate from emp where sysdate>add_months(hiredate,12*10);
select trunc(sysdate-hiredate) 入职天数,ename from emp; //别名
select hiredate,ename from emp where last_day(hriedate)-2=hiredate; //last_day反回指定日期月的最后一天
20.to_char函数的用法，转日期，货币格式，从日期取年，取月，取日
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),sal from emp; //to_char转日期格式
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99')sal from emp; 
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'$99,999.99'),sal from emp;
select ename,to_char(hiredate,'yyyy'),to_char(sal,'$99,999.99')sal from emp;  //取年
select ename,to_char(hiredate,'mm'),to_char(sal,'$99,999.99')sal from emp;  //取月
select ename,to_char(hiredate,'dd'),to_char(sal,'$99,999.99')sal from emp;  //取天
select * from emp where to_char(hiredate,'yyyy')=1980;
select * from emp where to_char(hiredate,'mm)=12;