--字符函数
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
-- 
学海无涯