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 ???????