一次性插入多条数据
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;