--字符函數
select LENGTH('HelloWorld') from dual;
select LTRIM(' HelloWorld
') from dual;
select RTRIM(' HelloWorld
') from dual;
select TRIM(' HelloWorld
') from dual;
select TRIM('H' FROM 'HelloWorld') from
dual;
select SUBSTR('HelloWorld',1,5) from dual; 從第一個位置截取5個
select LOWER('SQL Course') from dual; 全部小寫
select UPPER('SQL Course') from dual; 全部大寫
select INITCAP('SQL Course') from dual; 首字母大寫
select CONCAT('Hello', 'World') from dual; 連接兩個字符串，只能連接兩個
select INSTR('HelloWorld', 'W') from dual; 算出字符串當中的另一字符串出現的位置
select LPAD('salary',10,'*') from dual; 從左到右墊上10個字符，不夠的話，在左邊添*
select RPAD('salary', 10, '*') from dual;
--數值函數
select ROUND(45.926, 2) from dual;
select TRUNC(45.926, 2) from dual; 直接干掉小數點后面第三位
select MOD(1600, 300) from dual;
--日期函數
select sysdate from dual;
select MONTHS_BETWEEN (to_date('2007-12-1','yyyy-mm-dd'),sysdate)
from dual;
select ADD_MONTHS (sysdate,6) from dual;
select NEXT_DAY (sysdate,'星期五') from dual;
select LAST_DAY(sysdate) from dual;
select ROUND(SYSDATE,'MONTH') from dual;
select ROUND(to_date('2007-8-15','yyyy-mm-dd'),'MONTH')
from dual;
select ROUND(to_date('2007-8-16','yyyy-mm-dd'),'MONTH')
from dual;
select ROUND(SYSDATE ,'YEAR') from dual;
select ROUND(to_date('2007-6-30','yyyy-mm-dd'),'year')
from dual;
select ROUND(to_date('2007-7-1','yyyy-mm-dd'),'year')
from dual;
select TRUNC(SYSDATE ,'MONTH') from
dual;
select TRUNC(to_date('2007-8-15','yyyy-mm-dd'),'MONTH')
from dual;
select TRUNC(to_date('2007-8-16','yyyy-mm-dd'),'MONTH')
from dual;
select TRUNC(SYSDATE ,'YEAR') from
dual;
select TRUNC(to_date('2007-6-30','yyyy-mm-dd'),'year')
from dual;
select TRUNC(to_date('2007-7-1','yyyy-mm-dd'),'year')
from dual;
--數據類型轉換函數
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss') from dual;
select to_char(123456.789,'9,999,999.99')
from dual;
select to_number('123456.789') from dual;
select to_date('2000-01-01
13:23:45','yyyy-mm-dd hh24:mi:ss') from dual;
時間制一定要前后對應
--常規函數
select ename,nvl(comm,0) from emp;
select ename,sal,comm,
nvl2(comm, sal+comm, sal)
from emp;
select ename,job,
nullif(length(ename),length(job))
from emp
select ename,deptno,sal,
case deptno
when 10 then sal*10
when 20 then sal*20
when 30 then sal*30
else 0
end as test
from emp
select ename,deptno,sal,
decode(deptno,10,sal*10,20,sal*20,30,sal*30) as test
from emp
select deptno,
sum(decode(deptno,10,1)) as deptno10,
sum(decode(deptno,20,1)) as deptno20,
sum(decode(deptno,30,1)) as deptno30
from emp
group by deptno
--連接查詢
select ename,job,dname
from emp ,dept
where emp.deptno = dept.deptno
select ename,job,dname
from emp a,dept b
where a.deptno = b.deptno
select ename,job,a.deptno,dname
from emp a,dept b
where a.deptno = b.deptno
select ename,job,a.deptno,dname
from emp a,dept b
select ename,sal,grade
from emp a,salgrade b
where a.sal >= b.losal
and a.sal <= b.hisal
select ename,sal,grade
from emp a,salgrade b
where a.sal between b.losal and b.hisal
select dname,ename
from dept a left join emp b
on
a.deptno = b.deptno
select dname,ename
from dept a left join emp b
on
a.deptno = b.deptno
and
a.deptno = 10
select dname,ename
from dept a right join emp b
on
a.deptno = b.deptno
and
b.deptno = 10
select dname,ename
from dept a full join emp b
on
a.deptno = b.deptno
and
b.deptno = 10
select dname,ename
from dept a ,emp b
where a.deptno = b.deptno(+)
and
b.deptno(+) = 10
select e.ename,m.ename
from emp e,emp m
where e.mgr = m.empno
對PLSQL中
NVL函數的用法還不是很了解
已解決（有n 個參數，函數就為NVL（n-1）
從左到右，返回不為空的值）
select ename,job,
nullif(length(ename),length(job)) 已解決（若兩個長度相等，則為空，否則
from emp 返回第一個的參數的長度）
select
ename,deptno,sal,
case deptno
when 10 then sal*10
when 20 then sal*20
when 30 then sal*30
else 0
end as test
from emp
Select ename,deptno,sal,decode(deptno,10,sal*10,20,sal*20,30,sal*30)
as test
From emp 已解決（類似與 case）
select
dname,ename
from dept a left join emp on a.deptno = b.deptno
and a.deptno = 10
select dname,ename 已解決
from dept a full join emp b
on a.deptno = b.deptno
and b.deptno = 10
select
dname,ename 已解決
from dept a ,emp b
where a.deptno = b.deptno(+)
and b.deptno(+) = 10
select deptno,dname
from dept
where exists (select deptno 未解決
from emp
where dept.deptno =
emp.deptno)
select
a.empno,a.ename,a.sal
from emp a,(select deptno,avg(sal)
as avgsal
from emp
group by deptno) b
where a.deptno =
b.deptno and a.sal > b.avgsal
select (select count(*) from dept) +
(select count(*) from emp)
from dual
select empno,ename,deptno
from emp
where deptno = 10 or deptno = 20
union
select empno,ename,deptno
from emp
where deptno = 10 order by empno
-- 
學海無涯