oracle ro,ORACLE学习笔记一

1. initcap 首字母大写

select ename,initcap(ename) from emp;

2. lower   转换为小写字符

select ename,lower(ename) from emp;

3. upper 转换为大写

update dept set loc=lower(loc);

update dept set loc=upper(loc);

4. lpad(string,n,[pad_string])左填充

select deptno,lpad(dname,10,' '),loc from dept;

lpad('tech on the net', 16, 'z'); 将返回'ztech on the net'

5. RPAD 右填充

rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'

6. LTRIM 去除左边的空格

RTRIM 去除右边的空格

ALLTRIM  去除两边的空格

7. replace    替换

translate  转换

select ename,replace(ename,'S','s') from emp;

-- 用's'去替换ename中的'S'

select ename,translate(ename,'S','a') from emp;

8. substr 字符截取函数

select ename,substr(ename,1,3) from emp;

--从第1个位置开始 显示3个字符

9. sysdate 系统时间

select sysdate from dual;

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

select to_char(sysdate,'DDD') from dual;

select to_char(sysdate,'D') from dual;

select to_char(sysdate,'DAY') from dual;

select to_char(sysdate,'yyyy-mm-dd') from dual;

select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

select '''' from dual;

--从今天零点以后的秒数

select to_char(sysdate,'SSSSS') from dual;

10. ADD_MONTHS 添加月份 得到一个新的日期

select add_months(sysdate,1) from dual;

select add_months(sysdate,-1) from dual;

select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;

select add_months(sysdate,12) from dual;

--一年以后的今天

select add_months(sysdate,-12) from dual;

--一年以前的今天

11. last_day  某月的最后一天

select last_day(sysdate) from dual;

select add_months(last_day(sysdate)+3,-1) from dual;

--本月第3天的日期

12. months_between 两个日期之间的月数

select months_between(sysdate,'2005-02-01') from dual;

--方向 sysdate - '2005-02-01'

select months_between('2005-02-01',sysdate) from dual;

13. ceil(x)  不小于x的最小整数

ceil(12.4)   13

ceil(-12.4)   -12

14. round(x)  四舍五入

round(12.5)   13

round(12.456,2) 12.46

15. floor(x)  不大于x的最大整数

floor(12.5)  12

floor(-12.4)  -13

16. trunc(x)  舍去尾数

trunc(12.5)  12

trunc(12.456,2)  12.45

舍去日期的小时部分

select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;

17. mod(x,n)  x除以n以后的余数

mod(5,2) 1

mod(4,2) 0

18. power(x,y)  x的y次方

select power(3,3) from dual;

19. 求最大值

select greatest(100,90,80,101,01,19) from dual;

求最小值

select least(100,0,-9,10) from dual;

20. decode   选择结构  (if ... elseif .... elesif ... else结构)

要求:

sal=800  显示低工资

sal=3000  正常工资

sal=5000  高工资

只能做等值比较

select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断') from emp;

表示如下的if  else 结构

if sal=800 then

'低工资'

else if sal =3000 then

'正常工资'

else if  sal = 5000 then

'高工资'

else

'没判断'

end if

21.  判断正负

sign(x)   x是正  1

x是负  -1

x是0   0

select sign(-5) from  dual;

如何做大于小于的比较？？？？

sal

1000

3000

select sal,decode(sign(sal-1000),-1,'低工资',

decode(sign(sal-3000),-1,'正常工资',0,'正常工资',1,

decode(sign(sal-5000),-1,'高工资','高工资')

)) as 工资状态 from emp;

一般的情况  decode(x,y1,z1,y2,z2,z3)

if x= y1 then

z1

else if x = y2 then

z2

else

z3

end if

22. 层次查询

--level 伪列 层次查询的时候可以使用的  层的编号

select lpad('+',level,' ')||ename from emp

connect by prior empno = mgr --父子关系 父结点中的empno = 子节点中的mgr

start with mgr is null;--从 mgr is null的节点 开始遍历

select lpad('+',level,' ')||ename from emp

connect by prior empno = mgr

start with ename = 'BLAKE';

23. 关联查询

多张表,而表与表之间是有联系的

是通过字段中的数据的内在联系来发生

而不是靠相同的字段名来联系的或者是否有主外键的联系是没有关系的

select dname,ename from emp,dept;

笛卡尔积  (无意义的)

--当2个表作关联查询的时候一定要写关联的条件

--N个表 关联条件一定有N-1个

select dname,ename from mydept,myemp

where mydept.no = myemp.deptno;

多表查询的时候一定要有关联的条件

--使用的表的全名

select dname,ename from emp,dept

where emp.deptno = dept.deptno ;

--使用表的别名

select dname,ename,a.deptno from emp a,dept b

where a.deptno = b.deptno and a.deptno = 10;

--等值连接(内连接-两个表的数据作匹配a.deptno = b.deptno )

select dname,ename,a.deptno from

emp a inner join dept b

on a.deptno = b.deptno;

where a.deptno = 10;

--on写连接条件的

--where中写别的条件

--使用where/on

select dname,ename,a.deptno from emp a,dept b

where a.deptno = b.deptno and a.deptno=10;

--on中写连接条件

--where中写其他的条件

select dname,ename,a.deptno from

emp a inner join dept b

on a.deptno = b.deptno

where a.deptno = 10 ;

--外连接

左外连接 右外连接  全外连接

(+)写法只有在ORACLE中有效

select dname,ename,b.deptno

from emp a,dept b

where a.deptno(+) = b.deptno;

--标准写法

select dname,ename,b.deptno

from emp a right outer join dept b

on a.deptno = b.deptno;

select dname,ename,b.deptno

from emp a,dept b

where a.deptno = b.deptno(+);

--标准写法

select dname,ename,b.deptno

from emp a left outer join dept b

on a.deptno = b.deptno;

--标准写法(全外联)

select dname,ename,b.deptno

from emp a full outer join dept b

on a.deptno = b.deptno;

--自连接

select a.ename as 员工姓名,b.ename as 经理名字 from emp a,emp b

where a.mgr = b.empno(+);

a.empno = b.mgr  ???????