字符函數(shù)

• lower 轉(zhuǎn)換小寫
• upper 轉(zhuǎn)換大寫
• initcap 轉(zhuǎn)換首字母大寫

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 查詢字符長(zhǎng)度
• instr 獲取某個(gè)字符在字符串中的位置

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

• lpad 向左補(bǔ)齊字符
• rpad 向右補(bǔ)齊字符

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

• trim去除前后字符函數(shù)

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

• trim去除空格

SELECT TRIM('  HelloHWorldHH   ') FROM dual

• replace字符串替換函數(shù)

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

數(shù)值函數(shù)

• round 四舍五入到指定位

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

• trunc 對(duì)指定位截?cái)?/li>

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

日期函數(shù)

image.png

轉(zhuǎn)換函數(shù)

• to_date 字符轉(zhuǎn)換日期
• to_number 字符轉(zhuǎn)換數(shù)字
• to_char 對(duì)日期進(jìn)行轉(zhuǎn)換

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

• 金額格式，如果是用9，不足的位數(shù)不會(huì)填充；但是使用0，就會(huì)在不足的位上補(bǔ)零

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'

通用函數(shù)

• nvl 將空值轉(zhuǎn)換成一個(gè)已知的值
• 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 相比的優(yōu)點(diǎn)在于 COALESCE 可以同時(shí)處理交替的多個(gè)值

select coalesce(null,comm,sal) from emp

條件表達(dá)式

• case

select ename, deptno,
(case deptno
when 10 then '銷售部'
when 20 then '技術(shù)部'
when 30 then '管理部'
else '無(wú)' end) deptname
from emp

• decode

select ename, deptno,
decode(deptno,
10, '銷售部',
20, '技術(shù)部',
30, '管理部',
'無(wú)' ) deptname
from emp

練習(xí)1.1 寫一個(gè)查詢，用首字母大寫，其他字母小寫顯示雇員的ename，顯示名字的長(zhǎng)度，并給每一列適當(dāng)?shù)臉?biāo)簽，條件是滿足所有雇員名字的開(kāi)始字母是J、A或M的雇員，并對(duì)查詢結(jié)果按雇員的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 查詢部門編號(hào)為10或20，入職日期在81年5月1日之后，并且姓名中包含大寫字母A的員工姓名，員工姓名長(zhǎng)度（提示，要求使用INSTR函數(shù)，不能使用like進(jìn)行判斷)。

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

2.3 查詢每個(gè)職工的編號(hào),姓名，工資要求將查詢到的數(shù)據(jù)按照 要求將查詢到的數(shù)據(jù)按照定的格式合并成一個(gè)字符串.
–前10位：編號(hào),不足部分用填充,左對(duì)齊
–中間10位：姓名，不足部分用填充,左對(duì)齊
–后10位：工資，不足部分用*填充,右對(duì)齊

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

3.1 寫一個(gè)查詢，分別計(jì)算100.456四舍五入到小數(shù)點(diǎn)后2位，1位，整數(shù)位的值。

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

3.2 寫一個(gè)查詢，分別計(jì)算100.456從小數(shù)點(diǎn)后2位，1位，整數(shù)位截?cái)嗟闹怠?/p>

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

4.1 查詢每個(gè)員工截止到現(xiàn)在一共入職多少天？

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

5.1 查詢服務(wù)器當(dāng)前時(shí)間

select sysdate from dual

5.2 查詢部門10 20 的員工截止到2000年1月1日，工作了多少個(gè)月，入職的月份。(提示：使用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個(gè)月，查詢職位不是MANAGER的員工姓名，入職日期,轉(zhuǎn)正日期，入職日期后的第一個(gè)星期一,入職當(dāng)月的最后一天日期。（提示：使用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 轉(zhuǎn)換成日期格式轉(zhuǎn)換成日期格式，并計(jì)算
和系統(tǒng)當(dāng)前時(shí)間間隔多少天。 (提示：使用to_date函數(shù))

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

課后作業(yè)1. 計(jì)算2000年1月1日到現(xiàn)在有多少月，多少周（四舍五入）。

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

2. 查詢員工ENAME的第三個(gè)字母是A的員工的信息(使用2個(gè)函數(shù))。

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

3. 使用trim函數(shù)將字符串‘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. 查詢員工的姓名及其經(jīng)理編號(hào)，要求對(duì)于沒(méi)有經(jīng)理的顯示“No Manager”字符串。

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

6. 將員工的參加工作日期按如下格式顯示：月份/年份。

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

7. 在員工表中查詢出員工的工資，并計(jì)算應(yīng)交稅款：如果工資小于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. 創(chuàng)建一個(gè)查詢顯示所有雇員的 ename和 sal。格式化sal為 15 個(gè)字符長(zhǎng)度，用 $ 左填充，列標(biāo)簽 SALARY。

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