单行函数

字符函数

• lower 转换小写
• upper 转换大写
• initcap 转换首字母大写

SELECT 'helloworld Java',
LOWER('helloworld Java') ,
UPPER('helloWorld Java') ,
INITCAP('helloWorld Java') 
FROM dual                                                                                                                  

• concat 字符连接

SELECT CONCAT('Hello', ' World!') FROM  dual

• substr 查询子串

SELECT SUBSTR('abcdefgh',2,4) FROM dual

• length 查询字符长度
• instr 获取某个字符在字符串中的位置

SELECT LENGTH('HelloWorld!'),INSTR('HelloWorld!','W') FROM dual

• lpad 向左补齐字符
• rpad 向右补齐字符

SELECT salary,LPAD(salary,10,'*'),RPAD(salary,10,'*') FROM emp

• trim去除前后字符函数

SELECT TRIM('H' FROM 'HelloHWorldHH') FROM dual

• trim去除空格

SELECT TRIM('  HelloHWorldHH   ') FROM dual

• replace字符串替换函数

SELECT REPLACE('abcdabcdabcd','a','m') FROM dual

数值函数

• round 四舍五入到指定位

SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM dual

• trunc 对指定位截断

SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM dual

-mod 求余（模）

SELECT sal, MOD(sal,1000) AS Mod_Sal FROM emp

日期函数

image.png

转换函数

• to_date 字符转换日期
• to_number 字符转换数字
• to_char 对日期进行转换

select TO_CAHR(SYSDATE,'yyyy-MM-dd hh:mm:dd day') from dual

• 金额格式，如果是用9，不足的位数不会填充；但是使用0，就会在不足的位上补零

SELECT TO_CHAR(sal, '$999,999.00') SALARY
FROM   emp
WHERE  ename = 'King';

SELECT TO_CHAR(sal, '$000,000.00') SALARY
FROM   emp
WHERE  ename = 'King'

通用函数

• nvl 将空值转换成一个已知的值
• nvl2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

SELECT ename,sal,NVL2(comm, comm, 0) comm
FROM  emp
WHERE deptno IN (20, 30)

• nullif (expr1, expr2) :相等返回NULL，不等返回expr1

select nullif(50,50) from dual

• coalesce :与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值

select coalesce(null,comm,sal) from emp

条件表达式

• case

select ename, deptno,
(case deptno
when 10 then '销售部'
when 20 then '技术部'
when 30 then '管理部'
else '无' end) deptname
from emp

• decode

select ename, deptno,
decode(deptno,
10, '销售部',
20, '技术部',
30, '管理部',
'无' ) deptname
from emp

练习1.1 写一个查询，用首字母大写，其他字母小写显示雇员的ename，显示名字的长度，并给每一列适当的标签，条件是满足所有雇员名字的开始字母是J、A或M的雇员，并对查询结果按雇员的ename升序排序。（用initcap、length、substr）

select initcap(ename) ename, length(ename) length from emp
where substr(ename,1,1) in ('J','A','M')
order by ename 

2.1 查询员工姓名中中包含大写或小写字母A的员工姓名。

select ename from emp where instr(upper(ename),'A')>0

2.2 查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函数，不能使用like进行判断)。

select ename, length(ename) from emp 
where deptno in (10,20) and hiredate>'1981-05-01' and instr(ename,'A')>0

2.3 查询每个职工的编号,姓名，工资要求将查询到的数据按照 要求将查询到的数据按照定的格式合并成一个字符串.
–前10位：编号,不足部分用填充,左对齐
–中间10位：姓名，不足部分用填充,左对齐
–后10位：工资，不足部分用*填充,右对齐

select rpad(empno,10,'*')||rpad(ename,10,'*')||lpad(sal,10,'*') emp from emp 

3.1 写一个查询，分别计算100.456四舍五入到小数点后2位，1位，整数位的值。

select round(100.456,2), round(100.456, 1), round(100.456,0) from dual

3.2 写一个查询，分别计算100.456从小数点后2位，1位，整数位截断的值。

select trunc(100.456,2), trunc(100.456,1), trunc(100.456,0) from dual

4.1 查询每个员工截止到现在一共入职多少天？

select round(sysdate-hiredate,0) days from emp

5.1 查询服务器当前时间

select sysdate from dual

5.2 查询部门10 20 的员工截止到2000年1月1日，工作了多少个月，入职的月份。(提示：使用months_between,extract)

select round(months_between('2000-01-01', hiredate),0) work_months, extract(month from hiredate) hire_month
from emp 
where deptno in (10,20)

5.3 如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期,转正日期，入职日期后的第一个星期一,入职当月的最后一天日期。（提示：使用add_months,next_day,last_day)

select ename, hiredate, add_months(hiredate, 6), next_day(hiredate, 'Monday'), last_day(hiredate)
from emp 
where job<>'MANAGER'

6.4 把字符串2015-3月-18 13:13:13 转换成日期格式转换成日期格式，并计算
和系统当前时间间隔多少天。 (提示：使用to_date函数)

select  round(sysdate-to_date('2015-March-18 13:13:13','YYYY-MM-DD HH24:MI:SS')) from dual

课后作业1. 计算2000年1月1日到现在有多少月，多少周（四舍五入）。

select round(months_between(sysdate,'2000-01-01')) months,
round((sysdate-date'2000-01-01')/7) weeks
from dual

2. 查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。

select * from emp where substr(ename,3,1)='A'
select * from emp where instr(ename,'A',3,1)=3

3. 使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘hello’分别处理得到下列字符串ello、Hello、ll、hello。

select trim('h'from'hello') from dual;
select initcap('hello') from dual;
select trim('b'from'bllb') from dual;
select lower('hello') from dual

4. 将员工工资按如下格式显示：123,234.00 RMB 。

select to_char('12323400', '999,999,99')||'RMB' sal from dual

5. 查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

select ename, nvl(to_char(mgr),'No Manager') from emp

6. 将员工的参加工作日期按如下格式显示：月份/年份。

select to_char(hiredate, 'MON/YY') from emp

7. 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，
   税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。

select ename, sal, (case 
when sal<1000 then 0
when sal>=1000 and sal<2000 then sal*0.1
when sal>=2000 and sal<3000 then sal*0.15
when sal>=3000 then sal*0.2
end) tax
from emp

8. 创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。

select ename, lpad(sal,15,'$') salary from emp