oracle笔记

sqlplus sys/password  as sysdba;系统管理员登录
alter user scott account unlock; 更改用户
desc （表名）
select * from (table name);
select distinct ename from emp;
select ename,sal from emp;
select ename,sal*12 'annual_sal' from emp;
select ename,sal from emp where sal>1000;
select ename,sal from emp where sal<>1000;
select ename,sal from emp where ename>'CBA';
select ename,sal from emp where sal between 800 and 1500;
select ename,sal from emp where ename like 'CBA';
select ename,sal from emp where ename like '%$%%' escape '$';
select ename,sal from emp where sal in (800,1000);
select ename,sal from emp where sal in 800 or/and ename='KING';
select ename,enumber from emp order by enumber desc;
select ename,enumber from emp order by enumber asc,sal asc;
select ename,sal*12 annual_sal from emp
where ename not like '_A%' and sal > 800
order by sal desc;
select substr (ename,2,3) from emp;
select ename from emp where lower(ename) like '_a%';
select chr(65) from dual;
select ascii('A') from dual;
select round (23.598,2) from dual;   23.60
select round (23.598,-1) from dual;  20
select to_char(sal,'$99,999.9999') from emp;
select to_char(sal,'L0000.0000') from emp;
select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
select ename,hiredate from emp where hiredate >to_date('1981-02-13 12:00:00','YYYY-MM-DD HH:MI:SS');
select ename,sal from emp where sal>to_number('$1,250.00','$9,999,99');
select ename,sal*12+nvl(comm,0) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select to_char(avg(sal),'9999.99') from emp;
select round(max(sal),2) from emp;
select sum(sal) from emp;
select count(*) from emp where deptno=10;
select count(distinct deptno) from emp;
selcet avg(sal) from emp;
select deptno,avg(sal) from emp group by deptno;
select ename from emp where sal=(select max(sal) from emp);
select deptno,max(sal) from emp group by deptno;
select deptn