oracle 的几个查询语句

一次性插入多条数据

create table test(id number(4),name varchar2(10),job varchar2(9));

insert into test (id,name,job) select emono,ename,job from emp;

一次性修改对个值

update set (job,sal,mgr)=(select job,sal,mgr from emp where ename='KING') where ename='SMITH';

复制表

create table emp as select * from scott.emp;

复制部分字段

create table emp( emono,ename,job,mgr,hiredate,sal,comm,deptno) as select * from scott.emp;

将ename的第一个字母大小 其余小写

 select emono,upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)),sal,hiredate,job,deptno from emp;

查询工资大于本部门平均工资的人员
select al.ename,al.sal,al2.monery from emp al,(select deptno,avg(sal) as monery from emp group by deptno) al2 where al.sal>al2.monery and al.deptno=al2.deptno;

查询自己的领导部门

select a1.ename,a2.ename from emp a1,emp a2 where a1.mgr=a2.empno;

替换指定字符串

select replace(ename,'A','---') from emp;

trunc函数

select trunc(sysdate) from dual;  系统默认格式：YYYY-MM-DD

select trunc(89.885345,2) from dual; 如果为负，用0补全

round函数

select round(3.4) from dual;四舍五入

floor函数

select floor(3.4) from dual; 取小

ceil函数

select ceil(4.3) from dual;取大

oracle几个系统函数

select sys_context('userenv','db_name') from dual; 查看数据库

select sys_context('userenv','terminal') from dual; 终端名 其实就是计算机名

select sys_context('userenv',language) from dual; 当前使用语言

select sys_context('userenv','session_user') from dual; 当前会话用户  即你的登录账户

select sys_context('userenv','nls_date_format') from dual; 当前会话 日期格式

select sys_context('userenv','current_schema') from dual; 当前方案

查出每个月倒是第3天任职的员工

select ename,hiredate from emp where hiredate=last_day(hiredate)-2;

在公司任职的满10年的员工

select * from emp where sysdate>add_months(hiredate,12*10);

得到某个员工的任职天数

select ename,floor(sysdate-hiredate) as "任职天数" from emp;

得到1980年任职的员工

select * from emp where 1980=to_char(hiredate,'yyyy');

将字符串转化为时间

select to_date('2013-10-12','YYYY-MM-DD') from dual;

按知道格式输出

select　ename,to_char(hiredate,'YYYY-MM-DD hh24:mi:ss') ,to_char(sal,'L99999.99') from emp;