oracle基础select查询语句

select dname "工资" from dept;--查询全部员工select * from emp;--查询指定的编号,姓名和职位select empno,ename,job from emp;--修改显示别名空格,数字和特殊符号(#$除外)做别名需要双引号select empno as "1" ,ename 员工姓名,job "职位" from emp;--去除重复行select distinct job from emp;--列拼接select concat(concat(concat(concat(concat(' 编号是:',empno ),'的雇员,姓名是:'),ename) ,'工作是:'),job) from emp;--oracle用简单办法||拼接select '编号:' || empno || '的雇员,姓名是:' || ename || ',工作是:' || job from emp;--使用nvl (v1,v2)处理空值v1不为空返回,v2为空返回v2select ename,sal,comm,sal*12 + nvl (comm,0) from emp;--使用decodeselect ename,sal,comm,sal*12 + decode(comm,null,0,comm)from emp;--单行单列虚拟表dualselect sysdate from dual--得到一个32位的唯一guidselect sys_guid() from dual;--进行加减乘除运算select 1+3 from dual--oracle条件语句查询--查询出基本工资大于2000的所有雇员信息select * from emp where sal > 2000;--查询职位是办事员的所有雇员信息select * from emp where job ='CLERK'--查询工资在2000-3000之间的员工信息select * from emp where sal>2000 and sal < 3000;--查询工资在2000-3000之间的全部雇员信息*(包含)select * from emp where sal >= 2000 and sal <=3000;select * from emp where sal between 2000 and 3000;--查询职位是办事员或者销售人员的全部信息select * from emp where job='CLERK' or job='SALESMAN';select * from emp where job in ('CLERK','SALESMAN');--查询所用不是办事员的雇员信息select * from emp where job <>'CLERK';select * from emp where job != 'CLERK';select * from emp where not job = 'CLERK';--查询所有某段时间内入职的员工信息select * from emp where hiredate between to_date('1981/1/1','yyyy/mm/dd') and to_date('1982/1/1','yyyy/mm/dd');--查询所有有奖金的雇员select * from emp where comm is not null;--查询所有没有奖金的雇员select * from emp where comm is null;--查询出员工编号为7369.7499,7521的信息select * from emp where empno =7369 or empno=7499;select * from emp where empno in (7369,7499,7521)--查询出员工编号不为7369.7499,7521的信息select * from emp where empno not in (7369,7499,7521);--查询员工姓名中以字母A为开头的全部员工信息select ename from emp where ename like 'A%';--查询员工姓名中第二个字母是A的全部员工信息select ename from emp where ename like '_A%';--查询员工姓名中有A的全部员工信息select ename from emp where ename like '%A%';--oracle结果排序--查询所有员工,并按照要求工资升序排序select * from emp order by sal;--查询所有员工,并按照要求工资升序排序select * from emp order by sal desc;--按照工资从高到低排序,如果工资相同,则按照雇佣时间先后排序select * from emp order by sal desc, hiredate;--排序中的空值问题select * from emp order by comm desc;select * from emp order by comm desc nulls first;select * from emp order by comm desc nulls last;--oracle单行函数--字符函数--字符串大写upperselect upper('mingming')from dual;--字符串小写lowerselect lower(ename) from emp;--首字符大写initcapselect initcap(ename) from emp;--字符串取长度lengthselect ename,length(ename) from emp;--字符串替换replaceselect ename,replace(ename,'A','#')from emp;--字符串截取substrselect ename,substr(ename,0,2) from emp; select ename,substr(ename,1,2) from emp;select ename,substr(ename,-1) from emp;select ename,substr(ename,-2,1) from emp; select ename,substr(ename,-2,3) from emp;--两边去掉空格trimselect ename,trim(ename) from emp;--数字函数--四舍五入select round(987.6543) from dual;--988select round(987.6543,0) from dual;--988select round(987.6543,1) from dual;--987.7 select round(987.6543,2) from dual;--987.65 select round(987.6543,-1) from dual;--990select round(987.6543,-2) from dual;--1000--trunc舍弃内容select trunc(953.6286) from dual; --953select trunc(953.6286,0) from dual;--953select trunc(953.6286,1) from dual;--953.6select trunc(953.6286,2) from dual;--953.62 select trunc(953.6286,-1) from dual;--950select trunc(953.6286,-2) from dual--900--取模mod /余数select mod(10,3) from dual;--日期函数--获取当前时间sysdateselect sysdate from dual;--表示几天之后的日期+dayselect sysdate+3 from dual;--两个日期之间的天数差sysdate-hiredate select ename,hiredate,sysdate-hiredate from emp ; select trunc(sysdate-hiredate) from emp ;--本月的最后一天日期last_dayselect last_day(sysdate) from dual;--两个日期键的月份差months_betweenselect ename,hiredate ,trunc(MONTHS_BETWEEN(sysdate,hiredate)) from emp;--求出四个月后的日期add_montsselect add_months(sysdate,2)from dual;--转换函数--日期变为字符串to_char*(mi,hh24,yyyy,day)select to_char(sysdate,'yyyy-mm-dd')from dual;--2017-11--12select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;--2017-11-12 05:07:00--数字变为字符串to_char(L999,999,999)数字代表长度SELECT TO_CHAR(89078907890,'L9999,9999,9999,9999') FROM dual;-- $89,078,907,890--字符串转日期to_date(yyyy-mm-dd hh:mi:ss)--字符串必须是日期格式的字符串select to_date('2017-11-12','yyyy/mm/dd') from dual;--2017/11/12--字符串转数字to_number('123'+4)select to_number('123'+4) from dual--127--通用函数--nvl(v1,v2)处理null 如果第一个数为null返回第二个数,如果第一个数不为空返回第一个数select nvl(1,2)from dual;--1select nvl(null,2) from dual;--2--nvl(v1,v2,v3)处理null; 如果v1为null返回v3,否则返回v2select nvl2(null,1,2) from dual;select nvl2(3,1,2) from dual;--decode()多值判断DECODE(数值| 列，判断值1，显示值1，判断值2，显示值2，判断值3，显示值3，…)--将职位信息转为汉字SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁')FROM emp;--case when条件判断--将职位信息转为汉字select empno,ename,job,case jobwhen 'CLERK' then'业务员'when 'SALESMAN' then'销售人员'else'其他'endfrom emp;--oracle多行函数分组函数作用于一组数据,并对一组数据返回一个值--统计记录数count() 查询出所有员工的记录数select count(*) from emp;--不建议使用count(*)，可以使用一个具体的列以免影响性能select count(empno) from emp;--最小值查询min()查询出来员工最低工资select min(sal) from emp;--最大值查询max()查询出来员工最高工资select max(sal) from emp;--查询平均值avg()查询出员工的平均工资select avg(sal) from emp;--求和函数sum()查询出某部门额员工工资总和select sum(sal) from emp where deptno=20;--分组汇合统计group by--查询每个部分的人数select deptno,count(*) from emp group by deptno;--查询出每个部分的平均工资select deptno,avg(sal)from emp group by deptno;--唯一字段做分组select ename,count(*) from emp group by ename;--过滤分组数据having--查询部分平均工资大于2000的部门,用having和where都可以实现select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;--第二天多表查询--笛卡尔集select * from emp,dept;--内连接--隐式内连接select e.empno,e.ename,e.job,d.dname from emp e,dept d where e.deptno = d.deptno;--显示内连接select e.empno,e.ename,e.job,d.dname from emp e inner join dept d on e.deptno =d.deptno; --左外连接select * from dept left join emp on dept.deptno = emp.deptno;--右外连接select * from emp e right join dept d on e.deptno=d.deptno;--oracle特有外连接--使用符号(+):放在作为补充显示的列后面select * from emp,dept where emp.deptno(+)=dept.deptno;--自连接--查询出员工的姓名,职位,领导姓名select * from emp a,emp b where a.mgr=b.empno;--使用左右外连接,找到没有领导的员工select * from emp a,emp b where a.mgr=b.empno(+);--多表联查--查询员工姓名,部门名称,领导姓名select e1.ename,e2.ename,d.dname from emp e1,emp e2,dept d where e1.mgr=e2.empno and e1.deptno=d.deptno;--查询员工姓名,部门名称,领导名称,员工工资等级select e1.ename,e2.ename,d.dname,s.grade from emp e1,emp e2,dept d,salgrade s where e1.mgr=e2.empno and e1.deptno=d.deptno and e1.sal between s.losal and s.hisal;--查询员工姓名、部门名称、领导名称、员工工资等级、领导工资等级select e1.ename, e2.ename, d.dname, s1.grade,s2.gradefrom emp e1, emp e2, dept d, salgrade s1,salgrade s2where e1.mgr = e2.empnoand e1.deptno = d.deptnoand e1.sal between s1.losal and s1.hisaland e1.sal between s2.losal and s2.hisal;--将工资等级转换为汉字select e1.ename, e2.ename, d.dname, decode(s1.grade,'1','第五级','2','第四级','3','第三级','4','第二级' ,'5','第一级'),decode(s2.grade,'1','第五级','2','第四级','3','第三级','4','第二级' ,'5','第一级')from emp e1, emp e2, dept d, salgrade s1,salgrade s2where e1.mgr = e2.empnoand e1.deptno = d.deptnoand e1.sal between s1.losal and s1.hisaland e1.sal between s2.losal and s2.hisal;--子查询--单行子查询(单行单列)--查询比员工7654工资高,并和7788相同职位的员工select e.ename from emp e where e.sal>(select sal from emp where empno=7654)and e.job=(select job from emp where empno =7788)Select * from emp where sal>(Select sal from emp where empno=7654)And job = (Select job from emp where empno=7788)--子查询放在select中--查询员工信息和部门名称select e.* ,(select d.dname from dept d where d.deptno=e.deptno)from emp e ;--多行子查询(多行多列和多行单列)--多行多列子查询实现--查询每个部门的最低工资,和最低工资的员工select e.ename,e.sal,d.dname from emp e,(select deptno ,min(sal) sal from emp group by deptno) dm,dept d where e.deptno = d.deptno and e.deptno = dm.deptno and e.sal= dm.sal;Select e.ename,e.sal,d.dnameFrom emp e,(Select deptno,min(sal) sal from emp group by deptno) dm,dept dWhere e.deptno=d.deptno and e.deptno=dm.deptno And e.sal = dm.sal--多行单列子查询实现--查询是领导的所有员工信息select * from emp where emp.empno in(select mgr from emp e where e.mgr is not null);--查询不是领导的所有员工信息select * from emp where empno not in (select nvl(mgr,0) from emp)--exists--判断结果集是否存在exists(sql语句)--用来判断结果集是否存在,如果存在返回true,如果不存在返回falseselect * from emp where exists (select * from dept)--查询有员工的部门select * from dept where deptno in (select deptno from emp where deptno is not null)select * from dept d where exists (select deptno from emp e where e.deptno= d.deptno )--查询是领导的所有的员工信息select * from emp e1 where exists (select e2.mgr from emp e2 where e2.mgr= e1.empno)--伪列rownumselect rownum, e.* from emp e;--查询员工信息的前三条select rownum, e.* from emp e where rownum<4;--排序后rownum乱序select rownum ,e.* fROM emp e where rownum<20 order by sal desc ;--解决办法,先排序再生成rownumselect * from emp order by sal desc;select rownum ,e.* from (select * from emp order by sal desc)e where rownum <4;select * from(Select rownum rm, t.* from (select * from emp order by sal desc) t) where rm<4--找到员工表中薪水大于本部门平均薪水的员工--本部门平均薪水select deptno,avg(sal) from emp group by deptno;select * from emp e,(select deptno,avg(sal)avs from emp group by deptno)m where e.sal>m.avs and e.deptno=m.deptno;--统计每年入职的员工个数select to_char(e.hiredate,'yyyy')hire_year,count(*) from emp e group by to_char(hiredate,'yyyy');SelectSum(hire_count) total,sum(decode(t.hire_year,'1980',t.hire_count)) "1980",sum(decode(t.hire_year,'1981',t.hire_count)) "1981",sum(decode(t.hire_year,'1982',t.hire_count)) "1982",sum(decode(t.hire_year,'1987',t.hire_count)) "1987"from(Select to_char(hiredate,'yyyy') hire_year,count(*) hire_countFrom emp group by to_char(hiredate,'yyyy')) trowidRowid是oracle数据库插入数据时给数据分配的真实物理地址，唯一不变Rownum 是伪列，在查询数据时才会生成的临时数值--集合运算--并集--查询工资大于1500或是20号部分的员工select * from emp where sal>1500 or deptno=20;--union实现select * from emp where sal>1500unionselect * from emp where deptno =20;--union all实现(没有去重)select * from emp where sal>1500union allselect * from emp where deptno =20;--交集*(intersect 取两个集合共同的部分)select * from emp where sal >1500 and deptno=20;select * from emp where sal >1500intersectselect * from emp where deptno=20;--差集(minus 从一个集合中去掉另一个集合剩余的部分)--1981年入职的普通员工,不包含总裁和经理select * from emp where to_char(hiredate,'yyyy')='1981'and job not in ('MANAGER','PRESIDENT')select * from emp where to_char(hiredate,'yyyy')='1981'minusselect * from emp where job in ('MANAGER','PRESIDENT')。