oracle function select,Oracle笔记 三、function 、select

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