單行函數―字符函數
雖然各個數據庫都是支持SQL語句的,但是每一個數據庫也有每一個數據庫自己所支持的操作函數,這些就是單行函數,而如果要想進行數據庫開發的話,除了要會使用SQL之外,就是要多學習函數。
單行函數主要分為以下五類:字符函數、數字函數、日期函數、轉換函數、通用函數;
1-字符函數:
字符函數的功能主要是進行字符串數據的操作,下面給出幾個字符函數:
* UPPER(字符串|列):將輸入的字符串變為大寫返回;
* LOWER(字符串|列):將輸入的字符串變為小寫返回;
* INITCAP(字符串|列):開頭首字母大寫;
* LENGTH(字符串|列):求出字符串的長度;
* REPLACE(字符串|列):進行替換;
*SUBSTR(字符串|列,開始點[結束點]):字符串截取:
Oracle之中有一點比較麻煩,即使要驗證字符串,也必須編寫完整的SQL語句,所以在Oracle數據庫之中為了用戶的查詢方便,故專門提供了一個“dual”的虛擬表。
范例1:觀察轉大寫的函數
SELECT UPPER('hello') FROM dual ;
大寫轉換的用處: 在一般的使用之中,用戶輸入數據的時候去關心數據本身存放的是大寫還是小寫呢?
SELECT * FROM emp WHRER ename='&str';
此時如果輸入的是小寫,則肯定無法查詢出數據,所以這個時候不能要求用戶這么,故這個時候只能由程序自己去適應,加入一個函數:
SELECT * FROM emp WHERE ename=UPPER('&str');
當然以上的“&”(與)操作屬於替代變量的內容,此部分內容不做重點。
范例2:觀察轉小寫的操作,將所有的雇員姓名按照小寫字母返回;
SELECT LOWER('ename') FROM emp ;
范例3:將每一個雇員姓名的開頭首字母大寫
SELECT INITCAP(ename) FROM emp ;
范例4:查詢每個雇員姓名的長度
SELECT ename,LENGTH(ename) FROM emp ;
范例5:要求查詢出雇員姓名長度正好是5的信息;
SELECT ename,LENGTH(ename) FROM emp WHERE LENGTH(ename)=5 ;
范例6:使用字母“_”替換姓名中所有字母“A”的信息;
col REPLACE(ename,'A','_') FORMAT A50 ;SELECT REPLACE(ename,'A','_') FROM emp ;
字符串截取操作有兩種語法:
|-語法一:SUBSTPR(字符串|列,開始點),表示從開始點一直截以到結尾;
|-語法二:SUBSTPR(字符串|列,開始點,結束點),表示從開始點一直截以到結束點,截取部分內容;
范例7: 語法一:SUBSTPR(字符串|列,開始點),表示從開始點一直截以到結尾;
SELECT ename,SUBSTR(ename,3) FROM emp ; //從第3個字符開始一直到結尾!
范例8:語法二:SUBSTPR(字符串|列,開始點,結束點),表示從開始點一直截以到結束點,截取部分內容;
SELECT ename,SUBSTR(ename,0,3) FROM emp ; //截取前3個字符!SELECT ename,SUBSTR(ename,1,3) FROM emp ; //截取前3個字符!
范例9:要求截取每個雇員姓名的后三個字母;
* 正常思路:通過長度-2確定開始點
SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;
* 新思路:設置負數,表示從后指定截取位置
SELECT ename,SUBSTR(ename,-3) FROM emp ;
面試題:
1、請問SUBSTR()函數截取的時候下標是從0,還是從1開始?
* 在Oracle數據庫之中,SUBSTR()函數從0或1開始都是一樣的;(見范例8)
* SUBSTR()函數也可以設置為負數,表示由后指定截取開始點;(見范例9)
2-數字函數
數字函數一共有3個:
* ROUND(數字|列[,保留小數的位數]):四舍五入的操作;
* TRUNC(數字|列[,保留小數的位數]):舍棄指定位置的內容;
* MOD(數字1,數字2):取模,取余數;
范例10:驗證ROUND()函數
SELECT ROUND(903.5) FROM dual ;
SELECT ROUND(903.5),ROUND(-903.53567) FROM dual ;SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1) FROM dual ;SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1),ROUND(903.53567,2) FROM dual ;//保留2位
范例11:驗證TRUNC()函數
SELECT TRUNC(903.5),TRUNC(-903.53567),TRUNC(903.53567,-1),TRUNC(903.53567,2) FROM dual ;
范例12:驗證MOD()函數
SELECT MOD(10,3) FROM dual ;
以上的三個主要的數學函數,在學習Java中也會有相匹的內容!
2-日期函數
如果現在要想進行日期的操作,則首先有一個必須要解決的問題,就是如何取得當前的日期, 這個當前日期可使用“SYSDATE”取得,代碼如下:
SELECT SYSDATE FROM dual ;
范例13:除了以上的當前日期之外,在日期中也可以進行若干計算:
* 日期 + 數字 = 日期,表示若干天之后的日期;
SELECT SYSDATE+3,SYSDATE+300 FROM dual ;
* 日期 - 數字 = 日期,表示若干天前的日期;
SELECT SYSDATE-3,SYSDATE-300 FROM dual ;
* 日期 �C 日期 = 數字,表示的是兩個日期的天數,但是肯定是大日期 �C 小日期;
范例14:求出每個雇員到今天為止的雇佣天數;
SELECT ename,hiredate,SYSDATE-hiredate FROM emp ;
注:而且很多編程語言之中,也都會提出一種概念,日期可以通過數字表示出來!
除了以上三個公式之外,也提供了以下四個操作函數:
* LAST_DAY(日期):求出指定日期的最后一天;
范例15:求出本月的最后一天日期
SELECT LAST_DAY(SYSDATE) FROM dual ;
* NEXT_DAY(日期,星期數):求出下一個指定星期X的日期;
范例16:求出下一個周一
SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual ;
* ADD_MONTHS(日期,數字):求出若干月之后的日期;
范例17:求出4個月后是何時
SELECT ADD_MONTHS(SYSDATE,4) FROM dual ;
* MONTHS_BETWEEN(日期1,日期2):求出兩個日期之間所經歷的月份;
范例18:求出每個雇員到今天為止的雇佣月份;
SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp ;SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp ;
在所有的開發之中,如果是日期的操作,建議使用以上函數,因為這些函數可以避免閏年的問題。
4-轉換函數(核心)
現在已經接觸到了Oracle數據庫之中的三種數據:數字(NUMBER)、字符串(VACHAR2)、日期(DATE),轉換函數的主要功能是完成這幾種數據間的相互轉換的操作,一共有三種轉換函數:
* TO_CHAR(字符串|列,格式字符串):將日期或者是數字變為字符串顯示;
* TO_DATE(字符串,格式字符串):將字符串變為DATE數據顯示;
* TO_NUMBER(字符串):將字符串變為數字顯示;
1、TO_CHAR()函數
在之前查詢過當前的系統日期時間:
SELECT SYSDATE FROM dual ;
這個時候是按照“日-月-年”的格式顯示,很明顯此格式不符合於正常的思路,正常是“年-月-日”,
范例19:TO_CHAR()函數,但是使用此函數的話需要一些格式字符串:年(yyyy),月(mm),日(dd)
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') year,TO_CHAR(SYSDATE,'mm') month,TO_CHAR(SYSDATE,'dd') day FROM dual ;
范例20:但是這個時候的顯示數據之中可以發現會存在前導0,如果要想消除這個0的話,可以加入一個“fm”。
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') FROM dual ;
范例21:正常人都加0,故這個標記知道就行了,可是在Oracle之中,DATE里面是包含了時間的,但是之前的代碼沒有顯示出時間,要想顯示時間,則需要增加標記:
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh:mi:ss')day FROM dual ; //12時制SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') day FROM dual ;
范例22:一定要注意:使用TO_CHAR()函數之后,所有的內容都是字符串,不再是之前的DATE型數據,TO_CHAR()函數也可以用數字的格式化上,此時每一個“9”表示一位數字的概念,而不是數字9概念。
SELECT TO_CHAR(11157191115719,'999,999,999,999,999') FROM dual ;SELECT TO_CHAR(11157191115719,'L999,999,999,999,999') FROM dual ;
其中的字母“L”,表示的是“Locale”的含義,即:當前的所在語言環境下的貨幣符號。
2、TO_DATE()函數
范例23:此函數的主要功能是將一個字符串變為DATE型數據。
col TO_DATE('1988-8-8','yyyy-mm-dd') format a50 ;SELECT TO_DATE('1988-8-8','yyyy-mm-dd') FROM dual ;
一般此函數在更新數據時,使用比較多!
3、TO_NUMBER()函數:基本不用!
范例24:TO_NUMBER()函數一看就知道是將字符串變為數字的:
SELECT TO_NUMBER('1') + TO_NUMBER('2') + TO_NUMBER('3') FROM dual ;
范例25:但是在Oracle之中是很智能的,故以上功能不使用TO_NUMBER()函數也可以完成。
SELECT '1' + '2' + '3' FROM dual ;
重點:
* TO_NUMBER()函數,基本已經不考慮了;
* TO_CHAR()函數,是重點;
* TO_DATE()函數,是次重點。
5-通用函數(核心)
通用函數主要有兩個:NVL()、DECODE(),這兩個函數算是 Oracle自己的特色函數了;
1、NVL()函數,處理null
范例26::要求查詢出每個雇員的全部年薪
SQL> SELECT ename,sal,comm,(sal+comm)*12 年薪 FROM emp ;
范例27:由上可知,有雇員的年薪變為null了,而造成這種問題的關鍵是在於comm字段上為null,那么要想解決這個問題,就必須做一種處理:將null變為0,而這個就是NVL()函數作用。
SELECT ename,sal,comm,(sal+comm)*12,NVL(comm,0) FROM emp ;
范例28:看來,年薪還有為0的,下面再來看看!
SQL> SELECT ename,sal,comm,(sal+NVL(comm,0))*12 ?êD? FROM emp ;
2、DECODE()函數:多數值判斷
DECODE()函數非常類似於程序中的if…else語句,唯一不同的是DECODE()函數判斷的是數值,而不是邏輯條件了。
例如:要求顯示全部雇員的職位,但是這些職位要求替換成中文顯示:
* CLERK:辦事員;
* SALESMAN:銷售;
* MANAGER:經理;
* ANALYST:分析員;
* PRESIDENT:總裁;
這種判斷肯定是逐行判斷,故此時必須采用DECODE()函數,而此函數語法如下:
DECODE(數值|列,判斷1,顯示值1,判斷2,顯示值2,判斷3,顯示值3,...)
范例29:實現顯示的操作功能
SELECT empno,ename,job,DECODE(job,'CLERK','辦事員','SALESMAN','銷售員','MANAGER','經理','ANALYST','分析員','PRESIDENT','總裁') 職務 FROM emp ;
DECODE()函數是整個Oracle之中最具有特點的函數,必須掌握!!!
總結篇:
1、SQL語句的基本格式,此處給出的只是最基本的語法:SELECT、FROM、WHERE、ORDER BY的關系;
2、記下SCOTT用戶中的所有的數據表的信息,包括列的名稱,作用及類型;
3、Oracle中的幾個單行函數,一定要記下來,所有函數都要求使用。