Scott用户的表结构

Oracle笔记（三） Scott用户的表结构

创建备份表

–create table emp2 as select * from emp;

更新

–select * from emp where ename='SCOTT';

–update emp set deptno=10 where ename='SCOTT';

–update emp set mgr=7833, hiredate=date'1985-01-05' where ename='SCOTT';

删除

–delete from emp where empno=7788;

插入

–Insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('04/19/1987 ', 'MM/DD/YYYY'), 3000, 20);

2014 V1.5

SELECT的基本构成

经典格式

–select job, count(distinct deptno) from emp where mgr is not null group by job order by count(distinct deptno) desc, job;

–select job, count(distinct deptno) from emp where mgr is not null group by job order by 2 desc, job;

–select job, count(distinct deptno) uniq_deptno from emp where mgr is not null group by job order by uniq_deptno des c, job;

–select deptno, to_char(hiredate,'yyyy'), count(*) from emp group by deptno, to_char(hiredate,'yyyy');

–select deptno, to_char(hiredate,'yyyy'), count(*) from emp group by deptno, 2;

–select deptno, to_char(hiredate,'yyyy') hireyear, count(*) from emp group by deptno, hireyear;

–select deptno, to_char(hiredate,'yyyy'), count(*) from emp group by to_char(hiredate,'yyyy'), deptno;

经典格式

–select * from emp;

–select * from emp where empno>8000;

–select empno, ename, sal, comm from emp where sal
–select deptno, count(*) from emp group by deptno;

–select deptno, sum(sal) total_sal from emp where job='MANAGER' group by deptno;

–select job, count(distinct deptno) from emp where mgr is not null group by job order by job;