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