Oracle 10g数据库基础之基本查询语句-中-函数
?Oracle 10g數(shù)據(jù)庫(kù)基礎(chǔ)之基本查詢語句-中-函數(shù)
--資料參考：張烈?張建中《數(shù)據(jù)庫(kù)管理員培訓(xùn)講義》 函數(shù)：使用函數(shù)的目的是為了操作數(shù)據(jù)
將輸入的變量處理，返回一個(gè)結(jié)果。
變量可以有好多。
傳入的變量可以是列的值，也可以是表達(dá)式。
函數(shù)可以嵌套。
內(nèi)層函數(shù)的結(jié)果是外層函數(shù)的變量。
單行函數(shù)：每一行都有一個(gè)返回值，但可以有多個(gè)變量。
多行函數(shù)：多行有一個(gè)返回值。
單行函數(shù)的分類：字符操作函數(shù)
數(shù)字操作函數(shù)
日期操作函數(shù)
數(shù)據(jù)類型轉(zhuǎn)換函數(shù)
綜合數(shù)據(jù)類型函數(shù)
字符操作函數(shù)： 大小寫操作函數(shù)Lower,upper,initcap???小寫大寫首字母大寫
字符串操作函數(shù) Concat,length,substr,instr,trim,replace,lpad,rpad 實(shí)驗(yàn)12：操作字符串的函數(shù) 字符操作函數(shù)：大小寫操作函數(shù)
Lower,upper,initcap???小寫大寫首字母大寫字符串操作函數(shù)
Concat,length,substr,instr,trim,replace,lpad,rpad該實(shí)驗(yàn)的目的是掌握常用的字符串操作的函數(shù).
字符串的大小寫操作
SQL> Select lower(ename),upper(ename),initcap(ename) from emp; SQL>Select lower(ename)?小寫字母,upper(ename)?大寫字母,initcap(ename)?首字母大寫?from emp;SQL> select lower(' mf TR')?小寫字母, upper('mf TR')?大寫字母, initcap('mf TR')?首字母大寫?from dual;
Dual是虛表，讓我們用表的形式來訪問函數(shù)的值。
字符串操作函數(shù)
Concat,length,substr,instr,trim,replace,lpad,rpad其它字符串操作函數(shù)
concat將兩個(gè)字符連接到一起 select ename,job,concat(ename,job) from emp; 求字符串的長(zhǎng)度下面三句話是求字符串的長(zhǎng)度，字符串要單引。
select length('張三') from dual;--按照字 select lengthb('張三') from dual;--按字節(jié)select lengthc('張三') from dual;--unicode的長(zhǎng)度
substr截取字符串
substr(字符串,m,n),m是從第幾個(gè)字符開始，如果為負(fù)的意思是從后邊的第幾個(gè)開始。N是數(shù)多少個(gè)，如果不說就是一直到字符串的結(jié)尾
SQL> select ename,substr(ename,1,1) "第一個(gè)字母",substr(ename,-2) "最后兩個(gè)字母"from emp;
求子串在父串中的位置，0表示沒有在父串中找到該子串。 SQL> select ename,instr(ename,'A') "A在第幾位" from emp; trim截?cái)嘧址吞砑幼址暮瘮?shù) Trim函數(shù)是截掉頭或者尾連續(xù)的字符，一般我們的用途是去掉空格。 SQL> select trim(leading 'a' from 'aaabababaaa') from dual; 截掉連續(xù)的前置的a SQL> select trim(trailing 'a' from 'aaabababaaa') from dual; 截掉連續(xù)的后置的a SQL> select trim(both 'a' from 'aaabababaaa') from dual; 截掉連續(xù)的前置和后置的a SQL> select trim('a' from 'aaabababaaa') from dual; 如果不說明是前置還是后置就是both全截?cái)唷?/span>Lpad和rpad字符填充
SQL> select lpad(ename,20,'*') ename,rpad(ename,20,'。') ename from emp;
左填充和右填充，20是總共填充到多少位，*和。是要填充的字符串。
Lpad左填充，rpad右填充，一般的用途是美化輸出的結(jié)果。
如果位數(shù)不足，按照截取后的結(jié)果顯示，不報(bào)錯(cuò)。 select lpad(sal,18,'*')左填充18位星號(hào),rpad(sal,25,'。')右填充25位句號(hào),sal from emp; select lpad(sal,30,' ')左填充30位空格,rpad(sal,30,' ')右填充30位空格,sal from emp;REPLACE替換字符
SQL> SELECT REPLACE('JACK and JUE','J','xj_') FROM DUAL;將字符串中的J全部替換位xj_
實(shí)驗(yàn)13：操作數(shù)字的函數(shù)該實(shí)驗(yàn)的目的是掌握常用的關(guān)于數(shù)字操作的函數(shù).
? ROUND，TRUNC數(shù)字操作函數(shù)
以小數(shù)點(diǎn)位核心，2是小數(shù)點(diǎn)后兩位，0可以不寫，表示取整，-1表示小數(shù)點(diǎn)前一位
ROUND是四舍五入：
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;
TRUNC是截?cái)?#xff0c;全部舍棄：
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;
ceil取整，上進(jìn)位，和trunc全部去掉正好相反 SQL> select ceil(45.001) from dual; 取絕對(duì)值 SQL> select abs(-23.00) from dual; 取余數(shù) SQL> select mod(8.88,2) from dual; 實(shí)驗(yàn)14：操作日期的函數(shù)該實(shí)驗(yàn)的目的是掌握常用的關(guān)于日期操作的函數(shù).
系統(tǒng)日期的操作日期是很特殊的數(shù)據(jù)類型，用好了可以提高數(shù)據(jù)庫(kù)的性能，而使用不當(dāng)往往是錯(cuò)誤的根源，如果你使用字符型數(shù)據(jù)來存儲(chǔ)日期，就放棄日期特有的計(jì)算功能。
函數(shù)SYSDATE求當(dāng)前數(shù)據(jù)庫(kù)的時(shí)間。
SQL> select sysdate from dual;日期的顯示格式和客戶端的配置相關(guān)。
查看當(dāng)前的日期顯示格式SQL> select * from nls_session_parameters
where parameter='NLS_DATE_FORMAT';
col value for a20代表的含義是凡是列的名稱是value的，都按照20個(gè)寬度來顯示，你想取消該列的定義
col value clear，其中col是column的縮寫。你想查看幫助help column即可
alter session set NLS_DATE_FORMAT='yyyy/mm/dd:hh24:mi:ss';重新設(shè)定為我們想要的格式。
select sysdate from dual; SYSDATE ------------------- 2012/5/16007/05/01:16:32:54查看系統(tǒng)時(shí)間，數(shù)據(jù)庫(kù)本身沒有時(shí)間，它有scn號(hào)，和我們的時(shí)間不同。
alter session set NLS_DATE_FORMAT='DD-MON-RR';設(shè)定為默認(rèn)的顯示格式
select sysdate from dual;再次查看，我們發(fā)現(xiàn)日期的顯示隨著客戶端的格式變化而變化。
日期的內(nèi)部存儲(chǔ)都是以yyyymmddhh24miss存在數(shù)據(jù)庫(kù)中
日期的操作函數(shù) SQL> select round(sysdate-hiredate) days,sysdate,hiredate from emp;兩個(gè)日期相減的結(jié)果單位為天，往往是帶小數(shù)點(diǎn)。我們通過函數(shù)可以取整。
取兩個(gè)日期的月間隔SQL> select months_between(sysdate,hiredate) 兩個(gè)日期的月間隔,sysdate,hiredate from emp;
六個(gè)月過后是哪天?SQL> select sysdate?今天, add_months(sysdate,6)?六個(gè)月后? from dual;
SQL> select next_day(hiredate,5) ,hiredate from emp;
當(dāng)前的日期算起，下一個(gè)星期五是哪一天，這句話你可能運(yùn)行失敗，因?yàn)槿掌诤涂蛻舳说淖址O(shè)置有關(guān)系，如果你是英文的客戶端，就的用Friday來表達(dá)，日期是格式和字符集敏感的。如果你是中文的客戶端，就的用‘星期五’來表達(dá)。 該日期的月底是哪一天。 日期的進(jìn)位和截取 select hiredate,round(hiredate,'mm') ,round(hiredate,'month') from emp;select hiredate,round(hiredate,'yyyy') ,round(hiredate,'year') from emp;
select hiredate,trunc(hiredate,'mm') ,trunc(hiredate,'month') from emp;select hiredate,trunc(hiredate,'yyyy') ,trunc(hiredate,'year') from emp;
數(shù)字的進(jìn)位和截取是以小數(shù)點(diǎn)為中心，我們?nèi)⌒?shù)點(diǎn)前或后的值，而日期的進(jìn)位和截取是以年，月，日，時(shí)，分，秒為中心。
數(shù)據(jù)類型的隱式轉(zhuǎn)換字符串可以轉(zhuǎn)化為數(shù)字和日期。
數(shù)字要合法，日期要格式匹配。
select ename,empno from emp where empno='7900';數(shù)字和日期在賦值的時(shí)候也可以轉(zhuǎn)為字符串，但在表達(dá)式的時(shí)候不可以轉(zhuǎn)換。
select ename,empno from emp where ename='123'; select ename,empno from emp where ename=123; 數(shù)據(jù)類型的顯式轉(zhuǎn)換 To_char,to_date,to_number 日期轉(zhuǎn)化為字符串，請(qǐng)說明字符串的格式。SQL> select ename,to_char(hiredate,'yyyy/mm/dd') from emp;
FM消除前置的零和空格。SQL> select ename,to_char(hiredate,'fmyyyy/mm/dd') from emp;
其他格式：year,month,mon,day,dy,am,ddsp,ddspth
格式內(nèi)加入字符串請(qǐng)雙引。SQL> select to_char(hiredate,'fmyyyy "年" mm "月" dd "日"') from emp;
當(dāng)前距離零點(diǎn)的秒數(shù).select sysdate,to_char(sysdate,'sssss') ss from dual;
數(shù)據(jù)類型的顯式轉(zhuǎn)換 數(shù)字轉(zhuǎn)為字符串格式為9,0,$,l,.
col salary for a30SQL> select ename,to_char(sal,'9999.000') salary from emp;
SQL> select ename,to_char(sal,'$00099999000.00' ) salary from emp;
SQL> select ename,to_char(sal,'l99,999.000') salary from emp;
SQL> select ename,TO_char(sal,'9G999D99') salary from emp;
9是代表有多少寬度，如果不足會(huì)顯示成######,0代表強(qiáng)制顯示0，但不會(huì)改變你的結(jié)果。G是千分符，D是小數(shù)點(diǎn)。
在數(shù)據(jù)庫(kù)中16進(jìn)制的表達(dá)是按照字符串來描述的，所以你想將十進(jìn)制的數(shù)轉(zhuǎn)換為十六進(jìn)制的數(shù)使用to_char函數(shù)。
SQL> select to_char(321,'xxxxx') from dual;其中xxxxx的位數(shù)要足夠，不然報(bào)錯(cuò)，你就多寫幾個(gè)，足夠大就可以。
數(shù)據(jù)類型的顯式轉(zhuǎn)換 ? To_number,to_date如果你想將十六進(jìn)制的數(shù)轉(zhuǎn)換為十進(jìn)制的數(shù)請(qǐng)使用to_number函數(shù)。
SQL> select to_number('abc32','xxxxxxxx') from dual;
日期是格式和語言敏感的，切記！
SQL> select TO_NUMBER('100.00', '9G999D99') from dual;G為千分符，D為小數(shù)點(diǎn)
RR和yy日期數(shù)據(jù)類型
select to_char(sysdate,'yyyy') "當(dāng)前",
to_char(to_date('98','yy'),'yyyy') "yy98", to_char(to_date('08','yy'),'yyyy') "yy08", to_char(to_date('98','rr'),'yyyy') "rr98", to_char(to_date('08','rr'),'yyyy') "rr08" from dual;結(jié)果為
yy是兩位來表示年，世紀(jì)永遠(yuǎn)和說話者的當(dāng)前世紀(jì)相同。
RR比較靈活，它將世紀(jì)分為上半世紀(jì)和下半世紀(jì)。如果你處于上半世紀(jì)，描述的是0-49，那么就和當(dāng)前世紀(jì)相同，描述的是50-99就是上世紀(jì)。如果你處于下半世紀(jì)，描述的是0-49，那么是下個(gè)世紀(jì)，描述的是50-99就是當(dāng)前世紀(jì)。從而可以看出，RR的設(shè)計(jì)完全為了1990年到2010之間我們的思維習(xí)慣而設(shè)計(jì)的。當(dāng)我們時(shí)間到2050前后，使用起來就非常的別扭。
實(shí)驗(yàn)15：操作數(shù)據(jù)為null的函數(shù)該實(shí)驗(yàn)的目的是掌握常用的關(guān)于NULL值操作的函數(shù).
綜合數(shù)據(jù)類型函數(shù) NVL (expr1, expr2)如果expr1為非空，就返回expr1,?如果expr1為空返回expr2，兩個(gè)表達(dá)式的數(shù)據(jù)類型一定要相同。
NVL2 (expr1, expr2, expr3)如果expr1為非空，就返回expr2,?如果expr1為空返回expr3
NULLIF (expr1, expr2)如果expr1和?expr2相同就返回空，否則返回expr1
COALESCE (expr1, expr2, ..., exprn)返回括號(hào)內(nèi)第一個(gè)非空的值。
SQL> select ename,comm,nvl(comm,0) from emp;有獎(jiǎng)金就返回獎(jiǎng)金，獎(jiǎng)金為空就返回0。
select ename?姓名,sal?工資,comm?獎(jiǎng)金,sal+nvl(comm,0)?總工資,nvl2(comm,'工資加獎(jiǎng)金','純工資') "收入類別" from emp;
有獎(jiǎng)金就返回‘工資加獎(jiǎng)金‘，獎(jiǎng)金為空就返回‘純工資’。 SQL> select ename,nullif(ename,'KING') from emp; 如果員工的名稱為king就返回空，否則返回自己的名字。SQL> select ename?姓名,COALESCE(comm,0) "獎(jiǎng)金" from emp;
如果有獎(jiǎng)金就返回獎(jiǎng)金，如果沒有獎(jiǎng)金就返回0，起個(gè)別名叫做"獎(jiǎng)金"。
實(shí)驗(yàn)16：分支的函數(shù)該實(shí)驗(yàn)的目的是掌握分支操作的函數(shù).
??Case語句
9I以后才支持的新特性，說叫語句其實(shí)是函數(shù)。目的是為了分支。
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END 例子：例題：判別job,不同工作的人賦予不同的工資，除了CLERK，SALESMAN，ANALYST以外，其它的人工資不變，將函數(shù)的值起一個(gè)別名為"處理后數(shù)據(jù)"。
語句： SELECT ename, job, sal, CASE job WHEN 'CLERK' THEN 1.1111*sal WHEN 'SALESMAN' THEN 2.2222*sal WHEN 'ANALYST' THEN 3.3333*salELSE sal END "處理后數(shù)據(jù)"
FROM emp;Decode函數(shù)：
和CASE語句一樣都是分支語句，但Decode函數(shù)是ORACLE自己定義的，其它數(shù)據(jù)庫(kù)可能不支持。
語法如下：
DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])例題：判別job,不同工作的人賦予不同的工資，除了CLERK，SALESMAN，ANALYST以外，其它的人工資不變，將函數(shù)的值起一個(gè)別名為"處理后數(shù)據(jù)"。
語句： SELECT ename, job, sal, decode(job ,'CLERK' , 1.11*sal ,'SALESMAN' , 3.33*sal ,'ANALYST' , 8.88*sal, sal ) "處理后數(shù)據(jù)"
FROM emp; 下面的例題是求稅率：不同工資上的稅率不同。每2000一個(gè)臺(tái)階，8000以上一律40%的稅。
SQL>SELECT ename, sal, DECODE (TRUNC(sal/1000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30 , 0.40 )?稅率 FROM emp; 不管CASE語句還是DECODE函數(shù)，他們都是單行函數(shù)，每一行都有一個(gè)返回值。從ORACLE角度來講，DECODE更好，因?yàn)楦鱾€(gè)版本的數(shù)據(jù)庫(kù)都支持，橫向來說，CASE語句更好，因?yàn)樗菄?guó)標(biāo)，不同的數(shù)據(jù)庫(kù)間都認(rèn)可。 實(shí)驗(yàn)17：分組統(tǒng)計(jì)函數(shù)該實(shí)驗(yàn)的目的是掌握常用的組函數(shù).理解group by的操作.
需要掌握的知識(shí)點(diǎn)：1。組函數(shù)
2。分組統(tǒng)計(jì)
3。NULL值在組函數(shù)中的作用
4。HAVING的過濾作用
5。組函數(shù)的嵌套
??組函數(shù)這種函數(shù)每次處理多行，給出一個(gè)返回值
Avg平均
Sum求和
Max最大
Min最小
Count計(jì)數(shù)
所有組函數(shù)，除了count(*)以外，都忽略null值，count是計(jì)數(shù)，查看有多少行，count(列)是查看該列有多少非空的行。
例子：Group by?子句
Having是在結(jié)果中再次篩選。Having一定得出現(xiàn)在group by?子句得后面。不能獨(dú)立存在。
組函數(shù)的嵌套注意要使用GROUP BY子句。
巧用DECODE函數(shù)，改變排版方式
SQL>select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981", sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982", sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987", count(ename) "總?cè)藬?shù)" from emp;《完》
本文轉(zhuǎn)自xjzhujunjie 51CTO博客，原文鏈接:http://blog.51cto.com/xjzhujunjie/866373
總結(jié)
以上是生活随笔為你收集整理的Oracle 10g数据库基础之基本查询语句-中-函数的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 阿里云主机安装开发工具包报错处理
- 下一篇： Metasploit渗透某高校域服务器