Oracle筆記（三） Scott用戶的表結構

創(chuàng)建備份表
–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的基本構成
?經(jīng)典格式
–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;

經(jīng)典格式
–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;

轉載于:https://www.cnblogs.com/lujun/p/3714199.html

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的Scott用户的表结构的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。