【二】SQL的基本函數(shù)
2.1 關(guān)系型數(shù)據(jù)庫(kù)SQL命令類別
數(shù)據(jù)操縱語(yǔ)言：DML: select; insert; delete; update; merge.
數(shù)據(jù)定義語(yǔ)言：DDL: create; alter; drop; truncate; rename; comment.
事務(wù)控制語(yǔ)言：TCL: commit; rollback; savepoint.
數(shù)據(jù)控制語(yǔ)言：DCL: grant; revoke.
2.2 單行函數(shù)與多行函數(shù)
單行函數(shù)：指一行數(shù)據(jù)輸入，返回一個(gè)值得函數(shù)。所以查詢一個(gè)表時(shí)，對(duì)選擇的每一行數(shù)據(jù)都會(huì)返回一個(gè)結(jié)果。
SQL>select empno,lower(ename) from emp;
多行函數(shù)：指多行數(shù)據(jù)輸入，返回一個(gè)值得函數(shù)。所以對(duì)表的群組進(jìn)行操作，并且每組返回一個(gè)結(jié)果。（典型的是聚合函數(shù)）
SQL>select sum(sal) from emp;
2.3 單行函數(shù)的幾種類型
2.3.1 字符型函數(shù)
lower(‘SQL Course’)—–>sql course 返回小寫(xiě)
upper(‘sql course’)—–>SQL COURSE 返回大學(xué)
initcap(‘SQL course’)—–> Sql Course 每個(gè)單字返回首字母大寫(xiě)
concat(‘good’,’string’)—->good string 拼接 只能拼接2個(gè)字符串
substr(‘String’,1,3)—->Str 從第1位開(kāi)始截取3位數(shù)，
演變：只有兩個(gè)參數(shù)的
substr(‘String’,3) 正數(shù)第三位起始，得到后面所有字符
substr(‘String’,-2) 倒數(shù)第二位，起始，得到最后所有字符
instr(‘t#i#m#r#a#n#’,’#’) —>找第一個(gè)#字符在那個(gè)絕對(duì)位置，得到的數(shù)值
Instr參數(shù)經(jīng)常作為substr的第二個(gè)參數(shù)值
演變：Instr參數(shù)可有四個(gè)之多
如select instr(‘aunfukk’,’u’,-1,1) from dual; 倒數(shù)第一個(gè)u是哪個(gè)位置，結(jié)果返回5
length(‘String’)—->6 長(zhǎng)度，得到的是數(shù)值
length參數(shù)又經(jīng)常作為substr的第三個(gè)參數(shù)
lpad(‘first’,10,’#39;)左填充
rpad(676768,10,’*’)右填充
replace(‘JACK and JUE’,’J’,’BL’)—->BLACK and BLUE
trim(‘m’ from ‘mmtimranm’)—->timran 兩頭截，這里的‘m’是截取集，僅能有一個(gè)字符
trim( ‘ timran ‘)—->timran 作用是兩頭去空字符
處理字符串時(shí)，利用字符型函數(shù)的嵌套組合是非常有效的：
create table customers(cust_name varchar2(20));
insert into customers values(‘Lex De Hann’);
insert into customers values(‘Renske Ladwig’);
insert into customers values(‘Jose Manuel Urman’);
insert into customers values(‘Joson Malin’);
select * from customers;
CUST_NAME
——————–
Lex De Hann
Renske Ladwig
Jose Manuel Urman
Joson Malin
一共四條記錄，客戶有兩個(gè)名的，也有三個(gè)名的，現(xiàn)在想列出僅有三個(gè)名的客戶，且第一個(gè)名字用*號(hào)略去
答案之一：
SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name),’*’) “CUST NAME”
FROM customers
WHERE INSTR(cust_name,’ ‘,1,2)0;
CUST NAME
————————————————————————————————————————
*** De Hann
**** Manuel Urman
分析：
先用INSTR(cust_name,’ ‘)找出第一個(gè)空格的位置，
然后，SUBSTR(cust_name,INSTR(cust_name,’ ‘))從第一個(gè)空格開(kāi)始往后截取字符串到末尾，結(jié)果是第一個(gè)空格以后所有的字符,
最后，LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name),’*’)用LPAD左填充到cust_name原來(lái)的長(zhǎng)度，不足的部分用*填充，也就是將第一個(gè)空格前的位置，用*填充。
where后過(guò)濾是否有三個(gè)名字，INSTR(cust_name, ‘ ‘,1,2)從第一個(gè)位置，從左往右，查找第二次出現(xiàn)的空格，如果返回非0值，則說(shuō)明有第二個(gè)空格，則有第三個(gè)名字。
2.3.2 數(shù)值型函數(shù)
round 對(duì)指定的值做四舍五入,round(p,s) s為正數(shù)時(shí)，表示小數(shù)點(diǎn)后要保留的位數(shù)，s也可以為負(fù)數(shù)，但意義不大。
round:按指定精度對(duì)十進(jìn)制數(shù)四舍五入,如:round(45.923, 1),結(jié)果,45.9
round(45.923, 0),結(jié)果,46
round(45.923, -1),結(jié)果,50
trunc 對(duì)指定的值取整 trunc(p,s)
trunc:按指定精度截?cái)嗍M(jìn)制數(shù),如:trunc(45.923, 1),結(jié)果,45.9
trunc(45.923),結(jié)果,45
trunc(45.923, -1),結(jié)果, 40
mod 返回除法后的余數(shù)
SQL> select mod(100,12) from dual;
2.3.3 日期型函數(shù)
因?yàn)槿掌谠趏racle里是以數(shù)字形式存儲(chǔ)的，所以可對(duì)它進(jìn)行加減運(yùn)算，計(jì)算是以天為單位。
缺省格式：DD-MON-RR.
可以表示日期范圍：（公元前）4712 至（公元）9999
時(shí)間格式
SQL> select to_date(‘2003-11-04 00:00:00′ ,’YYYY-MM-DD HH24:MI:SS’) FROM dual;
SQL> select sysdate+2 from dual; 當(dāng)前時(shí)間+2day
SQL> select sysdate+2/24 from dual; 當(dāng)前時(shí)間+2hour
SQL> select sysdate+2/1440 from dual; 當(dāng)前時(shí)間+2分鐘
SQL> select (sysdate-hiredate)/7 week from emp; 兩個(gè)date類型差，結(jié)果是以天為整數(shù)位的實(shí)數(shù)。
①M(fèi)ONTHS_BETWEEN 計(jì)算兩個(gè)日期之間的月數(shù)
SQL>select months_between(‘1994-04-01′,’1992-04-01’) mm from dual;
查找emp表中參加工作時(shí)間>30年的員工
SQL>select * from emp where months_between(sysdate,hiredate)/12>32;
很容易認(rèn)為單行函數(shù)返回的數(shù)據(jù)類型與函數(shù)類型一致，對(duì)于數(shù)值函數(shù)類型而言的確如此，但字符和日期函數(shù)可以返回任何數(shù)據(jù)類型的值。比如instr函數(shù)是字符型的，months_between函數(shù)是日期型的，但它們返回的都是數(shù)值。
②ADD_MONTHS 給日期增加月份
SQL>select hiredate,add_months(hiredate,4) from emp;
③LAST_DAY 日期當(dāng)前月份的最后一天
SQL>select hiredate,last_day(hiredate) from emp;
④NEXT_DAY NEXT_DAY的第2個(gè)參數(shù)可以是數(shù)字1-7，分別表示周日–周六(考點(diǎn)）
比如要取下一個(gè)星期六，則應(yīng)該是：
SQL>select next_day(sysdate,7) FROM DUAL;
⑤ROUND(p,s)，TRUNC(p,s)在日期中的應(yīng)用，如何舍入要看具體情況，s是MONTH按30天計(jì)，應(yīng)該是15舍16入，s是YEAR則按6舍7入計(jì)算。
SQL>SELECT empno, hiredate,round(hiredate,’MONTH’) AS round,trunc(hiredate,’MONTH’) AS trunc FROM emp;
SQL>SELECT empno, hiredate, round(hiredate,’YEAR’) AS round,trunc(hiredate,’YEAR’) AS trunc FROM emp;
2.3.4 幾個(gè)有用的函數(shù)和表達(dá)式
1）DECODE函數(shù)和CASE表達(dá)式：
實(shí)現(xiàn)sql語(yǔ)句中的條件判斷語(yǔ)句，具有類似高級(jí)語(yǔ)言中的if-then語(yǔ)句的功能。
decode函數(shù)源自oracle, case表達(dá)式源自sql標(biāo)準(zhǔn)，實(shí)現(xiàn)功能類似，decode語(yǔ)法更簡(jiǎn)單些。
decode函數(shù)用法：
SQL> SELECT job, sal,
decode(job, ‘ANALYST’, SAL*1.1, ‘CLERK’, SAL*1.15,’MANAGER’, SAL*1.20, SAL) SALARY FROM emp
decode函數(shù)的另幾種常見(jiàn)用法：
SQL>select ename,job,decode(job,’MANAGER’,’中層干部’) leader from emp;
SQL> select ename,job,comm,decode (comm,null,’nonsale’,’sale’) saleman from emp;
注：?jiǎn)我涣刑幚?，共四個(gè)參數(shù)：含義是：comm 如果為null就取’nonsale，否則取’sale’
SQL> select sal,sign(sal-1500) from emp;
SQL> select ename,decode (sign(sal-1500), 1, ‘NORMAL’,’LOW’) as “LEV” from emp;
注：sign()函數(shù)根據(jù)某個(gè)值是0、正數(shù)還是負(fù)數(shù)，分別返回0、1、-1，含義是：工資大于1500,返回1，真取’NORMAL’，假取’LOW’
CASE表達(dá)式第一種用法：
SQL> SELECT job, sal,
case job when ‘ANALYST’ then SAL*1.10
when ‘CLERK’ then SAL*1.15
when ‘MANAGER’ then SAL*1.20
else sal end SALARY
FROM emp
CASE表達(dá)式第二種用法：
SQL> SELECT job, sal, case
when job=’ANALYST’ then SAL*1.1
when job=’CLERK’ then SAL*1.15
when job=’MANAGER’ then SAL*1.20
else sal end SALARY
FROM emp
以上三種寫(xiě)法結(jié)果都是一樣的
CASE第二種語(yǔ)法比第一種語(yǔ)法增加了搜索功能。形式上第一種when后跟定值，而第二種還可以使用表達(dá)式和比較符。
看一個(gè)例子
SQL> SELECT ename,sal,case
when sal>=3000 then ‘高級(jí)’
when sal>=2000 then ‘中級(jí)’
else ‘低級(jí)’ end 級(jí)別
FROM emp
再看一個(gè)例子:使用了復(fù)雜的表達(dá)式
SQL> SELECT AVG(CASE
WHEN sal BETWEEN 500 AND 1000 AND JOB=’CLERK’
THEN sal ELSE null END) “CLERK_SAL”
from emp;
比較;
SQL> select avg(sal) from emp where job=’CLERK’;
2）DISTINCT(去重)限定詞的用法：
distinct貌似多行函數(shù)，嚴(yán)格來(lái)說(shuō)它不是函數(shù)而是select子句中的一個(gè)選項(xiàng)。
SQL> select distinct job from emp; 消除表行重復(fù)值。
SQL> select distinct job,deptno from emp; 重復(fù)值是后面的字段組合起來(lái)考慮的
SQL> select distinct * from emp; 消除重復(fù)記錄
3）sys_context 獲取環(huán)境上下文的函數(shù)（多用于應(yīng)用環(huán)境）
scott遠(yuǎn)程登錄
SQL>select SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’) from dual;
——————————————————————————–
192.168.0.136
SQL> select sys_context(‘userenv’,’sid’) from dual;
SYS_CONTEXT(‘USERENV’,’SID’)
——————————————————————————–
129
SQL> select sys_context(‘userenv’,’terminal’) from dual;
SYS_CONTEXT(‘USERENV’,’TERMINAL’)
——————————————————————————–
TIMRAN-222C75E5
the end ！??！
@jackman 共筑美好！
