<div id="codeSnippetWrapper"> <div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New',courier,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet">
<pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New',monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">Scott表下有这么几个常用的表，而且还带有数据。分别是emp、dept、salgrade；
emp;2 * 4 dual;sysdate dual;sysdate “toDay 日 期” dual;2*3 || 8 dual;ename || sal scott.emp;ename || ‘ORACLE’ scott.emp;2 * 2 || dual;deptno scott.emp;deptno,job scott.emp;查询查询， * scott.emp sal = 1500;、>=、<=* scott.emp sal > 1500;* scott.emp sal > 1500 sal <= 5000 deptno = 10;、* scott.emp sal (1500,800) deptno (10,20)转义* scott.emp ename ‘%%’;* scott.emp ename ‘%\%k%’;* scott.emp ename ‘%#%k%’ ‘#’;、sal,ename scott.emp sal;scott.emp sal ;scott.emp sal ;scott.emp sal > 2000 sal ;scott.emp sal,deptno ;、、substr(‘abcABC’) dual;(‘abcABC’) dual;substr(‘abcABC’,1,3) dual;(65) dual;ascii(‘Z’) dual;round(22.456) dual;round(22.456,2) dual;round(22.456,-1) dual;to_char(sal,) scott.emp;输出) scott.emp;年的拼写月的全拼星期的全拼to_char(sysdate,) dual;) dual;to_date(,) dual;to_number(,999.99') + 10 dual;) + 10 dual;to_number() + 10 dual;ename,sal,nvl(comm,1.00) scott.emp;组函数：、、、、(sal) scott.emp;(sal) scott.emp;(sal) emp;round((sal),2) emp;to_char((sal),999.99') emp;(sal) emp;(comm) emp;( deptno) emp;分组deptno,(sal) emp deptno;(sal) emp deptno,job;emp sal ( (sal) emp deptno);对分组数据进行过滤* ( (sal) sal,deptno emp deptno) sal > 2000;(sal) sal,deptno emp deptno (sal) > 2000;查询emp.ename,emp.sal,emp.deptno emp,( (sal) max_sal,deptno emp deptno) t emp.sal = t.max_sal emp.deptno = t.deptno;s.grade,t.deptno,t.avg_sal scott.salgrade s,( deptno,(sal) avg_sal emp deptno) t t.avg_sal > s.losal t.avg_sal < s.hisal;（）a.ename,b.ename mgr_name emp a,emp b a.empno = b.mgr;查询dname,ename dept,emp dept.deptno = emp.deptno;dept emp dept.deptno = emp.deptno;dept emp (deptno);dept emp dept.deptno = emp.deptno;dept emp dept.deptno = emp.deptno;dept emp dept.deptno = emp.deptno;emp a emp b a.mgr = b.empno;emp a emp b a.mgr = b.empno;rounum,dname dept;* (rownum r,dept.* deptt.r > 2;查询,empno,mgr empmgr = empno;函数row_number() (partition deptno sal),emp.* emp;rank() (partition deptno sal),emp.* emp;rank() ( deptno),monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> rank() ( sal),monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> --dense_rank排序给出相同序号,不空留序号dense_rank() ( sal),monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">18、交集、并集、割集查询* emp* emp2;* emp* emp2;显示不同部分查询系统表、视图owner,object_name,object_type,status,dba_objects.* dba_objects object_type = status = ;* user_objects object_type ;ename,deptno empsal ( (sal) emp deptno);emp( (sal) max_sal,deptno emp deptno) temp.deptno = t.deptno emp.sal = t.max_sal;grade,losal,hisal,t.avg_sal salgrade( (sal) avg_sal,deptno emp deptno) tt.avg_sal losal hisal;emp empno ( mgr emp);函数，查询薪水最高值* ( sal,ename emp sal ) rownum = 1;a.sal emp a emp b a.sal > b.sal rownum = 1;sal emp sal ( a.sal emp a emp b a.sal < b.sal);( (sal) avg_sal,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> avg_sal = ((avg_sal) max_sal ( (sal) avg_sal,deptno emp deptno)emp deptno) tavg_sal = (((sal)) max_sal emp deptno名称dname dept deptno = (deptno ( (sal) avg_sal,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> avg_sal = ((avg_sal) max_sal ( (sal) avg_sal,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> )dname dept deptno = (deptno ( (sal) avg_sal,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> avg_sal = (((sal)) emp deptno名称deptno ( (sal) avg_sal,deptno emp deptno)avg_sal = ((avg_sal) min_sal ((sal) avg_sal emp deptnoemp deptno)avg_sal = (((sal)) avg_sal emp deptno名称deptno (grade,t.deptno salgrade s ((sal) avg_sal,deptno emp deptnot.avg_sal s.losal s.hisalgrade = ((grade) salgrade s (t.avg_sal s.losal s.hisal名称t.deptno,dname (emp empno ( mgr emp)deptt.deptno = dept.deptnosal = ((sal) emp empno ( mgr emp)名称empno,sal emp empno ( mgr emp mgr )t.sal > ((sal) max_sal emp empno (mgr emp mgr
