oracle第三章复习

1.子查询：在select，update，delete语句内部出现select语句。
（1）单行子查询
查询销售部（SALES）下面的员工姓名，工作，工资
select ename,job,sal
from emp 
where deptno=(select deptno from dept where dname='SALES')
/
（2）多行子查询
查询emp表中比某个（任意一个）销售员（SALESMAN）工资低的员工姓名，工作，工资
select ename,job,sal
from emp
where sal<any(select sal from emp where job='SALESMAN')
/


查询出比所有销售员的工资都高的员工姓名，工作，工资
select ename,job,sal
from emp
where sal>all(select sal from emp where job='SALESMAN')
/


2.oracle中的伪列
伪列就像表中的列一样，但是在表中并不存储，只能对伪列进行查询，不能增删改。
其中两个：ROWID   ROWNUM
（1）rowid表示一行的物理地址
select rowid ,ename from emp where sal>2000;




ROWID              ENAME
------------------ ------
AAAMfPAAEAAAAAgAAD JONES
AAAMfPAAEAAAAAgAAF BLAKE
AAAMfPAAEAAAAAgAAG CLARK
AAAMfPAAEAAAAAgAAH SCOTT
AAAMfPAAEAAAAAgAAI KING
AAAMfPAAEAAAAAgAAM FORD


已选择6行。




（2）rownum是表示一行在结果集中的行号。必须有结果集，才有rownum，第一行是1，第二行是2....
①查询出emp表中前5名员工的姓名，工作，工资
select ename,job,sal from emp where rownum<=5;


②查询出工资最高的前5名员工的姓名，工作，工资
select ename,job,sal from emp
where rownum<=5
order by sal desc
/


查询结果如下：


ENAME      JOB              SAL
---------- --------- ----------
JONES      MANAGER         2975
ALLEN      SALESMAN        1600
MARTIN     SALESMAN        1250
WARD       SALESMAN        1250
SMITH      CLERK            800


其实这样是错误的，因为生成rownum操作比排序要早，这样输出的结果并不是工资最高的5个人。
正确的做法是先按工资进行排序，然后在排序结果进行查询。


select rownum,ename,job,sal 
from (select ename,job,sal from emp order by sal desc)
where rownum<=5
/




或者也可以写成这样：
select rownum,T.*
from (select ename,job,sal from emp order by sal desc)T
where rownum<=5
/


T是子查询的别名


查询结果如下：
   ROWNUM ENAME      JOB              SAL
--------- ---------- --------- ----------
        1 KING       PRESIDENT       5000
        2 SCOTT      ANALYST         3000
        3 FORD       ANALYST         3000
        4 JONES      MANAGER         2975
        5 BLAKE      MANAGER         2850




③查询emp表中第5条到第10条的记录
select * from emp where rownum>=5 and rownum<=10;  


查询结果：
未选定行


原因：因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。
如果用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。


正确的做法：
如果用>这样的语句时，要使用子查询，内层查询得到rownum列，该列作为一个正常的列，在外部去对它进行约束


select * from
(select rownum R,ename,job,sal from emp where rownum<=10)
where R>=5
/




结果如下：
         R ENAME      JOB              SAL
---------- ---------- --------- ----------
         5 MARTIN     SALESMAN        1250
         6 BLAKE      MANAGER         2850
         7 CLARK      MANAGER         2450
         8 SCOTT      ANALYST         3000
         9 KING       PRESIDENT       5000
        10 TURNER     SALESMAN        1500


已选择6行。


3.oracle函数
（1）单行函数
①字符函数


select ASCII('a') from dual --求‘a’的ASCII码
/
select concat('hello','World') from dual --连接字符串
/
select length('hello') from dual --求字符串长度
/
select lower('HELLO') from dual
/
select trim('=' from '==hello==') from dual --字符截去函数
/
select substr('abcde',2) from dual --字符串子函数
/
select substr('abcde',2,3) from dual
/


②数字函数
abs(x) --求绝对值
log(x)
cos(x)
acos(x)
mod(x,y)
power(x,y) --x的y次幂
round(x[,y]) --x在第y位四舍五入
trunc(x[,y]) --x在第y位截断
sqrt(x) --x的平方根


③日期函数


④转换函数
to_char()
to_date(x[,fmt])
to_number(x[,fmt])


（2）多行函数
聚合函数
avg
sum
max，min
count


查询每个部门的平均工资
select deptno,avg(sal)
from emp
group by deptno
/