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<comm;
–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;