学习ORACLE的笔记一

 

oracle操作
sqlpuls sys/oracle as sysdba
select user from dual;
alter user scott account unlock;

desc
select ename sal*12 from emp;
select sysdate from dual;
select ename,sal*12 anuul_sal from emp;
select ename||sal from emp;
select ename||'sdasdasd' from emp;
select distinct deptno,job from emp;
select ename,sal from emp where sal in(800,1500,2000);
select ename,sal from emp where sal>=800 and sal <=3000;

select ename,sal from emp where sal between 800 and 3000;
select * from dept order by deptno desc;  降序
select empno,ename from emp order by empno desc; 
select empno,ename from emp order by empno;
select empno,ename from emp order by empno;
select empno,ename from emp where deptno <>10 order by empno desc;
select empno,ename from emp where deptno <>10 order by empno asc;
select deptno,ename,sal from emp  order by deptno asc,ename desc;
select ename,sal*12 annual_sal from emp where ename not like '_A%' and
sal > 800 order by sal desc;
select enamefrom emp  where lower(ename) like '_a%';
select substr(ename,1,2) from emp;
select chr(65) from dual;
select ascii('A') from dual;
select round(23.673) from dual;
select round(23.673,1) from dual;
select round(23.673,2) from dual;
select round(23.673,-1) from dual;
select to_char(sal,'$99,999.9999') from emp;
select to_char(sal,'L99,999.9999') from emp;
select to_char(sal,'L00,000.0000') from emp;
select to_char(hiredate,'yyyy-mm-dd hh:mi:ss') from emp;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','yyyy-mm-dd hh24:mi:ss');
select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','yyyy-mm-dd hh24:mi:ss')
order by hiredate desc;
select sal from emp where sal > to_number('$1,250.00',‘$9,999.99');
select ename,sal*12,comm from emp;
select ename,sal*12+nvl(comm,0) from emp;