|
<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.* dept
t.r > 2;
,empno,mgr emp
mgr = 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 emp
sal ( (sal) emp deptno);
emp
( (sal) max_sal,deptno emp deptno) t
emp.deptno = t.deptno emp.sal = t.max_sal;
grade,losal,hisal,t.avg_sal salgrade
( (sal) avg_sal,deptno emp deptno) t
t.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) t
avg_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 deptno
emp deptno)
avg_sal = (
((sal)) avg_sal emp deptno
deptno (
grade,t.deptno salgrade s (
(sal) avg_sal,deptno emp deptno
t.avg_sal s.losal s.hisal
grade = (
(grade) salgrade s (
t.avg_sal s.losal s.hisal
t.deptno,dname (
emp empno ( mgr emp)
dept
t.deptno = dept.deptno
sal = (
(sal) emp empno ( mgr emp)
empno,sal emp empno ( mgr emp mgr )
t.sal > (
(sal) max_sal emp empno (
mgr emp mgr
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
